<jupyter_start><jupyter_text>Goal: Clean the CBO Excel file, calculate percent change, and divide by number of employed people in each year Last edited: 10/17/21<jupyter_code>import pandas as pd
import numpy as np
import janitor

%load_ext nb_black

from IPython.core.display import display, HTML

display(HTML("<style>.container {width:90% !important;}</style>"))
# import
df = pd.read_csv(
    "/Users/samsloate/Desktop/807 MP/data/cbo/short_term_economic_projections_raw.csv",
    encoding="latin-1",
    skiprows=6,
    sep=",",
)
# clean columns
df = df.dropna(how="all").clean_names()
df = df.rename(
    columns={
        "unnamed_0": "component",
        "unnamed_1": "subcomponent",
        "unnamed_2": "subcomponent_1",
    }
)

# forward fill units
cols = ["component", "subcomponent", "subcomponent_1"]
df.loc[:, cols] = df.loc[:, cols].ffill()
df = df[~df.units.isna()]
# create income dataset: just millions/billions as units, and also include LFPR
inc = df[
    ((df.component == "Income") & (df.units == "Billions of dollars"))
    | (
        (
            (
                df.subcomponent
                == "Employment, Civilian, 16 Years or Older (Household Survey)"
            )
            & (df.units == "Millions")
        )
    )
]
inc = inc.copy().reset_index(drop=True)

# fix forward fill error
inc.loc[
    inc.subcomponent == "Profits, Corporate, With IVA & CCAdj", "subcomponent_1"
] = np.nan

inc.loc[
    inc.subcomponent == "Profits, Corporate, Domestic, With IVA & CCAdj",
    "subcomponent_1",
] = np.nan

# clean income dataset
inc.drop(
    "component", axis=1, inplace=True
)  # drop redundant column (only says 'income')

inc = inc.rename(
    columns={"subcomponent": "comp", "subcomponent_1": "subcomp"}
)  # rename
inc = inc[
    inc.columns.drop(list(inc.filter(regex="unnamed")))
]  # remove "unnamed" columns at end


inc.comp.replace(
    "Employment, Civilian, 16 Years or Older (Household Survey)",
    "employment",
    inplace=True,
)  # rename "employment"

val_vars = [str(i) for i in range(2017, 2032)]

inc = pd.melt(
    inc,
    id_vars=["comp", "subcomp", "units"],
    value_vars=val_vars,
    var_name="year",
    value_name="dollars",
)  # make long, not wide
# create new employment column
inc["employment"] = np.nan
inc.loc[inc.comp == "employment", "employment"] = inc.dollars
inc.employment = inc.employment.ffill()

# employment in millions
inc.employment = inc.employment * 1000000

# drop employment rows
inc = inc[inc.comp != "employment"]

# dollars in billions
inc.dollars = inc.dollars * 1000000000

# delete units column
inc.drop("units", axis=1, inplace=True)

# fill in "total" for sub component
inc.loc[inc.subcomp.isna(), "subcomp"] = inc.comp
# calculations
inc["dollars_per_wkr"] = (
    inc.dollars / inc.employment
)  # dollars per worker, to account for pop. growth
inc.sort_values(["comp", "subcomp"], inplace=True, ascending=[True, False])
inc["pct_change"] = inc.dollars_per_wkr.pct_change()
inc
# subset to just the data we need
inc.replace("Wages and Salaries", "wages and salaries", inplace=True)
inc.replace("Nonwage Income", "nonwage inc", inplace=True)
inc.replace("Income, Personal", "total personal inc", inplace=True)

# round numbers
inc["pct_change"] = inc["pct_change"].round(4)
inc["dollars_per_wkr"] = inc["dollars_per_wkr"].round(2)
inc["employment"] = inc["employment"].round(0)

inc_all = inc

inc_subset = inc[
    (inc.comp == "wages and salaries")
    | (inc.comp == "nonwage inc")
    | (inc.comp == "total personal inc")
]
# export

inc_all.to_csv(
    "/Users/samsloate/Desktop/807 MP/data/cbo/short_term_economic_projections_clean_all.csv"
)
inc_subset.to_csv(
    "/Users/samsloate/Desktop/807 MP/data/cbo/short_term_economic_projections_clean_subset.csv"
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14947368421052631, 'fraction_numerical': 0.016052631578947367, 'mean_word_length': 4.061251664447403, 'pattern_counts': {'":': 5, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, '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': '388852', 'license': 'Unlicense', 'n_tokens_mistral': 1396, 'n_tokens_neox': 1310, 'n_words': 312, 'path': 'data/microsim_outputs/10_clean_cbo_short_term.ipynb', 'repo_name': 'ssloate/masters_project', 'size': '3800'}