CODEWITHSUNDEEP
rreshape2
umair durrani
umair durrani

Reputation: 6179

How to transpose a data frame by group using reshape2 library?

Problem

I have 3 cars (grouping variable) for which the speeds over time are recorded in the following data set. I want to transpose this data frame by group.

Data

foo <- structure(list(file.ID2 = c("Cars_03", "Cars_03", "Cars_03", 
"Cars_03", "Cars_03", "Cars_03", "Cars_03", "Cars_03", "Cars_04", 
"Cars_04", "Cars_04", "Cars_04", "Cars_04", "Cars_04", "Cars_04", 
"Cars_04", "Cars_05", "Cars_05", "Cars_05", "Cars_05", "Cars_05", 
"Cars_05", "Cars_05", "Cars_05"), speed.kph.ED = c(129.3802848, 
129.4022304, 129.424176, 129.4461216, 129.4680672, 129.47904, 
129.5009856, 129.5229312, 127.8770112, 127.8221472, 127.7672832, 
127.7124192, 127.6575552, 127.6026912, 127.5478272, 127.4929632, 
134.1095616, 134.1205344, 134.1315072, 134.1534528, 134.1644256, 
134.1753984, 134.1863712, 134.197344)), row.names = c(NA, -24L
), class = c("tbl_df", "tbl", "data.frame"), .Names = c("file.ID2", 
"speed.kph.ED"))

Desired Output

> foo
              V2       V3       V4       V5       V6       V7       V8       V9
Cars_03 129.3803 129.4022 129.4242 129.4461 129.4681 129.4790 129.5010 129.5229
Cars_04 127.8770 127.8221 127.7673 127.7124 127.6576 127.6027 127.5478 127.4930
Cars_05 134.1096 134.1205 134.1315 134.1535 134.1644 134.1754 134.1864 134.1973

What I tried

After a lot of searching, I found that reshape2 library can be useful. I found this question that seems to be relevant. So, I tried using melt() and dcast() functions as follows: 

library(reshape2)
dcast(melt(foo,id.vars = 1),
      file.ID2 ~ variable, NULL)
Aggregation function missing: defaulting to length
  file.ID2 speed.kph.ED
1  Cars_03            8
2  Cars_04            8
3  Cars_05            8

But this just aggregates the values (counts/sum). Please help me get the desired output. Thanks.

Upvotes: 1

Views: 1520

Answers (1)

lebelinoz
lebelinoz

Reputation: 5068

For reshaping data, I like to use the tidyr library's spread function.

First, add an id column. There's a billion different ways to do this (I like to use the dplyr package):

library(dplyr)
foo = foo %>% group_by(file.ID2) %>% mutate(id = paste0("V",row_number()))

And here's how to use tidyr library to spread the data out:

library(tidyr)
answer = spread(data = foo, key = id, value = speed.kph.ED)

Upvotes: 3

Related Questions