How to transform from long to wide data set in r by creating means

user_13572205 0
Viewed 53

I have the following data set:

    Email           Relationship      Q1  Q2  Q3  Q4
1  sample@email.com    Self           1   2    2   3
2  sample@email.com    Peer           3   3    4   5
3  sample@email.com    Peer           5   2    3   1
4  sample@email.com    Peer           4   1    2   3
5  sample@email.com    Peer           2   3    3   4
6  sample@email.com    Direct Report  3   3    4   4   
7  sample@email.com    Direct Report  5   2    4   4
8  other@email.com     Self           3   4    4   2
9  other@email.com     Peer           2   2    3   4
10 other@email.com     Peer           3   3    3   2
11 other@email.com     Peer           2   5    5   3
12 other@email.com     Direct Report  4   4    4   3
13 other@email.com     Direct Report  5   3    2   1 
14 other@email.com     Direct Report  2   4    5   3

And I want to transform it from long to wide, such that I calculate means for each relationship group and total:

Email            Q1-Overall Q1-Self  Q1-Peer  Q1-Direct Report  Q2-Overall Q2-Self  Q2-Peer  Q2-Direct Report
other@email.com   3.00      3.00      2.33     3.67             3.57       4.00     3.33     3.67
sample@email.com  3.29      1.00      3.50     4.00             2.28       2.00     2.25     2.50

I've tried melting it:

df<-dcast(melt(Data_Long, id.vars=c("Email", "Relationship")), Email~Q1+Relationship)

And that gets me part of the way there, but my question is how to take it to the next step to get the means, or if there is a more efficient way. As my data has hundreds of questions, is there a way to efficiently convert all of it?

I've also tried the dplyr package summarise and spread commands, but wasn't able to find a way to combine the variables to create new variables in those. Any advice is appreciated.

reshape dataframe aggregate r
2 Answers

Here is a tidyverse solution. It groups by Email and Relationship to compute the columns Q* means. Then it reshapes to wide format with pivot_wider.

library(tidyverse)

Data_long %>%
  group_by(Email, Relationship) %>%
  summarise_at(vars(matches('^Q')), list(mean)) %>%
  pivot_wider(
    id_cols = Email,
    names_from = Relationship,
    values_from = matches('^Q')
  )
## A tibble: 2 x 13
## Groups:   Email [2]
#  Email `Q1_Direct Repo… Q1_Peer Q1_Self `Q2_Direct Repo… Q2_Peer Q2_Self `Q3_Direct Repo…
#  <chr>            <dbl>   <dbl>   <dbl>            <dbl>   <dbl>   <dbl>            <dbl>
#1 othe…             3.67    2.33       3             3.67    3.33       4             3.67
#2 samp…             4       3.5        1             2.5     2.25       2             4   
## … with 5 more variables: Q3_Peer <dbl>, Q3_Self <dbl>, #`Q4_Direct Report` <dbl>,
##   Q4_Peer <dbl>, Q4_Self <dbl>

Data.

Data_long <- read.table(text = "
    Email           Relationship      Q1  Q2  Q3  Q4
1  sample@email.com    Self           1   2    2   3
2  sample@email.com    Peer           3   3    4   5
3  sample@email.com    Peer           5   2    3   1
4  sample@email.com    Peer           4   1    2   3
5  sample@email.com    Peer           2   3    3   4
6  sample@email.com    'Direct Report'  3   3    4   4   
7  sample@email.com    'Direct Report'  5   2    4   4
8  other@email.com     Self           3   4    4   2
9  other@email.com     Peer           2   2    3   4
10 other@email.com     Peer           3   3    3   2
11 other@email.com     Peer           2   5    5   3
12 other@email.com     'Direct Report'  4   4    4   3
13 other@email.com     'Direct Report'  5   3    2   1 
14 other@email.com     'Direct Report'  2   4    5   3
", header = TRUE)
user_82454060

A data.table solution:

library(data.table)
setDT(df)
df[, melt(.SD, id.vars = c("Email", "Relationship"))
   ][, dcast(.SD, Email ~ paste(variable, Relationship, sep = "-"), fun.aggregate = mean)]

              Email Q1-Direct Report  Q1-Peer Q1-Self Q2-Direct Report  Q2-Peer Q2-Self Q3-Direct Report  Q3-Peer Q3-Self Q4-Direct Report Q4-Peer Q4-Self
1:  other@email.com         3.666667 2.333333       3         3.666667 3.333333       4         3.666667 3.666667       4         2.333333    3.00       2
2: sample@email.com         4.000000 3.500000       1         2.500000 2.250000       2         4.000000 3.000000       2         4.000000    3.25       3

Data (please provide yourself next time)

df <- data.frame(
  Email = rep(c("sample@email.com", "other@email.com"), each = 7L), 
  Relationship = c(
    "Self", "Peer", "Peer", "Peer", "Peer", "Direct Report", 
    "Direct Report", "Self", "Peer", "Peer", "Peer", "Direct Report", 
    "Direct Report", "Direct Report"
  ), 
  Q1 = c(1L, 3L, 5L, 4L, 2L, 3L, 5L, 3L, 2L, 3L, 2L, 4L, 5L, 2L), 
  Q2 = c(2L, 3L, 2L, 1L, 3L, 3L, 2L, 4L, 2L, 3L, 5L, 4L, 3L, 4L), 
  Q3 = c(2L, 4L, 3L, 2L, 3L, 4L, 4L, 4L, 3L, 3L, 5L, 4L, 2L, 5L), 
  Q4 = c(3L, 5L, 1L, 3L, 4L, 4L, 4L, 2L, 4L, 2L, 3L, 3L, 1L, 3L)
)
user_45522950
Related