Binding dataframes with different column names by row

Question

I imported this excel sheet as a list of dataframes. I want to merge the list into one dataframe. bind_rows() allow me to easily add together the dataframes, but the issue is that I have a variable/column that has different names in each dataframe. bind_row() will by default create two separate columns, with empty values for the data from the other data frames. How can I join these columns?

Sample code:

# Sample dataframes
df1 <- tibble(A = c(1,2,3),
              B = c("X","Y","Z"),
              C = c(T,F,F)
                  )
df2 <- tibble(A = c(3,4,5),
              B = c("U","V","W"),
              D = c(T,T,F)
)

# List of dataframes
my_ls <- list(df1, df2)
my_ls
[[1]]
# A tibble: 3 x 3
      A B     C    
    
1     1 X     TRUE 
2     2 Y     FALSE
3     3 Z     FALSE

[[2]]
# A tibble: 3 x 3
      A B     D    
    
1     3 U     TRUE 
2     4 V     TRUE 
3     5 W     FALSE

# Creating joined dataframe:
my_df <- bind_rows(my_ls)
my_df
# Current outcome: A tibble: 6 x 4
      A B     C     D    
     
1     1 X     TRUE  NA   
2     2 Y     FALSE NA   
3     3 Z     FALSE NA   
4     3 U     NA    TRUE 
5     4 V     NA    TRUE 
6     5 W     NA    FALSE

The desired outcome:

# Desired outcome: A tibble: 6 x 3
      A B     C         
     
1     1 X     TRUE    
2     2 Y     FALSE    
3     3 Z     FALSE    
4     3 U     TRUE 
5     4 V     TRUE 
6     5 W     FALSE

Currently, I've been using mutate() with case_when(), where I check which column is not empty (!is.na()). This works, but I can't help but think there must be an easier way.

# Example using mutate
my_df <- my_df %>% 
  mutate(
    C = case_when(is.na(C)  & !is.na(D) ~ D,
                  !is.na(C) & is.na(D)  ~ C,
                  # The lines below may be a bit redundant for my purpose, since the dataframes either have the C or D variable.
                  !is.na(C) & !is.na(D) ~ C, # Better would be to return that variable has overlapping information
                  is.na(C)  & is.na(D)  ~ NA
                 )
         ) %>%
        select(-D)
my_df
# A tibble: 6 x 3
      A B     C    
    
1     1 X     TRUE 
2     2 Y     FALSE
3     3 Z     FALSE
4     3 U     TRUE 
5     4 V     TRUE 
6     5 W     FALSE

user_3962914 · Answer

You can bind_rows and then select non-NA value using coalesce :

library(dplyr)

bind_rows(my_ls) %>% mutate(C = coalesce(C, D)) %>% select(A:C)

#      A  B     C    
#    
#1     1 X     TRUE 
#2     2 Y     FALSE
#3     3 Z     FALSE
#4     3 U     TRUE 
#5     4 V     TRUE 
#6     5 W     FALSE

user_6503141 · Answer

Apologize for breaking out of the tidyverse for a quick answer

expl <- read.table(text= " A B     C     D    
1     1 X     TRUE  NA   
2     2 Y     FALSE NA   
3     3 Z     FALSE NA   
4     3 U     NA    TRUE 
5     4 V     NA    TRUE 
6     5 W     NA    FALSE")

expl$E <- ifelse(is.na(expl$C), expl$D, expl$C)

print(expl)

or maybe

expl[,c("C", "D")] %>% rowMeans(na.rm = TRUE) %>% as.logical()

EDIT: Translated the latter to tidy:

expl %>% select("C", "D") %>% rowMeans(na.rm = TRUE) %>% as.logical()

EDIT after first comment:

If you want more control you should probably write the things you want to do in each case in a function similar to the following example:

library(magrittr)

expl <- read.table(text= " A B     C     D    
1     1 X     TRUE  NA   
2     2 Y     FALSE NA   
3     3 Z     FALSE NA   
4     3 U     NA    TRUE 
5     4 V     NA    TRUE 
6     5 W     NA    FALSE
7     7 I     NA    NA
8     9 J     TRUE  TRUE")

myfun <- function(a, b){
  if(is.na(a) & is.na(b)) 
     return(NA)
  if(!is.na(a) & !is.na(b)) {
    warning("too much information, a and b set!")
    return(NaN)
  }
  return(max(a, b, na.rm=TRUE))
}

myfun = Vectorize(myfun)

myfun(expl$C, expl$D) %>% as.logical()

user_12993861 · Answer

Following the comment by @KarthikS you can rename your columns before binding. My approach using rename_with does not require the columns to be in a specific order. To illusrate this I used somewhat different example dataframes:

library(purrr)
library(dplyr)

d1 <- data.frame(A = 1, B = 2, C = 3)
d2 <- data.frame(A = 4, B = 5, D = 6)
d3 <- data.frame(D = 7, A = 8, B = 9)

d <- list(d1, d2, d3)

map(d, ~ rename_with(.x, ~ "C", matches("^D$"))) %>% 
  bind_rows()
#>   A B C
#> 1 1 2 3
#> 2 4 5 6
#> 3 8 9 7

And now four your dataset:

d <- list(df1, df2)
map(d, ~ rename_with(.x, ~ "C", matches("^D$"))) %>% 
  bind_rows()
#> # A tibble: 6 x 3
#>       A B     C    
#>     
#> 1     1 X     TRUE 
#> 2     2 Y     FALSE
#> 3     3 Z     FALSE
#> 4     3 U     TRUE 
#> 5     4 V     TRUE 
#> 6     5 W     FALSE

And if we add an addtional one with a different order:

df3 <- tibble(D = c(T,T,F),
              A = c(7,8,9),
              B = c("A","B","C"))

d <- list(df1, df2, df3)
map(d, ~ rename_with(.x, ~ "C", matches("^D$"))) %>% 
  bind_rows()
#> # A tibble: 9 x 3
#>       A B     C    
#>     
#> 1     1 X     TRUE 
#> 2     2 Y     FALSE
#> 3     3 Z     FALSE
#> 4     3 U     TRUE 
#> 5     4 V     TRUE 
#> 6     5 W     FALSE
#> 7     7 A     TRUE 
#> 8     8 B     TRUE 
#> 9     9 C     FALSE

^{Created on 2020-10-16 by the reprex package (v0.3.0)}

Binding dataframes with different column names by row

3 Answers