<jupyter_start><jupyter_text>Split into applicant, offer, and school info dataframes<jupyter_code>applicant_cols = [
    (                 'School DBN', 'Unnamed: 0_level_1'),
    (                 'Applicants',              'Total'),
    (         'Unnamed: 4_level_0',              'Asian'),
    (         'Unnamed: 5_level_0',              'Black'),
    (         'Unnamed: 6_level_0',           'Hispanic'),
    (         'Unnamed: 7_level_0',              'Other'),
    (         'Unnamed: 8_level_0',              'White'),
    (         'Unnamed: 9_level_0',            'Private'),
    (        'Unnamed: 10_level_0',            'Poverty'),
    (        'Unnamed: 11_level_0',                'ELL'),
    (        'Unnamed: 12_level_0',                'SWD'),
]

offer_cols = [
    (                 'School DBN', 'Unnamed: 0_level_1'),
    (                     'Offers',              'Total'),
    (        'Unnamed: 14_level_0',              'Asian'),
    (        'Unnamed: 15_level_0',              'Black'),
    (        'Unnamed: 16_level_0',           'Hispanic'),
    (        'Unnamed: 17_level_0',              'Other'),
    (        'Unnamed: 18_level_0',              'White'),
    (        'Unnamed: 19_level_0',            'Private'),
    (        'Unnamed: 20_level_0',            'Poverty'),
    (        'Unnamed: 21_level_0',                'ELL'),
    (        'Unnamed: 22_level_0',                'SWD')
]

info_cols = [
    (                 'School DBN', 'Unnamed: 0_level_1'),
    (                'School Name', 'Unnamed: 1_level_1'),
    ('School Admissions Method(s)', 'Unnamed: 2_level_1'),
]

applicants = foil[applicant_cols].copy()
offers = foil[offer_cols].copy()
info = foil[info_cols].copy()
info.columns = ['school_name', 'admissions_method']
applicants.columns = applicants.columns.get_level_values(1)
applicants = applicants.rename(columns={'Unnamed: 0_level_1':'dbn'})
applicants = applicants.set_index('dbn')

offers.columns = offers.columns.get_level_values(1)
offers = offers.rename(columns={'Unnamed: 0_level_1':'dbn'})
offers = offers.set_index('dbn')

info.columns = info.columns.get_level_values(1)
info = info.rename(columns={'Unnamed: 0_level_1':'dbn'})
info = info.set_index('dbn')

screened = metrics.loc[info.index]
g = partial(min, 5)

def c(row):
    replacement = redacted_applicants.apply(g).loc[row.name]
    for key in row.keys():
        if row[key] == 's':
            row[key] = replacement
    return row

def replace_s_offers(row):
    replacement = redacted_offers.apply(g).loc[row.name]
    for key in row.keys():
        if row[key] == 's':
            row[key] = replacement
    return row
min_applicants = applicants.copy()
max_applicants = applicants.copy()

# Replace redactions w/ min possible
min_applicants = min_applicants.replace('s', 0).replace('s^', 6)
min_applicants = min_applicants.apply(pd.to_numeric, errors='coerce')

# Calculate total redacted students for each row
unredacted_applicants = applicants[['Asian','Black','Hispanic','Other','White','Private']].apply(pd.to_numeric, errors='coerce').sum(axis=1)
redacted_applicants   = min_applicants['Total'].sub(unredacted_applicants)

# Replace redactions w max possible
max_applicants = max_applicants.apply(replace_s_applicants, axis=1)
max_applicants = max_applicants.apply(pd.to_numeric, errors='coerce')
max_applicants = max_applicants.apply(lambda row: row.fillna(redacted_applicants[row.name]), axis=1)
min_offers = offers.copy()
max_offers = offers.copy()

# Replace redactions w/ min possible 
min_offers = min_offers.replace('s', 0).replace('s^', 6)
min_offers = min_offers.apply(pd.to_numeric, errors='coerce')

# Calculate total redacted students for each row
unredacted_offers = offers[['Asian','Black','Hispanic','Other','White','Private']].apply(pd.to_numeric, errors='coerce').sum(axis=1)
redacted_offers   = min_offers['Total'].sub(unredacted_offers)

# Replace redactions w max possible
max_offers = max_offers.apply(replace_s, axis=1).apply(pd.to_numeric, errors='coerce')
max_offers = max_offers.apply(lambda row: row.fillna(redacted_offers[row.name]), axis=1)
avg_offers = min_offers.add(max_offers).div(2)
avg_applicants = min_applicants.add(max_applicants).div(2)
folder = '../data/cleaned/'

max_applicants.to_csv(folder + 'max_applicants.csv')
min_applicants.to_csv(folder + 'min_applicants.csv')
avg_applicants.to_csv(folder + 'avg_applicants.csv')
applicants.to_csv(folder + 'applicants.csv')

max_offers.to_csv(folder + 'max_offers.csv')
min_offers.to_csv(folder + 'min_offers.csv')
avg_offers.to_csv(folder + 'avg_offers.csv')
offers.to_csv(folder + 'offers.csv')

info.to_csv(folder + 'info.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14915832090347325, 'fraction_numerical': 0.01704666524611123, 'mean_word_length': 3.213644524236984, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '286597', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1727, 'n_tokens_neox': 1602, 'n_words': 264, 'path': 'notebooks/1-data-cleaning.ipynb', 'repo_name': 'the-markup/investigation-nyc-high-school-admissions', 'size': '4693'}