Reshape data frame and conver values into columns-CodePudding

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

CodePudding user response：

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)}

CodePudding user response：

We could combine pivot_longer with pivot_wider:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(-Segment.Number) %>% 
  pivot_wider(names_from=Segment.Number, values_from=value)

  name  `Start Event`   `Start Time` `End Event` `End Time` `Segment Durat…` `Total SCRs` `ER-SCRs` `NS-SCRs` `Tonic SCL` `Mean SC` `Tonic Period`
  <chr> <chr>           <chr>        <chr>       <chr>      <chr>            <chr>        <chr>     <chr>     <chr>       <chr>     <chr>         
1 X1    time.txt:start… 60           time.txt:s… 200        140              27           0         27        16.8770208… 17.32510… 80.4569384835…
2 X2    time.txt:start… 215          time.txt:s… 515        300              68           1         67        18.5079437… 19.01216… 165.330220146…
3 X3    time.txt:start… 2040         time.txt:s… 2940       900              155          0         155       22.1224503… 22.60069… 546.209379862…
4 Path  Code1           Code1        Code1       Code1      Code1            Code1        Code1     Code1     Code1       Code1     Code1