1

I'm trying to merge rows in my data frame in a specific way. I have no idea how to go about this, but below I have a sample input and desired output.

For an explanation, say we have 2 employees identified by id = 1 & 2. Each of these employees receives an evaluation from a supervisor, which could be the same person (employee 2 has 2 evaluations from John). Last, each supervisor rates each employee on 2 questions, on a scale from 0 to 4.

x = data.frame(
  employee_id = c(1, 1, 2, 2),
  supervisor = c("John.1", "George.1", "John.1", "John.2"),
  q1 = c(4, 1, 0, 4),
  q2 = c(1, 2, 1, 3)
)

I want to combine this into one row for each employee so that the output looks like:

y = data.frame(
  employee_id = c(1, 2),
  John.1_q1 = c(4,0),
  John.1_q2 = c(1,1),
  George.1_q1 = c(1, NA),
  George.1_q2 = c(2, NA),
  John.2_q1 = c(NA, 4),
  John.2_q2 = c(NA,3)
)

I'm guessing this could be done using the reshape package but I can't make it work.

John Bacot
  • 31
  • 2

1 Answers1

3

We can use pivot_wider

library(tidyr)
library(dplyr)
library(stringr)
pivot_wider(x, names_from = supervisor, values_from = c(q1, q2)) %>%  
     rename_at(-1, ~ str_replace(., "(.*)_(.*)", "\\2_\\1"))
akrun
  • 874,273
  • 37
  • 540
  • 662