Forming Portfolios

[Peter Seel]

Dear Members,

I really struggle to find a solution to my Stata-Problem. I am quite new to using stata, so I hope, this is not one of those very obvious questions.
What I am trying to do is the following:
I have a data set looking something like that:

A	B	C	D	E	F	Ra	Rb	Rc	Rd	Re	Rf	P1	P2	P3	P4	P5
8	5	7	9	2	3	3	4	2	1	6	5

Meaning: I have data for certain industries (in this case A-F) and I already have another set of variables ranking these Instustries (Ra-Rf). What I would now like to do is, to build portfolios with these industries. To be precise, in this case I would like P1 to contain the largest and the second largest industrie i.e. Rank 1 and Rank 2. and the second Portfolio to contain only the third largest etc. (in my data, this portfolio would also contain a sum of industries).

Is there a clever or at all a way to do so?

Thanks for any advice in advance!

Kind Regards,

Peter

[Marcos Almeida]

Welcome to the Stata Forum / Statalist.

You will surely benefit from taking some time to read the FAQ, particularly on how to share command as well as data.

That said, I believe you could - reshape long - your data, then use - egen - with rank().

[Peter Seel]

Thanks Marcos,

Sorry if I did not followed to rules or recommendations for posting. Maybe as an additional explanation, I have for each industry 8xx observations (it is a timeseries). I then used the rowranks command to recieve the described Ra-Rf. As you can see in the example provided and the same is true for my original data, A-F and Ra-Rf are in the same order. What I am basically looking for is some sort of command, that first checks the Rank part and then takes the corresponding industry value. However, I have to use the same ranking but for other data, ie. I can not use the reshape long solution, because this would not work if I have to use this given rank order.

I hope this addition made my problem a little bit clearer.

Best regards,

Peter

[Nick Cox]

I really don't see why you can't reshape long. Anything ranked rowwise can be ranked again. Marcos is giving excellent advice which you're still ignoring.

Give us a data example we can use. People are much less likely to decode a word description, then to invent variable names and data, and finally to show you code.

[Peter Seel]

