Creating new column in data frame based on value matched to participant ID

TSL-Aquilus 0
Viewed 23

I know there is a simple solution to this problem, as I solved it a couple of months ago, but have since lost the relevant file, and cannot for the life of me work out how I did it.

My data is in a long form, where each row represents a participant's answer to one question, with all rows for one participant sharing a common participant ID - e.g.

ParticipantID Question  Resp
1             Age       x1
1             Gender    x2
1             Education x3
1             Q1        x4
1             Q2        x5
...
2             Age       y1
2             Gender    y2
...
etc

I want to add new columns to the data to associate the various demographic values with each answer provided by a given participant. So in the example above, I would have a new column "Age" which would take the value x1 for all rows where ParticipantID = 1, y1 for all rows where ParticipantID = 2, etc., like so:

ParticipantID Question  Resp Age Gender ...
1             Age       x1   x1  x2
1             Gender    x2   x1  x2
1             Education x3   x1  x2
1             Q1        x4   x1  x2
1             Q2        x5   x1  x2
...
2             Age       y1   y1  y2
2             Gender    y2   y1  y2
...
etc

Importantly, I can't just rotate the table from long to wide, because I need the study questions (represented as Q1, Q2, ... above) to remain in long form.

Any help that can be offered is greatly appreciated!

r
1 Answers

As long as each participant has the same questions in the same order, you can do

cbind(df, do.call(rbind, lapply(split(df, df$ParticipantID), function(x) {
  setNames(as.data.frame(t(x[-1])[rep(2, nrow(x)),]), x[[2]])
})), row.names = NULL)
#>    ParticipantID  Question Resp Age Gender Education Q1 Q2
#> 1              1       Age   x1  x1     x2        x3 x4 x5
#> 2              1    Gender   x2  x1     x2        x3 x4 x5
#> 3              1 Education   x3  x1     x2        x3 x4 x5
#> 4              1        Q1   x4  x1     x2        x3 x4 x5
#> 5              1        Q2   x5  x1     x2        x3 x4 x5
#> 6              2       Age   y1  y1     y2        y3 y4 y5
#> 7              2    Gender   y2  y1     y2        y3 y4 y5
#> 8              2 Education   y3  y1     y2        y3 y4 y5
#> 9              2        Q1   y4  y1     y2        y3 y4 y5
#> 10             2        Q2   y5  y1     y2        y3 y4 y5

Data used

df <- structure(list(ParticipantID = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 
2L, 2L, 2L), Question = c("Age", "Gender", "Education", "Q1", 
"Q2", "Age", "Gender", "Education", "Q1", "Q2"), Resp = c("x1", 
"x2", "x3", "x4", "x5", "y1", "y2", "y3", "y4", "y5")), class = "data.frame", 
row.names = c(NA, -10L))

df
#>    ParticipantID  Question Resp
#> 1              1       Age   x1
#> 2              1    Gender   x2
#> 3              1 Education   x3
#> 4              1        Q1   x4
#> 5              1        Q2   x5
#> 6              2       Age   y1
#> 7              2    Gender   y2
#> 8              2 Education   y3
#> 9              2        Q1   y4
#> 10             2        Q2   y5

Created on 2022-09-19 with reprex v2.0.2

Allan Cameron0
Related