<jupyter_start><jupyter_text>Parsing JAR datafiles for analysisDownloadable datasets received from Tennessee Department of Health [here](https://drive.google.com/drive/folders/1K1hiUFRyfpjLcvcf7pFePo48fS3Fijtz?usp=sharing)More information on Joint Annual Reports [here](https://apps.health.tn.gov/publicjars/default.aspx)<jupyter_code>import pandas as pd
import os
from functools import reduce
import numpy as np
os.chdir("Downloads/20210329_LS_TN_medicaid_data")
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)<jupyter_output><empty_output><jupyter_text>Below are the specific lines in the JAR we used from Schedule E and the specific formulas.|                                                                                                                                | 2015                                              | 2016-2019                                                                  || ------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------- | -------------------------------------------------------------------------- || Total\_expenses                                                                                                                | B2i                                               | B3                                                                         || Total\_charges                                                                                                                 | A4e                                               | A3e                                                                        || medicaid\_charges - adding up inpatient and outpatient charges                                                                 | ChargesGrossPatient-A1c + ChargesGrossPatient-A1d | GrossCharge A1e + GrossCharge A1f                                          || Medicaid\_revenue - adding up inpatient and outpatient revenue                                                                 | RevenueNetPatient\_A1c+ RevenueNetPatient\_A1d    | NetChargeA1e + NetCharge A1f                                               || Total\_charity\_care                                                                                                           | ChargesAdjustments\_A6h                           | The line where it says Total Charity (A5a + A5b)                           || Charity\_and\_bad\_debt\_cost - sometimes there are adjustments made to them so using the totals here instead of adding two up | ChargesAdjustments\_A6i                           | The line where it says Total Charity plus Total Bad Debt (A4i + A5a + A5b) |  Parsing 2011-2015 data<jupyter_code>def get_15_sheet(path,year):
    x2015 = pd.ExcelFile(path)
    raw2015A = x2015.parse("ScheduleA")
    try:
        raw2015Apt = raw2015A[['HospitalID','HospitalName','HospitalMedicareID','HospitalZipCode','HospitalCity']].copy().drop_duplicates()
    except Exception:
        raw2015Apt = raw2015A[['HospitalID','HospitalName','HospitalZipCode','HospitalCity']].copy().drop_duplicates()

    
    raw2015B = x2015.parse("ScheduleB")
    raw2015Bpt = raw2015B[['HospitalID','ControlOrganizationCode','ControlOrganization','HealthSystem','Headquarters','ServiceCategoryCode','ServiceCategory','HeadquartersCity']].copy().drop_duplicates()
    
    raw2015E = x2015.parse("ScheduleE")
    raw2015Ept = raw2015E[['HospitalID','ChargesSubCode','ChargesSub','ChargesGrossPatient','ChargesAdjustments','RevenueNetPatient','RevenueOther','Expenses']].copy().drop_duplicates()
    # extracting the subcodes for pivot
    subcodelist = ['A4e','A1c','A1d','A5f','A6h','A6i','B2i','A8f','A4a','A4d','A4c']
    raw2015Ept1 = raw2015Ept[raw2015Ept.ChargesSubCode.isin(subcodelist)].copy().drop_duplicates()
    raw2015Ept1pv = raw2015Ept1.pivot(columns = 'ChargesSubCode', values = ['ChargesGrossPatient','ChargesAdjustments','RevenueNetPatient','RevenueOther','Expenses'], index = 'HospitalID')
    raw2015Ept1pv.columns = raw2015Ept1pv.columns.to_flat_index()
    raw2015Ept1pv = raw2015Ept1pv.reset_index()
    raw2015Ept1pv.columns = [str(x) for x in raw2015Ept1pv.columns]
    raw2015Ept1pv.columns = raw2015Ept1pv.columns.str.replace("'","").str.replace("(","").str.replace(")","").str.replace(",","_").str.replace(" ","").str.strip()
    raw2015Ept1pvlist = ['HospitalID', 'ChargesGrossPatient_A1c', 'ChargesGrossPatient_A1d',
       'ChargesGrossPatient_A4e', 'ChargesAdjustments_A5f',
       'ChargesAdjustments_A6h', 'ChargesAdjustments_A6i',
       'RevenueNetPatient_A1c', 'RevenueNetPatient_A1d',
       'RevenueNetPatient_A4e', 'RevenueOther_A8f', 'Expenses_B2i',
        'ChargesGrossPatient_A4a','ChargesGrossPatient_A4d',
                        'ChargesGrossPatient_A4c']
    raw2015Ept1pv1 = raw2015Ept1pv[raw2015Ept1pvlist].copy().drop_duplicates()
    raw2015Ept1pv1['medicaid_charges'] = raw2015Ept1pv1.ChargesGrossPatient_A1c + raw2015Ept1pv1.ChargesGrossPatient_A1d
    raw2015Ept1pv1['medicaid_revenue'] = raw2015Ept1pv1.RevenueNetPatient_A1c + raw2015Ept1pv1.RevenueNetPatient_A1d
    raw2015Ept1pv1 = raw2015Ept1pv1.rename(columns = {"ChargesGrossPatient_A4e":"total_charges",
                                                      "RevenueNetPatient_A4e":"net_patient_revenue",
                                                     "RevenueOther_A8f":"total_revenue",
                                                     "Expenses_B2i":"total_expenses",
                                                     "ChargesAdjustments_A5f":"bad_debt",
                                                     "ChargesAdjustments_A6h":"total_charity",
                                                     "ChargesAdjustments_A6i":"bad_debt_charity",
                                                     "ChargesGrossPatient_A4a":"total_inpatient_charge",
                                                     "ChargesGrossPatient_A4d":"total_outpatient_charge",
                                                     "ChargesGrossPatient_A4c":"total_inpatient_charge_newborn"})

    raw2015F = x2015.parse("ScheduleF")
    raw2015Fpt = raw2015F[['HospitalID','BedsLicensedTotal']].copy().drop_duplicates()
    
    raw2015G  = x2015.parse("ScheduleG")
    raw2015Gpt = raw2015G.iloc[:,[0,2,3,10,11]].copy().drop_duplicates()
    payerlist = [10,5]
    raw2015Gpt1 = raw2015Gpt[raw2015Gpt.AdmisDischPayerCode.isin(payerlist)].copy().drop_duplicates()
    raw2015Gpt1pv = raw2015Gpt1.pivot(index = 'HospitalID', columns = 'AdmisDischPayerCode',values = ['AdmisDischDays','AdmisDischVisits'])
    raw2015Gpt1pv.columns = raw2015Gpt1pv.columns.to_flat_index()
    raw2015Gpt1pv = raw2015Gpt1pv.reset_index()
    raw2015Gpt1pv.columns = [str(x) for x in raw2015Gpt1pv.columns]
    raw2015Gpt1pv.columns = raw2015Gpt1pv.columns.str.replace("'","").str.replace("(","").str.replace(")","").str.replace(",","_").str.replace(" ","").str.replace('.','_').str.replace('_0','').str.strip()    
    raw2015Gpt1pv['medicaid_pct_inpatient'] = raw2015Gpt1pv.AdmisDischDays_5/raw2015Gpt1pv.AdmisDischDays_10
    raw2015Gpt1pv['medicaid_pct_outpatient'] = raw2015Gpt1pv.AdmisDischVisits_5/raw2015Gpt1pv.AdmisDischVisits_10
    
    dflist2015 = [raw2015Apt,raw2015Bpt,raw2015Ept1pv1,raw2015Fpt,raw2015Gpt1pv]
    jardoh2015 = reduce(lambda left,right: pd.merge(left, right,on = 'HospitalID', how = 'outer'), dflist2015)
    jardoh2015['year'] = year
    jardoh2015 = jardoh2015.rename(columns = {"HospitalID":'state_id'})
    return(jardoh2015)
