Reshape dataset to Panel Data

[Alessandro Corvasce]

Goodmorning everyone,

I have a large dataset (around 18 millions) which collects reports of natural disasters around the USA.

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 damagedzipcode str24 declarationdate str16 incidenttype float(propdmg persdmg) str41 renterdamagelevel byte destroyed int waterlevel
"78541" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  3204.88       0 "" 0  0
"78382" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78380" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   585.33       0 "" 0  2
"78576" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1758.13 4858.84 "" 0  0
"78336" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1789.22       0 "" 0 10
"78538" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  2420.03 1111.97 "" 0  0
"78408" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    51.62       0 "" 0  0
"78380" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78595" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   486.18  336.78 "" 0  0
"77701" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 3630.48 "" 0  2
"77078" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77640" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1075.14 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  3
"77705" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78539" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  2912.07       0 "" 0  0
"77705" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  3863.32 3330.43 "" 0  2
"78405" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78577" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   505.74       0 "" 0  4
"78408" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78404" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   892.32       0 "" 0  0
"77364" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78541" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   260.86       0 "" 0  0
"78543" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  4717.91  453.53 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1886.84 "" 0 36
"78516" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   852.08 1195.84 "" 0  0
"77040" "2002-11-05T05:00:00.000Z" "Severe Storm(s)" 10934.03  1215.1 "" 0 14
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  5480.91       0 "" 0  7
"78570" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   1558.3       0 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1290.75 "" 0  0
"75862" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   5102.1 8781.71 "" 0 10
"78408" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 6634.14 "" 0  0
"77662" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  5012.32       0 "" 0  1
"77656" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1614.37 "" 0  2
"77705" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 2463.13 "" 0  1
"78390" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   935.21 2340.68 "" 0  0
"78380" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78539" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"     87.3       0 "" 0  0
"78595" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    98.08  513.34 "" 0  1
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78501" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1801.38       0 "" 0  0
"77705" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  2801.1 "" 0  0
"78415" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78336" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1064.01       0 "" 0  0
"78415" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   324.78       0 "" 0  0
"78539" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 4585.94 "" 0 14
"78411" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1350.08 "" 0  0
"78596" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   203.53       0 "" 0  0
"78416" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1945.79       0 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    58.48       0 "" 0  0
"78407" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 2850.02 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  6582.4 "" 0  8
"78516" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   520.16  771.43 "" 0  0
"78405" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78405" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  462.56 "" 0  0
"78539" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  2464.34 1195.84 "" 0  1
"78405" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    46.18       0 "" 0  0
"78405" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1393.46       0 "" 0  1
"78387" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   254.76       0 "" 0  0
"78390" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  2519.44 2258.58 "" 0  6
"77702" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1239.83 "" 0  0
"78416" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1818.69  571.15 "" 0  0
"77701" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1669.73  342.66 "" 0  2
"78390" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  1755.5 "" 0  0
"77016" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  464.01 "" 0  1
"75956" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   297.57       0 "" 0  0
"78390" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  3092.44       0 "" 0  5
"78380" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   736.12       0 "" 0  0
"78574" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    426.8       0 "" 0  1
"78380" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77640" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   114.92  342.66 "" 0  0
"75951" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   158.98       0 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"    958.8  341.87 "" 0  0
"77656" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1324.26 "" 0  0
"77703" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 1324.26 "" 0  0
"78596" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1056.81       0 "" 0  0
"78415" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   412.58       0 "" 0  0
"77703" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1934.84 2162.76 "" 0  2
"77703" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1098.28       0 "" 0  1
"77055" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  5273.44  361.78 "" 0  0
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0 6277.25 "" 0 16
"78336" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   512.67       0 "" 0  0
"78387" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  211.67 "" 0  0
"77701" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  6269.03 2450.06 "" 0  6
"78543" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"78407" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  3787.24       0 "" 0  0
"78368" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77076" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  1655.32 1463.03 "" 0  2
"78538" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   477.75       0 "" 0  0
"78543" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   1697.7       0 "" 0  0
"77703" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  2381.54  321.32 "" 0  2
"78416" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77701" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"   330.58       0 "" 0  0
"78572" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"  4352.76 3075.79 "" 0  2
"77060" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
"77705" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0  799.84 "" 0  6
"78516" "2002-11-05T05:00:00.000Z" "Severe Storm(s)"        0       0 "" 0  0
end

I would like to get a panel dataset where my observation are recorded yearly at the zipcode level. So I would like to get a yearly sum of all the damages occurred at a certain zipcode, or the number of storms that hit that zipcode. How can I reshape properly my data?

Thank you very much!!

[Øyvind Snilsberg]

gen year = real(substr(declarationdate,1,4))
gen storms = 1
collapse (sum) *dmg storms, by(damagedzipcode year)

[Alessandro Corvasce]

thank you very much for your help, it has been very fruitful!
I had completely forgotten about collapse, my bad