Performing pivot_longer() over multiple sets of columns

user_1280548 0
Viewed 63

I am stuck in performing pivot_longer() over multiple sets of columns. Here is the sample dataset

df <- data.frame(
  id = c(1, 2),
  uid = c("m1", "m2"),
  germ_kg = c(23, 24),
  mineral_kg = c(12, 17),
  perc_germ = c(45, 34),
  perc_mineral = c(78, 10))

I need the output dataframe to look like this

out <- df <- data.frame(
  id = c(1, 1, 2, 2),
  uid = c("m1", "m1", "m2", "m2"),
  crop = c("germ", "germ", "mineral", "mineral"),
  kg = c(23, 12, 24, 17),
  perc = c(45, 78, 34, 10))
dplyr dataframe r
2 Answers
df %>%
  rename_with(~str_replace(.x,'(.*)_kg', 'kg_\\1')) %>%
  pivot_longer(-c(id, uid), names_to = c('.value', 'crop'), names_sep = '_')

# A tibble: 4 x 5
     id uid   crop       kg  perc
  <dbl> <chr> <chr>   <dbl> <dbl>
1     1 m1    germ       23    45
2     1 m1    mineral    12    78
3     2 m2    germ       24    34
4     2 m2    mineral    17    10

If you were to use data.table:

library(data.table)
melt(setDT(df), c('id', 'uid'), patterns(kg = 'kg', perc = 'perc'))
  id uid variable kg perc
1:  1  m1        1 23   45
2:  2  m2        1 24   34
3:  1  m1        2 12   78
4:  2  m2        2 17   10
user_83802720

I suspect there might be a simpler way using pivot_long_spec, but one tricky thing here is that your column names don't have a consistent ordering of their semantic components. @Onyambu's answer deals with this nicely by fixing it upsteam.

library(tidyverse)
df %>%
  pivot_longer(-c(id, uid)) %>%
  separate(name, c("col1", "col2")) %>%                 # only needed
  mutate(crop = if_else(col2 == "kg", col1, col2),      # because name
         meas = if_else(col2 == "kg", col2, col1)) %>%  # structure
  select(id, uid, crop, meas, value) %>%                # is
  pivot_wider(names_from = meas, values_from = value)   # inconsistent


# A tibble: 4 x 5
     id uid   crop       kg  perc
  <dbl> <chr> <chr>   <dbl> <dbl>
1     1 m1    germ       23    45
2     1 m1    mineral    12    78
3     2 m2    germ       24    34
4     2 m2    mineral    17    10
user_68518250
Related