Reshape long complicated

[Federico Nutarelli]

Hi Statlist,

I have a question for you about reshaping command.

I have a quarterly sales dataset as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(idfirm idproduct idmol) str4 launchdate long(salesmnf2004Q1 salesmnf2004Q2 salesmnf2004Q3 salesmnf2004Q4 salesmnf2005Q1 salesmnf2005Q2 salesmnf2005Q3 salesmnf2005Q4 salesmnf2006Q1 salesmnf2006Q2 salesmnf2006Q3 salesmnf2006Q4)
 808 13953 1028 "2001"  5716246  6375542  7983257 10053402 11806618 12723593 14279204 15913230 17718268 18705307 20003340 20869268
 808 13953 1028 "2001"   378780   352123   393914   418006   462974   491951   479686   510912   487507   489807   477902   506023
 808 13953 1028 "2001"        0        0        0        0        0        0        0        0        0        0      287       96
 808 13953 1028 "2003"   350482   421342   539766   652069   814804   959097  1144756  1456739  1827808  2234537  2697249  3397319
 808 13953 1028 "2003"        0        0        0        0        0        0        0        0        0        0        0      199
 650  9361 1040 "1996" 12486040 12152422 11810541 11702229 12802176 12387685 12688401 12059114 12257384 12389969 12322742 13191780
 650  9361 1040 "1996"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9361 1040 "1996"   140184   166474   186330   200948   218968   222201   237880   177488   167284   187499   164714   157169
 650  9361 1040 "1996"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9361 1040 "1996"   105021    63521    31681    21991     2775     1003      112     2436      176        0       88       59
 650  9361 1040 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9361 1040 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9361 1040 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9361 1040 "1998"        0        0     1400      215        0        0        0        0        0      396       99      274
 650  9361 1040 "1998"  1024321  1040405  1059115  1086747  1138835  1153744  1150944  1172772  1144778  1169022  1229906  1308508
 650 10085 1036 "1983"  2949776  2627100  2545629  2560069  2646117  2533814  2757082  2443770  2409938  2317764  2427474  2609575
 650 10085 1036 "1983"        0        0        0        0        0        0        0        0        0        0        0        0
 650 10085 1036 "1983"   255795   259991   264256   270525   281910   319500   337160   314870   342580   318994   378586   352850
 808 10084 1036 "1983" 12227499 11903731 13202682 14546717 16251168 16801356 17134591 15456527 17104313 16467655 17022026 17272516
 808 10084 1036 "1983"      334        0        0        0        0        0        0        0        0        0        0        0
 808 10084 1036 "1983"   169321   166774   190934   191402   206652   196569   195465   193605   208565   193140   198623   194295
 808 10084 1036 "1986"      225      331       55        0        0        0        0        0        0        0        0        0
 808 10084 1036 "1995"       38        0        0        0        0        0        0        0        0        0        0        0
 808 10084 1036 "1999"     8164     7948     4175     2248        0       68       34        0        0        0        0        0
 808 10084 1036 "2002"    67812    90507    87340    77883   122810   179888   260359   361064   455530   528391   662583   782082
1035 10102 1036 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
1035 10102 1036 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
1035 10102 1036 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
1035 10102 1036 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
 623 10083 1036 "2013"        0        0        0        0        0        0        0        0        0        0        0        0
 893  7279 1036 "2006"        0        0        0        0        0        0        0        0        0        0        0        0
 893  7279 1036 "2006"        0        0        0        0        0        0        0        0        0        0        0        0
 893  7279 1036 "2006"        0        0        0        0        0        0        0        0        0        0   101145    13446
 893  7279 1036 "2006"        0        0        0        0        0        0        0        0        0        0    66956    11697
 893  7279 1036 "2006"        0        0        0        0        0        0        0        0        0        0    79048     5526
