R – Can't seem to reorganize a data set using pivot_longer

Question

I have the following data frame:

Sex	Controlled	Uncontrolled	SE_Controlled	SE_Uncontrolled
Male	a	c	e	g
Woman	b	d	f	h

However, I find this layout unhelpful and I would like to have the following structure:

Sex	Control	prop	SE
Male	Controlled	a	e
Woman	Controlled	b	f
Male	Uncontrolled	c	g
Woman	Uncontrolled	d	h

I've tried using pivot_longer() but can't seem to find a correct solution. Any suggestions?

Answer 1

The most straightforward way of doing this is to:

1. Add a "temp" prefix to the columns we want to be a part of the "temp" column in the output, then
2. Pivot longer, using the first part of the column name as the name of the column we want the value to go into, and the second part as the value of the type column (the names_to syntax is a bit hard to read, but I hope that explanation makes sense).

library(tidyverse)

df |>
  rename_with(~ paste0("temp_", .x), .cols = c(Controlled, Uncontrolled)) |>
  pivot_longer(-Sex, names_to = c(".value", "type"), names_sep = "_")

Output:

# A tibble: 4 × 4
  Sex   type         temp  SE   
  <chr> <chr>        <chr> <chr>
1 Male  Controlled   a     e    
2 Male  Uncontrolled c     g    
3 Woman Controlled   b     f    
4 Woman Uncontrolled d     h    

Data:

df <- read.table(text="
Sex     Controlled  Uncontrolled    SE_Controlled   SE_Uncontrolled
Male    a   c   e   g
Woman   b   d   f   h", head = TRUE)