Merge two dataframes: specifically merge a selection of columns based on two conditions?

user_15573331 0
Viewed 61

I have two datasets on the same 2 patients. With the second dataset I want to add new information to the first, but I can't seem to get the code right.

My first (incomplete) dataset has a patient ID, measurement time (either T0 or FU1), year of birth, date of the CT scan, and two outcomes (legs_mass and total_mass):

library(tidyverse)
library(dplyr)
library(magrittr)
library(lubridate)

df1 <- structure(list(ID = c(115, 115, 370, 370), time = structure(c(1L, 
6L, 1L, 6L), .Label = c("T0", "T1M0", "T1M6", "T1M12", "T2M0", 
"FU1"), class = "factor"), year_of_birth = c(1970, 1970, 1961, 
1961), date_ct = structure(c(16651, 17842, 16651, 18535), class = "Date"), 
    legs_mass = c(9.1, NA, NA, NA), total_mass = c(14.5, NA, 
    NA, NA)), row.names = c(NA, -4L), class = c("tbl_df", "tbl", 
"data.frame"))

# Which gives the following dataframe
df1

# A tibble: 4 x 6
     ID time  year_of_birth date_ct    legs_mass total_mass
  <dbl> <fct>         <dbl> <date>         <dbl>      <dbl>
1   115 T0             1970 2015-08-04       9.1       14.5
2   115 FU1            1970 2018-11-07      NA         NA  
3   370 T0             1961 2015-08-04      NA         NA  
4   370 FU1            1961 2020-09-30      NA         NA

The second dataset adds to the legs_mass and total_mass columns:

df2 <- structure(list(ID = c(115, 370), date_ct = structure(c(17842, 
18535), class = "Date"), ctscan_label = c("PXE115_CT_20181107_xxxxx-3.tif", 
"PXE370_CT_20200930_xxxxx-403.tif"), legs_mass = c(956.1, 21.3
), total_mass = c(1015.9, 21.3)), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))

# Which gives the following dataframe:
df2

# A tibble: 2 x 5
     ID date_ct    ctscan_label                     legs_mass total_mass
  <dbl> <date>     <chr>                                <dbl>      <dbl>
1   115 2018-11-07 PXE115_CT_20181107_xxxxx-3.tif       956.      1016. 
2   370 2020-09-30 PXE370_CT_20200930_xxxxx-403.tif      21.3       21.3

What I am trying to do, is...

  1. Add the legs_mass and total_mass column values from df2 to df1, based on ID number and date_ct.
  2. Add the new columns of df2 (the one that is not in df1; ctscan_label) to df1, also based on the date of the ct and patient ID. So that the final dataset df3 looks as follows:
df3 <- structure(list(ID = c(115, 115, 370, 370), time = structure(c(1L, 
6L, 1L, 6L), .Label = c("T0", "T1M0", "T1M6", "T1M12", "T2M0", 
"FU1"), class = "factor"), year_of_birth = c(1970, 1970, 1961, 
1961), date_ct = structure(c(16651, 17842, 16651, 18535), class = "Date"), 
    legs_mass = c(9.1, 956.1, NA, 21.3), total_mass = c(14.5, 
    1015.9, NA, 21.3)), row.names = c(NA, -4L), class = c("tbl_df", 
"tbl", "data.frame"))

# Corresponding to the following tibble:
# A tibble: 4 x 6
     ID time  year_of_birth date_ct    legs_mass total_mass
  <dbl> <fct>         <dbl> <date>         <dbl>      <dbl>
1   115 T0             1970 2015-08-04       9.1       14.5
2   115 FU1            1970 2018-11-07     956.      1016. 
3   370 T0             1961 2015-08-04      NA         NA  
4   370 FU1            1961 2020-09-30      21.3       21.3

I have tried the merge function and rbind from baseR, and bind_rows from dplyr but can't seem to get it right.

Any help?

rbind dplyr r merge
2 Answers

You can join the two datasets and use coalesce to keep one non-NA value from the two datasets.

library(dplyr)

left_join(df1, df2, by = c("ID", "date_ct")) %>%
  mutate(leg_mass = coalesce(legs_mass.x , legs_mass.y), 
         total_mass = coalesce(total_mass.x, total_mass.y)) %>%
  select(-matches('\\.x|\\.y'), -ctscan_label)

#     ID time  year_of_birth date_ct    leg_mass total_mass
#  <dbl> <fct>         <dbl> <date>        <dbl>      <dbl>
#1   115 T0             1970 2015-08-04      9.1       14.5
#2   115 FU1            1970 2018-11-07    956.      1016. 
#3   370 T0             1961 2015-08-04     NA         NA  
#4   370 FU1            1961 2020-09-30     21.3       21.3
user_39629140

We can use data.table methods

library(data.table)
setDT(df1)[setDT(df2), c("legs_mass", "total_mass") := 
   .(fcoalesce(legs_mass, i.legs_mass), 
     fcoalesce(total_mass, i.total_mass)), on = .(ID, date_ct)]

-output

df1
    ID time year_of_birth    date_ct legs_mass total_mass
1: 115   T0          1970 2015-08-04       9.1       14.5
2: 115  FU1          1970 2018-11-07     956.1     1015.9
3: 370   T0          1961 2015-08-04        NA         NA
4: 370  FU1          1961 2020-09-30      21.3       21.3
user_37322710
Related