rdata-structurespivottidyverse

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


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?


Solution

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