SHW SHW - 2 months ago 11
R Question

Reshape column along with reshaped dataframe (from long to wide, using dcast - reshape2 package)

I reshaped a long dataframe to a wide dataframe using the code below (up to Current dataframe). The thing that I don't know how to achieve within this code, is to also embed the column 'answer' into the new dataframe (see Desired dataframe). Any wise guy or gall can help me out a little?

#Variables
resp < c(1325851107,1325851108,1325851109,1325851107,1325851108,1325851109,1325851107,1325851108,1325851109)
company <- c("Dark.nl","Dark.nl","Dark.nl","Dark.nl","Dark.nl","Dark.nl","Manual.nl","Manual.nl","Manual.nl")
question <- c("Quality","Quality","Quality","Amount","Amount","Amount","Quality","Quality","Quality")
score <- c(4,1,2,6,8,10,5,5,7)
answer <- c("Didn't like","Was ok","Sure","Maybe","Fine","Not bad","Fine","No, thank you","Why not")
resp2 <- c(1325851107,1325851107,1325851108,1325851108,1325851109,1325851109)
company2 <- c("Dark.nl","Manual.nl","Dark.nl","Manual.nl","Dark.nl","Manual.nl")
quality <- c(4,5,1,5,2,7)
quality_a <- c("Didn't like","Fine","Was ok","No, thank you","Sure","Why not")
amount <- c(6, NA, 8, NA, 10, NA)
amount_a <- c("Maybe",NA, "Fine", NA, "Not bad", NA)

#Current dataframe
current <- data.frame(resp,company,question,score,answer); current

#Using dcast function to reshape
library("reshape2")
dcast(current, resp + company ~ question, value.var="score")

#Desired dataframe
desired <- data.frame(resp2,company2,quality,quality_a,amount,amount_a); desired

#Grouping variable more than two levels, include "Senses"
resp <- c(1325851107,1325851108,1325851109,1325851107,1325851108,1325851109,1325851107,1325851108,1325851109,1325851107,1325851108,1325851109)
company <- c("Dark.nl","Dark.nl","Dark.nl","Dark.nl","Dark.nl","Dark.nl","Manual.nl","Manual.nl","Manual.nl","Dark.nl","Dark.nl","Dark.nl")
question <- c("Quality","Quality","Quality","Amount","Amount","Amount","Quality","Quality","Quality","Sense","Sense","Sense")
score <- c(4,1,2,6,8,10,5,5,7,4,6,7)
answer <- c("Didn't like","Was ok","Sure","Maybe","Fine","Not bad","Fine","No, thank you","Why not","Nice","Ok","Yuk")

current_2 <- data.frame(resp,company,question,score,answer); current_2

Answer

Consider a merge on filtered subsets:

cols2keep <- c("resp", "company", "score", "answer")

df <- merge(current[current$question=='Quality', cols2keep],
            current[current$question=='Amount', cols2keep],
            by=c("resp", "company"), all=TRUE)

colnames(df) <- c("resp", "company", "quality", "quality_a", "amount", "amount_a")    
df

#         resp   company quality     quality_a amount amount_a
# 1 1325851107   Dark.nl       4   Didn't like      6    Maybe
# 2 1325851107 Manual.nl       5          Fine     NA     <NA>
# 3 1325851108   Dark.nl       1        Was ok      8     Fine
# 4 1325851108 Manual.nl       5 No, thank you     NA     <NA>
# 5 1325851109   Dark.nl       2          Sure     10  Not bad
# 6 1325851109 Manual.nl       7       Why not     NA     <NA>