"""
Created on Wed Jul  2 14:06:29 2025

@author: shaphanng
"""
from zipfile import ZipFile
import pandas as pd 
import os

##HD database
#clean HD data from 1997-2022
#county fips code not reported from 2000-2008
cd = r"C:\Users\shaphanng\Dropbox\TaxSubsidy\replication\unidata\\"
year = 2009
while year < 2023: 
    file_name = cd + "HD"+str(year)+"_Data_Stata.zip"
    zip_read = ZipFile(file_name)
    namelist = zip_read.namelist()
    df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')  
    
    df.columns = df.columns.str.lower()   
    
    df1 = df[['unitid', 'instnm', 'city', 'zip', 'fips', 'sector', 'countycd']]
    df1['year'] = year
    
    
    df1.to_csv(cd+'clean\\uni'+str(year)+'.csv')
    
    print(year)
    year = year +1

file_name_list = ['ic9798_HDR_Data_Stata', 'IC98hdac_Data_Stata', 'IC99_HD_Data_Stata']

i=0
year = 1997
while year < 2000: 
    file_name = cd + file_name_list[i]+".zip"
    zip_read = ZipFile(file_name)
    namelist = zip_read.namelist()
    df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')  
    
    df.columns = df.columns.str.lower()   
    df['countycd'] = df['fips']*1000 + df['cntygeo']
    
    df1 = df[['unitid', 'instnm', 'city', 'zip', 'fips', 'sector', 'countycd']]
    df1['year'] = year
    
    
    df1.to_csv(cd+'clean\\uni'+str(year)+'.csv')
    
    print(year)
    i=i+1
    year = year +1

all_df = pd.DataFrame()
year = 1997
while year < 2000: 
    df = pd.read_csv(cd+'clean\\uni'+str(year)+'.csv')
    df = df.drop(columns = 'Unnamed: 0')
    
    df.columns = df.columns.str.lower()   
    all_df = pd.concat([all_df, df])
    
    print(year)
    year = year +1

year = 2009
while year < 2023: 
    df = pd.read_csv(cd+'clean\\uni'+str(year)+'.csv')
    df = df.drop(columns = 'Unnamed: 0')
    
    df.columns = df.columns.str.lower()   
    all_df = pd.concat([all_df, df])
    
    print(year)
    year = year +1

all_df.to_csv(cd+'clean\\uni.csv')

##missingcounty info
year = 2002
while year < 2009: 
    file_name = cd + "HD"+str(year)+"_Data_Stata.zip"
    zip_read = ZipFile(file_name)
    namelist = zip_read.namelist()
    df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')  
    
    df.columns = df.columns.str.lower()   
    
    df1 = df[['unitid', 'instnm', 'city', 'zip', 'fips', 'sector']]   
    df1['year'] = year
    
    
    df1.to_csv(cd+'misscounty\\uni'+str(year)+'.csv')
    
    print(year)
    year = year +1

##different naming
year = 2000
while year < 2002: 
    file_name = cd + "FA"+str(year)+"HD_Data_Stata.zip"
    zip_read = ZipFile(file_name)
    namelist = zip_read.namelist()
    df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')  
    
    df.columns = df.columns.str.lower()   
    
    df1 = df[['unitid', 'instnm', 'city', 'zip', 'fips', 'sector']]   
    df1['year'] = year
    
    
    df1.to_csv(cd+'misscounty\\uni'+str(year)+'.csv')
    
    print(year)
    year = year +1

all_df = pd.DataFrame()
year = 2000
while year < 2009: 
    df = pd.read_csv(cd+'misscounty\\uni'+str(year)+'.csv')
    df = df.drop(columns = 'Unnamed: 0')
    
    df.columns = df.columns.str.lower()   
    all_df = pd.concat([all_df, df])
    
    print(year)
    year = year +1

all_df.to_csv(cd+'misscounty\\uni.csv')

###clean HD data from 1990-1996
#1997 data contain county fips code; fips code not provided prior to 1997
hd_list = os.listdir(cd+'1990-1996')
year_list = [1991,1992,1993,1994,1990,1995,1996]
i = 0
while i < len(hd_list):
    year = year_list[i]
    file_name = cd + '1990-1996\\'+ hd_list[i]
    zip_read = ZipFile(file_name)
    namelist = zip_read.namelist()
    df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')  
    
    df.columns = df.columns.str.lower()   
    
    df1 = df[['unitid', 'instnm', 'city', 'zip', 'fips', 'sector','countynm']]
    df1['year'] = year    
    df1.to_csv(cd+'clean\\uni'+str(year)+'.csv')
    
    print(year)
    i=i+1

all_df = pd.DataFrame()
year = 1990
while year < 1997: 
    df = pd.read_csv(cd+'clean\\uni'+str(year)+'.csv')
    df = df.drop(columns = 'Unnamed: 0')
    
    df.columns = df.columns.str.lower()   
    all_df = pd.concat([all_df, df])
    
    print(year)
    year = year +1

all_df['zip5'] = all_df['zip'].apply(lambda x : str(x).split(".")[0][:5])

all_df.to_csv(cd+'clean\\uni_9096.csv')

#append 1997 data because pre-1997 data have no county fips code
df_97 = pd.read_csv(cd+"clean\\uni1997.csv")
df_97 = df_97.drop(columns = 'Unnamed: 0')
all_df1 = pd.concat([all_df, df_97])
all_df1['zip5'] = all_df1['zip'].apply(lambda x : str(x).split(".")[0][:5])
all_df1.to_csv(cd+'clean\\uni_9097.csv')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2009
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2010
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2011
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2012
2013
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2014
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2015
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2016
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2017
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2018
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2019
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2020
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2021
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:24: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2022
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:46: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1997
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:46: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:46: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1998
1999
1997
1998
1999
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:86: DtypeWarning: Columns (12) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2002
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2003
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2004
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2005
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2006
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2007
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:91: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2008
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:105: DtypeWarning: Columns (11) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:110: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2000
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:105: DtypeWarning: Columns (92,93) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:110: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
2001
2000
2001
2002
2003
2004
2005
2006
2007
2008
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1991
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:142: DtypeWarning: Columns (23) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1992
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1993
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1994
1990
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:142: DtypeWarning: Columns (39,40) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1995
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:142: DtypeWarning: Columns (41,42) have mixed types. Specify dtype option on import or set low_memory=False.
  df = pd.read_csv(zip_read.open(namelist[0]),encoding='latin1')
C:\Users\shaphanng\AppData\Local\Temp\ipykernel_18888\444397169.py:147: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df1['year'] = year
1996
1990
1991
1992
1993
1994
1995
1996