<jupyter_start><jupyter_text>Here, we're going to combine multiple files This file is a quick port of the prior year's process, which was automated with a script and YAML fileThere are filenames in the yaml file that __need to be updated based on this year's files__ (specifically by incrementing the year)<jupyter_code># The cells here are mostly just transcriptions of the merge_files.py script in the root directory of this repo
# EDIT THE YAML FILE TO HAVE REFERENCES TO THE MOST RECENT YEAR!
import yaml
import pandas as pd
import numpy as np
import os

SETTINGS_FILE = '../raw_inputs/inputs.yaml'
####### Function definitions ###############
### converter functions ###
def safe2int(x):
    '''converts to int if possible, otherwise is a string'''
    try:
        return int(x)
    except:
        return x

def safe2f(x):
    '''converts to float if possible, otherwise is a string'''
    try:
        return float(x)
    except:
        return x

def p2f(x):
    '''converts percent string to float number'''
    return None if x == 'N/A' else float(x.strip('%'))/100

# This dictionary lets the yaml input translate to one of the functions above
CONVERTERS = {'safe2int': safe2int,
              'safe2f': safe2f,
              'p2f': p2f,
              }

def safe_divide(x):
    '''Divides the first element by the second'''
    if np.isnan(x[0]) or np.isnan(x[1]) or x[1] == 0:
        return np.nan
    else:
        return x[0]/x[1]

### Substantive functions
### These are used for processing input files based on settings in the yaml file

def read_input_file(filename, params):
    """Reads an input file into a DataFrame based on the provided parameters"""
    converters = params['converters'].copy()
    for key, value in converters.items():
        converters[key] = CONVERTERS[value]  # now references actual function
    df = pd.read_csv(filename,
                       index_col=params['index_col'],
                       encoding=params['encoding'],
                       na_values=params['na_values'],
                       converters=converters)
    if 'reduce' in params: # reduce table to those matching this parameter
        var, value = params['reduce'].popitem()
        return df.loc[df[var] == value]
    else:
        return df

def insert_calc_column(df, input_df, ix, final_label, specs):
    '''Processes the insertion of a non-direct pull column into df using
    input_df as the raw table read from a file'''
    if specs['type'] == 'direct test':
        df.insert(ix, final_label, input_df[specs['source']])
    elif specs['type'] == 'remap':
        df.insert(ix, final_label, input_df[specs['source']].replace(
            specs['mapping']))
    elif specs['type'] == 'division':
        operands = specs['source'].split(sep='/')
        new_col = input_df[operands].apply(safe_divide, axis=1)
        df.insert(ix, final_label, new_col)
    elif specs['type'] == 'constant':
        df[final_label] = specs['source']
        
def process_read_instructions(df_in, columns):
    '''Reduces the raw DF (from file) down to a smaller DF based on the
    descriptions from the yaml file'''
    # First, handle the 'direct pull' columns for the base of the DF
    pull_columns = []
    pull_column_labels = []
    for column in columns: # iterates over the list of columns
        for label in column: # iterates over the single key in the dict
            if column[label]['type'] == 'direct pull':
                pull_column_labels.append(label)
                pull_columns.append(column[label]['source'])
    df = df_in[pull_columns]
    df.columns = pull_column_labels

    # Second, insert the more complicated columns in the correct place
    for i in range(len(columns)):
        column = columns[i]
        for label in column: # iterates over the single key in the dict
            if column[label]['type'] != 'direct pull':
                insert_calc_column(df, df_in, i-1, label, column[label])
    return df

# This is the settings file specifying the details of the merge
# Edit it directly to fix the year of the latest files (there is an "INCREMENT" comment where this is required)
with open(SETTINGS_FILE) as f:
    print(f.read())<jupyter_output>########################################################
# Master instructions for creation of directory
# Adds filenames in sequence along with the columns to use
# for each
#
# Most of the keys here are based on the arguments for
# the pandas.read_csv function
#
# Where relevant, an "INCREMENT" comment appears next
# to each line with a dated filename that needs to be
# incremented based on the latest file (downloaded in
# step 0)
########################################################

output_file: inputs/College Directory.xlsx
output_sheet_name: 'College Directory 2020'  # INCREMENT each year

input_details:
    - inputs/base_dir.csv:
        - file_setup:
            index_col: 0
            encoding: cp1252
            na_values: N/A
            converters:
                UNITID: safe2int
                ZIP: safe2int
        - UNITID:
            type: index
            source: UNITID
        - College Name:
            type: direct pull
            source: INSTNM
        - Co[...]<jupyter_text>From here below actually runs all of this setup<jupyter_code>####### Main script ####################
print('Loading configuration file...')
with open(SETTINGS_FILE, 'r') as ymlfile:
    cfg = yaml.load(ymlfile, yaml.FullLoader)

