Merge data frames from a list with each other

Question

What I need:

I have a huge data frame with the following columns (and some more, but these are not important). Here's an example:

    user_id video_id group_id    x   y
1         1        0        0   39 108
2         1        0        0   39 108
3         1       10        0  135 180
4         2        0        0   20 123

User, video and group IDs are factors, of course. For example, there are 20 videos, but each of them has several "observations" for each user and group.

I'd like to transform this data frame into the following format, where there are as many x.N, y.N as there are users (N).

video_id  x.1   y.1  x.2  y.2  …
       0   39   108   20  123

So, for video 0, the x and y values from user 1 are in columns x.1 and y.1, respectively. For user 2, their values are in columns x.2, y.2, and so on.

What I've tried:

I made myself a list of data frames that are solely composed of all the x, y observations for each video_id:

summaryList = dlply(allData, .(user_id), function(x) unique(x[c("video_id","x","y")]) )

That's how it looks like:

List of 15
 $ 1 :'data.frame': 20 obs. of  3 variables:
  ..$ video_id: Factor w/ 20 levels "0","1","2","3",..: 1 11 8 5 12 9 20 13 7 10 ...
  ..$ x       : int [1:20] 39 135 86 122 28 167 203 433 549 490 ...
  ..$ y       : int [1:20] 108 180 164 103 187 128 185 355 360 368 ...
 $ 2 :'data.frame': 20 obs. of  3 variables:
  ..$ video_id: Factor w/ 20 levels "0","1","2","3",..: 2 14 15 4 20 6 19 3 13 18 ...
  ..$ x       : int [1:20] 128 688 435 218 528 362 299 134 83 417 ...
  ..$ y       : int [1:20] 165 117 135 179 96 328 332 563 623 476 ...

Where I'm stuck:

What's left to do is:

Merge each data frame from the summaryList with each other, based on the video_id. I can't find a nice way to access the actual data frames in the list, which are summaryList[1]$`1`, summaryList[2]$`2`, et cetera.

@James found out a partial solution:
```
Reduce(function(x,y) merge(x,y,by="video_id"),summaryList)
```
Ensure the column names are renamed after the user ID and not kept as-is. Right now my summaryList doesn't contain any info about the user ID, and the output of Reduce has duplicate column names like x.x y.x x.y y.y x.x y.x and so on.

How do I go about doing this? Or is there any easier way to get to the result than what I'm currently doing?

Can you provide a subset of your data? I'm thinking Reduce(function(x,y) merge(x,y,by="video_id"),summaryList) might do the trick — James
– James, Commented Dec 19, 2012 at 13:34
Can you provide a (simplified) example of your input data.frame and the expected output? I'm having difficulties understanding what you want to do. — Roland
– Roland, Commented Dec 19, 2012 at 13:37
@Roland I added a small example. Here's the whole dataset just in case. — slhck
– slhck, Commented Dec 19, 2012 at 13:41
@James That almost works, only the columns are video_id x.x y.x x.y y.y x.x y.x, etc., so not named after the user IDs. — slhck
– slhck, Commented Dec 19, 2012 at 13:42
@slhck Try using names(reducedData)[-1] <- do.call(function(...) paste(...,sep="."),expand.grid(letters[24:25],names(summaryList))) afterwards. — James
– James, Commented Dec 19, 2012 at 13:49

Roland · Accepted Answer · 2012-12-19 14:54:56Z

4

I am still somewhat confused. However, I guess you simply want to melt and dcast.

library(reshape2)
d <- melt(allData,id.vars=c("user_id","video_id"), measure.vars=c("x","y"))
dcast(d,video_id~user_id+variable,value.var="value",fun.aggregate=mean)

Resulting in:

 video_id  1_x 1_y  2_x 2_y  3_x 3_y  4_x 4_y  5_x 5_y  6_x 6_y  7_x 7_y  8_x 8_y  9_x 9_y 10_x 10_y 11_x 11_y 12_x 12_y 14_x 14_y 15_x 15_y 16_x 16_y
1         0   39 108  899 132   61 357  149 298 1105 415  148 208  442 200  210 134   58 244  910  403  152   52 1092  617 1012  114 1105  424  548  394
2         1 1125  70  128 165 1151 390  171 587  623 623   80 643  866 310  994 114  854 129  781  306  672   -1 1096  354  525  524  150

edited Dec 19, 2012 at 14:54

answered Dec 19, 2012 at 13:57

Roland

134k12 gold badges203 silver badges305 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

slhck Over a year ago

I had to get rid of a few exta columns, see the edit to your post, but this works as well, thanks! I'm somewhat unexperienced with reshape, so could you perhaps explain what dcast does here or why it creates the columns in the first place?

Roland Over a year ago

dcast simply transforms from long format (most used with statistical software) to wide format (loved by Excel folks). The formula defines which variables become column headers. meltis the inverse transformation.

slhck · Accepted Answer · 2012-12-19 14:01:30Z

3

Reduce does the trick:

reducedData <- Reduce(function(x,y) merge(x,y,by="video_id"),summaryList)

… but you need to fix the names afterwards:

names(reducedData)[-1] <- do.call(function(...) paste(...,sep="."),expand.grid(letters[24:25],names(summaryList)))

The result is:

   video_id  x.1 y.1  x.2 y.2  x.3 y.3  x.4 y.4  x.5 y.5  x.6 y.6  x.7 y.7  x.8
1         0   39 108  899 132   61 357  149 298 1105 415  148 208  442 200  210
2         1 1125  70  128 165 1151 390  171 587  623 623   80 643  866 310  994

edited Dec 19, 2012 at 14:01

slhck

39.6k33 gold badges163 silver badges220 bronze badges

answered Dec 19, 2012 at 13:59

James

67.1k14 gold badges158 silver badges200 bronze badges

Collectives™ on Stack Overflow

Merge data frames from a list with each other

What I need:

What I've tried:

Where I'm stuck:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I need:

What I've tried:

Where I'm stuck:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related