Advice regarding panel data and collapse

[Ramadan Aly]

Hello Everyone,

I have a situation with respect to my data. I have weekly data from the top 200 songs streamed in Spotify for a total of 47 countries per week. So at the end when I append the 47 countries in one file to obtain a single file I get the following situation:

id artist country date
1 a x w1
1 a y w1
1 a z w1
1 a xx w1
2 b x w1
2 b y w1
2 b z w1
2 b xx w1

And so forth for the period of two years. So basically what I have is the same id in the same country repeated n times depending on how many songs they have in that country in that week, repeated around 47 countries for a total of 104 weeks. So basically I don't know how to set up the data for a xtset or a tsset, or if even doing that is necessary. Besides that, I tried to collapse the data so to have the percentages of how many times by country they are names but still I have the problem that the id ends up repeated through the different dates.

Any pieces of advice?

Cordially,
Ramadan Aly

[Clyde Schechter]

Would I be correct in assuming that the variable id is an identifier for songs?

Whether or how you need to -xtset- your data depends on what analyses you have in mind. Please elaborate.

I tried to collapse the data so to have the percentages of how many times by country they are names

I have no idea what this means.

[Ramadan Aly]

Dear Clyde Schechter,

First of all I am really sorry for this extremely late reply. Yes it was, I notice that the data set had a bad merge so I re did it and now I have no longer that problem. Nonetheless, I have another issue and maybe you could help me. This data set is made to be used in a gravity model to understand the trade in music. I have run the regression but, the estimates I get (coeffcients) are just too big for a log variable (the dependant variable is in log). I am not sure of what is going on.

Here you can find the resutls:

 reg lstreams lgdpconst_imp lgdpconst_exp ldist lpop_tot_imp contig comlang_off colony comcol home internet_imp, robust

Linear regression                                      Number of obs =    3792
                                                       F( 10,  3781) =   74.62
                                                       Prob > F      =  0.0000
                                                       R-squared     =  0.1649
                                                       Root MSE      =  7.1291

-------------------------------------------------------------------------------
              |               Robust
     lstreams |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
--------------+----------------------------------------------------------------
lgdpconst_imp |  -.2236225   .2784473    -0.80   0.422    -.7695439    .3222988
lgdpconst_exp |   1.294248   .0722942    17.90   0.000     1.152509    1.435987
        ldist |  -1.672021   .1242693   -13.45   0.000    -1.915662    -1.42838
 lpop_tot_imp |   .0931592   .3106246     0.30   0.764    -.5158488    .7021671
       contig |   .4628573   .6167356     0.75   0.453    -.7463093    1.672024
  comlang_off |  -.9853822   .4071173    -2.42   0.016    -1.783573   -.1871915
       colony |   4.961804   .7555045     6.57   0.000     3.480568     6.44304
       comcol |  -7.943685    1.20193    -6.61   0.000    -10.30018   -5.587191
         home |   2.064422   .9244948     2.23   0.026     .2518655    3.876979
 internet_imp |   .0162361   1.681968     0.01   0.992    -3.281417    3.313889
        _cons |  -8.335912   3.067869    -2.72   0.007    -14.35075   -2.321074
-------------------------------------------------------------------------------

The main variables are:

-lstreams: logarithm of the number of streams
-lgdpconst_imp/exp: logarithm of the constant gdp
-ldist: log of distance
-lpop_tot_imp: log of the total population of the importer
-internet_imp: is the porcentage of internet access in the country in percentage
The rest are dummies.

When I add fixed effects I have the following:

 reg lstreams lgdpconst_imp lgdpconst_exp ldist lpop_tot_imp contig comlang_off colony comcol home internet_imp dimp* dexp*, robust
note: dimp25 omitted because of collinearity
note: dexp31 omitted because of collinearity

Linear regression                                      Number of obs =    3792
                                                       F(104,  3687) =  295.93
                                                       Prob > F      =  0.0000
                                                       R-squared     =  0.7177
                                                       Root MSE      =  4.1977