1035  1371 1035 "2006"        0        0        0        0        0        0        0        0    51779    91542   196068   267469
1035  1371 1035 "2006"        0        0        0        0        0        0        0        0    16403    24531    49991    59918
1035  1371 1035 "2009"        0        0        0        0        0        0        0        0        0        0        0        0
1035  1371 1035 "2009"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9374 1037 "1983"  3186476  2890937  2714809  2616672  2576408  2448324  2502946  2356987  2255355  2125749  2268401  2183309
 650  9374 1037 "1983"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9374 1037 "1983"        0        0        0        0       29       29        0       29        0        0        0       29
 650  9374 1037 "2014"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9374 1037 "1998"   215488   216493   208170   203183   182256   170896   167071   171229   148557   146066   143790   149126
 808 10088 1037 "1985"  9745781  8978707  9400777 10116617 11011991 11029793 11172571  9519342 10616424 10848561 10907625 10224116
 808 10088 1037 "1985"     7657     4976     3695      962      245        0        0        0        0        0        0        0
 808 10088 1037 "1985"   175447   171759   197362   198116   178033   157632   158973   154692   157678   156418   151145   142934
 808 10088 1037 "1995"     9979     8081     2426      720        0        0        0        0        0        0        0        0
 808 10088 1037 "2002"    79745    94856   100769   103937   110283   128695   146812   164039   181031   197596   223119   249390
 623 10083 1036 "2013"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9373 1036 "1989"  2876163  2631807  2490045  2371312  2341280  2265949  2380709  2282987  2264455  2096510  2193915  2215304
 650  9373 1036 "1989"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9373 1036 "2014"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9373 1036 "1992"   133302   128177   140164   128452   121662   116993   103116   115029    92347    85585    99139    99989
 650  9373 1036 "1998"   218726   205503   217436   218060   219137   196144   217168   209206   200016   187955   199080   195571
 808   340 1036 "1986"  9322483  9116945  9883534  9894260 10960938 10990087 11173859 10058948 10619274 10159334 10228110 10043921
 808   340 1036 "1986"      793      100       36        0        0        0        0        0        0        0        0        0
 808   340 1036 "1986"   320526   296264   313062   289665   283196   277645   247295   254976   241129   225586   250209   217598
 808   340 1036 "1993"      193      109        0       94        0        0        0        0        0        0        0        0
 808   340 1036 "2002"   158889   180064   171824   190925   175371   186628   179928   199396   201176   219133   251717   257079
 808 13950 1028 "2002"   391156   418972   457248   521923   588174   710121   787857   929612  1049919  1106812  1299209  1160470
 808 13950 1028 "2002"        0        0        0        0        0        0        0        0        0        0        0        0
 808 13950 1028 "2002"   644404   853662  1166635  1525859  1854937  2302339  2607470  3046103  3363282  3528097  3685133  3866392
 808 13950 1028 "2002"    92941    70646    81540    80243    74692    75693    73807    64431    72796    76686    76655    76750
 650  9362 1040 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9362 1040 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9362 1040 "2000"  3794216  3633524  3485343  3302116  3545529  3365885  3376023  3197038  3048721  2895560  2735258  2824567
 650  9362 1040 "2006"        0        0        0        0        0        0        0        0        0        0        0    13062
 650  9362 1040 "2000"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9362 1040 "2000"   728715   723192   692558   655694   682682   639549   656103   651307   624403   600193   621885   665619
 650  9362 1040 "2006"        0        0        0        0        0        0        0        0    13461    46442    61703    78471
 650  9372 1038 "1985"   514437   481280   511447   566531   443044   392679   419476   230721   146415   118296    58582        0
 808 10087 1038 "1983"     7880    16310    18873    11808     3140     1146      156       60       52       30       15       60
1035 10819 1034 "2001" 22466987 24112420 25906035 29754070 34505845 35907171 37373129 41993321 46282423 48128774 50244346 52511838
1035 10819 1034 "2001"        0        0        0        0        0        0        0        0      227      335      449      446
1035 10819 1034 "2001"        0        0        0        0   208184   411643   570144   898573  1214530  1522745  1863622  2198653
1035 10819 1034 "2007"        0        0        0        0        0        0        0        0        0        0        0        0
1035 10819 1034 "2007"        0        0        0        0        0        0        0        0        0        0        0        0
1035 10819 1034 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
 808 10916 1033 "2006"        0        0        0        0        0        0        0        0    74666   193106   448303   745157
 808 10916 1033 "2006"        0        0        0        0        0        0        0        0   128698   187590   388120   604865
 808 10916 1033 "2006"        0        0        0        0        0        0        0        0      249        0      124        0
 808 10916 1033 "2014"        0        0        0        0        0        0        0        0        0        0        0        0
 808 10916 1033 "2014"        0        0        0        0        0        0        0        0        0        0        0        0
 808 19760 1032 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
 808 19760 1032 "2015"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9375 1039 "1987"   378764   340963   332154   326496   301053   282926   266890   208735    57519      887     1051        0
 650  9937 1029 "1975"        0        0        0        0        0        0        0        0        0        0        0        0
 650 10086 1030 "1978"      391      980      456      472      251      123      595     1187      123        0        0        0
 650  9939 1031 "1977"        0        0        0        0        0        0        0        0        0        0        0        0
 650  9938 1031 "1980"     2520      352        0        0        0        0       38        0        0        0        0        0
 650  9937 1041 "1980"    46794    30131    22990    18053    17056    20652    30905    14698     4065        0        0        0
 650 10086 1042 "1980"    39621    63389    32087    28899    21265    19117    25519    15599     5323        0        0        0
 808 10097 1043 "1981"       26        0        0        0        0        0        0        0        0        0        0        0
 808 13948  632 "2008"        0        0        0        0        0        0        0        0        0        0        0        0
 808 13949  632 "1988"        0        0        0        0       43        0        0        0       38        0        0        0
 808 13948  632 "1995"        0        0        0        0        0        0        0        0        0        0        0        0
 808 13948  632 "2001"    61738    81922    86780   100581   116653   125595   140322   153224   167962   184236   183640   193547
 170 10099  632 "1991"    33622    33921    29010    58402    45053    45630    53127    53943    63295    69323    73483    72713
 170 10099  632 "1991"      345      503      102      247        0        0        0        0        0        0        0      986