df = None
for file in cfg['input_details']:
    filename, details = file.popitem()
    print('Reading file ({})'.format(filename),flush=True)
    
    this_df = read_input_file(filename, details[0]['file_setup'])
    sliced_df = process_read_instructions(this_df, details[1:])
    
    if type(df) is not pd.DataFrame:
        df = sliced_df
    else:
        df = pd.concat([df, sliced_df], axis=1, join_axes=[df.index])<jupyter_output>Reading file (inputs/base_dir.csv)
Reading file (inputs/ic2018.csv)
Reading file (inputs/adm2018.csv)
Reading file (inputs/sfa1718.csv)
Reading file (inputs/ef2018a.csv)
Reading file (inputs/grad_rates.csv)
Reading file (inputs/ef2018d.csv)
Reading file (inputs/hd2018.csv)<jupyter_text>--- After the raw data has been loaded and combined, there are a few manipulations In order, we'll do:1. Calculation of AdjACT25, 50, and 75 fields (which add converted SAT to low ACT schools)2. Calculation of AdjSAT25, 50, and 75 fields (which add converted ACT to low SAT schools)3. Size Range4. Estimated AA_H retention--based on a formula and the gap between AA/H and Overall grad rates and Retention (will be 0-100)<jupyter_code># First, we'll create "AdjACT25, 50, and 75" fields for admissions analyses
# Next AdjACT25, 50, and 75
admissions_file = 'inputs/adm2018.csv'  # INCREMENT
sat_to_act_file = '../raw_inputs/sat_to_act.csv'
adm_fields = ['APPLCN','ADMSSN',
              'SATPCT','ACTPCT',
              'SATVR25','SATVR75','SATMT25','SATMT75',
              'ACTCM25','ACTCM75']
adm_df = pd.read_csv(admissions_file, index_col=['UNITID'],
                     usecols=['UNITID']+adm_fields,
                     na_values='.',
                     encoding='latin-1')
sat_to_act = pd.read_csv(sat_to_act_file, index_col=['SAT'],dtype={'SAT':int,'ACT':int},encoding='cp1252')
adm_df['pct_accepted'] = adm_df.ADMSSN/adm_df.APPLCN

def calculate_adjact25_50_75(df):
    """
    Estimates the median 'ACT' based on 25th to 75th percentile range of either ACT
    or converted SAT
    """
    adj25, adj50, adj75 = (np.nan, np.nan, np.nan)
    adj_source = "Neither"
    # Pick which source to use:
    if df.ACTPCT >= 20 and np.isfinite(df.ACTCM25) and np.isfinite(df.ACTCM75): #reasonable number of ACT
        adj_source = "ACT"
    elif df.SATPCT >= 20 and (np.isfinite(df.SATVR25) and np.isfinite(df.SATMT25) and
                              np.isfinite(df.SATVR75) and np.isfinite(df.SATMT75)): #same threshold for SAT
        adj_source = "SAT"
    elif np.isfinite(df.ACTCM25) and np.isfinite(df.ACTCM75): # There are at least ACT values
        adj_source = "ACT"
    elif (np.isfinite(df.SATVR25) and np.isfinite(df.SATMT25) and
          np.isfinite(df.SATVR75) and np.isfinite(df.SATMT75)): # At least some SAT values
        adj_source = "SAT"
        
    if adj_source == "ACT":    
        adj25 = df.ACTCM25
        adj75 = df.ACTCM75
        adj50 = (adj25 + adj75)/2
    elif adj_source == "SAT":
        sat25 = int(np.round(df.SATVR25+df.SATMT25,decimals=-1))
        sat75 = int(np.round(df.SATVR75+df.SATMT75,decimals=-1))
        adj25 = sat_to_act.ACT[sat25]
        adj75 = sat_to_act.ACT[sat75]
        adj50 = (adj25 + adj75)/2
    return (adj25, adj50, adj75)

adm_df[['AdjACT25','AdjACT50','AdjACT75']] = adm_df.apply(calculate_adjact25_50_75,axis=1,result_type='expand')
# Visually inspect the first few converted rows (looking for AdjACT25, 50, 75 values:
adm_df[adm_df['ACTPCT']<20].head()
# Now add them to the main DF
for var in ['AdjACT25','AdjACT50','AdjACT75']:
    df[var] = df.index.map(lambda x: adm_df[var].get(x, default=np.nan))
# Second, we'll follow exactly the same process to create similar variables for SAT
admissions_file = 'inputs/adm2018.csv'  # INCREMENT
act_to_sat_file = '../raw_inputs/act_to_sat.csv'
adm_fields = ['APPLCN','ADMSSN',
              'SATPCT','ACTPCT',
              'SATVR25','SATVR75','SATMT25','SATMT75',
              'ACTCM25','ACTCM75']
adm_df = pd.read_csv(admissions_file, index_col=['UNITID'],
                     usecols=['UNITID']+adm_fields,
                     na_values='.',
                     encoding='latin-1')
act_to_sat = pd.read_csv(act_to_sat_file, index_col=['ACT'],dtype={'SAT':int,'ACT':int},encoding='cp1252')
adm_df['pct_accepted'] = adm_df.ADMSSN/adm_df.APPLCN

