1

I have used dcast to put a data.table into a wide format. Since I have many columns now (because I specified multiple variables in the var.values argument, I'd like to reorder the columns. This is an example for the data I have entered:

dt<-data.table(a_1=c(1,2,3), a_2=c(1,2,3), a_3=c(1,2,3), freq_1=c(1,2,3),freq_2=c(1,2,3), freq_3=c(1,2,3))

    a_1 a_2 a_3 freq_1 freq_2 freq_3
1:   1   1   1      1      1      1
2:   2   2   2      2      2      2
3:   3   3   3      3      3      3

This is how it should look like:

dt1<-data.table(a_1=c(1,2,3), freq_1=c(1,2,3), a_2=c(1,2,3), freq_2=c(1,2,3), a_3=c(1,2,3), freq_3=c(1,2,3))

   a_1 freq_1 a_2 freq_2 a_3 freq_3
1:   1      1   1      1   1      1
2:   2      2   2      2   2      2
3:   3      3   3      3   3      3

First hint was something like:

library("gtools")
cdat <- colsplit(names(dt),"\\_",c("name","num"))
dt<-dt[,order(mixedorder(cdat$name),cdat$num)]

But this did not work, unfortunately

Thank you very much for your help!

Antje Rosebrock
  • 15
  • 4

1 Answers1

2

You can use setcolorder, 

library(data.table)
setcolorder(dt, order(sub('.*_', '', names(dt))))

which gives,

   a_1 freq_1 a_2 freq_2 a_3 freq_3
1:   1      1   1      1   1      1
2:   2      2   2      2   2      2
3:   3      3   3      3   3      3
Sotos
  • 51,121
  • 6
  • 32
  • 66
  • Thank you very much! Since I have numbers from 1-146, it gets still messed up: I have a_1, freq_1, a_10, freq_10... and so on. Do you have any solution for that? Thank you!!! – Antje Rosebrock Feb 22 '18 at 15:51
  • It should work fine with that as well. Why should it matter if you have up to `freq_3` or `freq_100`? – Sotos Feb 22 '18 at 15:52
  • It just does, I think I would need to rename a_1 as a_001. Is it possible to do that automatically, thank you! – Antje Rosebrock Feb 22 '18 at 15:55
  • That should not matter. Please share an example that gives that problem. Otherwise I can not help any further – Sotos Feb 22 '18 at 15:55