<jupyter_start><jupyter_text>IRS Migration Coverage Estimates The IRS Migration DB only includes filers who were matched between 2 consecutive years.This notebook compares the total number of filers who filed in a year from a separate IRS reportto the number of matched filers in the database and calculates coverage rates for NYC and NY Metro<jupyter_code>%matplotlib inline
import pandas as pd
import pandas.io.formats.format as pf
import sqlite3
import os
import matplotlib
matplotlib.style.use('seaborn-whitegrid')
# display numbers (floats) with thousand separator
pd.options.display.float_format = '{:,.0f}'.format

# found this hack to format integers for display with thousand separator
# https://stackoverflow.com/questions/29663252/format-pandas-integers-for-display?answertab=active#tab-top
class IntArrayFormatter(pf.GenericArrayFormatter):
    def _format_strings(self):
        formatter = self.formatter or '{:,d}'.format
        fmt_values = [formatter(x) for x in self.values]
        return fmt_values
pf.IntArrayFormatter = IntArrayFormatter
#dir_path = os.getcwd()
data_path=os.path.join('source_data')
db='irs_migration_county.sqlite'
data_csv=os.path.join(data_path,'total_filers','total_filers_nycmetro.csv') # Generated from create_total_filers.py

nyc=('36005','36047','36061','36081','36085')
nyma=('34003','34013','34017','34019','34023','34025','34027','34029','34031',\
      '34035','34037','34039','36005','36047','36059','36061',\
      '36079','36081','36085','36087','36103','36119','42103')

years=['2011_12','2012_13','2013_14','2014_15','2015_16','2016_17','2017_18']
#Get sum of filers in the migration db for NY metro by grouping non-movers and in-migrants
con=sqlite3.connect(os.path.join (data_path, db))
df_nymetmig = pd.DataFrame()

for year in years:
    tab='inflow_{}'.format(year)
    mtotal = pd.read_sql_query('SELECT "{}" AS year, destination AS fips, \
    sum(returns) AS returns,sum(exemptions) AS exemptions \
    FROM {} where destination in {} GROUP BY destination'.format(year,tab,nyma),con)
    df_nymetmig=df_nymetmig.append(mtotal,ignore_index=True)
    
con.close()
df_nymetmig.head()
#Get total number of all filers from IRS report csv

df_totalfiler=pd.read_csv((data_csv),converters={'fips':str})
df_totalfiler.head()
#Join the two tables together
joined=pd.merge(df_totalfiler, df_nymetmig, on=['fips','year'])
joined.rename(columns={'returns_x':'totreturns','exempts':'totexempts',
                       'returns_y':'migreturns','exemptions':'migexempts'},inplace=True)

joined.head()
#Generate summaries and calculate percentages for the NY metro area
nyma_matched=joined[['year','migreturns','totreturns','migexempts','totexempts']].groupby(['year']).sum().reset_index()
nyma_matched['pct_returns']=((nyma_matched.migreturns / nyma_matched.totreturns)*100).round(1)
nyma_matched['pct_exempts']=((nyma_matched.migexempts / nyma_matched.totexempts)*100).round(1)

nyma_matched
nyma_matched.iloc[:,1:5].sum()
nyma_pct_totreturns=((nyma_matched.loc[:,'migreturns'].sum()/nyma_matched.loc[:,'totreturns'].sum())*100).round(1)
nyma_pct_totreturns
nyma_pct_totexempts=((nyma_matched.loc[:,'migexempts'].sum()/nyma_matched.loc[:,'totexempts'].sum())*100).round(1)
nyma_pct_totexempts
#Generate summaries and calculate percentages for NYC
nyc_matched=joined[joined['fips'].isin(nyc)][['year','migreturns','totreturns','migexempts','totexempts']].groupby(['year']).sum().reset_index()
nyc_matched['pct_returns']=((nyc_matched.migreturns / nyc_matched.totreturns)*100).round(1)
nyc_matched['pct_exempts']=((nyc_matched.migexempts / nyc_matched.totexempts)*100).round(1)

nyc_matched
nyc_matched.iloc[:,1:5].sum()
nyc_pct_totreturns=((nyc_matched.loc[:,'migreturns'].sum()/nyc_matched.loc[:,'totreturns'].sum())*100).round(1)
nyc_pct_totreturns
nyc_pct_totexempts=((nyc_matched.loc[:,'migexempts'].sum()/nyc_matched.loc[:,'totexempts'].sum())*100).round(1)
nyc_pct_totexempts
#LaTeX example NYC table
print(nyc_matched.to_latex())
#Figure example NYC plot
bchart=pd.DataFrame(nyc_matched.set_index(['year']), columns=['pct_returns','pct_exempts'])
my_plot = bchart.plot(kind='bar',title="Total NYC Filers Included in Migration DB",rot=0)
my_plot.set_xlabel("Year")
my_plot.set_ylabel("% Total")
my_plot.legend(["Returns","Exemptions"], loc=4,ncol=2)

#fig = my_plot.get_figure()
#fig.savefig('test_figure.png')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16079144871503298, 'fraction_numerical': 0.05299067546054128, 'mean_word_length': 7.9208924949290065, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '271972', 'license': 'MIT', 'n_tokens_mistral': 1747, 'n_tokens_neox': 1575, 'n_words': 256, 'path': 'scripts/coverage_estimates.ipynb', 'repo_name': 'frankpd/pep_irs_migration', 'size': '4397'}