0

I have a data frame in the below format Original format

For a given ID; the age ,sex and status remain the same.

I want to do a couple of transformations

  1. Have a single row for each "Id" and "Type".
  2. Find unique values in "Tablet" column and do a transpose .
  3. Transpose "Level" column and have the values corresponding to each tablet under newly transposed tablet column.Empty cells have to be filled with "NA".

The desired output format is attached below for reference.desired format

I have tried using dcast and reshape; tidyr with dplyr using gather and spread,however am not able to acheive the 3rd transformation

Any help would be great! Thanks

Vinds
  • 35
  • 1
  • 8
  • 2
    This isn't quite a duplicate of your assignment, but it should be close enough https://stackoverflow.com/questions/10589693. If not, consider reposting with mvce elements. https://stackoverflow.com/help/mcve – wibeasley Nov 11 '17 at 06:43

1 Answers1

3

You can use the spread() function in the tidyr package:

Set up packages and data:

library(dplyr)
library(tidyr)


 df=data.frame(
  id=c(1,1,1,1,1,2,2),
  age=c(3,3,3,3,3,51,51),
  sex=c('f','f','f','f','f','m','m'),
  type=c('a','a','a','b','b','a','a'),
  tablet=c('t1','t2','t3','t1','t5','t3','t10'),
  level=c(1,2,3,1,4,2,3),
  status=c('n','n','n','n','n','y','y')
  )

Use group_by() to get you result by id and type.

Use spread() to transpose the data with tablet column containing the keys for the column names, and level column containing the values. spread() will fill in blanks with NA by default.

Use select() to rearrange columns into the desired format:

df %>% 
  group_by(id,type) %>% 
  spread(tablet, level) %>% 
  select(id,age,sex,type,t1,t2,t3,t5,t10,status)
Andrew Haynes
  • 2,612
  • 2
  • 20
  • 35
  • Thanks Andrew. The code works nearly perfect. I want to have all levels for a sample ID in one row. Now I get for 4 rows for ID "1" and 2 rows for ID "2". tried a coalesce and merge.. not much luck.. finally got it working by doing a slice "fill(everything(), .direction = "down") %>% fill(everything(), .direction = "up") %>% slice(1)" .. The performance is not great though. Suggestions for any alternatives ? – Vinds Nov 13 '17 at 05:43