CODEWITHSUNDEEP
rplyrreshapereshape2
alaj
alaj

Reputation: 187

Transforming key values from one data frame into columns of another data frame

I have searched around but could not find a particular answer to my question.

Suppose I have two data frames:

df1 <- data.frame(name = c('a', 'b', 'c'),
                  t1 = c(3, 25, 400),
                  t2 = c(3, 25, 400),
                  t3 = c(NA, 25, 800),
                  t4 = c(NA, 25, 800),
                  t5 = c(NA, 25, 800),
                  t6 = c(NA, 25, 800),
                  t7 = c(5, 90, 1200))

df2 <- data.frame(id = c(1, 2),
                  t1 = c(0.33, 0.98),
                  t2 = c(0.87, 0.54),
                  t3 = c(0.92, 0.84),
                  t4 = c(0.65, 0.16),
                  t5 = c(0.94, 0.84),
                  t6 = c(0.16, 0.02),
                  t7 = c(0.93, 0.18))

In the example above df2 has two rows of data for test measurements. (t1 .. t7)

df1 has values for the test condition (a, b, c) under which the test measurements (t1 .. t7) were performed.

I'm looking for a way to create a new data frame that converts the test conditions (a, b, c) into columns and combines them with the test measurements so that the resulting data frame looks like this:

# Desired Output
#
#  id   a   b   c    t1    t2    t3    t4    t5    t6    t7
#  1    3   25  400  0.33  0.87  NA    NA    NA    NA    NA
#  1    NA  25  800  NA    NA    0.92  0.65  0.94  0.16  NA
#  1    5   90  1200 NA    NA    NA    NA    NA    NA    0.93
#  2    3   25  400  0.98  0.54  NA    NA    NA    NA    NA
#  2    NA  25  800  NA    NA    0.84  0.16  0.84  0.02  NA
#  2    5   90  1200 NA    NA    NA    NA    NA    NA    0.18

Thanks for any help!

Upvotes: 0

Views: 32

Answers (1)

bouncyball
bouncyball

Reputation: 10771

Here's a way, using gather, inner_join, and then two calls to spread:

library(tidyverse)

df1 %>%
    gather(variable, value, -name) %>% #wide to long
    inner_join(df2 %>% gather(variable, value, -id), by = 'variable') %>%
    spread(name, value.x) %>% # spread first time
    spread(variable, value.y) # spread second time

  id  a  b    c   t1   t2   t3   t4   t5   t6   t7
1  1  3 25  400 0.33 0.87   NA   NA   NA   NA   NA
2  1  5 90 1200   NA   NA   NA   NA   NA   NA 0.93
3  1 NA 25  800   NA   NA 0.92 0.65 0.94 0.16   NA
4  2  3 25  400 0.98 0.54   NA   NA   NA   NA   NA
5  2  5 90 1200   NA   NA   NA   NA   NA   NA 0.18
6  2 NA 25  800   NA   NA 0.84 0.16 0.84 0.02   NA

It might be helpful to step through each line one-by-one to see the results. It would make for a very long and cumbersome answer, but you should consider doing it in your own R session.

Upvotes: 1

Related Questions