try2015 = get_15_sheet('JAR data transfer/2015_HospitalJARs.xlsx',2015)
try2014 = get_15_sheet('JAR data transfer/2014_HospitalJARs.xlsx',2014)
try2013 = get_15_sheet('JAR data transfer/2013_HospitalJARs.xlsx',2013)
try2012 = get_15_sheet('JAR data transfer/2012_HospitalJARs.xlsx',2012)
dflist1215 = [try2012,try2013,try2014,try2015]
df1215 = pd.concat(dflist1215)<jupyter_output><empty_output><jupyter_text>Parsing 2016-2018 data<jupyter_code>def clean_tuple(tup):
    string = str(tup).strip().replace("(","").replace(")","").replace(",","_").replace("/","_").replace("'","").replace(" ","").strip()
    return(string)
def get_16_sheet(path, year):
    x2017 = pd.ExcelFile(path)
    raw2017A = x2017.parse('dbo_JARHospitalScheduleA')
    raw2015Aptnames = ['StateID', 'FacilityID', 'FacilityName', 'MedicareID', 'ZipCode','City']
    raw2017Apt = raw2017A[raw2015Aptnames].copy().drop_duplicates()

    raw2017B = x2017.parse("dbo_JARHospitalScheduleB")
    raw2015Bptnames = ['OrganizationGroup',
     'BelongToHealthGroup',
     'HealthGroupName',
     'FacilityID',
     'CategoryText',
    'HealthGroupCity']
    raw2017Bpt = raw2017B[raw2015Bptnames].copy().drop_duplicates()

    raw2017Ea = x2017.parse('dbo_JARHospitalScheduleEa')
    raw2017Eanames = ['ChargeText','ChargesSubType','GrossCharge','AdjCharge','NetCharge','FacilityID']
    raw2017Eapt = raw2017Ea[raw2017Eanames].copy().drop_duplicates()
    chargelist = ['Medicaid/TennCare Inpatient',
                 'Medicaid/TennCare Outpatient',
                 'Grand Total',
                 'Total Bad Debt',
                 'Total Charity',
                 'Total Charity plus Bad Debt',
                 'TOTAL REVENUE',
                 'Total Inpatient (excludes Normal  Newborn)',
                'Total Inpatient (includes Normal Newborn)',
                 'Total Outpatient']

    raw2017Eapt1 = raw2017Eapt[(raw2017Eapt.ChargeText.isin(chargelist)) & (pd.isna(raw2017Eapt.ChargesSubType) == True) ]
    raw2017Eapt1pv = raw2017Eapt1.pivot(index = 'FacilityID', columns = 'ChargeText')
    raw2017Eapt1pv.columns = raw2017Eapt1pv.columns.to_flat_index()
    raw2017Eapt1pv = raw2017Eapt1pv.reset_index()
    raw2017Eapt1pv1 = raw2017Eapt1pv.dropna(how = 'all', axis = 1).copy().drop_duplicates()
    raw2017Eapt1pv1.columns = [str(x) for x in raw2017Eapt1pv1.columns]
    raw2017Eapt1pv1.columns = raw2017Eapt1pv1.columns.str.replace("'","").str.replace("(","").str.replace(")","").str.replace(",","_").str.replace(" ","").str.replace('/','_').str.strip()
    raw2017Eapt1pv1['medicaid_charges'] = raw2017Eapt1pv1.GrossCharge_Medicaid_TennCareInpatient + raw2017Eapt1pv1.GrossCharge_Medicaid_TennCareOutpatient
    raw2017Eapt1pv1['medicaid_revenue'] = raw2017Eapt1pv1.NetCharge_Medicaid_TennCareInpatient + raw2017Eapt1pv1.NetCharge_Medicaid_TennCareOutpatient

    raw2017Eb = x2017.parse('dbo_JARHospitalScheduleEb')
    raw2017Ebpt = raw2017Eb[['ExpenseText','GrossExpenses','FacilityID']].copy().drop_duplicates()
    raw2017Ebpt1 = raw2017Ebpt[raw2017Ebpt.ExpenseText == 'TOTAL EXPENSES'].copy().drop_duplicates()
    raw2017Ebpt2 = raw2017Ebpt1.drop('ExpenseText', axis = 1)

    raw2017F = x2017.parse("dbo_JARHospitalScheduleF")
    raw2017Fpt = raw2017F[['NumLicensedBeds','FacilityID']].copy().drop_duplicates()
    raw2017G = x2017.parse('dbo_JARHospitalScheduleG3')
    raw2017Gpt = raw2017G[['PatientDays','NumVisits','PayorText','FacilityID']].copy().drop_duplicates()
    payorlist = ['Medicaid/TennCare InPatient','Medicaid/TennCare OutPatient','Total Payor']
    raw2017Gpt1 = raw2017Gpt[raw2017Gpt.PayorText.isin(payorlist)].copy().drop_duplicates()
    raw2017Gpt1pv = raw2017Gpt1.pivot(index = 'FacilityID', columns = 'PayorText')
    raw2017Gpt1pv1 = raw2017Gpt1pv.dropna(how = 'all', axis = 1)
    raw2017Gpt1pv1.columns = raw2017Gpt1pv1.columns.to_flat_index()
    raw2017Gpt1pv1.columns = [clean_tuple(x) for x in raw2017Gpt1pv1.columns ]
    raw2017Gpt1pv1['medicaid_pct_inpatient'] = raw2017Gpt1pv1.PatientDays_Medicaid_TennCareInPatient/raw2017Gpt1pv1.PatientDays_TotalPayor
    raw2017Gpt1pv1['medicaid_pct_outpatient'] = raw2017Gpt1pv1.NumVisits_Medicaid_TennCareOutPatient/raw2017Gpt1pv1.NumVisits_TotalPayor

    raw2017P = x2017.parse("dbo_JARHospitalScheduleP")
    raw2017Ppt = raw2017P[['TotUnInsPat','TotNumPatNoIns','CostToChargeRatio','FacilityID']].copy().drop_duplicates()

    dflist2017 = [raw2017Apt,raw2017Bpt,raw2017Eapt1pv1,raw2017Ebpt2,raw2017Fpt,raw2017Gpt1pv1,raw2017Ppt]
    
    jardoh2017 = reduce(lambda left,right: pd.merge(left, right,on = 'FacilityID', how = 'outer'), dflist2017)
    jardoh2017['year'] = year
    return(jardoh2017)
