CODEWITHSUNDEEP
r
Neil
Neil

Reputation: 8247

How to sum values of matching columns while merging two dataframes in r

I have two dataframes in r

ship_no    bay_1   bay_2    bay_3    bay_5     bay_6
 ABC        0       10       15       20        30
 DEF        10      20       0        25        10
 ERT        0       10       0        10         0


ship_no    bay_1   bay_2    bay_7    bay_5     bay_6
 ABC        10      10       10       0         0
 DEF        10      10       0        15        10
 ERT        0       0        0        10        0

I want to add columns values while merging above two dataframes on column key ship_no

My desired dataframe would be

ship_no    bay_1   bay_2    bay_3    bay_5     bay_6     bay_7
  ABC        10      20       15       20        30       10
  DEF        20      30       0        40        20        0
  ERT        0       10       0        20        0         0

How can I do it in r?

Upvotes: 3

Views: 1653

Answers (1)

akrun
akrun

Reputation: 887098

We can place the datasets in a list, use rbindlist to rbind the datasets, grouped by 'ship_no', get the sum of other columns

library(data.table)
rbindlist(list(df1, df2), fill = TRUE)[,lapply(.SD, sum, na.rm = TRUE) , ship_no]
#   ship_no bay_1 bay_2 bay_3 bay_5 bay_6 bay_7
#1:     ABC    10    20    15    20    30    10
#2:     DEF    20    30     0    40    20     0
#3:     ERT     0    10     0    20     0     0

Another option would be dplyr

library(dplyr)
bind_rows(df1, df2) %>%
            group_by(ship_no) %>%
            summarise_all(funs(sum(., na.rm = TRUE)))
# A tibble: 3 x 7
#  ship_no bay_1 bay_2 bay_3 bay_5 bay_6 bay_7
#    <chr> <int> <int> <int> <int> <int> <int>
#1     ABC    10    20    15    20    30    10
#2     DEF    20    30     0    40    20     0
#3     ERT     0    10     0    20     0     0

Upvotes: 4

Related Questions