end

I would like to reshape the dataset long so that I end up having for each product the quarterly sales. Now, notice that in the wide form the same product is repeated more than once without being a duplicate (the same product can be produced with different molecules), so that for instance if product 1 is repeated 4 times in the reshaped wide dataset from quarter 1 of 2004 to quarter 4 of 2006, I expect in the reshaped long dataset product 1 repeated 12 X 4 = 48 times:

idpr idf idmol quarter Year
1 45 3 2004Q1
1 45 3  2004Q2
1 45 3 2004Q3
1 45 3 2004Q4
1 45 3 2005Q1
1 45 3 2005Q2
1 45 3 2005Q3 
1 45 3 2005Q4
1 45 3 2006Q1
1 45 3 2006Q2
1 45 3 2006Q3
1 45 3 2006Q4
1 45 3 2004Q1
1 45 3 2004Q2
1 45 3 2004Q3
...

with the structure

1 45 3 2004Q1
1 45 3 2004Q1
1 45 3  2004Q2
1 45 3 2004Q3
1 45 3 2004Q4
1 45 3 2005Q1
1 45 3 2005Q2
1 45 3 2005Q3 
1 45 3 2005Q4
1 45 3 2006Q1
1 45 3 2006Q2
1 45 3 2006Q3
1 45 3 2006Q4

repeated as said above, 4 times since in the wide for product 1 is repeated 4 times.
To do that I implemented the following, but am unsure about its correct functioning (the dataset is too big and I cannot do a very precise check):

reshape long salesmnf, i(id_numeric) j(quarter) string 

gen trimestre = quarterly(quarter, "YQ")
format trimestre %tq 
gen Year = year(dofq(trimestre))

Do you think it does the job correctly?

Thank you!!!

Federico

[William Lisowski]

Your sample code includes the variable id_numeric.

Your sample data does not include id_numeric.

You do not describe how id_numeric is created.

The sample of what you expect for the output does not correspond to any observation in your input data, and also does not include id_numeric.

[Federico Nutarelli]

I am sorry. id_numeric is simply this:

gen id_numeric = _n

[Clyde Schechter]

It is hard to say if your code is "correct" because it isn't entirely clear what end result you want. It is certainly (including the line of code from #3) syntactically correct and produces a sensible data organization. Whether that data organization is the one you need, I can't tell. In #1 you allude to wanting total quarterly sales per product, and the results you have from #2 are not organized per product because they are disaggregated by molecule within product. If what you need is to put all the observations for a given firm and product together, regardless of the molecule, then you can follow what you've done with

collapse (sum) salesmnf, by(idfirm idproduct trimestre)

[Federico Nutarelli]

Well yes indt step. For the moment I just would like to know if the reshape I have implemented produces the last structure I allude in #1

[Clyde Schechter]

Yes, it does.

[Federico Nutarelli]

Perfect. Thank you very much. I am in debugging phase and need to be sure about the implemented procedures. For this reason, before going further with other questions, may I ask you if I can post such questions on Statlist? In my opinion they could be useful for the community but since are just "yes/no questions" about a past code, I would like to have the opinion of a tenured member.

thank you,


federico

[Clyde Schechter]

Anyone can post pretty much anything here. We rely on the Forum members to use their discretion to keep the Forum running on a collegial and professional plane. The Forum is not moderated, though posts are first filtered in search of spam. I am aware of only one instance where a post that passed the spam filters was removed, that one being due to inappropriate language.

Broadly speaking, anything about Stata or statistics is considered relevant here. Reasonable people might occasionally disagree about whether particular questions fall into those categories, but there has been little or no difficulty with people posting things that are inappropriate here. (Well, there was a problem with some spam-bots, but that's really a different issue, and it was resolved with better filters.)

If you post questions that people feel are not relevant, they will simply go unanswered. Or somebody might respond in the post or privately message you to suggest that you are misusing the Forum resources. Personally, I have not found your questions so far inappropriate: if I had, I wouldn't have responded to them.