r arrange data nested wide format

user_17633420 0
Viewed 51

I have a dataset like this

               Time1              Time2             Time3
 A
 Median        0.046              0.12              0 
 Q1, Q3       -0.12, 0.22        -1.67, -4.59      -0.245, 0.289     
 Range        -2.75 -4.65        -2.20  - 1.425    -3.12, -1.928
 B
 Median        0.016              0.42              0.067 
 Q1, Q3       -0.21, 0.63        -1.17, -2.98      -0.478, 0.187     
 Range        -2.15 -2.15        -1.12  - 1.125    -1.45, -1.478

What I want is to make this look like this

     Time1                             Time2                                  Time3
     Median   Q1,Q3       Range        Median   Q1,Q3         Range           Median  Q1,Q3 Range
A    0.046   -0.12, 0.22  2.75 -4.65   0.12    -1.67, -4.59  -2.20 - 1.425    0      -0.245, 0.289 -3.12, -1.928
B    0.016   -0.21, 0.63 -2.15 -2.15   0.42    -1.17, -2.98  -1.12 - 1.125    0.067  -0.478, 0.187 -1.45, -1.478

I have used spread function before to change long to wide, not sure how to turn this into a nested wide. Any suggestions is much appreciated.

df <- structure(list(Col1 = c("A", "Median", "Q1, Q3", "Range", "B", 
"Median", "Q1, Q3", "Range"), Time1 = c("", "0.046", "-0.12, 0.22", 
"-2.75 -4.65", "", "0.016", "-0.21, 0.63", "-2.15 -2.15"), Time2 = c("", 
"0.12", "-1.67, -4.59", "-2.20  - 1.425", "", "0.42", "-1.17, -2.98", 
"-1.12  - 1.125"), Time3 = c("", "0 ", "-0.245, 0.289     ", 
"-3.12, -1.928", "", "0.067 ", "-0.478, 0.187     ", "-1.45, -1.478"
)), class = "data.frame", row.names = c(NA, -8L))
reshape2 spread kable tidyr r
1 Answers

Here is a potential solution, see comments for the step by step.

library(tidyr)
#find rows containing the ids
namerows <- which(df$Time1=="")
#create and fill in the id column
df$id <- ifelse(df$Time1=="", df$Col1, NA)
df <- fill(df, id, .direction="down")
#clean up the dataframe
df <- df[-namerows, ]

#pivot
pivot_wider(df, id_cols = "id", names_from = "Col1", values_from = starts_with("Time"))

The result:

# A tibble: 2 × 10
  id    Time1_Median `Time1_Q1, Q3` Time1_Range Time2_Median `Time2_Q1, Q3` Time2_Range    Time3_Median `Time3_Q1, Q3`       Time3_Range  
  <chr> <chr>        <chr>          <chr>       <chr>        <chr>          <chr>          <chr>        <chr>                <chr>        
1 A     0.046        -0.12, 0.22    -2.75 -4.65 0.12         -1.67, -4.59   -2.20  - 1.425 "0 "         "-0.245, 0.289     " -3.12, -1.928
2 B     0.016        -0.21, 0.63    -2.15 -2.15 0.42         -1.17, -2.98   -1.12  - 1.125 "0.067 "     "-0.478, 0.187     " -1.45, -1.478
user_57922440
Related