try17 = get_16_sheet('JAR data transfer/2017_HospitalJARs.xlsx',2017)
try16 = get_16_sheet('JAR data transfer/2016_HospitalJARs.xlsx',2016)
try18 = get_16_sheet('JAR data transfer/2018_HospitalJARs.xlsx',2018)<jupyter_output><empty_output><jupyter_text>Parsing 2019 dataThe dataset has different tab names but same variable names as 2016-2018<jupyter_code>def get_19_sheet(path, year):
    x2017 = pd.ExcelFile(path)
    raw2017A = x2017.parse('dbo_JARHospitalScheduleA_query')
    raw2015Aptnames = ['StateID', 'FacilityID', 'FacilityName', 'MedicareID', 'ZipCode','City']
    raw2017Apt = raw2017A[raw2015Aptnames].copy().drop_duplicates()

    raw2017B = x2017.parse("dbo_JARHospitalScheduleB_query")
    raw2015Bptnames = ['OrganizationGroup',
     'BelongToHealthGroup',
     'HealthGroupName',
     'FacilityID',
     'CategoryText',
    'HealthGroupCity']
    raw2017Bpt = raw2017B[raw2015Bptnames].copy().drop_duplicates()

    raw2017Ea = x2017.parse('dbo_JARHospitalScheduleEa_query')
    raw2017Eanames = ['ChargeText','ChargesSubType','GrossCharge','AdjCharge','NetCharge','FacilityID']
    raw2017Eapt = raw2017Ea[raw2017Eanames].copy().drop_duplicates()
    chargelist = ['Medicaid/TennCare Inpatient',
                 'Medicaid/TennCare Outpatient',
                 'Grand Total',
                 'Total Bad Debt',
                 'Total Charity',
                 'Total Charity plus Bad Debt',
                 'TOTAL REVENUE',
                 'Total Inpatient (excludes Normal  Newborn)',
                  'Total Inpatient (includes Normal Newborn)',
                 'Total Outpatient']

    raw2017Eapt1 = raw2017Eapt[(raw2017Eapt.ChargeText.isin(chargelist)) & (pd.isna(raw2017Eapt.ChargesSubType) == True)]
    raw2017Eapt1pv = raw2017Eapt1.pivot(index = 'FacilityID', columns = 'ChargeText')
    raw2017Eapt1pv.columns = raw2017Eapt1pv.columns.to_flat_index()
    raw2017Eapt1pv = raw2017Eapt1pv.reset_index()
    raw2017Eapt1pv1 = raw2017Eapt1pv.dropna(how = 'all', axis = 1).copy().drop_duplicates()
    raw2017Eapt1pv1.columns = [str(x) for x in raw2017Eapt1pv1.columns]
    raw2017Eapt1pv1.columns = raw2017Eapt1pv1.columns.str.replace("'","").str.replace("(","").str.replace(")","").str.replace(",","_").str.replace(" ","").str.replace('/','_').str.strip()
    raw2017Eapt1pv1['medicaid_charges'] = raw2017Eapt1pv1.GrossCharge_Medicaid_TennCareInpatient + raw2017Eapt1pv1.GrossCharge_Medicaid_TennCareOutpatient
    raw2017Eapt1pv1['medicaid_revenue'] = raw2017Eapt1pv1.NetCharge_Medicaid_TennCareInpatient + raw2017Eapt1pv1.NetCharge_Medicaid_TennCareOutpatient

    raw2017Eb = x2017.parse('dbo_JARHospitalScheduleEb_query')
    raw2017Ebpt = raw2017Eb[['ExpenseText','GrossExpenses','FacilityID']].copy().drop_duplicates()
    raw2017Ebpt1 = raw2017Ebpt[raw2017Ebpt.ExpenseText == 'TOTAL EXPENSES'].copy().drop_duplicates()
    raw2017Ebpt2 = raw2017Ebpt1.drop('ExpenseText', axis = 1)

    raw2017F = x2017.parse("dbo_JARHospitalScheduleF_query")
    raw2017Fpt = raw2017F[['NumLicensedBeds','FacilityID']].copy().drop_duplicates()
    
    raw2017G = x2017.parse('dbo_JARHospitalScheduleG3_query')
    raw2017Gpt = raw2017G[['PatientDays','NumVisits','PayorText','FacilityID']].copy().drop_duplicates()
    payorlist = ['Medicaid/TennCare InPatient','Medicaid/TennCare OutPatient','Total Payor']
    raw2017Gpt1 = raw2017Gpt[raw2017Gpt.PayorText.isin(payorlist)].copy().drop_duplicates()
    raw2017Gpt1pv = raw2017Gpt1.pivot(index = 'FacilityID', columns = 'PayorText')
    raw2017Gpt1pv1 = raw2017Gpt1pv.dropna(how = 'all', axis = 1)
    raw2017Gpt1pv1.columns = raw2017Gpt1pv1.columns.to_flat_index()
    raw2017Gpt1pv1.columns = [clean_tuple(x) for x in raw2017Gpt1pv1.columns ]
    raw2017Gpt1pv1['medicaid_pct_inpatient'] = raw2017Gpt1pv1.PatientDays_Medicaid_TennCareInPatient/raw2017Gpt1pv1.PatientDays_TotalPayor
    raw2017Gpt1pv1['medicaid_pct_outpatient'] = raw2017Gpt1pv1.NumVisits_Medicaid_TennCareOutPatient/raw2017Gpt1pv1.NumVisits_TotalPayor
    
    raw2017P = x2017.parse("dbo_JARHospitalScheduleP_query")
    raw2017Ppt = raw2017P[['TotUnInsPat','TotNumPatNoIns','CostToChargeRatio','FacilityID']].copy().drop_duplicates()

    dflist2017 = [raw2017Apt,raw2017Bpt,raw2017Eapt1pv1,raw2017Ebpt2,raw2017Fpt,raw2017Gpt1pv1,raw2017Ppt]
    jardoh2017 = reduce(lambda left,right: pd.merge(left, right,on = 'FacilityID', how = 'outer'), dflist2017)
    jardoh2017['year'] = year
    return(jardoh2017)
