How to add one value to all values in a column with the same id

user_19689445 0
Viewed 80

I am quite new to R and never worked with any bigger data. For the examples i reduced the two dataframes:

  • df1
id val1 val2
11 1 2
11 2 5
22 2 2
22 4 6
... ... ...
  • df2
id val1 val2
11 5 3
22 6 5
... ... ...

I am looking for a way to add the values of df2 to each value in df1 with the same id.

So the result should be something like this:

id val1 val2
11 6 5
11 7 8
22 8 7
22 10 11
... ... ...

Because the original data is over 3000 observations of 47 variables with 8 different id I am looking for a solution where the values are not added one by one.

#reproducible data
df1 <- read.table(text = "id val1 val2
  11 1   2   
  11 2   5   
  22 2   2   
  22 4   6", header = TRUE)
df2 <- read.table(text = "id val1 val2
  11 5   3   
  22 6   5", header = TRUE)
dataframe r
4 Answers

Merge the datasets then add columns:

# merge
res <- merge(df1, df2, by = "id")
# then add
cbind(res[ 1 ], res[, 2:3] + res[, 4:5])
#   id val1.x val2.x
# 1 11      6      5
# 2 11      7      8
# 3 22      8      7
# 4 22     10     11
user_6800680

You could use powerjoin to handle conflicted columns when joining.

library(powerjoin)

power_left_join(df1, df2, by = "id", conflict = `+`)

#   id val1 val2
# 1 11    6    5
# 2 11    7    8
# 3 22    8    7
# 4 22   10   11
user_100689850

One approach is to merge both datasets by the id variable, then additioning corresponding columns to create the new val1 and val2 variables, as suggested in the comments by @zx8754. Using dplyr you can obtain the output with :

library(dplyr)
merge(df1,df2,by="id") %>% 
  mutate(val1=val1.x+val1.y,val2=val2.x+val2.y,)%>%
  select(id,val1,val2)
user_153682350

Another approach - row bind then index by the table when summing

library(tidyverse)   
imap(list(df1,df2), ~
        mutate(.x, table = .y)) %>%
        bind_rows() %>%
        group_by(id) %>%
        summarise(across(matches("val"), ~ .[table == 1] + .[table == 2]),.groups = "drop")
user_94240880
Related