Collecting multiway tabulations of medians using table into wider table with collect command

[Rober Martinez-Espineira]

Dear Statalist users,


I have a dataset like the one shown with dataex at the bottom of this post.

A summarizing yields


. sum

Variable | Obs Mean Std. dev. Min Max
-------------+---------------------------------------------------------
idnum | 54 819.7778 623.1845 122 1861
country | 0
index1 | 45 .0093131 .0205261 0 .077334
index2 | 45 .1258338 .1240493 0 .2992322
index3 | 45 .1282634 .127473 0 .3099134
-------------+---------------------------------------------------------
incomeq5 | 45 .6 .653661 0 2


I can use this data to obtain a table like this


. table (country) (incomeq5) , stat(median index*) nototals nformat( %8.4f)

------------------------------------------
| incomeq5
| 0 1 2
---------------+--------------------------
Country |
Lalaland |
index1 | 0.0000 0.0031 0.0062
index2 | 0.2461 0.0243 0.0171
index3 | 0.2461 0.0243 0.0171
TheOtherland |
index1 | 0.0000 0.0651
index2 | 0.2955 0.1193
index3 | 0.3036 0.1193
------------------------------------------

But I would like to have one row per country (2 rows) and 9 columns (3 for each of the indices) in a table containing the medians of index 1 in the first 3 columns, of index 2 in the next 3 and of index3 in the last 3.

I have tried to save a table for each index, appending them and then using the new collect command but I just cannot find the way to line up the 3 columns from each table into a wider table.


. table (country) (incomeq5) , stat(median index1) nototals nformat( %8.2f) name(table1) replace

------------------------------------
| incomeq5
| 0 1 2
---------------+--------------------
Country |
Lalaland | 0.00 0.00 0.01
TheOtherland | 0.00 0.07
------------------------------------

Any hint would be greatly appreciated

Rober


Dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input double idnum str18 country float(index1 index2 index3 incomeq5)
 122 "Lalaland"               0          0          0 0
 122 "Lalaland"               .          .          . .
 122 "Lalaland"               0          0          0 0
 122 "Lalaland"               0          0          0 0
 122 "Lalaland"               0          0          0 0
 122 "Lalaland"               0          0          0 0
 147 "Lalaland"               .          .          . .
 147 "Lalaland"               0  .29686132   .3073778 0
 147 "Lalaland"               0  .29134855    .302008 0
 147 "Lalaland"               0  .29546025   .3020446 0
 147 "Lalaland"               0  .29258612   .3035543 0
 147 "Lalaland"               0   .2992322   .3099134 0
 183 "Lalaland"               0          0          0 1
 183 "Lalaland"               0          0          0 1
 183 "Lalaland"               0          0          0 2
 183 "Lalaland"               .          .          . .
 183 "Lalaland"               0          0          0 1
 183 "Lalaland"               0          0          0 1
 620 "Lalaland"      .013224094 .025919225 .025919225 1
 620 "Lalaland"      .009113645 .017862745 .017862745 2
 620 "Lalaland"      .008384038 .016432714 .016432714 2
 620 "Lalaland"       .01436254  .02815058  .02815058 1
 620 "Lalaland"      .011611014  .02275759  .02275759 1
 620 "Lalaland"               .          .          . .
1148 "Lalaland"               .          .          . .
1148 "Lalaland"     .0018681057  .28208396  .28208396 0
1148 "Lalaland"     .0020906783  .29686132   .3073778 0
1148 "Lalaland"       .00170781   .2578793   .2578793 0
1148 "Lalaland"     .0014615288  .22069083  .22069083 1
1148 "Lalaland"     .0016300724   .2461409   .2461409 0
1625 "Lalaland"               0          0          0 0
1625 "Lalaland"               0          0          0 0
1625 "Lalaland"               .          .          . .
1625 "Lalaland"               0          0          0 1
1625 "Lalaland"               0          0          0 0
1625 "Lalaland"               0          0          0 1
1861 "Lalaland"      .004745767  .12338994  .12338994 1
1861 "Lalaland"      .005873268  .15270497  .15270497 1
1861 "Lalaland"     .0040535135  .10539135  .10539135 2
1861 "Lalaland"               .          .          . .
1861 "Lalaland"      .005535867  .14393254  .14393254 1
1861 "Lalaland"      .006623199  .17220317  .17220317 1
 516 "TheOtherland"  .062586315  .11474157  .11474157 1
 516 "TheOtherland"   .05555225   .1018458   .1018458 1
 516 "TheOtherland"   .07733403  .14177905  .14177905 1
 516 "TheOtherland"  .065086894  .11932597  .11932597 1
 516 "TheOtherland"   .06624459   .1214484   .1214484 1
 516 "TheOtherland"           .          .          . .
1156 "TheOtherland"           .          .          . .
1156 "TheOtherland"           0  .29546025   .3020446 0
1156 "TheOtherland"           0  .29134855    .302008 0
1156 "TheOtherland"           0  .29258612   .3035543 0
1156 "TheOtherland"           0   .2992322   .3099134 0
1156 "TheOtherland"           0  .29686132   .3073778 0
end

[Maarten Buis]

To see how Stata sees the rows and columns of a table you can type collect layout

. table (country) (incomeq5) , stat(median index*) nototals nformat( %8.4f)

------------------------------------------
               |          incomeq5        
               |       0        1        2
---------------+--------------------------
country        |                          
  Lalaland     |                          
    index1     |  0.0000   0.0031   0.0062
    index2     |  0.2461   0.0243   0.0171
    index3     |  0.2461   0.0243   0.0171
  TheOtherland |                          
    index1     |  0.0000   0.0651         
    index2     |  0.2955   0.1193         
    index3     |  0.3036   0.1193         
------------------------------------------

. collect layout

Collection: Table
      Rows: country#result#var
   Columns: incomeq5
   Table 1: 9 x 3

------------------------------------------
               |          incomeq5        
               |       0        1        2
---------------+--------------------------
country        |                          
  Lalaland     |                          
    index1     |  0.0000   0.0031   0.0062
    index2     |  0.2461   0.0243   0.0171
    index3     |  0.2461   0.0243   0.0171
  TheOtherland |                          
    index1     |  0.0000   0.0651         
    index2     |  0.2955   0.1193         
    index3     |  0.3036   0.1193         
------------------------------------------

The rows are country#result#var and columns are incomeeq6. To change that such that only the countries are on the rows we need to change that to just country and the columns to incomeq5#result#var

. collect layout (country) (incomeq5#result#var)

Collection: Table
      Rows: country
   Columns: incomeq5#result#var
   Table 1: 3 x 9

------------------------------------------------------------------------------------------------
               |                                     incomeq5                                   
               |              0                          1                          2           
               |  index1   index2   index3   index1   index2   index3   index1   index2   index3
---------------+--------------------------------------------------------------------------------
country        |                                                                                
  Lalaland     |  0.0000   0.2461   0.2461   0.0031   0.0243   0.0243   0.0062   0.0171   0.0171
  TheOtherland |  0.0000   0.2955   0.3036   0.0651   0.1193   0.1193                           
------------------------------------------------------------------------------------------------

[Rober Martinez-Espineira]

Thanks so very much, Maarten

Rober