Reshape data from rows to column in dataframe in R

Question

I want to reshape my data which is in the following form in dataframe :

a  b  Type_c d  e  
1  1   0     10 9
2  1   0     20 9 
3  2   1     30 4
4  2   2     40 3
5  3   3     50 2
6  3   0     60 1
7  4   3     70 2
8  4   2     80 7 
9  4   2     90 8

And would like to get the data in the following format by reshaping or transforming.

a  b  Type_0_d Type_0_e Type_1_d Type_1_e type_2_d type_2_e type_3_d  type_3_e  
1  1  10       9          0        0         0      0         0          0
2  1  20       9          0        0         0      0         0          0
3  2  0        0          30       4         0      0         0          0
4  2  0        0          0        0        40      3         0          0
5  3  0        0          0        0         0      0        50          2
6  3  60       1          0        0         0      0         0          0
7  4  0        0          0        0         0      0        70          2
8  4  0        0          0        0        80      7         0          0
9  4  0        0          0        0        90      8         0          0

I found it little difficult to do the same in R. However, in Tableau it was just straight forward. Just wanted to know if there's a way to do the same in R effectively.

score 3 · Accepted Answer · answered Aug 05 '16 at 13:31

3

We can use dcast from data.table which can take multiple value.var columns

library(data.table)
dcast(setDT(df1), a+b ~paste0("Type_", Type_c), value.var = c("d", "e"), fill = 0)

answered Aug 05 '16 at 13:31

akrun

874,273
37
540
662

1

That is lovely. I ve been trying with `dcast` from `reshape` but I was nowhere close :/ – Sotos Aug 05 '16 at 13:32
1

@akrun : Thanks, this was great approach to work on. Simpler and faster. – PD1 Aug 05 '16 at 20:00

score 2 · Answer 2 · answered Aug 05 '16 at 13:40

2

Another option maybe using spread of tidyr package

library(dplyr)
library(tidyr)
df1 %>% mutate(e1=Type_c) %>% spread(key = Type_c,value = d,fill = 0) %>% spread(key =e1,value = e,fill = 0)

I know this is a bad solution but still it is doing the job. Any suggestion regarding modification of the code is welcome. One more thing we have to rename the columns properly.

answered Aug 05 '16 at 13:40

user2100721

3,557
2
20
29

1

I would use `gather/spread` `gather(df1, Var, Val, d:e) %>% unite(Varc, Var, Type_c) %>% spread(Varc, Val, fill = 0)` – akrun Aug 05 '16 at 13:45

Reshape data from rows to column in dataframe in R

2 Answers2