R: Sum column wise value of two/more data frames having same variables (column names) and take Date column as reference

0
votes

I want to sum two data frames having similar columns and take Date column as a reference. Eg:

df1: 

      Date  V1  V2  V3  
2017/01/01   2   4   5   
2017/02/01   3   5   7

df2: 

      Date  V1  V2  V3  
2017/01/01   1   3   6  
2017/02/01   5   7   7

I want the result as:

df3: 

      Date  V1  V2  V3  
2017/01/01   3   7  11  
2017/02/01   8  12  14

When I try to add df1 and df2, it gives error as Dates cannot be joined. Merge is also not useful here as this is about summing the values of similar data frames.

3
rdataframe

3 Answers

0
votes

You can consider the following base R approach.

df3 <- cbind(df1[1], df1[-1] + df2[-1])
df3
        Date V1 V2 V3
1 2017/01/01  3  7 11
2 2017/02/01  8 12 14

Or the dplyr approach.

library(dplyr)
df3 <- bind_rows(df1, df2) %>%
  group_by(Date) %>%
  summarise_all(funs(sum))
df3
        Date    V1    V2    V3
       <chr> <int> <int> <int>
1 2017/01/01     3     7    11
2 2017/02/01     8    12    14

Or the data.table approach.

library(data.table)
df_bind <- rbindlist(list(df1, df2))
df3 <- df_bind[, lapply(.SD, sum), by = Date]
df3
         Date V1 V2 V3
1: 2017/01/01  3  7 11
2: 2017/02/01  8 12 14

Data:

df1 <- read.table(text = "Date    V1    V2    V3  
'2017/01/01' 2    4    5   
'2017/02/01' 3    5    7",
                  header = TRUE, stringsAsFactors = FALSE)

df2 <- read.table(text = "Date    V1    V2    V3  
'2017/01/01'    1    3     6  
                  '2017/02/01'    5    7     7",
                  header = TRUE, stringsAsFactors = FALSE)
0
votes

This should work: 

df = rbind(df1,df2)
aggregate(df[,2:4],by=list(date = df$Date),sum)
0
votes

You could do something like this:

pp <- cbind(names=c(rownames(df1), rownames(df2)), 
                    rbind.fill(list(df1, df2)))

Then, you could aggregate with plyr's ddply as follows:

ddply(pp, .(names), function(x) colSums(x[,-1], na.rm = TRUE))