0

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?

Emily
  • 15
  • 2

1 Answers1

0

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
Adam Sampson
  • 1,971
  • 1
  • 7
  • 15
  • Apparently pivot_wider has separate built in... `df %>% tidyr::pivot_longer( Stim1_1:Stim3_3, names_to = c("Stim","Item"), names_prefix = "Stim", names_sep = "_" ) %>% pivot_wider( names_from = Item, values_from = value, names_prefix = "Item" )` – Adam Sampson Nov 16 '20 at 22:28
  • Yes, thank you so much! That was it exactly. I already had a dataframe with data (I simplified it above for Stack Overflow) so I just left out the dplyr::tribble part, but otherwise it worked perfectly. Thank you so much, you just saved me hours! – Emily Nov 17 '20 at 00:27