try19 = get_19_sheet('JAR data transfer/2019_HospitalJARs.xlsx',2019)
dflist1619 = [try16,try17,try18,try19]
df1619 = pd.concat(dflist1619)
df1619.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 672 entries, 0 to 168
Data columns (total 47 columns):
 #   Column                                           Non-Null Count  Dtype  
---  ------                                           --------------  -----  
 0   StateID                                          672 non-null    int64  
 1   FacilityID                                       672 non-null    int64  
 2   FacilityName                                     672 non-null    object 
 3   MedicareID                                       667 non-null    object 
 4   ZipCode                                          672 non-null    int64  
 5   City                                             672 non-null    object 
 6   OrganizationGroup                                661 non-null    object 
 7   BelongToHealthGroup                              672 non-null    bool   
 8   HealthGroupName                                  508 non-null    object 
 9   CategoryText                    [...]<jupyter_text>Summarizing ownership type<jupyter_code>owner15 = pd.DataFrame(df1215.ControlOrganization.value_counts())
owner15
owner15 = owner15.reset_index()
owner15['type'] = ['for_profit','non_profit','public','public','for_profit','public','non_profit','non_profit','not_applicable','public','for_profit','not_applicable','not_applicable']
owner15 = owner15.drop('ControlOrganization', axis = 1)
owner15.columns = ['ControlOrganization', 'type']
owner15
df1215_1 = pd.merge(df1215, owner15, on = 'ControlOrganization', how = 'left')
owner16 = pd.DataFrame(df1619.OrganizationGroup.value_counts())
owner16 = owner16.reset_index()
owner16 = owner16.drop('OrganizationGroup', axis = 1)
owner16['type'] = ['for_profit', 'non_profit','public','public']
owner16.columns = ['OrganizationGroup','type']
owner16
df1619_1 = pd.merge(df1619, owner16, on = 'OrganizationGroup', how = 'left')<jupyter_output><empty_output><jupyter_text>Summarizing service category typeCategories changed in starting 2016<jupyter_code>df1619_1.CategoryText.value_counts()
df1215.ServiceCategory.value_counts()
def acute_care(stri):
    if pd.isna(stri) == True:
        return 'no'
    if ((stri != 'Psychiatric') & (stri != 'Rehabilitation') & (stri != 'Long Term Acute Care') & (stri != 'Pediatric')):
        return('yes')
    else:
        return'no'<jupyter_output><empty_output><jupyter_text>Reconciling column names before and after 2015Handcoded the column names in a separate spreadsheet<jupyter_code>#rename columns to join together
