Tidyverse: using pivot_wide when data are dependent on multiple columns

Question

I searched similar questions but couldn't find an answer to what I am trying to achieve. I have a dataset that is set up like so:

ID    Trial   Treatment   Frequency  Value   
A       1      Control    8000       65.1   
A       1      Top        8000       62.8    
A       1      Bottom     8000       60.3  
A       1      Control    9000       63.1   
A       1      Top        9000       66.2    
A       1      Bottom     9000       69.8
A       2      Control    8000       67.6   
A       2      Top        8000       63.4    
A       2      Bottom     8000       71.9 
A       2      Control    9000       59.7  
A       2      Top        9000       63.3  
A       2      Bottom     9000       57.2

Each ID (altogether there are 27) is subjected to three Treatment of playbacks of sounds at either 8000 or 9000 Hz (Frequency). The process is repeated twice, meaning there are multiple Trial.

I want to use pivot_wider on the Treatment column to end up with a table that looks like:

ID   Trial   Frequency   Control   Top   Bottom   
A    1       8000        65.1      62.8  60.3
A    2       8000        67.6      63.4  71.9  
A    1       9000        63.1      66.2  69.8
A    2       9000        59.7      63.3  57.2

Reproducible data:

df <- read.table(text="ID  Trial  Treatment   Frequency  Value   
A       1      Control    8000  65.1   
A       1      Top    8000  62.8    
A       1      Bottom   8000  60.3  
A       1      Control    9000  63.1   
A       1      Top    9000  66.2    
A       1      Bottom  9000  69.8 
A       2      Control  8000  67.6  
A       2      Top      8000  63.4  
A       2      Bottom   8000   71.9 
A       2      Control    9000  59.7  
A       2      Top      9000  63.3  
A       2      Bottom     9000  57.2", strin=F,h=T)

`tidyr::pivot_wider(df, names_from = Treatment, values_from = Value)` — Ronak Shah, Sep 09 '20 at 01:28

akrun · Accepted Answer · 2020-09-09T01:44:08.140

0

We could create an index column with rowid (if there duplicate elements for ID, Treatment) and then do the reshaping to wide format with pivot_wider

library(dplyr)
library(tidyr)
library(data.table)
df %>% 
    mutate(rid = rowid(ID, Treatment))  %>%
    pivot_wider(names_from = Treatment, values_from = c(Value))
# A tibble: 4 x 6
#  ID    Trial Frequency Control   Top Bottom
#  <chr> <int>     <int>   <dbl> <dbl>  <dbl>
#1 A         1      8000    65.1  62.8   60.3
#2 A         1      9000    63.1  66.2   69.8
#3 A         2      8000    67.6  63.4   71.9
#4 A         2      9000    59.7  63.3   57.2

Or another option is to apply a function i.e. mean for the 'Value' column so that we get the average value for any duplicates

df %>%
    pivot_wider(names_from = Treatment, values_from = c(Value), values_fn = mean)

edited Sep 09 '20 at 01:44

answered Sep 09 '20 at 01:28

akrun

874,273
37
540
662

Thanks. This works on the data I have shared here, but when I tried previously on my full dataset the values spread across multiple rows. – Sep 09 '20 at 01:33
@r_noob Is it based on the example you showed – akrun Sep 09 '20 at 01:33
@r_noob what is the output you are getting – akrun Sep 09 '20 at 01:35
I end up with something like A 1 8000 65.1 NA NA A 1 8000 NA 62.8 NA A 1 8000 NA NA 60.3 Sorry, if that isn't helpful.. – Sep 09 '20 at 01:40
@r_noob is it based on the same example – akrun Sep 09 '20 at 01:40
no, the example works fine. The issue is when I try implement it to my larger dataset. – Sep 09 '20 at 01:42
@r_noob in that case, the dataset may have duplicates. can you try the updated post – akrun Sep 09 '20 at 01:44

Tidyverse: using pivot_wide when data are dependent on multiple columns

1 Answers1