[Solved] R formating long data to wide data… but with linked results

by


Your issue is that R won’t like the final table because it has duplicate column names. Maybe you need the data in that format but it’s a bad way to store data because it would be difficult to put the columns back into rows again without a load of manual work.

That said, if you want to do it you’ll need a new column to help you transpose the data.

I’ve used dplyr and tidyr below, which are worth looking at rather than reshape. They’re by the same author but more modern and designed to fit together as part of the ‘tidyverse’.

library(dplyr)
library(tidyr)

#Recreate your data (not doing this bit in your question is what got you downvoted)
df <- data.frame(
  SN = c("TD62","TD62","TD58","TD58","TD14","TD14"),
  LabTest = c("Creat","AST","Creat","Albumin","AST","Albumin"),
  LabDate = c("05/12/2004","06/12/2004","26/05/2007","26/05/2005","28/02/2007","26/02/2007"),
  Result = c(22,652,72,22,234,15),
  Lower = c(30,6,30,25,6,25),
  Upper = c(90,45,90,35,45,35),
  stringsAsFactors = FALSE
)

output <- df %>% 
  group_by(SN) %>% 
  mutate(id_number = row_number()) %>% #create an id number to help with tracking the data as it's transposed
  gather("key", "value", -SN, -id_number) %>% #flatten the data so that we can rename all the column headers
  mutate(key = paste0("t",id_number, key)) %>% #add id_number to the column names. 't' for 'test' to start name with a letter.
  select(-id_number) %>% #don't need id_number anymore
  spread(key, value)

  SN    t1LabDate  t1LabTest t1Lower t1Result t1Upper t2LabDate  t2LabTest t2Lower t2Result t2Upper
  <chr> <chr>      <chr>     <chr>   <chr>    <chr>   <chr>      <chr>     <chr>   <chr>    <chr>  
1 TD14  28/02/2007 AST       6       234      45      26/02/2007 Albumin   25      15       35     
2 TD58  26/05/2007 Creat     30      72       90      26/05/2005 Albumin   25      22       35     
3 TD62  05/12/2004 Creat     30      22       90      06/12/2004 AST       6       652      45

And you’re there, possibly with some sorting issues still to crack if you need the columns in a specific order.

4

solved R formating long data to wide data… but with linked results