Sum of elements based on unique names across a list(unknown length) of data frames

Question

I am trying to get the sum of elements based on unique names across a list containing unknown number of dataframes.

## Test Data
Name1 <- c("A","B","C","D")
Name2 <- c("A","D")
Name3 <- c("B","C","F")
Values1 <- c(1,2,3,4)
Values2 <- c(5,7)
Values3 <- c(6,8,9)
DF1 <- data.frame(Name1,Values1,stringsAsFactors = FALSE)
DF2 <- data.frame(Name2,Values2,stringsAsFactors = FALSE)
DF3 <- data.frame(Name3,Values3,stringsAsFactors = FALSE)

DFList <- list(DF1,DF2,DF3)

My Output will be:

A  B  C  D  F 
6  8 11 11  9

I am not sure if using a loop is effective, since there can be any number of dataframes in the list and the number of unique rows in a dataframe can range anywhere between 100,000 to 1 Million.

score 4 · Accepted Answer · answered Feb 08 '18 at 20:19

4

Solution using data.table::rbindlist:

data.table::rbindlist(DFList)[, sum(Values1), Name1]

   Name1 V1
1:     A  6
2:     B  8
3:     C 11
4:     D 11
5:     F  9

rbindlist binds columns despite their names and then you can sum(Values1) by Name1.

answered Feb 08 '18 at 20:19

pogibas

27,303
19
84
117

1

now you are faster ;-) – Jaap Feb 08 '18 at 20:20
1

Sweet and short. Thanks!! – Jason Mathews Feb 08 '18 at 20:20
2

you could also use `use.names = FALSE` like: `data.table::rbindlist(DFList, use.names = FALSE)[, sum(Values), by = Name]` – Jaap Feb 08 '18 at 20:21
Awesome solution – Onyambu Feb 08 '18 at 20:27

d.b · Answer 2 · 2018-02-08T20:30:07.167

3

sapply(split(unlist(lapply(DFList, "[[", 2)), unlist(lapply(DFList, "[[", 1))), sum)
# A  B  C  D  F 
# 6  8 11 11  9

OR

aggregate(formula = Value~Name,
          data = do.call(rbind, lapply(DFList, function(x) setNames(x, c("Name", "Value")))),
          FUN = sum)
#  Name Value
#1    A     6
#2    B     8
#3    C    11
#4    D    11
#5    F     9

edited Feb 08 '18 at 20:30

answered Feb 08 '18 at 20:16

d.b

32,245
6
36
77

score 2 · Answer 3 · answered Feb 08 '18 at 20:20

2

Sum of elements based on unique names across a list(unknown length) of data frames

3 Answers3