I am sorry, it wasn´t my intention to offend anybody.
An abstract of my data looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long v1 float(agric food smoke beer agric_size food_size smoke_size beer_size)
194501       2.49       2.72       2.48       2.46 4.892677  3.921379  4.418479   3.30064
194502       1.64       2.47       6.49   9.309999 4.917569  3.944684 4.4400597  3.316728
194503         .4      -2.99      -3.53      -1.56 4.934042  3.965943  4.497585 3.4031954
194504       7.65       5.62       7.79      15.62 4.927688 3.9324136 4.4585247 3.3870986
194505       1.69       2.29       2.39  -.6199999 5.001662  3.984716 4.5308776  3.525478
194506        .05       -.07        .33       6.42 5.018736  4.003325 4.5501857  3.516607
194507       3.31      -1.46       -.22      -7.93 5.009768  3.985645 4.5509253  3.609836
194508       4.27       6.41       10.7       4.95 5.042651 3.9685924 4.5460567  3.519869
194509        .99       7.94       1.23       14.8 5.084753 4.0280266  4.643525 3.5661466
194510       4.75       6.56         .3      39.06 5.085866 4.1183867  4.654722 3.7042606
194511       -.64       3.45       2.98       4.11 5.132499 4.1802163 4.6554832  4.028917
194512        .05          0      -1.94      19.45 5.126283 4.2087145  4.678978 4.0676584
194601        2.8       5.55  .57000005         .5 5.117814  4.206482  4.657193  4.245634
194602      -3.12      -3.94      -5.23      -6.57  5.14569 4.2580214 4.6605105 4.2463503
194603      14.36       4.37       8.54       8.88 5.114275  4.215086  4.601865 4.1774592
194604      13.66       1.71       3.86        .21 5.241059  4.255613 4.6825013  4.262539
194605       3.22       1.62       3.26       7.81 5.369335 4.2705364 4.7181416 4.2584457
194606       6.63      -4.23      -4.78       2.39 5.401325 4.2834487 4.7463226  4.332705
194607      -3.62 -.53999996       -.79        3.4 5.456474  4.206631  4.695559  4.414494
194608        .03      -5.18         -4      -6.48 5.419915 4.1994553 4.6853666 4.4402957
194609      -8.36      -8.88      -11.2      -17.1 5.420535 4.1432934  4.640151  4.372355
194610       -.67        .31       4.66       -3.9 5.322864  4.047078  4.520157 4.1849465
194611        .55       -.51  .51000005  -8.889999 5.316403  4.047253  4.563202 4.1346865
194612      11.97       3.24  4.1899996       1.49 5.322229 4.0358324 4.5602775 4.0398884
194701      -8.45  1.1600001       1.94      -8.28 5.425654  4.062338 4.5975413  4.054217
194702       5.51        .54      -4.83      -2.21  5.33773  4.070905  4.614328  3.955657
194703       1.62      -1.55      -2.76      -6.19  5.39158   4.07261  4.558498 3.9318256
194704      -3.23      -5.47  -8.349999     -11.26 5.388067  4.053349  4.528829  3.867862
194705      -1.65       -.57       3.57      -3.81 5.355595  3.994156 4.4386435 3.7309806
194706      12.15       5.11       5.57       8.37  5.33922 3.9839716 4.4685483  3.689629
194707       5.67        4.9       3.45  2.1000001 5.444753 4.0047846 4.5366774  3.667911
194708 -4.1400003      -1.01      -2.52      -3.72 5.500196 4.0502186  4.567468 3.6752875
194709       4.36 -1.0799999       -.75      11.75 5.458223  4.036009 4.5359273  3.633367
194710       4.36       2.31      -1.62      -4.63 5.474286 4.0215945  4.527425  3.744551
194711       -1.9      -2.69         .8      -1.98 5.517493 4.0418224  4.508219  3.684118
194712       -1.2        .11          1      -1.19  5.49897 4.0064235 4.5071163  3.659193
194801      -6.24      -2.62      -1.68      -3.21 5.478553  4.001132  4.512287 3.6480575
194802      -3.06      -7.25      -2.53  -8.549999 5.414811  3.970669 4.4924493 3.6014135
194803      12.77       5.59       -1.8      11.17 5.384495  3.891412 4.4577136 3.5073576
194804       1.52       1.26       -.97       -.81 5.478386  3.940805  4.437816 3.6117284
194805        .19       4.14       2.79      12.64 5.494295 3.9512436  4.425086  3.589059
194806      -2.86      -1.19      -1.68      -5.66 5.497005  3.987687 4.4462914  3.704014
194807       -.78      -3.58       4.29      -3.73 5.459586  3.956231 4.4837933 3.6146946
194808       1.77 -.59000003       1.81 .019999996 5.452582 3.9172094 4.5230923 3.5635996
194809      -4.01      -4.49      -3.19      -1.27 5.471052 3.9070106 4.5351768  3.558771
194810       7.12       3.34       3.41       5.27  5.40128 3.8554525  4.501253  3.546451
194811      -5.61      -6.28      -2.93      -7.91  5.47042  3.885679 4.5317388  3.584074
194812        .45       1.19        .75   .7399999 5.413074  3.811982 4.4897594  3.494688
194901       1.64       3.78       4.71  2.3400002 5.408158  3.816833 4.4952435   3.50255
194902       -.61      -2.74        .27       -1.8  5.42539  3.849722  4.538175 3.5124404
194903       2.53       4.56        .84       5.69 5.420137  3.818152  4.531093  3.488292
194904      -5.46      -1.52        .13      -2.08   5.4173  3.858622  4.536784 3.5438535
194905      -6.27       -.73        2.8       -1.1  5.36209 3.8409564 4.5354986  3.509454
194906       6.18       -.46       -.16       3.23 5.298418  3.829728  4.556925  3.492256
194907       3.32        5.5       3.01       7.15 5.348963  3.820346 4.5628886  3.524594
194908       2.92  4.6099997       3.42       4.42 5.382429  3.872034 4.5905643  3.581016
194909        .15       2.55       2.59       5.58 5.412092  3.913422 4.6183825  3.618457
194910  4.5099998       1.79      -1.48       2.76  5.38477  3.934958 4.6428514  3.673512
194911       2.52       1.74  2.1100001       4.41  5.42974 3.9502816 4.6260505  3.689629
194912       5.34        5.8  2.3600001  4.2599998 5.455449 3.9590974  4.636184  3.726657
195001       3.51       1.32  -.9400001       3.57 5.499379 4.0098753 4.6570983  3.769076
195002       2.94        1.4       1.34 -1.1600001 5.534693  4.019082  4.645544  3.794365
195003  .53999996 .009999998  -.7600001       -.39 5.564559  4.029984  4.649474  3.777806
195004        .34       -.55      -2.92       2.63 5.545529  4.026244  4.639765 3.7745984
195005       6.78       1.61        .39       -.29 5.549815 4.0189023 4.6077666  3.790533
195006      -4.54      -7.07      -4.58      -2.27 5.616371  4.031405   4.60597 3.7825975
195007      -6.95      -1.85      -1.99       5.86 5.558371  3.960051 4.5081086 3.7653775
195008      18.22       3.35        .25       3.12 5.487366  3.939054 4.4853725  3.815292
195009  2.3700001       3.94       4.67      10.03 5.655642 3.9672685 4.4815326  3.836653
195010       -.45       -.56  .03000001      -1.28 5.653436  4.001681  4.525694  3.933197
195011      -5.13        .03      -1.84        3.1   5.6501  3.993787  4.523743  3.910822
195012      -1.76       1.82      -1.49       6.29 5.598681  3.986017  4.494015 3.9324136
195101      14.03       2.99       3.59   8.559999 5.569565  3.999668  4.477905  3.990834
195102        2.5       1.58       -.87       -3.5 5.702014  4.025887 4.5108595  4.064229
195103        .53      -1.68      -1.69      -4.85 5.727695 4.0379505 4.4921136  4.028205
195104        .99        .19       -.72       4.87 5.708869 4.0180035 4.4731236 3.9763114
195105        .04      -1.48 -2.2099998       -.26 5.720016 4.0172834 4.4671717 4.0142183
195106      -4.06      -3.47       -3.8      -2.67  5.72159   3.99765  4.434619  4.010963
195107  2.3400002       3.13       2.94       5.14 5.670122  3.960432  4.426163    3.9718
195108       6.58       2.87       3.32       2.72 5.694136   3.98991  4.456438 4.0484753
195109      -6.35       -.05  .13999999       3.95 5.759059  4.013315  4.479153  4.073121
195110      -7.69      -3.06      -2.52      -3.66 5.676171 4.0091496 4.4789267 4.1064377
195111        -.5       -.13        .23      -1.78 5.597866  3.977436  4.455161 4.0606155
195112       5.43       1.75         .7        2.6  5.59393   3.96689  4.442416 4.0418224
195201      -1.14         .9       5.06      -4.19 5.636039  3.980242 4.4485164  4.065259
195202      -5.64       -.73      -2.72      -3.47 5.626073 3.9886136 4.4992537 4.0194416
195203       5.31        .81       1.75       -.33 5.569298  3.976124 4.4571342  3.978372
195204      -2.76      -2.03      -3.95      -9.45 5.602082 3.9808025  4.472781  4.051089
195205       4.67       1.97       -.58       1.64 5.575267  3.959288  4.433789  3.947583
195206       1.42       2.23       3.57       2.49 5.622102 3.9736824 4.4175143  3.956805
195207       -3.3        .86       -.79        5.5 5.625929  3.998751   4.28193 4.0078783
195208        3.7        .89       1.48       -.05 5.593781 4.0066056  4.275554  4.057853
195209      -6.43      -1.23 -.06999999     -12.75  5.63157  4.011144  4.280686   4.04987
195210      -5.02      -1.41       6.47      -2.47 5.545568 3.9948924  4.278609  3.910221
195211       2.65       4.92       7.93   9.549999 5.495528  3.979682  4.342506  3.880739
195212       1.71       1.36        .28       -.62  5.52262  4.020519  4.407938  3.967647
195301      -1.21       2.93       4.17        .15 5.528158 4.0303392 4.4100065  3.958143
195302      -5.68       1.15        5.5        .06 5.517573 4.0590625 4.4523687   3.95604
195303       3.82      -1.11 -4.6299996      -1.75 5.460564 4.0738015 4.4945736  3.953165
195304       -3.7       -.44       4.71      -3.87 5.486166  4.059581 4.4458227 3.9324136
end