colnames15 = pd.read_excel('20210413_DOH_column_merge.xlsx','15')
colnames16 = pd.read_excel('20210413_DOH_column_merge.xlsx','1619')
colnames16 = colnames16[colnames16.include == 1].copy()
colnames16.name_1619 = colnames16.name_1619.str.strip()
colnames16['edited_name'] = colnames16['edited_name'].str.strip()
df1619_2 = df1619_1[list(colnames16.name_1619)].copy().drop_duplicates()
df1619_2.columns = list(colnames16['edited_name'])
colnames15 = colnames15[colnames15.include == 1].copy()
colnames15.name_15 = colnames15.name_15.str.strip()
colnames15['edited_name'] = colnames15['edited_name'].str.strip()
df1215_2 = df1215_1[list(colnames15.name_15)].copy().drop_duplicates()
df1215_2.columns = list(colnames15.edited_name)<jupyter_output><empty_output><jupyter_text>Concatenate pre and post 16 data together, adding other columns<jupyter_code>jar1219 = pd.concat([df1619_2,df1215_2])
jar1219.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1340 entries, 0 to 667
Data columns (total 35 columns):
 #   Column                          Non-Null Count  Dtype  
---  ------                          --------------  -----  
 0   state_id                        1340 non-null   int64  
 1   name                            1340 non-null   object 
 2   ccn                             816 non-null    object 
 3   zipcode                         1337 non-null   float64
 4   part_of_system                  1340 non-null   int64  
 5   system_name                     1002 non-null   object 
 6   service_cat                     1330 non-null   object 
 7   total_charges                   1332 non-null   float64
 8   medicaid_inpatient_charge       1310 non-null   float64
 9   medicaid_outpatient_charge      1249 non-null   float64
 10  total_revenue                   1331 non-null   float64
 11  bad_debt                        1332 non-null   float64
 12  net_patient_revenue             133[...]<jupyter_text>Labelling acute care<jupyter_code>jar1219['acute_care'] = [acute_care(x) for x in jar1219.service_cat]