-------------------------------------------------------------------------------
              |               Robust
     lstreams |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
--------------+----------------------------------------------------------------
lgdpconst_imp |  -4.779969   7.266596    -0.66   0.511    -19.02691    9.466975
lgdpconst_exp |    10.2511   7.058105     1.45   0.146    -3.587074    24.08927
        ldist |   -.679207   .1064768    -6.38   0.000    -.8879662   -.4704479
 lpop_tot_imp |  -33.36245   17.93793    -1.86   0.063    -68.53169    1.806785
       contig |   .2874681    .397527     0.72   0.470    -.4919263    1.066863
  comlang_off |   2.555379   .2963845     8.62   0.000     1.974285    3.136473
       colony |   .5296488   .4545907     1.17   0.244    -.3616252    1.420923
       comcol |   1.705284   1.847692     0.92   0.356    -1.917315    5.327883
         home |    5.65193   .6261811     9.03   0.000     4.424235    6.879625
 internet_imp |   1.223391   4.914636     0.25   0.803    -8.412282    10.85906
        dimp1 |   176.9734   82.69612     2.14   0.032     14.83876     339.108
        dimp2 |   122.8572   55.37271     2.22   0.027     14.29307    231.4214
        dimp3 |   161.9402   73.07176     2.22   0.027     18.67513    305.2052
        dimp4 |   130.7733   59.72418     2.19   0.029     13.67766     247.869
        dimp5 |   116.3572   61.50427     1.89   0.059    -4.228504     236.943
        dimp6 |   235.9175   109.1073     2.16   0.031       22.001    449.8341
        dimp7 |   175.7773   79.74765     2.20   0.028     19.42348    332.1312
        dimp8 |     122.86   55.07474     2.23   0.026     14.88007      230.84
        dimp9 |   144.2106   67.82235     2.13   0.034     11.23756    277.1836
       dimp10 |   178.3974   84.64399     2.11   0.035     12.44375     344.351
       dimp11 |   92.32034   46.04731     2.00   0.045     2.039625    182.6011
       dimp12 |   125.8143   58.31641     2.16   0.031     11.47867    240.1499
       dimp13 |   205.8597   93.68339     2.20   0.028     22.18334    389.5361
       dimp14 |   105.9844   48.27203     2.20   0.028     11.34193    200.6269
       dimp15 |   118.0913   58.86017     2.01   0.045     2.689572     233.493
       dimp16 |   136.6429   67.00691     2.04   0.041     5.268695    268.0172
       dimp17 |   182.5111   83.67077     2.18   0.029     18.46556    346.5566
       dimp18 |   105.1687   47.32157     2.22   0.026     12.38972    197.9478
       dimp19 |   198.4106    90.0198     2.20   0.028      21.9171    374.9041
       dimp20 |   119.5942   58.54767     2.04   0.041     4.805216    234.3832
       dimp21 |   109.9298    59.0842     1.86   0.063    -5.911087    225.7708
       dimp22 |   119.4004   57.01616     2.09   0.036     7.614046    231.1867
       dimp23 |   241.2861   113.6384     2.12   0.034      18.4857    464.0865
       dimp24 |   100.0774   45.44635     2.20   0.028     10.97495    189.1799
       dimp25 |          0  (omitted)
       dimp26 |   192.5458   88.25041     2.18   0.029     19.52141    365.5703
       dimp27 |   73.06823   35.93627     2.03   0.042     2.611303    143.5252
       dimp28 |   57.72585   29.85534     1.93   0.053    -.8087511    116.2605
       dimp29 |   8.171625   6.633156     1.23   0.218     -4.83339    21.17664
       dimp30 |    215.925   100.4922     2.15   0.032     18.89933    412.9507
       dimp31 |   95.18748   53.24338     1.79   0.074    -9.201894    199.5769
       dimp32 |   141.2033   66.82714     2.11   0.035     10.18149    272.2251
       dimp33 |   104.7222   47.20378     2.22   0.027      12.1741    197.2703
       dimp34 |   96.38176    44.8129     2.15   0.032     8.521239    184.2423
       dimp35 |   85.63116   42.79006     2.00   0.045     1.736645    169.5257
       dimp36 |   161.2757   77.46372     2.08   0.037     9.399707    313.1516
       dimp37 |   204.4511   98.15751     2.08   0.037     12.00269    396.8994
       dimp38 |   172.5951    80.0769     2.16   0.031     15.59567    329.5944
       dimp39 |   124.1243   57.83454     2.15   0.032     10.73346    237.5151
       dimp40 |   101.8909   52.31994     1.95   0.052    -.6879563    204.4698
       dimp41 |    128.277   57.78978     2.22   0.026     14.97396    241.5801
       dimp42 |   99.80771   46.98591     2.12   0.034     7.686773    191.9286
       dimp43 |   97.62728   51.96717     1.88   0.060     -4.25995    199.5145
       dimp44 |   187.0328   90.62704     2.06   0.039     9.348732    364.7169
       dimp45 |   201.3087   93.14039     2.16   0.031     18.69692    383.9204
       dimp46 |   198.0552   89.76394     2.21   0.027     22.06334    374.0471
       dimp47 |   259.9872   117.2265     2.22   0.027     30.15203    489.8224
       dimp48 |   80.08262   39.54261     2.03   0.043      2.55508    157.6102
        dexp1 |  -41.44479   25.34293    -1.64   0.102    -91.13233    8.242763
        dexp2 |  -31.30094   25.01363    -1.25   0.211    -80.34286    17.74099
        dexp3 |  -49.92484    33.2088    -1.50   0.133    -115.0343    15.18457
        dexp4 |  -38.66007   26.42705    -1.46   0.144    -90.47315      13.153
        dexp5 |  -19.52612   5.741979    -3.40   0.001    -30.78388    -8.26835
        dexp6 |  -51.85163   36.77139    -1.41   0.159    -123.9459    20.24263
        dexp7 |  -46.24088   35.33041    -1.31   0.191      -115.51     23.0282
        dexp8 |  -49.35732   27.97887    -1.76   0.078    -104.2129    5.498265
        dexp9 |  -28.07433   21.81211    -1.29   0.198    -70.83932    14.69067
       dexp10 |  -46.19853    23.9628    -1.93   0.054    -93.18018    .7831191
       dexp11 |  -21.05115    9.55243    -2.20   0.028    -39.77972   -2.322581
       dexp12 |  -27.12648   20.90505    -1.30   0.195    -68.11308    13.86012
       dexp13 |  -63.64123    40.4767    -1.57   0.116    -143.0001    15.71769
       dexp14 |  -45.58706   23.75276    -1.92   0.055    -92.15691    .9827926
       dexp15 |  -30.19718   12.91482    -2.34   0.019    -55.51807   -4.876291
       dexp16 |  -23.17243    13.7167    -1.69   0.091     -50.0655    3.720638
       dexp17 |  -44.64377    33.8087    -1.32   0.187    -110.9294    21.64184
       dexp18 |  -26.96165    21.5262    -1.25   0.210    -69.16609    15.24278
       dexp19 |  -61.21189   38.37242    -1.60   0.111    -136.4452    14.02137
       dexp20 |  -42.07247   21.07624    -2.00   0.046    -83.39471   -.7502281
       dexp21 |  -13.91519   3.616916    -3.85   0.000    -21.00654   -6.823832
       dexp22 |  -32.21511   17.87677    -1.80   0.072    -67.26443    2.834214
       dexp23 |  -54.14776   31.62301    -1.71   0.087    -116.1481    7.852559
       dexp24 |  -33.01366   23.51782    -1.40   0.160    -79.12288    13.09555
       dexp25 |  -.0372249   2.567085    -0.01   0.988    -5.070271    4.995821
       dexp26 |  -61.43514   36.21895    -1.70   0.090    -132.4463    9.576006
       dexp27 |  -24.14054   9.496499    -2.54   0.011    -42.75945   -5.521633
       dexp28 |  -20.26997   6.258225    -3.24   0.001     -32.5399   -8.000047
       dexp29 |  -5.799746   .9740098    -5.95   0.000    -7.709397   -3.890095
       dexp30 |   -43.4411    32.6923    -1.33   0.184    -107.5379    20.65568
       dexp31 |          0  (omitted)
       dexp32 |   -54.9008   30.37994    -1.81   0.071    -114.4639    4.662354
       dexp33 |  -33.31963   25.76153    -1.29   0.196    -83.82788    17.18863
       dexp34 |  -34.58447   18.87102    -1.83   0.067    -71.58314    2.414195
       dexp35 |  -24.69469   9.437051    -2.62   0.009    -43.19704   -6.192333
       dexp36 |  -39.16062   19.56921    -2.00   0.045    -77.52817   -.7930735
       dexp37 |  -40.86137   22.60554    -1.81   0.071    -85.18196    3.459218
       dexp38 |  -36.15663   27.42627    -1.32   0.187    -89.92879    17.61553
       dexp39 |  -33.64918   20.89151    -1.61   0.107    -74.60924    7.310883
       dexp40 |  -20.66373   7.557435    -2.73   0.006     -35.4809   -5.846571
       dexp41 |  -35.60956   27.04634    -1.32   0.188    -88.63682     17.4177
       dexp42 |  -18.38257   15.26458    -1.20   0.229    -48.31043    11.54529
       dexp43 |  -13.24413   4.076987    -3.25   0.001     -21.2375   -5.250756
       dexp44 |  -46.34726   24.92302    -1.86   0.063    -95.21153    2.517008
       dexp45 |  -54.67144   32.26884    -1.69   0.090     -117.938    8.595088
       dexp46 |  -49.73699   38.26737    -1.30   0.194    -124.7643     25.2903
       dexp47 |  -67.03476   51.08916    -1.31   0.190    -167.2006    33.13105
       dexp48 |  -20.30303   9.750216    -2.08   0.037    -39.41938   -1.186683
        _cons |   317.2544   204.6165     1.55   0.121    -83.91835    718.4271
