How to reshape long ranked order data into Wide data format

Question

The data snippet is taken from mlogit package (Game2) which is in long format to mimic my situation. where ch is rank given to the platform, and chid is id of one respondent

         age        hours      platform       ch       own      chid
1        33        2.00        GameBoy        6        0        1        
2        33        2.00        GameCube       5        0        1
3        33        2.00        PC             4        1        1
4        33        2.00        PlayStation    1        1        1
5        33        2.00        PSPortable     3        0        1
6        33        2.00        Xbox           2        0        1        
7        19        3.25        GameBoy        6        0        2
8        19        3.25        GameCube       5        0        2
9        19        3.25        PC             1        1        2
10       19       3.25         PlayStation    2        1        2
11       19       3.25         PSPortable     3        0        2
12       19       3.25         Xbox           4        0        2        
13       18       4.00         GameBoy        6        0        3        
14       18       4.00         GameCube       4        0        3
15       18       4.00         PC             5        1        3        
16       18       4.00         PlayStation    1        1        3
17       18       4.00         PSPortable     2        0        3
18       18       4.00         Xbox           3        0        3

I need to convert this long data into wide form as shown below. which is in mlogit package. the rank is retained (from column 1 (ie. ch.Xbox) to column 6 (i.e ch.PC).

  ch.Xbox ch.PlayStation ch.PSPortable ch.GameCube ch.GameBoy ch.PC own.Xbox own.PlayStation own.PSPortable own.GameCube own.GameBoy own.PC age hours
1 2       1              3             5           6          4     0        1               0              0            0           1      33  2.00
2 4       2              3             5           6          1     0        1               0              0            0           1      19  3.25
3 3       1              2             4           6          5     0        1               0              0            0           1      18  4.00

My question is to retain long format to wide format given above as an example.

Possible duplicate of can the value.var in dcast be a list or have multiple value variables? — Hardik Gupta

www www · Accepted Answer · 2017-10-08T05:37:55

We can use dplyr and tidyr to perform the reshape.

library(dplyr)
library(tidyr)

# Reshape the data    
dt2 <- dt %>%
  gather(type, value, ch, own) %>%
  unite("platform_type", type, platform, sep = ".") %>%
  spread(platform_type, value) %>%
  arrange(chid)

If you want the final output to be the same as your desired output, you can further prepare a vector of column names and select the columns based on that.

# Prepare the column vector
vec <- c("Xbox", "PlayStation", "PSPortable", "GameCube", "GameBoy", "PC")
colname <- unlist(lapply(c("ch.", "own."), function(x) paste0(x, vec)))
colname2 <- c(colname, "age", "hours")

# Select columns
dt3 <- dt2 %>% select(colname2)

# View the result
ch.Xbox ch.PlayStation ch.PSPortable ch.GameCube ch.GameBoy ch.PC own.Xbox own.PlayStation own.PSPortable own.GameCube own.GameBoy own.PC age hours
1       2              1             3           5          6     4        0               1              0            0           0      1  33  2.00
2       4              2             3           5          6     1        0               1              0            0           0      1  19  3.25
3       3              1             2           4          6     5        0               1              0            0           0      1  18  4.00

DATA

dt <- read.table(text = "         age        hours      platform       ch       own      chid
1        33        2.00        GameBoy        6        0        1        
                 2        33        2.00        GameCube       5        0        1
                 3        33        2.00        PC             4        1        1
                 4        33        2.00        PlayStation    1        1        1
                 5        33        2.00        PSPortable     3        0        1
                 6        33        2.00        Xbox           2        0        1        
                 7        19        3.25        GameBoy        6        0        2
                 8        19        3.25        GameCube       5        0        2
                 9        19        3.25        PC             1        1        2
                 10       19       3.25         PlayStation    2        1        2
                 11       19       3.25         PSPortable     3        0        2
                 12       19       3.25         Xbox           4        0        2        
                 13       18       4.00         GameBoy        6        0        3        
                 14       18       4.00         GameCube       4        0        3
                 15       18       4.00         PC             5        1        3        
                 16       18       4.00         PlayStation    1        1        3
                 17       18       4.00         PSPortable     2        0        3
                 18       18       4.00         Xbox           3        0        3",
                 header = TRUE, stringsAsFactors = FALSE)

How to reshape long ranked order data into Wide data format

1 Answers