0

I have something like this:

id    role1   Approved by Role1     role2          Approved by Role2
1      Amy        1/1/2019          David             4/4/2019
2      Bob        2/2/2019          Sara              5/5/2019
3      Adam       3/3/2019          Rachel            6/6/2019

I want something like this:

id    Name      Role        Approved     
1      Amy      role1       1/1/2019         
2      Bob      role1       2/2/2019        
3      Adam     role1       3/3/2019  
1      David    role2       4/4/2019         
2      Sara     role2       5/5/2019        
3      Rachel   role2       6/6/2019

I thought something like this would work

 melt(df,id.vars= id,
  measure.vars= list(c("role1", "role2"),c("Approved by Role1", "Approved by Role2")),
  variable.name= c("Role","Approved"),
  value.name= c("Name","Date"))

but i am getting Error: measure variables not found in data:c("role1", "role2"),c("Approved by Role1", "Approved by Role2")

I have tried replacing this with the number of the columns as well and haven't had any luck.

Any suggestions?? Thanks!

Ellie
  • 415
  • 7
  • 16
  • 1
    You're missing a closing quotation mark in `c("role1", "role2)` – camille Aug 19 '19 at 00:18
  • sorry that's my mistype, corrected – Ellie Aug 19 '19 at 00:20
  • Possible duplicate of [Reshaping multiple sets of measurement columns (wide format) into single columns (long format)](https://stackoverflow.com/questions/12466493/reshaping-multiple-sets-of-measurement-columns-wide-format-into-single-columns) – divibisan Aug 19 '19 at 18:44

1 Answers1

2

I really like the new tidyr::pivot_longer() function. It's still only available in the dev version of tidyr, but should be released shortly. First I'm going to clean up the column names slightly, so they have a consistent structure:

> df
# A tibble: 3 x 5
     id name_role1 approved_role1 name_role2 approved_role2
  <dbl> <chr>      <chr>          <chr>      <chr>         
1     1 Amy        1/1/2019       David      4/4/2019      
2     2 Bob        2/2/2019       Sara       5/5/2019      
3     3 Adam       3/3/2019       Rachel     6/6/2019

Then it's easy to convert to long format with pivot_longer():

library(tidyr)

df %>%
    pivot_longer(
        -id,
        names_to = c(".value", "role"),
        names_sep = "_"
    )

Output:

     id role  name   approved
  <dbl> <chr> <chr>  <chr>   
1     1 role1 Amy    1/1/2019
2     1 role2 David  4/4/2019
3     2 role1 Bob    2/2/2019
4     2 role2 Sara   5/5/2019
5     3 role1 Adam   3/3/2019
6     3 role2 Rachel 6/6/2019
Marius
  • 58,213
  • 16
  • 107
  • 105