Unify column names across datasets

user_5205394 0
Viewed 53

I have several datasets, whose number and/or name of columns may vary (or not). I'd like to produce a single dataset with harmonized column names.

Let's take the following example:

df1 <- tibble::tribble(
          ~v1a, ~v2,  ~v3,  ~v4,  ~v5,
           "A",   4, "Z1", "a1", "ti",
           "B",   3, "Y2", "b2", "tu",
           "C",   2, "X3", "c3", "to",
           "D",   1, "W4", "d4", "ta"
          )
df2 <- tibble::tribble(
      ~v1a, ~v2,  ~v3,  ~v4,
       "D",   1, "W4", "d4",
       "C",   2, "X3", "c3",
       "B",   3, "Y2", "b2",
       "A",   4, "Z1", "a1"
      )
df3 <- tibble::tribble(
      ~V1, ~V2,  ~V4,
      "A",   4, "a1",
      "B",   3, "b2",
      "C",   2, "c3",
      "D",   1, "d4"
      )
df4 <- tibble::tribble(
      ~V1a, ~V2a, ~V3a, ~V4a,
       "A",    4, "Z1", "a1",
       "B",    3, "Y2", "b2",
       "C",    2, "X3", "c3",
       "D",    1, "W4", "d4"
      )

If I do bind_rows(df1, df2, df3, df4), I get a dataset with 12 variables, although I would like one with only 5, as follows:

expected_df <- tibble::tribble(
  ~var1, ~var2,  ~var3,  ~var4,  ~var5,
   "A",  4L, "Z1", "a1", "ti",
   "B",  3L, "Y2", "b2", "tu",
   "C",  2L, "X3", "c3", "to",
   "D",  1L, "W4", "d4", "ta",
   "D",  1L, "W4", "d4",   NA,
   "C",  2L, "X3", "c3",   NA,
   "B",  3L, "Y2", "b2",   NA,
   "A",  4L, "Z1", "a1",   NA,
   "A",  4L,   NA, "a1",   NA,
   "B",  3L,   NA, "b2",   NA,
   "C",  2L,   NA, "c3",   NA,
   "D",  1L,   NA, "d4",   NA,
   "A",  4L, "Z1", "a1",   NA,
   "B",  3L, "Y2", "b2",   NA,
   "C",  2L, "X3", "c3",   NA,
   "D",  1L, "W4", "d4",   NA
  )

How could I achieve this?

I think a potential solution start would be to create a sort of correspondance table with 'old' and 'new' column names:

col_names <- tibble::tribble(
   ~old,   ~new,
  "v1a", "var1",
   "v2", "var2",
   "v3", "var3",
   "v4", "var4",
   "v5", "var5",
   "V1", "var1",
   "V2", "var2",
   "V4", "var4",
  "V1a", "var1",
  "V2a", "var2",
  "V3a", "var3",
  "V4a", "var4"
  )

And to then conditionally rename the various datasets' column names, but I have no clue re. how to do this... Do you have any idea?

Thanks a lot!

tidyverse r
2 Answers

Get the data in a list, use match to change the column names and map_df to combine them in one dataframe.

library(dplyr)
library(purrr)

map_df(mget(paste0('df', 1:4)), 
      ~.x %>% rename_with(~col_names$new[match(., col_names$old)]))

#   var1   var2 var3  var4  var5 
#   <chr> <dbl> <chr> <chr> <chr>
# 1 A         4 Z1    a1    ti   
# 2 B         3 Y2    b2    tu   
# 3 C         2 X3    c3    to   
# 4 D         1 W4    d4    ta   
# 5 D         1 W4    d4    NA   
# 6 C         2 X3    c3    NA   
# 7 B         3 Y2    b2    NA   
# 8 A         4 Z1    a1    NA   
# 9 A         4 NA    a1    NA   
#10 B         3 NA    b2    NA   
#11 C         2 NA    c3    NA   
#12 D         1 NA    d4    NA   
#13 A         4 Z1    a1    NA   
#14 B         3 Y2    b2    NA   
#15 C         2 X3    c3    NA   
#16 D         1 W4    d4    NA
user_39629140

We may use rename as well

library(dplyr)
library(purrr)
library(tibble)
mget(ls(pattern = '^df\\d+$')) %>% 
    map_dfr(~ .x %>%
        rename(!!! col_names %>%
                      select(2:1) %>% 
                      filter(old %in% names(.x)) %>%
                     deframe))
# A tibble: 16 x 5
   var1   var2 var3  var4  var5 
   <chr> <dbl> <chr> <chr> <chr>
 1 A         4 Z1    a1    ti   
 2 B         3 Y2    b2    tu   
 3 C         2 X3    c3    to   
 4 D         1 W4    d4    ta   
 5 D         1 W4    d4    <NA> 
 6 C         2 X3    c3    <NA> 
 7 B         3 Y2    b2    <NA> 
 8 A         4 Z1    a1    <NA> 
 9 A         4 <NA>  a1    <NA> 
10 B         3 <NA>  b2    <NA> 
11 C         2 <NA>  c3    <NA> 
12 D         1 <NA>  d4    <NA> 
13 A         4 Z1    a1    <NA> 
14 B         3 Y2    b2    <NA> 
15 C         2 X3    c3    <NA> 
16 D         1 W4    d4    <NA>
user_37322710
Related