I have some data that looks like this

color   value_morning   quality_morning value_evening   quality_evening value_night quality_night
red     10              good            9               good            10          good
blue    7               bad             8               great           10          great
green   7               bad             8               good            6           bad

and I am trying to use pivot_longer() (or any other tidy method) to transform it into:

color   value   time
red     10      morning
blue    7       morning
green   7       morning
red     9       evening
blue    8       evening
green   8       evening
red     10      night
blue    10      night
green   6       night

I don't need the quality_ data, just the value_ data.

I tried creating a vector that looked like labels <- c("value_morning", "value_evening", "value_night") and running df %>% pivot_longer(cols = labels) but that didn't work. Any help is appreciated!

CodePudding user response：

We remove the quality colums and then do the pivot_longer

library(dplyr)
library(tidyr)
df1 %>% 
   select(-starts_with('quality')) %>% 
   pivot_longer(cols = -color, names_to = c(".value", "time"), 
          names_pattern = "(.*)_(.*)")

-output

# A tibble: 9 × 3
  color time    value
  <chr> <chr>   <int>
1 red   morning    10
2 red   evening     9
3 red   night      10
4 blue  morning     7
5 blue  evening     8
6 blue  night      10
7 green morning     7
8 green evening     8
9 green night       6

data

df1 <- structure(list(color = c("red", "blue", "green"), value_morning = c(10L, 
7L, 7L), quality_morning = c("good", "bad", "bad"), value_evening = c(9L, 
8L, 8L), quality_evening = c("good", "great", "good"), value_night = c(10L, 
10L, 6L), quality_night = c("good", "great", "bad")),
 class = "data.frame", row.names = c(NA, 
-3L))