Complete the missing data in Panel data

[Muhammad Arslan Iqbal]

Hi everyone,

I am working with panel data. The first dimension is year, second dimension in country. I have grouped these two to define a market. Now, within each market there are many car models and the code variable is artificially generated code for each car model and model variable is string variable for car name. The problem is that in each market i.e. (country , year group) not all car brands are sold. Secondly, the data is not arranged in terms of car models. I am trying to do the following two things.

1- Creating rows in each market for remaining models that are not sold. In total I have 356 models that are sold anytime during the studied period. I want to create these rows as "." or missing value.

2- I want to rearrange the rows in such a way, that for each market model no 1 appears in first row, and 2 in second row, and so on. I also want to save that in excel in the same format.

Any help is much appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(mkt code) str11 model
1 236 "33"       
1 262 "olympia"  
1 230 "2002"     
1  48 "escort"   
1 185 "corolla"  
1 228 "1502"     
1  43 "128"      
1 224 "60-75"    
1 240 "125"      
1 268 "viva"     
1 274 "1100"     
1 254 "primula"  
1 234 "d-ds 20"  
1 252 "fulvia"   
1 275 "1300"     
1 131 "4"        
1 290 "bagheera" 
1 148 "mini"     
1 116 "commodor" 
1 128 "304"      
1 229 "1800"     
1 250 "2000"     
1 225 "prinz1000"
1 222 "1200"     
1 259 "1000"     
1 219 "1750"     
1 270 "404"      
1 145 "12"       
1  88 "200"      
1 288 "1301"     
1  15 "2CV6"     
1 109 "kadet"    
1 144 "6"        
1 269 "204"      
1 227 "ro 80"    
1 285 "1000"     
1 238 "124"      
1 233 "ami"      
1  23 "dyane"    
1 169 "99"       
1 242 "500/600"  
1 221 "giulia"   
1 249 "N600"     
1 239 "124 S"    
1 231 "2500"     
1 294 "chamois"  
1 261 "admiral"  
1 278 "3500"     
1  53 "taunus"   
1 299 "140"      
end
label values code code
label def code 15 "26", modify
label def code 23 "36", modify
label def code 43 "64", modify
label def code 48 "71", modify
label def code 53 "77", modify
label def code 88 "134", modify
label def code 109 "165", modify
label def code 116 "174", modify
label def code 128 "187", modify
label def code 131 "196", modify
label def code 144 "213", modify
label def code 145 "214", modify
label def code 148 "217", modify
label def code 169 "241", modify
label def code 185 "269", modify
label def code 219 "400", modify
label def code 221 "402", modify
label def code 222 "404", modify
label def code 224 "406", modify
label def code 225 "407", modify
label def code 227 "409", modify
label def code 228 "410", modify
label def code 229 "411", modify
label def code 230 "412", modify
label def code 231 "413", modify
label def code 233 "417", modify
label def code 234 "418", modify
label def code 236 "422", modify
label def code 238 "429", modify
label def code 239 "430", modify
label def code 240 "431", modify
label def code 242 "434", modify
label def code 249 "447", modify
label def code 250 "452", modify
label def code 252 "455", modify
label def code 254 "458", modify
label def code 259 "468", modify
label def code 261 "478", modify
label def code 262 "481", modify
label def code 268 "488", modify
label def code 269 "490", modify
label def code 270 "491", modify
label def code 274 "499", modify
label def code 275 "500", modify
label def code 278 "503", modify
label def code 285 "521", modify
label def code 288 "524", modify
label def code 290 "526", modify
label def code 294 "530", modify
label def code 299 "541", modify

[Fei Wang]

For car model "1000", its code is "259" in some line but is "285" in another line. Is that an error? For sorting by car model numbers, did you mean the car model number by its original value or value labels? -- If you mean the latter, I would recommend directly recoding the original values of code with the value labels rather than assigning the labels to original values.

[Carlo Lazzaro]