-------------------------------------------------------------------------------

And as you can see the coefficients are just too big, I can't have a "-33.36" when I am using logarithms.

Do you have any advices?

[Clyde Schechter]

I don't think I can help you with this. I have only an educated layman's knowledge of economics and I don't understand much about these variables or what kind of relationships should be expected among them. I agree that your coefficients seem very large given that the dependent variable is log-transformed. But I also note that there are both large positive and large negative coefficients, so that the net result may well be predicted outcomes that are perfectly sensible. Have you looked at the predicted outcomes and how well they match with the observed ones?

Whether this is a reasonable model overall is a substantive economics question about which I am unable to advise you.

My advice to you is to repost this question as a New Topic in the General Forum, for several reasons. First, it digresses from the original question asked. While it is easy to be lulled into thinking of participation in this Forum as a dialog between a questioner and a responder, in fact there are bystanders who read to learn, and others who come searching specific questions. So it is important that the content of posts be accurately characterized by the thread titles so that all who read the Forum can allocate their time rationally to the topics that are relevant to their needs. In addition, by reposting with a new title that calls attention to the actual content of your current question, you may attract the interest of some of the many economists who follow this Forum and have the necessary knowledge to provide you with guidance. I think it is highly unlikely that an economist who posts here frequently and has expertise on the gravity model, is going to be attracted to a post about the collapse command. But there is a good chance he will read one that mentions the gravity model in its title.

[Ramadan Aly]

Yeah, you are right. I did it already and hopefully someone can give me a hand as I do not know how to tackle this. Thank you very much!

[Nick Cox]

The duplicate alluded to in #5 is at https://www.statalist.org/forums/for...-gravity-model

Any comments on #3 to #5 should, I suggest, be posted there.