R In data table change row order

Question

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?

Answer 1

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.

Answer 2

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]