Is there an R function for pivoting multiple columns to the same row?

Question

I recently ran an experiment that asked each participant to fill out a scale for each of the stimuli presented my data now looks something like this:

Participant Stim1_1 Stim1_2 Stim1_3 Stim2_1 Stim2_2 Stim2_3 Stim3_1 Stim3_2 Stim3_3

A
B

With multiple stimuli and multiple scale items. I want to pivot this to make it a long dataset, with each stimulus having its own row with each of the items making up their own column, something like this:

Participant Stim Item1 Item2 Item3

A 1
A 2
A 3
B 1
B 2
B 3

If that makes sense. I was thinking about using pivot_longer, but that seems to make a new row for each stimulus and item, so that stimulus 1, item 1 would have its own row, as well as stimulus 1, item 2, etc. Are there any ways to do this?

Answer 1

You were on the right track with pivot_longer. There may be an easier way to do this with a single pivot_wider, but I just break it down into 3 steps myself.

First I get everything in a long format. Then I break the column names up the way I want and get the data into a tidy format. Then I pivot wider to get the columns back into a human readable format.

library(dplyr)
library(tidyr)

df <- dplyr::tribble(
  ~Participant,~Stim1_1,~Stim1_2,~Stim1_3,~Stim2_1,~Stim2_2,~Stim2_3,~Stim3_1,~Stim3_2,~Stim3_3,
  "A",NA,NA,NA,NA,NA,NA,NA,NA,NA,
  "B",NA,NA,NA,NA,NA,NA,NA,NA,NA,
)

df %>% tidyr::pivot_longer(
  Stim1_1:Stim3_3,
  names_prefix = "Stim"
) %>%
  tidyr::separate(name,c("Stim","Item"),sep = "_") %>%
  pivot_wider(
    names_from = Item,
    values_from = value,
    names_prefix = "Item"
  )

# # A tibble: 6 x 5
# Participant Stim  Item1 Item2 Item3
# <chr>       <chr> <lgl> <lgl> <lgl>
# 1 A           1     NA    NA    NA   
# 2 A           2     NA    NA    NA   
# 3 A           3     NA    NA    NA   
# 4 B           1     NA    NA    NA   
# 5 B           2     NA    NA    NA   
# 6 B           3     NA    NA    NA