# Load data
columns_to_load = [
"countyfip", # FIPS
"rfrnc_yr", # Year
"rfrnc_qtroy", # Quarter
"d_pc_qwi_payroll", # Earnings (annual diff)
"hms_deep", # Number of smoke days
"fe_countyqtroy",
"fe_styr",
"fe_stqtros",
"seer_pop", # Population
]
county_df = pd.read_csv(
"data/county_quarter.tab",
sep="\t",
usecols=columns_to_load,
)
# Rename columns
column_rename_dict = {
"countyfip":"fips",
"rfrnc_yr":"year",
"rfrnc_qtroy":"quarter",
"d_pc_qwi_payroll":"diff_payroll",
"hms_deep":"smoke_days",
"fe_countyqtroy":"fe_id_county_quarter",
"fe_styr":"fe_id_state_year",
"fe_stqtros":"fe_id_state_quarter",
"seer_pop":"population",
}
county_df = county_df.rename(columns=column_rename_dict)
# Drop missing rows
county_df.dropna(inplace=True)
# Subtract state-year averages
county_df[["diff_payroll_no_state_year", "smoke_days_no_state_year"]] = (
county_df.groupby("fe_id_state_year")[["diff_payroll", "smoke_days"]]
.transform(lambda x: x-x.mean())
)