<jupyter_start><jupyter_text>This Jupyter Notebook contains code that accompanies <NAME>'s Medium Blog Post Binning Records on a Continuous Variable with Pandas Cut and QCut: When, why, and how to transform a numeric feature into a categorical feature<jupyter_code># import necessary packages
import pandas as pd
from sodapy import Socrata
from matplotlib import pyplot as plt
import numpy as np
from scipy import stats
import random
# import data

# Unauthenticated client only works with public data sets. Note 'None'
# in place of application token, and no username or password:
client = Socrata("data.seattle.gov", None)
results = client.get("k9m8-nhz3", limit=1000)

# Convert to pandas DataFrame
df = pd.DataFrame.from_records(results)
# this data set is City of Seattle Wages: Comparison by Gender –Wage Progression Job Titles
# https://data.seattle.gov/City-Business/City-of-Seattle-Wages-Comparison-by-Gender-Wage-Pr/k9m8-nhz3

df.head()
# fill NaN with 0
df = df.fillna(0)

# cast numeric strings as floats
cols = df.columns.drop(['job_classification', 'notes'])
df[cols] = df[cols].astype(float)
df.info()
df.head(100)<jupyter_output><empty_output><jupyter_text>Ungrouped Data<jupyter_code>df['pct_female'] = df['no_female_empl'] / df['total_no_empl'] *100
x = df['total_avg_hrly_rate']
y = df['pct_female']
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.xlim(0, 100)
plt.scatter(x, y, alpha=.5)
plt.title('Seattle City Job Percent Female vs. Average Hourly Rate')
plt.plot(np.unique(x), np.poly1d(np.polyfit(x, y, 1))(np.unique(x)), color='black')
fig.tight_layout()
fig.savefig('./images/ungrouped.png')
slope, intercept, rvalue, pvalue, stderr = stats.linregress(x, y)
rvalue*rvalue
stats.linregress(x, y)<jupyter_output><empty_output><jupyter_text>Grouping job titles by total average hourly rate<jupyter_code>df['pay_grp_cut_n'] = pd.cut(df['total_avg_hrly_rate'], 5)
df.head().T
# get sums within group - esp for employee counts
grouped = df.groupby(df['pay_grp_cut_n'], as_index=False).sum()
grouped
df['pay_grp_cut_n'].value_counts().sort_index().reset_index()
cut_counts = df['pay_grp_cut_n'].value_counts().sort_index().reset_index()

x = cut_counts['index'].astype(str)
y = cut_counts['pay_grp_cut_n']

fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Hourly Pay Group')
plt.ylabel('Job Title Count')
plt.title('Job Title Count vs. Hourly Pay Group with pd.cut')
plt.bar(x, y, alpha=.5)