jar1219.acute_care.value_counts()<jupyter_output><empty_output><jupyter_text>Counting the acute care hospitals who have five reports betweeen 2015-2019To leave out hospitals that closed in the period if we need to<jupyter_code>jar1519 = jar1219[jar1219.year >=2015].copy().drop_duplicates()
jar1519_working = jar1519[jar1519.acute_care == 'yes'].copy().drop_duplicates()
reportcnt = pd.DataFrame(jar1519_working.groupby('state_id').state_id.count())
reportcnt.columns = ['report_count']
reportcnt = reportcnt.reset_index()
jar1219_working1 = pd.merge(jar1219, reportcnt, on = 'state_id', how = 'left')
jar1219_working1.head()<jupyter_output><empty_output><jupyter_text>Filling in missing CCNDOH told us that CCN shouldn't really change once on file. While it has changed for a few facilities, without the ability to look into CCN history, we're filling in empty CCN with first non null value.<jupyter_code>def hasnull(list):
    for e in list:
        if pd.isna(e) == True:
            return('yes')

def getfirstnn(a):
    try:
        return(next((item for item in a if item is not None)))
    except Exception:
        return np.nan
ccngroup = jar1219_working1.groupby('state_id').agg(lambda ccn: ccn.tolist() )
ccngroup1 = ccngroup[['ccn']].copy()
ccngroup1 = ccngroup1.reset_index()
ccngroup1['hasnull'] = [hasnull(x) for x in ccngroup1.ccn]
nullccn = ccngroup1[ccngroup1.hasnull == 'yes'].copy()
nullccn = nullccn.rename(columns = {'ccn':'ccn_list'})
nullccn['ccn'] = [x[0] for x in nullccn.ccn_list]
nullccn1 = nullccn[['state_id','ccn']]
nullccn1.columns = ['state_id','ccn_fill']
jar1219_working2 = pd.merge(jar1219_working1, nullccn1, on = 'state_id', how = 'left')
jar1219_working2.ccn = [x if pd.isna(x) == False else y for (x,y) in zip(jar1219_working2.ccn,jar1219_working2.ccn_fill)]
jar1219_working2 = jar1219_working2.drop('ccn_fill', axis = 1)
jar1219_working2.ccn = [int(x) if pd.isna(x) == False else x for x in jar1219_working2.ccn]
jar1219_working2[(pd.isna(jar1219_working2.ccn) == True) & (jar1219_working2.acute_care == 'yes') & (jar1219_working2.year >= 2015)]<jupyter_output><empty_output><jupyter_text>Merging JAR data with TennCare supplemental data<jupyter_code>tcsupp = pd.read_excel('20210406_TennCare_data_cleaned.xlsx')
tcsupp1 = tcsupp[['state_id','total_supplemental','year']].copy().drop_duplicates()
tcsupp1.info()
jar1219_working3 = pd.merge(jar1219_working2,tcsupp1, on = ['state_id','year'], how = 'left' )
jar1219_working3.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1340 entries, 0 to 1339
Data columns (total 38 columns):
 #   Column                          Non-Null Count  Dtype  
