CODEWITHSUNDEEP
rtidyr
user3115933
user3115933

Reputation: 4443

How can I convert this long format dataframe into a wide format?

I am using RStudio for data analysis in R. I currently have a dataframe which is in a long format. I want to convert it into the wide format.

An extract of the dataframe (df1) is shown below. I have converted the first column into a factor.

Extract:

df1 <- read.csv("test1.csv", stringsAsFactors = FALSE, header = TRUE)

df1$Respondent <- factor(df1$Respondent)

df1

      Respondent  Question      CS             Imp     LOS  Type  Hotel
1          1       Q1       Fully Applied     High     12   SML   ABC
2          1       Q2       Optimized         Critical 12   SML   ABC

I want a new dataframe (say, df2) to look like this:

Respondent      Q1CS           Q1Imp     Q2CS        Q2Imp     LOS   Type   Hotel
  1          Fully Applied      High    Optimized    Critical   12   SML    ABC

How can I do this in R?

Additional notes: I have tried looking at the tidyr package and its spread() function but I am having a hard time implementing it to this specific problem.

Upvotes: 0

Views: 63

Answers (2)

Wimpel
Wimpel

Reputation: 27732

In data.table, this can be done in a one-liner....

dcast(DT, Respondent ~ Question, value.var = c("CS", "Imp"), sep = "")[DT, `:=`(LOS = i.LOS, Type = i.Type, Hotel = i.Hotel), on = "Respondent"][]

   Respondent          CSQ1      CSQ2 ImpQ1    ImpQ2 LOS Type Hotel
1:          1 Fully Applied Optimized  High Critical  12  SML   ABC

explained step by step

create sample data

DT <- fread("Respondent  Question      CS             Imp     LOS  Type  Hotel
             1  Q1       'Fully Applied'     High     12   SML   ABC
            1   Q2       'Optimized'         Critical 12   SML   ABC", quote = '\'')

Cast a part of the datatable to desired format by question
colnames might not be what you want... you can always change them using setnames().

dcast(DT, Respondent ~ Question, value.var = c("CS", "Imp"), sep = "")
#    Respondent          CSQ1      CSQ2 ImpQ1    ImpQ2
# 1:          1 Fully Applied Optimized  High Critical

Then join by reference on the orikginal DT, to get the rest of the columns you need...

result.from.dcast[DT, `:=`( LOS = i.LOS, Type = i.Type, Hotel = i.Hotel), on = "Respondent"]

Upvotes: 1

Maurits Evers
Maurits Evers

Reputation: 50668

This can be achieved with a gather-unite-spread approach

df %>%
    group_by(Respondent) %>%
    gather(k, v, CS, Imp) %>%
    unite(col, Question, k, sep = "") %>%
    spread(col, v)
#  Respondent LOS Type Hotel          Q1CS Q1Imp      Q2CS    Q2Imp
#1          1  12  SML   ABC Fully Applied  High Optimized Critical

Sample data

df <- read.table(text =
    "      Respondent  Question      CS             Imp     LOS  Type  Hotel
1          1       Q1       'Fully Applied'     High     12   SML   ABC
2          1       Q2       'Optimized'         Critical 12   SML   ABC", header = T)

Upvotes: 1

Related Questions