I used

rowranks agric food smoke beer, gen (y1-y4)

What I am looking for now is a way to match my received ranks (y1-y4) with agric food smoke beer. The reason I am not sure if reshape is the best way to go is, that I also want, in a second step, the rank of y1-y4 to be used on agric_size food_size smoke_size beer_size, e.g. for the last value i.e. v1 (195304), rank 1 would be assigned to smoke and therefore the correspondent value would be 4.445 and not the 5.486, which would be the highest value of the _size variables if I would use the rank command onto them.

Thank you very much for your help!

Kind Regards,

Peter

[Nick Cox]

Thanks very much for the data example. Don't think that anyone is offended; rather you and we are just likely to be frustrated if a question isn't clear or easy to answer.

I still don't understand your last paragraph, but this shows some technique. Note that your date variable, although easy for people to understand, is not good for analyses, as even a simple line graph would show. I don't understand what these variables are; it's enough that they come in two batches of four. Sensible variable names are at your discretion.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long v1 float(agric food smoke beer agric_size food_size smoke_size beer_size)
194501       2.49       2.72       2.48       2.46 4.892677  3.921379  4.418479   3.30064
194502       1.64       2.47       6.49   9.309999 4.917569  3.944684 4.4400597  3.316728
194503         .4      -2.99      -3.53      -1.56 4.934042  3.965943  4.497585 3.4031954
194504       7.65       5.62       7.79      15.62 4.927688 3.9324136 4.4585247 3.3870986
194505       1.69       2.29       2.39  -.6199999 5.001662  3.984716 4.5308776  3.525478
194506        .05       -.07        .33       6.42 5.018736  4.003325 4.5501857  3.516607
194507       3.31      -1.46       -.22      -7.93 5.009768  3.985645 4.5509253  3.609836
194508       4.27       6.41       10.7       4.95 5.042651 3.9685924 4.5460567  3.519869
194509        .99       7.94       1.23       14.8 5.084753 4.0280266  4.643525 3.5661466
194510       4.75       6.56         .3      39.06 5.085866 4.1183867  4.654722 3.7042606
194511       -.64       3.45       2.98       4.11 5.132499 4.1802163 4.6554832  4.028917
194512        .05          0      -1.94      19.45 5.126283 4.2087145  4.678978 4.0676584
194601        2.8       5.55  .57000005         .5 5.117814  4.206482  4.657193  4.245634
194602      -3.12      -3.94      -5.23      -6.57  5.14569 4.2580214 4.6605105 4.2463503
194603      14.36       4.37       8.54       8.88 5.114275  4.215086  4.601865 4.1774592
194604      13.66       1.71       3.86        .21 5.241059  4.255613 4.6825013  4.262539
194605       3.22       1.62       3.26       7.81 5.369335 4.2705364 4.7181416 4.2584457
194606       6.63      -4.23      -4.78       2.39 5.401325 4.2834487 4.7463226  4.332705
194607      -3.62 -.53999996       -.79        3.4 5.456474  4.206631  4.695559  4.414494
194608        .03      -5.18         -4      -6.48 5.419915 4.1994553 4.6853666 4.4402957
194609      -8.36      -8.88      -11.2      -17.1 5.420535 4.1432934  4.640151  4.372355
194610       -.67        .31       4.66       -3.9 5.322864  4.047078  4.520157 4.1849465
194611        .55       -.51  .51000005  -8.889999 5.316403  4.047253  4.563202 4.1346865
194612      11.97       3.24  4.1899996       1.49 5.322229 4.0358324 4.5602775 4.0398884
194701      -8.45  1.1600001       1.94      -8.28 5.425654  4.062338 4.5975413  4.054217
194702       5.51        .54      -4.83      -2.21  5.33773  4.070905  4.614328  3.955657
194703       1.62      -1.55      -2.76      -6.19  5.39158   4.07261  4.558498 3.9318256
194704      -3.23      -5.47  -8.349999     -11.26 5.388067  4.053349  4.528829  3.867862
194705      -1.65       -.57       3.57      -3.81 5.355595  3.994156 4.4386435 3.7309806
194706      12.15       5.11       5.57       8.37  5.33922 3.9839716 4.4685483  3.689629
194707       5.67        4.9       3.45  2.1000001 5.444753 4.0047846 4.5366774  3.667911
194708 -4.1400003      -1.01      -2.52      -3.72 5.500196 4.0502186  4.567468 3.6752875
194709       4.36 -1.0799999       -.75      11.75 5.458223  4.036009 4.5359273  3.633367
194710       4.36       2.31      -1.62      -4.63 5.474286 4.0215945  4.527425  3.744551
194711       -1.9      -2.69         .8      -1.98 5.517493 4.0418224  4.508219  3.684118
194712       -1.2        .11          1      -1.19  5.49897 4.0064235 4.5071163  3.659193
194801      -6.24      -2.62      -1.68      -3.21 5.478553  4.001132  4.512287 3.6480575
194802      -3.06      -7.25      -2.53  -8.549999 5.414811  3.970669 4.4924493 3.6014135
194803      12.77       5.59       -1.8      11.17 5.384495  3.891412 4.4577136 3.5073576
194804       1.52       1.26       -.97       -.81 5.478386  3.940805  4.437816 3.6117284
194805        .19       4.14       2.79      12.64 5.494295 3.9512436  4.425086  3.589059
194806      -2.86      -1.19      -1.68      -5.66 5.497005  3.987687 4.4462914  3.704014
194807       -.78      -3.58       4.29      -3.73 5.459586  3.956231 4.4837933 3.6146946
194808       1.77 -.59000003       1.81 .019999996 5.452582 3.9172094 4.5230923 3.5635996
194809      -4.01      -4.49      -3.19      -1.27 5.471052 3.9070106 4.5351768  3.558771
194810       7.12       3.34       3.41       5.27  5.40128 3.8554525  4.501253  3.546451
194811      -5.61      -6.28      -2.93      -7.91  5.47042  3.885679 4.5317388  3.584074
194812        .45       1.19        .75   .7399999 5.413074  3.811982 4.4897594  3.494688
194901       1.64       3.78       4.71  2.3400002 5.408158  3.816833 4.4952435   3.50255
194902       -.61      -2.74        .27       -1.8  5.42539  3.849722  4.538175 3.5124404
194903       2.53       4.56        .84       5.69 5.420137  3.818152  4.531093  3.488292
194904      -5.46      -1.52        .13      -2.08   5.4173  3.858622  4.536784 3.5438535
194905      -6.27       -.73        2.8       -1.1  5.36209 3.8409564 4.5354986  3.509454
194906       6.18       -.46       -.16       3.23 5.298418  3.829728  4.556925  3.492256
194907       3.32        5.5       3.01       7.15 5.348963  3.820346 4.5628886  3.524594
194908       2.92  4.6099997       3.42       4.42 5.382429  3.872034 4.5905643  3.581016
194909        .15       2.55       2.59       5.58 5.412092  3.913422 4.6183825  3.618457
194910  4.5099998       1.79      -1.48       2.76  5.38477  3.934958 4.6428514  3.673512
194911       2.52       1.74  2.1100001       4.41  5.42974 3.9502816 4.6260505  3.689629
194912       5.34        5.8  2.3600001  4.2599998 5.455449 3.9590974  4.636184  3.726657
195001       3.51       1.32  -.9400001       3.57 5.499379 4.0098753 4.6570983  3.769076
195002       2.94        1.4       1.34 -1.1600001 5.534693  4.019082  4.645544  3.794365
195003  .53999996 .009999998  -.7600001       -.39 5.564559  4.029984  4.649474  3.777806
195004        .34       -.55      -2.92       2.63 5.545529  4.026244  4.639765 3.7745984
195005       6.78       1.61        .39       -.29 5.549815 4.0189023 4.6077666  3.790533
195006      -4.54      -7.07      -4.58      -2.27 5.616371  4.031405   4.60597 3.7825975
195007      -6.95      -1.85      -1.99       5.86 5.558371  3.960051 4.5081086 3.7653775
195008      18.22       3.35        .25       3.12 5.487366  3.939054 4.4853725  3.815292
195009  2.3700001       3.94       4.67      10.03 5.655642 3.9672685 4.4815326  3.836653
195010       -.45       -.56  .03000001      -1.28 5.653436  4.001681  4.525694  3.933197
195011      -5.13        .03      -1.84        3.1   5.6501  3.993787  4.523743  3.910822
195012      -1.76       1.82      -1.49       6.29 5.598681  3.986017  4.494015 3.9324136
195101      14.03       2.99       3.59   8.559999 5.569565  3.999668  4.477905  3.990834
195102        2.5       1.58       -.87       -3.5 5.702014  4.025887 4.5108595  4.064229
195103        .53      -1.68      -1.69      -4.85 5.727695 4.0379505 4.4921136  4.028205
195104        .99        .19       -.72       4.87 5.708869 4.0180035 4.4731236 3.9763114
195105        .04      -1.48 -2.2099998       -.26 5.720016 4.0172834 4.4671717 4.0142183
195106      -4.06      -3.47       -3.8      -2.67  5.72159   3.99765  4.434619  4.010963
195107  2.3400002       3.13       2.94       5.14 5.670122  3.960432  4.426163    3.9718
195108       6.58       2.87       3.32       2.72 5.694136   3.98991  4.456438 4.0484753
195109      -6.35       -.05  .13999999       3.95 5.759059  4.013315  4.479153  4.073121
195110      -7.69      -3.06      -2.52      -3.66 5.676171 4.0091496 4.4789267 4.1064377
195111        -.5       -.13        .23      -1.78 5.597866  3.977436  4.455161 4.0606155
195112       5.43       1.75         .7        2.6  5.59393   3.96689  4.442416 4.0418224
195201      -1.14         .9       5.06      -4.19 5.636039  3.980242 4.4485164  4.065259
195202      -5.64       -.73      -2.72      -3.47 5.626073 3.9886136 4.4992537 4.0194416
195203       5.31        .81       1.75       -.33 5.569298  3.976124 4.4571342  3.978372
195204      -2.76      -2.03      -3.95      -9.45 5.602082 3.9808025  4.472781  4.051089
195205       4.67       1.97       -.58       1.64 5.575267  3.959288  4.433789  3.947583
195206       1.42       2.23       3.57       2.49 5.622102 3.9736824 4.4175143  3.956805
195207       -3.3        .86       -.79        5.5 5.625929  3.998751   4.28193 4.0078783
195208        3.7        .89       1.48       -.05 5.593781 4.0066056  4.275554  4.057853
195209      -6.43      -1.23 -.06999999     -12.75  5.63157  4.011144  4.280686   4.04987
195210      -5.02      -1.41       6.47      -2.47 5.545568 3.9948924  4.278609  3.910221
195211       2.65       4.92       7.93   9.549999 5.495528  3.979682  4.342506  3.880739
195212       1.71       1.36        .28       -.62  5.52262  4.020519  4.407938  3.967647
195301      -1.21       2.93       4.17        .15 5.528158 4.0303392 4.4100065  3.958143
195302      -5.68       1.15        5.5        .06 5.517573 4.0590625 4.4523687   3.95604
195303       3.82      -1.11 -4.6299996      -1.75 5.460564 4.0738015 4.4945736  3.953165
195304       -3.7       -.44       4.71      -3.87 5.486166  4.059581 4.4458227 3.9324136
end

