Collapse rows in a data frame using R

Question

I have a data frame in R defined as follows:

data frame:

col 1 col 2 col 3 col4
200    AIG   8.5   12
800    AIG   8.1   20.1
500    A1B   20   50.5
800    A1B   12   30
120    A2M   1.6   8.5

dat <- structure(list(col1 = c(200, 800, 500, 800, 120), col2 = structure(c(3L, 
    3L, 1L, 1L, 2L), .Label = c("A1B", "A2M", "AIG"), class = "factor"), 
        col3 = c(8.5, 8.1, 20, 12, 1.6), col4 = c(12, 20.1, 50.5, 
        30, 8.5)), .Names = c("col1", "col2", "col3", "col4"), row.names = c(NA, 
    -5L), class = "data.frame")

Then I'd like to collapse the rows by id (in this case the unique ids are A1G, A1B, A2M).
Col 1, I'd like to collapse it by adding the rows with the same id.
Col 2, I'd like to collapse it to each unique id
Col 3, I'd like to collapse it as follows, take col1*col3, add them, and then divide them by the sum of col1.
I.e., the A1G new row value should be (8.5*20+8.1*80)/(80+20). Aka the weighted average of column 3 weighted by the values of col1.
Col 4, I'd like to take the maximum value.

The resulting data frame should look like:

column 1 column 2 column 3 column 4
800+200=1000    AIG   (8.5*200+8.1*800)/1000=8.18   max(12,20.1)=20.1
800+500=1300    AIB   (20*800+12*500)/1300=16.9   max(50.5, 30)=50.5
120    A2M   1.6   8.5

Any suggestions?

what an interesting way to present your data.frame. I hope you didn't do all those keyboard tags by hand! — GSee, Oct 14 '12 at 17:36
@GSee, hahaha, no just copied them from http://meta.stackexchange.com/questions/73566/is-there-any-markdown-to-create-tables — Dnaiel, Oct 14 '12 at 17:39
In the future, you can just use `dput` (which is how Tyler got the code he edited into your question). See the classic "[how to make a great R reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)" if you haven't already. — GSee, Oct 14 '12 at 18:38

GSee · Accepted Answer · 2012-10-14T17:54:26.327

20

Here is a data.table solution that will scale well for big data (speed and memory efficient)

library(data.table)
DT <- data.table(dat, key="col2")
DT[, list(col1=sum(col1), 
          col3=sum(col1 * col3) / sum(col1), 
          col4=max(col4)), by=col2]
#   col2 col1     col3 col4
#1:  A1B 1300 15.07692 50.5
#2:  A2M  120  1.60000  8.5
#3:  AIG 1000  8.18000 20.1

edited Oct 14 '12 at 17:54

answered Oct 14 '12 at 17:48

GSee

48,880
13
125
145

This is the fastest and simplest solution. I tried it with a large file and its much faster than plyr, or base packages – Dnaiel Oct 15 '12 at 00:14
1

Yes, `data.table` runs circles around `plyr` and even base R. See the [timings vignette](http://cran.r-project.org/web/packages/data.table/vignettes/datatable-timings.pdf). It also uses less memory. – GSee Oct 15 '12 at 00:18
1

do you really need to set the key if the by argument is used in the second data.table command? – Jakob Jul 22 '17 at 08:16

score 10 · Answer 2 · answered Oct 14 '12 at 18:00

10

A solution in base:

dat2<-do.call(rbind,
  by(dat,dat$col2, function(x) 
    with (x,
     data.frame(
       col1 = sum(col1),
       col3 = sum(col1 * col3) / sum(col1),
       col4 = max(col4)
     )
    )
  )
)
dat2$col2<-rownames(dat2)

#     col1     col3 col4 col2
# A1B 1300 15.07692 50.5  A1B
# A2M  120  1.60000  8.5  A2M
# AIG 1000  8.18000 20.1  AIG

answered Oct 14 '12 at 18:00

nograpes

18,623
1
44
67

1

Way better than my base solution +1 – Tyler Rinker Oct 14 '12 at 18:02

score 6 · Answer 3 · answered Oct 14 '12 at 17:41

Using the plyr package:

library(plyr)
ddply(df, "col2", summarize, col1 = sum(col1),
                             col3 = sum(col1 * col3) / sum(col1),
                             col4 = max(col4))
#   col2 col1     col3 col4
# 1  A1B 1300 15.07692 50.5
# 2  A2M  120  1.60000  8.5
# 3  AIG 1000  8.18000 20.1

score 1 · Answer 4 · answered Oct 14 '12 at 18:01

A base solution but I like the data.table solution:

dat[, 2] <- factor(dat[, 2], levels=unique(dat[, 2])) #in case not already ordered
L1 <- split(dat, dat$col2)                            #split into list by col2

funny <- function(x){                                 #function to calculate stuff
    x <- data.frame(x)
    c(col1=sum(x[,1]), col2=as.character(x[1, 2]), 
        col3=sum((x[, 3]*x[, 1]))/sum(x[, 1]),
        col4=max(x[,4]))
}

#apply function and wrap it up into dataframe
dat2 <- data.frame(do.call(rbind, lapply(L1, funny)), row.names=NULL) 
dat2[, -2] <- apply(dat2[, -2], 2, as.numeric)       #reapply classes    
dat2

#> dat2
#  col1 col2     col3 col4
#1 1000  AIG  8.18000 20.1
#2 1300  A1B 15.07692 50.5
#3  120  A2M  1.60000  8.5

Collapse rows in a data frame using R

4 Answers4