<jupyter_start><jupyter_text>United Nations --- Introduction.For one of my projects at the United Nations (UN), I needed to calculate the duration between the steps in UN job applications. I solved this problem with Python and SQL. Note, all the data in this report is made up and is not related to the UN. Also, the amount of data in this report is limited because this project is about how I solved the problem and not about the data. --- Importing libraries.<jupyter_code>import os
import mysql.connector
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>--- Connecting to database.<jupyter_code>connection = mysql.connector.connect(
    host = "localhost",
    user = os.environ.get("DB_USER"),
    password = os.environ.get("DB_PASS"),
    database = "development"
)

cursor = connection.cursor(buffered=True)<jupyter_output><empty_output><jupyter_text>--- Creating the [applicants] table._I only create a new applicants table if one does not already exist._<jupyter_code>query = """
    CREATE TABLE IF NOT EXISTS applicants (
        job_id INT,
        applicant_id INT,
        disposition VARCHAR(255),
        disposition_date DATE
    );
"""

cursor.execute(query)<jupyter_output><empty_output><jupyter_text>--- Adding made-up data._I remove any previous data from the applicants' table and then populate it with made-up data._<jupyter_code>query = "DELETE FROM applicants"

cursor.execute(query)

query = """
    INSERT INTO applicants
        (job_id, applicant_id, disposition, disposition_date)
    VALUES
        (%s, %s, %s, %s);
"""

rows = [
    (1, 10, "010 Applied", "2019-01-01"),
    (1, 10, "030 Screen", "2019-01-10"),
    (1, 10, "061 Recommended", "2019-01-15"),
    (1, 10, "060 Interview", "2019-01-17"),
    (2, 20, "010 Applied", "2019-02-01"),
    (2, 20, "030 Screen", "2019-02-04"),
    (1, 30, "010 Applied", "2019-03-25"),
    (1, 30, "030 Screen", "2019-04-01")
]

for row in rows:
    cursor.execute(query, row)<jupyter_output><empty_output><jupyter_text>--- Importing MySQL table as a DataFrame._By importing the data into a DataFrame, I can manipulate the data in the next steps._<jupyter_code>query = "SELECT * FROM applicants;"
df = pd.read_sql_query(query, connection)
df<jupyter_output><empty_output><jupyter_text>--- Adding [next_disposition_date]._I create two subqueries and assign a row number to each step in the job application process. This action is performed for every applicant and each of their job applications. Note, the two tables are identical to each other. I then join the tables together but with an offset in row numbers. The result is that row number one of the first table will get the disposition date of row number two of the second table. I name the disposition date from the second table: next disposition date._<jupyter_code>query = """
    SELECT
        a.job_id,
        a.applicant_id,
        a.disposition,
        a.disposition_date,
        b.disposition_date AS next_disposition_date

    FROM
        (SELECT
            t1.*,
            ROW_NUMBER() OVER (PARTITION BY t1.job_id, t1.applicant_id ORDER BY t1.disposition_date, t1.disposition) AS sequence
        FROM
            applicants t1
        ) AS a

        LEFT JOIN
            (SELECT
                t2.*,
                ROW_NUMBER() OVER (PARTITION BY t2.job_id, t2.applicant_id ORDER BY t2.disposition_date, t2.disposition) AS sequence
            FROM
                applicants t2
            ) AS b
        ON
            a.job_id = b.job_id
            &&
            a.applicant_id = b.applicant_id
            &&
            (a.sequence + 1) = b.sequence
    ORDER BY
        a.job_id, applicant_id, disposition_date, disposition
"""

df = pd.read_sql_query(query, connection)
df<jupyter_output><empty_output><jupyter_text>--- Calculating the disposition duration.<jupyter_code>df.loc[:, "disposition_duration"] = (df.loc[:, "next_disposition_date"] - df.loc[:, "disposition_date"]).dt.days
df<jupyter_output><empty_output><jupyter_text>--- Visualizing the data.<jupyter_code>df_sub = df.loc[:, ["disposition", "disposition_duration"]].groupby(by="disposition").median()
dispositions = list(df_sub.index)
median_disposition_durations = df_sub.loc[:, "disposition_duration"].values

fig, ax = plt.subplots(figsize=(10,5))
y_pos = np.arange(len(dispositions))

ax.barh(y=y_pos, width=median_disposition_durations)
ax.set_yticks(y_pos)
ax.set_yticklabels(dispositions)
ax.invert_yaxis()
ax.set_xlabel("Days")
ax.set_title("Median Disposition Duration")
ax.grid(axis="x")

fig.tight_layout()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10953303206369701, 'fraction_numerical': 0.02819023025607919, 'mean_word_length': 3.5658153241650297, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, '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': '163570', 'license': 'MIT', 'n_tokens_mistral': 1596, 'n_tokens_neox': 1449, 'n_words': 441, 'path': 'united_nations.ipynb', 'repo_name': 'marcusvanwinden/united_nations', 'size': '4647'}