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

Question

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?

akrun · Accepted Answer · 2017-08-15 07:59:32Z

4

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

answered Aug 15, 2017 at 7:59

akrun

891k38 gold badges590 silver badges700 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related