Crosstable with multiple items in rows and columns

Question

Let's say I have data that comes from averaging a variable called metric_1 by four different groups (xi, delta, scenario and model) giving a new variable called mean_metric1. The data would look like the following.

set.seed(777)
xi        =   c(2, 4 )  
delta     =   c( 0.5, 1 )
scenario  = c("scenario_1","scenario_2")
model <- c("model_1", "model_2", "model_3")
df <- expand.grid(xi = xi, 
                   delta = delta,
                   scenario = scenario,
                   model = model)
df$mean_metric1 <- rnorm(nrow(df), 100 , 25)
   xi delta   scenario   model mean_metric1
1   2   0.5 scenario_1 model_1    112.24466
2   4   0.5 scenario_1 model_1     90.03647
3   2   1.0 scenario_1 model_1    112.77091
4   4   1.0 scenario_1 model_1     90.02970
5   2   0.5 scenario_2 model_1    140.96715
6   4   0.5 scenario_2 model_1    115.53185
7   2   1.0 scenario_2 model_1    105.06761
8   4   1.0 scenario_2 model_1    127.72344
9   2   0.5 scenario_1 model_2     94.84438
10  4   0.5 scenario_1 model_2     90.52587
11  2   1.0 scenario_1 model_2     92.39346
12  4   1.0 scenario_1 model_2    101.35406
13  2   0.5 scenario_2 model_2     52.97673
14  4   0.5 scenario_2 model_2     99.15609
15  2   1.0 scenario_2 model_2    157.78737
16  4   1.0 scenario_2 model_2    124.30850
17  2   0.5 scenario_1 model_3    124.11520
18  4   0.5 scenario_1 model_3     86.39669
19  2   1.0 scenario_1 model_3    116.78058
20  4   1.0 scenario_1 model_3    112.52047
21  2   0.5 scenario_2 model_3     49.23410
22  4   0.5 scenario_2 model_3    105.69380
23  2   1.0 scenario_2 model_3     80.42437
24  4   1.0 scenario_2 model_3    131.83980

I want to create a cross table out of this information using two variables in the columns and two variables in the rows as illustrated in object wanted.

wanted <- structure(list(V1 = c("delta_0.5", "xi_2", "112.24466", "94.84438","124.1152", "140.96715", "52.97673", "49.2341"), 
               V2 = c("delta_0.5","xi_4", "90.03647", "90.52587", "86.39669", "115.53185", "99.15609","105.6938"), 
               V3 = c("delta_1", "xi_2", "112.77091", "92.39346","116.78058", "105.06761", "157.78737", "80.42437"), 
               V4 = c("delta_1","xi_4", "90.0297", "101.35406", "112.52047", "127.72344", "124.3085","131.8398")), 
          class = "data.frame", row.names = c(" ", "", "model_1_scenario_1","model_2_scenario_1", "model_3_scenario_1", "model_1_scenario_2","model_2_scenario_2", "model_3_scenario_2"))

                          V1        V2        V3        V4
                   delta_0.5 delta_0.5   delta_1   delta_1
                        xi_2      xi_4      xi_2      xi_4
model_1_scenario_1 112.24466  90.03647 112.77091   90.0297
model_2_scenario_1  94.84438  90.52587  92.39346 101.35406
model_3_scenario_1  124.1152  86.39669 116.78058 112.52047
model_1_scenario_2 140.96715 115.53185 105.06761 127.72344
model_2_scenario_2  52.97673  99.15609 157.78737  124.3085
model_3_scenario_2   49.2341  105.6938  80.42437  131.8398

If you have character elements in a column, that convert the whole column to character — akrun, Mar 04 '21 at 17:09
R doesn't have multi-line/nested column names like you're describing in your desired output (except for `ftable` or functions to print tables in reports), but the general data organization you've shown can be gotten using `library(data.table); as.data.table(df)[, dcast(.SD, scenario + model ~ xi + delta, value.var="mean_metric1")]`. — A5C1D2H2I1M1N2O1R2T1, Mar 04 '21 at 17:09
For reference, the `ftable` approach could be something like: `ftable(xtabs(mean_metric1 ~ scenario + model + xi + delta, df), col.vars=3:4)` — A5C1D2H2I1M1N2O1R2T1, Mar 04 '21 at 17:14
@A5C1D2H2I1M1N2O1R2T1 thanks a lot for your comments. Additionally, the `ftable` approach you provided does exactly what I wanted. If you are willing to turn this into an answer I would be very happy to accept it. — Álvaro A. Gutiérrez-Vargas, Mar 04 '21 at 17:19

score 3 · Accepted Answer · answered Mar 04 '21 at 17:22

You can use ftable in conjunction with xtabs:

ftable(xtabs(mean_metric1 ~ scenario + model + xi + delta, df), col.vars=3:4)
                   xi            2                   4          
                   delta       0.5         1       0.5         1
scenario   model                                                
scenario_1 model_1       112.24466 112.77091  90.03647  90.02970
           model_2        94.84438  92.39346  90.52587 101.35406
           model_3       124.11520 116.78058  86.39669 112.52047
scenario_2 model_1       140.96715 105.06761 115.53185 127.72344
           model_2        52.97673 157.78737  99.15609 124.30850
           model_3        49.23410  80.42437 105.69380 131.83980

xtabs is required since using ftable directly would just give a count of the the combinations. Note that xtabs will sum duplicated combinations.

Crosstable with multiple items in rows and columns

1 Answers1

Linked