data.frame Group By column [duplicate]

Question

I have a data frame DF.

Say DF is:

Now I want to combine together the rows by the column A and to have the sum of the column B.

For example:

I am doing this currently using an SQL query with the sqldf function. But for some reason it is very slow. Is there any more convenient way to do that? I could do it manually too using a for loop but it is again slow. My SQL query is " Select A,Count(B) from DF group by A".

In general whenever I don't use vectorized operations and I use for loops the performance is extremely slow even for single procedures.

Jim G. · Accepted Answer · 2016-10-03 11:20:11Z

106

This is a common question. In base, the option you're looking for is aggregate. Assuming your data.frame is called "mydf", you can use the following.

> aggregate(B ~ A, mydf, sum)
  A  B
1 1  5
2 2  3
3 3 11

I would also recommend looking into the "data.table" package.

> library(data.table)
> DT <- data.table(mydf)
> DT[, sum(B), by = A]
   A V1
1: 1  5
2: 2  3
3: 3 11

edited Oct 3, 2016 at 11:20

Jim G.

15.4k23 gold badges109 silver badges183 bronze badges

answered Sep 14, 2013 at 8:39

A5C1D2H2I1M1N2O1R2T1

194k31 gold badges417 silver badges497 bronze badges

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

1 Comment

Josiah Yoder Over a year ago

Note that ~ is a "formula" and produces a "formula object"

mpalanco · Accepted Answer · 2015-06-24 11:48:02Z

27

Using dplyr:

require(dplyr)    
df <- data.frame(A = c(1, 1, 2, 3, 3), B = c(2, 3, 3, 5, 6))
df %>% group_by(A) %>% summarise(B = sum(B))

## Source: local data frame [3 x 2]
## 
##   A  B
## 1 1  5
## 2 2  3
## 3 3 11

With sqldf:

library(sqldf)
sqldf('SELECT A, SUM(B) AS B FROM df GROUP BY A')

edited Jun 24, 2015 at 11:48

answered Jan 31, 2015 at 19:53

mpalanco

13.7k3 gold badges66 silver badges71 bronze badges

Comments

r0bert · Accepted Answer · 2013-09-14 09:38:40Z

9

I would recommend having a look at the plyr package. It might not be as fast as data.table or other packages, but it is quite instructive, especially when starting with R and having to do some data manipulation.

> DF <- data.frame(A = c("1", "1", "2", "3", "3"), B = c(2, 3, 3, 5, 6))
> library(plyr)
> DF.sum <- ddply(DF, c("A"), summarize, B = sum(B))
> DF.sum
  A  B
1 1  5
2 2  3
3 3 11

answered Sep 14, 2013 at 9:38

r0bert

1913 bronze badges

Comments

David Arenburg · Accepted Answer · 2016-05-27 08:22:42Z

4

require(reshape2)

T <- melt(df, id = c("A"))

T <- dcast(T, A ~ variable, sum)

I am not certain the exact advantages over aggregate.

edited May 27, 2016 at 8:22

David Arenburg

92.4k18 gold badges145 silver badges202 bronze badges

answered Jul 31, 2015 at 0:40

Soc

411 bronze badge

Collectives™ on Stack Overflow

data.frame Group By column [duplicate]

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Related