R: Reshape single column in nested data set

Question

I'm new to nested data and am not experienced in how to best organize and shuffle them.

Currently I have nested data with specific subjects, trials and two ratings A and B. All of my data are organized in rows but my ratings A and B are structured as single columns. I want to reorganize them so the column of rating B is added to my rows including all of the other information from the remaining columns (except rating A).

To hopefully make it easier to understand I added a screenshot of what I would like to have.

Thanks so much for your help!!!

score 0 · Answer 1 · answered May 12 '22 at 14:00

You can use pivot_longer() like this:

library(tidyverse)

dt %>% 
  pivot_longer(
    starts_with("rating"),
    names_prefix = "rating",
    names_to = "RatingA-B",
    values_to = "rating"
  )

Output:

      ID trial `RatingA-B` rating
   <dbl> <dbl> <chr>        <dbl>
 1     1     1 A              3  
 2     1     1 B             30  
 3     1     2 A              4  
 4     1     2 B             40  
 5     2     1 A              8  
 6     2     1 B             80  
 7     2     2 A              9  
 8     2     2 B             90  
 9     3     1 A              0.1
10     3     1 B              1  
11     3     2 A              0.5
12     3     2 B              5

Input:

dt = data.frame(ID=c(1,1,2,2,3,3),trial=c(1,2,1,2,1,2),
                ratingA=c(3,4,8,9,0.1,0.5),
                ratingB=c(30,40,80,90,1,5)
                )

score 0 · Answer 2 · answered May 12 '22 at 14:01

With tidyr:

library(tidyr)

df <- tibble(ID = rep(1:3, each = 5), trial = rep(1:5, 3), ratingA = sample(15), ratingB = sample(15))

pivot_longer(df, ratingA:ratingB, names_to = 'group', names_prefix = 'rating', values_to = 'rating')

#> # A tibble: 30 × 4
#>       ID trial group rating
#>    <int> <int> <chr>  <int>
#>  1     1     1 A         10
#>  2     1     1 B          1
#>  3     1     2 A          2
#>  4     1     2 B          9
#>  5     1     3 A         14
#>  6     1     3 B          4
#>  7     1     4 A          7
#>  8     1     4 B          6
#>  9     1     5 A          5
#> 10     1     5 B         15
#> # … with 20 more rows

R: Reshape single column in nested data set

2 Answers2