Reshape data frame and conver values into columns

June 28, 2022

I have this dataframe:

structure(list(Segment.Number = c("Start Event", "Start Time", 
"End Event", "End Time", "Segment Duration", "Total SCRs", "ER-SCRs", 
"NS-SCRs", "Tonic SCL", "Mean SC", "Tonic Period"), X1 = c("time.txt:start pressed (F1):BL1", 
"60", "time.txt:start pressed (F1):BL2", "200", "140", "27", 
"0", "27", "16.877020827457564", "17.325101513639225", "80.45693848354793"
), X2 = c("time.txt:start pressed (F1):F1", "215", "time.txt:start pressed (F1):F2", 
"515", "300", "68", "1", "67", "18.507943774797333", "19.012163892375462", 
"165.33022014676453"), X3 = c("time.txt:start pressed (F1):W1", 
"2040", "time.txt:start pressed (F1):W2", "2940", "900", "155", 
"0", "155", "22.1224503921822", "22.600699854723032", "546.20937986219167"
), Path = c("Code1", "Code1", "Code1", "Code1", "Code1", "Code1", 
"Code1", "Code1", "Code1", "Code1", "Code1")), row.names = c(NA, 
-11L), class = "data.frame")

I’d like to change it format so things look like this:

The values under column "Segment.Number" would be column names and each value is in the right place.

I have done Gather and Spread functions a lot but I can not get it right.

I have reviewed similar questions but somthing is different here, I need also to convert the values into columns.

Thanks a lot,
Ido

>Solution :

Functions spread and gather are deprecated in favor of pivot_wider and pivot_longer. Start by normalizing the data into 3NF / tidy form into a long form using pivot_longer. Then you have just name (key), value pairs that can be put into multiple new columns using pivot_wider:

library(tidyverse)

data <- structure(list(Segment.Number = c(
  "Start Event", "Start Time",
  "End Event", "End Time", "Segment Duration", "Total SCRs", "ER-SCRs",
  "NS-SCRs", "Tonic SCL", "Mean SC", "Tonic Period"
), X1 = c(
  "time.txt:start pressed (F1):BL1",
  "60", "time.txt:start pressed (F1):BL2", "200", "140", "27",
  "0", "27", "16.877020827457564", "17.325101513639225", "80.45693848354793"
), X2 = c(
  "time.txt:start pressed (F1):F1", "215", "time.txt:start pressed (F1):F2",
  "515", "300", "68", "1", "67", "18.507943774797333", "19.012163892375462",
  "165.33022014676453"
), X3 = c(
  "time.txt:start pressed (F1):W1",
  "2040", "time.txt:start pressed (F1):W2", "2940", "900", "155",
  "0", "155", "22.1224503921822", "22.600699854723032", "546.20937986219167"
), Path = c(
  "Code1", "Code1", "Code1", "Code1", "Code1", "Code1",
  "Code1", "Code1", "Code1", "Code1", "Code1"
)), row.names = c(
  NA,
  -11L
), class = "data.frame")

data %>%
  as_tibble() %>%
  # always normalize the data first
  pivot_longer(c(X1, X2, X3), names_to = "Time") %>%
  # format to desired shape
  pivot_wider(names_from = Segment.Number)
#> # A tibble: 3 × 13
#>   Path  Time  `Start Event` `Start Time` `End Event` `End Time` `Segment Durat…`
#>   <chr> <chr> <chr>         <chr>        <chr>       <chr>      <chr>           
#> 1 Code1 X1    time.txt:sta… 60           time.txt:s… 200        140             
#> 2 Code1 X2    time.txt:sta… 215          time.txt:s… 515        300             
#> 3 Code1 X3    time.txt:sta… 2040         time.txt:s… 2940       900             
#> # … with 6 more variables: `Total SCRs` <chr>, `ER-SCRs` <chr>,
#> #   `NS-SCRs` <chr>, `Tonic SCL` <chr>, `Mean SC` <chr>, `Tonic Period` <chr>

^{Created on 2022-06-28 by the reprex package (v2.0.0)}