Pivoting table multiple variables at once in R

Question

I have a data frame that looks like this:

df <- data.frame(residence_pre = c("house", "apartment", "house"),
                 residence_during = c("house", "apartment", "house"),
                 residence_after = c("house", "apartment", "house"),
                 family_pre = c(1,2,3),
                 family_during = c(2,2,4),
                 family_after = c(1,2,4))

I would like to pivot it in a way that it looks like this: (This is example data. There are many more columns in the real data frame, all following this pattern)

pivot <- data.frame(obs.number = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
                    residence = c("house", "house", "house", "apartment", "apartment", "apartment", "house", "house", "house"),
                    family = c(1, 2, 1, 2, 2, 2, 3, 4, 4),
                    status = c("pre", "during", "after", "pre", "during", "after", "pre", "during", "after"))

Answer 1

After adding the obs.number, we can do this in one pivot step:

library(tidyverse)
df |>
  mutate(obs.number = row_number()) |>
  pivot_longer(-obs.number, names_to = c(".value", "status"),
               values_to = "family", names_sep = "_")
  # values_to = "family" is to assign the values to a column called "family",
  #   instead of the default column name "value"


# A tibble: 9 × 4
  obs.number status residence family
       <int> <chr>  <chr>      <dbl>
1          1 pre    house          1
2          1 during house          2
3          1 after  house          1
4          2 pre    apartment      2
5          2 during apartment      2
6          2 after  apartment      2
7          3 pre    house          3
8          3 during house          4
9          3 after  house          4