R tidyr gather() two sets of columns based on lookup

user_7819673 0
Viewed 80

I've got a dataset of results from a satisfaction survey by region. Each question in the survey is rated on a 4-point scale (from Very Satisfied to Very Dissatisfied). Each row in the dataset contains the aggregated result for a given question in a given region at the end of a given 'fiscal year'. It also contains the total number of respondents for each level.

This is what the dataset looks like 

testdf=data.frame(FY=c("FY13","FY14","FY15","FY14","FY15","FY13","FY14","FY15","FY13","FY15","FY13","FY14","FY15","FY13","FY14","FY15"),
                  Region=c(rep("AFRICA",5),rep("ASIA",5),rep("AMERICA",6)),
                  QST=c(rep("Q2",3),rep("Q5",2),rep("Q2",3),rep("Q5",2),rep("Q2",3),rep("Q5",3)),
                  Very.Satisfied=runif(16,min = 0, max=1),
                  Total.Very.Satisfied=floor(runif(16,min=10,max=120)),
                  Satisfied=runif(16,min = 0, max=1),
                  Total.Satisfied=floor(runif(16,min=10,max=120)),
                  Dissatisfied=runif(16,min = 0, max=1),
                  Total.Dissatisfied=floor(runif(16,min=10,max=120)),
                  Very.Dissatisfied=runif(16,min = 0, max=1),
                  Total.Very.Dissatisfied=floor(runif(16,min=10,max=120))
                  )

My Objective

My objective is to reshape the dataset from a wide to a long format, by first creating a column called Level (which indicates the level from the 4 point scale). Then creating a column called Score with the aggregated scores and last column called Total with the total number of respondents for each corresponding level.

My Attempt

So far, I have just been able to gather the levels into a single column, and create the Score column.

#Gather Satisfation levels
library(tidyverse)
testfinal = testdf %>% gather(Level,Score,-FY:-QST,-Total.Very.Satisfied,-Total.Satisfied,-Total.Dissatisfied,-Total.Very.Dissatisfied) %>%
  select(1:3,8:9,4:7)

I am unable to figure out how to gather the totals into another column such that each total in the newly created Total column corresponds to the level associated with it.

Here a preview of what the final dataset would look like, if all goes well

#FY   #Region  #QST  #Level            #Score        #Total
 FY13  AFRICA   Q2    Very.Satisfied    0.73080770    48
 FY14  AFRICA   Q2    Very.Satisfied    0.58686424    97
 FY15  AFRICA   Q2    Very.Satisfied    0.07358698    34
 FY14  AFRICA   Q5    Very.Satisfied    0.59996830    22

and so on...

Any inputs would be highly appreciated.

tidyr data-manipulation r
1 Answers

This could be done more easily with melt from data.table. Convert the 'data.frame' to 'data.table' (setDT), melt from 'wide' to 'long' while specifying the multiple column name patterns in the measure argument

library(data.table)
out <- melt(setDT(testdf), measure = patterns("^Total", 
     "^(Very|Satisfied|Dis)"), value.name = c("Total", "Score"), 
       variable.name = "Level")
nm1 <- names(testdf)[c(4, 6, 8, 10)]
out[, Level := nm1[Level]][]
head(out)
#     FY Region QST          Level Total      Score
#1: FY13 AFRICA  Q2 Very.Satisfied    85 0.36888202
#2: FY14 AFRICA  Q2 Very.Satisfied   108 0.67589979
#3: FY15 AFRICA  Q2 Very.Satisfied    13 0.90792351
#4: FY14 AFRICA  Q5 Very.Satisfied    52 0.01966743
#5: FY15 AFRICA  Q5 Very.Satisfied    59 0.68895083
#6: FY13   ASIA  Q2 Very.Satisfied    96 0.24912066

Or using tidyverse

library(tidyverse)
testdf %>%
    gather(key, val, 4:ncol(.)) %>%
    extract(key, into = c("key1", "Level"), "(Total)?\\.?(.*)") %>%
    mutate(key1 = replace_na(key1, "Score")) %>%
    spread(key1, val)
#      FY  Region QST             Level      Score Total
#1  FY13  AFRICA  Q2      Dissatisfied 0.69226483    71
#2  FY13  AFRICA  Q2         Satisfied 0.91617425    26
#3  FY13  AFRICA  Q2 Very.Dissatisfied 0.64277110    43
#4  FY13  AFRICA  Q2    Very.Satisfied 0.36888202    85
#5  FY13 AMERICA  Q2      Dissatisfied 0.10219036    37
#6  FY13 AMERICA  Q2         Satisfied 0.28281065    26
#7  FY13 AMERICA  Q2 Very.Dissatisfied 0.59667659    90
#8  FY13 AMERICA  Q2    Very.Satisfied 0.30204224    76
# ....

NOTE: The values in 'Total', 'Score' are different as there was no set.seed while creating the dataset.

user_37322710
Related