Transform data from long to wide

February 16, 2023

I have the following data frame:

df <- data.frame(
  timestamp = c(1675930826.3839524, 1675930826.3839593, 1675930826.3839765, 1675930826.388385, 1675930826.3884094, 1675930826.3884153),
  label = c("A", "B", "C", "A", "B", "C"),
  value = c(1.996, 0.404, 4.941, 1.996, 0.404, 4.941)
)

Basically, the data are in cycles, first A, then B and finally C. So Instead of having them in three separate rows, I want to produce this output:

timestamp          A      B      C
1675930826.3839524 1.996  0.404  4.941
1675930826.388385  1.996  0.404  4.941

I would like to have the timestamp of A and then add the A, B, and C values. I tried this to solve my problem:

df %>% 
  pivot_wider(names_from = label, values_from = value) %>% 
  pivot_longer(cols = c("A", "B", "C"), names_to = "label", values_to = "value") %>% 
  arrange(timestamp) %>% 
  select(timestamp, A, B, C)

>Solution :

library(tidyverse)

df %>%
  group_by(grp = cumsum(label == 'A')) %>%
  mutate(timestamp = timestamp[label == 'A']) %>%
  ungroup() %>%
  pivot_wider(id_cols = timestamp, names_from = label, values_from = value)

# # A tibble: 2 × 4
#     timestamp     A     B     C
#         <dbl> <dbl> <dbl> <dbl>
# 1 1675930826.  2.00 0.404  4.94
# 2 1675930826.  2.00 0.404  4.94