def calculate_adjsat25_50_75(df):
    """
    Estimates the median 'SAT' based on 25th to 75th percentile range of either SAT
    or converted ACT
    """
    adj25, adj50, adj75 = (np.nan, np.nan, np.nan)
    adj_source = "Neither"
    # Pick which source to use:
    if df.SATPCT >= 20 and (np.isfinite(df.SATVR25) and np.isfinite(df.SATMT25) and
                              np.isfinite(df.SATVR75) and np.isfinite(df.SATMT75)):  # reasonable threshold
        adj_source = "SAT"
    elif df.ACTPCT >= 20 and np.isfinite(df.ACTCM25) and np.isfinite(df.ACTCM75):  # same cutoff for ACT
        adj_source = "ACT"
    elif (np.isfinite(df.SATVR25) and np.isfinite(df.SATMT25) and
          np.isfinite(df.SATVR75) and np.isfinite(df.SATMT75)):  # at least some SAT
        adj_source = "SAT"
    elif np.isfinite(df.ACTCM25) and np.isfinite(df.ACTCM75):  # at least some ACT
        adj_source = "ACT"
        
    if adj_source == "SAT":
        adj25 = int(np.round(df.SATVR25+df.SATMT25,decimals=-1))
        adj75 = int(np.round(df.SATVR75+df.SATMT75,decimals=-1))
        adj50 = (adj25 + adj75)/2
    elif adj_source == "ACT":
        act25 = int(df.ACTCM25)
        act75 = int(df.ACTCM75)
        try:
            adj25 = int(act_to_sat.SAT[int(act25)])
        except KeyError as e:
            if act25 < min(act_to_sat.index):  # If they reported an impossibly low score, correct
                adj25 = act_to_sat.SAT[min(act_to_sat.index)]
            else:
                raise e
        adj75 = int(act_to_sat.SAT[act75])
        adj50 = int((adj25 + adj75)/2)
    
    return (adj25, adj50, adj75)

adm_df[['AdjSAT25','AdjSAT50','AdjSAT75']] = adm_df.apply(calculate_adjsat25_50_75,axis=1,result_type='expand')
# Visually inspect the first few converted rows (looking for AdjSAT25, 50, 75 values:
adm_df[adm_df['SATPCT']<20].head()
# Now add them to the main DF
for var in ['AdjSAT25','AdjSAT50','AdjSAT75']:
    df[var] = df.index.map(lambda x: adm_df[var].get(x, default=np.nan))
# Third, we'll calculate Size Range
def return_size_range(num_ug):
    """Apply function to give a text response for Size Range"""
    if np.isnan(num_ug):
        return 'N/A'
    else:
        if num_ug >= 20000:
            return '20,000 and above'
        elif num_ug >= 10000:
            return '10,000-19,999'
        elif num_ug >= 5000:
            return '5,000-9,999'
        elif num_ug >= 1000:
            return '1,000-4,999'
        else:
            return 'Under 1,000'
df['Size Range'] = df['# of undergraduates'].apply(return_size_range)
# Fourth, estimated AA/H retention
def calc_aa_h_retention(x):
    """Apply function to estimate the URM retention"""
    overall_grad, aa_h_grad, overall_retention = x
    if np.isfinite(overall_grad) and np.isfinite(aa_h_grad) and np.isfinite(overall_retention):
        overall_6yr_loss = 1 - overall_grad  # This number comes as a percentage
        aa_h_6yr_loss = 1 - aa_h_grad  # This number comes as a percentage
        overall_1yr_loss = 100 - overall_retention  # This number comes as 0-100
        if (overall_6yr_loss > 0) & (aa_h_6yr_loss > 0):
            aa_h_loss_as_share_of_overall = aa_h_6yr_loss / overall_6yr_loss
            aa_h_retention = 100 - aa_h_loss_as_share_of_overall * overall_1yr_loss  # comes as 0-100
            if (aa_h_retention > 0):
                return int(np.round(aa_h_retention,decimals=0))
            else:
                return 0
    return overall_retention
df['Estimated AA_H Retention'] = df[['Adj 6yr Grad','Adj 6yr Grad AA_H','Retention']].apply(
    calc_aa_h_retention, axis=1)
# Finally, write the output
writer = pd.ExcelWriter(cfg['output_file'], engine='xlsxwriter')
wb = writer.book
df.to_excel(writer, sheet_name=cfg['output_sheet_name'], na_rep='N/A')
writer.save()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12385861561119293, 'fraction_numerical': 0.03416789396170839, 'mean_word_length': 3.0287748442598637, 'pattern_counts': {'":': 4, '<': 14, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '462000', 'license': 'MIT', 'n_tokens_mistral': 4846, 'n_tokens_neox': 4453, 'n_words': 1233, 'path': 'Notebooks/Step_4_combine_files.ipynb', 'repo_name': 'NobleNetworkCharterSchools/annual-counseling', 'size': '13580'}