<jupyter_start><jupyter_text>df_final = df_total.melt(id_vars=['countyFIPS','County Name'], var_name='Month_Year',value_name='Cases').sort_values('countyFIPS')df_final.head()<jupyter_code># create a numeric month column
month_dict = {1:"Jan",2:"Feb", 3:"Mar", 4:"Apr", 5:"May", 6:"Jun",7:"Jul", 8:"Aug", 9:"Sep",10:"Oct", 11:"Nov", 12:"Dec"}
#df_final['Month_c'] = df_final['Month_Year'].str.slice(0, 3) 
df_final['Month_c'] = df_final.month.map(month_dict)
df_final['Year'] = '20'
df_final.head()
df_final_s = df_final.sort_values(['countyFIPS','Month_Year'])
df_final_s.head()
df_final_s['countyFIPS'] = df_final_s['countyFIPS'].astype(str)
df_final_s['County_FIPS']  = df_final_s['countyFIPS'].str.slice(2,) 
df_final_s.head()
df_final_s.to_csv('../data/covid_transformed.csv')
df_final_s.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 147 entries, 0 to 146
Data columns (total 9 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   countyFIPS   147 non-null    object        
 1   County Name  147 non-null    object        
 2   Month_Year   147 non-null    datetime64[ns]
 3   Cases        147 non-null    int64         
 4   year         147 non-null    int64         
 5   month        147 non-null    int64         
 6   Month_c      147 non-null    object        
 7   Year         147 non-null    object        
 8   County_FIPS  147 non-null    object        
dtypes: datetime64[ns](1), int64(3), object(5)
memory usage: 11.5+ KB<jupyter_text>EMPLOYMENT DATA<jupyter_code>employment_df=pd.read_excel("../data/laucntycur14_v1.xlsx", dtype=str)
employment_df.shape
employment_df.head(3)
empl_NJ_df = employment_df.loc[employment_df['State FIPS Code']=='34']
empl_NJ_df.reset_index(drop = True, inplace=True)
empl_NJ_df['County FIPS Code'] = empl_NJ_df['County FIPS Code'].str.zfill(3)
empl_NJ_df.shape
 empl_NJ_df.info()
empl_NJ_df.head()
df_final_s.head()
# empl_NJ_df.info()
# JOIN COVID AND EMPLOYMENT DATA on County FIPS Code AND MONTH_YEAR
joined_df = pd.merge(df_final_s, empl_NJ_df, how='left', left_on=["County_FIPS","Month_c","Year"], right_on=["County FIPS Code","Month_Year","Year"])
joined_df.head()
joined_df.shape
joined_df.to_csv('../data/covid_employment.csv')
joined_df.shape
# September unemployment data is partial No data for Oct, Nov - Deleting rows for Sep to Nov
filter1 = joined_df['month'].isin([2,3,4,5,6,7,8])
joined_df_final=joined_df[filter1]
joined_df_final.shape
joined_df_final.to_csv('../data/joined_df_final.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15396578538102643, 'fraction_numerical': 0.038880248833592534, 'mean_word_length': 4.1875, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '186626', 'license': 'MIT', 'n_tokens_mistral': 1069, 'n_tokens_neox': 982, 'n_words': 173, 'path': 'notebooks/.ipynb_checkpoints/clean_data v1-checkpoint.ipynb', 'repo_name': 'strivedi01/Project2_Richard_Ashni_Sumita', 'size': '2572'}