Counting events in panel data to create new dataset.

[Bart MJ Visser]

Hi all,

I'm new to this forum so excuse me if I don't follow the rules entirely correct.

I Have a large dataset from the U.S. Storm Events Database. This is a database which records all extreme weather events in the U.S. Out of this I want to create a panel data set that contains the amount of extreme weather events per county per year. So an example of the panel data set I want to create:

state county year #Thunderstorm Wind #Drought #Heavy Rain
alabama Shelby 2010 5 3 2
alabama Shelby 2011 2 1 7
California Kings County 2010 0 11 3
California Kings County 2011 7 12 8

So what I think I have to do is create nested looped to count per county per year per kind of extreme weather the amount of events. And then I want to print this to a new file(or is this possible to do in the same file?) so that I can construct a new dataset as described above. I'm struggling how to do this in Stata, also because I almost have 1 million observations. So can anyone help me? If you need extra information please let me know! Either way, thanks in advance!


Below is an example of the dataset that I have extracted from the U.S. Storm Events Database.


input long(episode_id event_id) str20 state byte state_fips int year str9 month_name str26 event_type str1 cz_type int cz_fips strL cz_name int(injuries_direct injuries_indirect deaths_direct) byte deaths_indirect str8 damage_property str18 damage_crops
8577 49137 "ALABAMA" 1 2007 "July" "Drought" "Z" 38 "CHAMBERS" 0 0 0 0 "0.00K" ""
96381 597060 "ALABAMA" 1 2015 "July" "Thunderstorm Wind" "C" 103 "MORGAN" 0 0 0 0 "0.00K" "0.00K"
1202262 5482183 "ALABAMA" 1 2005 "December" "Hail" "C" 117 "SHELBY" 0 0 0 0 "" ""
5177 30333 "ALABAMA" 1 2007 "April" "Frost/Freeze" "Z" 32 "HALE" 0 0 0 0 "0.00K" ""
1167743 5390900 "ALABAMA" 1 2004 "April" "Thunderstorm Wind" "C" 25 "CLARKE" 0 0 0 0 "15K" ""
186237 1143103 "ALABAMA" 1 2023 "November" "Drought" "Z" 24 "JEFFERSON" 0 0 0 0 "0.00K" "0.00K"
1182043 5425411 "ALABAMA" 1 2004 "November" "Thunderstorm Wind" "C" 111 "RANDOLPH" 0 0 0 0 "75K" ""
1186169 5445131 "ALABAMA" 1 2005 "March" "Funnel Cloud" "C" 101 "MONTGOMERY" 0 0 0 0 "" ""
1195742 5466786 "ALABAMA" 1 2005 "July" "Thunderstorm Wind" "C" 121 "TALLADEGA" 0 0 0 0 "2K" ""
156155 942238 "ALABAMA" 1 2021 "February" "Winter Storm" "Z" 22 "PICKENS" 0 0 0 0 "0.00K" "0.00K"
1220357 5530904 "ALABAMA" 1 2006 "August" "Drought" "Z" 29 "RANDOLPH" 0 0 0 0 "" ""
1217331 5522895 "ALABAMA" 1 2006 "July" "Thunderstorm Wind" "C" 75 "LAMAR" 0 0 0 0 "10K" ""
1219619 5527688 "ALABAMA" 1 2006 "August" "Heavy Rain" "C" 77 "LAUDERDALE" 0 0 0 0 "" ""
1192430 5459960 "ALABAMA" 1 2005 "June" "Strong Wind" "Z" 25 "SHELBY" 0 0 0 0 "3K" ""
1197913 5473437 "ALABAMA" 1 2005 "August" "Thunderstorm Wind" "C" 123 "TALLAPOOSA" 0 0 0 0 "3K" ""
1195963 5467083 "ALABAMA" 1 2005 "July" "Thunderstorm Wind" "C" 71 "JACKSON" 0 0 0 0 "" ""
28061 164629 "ALABAMA" 1 2009 "June" "Thunderstorm Wind" "C" 43 "CULLMAN" 0 0 0 0 "2.00K" "0.00K"
176876 1075760 "ALABAMA" 1 2023 "January" "Thunderstorm Wind" "C" 39 "COVINGTON" 0 0 0 0 "" "0.00K"
1220357 5530900 "ALABAMA" 1 2006 "August" "Drought" "Z" 44 "MONTGOMERY" 0 0 0 0 "" ""
37665 220083 "ALABAMA" 1 2010 "March" "Lightning" "C" 59 "FRANKLIN" 0 0 0 0 "15.00K" "0.00K"
6229 36336 "ALABAMA" 1 2007 "May" "Drought" "Z" 44 "MONTGOMERY" 0 0 0 0 "0.00K" ""
50455 316058 "ALABAMA" 1 2011 "April" "Thunderstorm Wind" "C" 9 "BLOUNT" 0 0 0 0 "20.00K" "0.00K"
1221427 5533923 "ALABAMA" 1 2006 "September" "Drought" "Z" 30 "SUMTER" 0 0 0 0 "" ""
1186214 5445461 "ALABAMA" 1 2005 "March" "Hail" "C" 63 "GREENE" 0 0 0 0 "" ""
11087 61017 "ALABAMA" 1 2007 "September" "Drought" "Z" 48 "RUSSELL" 0 0 0 0 "0.00K" ""
8380 48018 "ALABAMA" 1 2007 "July" "Flash Flood" "C" 103 "MORGAN" 0 0 0 0 "0.00K" "0.00K"
1180690 5423286 "ALABAMA" 1 2004 "September" "High Wind" "Z" 25 "SHELBY" 0 0 0 0 "1.2M" "0"
50911 328456 "ALABAMA" 1 2011 "June" "Thunderstorm Wind" "C" 89 "MADISON" 0 0 0 0 "8.00K" "0.00K"
40570 236137 "ALABAMA" 1 2010 "May" "Thunderstorm Wind" "C" 73 "JEFFERSON" 0 0 0 0 "0.00K" "0.00K"

[David Beheshti]

* Creating count variables
gen thunderstorm_count = 1 if event_type=="Thunderstorm Wind"
gen drought_count = 1 if event_type=="Drought"
gen heavy_rain_count = 1 if event_type=="Heavy Rain"

* Adding up the counts
collapse (sum) *count ///
         (first) state cz_name ///
         , by(cz_fips year)

Your example data doesn't have counties, it has commuting zones, so I assume that's the level at which you want your panel.

[Bart MJ Visser]

Thanks!

[Nick Cox]

You can save yourself some typing with statements like

 
 gen thunderstorm_count = event_type == "Thunderstorm Wind"

See e.g. https://www.stata.com/support/faqs/d...rue-and-false/

https://journals.sagepub.com/doi/pdf...36867X19830921 esp. Section 5.