<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 H-2 Employers Investigated Per Fiscal Year
The Python code below calculates the number of WHD cases concluded each fiscal year that examined some aspect of H-2 regulations, and the number of distinct employer IDs associated with those cases. In addition, it provides a rough estimate of the number of employers certified for H-2 per fiscal year, to support the statement that "vast majority" of H-2 certified employers are not inspected.
Note
Step1:  Note
Step2:  Number of H-2–related cases by overall investigation type and fiscal year concluded
Step3:  Note
Step4:  Data Loading — Certifications
Step5:  Basic Standardization of Employer Names
Step6:  Example of employer names before and after standardization
Step7:  Rough count of the number of employers certified for H-2 visas, per fiscal year
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import sys
import re
sys.path.append("../utils")
import loaders
Explanation: H-2 Employers Investigated Per Fiscal Year
The Python code below calculates the number of WHD cases concluded each fiscal year that examined some aspect of H-2 regulations, and the number of distinct employer IDs associated with those cases. In addition, it provides a rough estimate of the number of employers certified for H-2 per fiscal year, to support the statement that "vast majority" of H-2 certified employers are not inspected.
Note: The visa certification data published by the OFLC do not include unique identifiers for employers. Additionally, the data contain multiple alternate spellings and mis-spellings of employer names, making it difficult to determine the number of distinct employers. For these reasons, this analysis intentionally does not provide an overall inspection rate. It should be clear from the numbers below, however, that number of H-2 employers that WHD inspects each year amounts to a small fraction of the number of employers that DOL certifies for H-2 visas.
Investigations Methodology
Load the CASE_ACT_SUMMARY rows for each case, and select those for the "H2A" and "H2B" ACT_IDs. This includes any case with H-2 findings, regardless of whether WHD identified any violations.
For each fiscal year 2010 - 2014, the case was "concluded," and by the CASE_ID (since some employers have summaries for both H-2A and H-2B findings).
For each fiscal year, FY 2010–2014, count the number of cases and unique employer IDs.
Certifications Methodology
Load list of the Office of Foreign Labor Certification's H-2 certification decisions.
Select only decisions to certify visas (rather than deny them), and exclude expired certifications as well as certifications for umbrella organizations (rather than specific employers).
Standardize the provided employer name by uppercasing the names and removing punctuation.
For each fiscal year of decisions, FY 2010–2014, count the number of unique (standardized) employer names.
Data Loading — Investigations
End of explanation
employers = loaders.load_employers().set_index("CASE_ID")
cases = loaders.load_cases().set_index("CASE_ID")
cases_basics = cases[[ "DATE_CONCLUDED_FY", "INVEST_TOOL_DESC" ]]\
    .join(employers[ "employer_id" ])\
    .reset_index()
act_summaries = loaders.load_act_summaries()
h2_summaries = act_summaries[
    act_summaries["ACT_ID"].isin([ "H2A", "H2B" ])
]
matching_cases = cases_basics[
    cases_basics["CASE_ID"].isin(h2_summaries["CASE_ID"]) &
    (cases_basics["DATE_CONCLUDED_FY"] >= 2010) &
    (cases_basics["DATE_CONCLUDED_FY"] <= 2014)
]
invest_tool_counts = matching_cases["INVEST_TOOL_DESC"].value_counts()
case_counts = matching_cases.groupby([
    "DATE_CONCLUDED_FY",
    "INVEST_TOOL_DESC"
])["CASE_ID"].nunique()\
    .unstack()\
    .fillna(0)\
    [invest_tool_counts.index.tolist()]
case_counts["[total]"] = case_counts.sum(axis=1)
employer_counts = pd.DataFrame({
    "n_employer_ids": matching_cases.groupby("DATE_CONCLUDED_FY")["employer_id"].nunique()
})
Explanation: Note: loaders is a custom module to handle most common data-loading operations in these analyses. It is available here.
End of explanation
case_counts
Explanation: Number of H-2–related cases by overall investigation type and fiscal year concluded:
End of explanation
employer_counts
Explanation: Note: The counts and below include all cases with at least some H-2 aspect indicated, regardless of whether H-2 was the primary focus or whether investigators found any H-2 violations.
Distinct employer IDs associated with the cases above:
End of explanation
date_parser = lambda x: pd.to_datetime(x, format="%Y-%m-%d", coerce=True)
oflc_decisions = pd.read_csv("../data/oflc-decisions/processed/oflc-decisions.csv",
                            parse_dates=["last_event_date"],
                            date_parser=date_parser)
oflc_decisions["last_event_date_fy"] = oflc_decisions["last_event_date"].apply(loaders.date_to_fy)
certifications = oflc_decisions[
    (oflc_decisions["is_certified"] == True) &
    (oflc_decisions["is_expired"] == False) &
    (oflc_decisions["is_duplicate"] == False) &
    (oflc_decisions["last_event_date_fy"] >= 2010) &
    (oflc_decisions["last_event_date_fy"] <= 2014)
].copy()
non_alphanum_pat = re.compile(r"[^A-Z0-9 ]+")
Explanation: Data Loading — Certifications
End of explanation
def standardize_name(x):
    return re.sub(non_alphanum_pat, "", x.upper().strip())
certifications["employer_name_standard"] = certifications["employer_name"]\
    .fillna("")\
    .apply(standardize_name)
Explanation: Basic Standardization of Employer Names
End of explanation
certifications[["employer_name", "employer_name_standard"]].tail()
certs_by_fy = certifications.groupby("last_event_date_fy")
employer_cert_counts = pd.DataFrame({
    "n_employer_names": certs_by_fy["employer_name_standard"].nunique()
})
Explanation: Example of employer names before and after standardization:
End of explanation
employer_cert_counts
Explanation: Rough count of the number of employers certified for H-2 visas, per fiscal year:
End of explanation
<END_TASK>