2

I have a data.frame in long form. I want to reshape to wide, but I care about the ordering of the columns.

Here is a toy example. In reality, I will have many more groups (grp).

# Minimum working example

mwe <- "grp mean sd label

1  C90   90 19 Heart_rate
2  D20   86 18 Heart_rate
3  H09   80 16 Heart_rate
9  Z89   89 18 Heart_rate"

# Read in the text data

dd <- read.table(text=mwe, header=TRUE)

I tried this solution without success. 

dcast(setDT(dd), label~grp, value.var=list("mean", "sd"), sep="_" )

    label mean_C90 mean_D20 mean_H09 mean_Z89 sd_C90 sd_D20 sd_H09 sd_Z89
1: Heart_rate       90       86       80       89     19     18     16     18

What I want are the columns after label ordered by the grp then by mean and sd ... like this

    label mean_C90 sd_C90 mean_D20 sd_D20 mean_H09 sd_H09 mean_Z89 sd_Z89

I am stuck!

Community
  • 1
  • 1
drstevok
  • 715
  • 1
  • 6
  • 15

2 Answers2

7

You need to melt dd first, so that the values for mean and sd are in the same column:

dcast(melt(dd), label ~ grp + variable)
#       label C90_mean C90_sd D20_mean D20_sd H09_mean H09_sd Z89_mean Z89_sd
#1 Heart_rate       90     19       86     18       80     16       89     18
mtoto
  • 23,919
  • 4
  • 58
  • 71
3

One quick way to solve this is to rearrange the columns afterwards using setcolorder:

setcolorder(temp, c("label", paste(c("mean","sd"), 
             rep(unique(gsub("^(mean|sd)_", "", names(temp)[-1])), each=2), sep="_")))

This is a bit compact, so here are the pieces:

  • gsub("^(mean|sd)_", "", names(temp)[-1]) prints out the names of the variables (except "label") with "mean_" and "sd_" removed. This result is wrapped in unique to remove duplicate names.
  • The result above is fed to rep with the each=2 argument so that it is repeated twice.
  • These repeated values are pasted to "mean_" and "sd_" to construct the desired order of the columns.
  • "label" is concatenated to the front of this string vector.

A nice thing about setcolorder is that it changes the data by reference, so that it does not involve any copying of the data.table (ie, it is super fast).

lmo
  • 37,904
  • 9
  • 56
  • 69