Matching values from multiple columns in 1 data frame to key in second data frame and creating columns

Question

I have 2 data frames. One (df1) looks like this:

 var.1 var.2 var.3 var.4
1     7     9     1     2
2     4     6     9     7
3     2     NA    NA    NA

And the other (df2) looks like this:

 var.a var.b var.c var.d
1     1     b     c     d
2     2     f     g     h
3     4     j     k     l
3     7     j     k     z
...

with all of the values listed out in var.1-var.4 in df1 in var.a of df2.

I want to match var.a from df2 across all of the columns listed in df1 and then add these columns to df1 with new/combined column names. So for instance it'll look like this:

 var.1 var1.b var1.c var1.d ... var.4 var4.b var4.c var4.d
1     7     j     k     z         2     f     g     h
2     4     j     k     l         7     j     k     z
3     2     f     g     h         NA    NA    NA    NA

Thanks in advance!

Lyngbakr Lyngbakr · Accepted Answer · 2019-05-30T18:47:47

Here's a tidyverse solution. First, I define the data frames.

df1 <- read.table(text = " var.1 var.2 var.3 var.4
                  1     7     9     1     2
                  2     4     6     9     7
                  3     2     NA    NA    NA", header = TRUE)


df2 <- read.table(text = "  var.a var.b var.c var.d
1     1     b     c     d
2     2     f     g     h
3     4     j     k     l
4     7     j     k     z", header=TRUE)

Then, I load the libraries.

# Load libraries
library(tidyr)
library(dplyr)
library(tibble)

Finally, I restructure the data.

# Manipulate data
df1 %>% 
  rownames_to_column() %>% 
  gather(variable, value, -rowname) %>% 
  left_join(df2, by = c("value" = "var.a")) %>% 
  gather(foo, bar, -variable, -rowname) %>% 
  unite(goop, variable, foo) %>% 
  spread(goop, bar) %>% 
  select(-rowname)
#> Warning: attributes are not identical across measure variables;
#> they will be dropped

which gives,

#>   var.1_value var.1_var.b var.1_var.c var.1_var.d var.2_value var.2_var.b
#> 1           7           j           k           z           9        <NA>
#> 2           4           j           k           l           6        <NA>
#> 3           2           f           g           h        <NA>        <NA>
#>   var.2_var.c var.2_var.d var.3_value var.3_var.b var.3_var.c var.3_var.d
#> 1        <NA>        <NA>           1           b           c           d
#> 2        <NA>        <NA>           9        <NA>        <NA>        <NA>
#> 3        <NA>        <NA>        <NA>        <NA>        <NA>        <NA>
#>   var.4_value var.4_var.b var.4_var.c var.4_var.d
#> 1           2           f           g           h
#> 2           7           j           k           z
#> 3        <NA>        <NA>        <NA>        <NA>

^{Created on 2019-05-30 by the reprex package (v0.3.0)}

This is a little bit convoluted, but I'll try to explain.

I turn row numbers into a column at first, as this will help me put the data back together at the very end.
I go from wide to long format for df1.
I join df2 to df1 based on var.a and var.1 (now called value), respectively.
I go from wide to long again.
I combine the variable names from each data frame into one variable.
Finally, I go from long to wide format (this is where the row numbers come in handy) and drop the row numbers.

Matching values from multiple columns in 1 data frame to key in second data frame and creating columns

1 Answers