CODEWITHSUNDEEP
rdata.table
Mixalis
Mixalis

Reputation: 542

Reorder all the columns in a data table based on Other files rows

I have 1 data.table(dt1) that has some ids sorted with my criteria. 

1. id10
2. id7757
3. id75340
4. id999
5. id5498

and a 2nd data.table(dt2) with the id's of the first data.table as column names.

SOURCE    id7757    id8948    id5498    id999     id10    id75340
source1   32        87        643       8676      34      10
source2   65        32        876       9457      8       777
source3   64        666       99        222       66      222

How can I short the columns of the 2nd data.table based on the order of the 1st data.table? Meaning that the id on the 1st position should be 1st column, 2nd position~2nd column etc...

The output file should be as follows:

SOURCE    id10    id7757    id75340    id999    id5498
source1   34      32        10         8676     643
source2   8       65        777        9476     32
source3   66      64        222        222      666

How can this be done in R?

Upvotes: 2

Views: 111

Answers (2)

Mixalis
Mixalis

Reputation: 542

it's actually very straight forward to do this. Turns out my dataset was faulty.

So first sanitize your datasets people!

the solution was the following:

all.sorted <- read.table("sorted.txt", header=T, sep=' ', na.strings="n/a")

sorted.ids <- unlist(all.sorted$ID)
sorted.ids.sanitized <- make.names(sorted.ids)

sorted.variants <- sorted.ids.sanitized # this step was just to check if all is good and keep a copy of the original
sorted.variants <- as.character(sorted.variants)

new.order <- dt2[, sorted.variants]

Upvotes: 1

Nate
Nate

Reputation: 10671

a base/dplyr approach:

new_order <- sapply(dt1$V2, grep, colnames(dt2)) %>% unlist()

select(dt2, SOURCE, new_order)

SOURCE id10 id7757 id75340 id999 id5498
1: source1   34     32      10  8676    643
2: source2    8     65     777  9457    876
3: source3   66     64     222   222     99

Upvotes: 0

Related Questions