Performing pivot_longer() over multiple sets of columns

Question

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

Answer 1

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