CODEWITHSUNDEEP
rdataframe
shekharamit
shekharamit

Reputation: 23

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

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.

Upvotes: 0

Views: 1809

Answers (3)

www
www

Reputation: 39154

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)

Upvotes: 1

user7571182
user7571182

Reputation:

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))

Upvotes: 0

xraynaud
xraynaud

Reputation: 2126

This should work: 

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

Upvotes: 0

Related Questions