<jupyter_start><jupyter_text>Load Dataset<jupyter_code>df = pd.read_csv("datasets/data_perm_take_home.csv", encoding="ISO-8859-1")
def map_date(date_str):
    if type(date_str) != str:
        return None
    return datetime.strptime(date_str, "%m/%d/%y").date()

df.case_received_date = df.case_received_date.apply(map_date)
df.decision_date = df.decision_date.apply(map_date)
df.head()<jupyter_output><empty_output><jupyter_text>Load Database into MySQL Server<jupyter_code>from sqlalchemy import create_engine
username = "shaw"
password = "<PASSWORD>"
port = 8888
database = "Case"

engine = create_engine('mysql+mysqldb://%s:%s@localhost:%i/%s'
                       %(username, password, port, database))
df.to_sql('Perm', engine, if_exists='replace')<jupyter_output><empty_output><jupyter_text>Part I: Short Question__Q1__: Which employer has the most entries in the dataset?__Ans__: COGNIZANT TECHNOLOGY SOLUTIONS US CORPORATION<jupyter_code>sql = """
SELECT employer_name, COUNT(*) entry_count 
FROM Perm 
GROUP BY employer_name 
ORDER BY entry_count 
DESC LIMIT 5;
"""
pd.read_sql_query(sql.replace('\n', " ").replace("\t", " "), engine)<jupyter_output><empty_output><jupyter_text>__Q2__: Which employer has the most “Certified-Expired” cases?__Ans__: MICROSOFT CORPORATION<jupyter_code>sql = """
SELECT employer_name, COUNT(*) AS cert_expired_count 
FROM Perm 
WHERE case_status = "Certified-Expired"
GROUP BY employer_name 
ORDER BY cert_expired_count 
DESC LIMIT 5;
"""
pd.read_sql_query(sql.replace('\n', " ").replace("\t", " "), engine)<jupyter_output><empty_output><jupyter_text>__Q3__: How many unique employers contain the character string "APPLE" that is not "APPLE INC."?__Ans__: 7<jupyter_code>sql = """
SELECT DISTINCT employer_name
FROM Perm
WHERE employer_name LIKE "%%APPLE%%" 
  AND employer_name != "APPLE INC.";
"""
df_q3 = pd.read_sql_query(sql.replace('\n', " ").replace("\t", " "), engine)
print("Number of unique employers contain the character string APPLE that is not APPLE INC: %i"%len(df_q3))
df_q3<jupyter_output>Number of unique employers contain the character string APPLE that is not APPLE INC: 7<jupyter_text>__Q4__: Write a function that plots a histogram with 50 bins of the duration between from case_received_dateto decision_date for any given employeer_name. Use the function to plot for "APPLE INC." and "GOOGLE INC."<jupyter_code>def plot_processing_time_for_company(employer_name):
    sql = """
    SELECT 
      decision_date
      ,case_received_date
      ,DATEDIFF(decision_date, case_received_date) AS duration
    FROM Perm 
    WHERE employer_name = "%s";
    """%employer_name
    
    df_duration = pd.read_sql_query(sql.replace('\n', " ").replace("\t", " "), engine)
    
    plt.hist(df_duration.duration, bins=50)
    plt.xlabel("days between received and decision")
    plt.ylabel("frequency")
    plt.title(employer_name)
    plt.show()
plot_processing_time_for_company("APPLE INC.")
plot_processing_time_for_company("GOOGLE INC.")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12231404958677686, 'fraction_numerical': 0.007933884297520661, 'mean_word_length': 5.665198237885463, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '611044', 'license': 'MIT', 'n_tokens_mistral': 1083, 'n_tokens_neox': 1031, 'n_words': 236, 'path': 'case_study/us_department_of_labor/part_1_short_questions.ipynb', 'repo_name': 'CaiWeichuan/Data-Science-Toolbox', 'size': '3019'}