<jupyter_start><jupyter_text>Death recordsThis notebook parses and cleans up the raw deaths records data from the California Department of Public Health. Import Python tools<jupyter_code>import pandas as pd
import openpyxl<jupyter_output><empty_output><jupyter_text>Functions to read in and standardize different file types<jupyter_code>def read_dat(
    name,
    year,
    sep=",",
    header=None,
    names=[
        "last_name",
        "first_name",
        "middle_name",
        "sex",
        "date_of_birth",
        "date_of_death",
        "place_of_birth_state_or_foreign_country",
        "place_of_death_county",
        "fathers_last_name"
    ],
    parse_dates=["date_of_death"],
    **kwargs
):
    """
    Reads the provided DAT file into a DataFrame.
    """
    # Read in the CSV file
    path = f"../input/raw/death-records/{name}"
    df = pd.read_csv(
        path,
        sep=sep,
        header=header,
        names=names,
        parse_dates=parse_dates,
        **kwargs
    )
    print(f"- {len(df)} total records")
    # Set metadata columns
    df['date_of_birth'] = pd.to_datetime(df.date_of_birth, errors='coerce')
    df['file_name'] = name
    df['file_year'] = year
    # Trim invalid dates
    trimmed_df = trim_invalid_dates(df)
    print(f"- {len(df) - len(trimmed_df)} out of year records trimmed")
    # Pass it back
    return trimmed_df
def read_xlsx(name, year):
    """
    Reads in the provided Excel file and returns a DataFrame.
    """
    # Read in Excel file
    path = f"../input/raw/death-records/{name}"
    df = pd.read_excel(path)
    print(f"- {len(df)} total records")
    # Standardize column names
    df.columns = df.columns.str.lower()
    df.rename(columns={"father's_last_name": "fathers_last_name"}, inplace=True)
    # Set metadata columns
    df['date_of_birth'] = pd.to_datetime(df.date_of_birth, errors='coerce')
    df['date_of_death'] = pd.to_datetime(df.date_of_death, errors='coerce')
    df['file_name'] = name
    df['file_year'] = year
    # Trim invalid dates
    trimmed_df = trim_invalid_dates(df)
    print(f"- {len(df) - len(trimmed_df)} out of year records trimmed")
    # Pass it back
    return trimmed_df
def trim_invalid_dates(df):
    """
    Removes records for which there are invalid DOB or DODs
    """
    # Filter out rows without a date of death
    filter_na_dod_df = df[~df.date_of_death.isnull()]
    # Filter out rows without a date of birth
    filter_na_dob_df = filter_na_dod_df[~filter_na_dod_df.date_of_birth.isnull()]
    return filter_na_dob_df<jupyter_output><empty_output><jupyter_text>Crosswalk with all of our source data files and key input options<jupyter_code>file_list = [
    dict(name="2010.DAT", year=2010),
    dict(name="2011.DAT", year=2011),
    dict(name="2012.DAT", year=2012),
    dict(name="2013.DAT", year=2013),
    dict(name="PUBLIC_DEATH_010114-123114.xlsx", year=2014),
    dict(name="Public_DEATH_010115-123115FINAL.xlsx", year=2015),
    dict(name="PUBLIC_DEATH_010116-123116.xlsx", year=2016),
    dict(name="PUBLIC_DEATH_010117-123117.xlsx", year=2017),
    dict(name="PUBLIC_DEATH_2018.xlsx", year=2018),
    dict(name="PUBLIC_DEATH_2019.xlsx", year=2019)
]<jupyter_output><empty_output><jupyter_text>Loop through the data files and read them in one by one<jupyter_code>df_list = []
for d in file_list:
    print(f"Opening {d['name']}")
    if d['name'].lower().endswith(".dat"):
        df = read_dat(d['name'], d['year'])
    else:
        df = read_xlsx(d['name'], d['year'])
    df_list.append(df)<jupyter_output>Opening 2010.DAT
- 264108 total records
- 19 out of year records trimmed
Opening 2011.DAT
- 269949 total records
- 22 out of year records trimmed
Opening 2012.DAT
- 272016 total records
- 0 out of year records trimmed
Opening 2013.DAT
- 278489 total records
- 0 out of year records trimmed
Opening PUBLIC_DEATH_010114-123114.xlsx
- 238862 total records
- 44 out of year records trimmed
Opening Public_DEATH_010115-123115FINAL.xlsx
- 259361 total records
- 57 out of year records trimmed
Opening PUBLIC_DEATH_010116-123116.xlsx
- 263225 total records
- 62 out of year records trimmed
Opening PUBLIC_DEATH_010117-123117.xlsx
- 269409 total records
- 69 out of year records trimmed
Opening PUBLIC_DEATH_2018.xlsx
- 269977 total records
- 65 out of year records trimmed
Opening PUBLIC_DEATH_2019.xlsx
- 270783 total records
- 71 out of year records trimmed<jupyter_text>Combine dataframes into master file<jupyter_code>concat_df = pd.concat(df_list)<jupyter_output><empty_output><jupyter_text>Filter out years not in study period<jupyter_code>filter_df = concat_df[(concat_df.date_of_death > "2010-01-01") & (concat_df.date_of_death < "2019-12-31")]<jupyter_output><empty_output><jupyter_text>Remove duplicate deaths using common set of columns<jupyter_code>deduped_df = filter_df.drop_duplicates(
    subset=['last_name', 'first_name', 'sex', 'date_of_birth', 'date_of_death', 'place_of_death_county'],
    keep='last'
)
print(f"Dropped {len(filter_df) - len(deduped_df)} duplicated records")<jupyter_output>Dropped 107362 duplicated records<jupyter_text>Write it out<jupyter_code>deduped_df.to_csv(
    "../input/processed/death-records.csv",
    index=False
)
print(f"Wrote {len(deduped_df)} records to file")<jupyter_output>Wrote 2546720 records to file
{'fraction_non_alphanumeric': 0.11437723761070284, 'fraction_numerical': 0.054833239118145846, 'mean_word_length': 4.173489278752436, 'pattern_counts': {'":': 1, '<': 31, '<?xml version=': 0, '>': 31, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '129614', 'license': 'MIT', 'n_tokens_mistral': 2138, 'n_tokens_neox': 1852, 'n_words': 424, 'path': 'processing/01-death-records.ipynb', 'repo_name': 'datadesk/extreme-heat-excess-deaths-analysis', 'size': '5307'}