fig.tight_layout()
fig.savefig('./images/cut5.png');
x = grouped['pay_grp_cut_n'].astype(str)
y = grouped['no_female_empl'] / grouped['total_no_empl'] *100
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.title('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
plt.ylim(0,100)
plt.bar(x, y, alpha=.5)
fig.tight_layout()
fig.savefig('./images/cut5_pct_female');<jupyter_output><empty_output><jupyter_text>labels<jupyter_code>df['pay_grp_cut_l'] = pd.cut(df['total_avg_hrly_rate'], 5, labels=['Very Low', 'Low', 'Medium', 'High', 'Very High'])
grouped1 = df.groupby(df['pay_grp_cut_l'], as_index=False).sum()
x1 = grouped1['pay_grp_cut_l']
y1 = grouped1['no_female_empl'] / grouped1['total_no_empl'] *100
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.title('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
plt.ylim(0,100)
plt.bar(x1, y1, alpha=.5);
for grp in df['pay_grp_cut_l'].unique():
    a = df.loc[df['pay_grp_cut_l'] == grp, 'pct_female']
    b = df.loc[df['pay_grp_cut_l'] != grp, 'pct_female']
    if len(a) < len(b):
        print(grp, '\n', stats.ttest_ind(a, random.sample(list(b), len(a))))
    if len(b) < len(a):
        print(grp, '\n', stats.ttest_ind(b, random.sample(list(a), len(b))))<jupyter_output>Low 
 Ttest_indResult(statistic=-4.51050133075541, pvalue=7.674220758165174e-06)
Medium 
 Ttest_indResult(statistic=-3.058629424558148, pvalue=0.0023394509803471124)
Very Low 
 Ttest_indResult(statistic=1.6010308463712921, pvalue=0.11445362155638761)
Very High 
 Ttest_indResult(statistic=-2.3719501183053397, pvalue=0.03257032845192342)
High 
 Ttest_indResult(statistic=-3.2374701494360276, pvalue=0.002063604729787673)<jupyter_text>Choose Group Boundaries (x-axis)<jupyter_code>df['pay_grp_cut_set_boundaries'] = pd.cut(df['total_avg_hrly_rate'], [5,20,35,50,65,80])
grouped2 = df.groupby(df['pay_grp_cut_set_boundaries'], as_index=False).sum()
x2 = grouped2['pay_grp_cut_set_boundaries'].astype(str)
y2 = grouped2['no_female_empl'] / grouped['total_no_empl'] *100
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.title('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
plt.ylim(0,100)
plt.bar(x2, y2, alpha=.5);
# graph for blog

fig, axs = plt.subplots(nrows=1, ncols=2, figsize=(12,8))
axs[0].set_xlabel('Average Hourly Rate')
axs[1].set_xlabel('Average Hourly Rate')
axs[0].set_ylabel('Percent Female')
axs[1].set_ylabel('Percent Female')
fig.suptitle('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
axs[0].set_ylim(0,50)
axs[1].set_ylim(0,50)
axs[0].set_title('Custom Labels')
axs[1].set_title('Custom Bins')
axs[0].bar(x1, y1, alpha=.5)
axs[1].bar(x2, y2, alpha=.5)
fig.savefig('./images/custom_bins.png');<jupyter_output><empty_output><jupyter_text>Even Volume Groups<jupyter_code>df['pay_grp_qcut_n'] = pd.qcut(df['total_avg_hrly_rate'], 5)
grouped = df.groupby(df['pay_grp_qcut_n'], as_index=False).sum()
x = [str(x) for x in grouped['pay_grp_qcut_n']]
y = grouped['no_female_empl'] / grouped['total_no_empl']
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.title('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
plt.ylim(0,1)
plt.bar(x, y, alpha=.5, color='#1896ac')
fig.savefig('./images/qcut_5_bins.png');
df['pay_grp_qcut_l'] = pd.qcut(df['total_avg_hrly_rate'], 5, labels=['Very Low', 'Low', 'Medium', 'High', 'Very High'])
grouped = df.groupby(df['pay_grp_qcut_l'], as_index=False).sum()
x = grouped['pay_grp_qcut_l']
y = grouped['no_female_empl'] / grouped['total_no_empl']
fig, ax = plt.subplots(figsize=(12,8))
plt.xlabel('Average Hourly Rate')
plt.ylabel('Percent Female')
plt.title('Percent Female vs. Average Hourly Rate for Seattle City Jobs')
plt.ylim(0,1)
plt.bar(x, y, alpha=.5, color='#1896ac');
qcut_counts = df['pay_grp_qcut_l'].value_counts().sort_index().reset_index()
cut_counts = df['pay_grp_cut_l'].value_counts().sort_index().reset_index()

x1 = cut_counts['index'].astype(str)
y1 = cut_counts['pay_grp_cut_l']

x2 = qcut_counts['index'].astype(str)
y2 = qcut_counts['pay_grp_qcut_l']

fig, axs = plt.subplots(nrows=1, ncols=2, figsize=(12,8))
axs[0].set_xlabel('Hourly Pay Group')
axs[1].set_xlabel('Hourly Pay Group')
axs[0].set_ylabel('Job Title Count')
axs[1].set_ylabel('Job Title Count')
fig.suptitle('Job Title Count vs. Hourly Pay Group with cut and qcut')
axs[0].set_ylim(0, 500)
axs[1].set_ylim(0, 500)
axs[0].set_title('Cut')
axs[1].set_title('QCut')
axs[1].bar(x2, y2, alpha=.5, color='#1896ac')
axs[0].bar(x1, y1, alpha=.5)
fig.savefig('./images/cut_qcut_counts.png');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16719591329559574, 'fraction_numerical': 0.04928896865939528, 'mean_word_length': 7.031042128603104, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 25, 'https://': 1, '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': '98281', 'license': 'MIT', 'n_tokens_mistral': 3175, 'n_tokens_neox': 2891, 'n_words': 556, 'path': 'pandas_cut_qcut.ipynb', 'repo_name': 'allisonhonold/cut_qcut_blog', 'size': '7251'}