R - Is there a way to join two dataframes by actually merging same-named columns?

user_16382661 0
Viewed 89

enter image description here

enter image description here

enter image description here

It is generally not possible, that a -1 and 1 are at the same spot. Therefore, this is not a problem that should bother. Also, both dataframes have the same number of columns and observations. The final product should not have .x or .y files, like Var1.x and Var.y. My attempts to solve this with the join function all failed, because it always only merges those two dataframes instead of joining columns together. I might have to add that the number of columns and observations is very high. Therefore, taking the column names into account in the code does not solve my problem.

Thank you guys.

r merge join
4 Answers

Not at all elegant, but it may work fine

A[is.na(A)]<- 0
B[is.na(B)]<- 0

C = A[,-1] + B[,-1]

C[] <- sign(C)

C[C==0] <- NA

C = data.frame(ID = A[,1], C)
user_120202000

These two simple dataframes can be used as an example:

library(tidyverse)

df1 <- tibble(
  id = 1:5,
  var1 = c(1, 2, NA, 4, NA),
  var2 = c(1, 2, NA, NA, NA)
)

df2 <- tibble(
  id = 1:5,
  var1 = c(NA, NA, 3, NA, 5),
  var2 = c(NA, NA, 3, 4, 5)
)

We can now join them using inner_join and then pivot them to a longer format.

(df_joined_long <- inner_join(df1, df2, by = "id") %>% 
  pivot_longer(-id, names_sep = "\\.", names_to = c("var", ".value")))
#> # A tibble: 10 x 4
#>       id var       x     y
#>    <int> <chr> <dbl> <dbl>
#>  1     1 var1      1    NA
#>  2     1 var2      1    NA
#>  3     2 var1      2    NA
#>  4     2 var2      2    NA
#>  5     3 var1     NA     3
#>  6     3 var2     NA     3
#>  7     4 var1      4    NA
#>  8     4 var2     NA     4
#>  9     5 var1     NA     5
#> 10     5 var2     NA     5

In this form we now only have to deal with two columns. We want to replace missing values from x with non-missing values from y and vice-versa. This can be dones using coalesece. After doing that, we simply pivot back to the wide format.

df_joined_long %>% 
  mutate(val = coalesce(x, y), .keep = "unused") %>% 
  pivot_wider(names_from = var, values_from = val)
#> # A tibble: 5 x 3
#>      id  var1  var2
#>   <int> <dbl> <dbl>
#> 1     1     1     1
#> 2     2     2     2
#> 3     3     3     3
#> 4     4     4     4
#> 5     5     5     5

Created on 2021-07-14 by the reprex package (v1.0.0)

user_77787870

The data.frame's could be subsetted with the positions where the NA's are located e.g. in A and overwrite with that what is in B at this positions.

i <- is.na(A)
A[i] <- B[i]
#C <- `[<-`(A, i, value=B[i]) #In case A should not be changed

A
#  id var1 var2
#1  1    1   -1
#2  2   -1    1
#3  3    1   NA
#4  4   NA   -1
#5  5   -1    1

Data:

A <- data.frame(
  id = 1:5,
  var1 = c(1, -1, NA, NA, NA),
  var2 = c(-1, 1, NA, NA, NA)
)

B <- data.frame(
  id = 1:5,
  var1 = c(NA, NA, 1, NA, -1),
  var2 = c(NA, NA, NA, -1, 1)
)
user_104885040

Using this data:

df.A <- data.frame(ID = 1:5,
                   Var1 = NA,
                   Var2 = c(NA, 1, NA, NA, 1),
                   Var3 = c(1, NA, NA, 1, 1))

df.B <- data.frame(ID = 1:5,
                   Var1 = c(1, 1, NA, NA, NA),
                   Var2 = c(-1, NA, 1, NA, NA),
                   Var3 = NA)

First we inner_join both columns like so:

library(dplyr)
df <- df.A %>%
  inner_join(df.B, by="ID")

Then we can start a for loop based on column names (does not require string matches so any column name works as long as they match in A and B):

for (i in 2:ncol(df.B)) {
  col <- names(df.B[i])
  df <- df %>%
    mutate("{col}" := coalesce(!!as.name(paste0(col, ".x")), !!as.name(paste0(col, ".y"))))
}

Lastly we remove the columns we don't need:

df %>% select(!contains(c(".x", ".y")))
user_94228710
Related