Muhammad:
1) I was not able to spot -year- in your dara example/excerpt (BTW: thanks for using -dataex-);
2) there's no technical need to make up your data and obtain a balanced panel dataset, as Stata listwise deletes all the observations with missing values;
3) to order the observations according to your preference, you may want to try:

sort mkt model

[Muhammad Arslan Iqbal]

Thank you Fei for the reply. These are different because car brand is different although the model name is same. i.e. one is Nissan and the other one is Talbot. I mean sorting by market mkt and artificially generated code (Code) variable. I generated this code using the following:

*** Generating model code - easy to count
egen code= group(co), label

where (co) is the code of model provided in source data. However, it was not in any order. Hence, I used the above code command to make it easier to use in Matlab for further analysis. Also, the issue is to fill rows for missing models that are not sold in the given market.

[Fei Wang]

Thank you Fei for the reply. These are different because car brand is different although the model name is same. i.e. one is Nissan and the other one is Talbot. I mean sorting by market mkt and artificially generated code (Code) variable. I generated this code using the following:

*** Generating model code - easy to count
egen code= group(co), label

where (co) is the code of model provided in source data. However, it was not in any order. Hence, I used the above code command to make it easier to use in Matlab for further analysis. Also, the issue is to fill rows for missing models that are not sold in the given market.

The code below may be what you want.

xtset mkt code
tsfill, full
sort mkt code

[Muhammad Arslan Iqbal]

Dear Carlo, Thank you for the help. I did not provide year and country (ma) variable because i already grouped them to define the market;

egen mkt = group(ye ma)