gen mdate = ym(floor(v1/100), mod(v1, 100)) 
format mdate %tm 
drop v1 
foreach v in agric food smoke beer { 
   rename `v' R`v' 
   rename `v'_size S`v' 
}

reshape long R S, i(mdate) j(which) string 

egen rankR = rank(R), by(mdate) 
egen rankS = rank(S), by(mdate) 
list in 1/16, sepby(mdate) 


     |  mdate   which          R          S   rankR   rankS |
     |------------------------------------------------------|
  1. | 1945m1   agric       2.49   4.892677       3       4 |
  2. | 1945m1    beer       2.46    3.30064       1       1 |
  3. | 1945m1    food       2.72   3.921379       4       2 |
  4. | 1945m1   smoke       2.48   4.418479       2       3 |
     |------------------------------------------------------|
  5. | 1945m2   agric       1.64   4.917569       1       4 |
  6. | 1945m2    beer   9.309999   3.316728       4       1 |
  7. | 1945m2    food       2.47   3.944684       2       2 |
  8. | 1945m2   smoke       6.49    4.44006       3       3 |
     |------------------------------------------------------|
  9. | 1945m3   agric         .4   4.934042       4       4 |
 10. | 1945m3    beer      -1.56   3.403195       3       1 |
 11. | 1945m3    food      -2.99   3.965943       2       2 |
 12. | 1945m3   smoke      -3.53   4.497585       1       3 |
     |------------------------------------------------------|
 13. | 1945m4   agric       7.65   4.927688       2       4 |
 14. | 1945m4    beer      15.62   3.387099       4       1 |
 15. | 1945m4    food       5.62   3.932414       1       2 |
 16. | 1945m4   smoke       7.79   4.458525       3       3 |
     +------------------------------------------------------+

[Peter Seel]

Thanks Marcos and Nick,
the combination of you two answeres solved the problem perfectly!