---  ------                          --------------  -----  
 0   state_id                        1340 non-null   int64  
 1   name                            1340 non-null   object 
 2   ccn                             1308 non-null   float64
 3   zipcode                         1337 non-null   float64
 4   part_of_system                  1340 non-null   int64  
 5   system_name                     1002 non-null   object 
 6   service_cat                     1330 non-null   object 
 7   total_charges                   1332 non-null   float64
 8   medicaid_inpatient_charge       1310 non-null   float64
 9   medicaid_outpatient_charge      1249 non-null   float64
 10  total_revenue                   1331 non-null   float64
 11  bad_debt                        1332 non-null   float64
 12  net_patient_revenue             13[...]<jupyter_text>Complete and export<jupyter_code>jar1219_working3.to_csv('20210506_JAR_TC_data_compile_no_pediatric.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12640840864752956, 'fraction_numerical': 0.08655492296054465, 'mean_word_length': 3.02338619253564, 'pattern_counts': {'":': 11, '<': 53, '<?xml version=': 0, '>': 55, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 89, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '656752', 'license': 'MIT', 'n_tokens_mistral': 9719, 'n_tokens_neox': 8106, 'n_words': 1207, 'path': '20210414_parsing_doh_jar_files.ipynb', 'repo_name': 'NPR-investigations/the_healthcare_divide', 'size': '25117'}