Reshaping data frame 8 rows to 2 rows

Question

df<-structure(list(AC_NAME = c("Behat", "Behat", "Behat", "Behat", 
"Nakur", "Nakur", "Nakur", "Nakur"), CAND_NAME = c("MAHAVEER SINGH RANA", 
"NARESH", "UMAR ALI KHAN", "AJAY CHAUHAN", "DR. DHARAM SINGH SAINI", 
"IMRAN MASOOD", "FIROZ AFTAB", "GOVIND CHOUDHARY"), PARTYABBRE = c("BSP", 
"INC", "SP", "BJP", "BSP", "INC", "SP", "IND"), TOTVOTPOLL = c(70274, 
69760, 47366, 23623, 89187, 84623, 29503, 14226), POSITION = c(1, 
2, 3, 4, 1, 2, 3, 4)), row.names = c(NA, 8L), class = "data.frame")

>df 
  AC_NAME              CAND_NAME PARTYABBRE TOTVOTPOLL POSITION
1   Behat    MAHAVEER SINGH RANA        BSP      70274        1
2   Behat                 NARESH        INC      69760        2
3   Behat          UMAR ALI KHAN         SP      47366        3
4   Behat           AJAY CHAUHAN        BJP      23623        4
5   Nakur DR. DHARAM SINGH SAINI        BSP      89187        1
6   Nakur           IMRAN MASOOD        INC      84623        2
7   Nakur            FIROZ AFTAB         SP      29503        3
8   Nakur       GOVIND CHOUDHARY        IND      14226        4

I want this to reshape like this.

data
  AC_NAME        Candidate_Rank1 Candidate_Rank2 Candidate_Rank3  Candidate_Rank4 Party_Rank1 Party_Rank2 Party_Rank3 Party_Rank4 Votes_Rank1 Votes_Rank2 Votes_Rank3
1   Behat    MAHAVEER SINGH RANA          NARESH   UMAR ALI KHAN     AJAY CHAUHAN         BSP         INC          SP         BJP       70274       69760       47366
2   Nakur DR. DHARAM SINGH SAINI    IMRAN MASOOD     FIROZ AFTAB GOVIND CHOUDHARY         BSP         INC          SP         IND       89187       84623       29503
  Votes_Rank4 Poistion1 Poistion2 Poistion3 Poistion4
1       23623         1         2         3         4
2       14226         1         2         3         4

I have tried dcast. Using it I am having more than desired columns. I think it won't work here I am not sure. Someone guide me.I dont mind that column names be the same as mine they can be changed later.

dd <- dcast(data, AC_NAME+CAND_NAME ~POSITION ,value.var = "POSITION")

score 1 · Accepted Answer · answered Sep 15 '21 at 12:28

1

You can use dcast -

library(data.table)
dcast(setDT(df), AC_NAME ~POSITION ,
      value.var = c('CAND_NAME', 'PARTYABBRE', 'TOTVOTPOLL'))  

#   AC_NAME            CAND_NAME_1  CAND_NAME_2   CAND_NAME_3      CAND_NAME_4 PARTYABBRE_1 PARTYABBRE_2
#1:   Behat    MAHAVEER SINGH RANA       NARESH UMAR ALI KHAN     AJAY CHAUHAN          BSP          INC
#2:   Nakur DR. DHARAM SINGH SAINI IMRAN MASOOD   FIROZ AFTAB GOVIND CHOUDHARY          BSP          INC

#   PARTYABBRE_3 PARTYABBRE_4 TOTVOTPOLL_1 TOTVOTPOLL_2 TOTVOTPOLL_3 TOTVOTPOLL_4
#1:           SP          BJP        70274        69760        47366        23623
#2:           SP          IND        89187        84623        29503        14226

Or using tidyr::pivot_wider.

tidyr::pivot_wider(df, names_from = POSITION, values_from = c(CAND_NAME, PARTYABBRE, TOTVOTPOLL))

POSITION value is already included in the column names of the new dataframe.

answered Sep 15 '21 at 12:28

Ronak Shah

377,200
20
156
213

`value.var` converts 'CAND_NAME', 'PARTYABBRE', 'TOTVOTPOLL' to columns? – Daman deep Sep 16 '21 at 00:21
No, it is the vector of column names from where we want the values in wide format. – Ronak Shah Sep 16 '21 at 01:37
`AC_NAME ~POSITION` Why we wrote POSITION here ronak? – Daman deep Sep 28 '21 at 07:35
The general syntax is `id_column~column_name` so here the id value which is unique is `AC_NAME` whereas `POSITION` is the column name that we want in wide format. – Ronak Shah Sep 28 '21 at 09:50

Reshaping data frame 8 rows to 2 rows

1 Answers1