<jupyter_start><jupyter_text>EDA on commute data<jupyter_code>import pandas as pd
import numpy as np
# load data
table1 = pd.read_excel(
    '../data/commute/table1.xlsx',
    #header=5,
    skiprows=[0,1,2,3,4,5, 139440, 139441],
    dtype=str
)
table1.columns = [
    'Resident State FIPS',
    'Resident County FIPS',
    'Resident State Name',
    'Resident County Name',
    'Work State FIPS',
    'Work County FIPS',
    'Work State Name',
    'Work County Name',
    'commute',
    'error',
]
# load data: county info
county_info = pd.read_csv('../data/02_county_FIPS.csv', dtype=str, index_col=0)
# preprocess the table : change the double float type
table1['commute'] = table1['commute'].astype(np.float)
table1['error'] = table1['error'].astype(np.float)
# preprocess: change the county FIPS
table1['Work State FIPS'] = table1['Work State FIPS'].apply(lambda x : x[1:] if type(x) == str else np.nan)
table1['Resident County FIPS'] = table1['Resident State FIPS'] + table1['Resident County FIPS']
table1['Work County FIPS'] = table1['Work State FIPS'] + table1['Work County FIPS']
# visualize the california data
table1[table1['Resident State Name'] == 'California']
# visualize the states with the most people go to work
table1.groupby('Work State Name')['commute'].sum().sort_values(ascending=False).head()
# visualize the states with the most people resides
table1.groupby('Resident State Name')['commute'].sum().sort_values(ascending=False).head()
# preprocess find the county FIPS that are in this data but not in county_info
missed_counties = set(table1['Resident County FIPS'].unique()) - set(county_info['FIPS'])
table1.loc[table1['Resident County FIPS'].isin(missed_counties), 'Resident County Name'].unique()
# visualize people from CA to NY
table1[(table1['Resident State Name'] == 'California') & (table1['Work State Name'] == 'New York')]
# save data
table1.to_csv("../data/commute/table1.csv")
# load data
table2 = pd.read_excel(
    '../data/commute/table3.xlsx',
    #header=5,
    #skiprows=[0,1,2,3,4,5, 139440, 139441],
    dtype=str
)
table2<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1523809523809524, 'fraction_numerical': 0.03428571428571429, 'mean_word_length': 5.107558139534884, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '384626', 'license': 'MIT', 'n_tokens_mistral': 750, 'n_tokens_neox': 721, 'n_words': 190, 'path': 'modeling/eda_raw_data/commute/01_EDA_commute.ipynb', 'repo_name': 'csinva/covid-19-analysis', 'size': '2100'}