<jupyter_start><jupyter_text>enrollment with issuer characteristics<jupyter_code>service_df = pd.read_csv(data_path + 'ServiceArea_PUF_2016.csv', encoding='cp1252')
service_df = service_df[['County','ServiceAreaId','IssuerId']]
service_df = service_df.drop_duplicates()
issuer_df = pd.read_csv('../data/processed_data/issuer_characteristics.csv')
issuer_service = issuer_df.merge(service_df, how='inner', on=['ServiceAreaId', 'IssuerId'])
issuer_service = issuer_service[ ~issuer_service['County'].isna() ]

#reorder the columns, drop service area
col_order= ['IssuerId','County'] + list(issuer_service.columns)[2:-1]
issuer_service = issuer_service[ col_order ]

#group by county
#pre_cols = list(issuer_service.columns)
issuer_service = issuer_service.groupby(['IssuerId','County','StandardComponentId'],as_index=False).median()
issuer_service_count = issuer_service.groupby(['IssuerId','County'],as_index=False)['StandardComponentId'].count()
issuer_service = issuer_service.groupby(['IssuerId','County'],as_index=False).mean()
issuer_service['Plan Counts'] = issuer_service_count['StandardComponentId']

print(issuer_service.shape)
#print(len(pre_cols),len(post_cols))
enroll_issuer = enroll_df.merge(issuer_service, how='left', right_on=['County', 'IssuerId'],
                                     left_on=['Policy County FIPS Code','HIOS ID'])

enroll_issuer['County'] = enroll_issuer['Policy County FIPS Code']
enroll_issuer['IssuerId'] = enroll_issuer['HIOS ID']
enroll_issuer = enroll_issuer.fillna(0)
print(enroll_issuer.shape)<jupyter_output>(8796, 135)<jupyter_text>Merge with county characteristics<jupyter_code>county = pd.read_csv('../data/processed_data/county_characteristics.csv')
result = enroll_issuer.merge(county, how='left', left_on='County', right_on='County')
result =result[~result['FIPS County Code'].isna()]
all_cols  = list(result.columns)
keys = ['HIOS ID','Policy County FIPS Code','IssuerId','County','State','FIPS County Code','County Name']
for key in keys:
    all_cols.remove(key)
    
#delete bad columns/clean up census data
all_cols2 = []

for col in all_cols:
    #fix cols from census data
    result[col] = result[col].apply(lambda x : float(str(x).replace('-','0').replace('N','0')))
    
    #clean up cols with no variance  
    if result[col].std() > 0:
        all_cols2.append(col)


result[keys + all_cols2].to_csv('../data/processed_data/merged_characteristics.csv',index=False)<jupyter_output><empty_output><jupyter_text>Summary stats<jupyter_code>result['Ever Enrolled Count'].sum()
result['Policy County FIPS Code'].nunique()
result['IssuerId'].nunique()
col_names = pd.Series(result.columns)
#col_names.to_csv('col_names.csv')
result1 = pd.DataFrame(result.isnull().sum())
result1<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15412445730824892, 'fraction_numerical': 0.009406657018813314, 'mean_word_length': 7.560371517027864, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '125346', 'license': 'MIT', 'n_tokens_mistral': 977, 'n_tokens_neox': 938, 'n_words': 146, 'path': 'preprocess/merge_characteristics.ipynb', 'repo_name': 'ericschulman/health_entry', 'size': '2764'}