However, It is provided below ;

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(ye ma) float mkt int co str11 model float code
70 1 1 422 "33"        236
70 1 1 481 "olympia"   262
70 1 1 412 "2002"      230
70 1 1  71 "escort"     48
70 1 1 269 "corolla"   185
70 1 1 410 "1502"      228
70 1 1  64 "128"        43
70 1 1 406 "60-75"     224
70 1 1 431 "125"       240
70 1 1 488 "viva"      268
70 1 1 499 "1100"      274
70 1 1 458 "primula"   254
70 1 1 418 "d-ds 20"   234
70 1 1 455 "fulvia"    252
70 1 1 500 "1300"      275
70 1 1 196 "4"         131
70 1 1 526 "bagheera"  290
70 1 1 217 "mini"      148
70 1 1 174 "commodor"  116
70 1 1 187 "304"       128
70 1 1 411 "1800"      229
70 1 1 452 "2000"      250
70 1 1 407 "prinz1000" 225
70 1 1 404 "1200"      222
70 1 1 468 "1000"      259
70 1 1 400 "1750"      219
70 1 1 491 "404"       270
70 1 1 214 "12"        145
70 1 1 134 "200"        88
70 1 1 524 "1301"      288
70 1 1  26 "2CV6"       15
70 1 1 165 "kadet"     109
70 1 1 213 "6"         144
70 1 1 490 "204"       269
70 1 1 409 "ro 80"     227
70 1 1 521 "1000"      285
70 1 1 429 "124"       238
70 1 1 417 "ami"       233
70 1 1  36 "dyane"      23
70 1 1 241 "99"        169
70 1 1 434 "500/600"   242
70 1 1 402 "giulia"    221
70 1 1 447 "N600"      249
70 1 1 430 "124 S"     239
70 1 1 413 "2500"      231
70 1 1 530 "chamois"   294
70 1 1 478 "admiral"   261
70 1 1 503 "3500"      278
70 1 1  77 "taunus"     53
70 1 1 541 "140"       299
70 1 1 497 "8-10"      273
70 1 1 172 "record"    114
70 1 1 544 "17M"       300
70 1 1 453 "a111"      251
70 1 1 535 "411-412"   295
70 1 1 408 "prinz4"    226
70 1 1 291 "kaefer"    198
70 1 1 186 "504"       127
70 1 1 435 "850"       243
70 1 1 419 "ds 21-23"  235
70 1 1 536 "berline"   296
70 1 1  15 "100/200"    12
70 1 1 437 "12-15M"    244
70 1 1 439 "20-26M"    245
70 1 1 212 "16"        143
70 2 2  77 "taunus"     53
70 2 2 455 "fulvia"    252
70 2 2 541 "140"       299
70 2 2 413 "2500"      231
70 2 2 431 "125"       240
70 2 2  36 "dyane"      23
70 2 2 434 "500/600"   242
70 2 2 402 "giulia"    221
70 2 2 499 "1100"      274
70 2 2  71 "escort"     48
70 2 2 535 "411-412"   295
70 2 2 439 "20-26M"    245
70 2 2 458 "primula"   254
70 2 2 172 "record"    114
70 2 2 214 "12"        145
70 2 2 187 "304"       128
70 2 2 524 "1301"      288
70 2 2 411 "1800"      229
70 2 2  64 "128"        43
70 2 2 447 "N600"      249
70 2 2 400 "1750"      219
70 2 2 522 "1200S"     286
70 2 2 196 "4"         131
70 2 2 452 "2000"      250
70 2 2 521 "1000"      285
70 2 2 407 "prinz1000" 225
70 2 2 536 "berline"   296
70 2 2 491 "404"       270
70 2 2 404 "1200"      222
70 2 2 174 "commodor"  116
70 2 2 481 "olympia"   262
70 2 2 412 "2002"      230
70 2 2 165 "kadet"     109
70 2 2 212 "16"        143
70 2 2 213 "6"         144
end
label values ma market
label def market 1 "Belgium", modify
label def market 2 "France", modify
label values code code
label def code 12 "15", modify
label def code 15 "26", modify
label def code 23 "36", modify
label def code 43 "64", modify
label def code 48 "71", modify
label def code 53 "77", modify
label def code 88 "134", modify
label def code 109 "165", modify
label def code 114 "172", modify
label def code 116 "174", modify
label def code 127 "186", modify
label def code 128 "187", modify
label def code 131 "196", modify
label def code 143 "212", modify
label def code 144 "213", modify
label def code 145 "214", modify
label def code 148 "217", modify
label def code 169 "241", modify
label def code 185 "269", modify
label def code 198 "291", modify
label def code 219 "400", modify
label def code 221 "402", modify
label def code 222 "404", modify
label def code 224 "406", modify
label def code 225 "407", modify
label def code 226 "408", modify
label def code 227 "409", modify
label def code 228 "410", modify
label def code 229 "411", modify
label def code 230 "412", modify
label def code 231 "413", modify
label def code 233 "417", modify
label def code 234 "418", modify
label def code 235 "419", modify
label def code 236 "422", modify
label def code 238 "429", modify
label def code 239 "430", modify
label def code 240 "431", modify
label def code 242 "434", modify
label def code 243 "435", modify
label def code 244 "437", modify
label def code 245 "439", modify
label def code 249 "447", modify
label def code 250 "452", modify
label def code 251 "453", modify
label def code 252 "455", modify
label def code 254 "458", modify
label def code 259 "468", modify
label def code 261 "478", modify
label def code 262 "481", modify
label def code 268 "488", modify
label def code 269 "490", modify
label def code 270 "491", modify
label def code 273 "497", modify
label def code 274 "499", modify
label def code 275 "500", modify
label def code 278 "503", modify
label def code 285 "521", modify
label def code 286 "522", modify
label def code 288 "524", modify
label def code 290 "526", modify
label def code 294 "530", modify
label def code 295 "535", modify
label def code 296 "536", modify
label def code 299 "541", modify
label def code 300 "544", modify

Actually, I have to export it to MATLAB for further analysis and there I need the dimensions to be balanced, otherwise the coding would be hard (because I am not that proficient). Can you please guide, how can I insert row for missing models identified by variable (code) if a code (for car) not sold in the market mkt which is (year country group). The objective is to get balanced panel with missing values rows for cars which are not sold in a given market.

[Carlo Lazzaro]

Muhammad:
thanks for clarifying.
Fei's helpful code will do the trick.
I am not familiar with MATLAB; that said, some concerns still hold about creating a full balanced panel from an unbalanced one (and possibly run regression analysis on the resulting dataset after the make-up).