Reputation: 2506
I want to change the row order according to a list of column elements. Here's an example of such a list:
scenChoice <- c("X2010", "SSP2-NoCC-REF", "SSP1-NoCC-REF", "SSP3-NoCC-REF", "SSP2-GFDL-REF", "SSP2-IPSL-REF", "SSP2-HGEM-REF")
Here's output from a data table I'm using:
scenario region_code value
SSP1-NoCC-REF lowInc 0.079897553
SSP2-GFDL-REF lowInc 0.119297969
SSP2-HGEM-REF lowInc 0.124730614
SSP2-IPSL-REF lowInc 0.121280208
SSP2-MIROC-REF lowInc 0.121459249
2010 lowInc 0.316646263
SSP2-NoCC-REF lowInc 0.115680977
SSP3-NoCC-REF lowInc 0.167769763
SSP1-NoCC-REF upMidInc 0.020520457
SSP2-GFDL-REF upMidInc 0.024871731
SSP2-HGEM-REF upMidInc 0.026223688
SSP2-IPSL-REF upMidInc 0.025594828
SSP2-MIROC-REF upMidInc 0.025523934
2010 upMidInc 0.063808085
SSP2-NoCC-REF upMidInc 0.023987357
SSP3-NoCC-REF upMidInc 0.027964746
SSP1-NoCC-REF highInc 0.014672749
SSP2-GFDL-REF highInc 0.015258928
SSP2-HGEM-REF highInc 0.016021142
SSP2-IPSL-REF highInc 0.015681295
SSP2-MIROC-REF highInc 0.015602455
2010 highInc 0.020245181
SSP2-NoCC-REF highInc 0.014812163
SSP3-NoCC-REF highInc 0.014574464
SSP1-NoCC-REF lowMidInc 0.033036779
SSP2-GFDL-REF lowMidInc 0.041298546
SSP2-HGEM-REF lowMidInc 0.043529098
SSP2-IPSL-REF lowMidInc 0.042395484
SSP2-MIROC-REF lowMidInc 0.042263571
2010 lowMidInc 0.092290532
SSP2-NoCC-REF lowMidInc 0.0395448
SSP3-NoCC-REF lowMidInc 0.04952689
I thought the following code would create a new column, scenarioOrder, with values between 1 and 6 depending on the position of the scenario value that I could then order the data table on.
DT[, scenarioOrder := which(scenario == scenChoice)]
If I run which(scenario == scenChoice)
outside the data table, it returns an integer between 1 and 6. But within the data table, it creates a new column with values of the row number for one of several occurrences of a specific scenario.
How can I create the scenarioOrder column with entries between 1 and 6 depending on where the row value is in the order for scenChoice?
Upvotes: 1
Views: 557
Reputation: 83275
You can use match
to achieve this:
DT[, scenarioOrder := match(scenario, scenChoice)]
which gives:
scenario region_code value scenarioOrder
1: SSP1-NoCC-REF lowInc 0.07989755 3
2: SSP2-GFDL-REF lowInc 0.11929797 5
3: SSP2-HGEM-REF lowInc 0.12473061 7
4: SSP2-IPSL-REF lowInc 0.12128021 6
5: SSP2-MIROC-REF lowInc 0.12145925 NA
6: 2010 lowInc 0.31664626 NA
7: SSP2-NoCC-REF lowInc 0.11568098 2
8: SSP3-NoCC-REF lowInc 0.16776976 4
9: SSP1-NoCC-REF upMidInc 0.02052046 3
10: SSP2-GFDL-REF upMidInc 0.02487173 5
11: SSP2-HGEM-REF upMidInc 0.02622369 7
12: SSP2-IPSL-REF upMidInc 0.02559483 6
13: SSP2-MIROC-REF upMidInc 0.02552393 NA
14: 2010 upMidInc 0.06380809 NA
15: SSP2-NoCC-REF upMidInc 0.02398736 2
16: SSP3-NoCC-REF upMidInc 0.02796475 4
17: SSP1-NoCC-REF highInc 0.01467275 3
18: SSP2-GFDL-REF highInc 0.01525893 5
19: SSP2-HGEM-REF highInc 0.01602114 7
20: SSP2-IPSL-REF highInc 0.01568130 6
21: SSP2-MIROC-REF highInc 0.01560245 NA
22: 2010 highInc 0.02024518 NA
23: SSP2-NoCC-REF highInc 0.01481216 2
24: SSP3-NoCC-REF highInc 0.01457446 4
25: SSP1-NoCC-REF lowMidInc 0.03303678 3
26: SSP2-GFDL-REF lowMidInc 0.04129855 5
27: SSP2-HGEM-REF lowMidInc 0.04352910 7
28: SSP2-IPSL-REF lowMidInc 0.04239548 6
29: SSP2-MIROC-REF lowMidInc 0.04226357 NA
30: 2010 lowMidInc 0.09229053 NA
31: SSP2-NoCC-REF lowMidInc 0.03954480 2
32: SSP3-NoCC-REF lowMidInc 0.04952689 4
Note: the first element of senChoice
is X2010
. You might want to change that to 2010
if you want matches on that element as well.
Upvotes: 6
Reputation: 326
I would make another data.table that includes the scenes and their order and then I would merge that data.table with the first one.
DT[.(scenario = scenChoice, id = seq_along(scenChoice)), on = 'scenario',
scenarioOrder := id]
Upvotes: 2