CODEWITHSUNDEEP
rdata.table
akh22
akh22

Reputation: 701

Group/Aggregate data.table columns by a ref from another data.table file.

I am trying to aggregate columns of a following data:

       Label    A  B   C  D E
  1:     a    0.00 0 0.00 0 1
  2:     b    0.00 0 0.00 0 2
  3:     c    0.00 0 0.00 0 3
  4:     d    0.00 0 0.11 0 4
  5:     e    0.44 0 4.00 0 5

into two groups by the following ref data;

ID Exp
1:  A Dis
2:  B Con
3:  C Con
4:  D Con
5:  E Dis

So a desired column output is;

Label    A E  B C D

Any help or pointers to accomplish this is really appreciated.

Upvotes: 1

Views: 57

Answers (1)

akrun
akrun

Reputation: 887148

We can order based on the 'Exp' column, extract the 'ID' and use that reorder the dataset

dt[, c("Label", refdt[order(-Exp)]$ID), with = FALSE]

Or with setcolorder

setcolorder(dt, c("Label", refdt[order(-Exp)]$ID))
dt
#   Label    A E B    C D
#1:     a 0.00 1 0 0.00 0
#2:     b 0.00 2 0 0.00 0
#3:     c 0.00 3 0 0.00 0
#4:     d 0.00 4 0 0.11 0
#5:     e 0.44 5 0 4.00 0

As @Frank mentioned, it is better to extract the column from inside the []

setcolorder(dt, c("Label", refdt[order(-Exp), ID]))

NOTE: Assuming both the datasets are data.table

Upvotes: 3

Related Questions