def ap(noble_aps):
    #initialize new df
    ap_tests = pd.DataFrame()
    # add id
    ap_tests['noble_powerschool_id'] = noble_aps['student_number']
    # add date
    ap_tests['date'] = pd.to_datetime(noble_aps['test_date'])
    # add score 
    ap_tests['score'] = noble_aps['numscore']
    # load ap dictionary from manually coded csv
    subject_dict = create_conversion_dict(config.PERSISTENCE_PATH + '/code/etl/mappers/APsubjecttranslation.csv')
    # do conversions from freetext to coded options
    noble_aps.testname = noble_aps.testname.apply(lambda x: x.replace('AP ', '')) #remove the 'AP '
    ap_tests['subject'] = noble_aps.testname.fillna('missing').apply(lambda x: convert_free_text(subject_dict, x))
    return ap_tests
def ap(noble_aps):
    #initialize new df
    ap_tests = pd.DataFrame()
    # add id
    ap_tests['noble_powerschool_id'] = noble_aps['student_number']
    # add date
    ap_tests['date'] = pd.to_datetime(noble_aps['test_date'])
    # add score
    ap_tests['score'] = noble_aps['numscore']
    # load ap dictionary from manually coded csv
    subject_dict = create_conversion_dict(
        config.PERSISTENCE_PATH + '/code/etl/mappers/APsubjecttranslation.csv')
    # do conversions from freetext to coded options
    noble_aps.testname = noble_aps.testname.apply(
        lambda x: x.replace('AP ', ''))  #remove the 'AP '
    ap_tests['subject'] = noble_aps.testname.fillna('missing').apply(
        lambda x: convert_free_text(subject_dict, x))
    return ap_tests
def enrollments(enrollment):

    # initialize clean dataframe 
    clean_enrollment = pd.DataFrame()

    # add student and college ids
    clean_enrollment['noble_student_sf_id'] = enrollment['Student__c']
    clean_enrollment['noble_college_sf_id'] = enrollment['College__c']

    # convert date columns to datetime to do date cleaning
    enrollment['Start_Date__c'] = pd.to_datetime(enrollment['Start_Date__c'])
    enrollment['End_Date__c'] = pd.to_datetime(enrollment['End_Date__c'])
    enrollment['Date_Last_Verified__c'] = pd.to_datetime(enrollment['Date_Last_Verified__c'])

    #remove weird dates, add to new df
    clean_enrollment['start_date'] = enrollment.Start_Date__c.apply(wrong_dates_to_null)
    clean_enrollment['end_date'] = enrollment.End_Date__c.apply(wrong_dates_to_null)
    clean_enrollment['date_last_verified'] = enrollment.Date_Last_Verified__c.apply(wrong_dates_to_null)

    # add status, data source, and degree type as is
    clean_enrollment['status'] = enrollment.Status__c
    clean_enrollment['data_source'] = enrollment.Data_Source__c

    # Living on campus
    clean_enrollment['living_on_campus'] = np.nan

    # Degree type, without apostrophes
    clean_enrollment['degree_type'] = enrollment.Degree_Type__c.dropna().apply(lambda x: str(x).replace("'",""))

    # load degree and major dictionaries from manually coded csvs
    degree_dict = create_conversion_dict(config.PERSISTENCE_PATH + '/code/etl/mappers/degreetranslation.csv')
    major_dict = create_conversion_dict(config.PERSISTENCE_PATH + '/code/etl/mappers/majortranslation.csv')

    # do conversions from freetext to coded options
    clean_enrollment['degree_subject'] = enrollment.Degree_Text__c.fillna('missing').apply(lambda x: convert_free_text(degree_dict, x))
    clean_enrollment['major'] = enrollment.Major_Text__c.fillna('missing').apply(lambda x: convert_free_text(major_dict, x))

    # convert withdrawal reasons to several boolean categories
    def boolean_withdrawal(dummy_code, input_code):
        if input_code == 'missing':
            return np.nan
        else:
            codes = input_code.split(';')
            dummy_booleans = [True if code == dummy_code else False for code in codes]
            if sum(dummy_booleans) > 0:
                return True
            else: return False

    withdrawal_codes = ['Financial', 'Academic', 'Motivational', 'Family', 'Health', 'Social', 'Racial Conflict']
    for dummy_code in withdrawal_codes:
        clean_enrollment['withdrawal_reason_' + "_".join(dummy_code.lower().split())] = enrollment.Withdrawal_code__c.fillna('missing').apply(lambda x: boolean_withdrawal(dummy_code, x))
    clean_enrollment.rename(columns={'withdrawal_reason_racial_conflict': 'withdrawal_reason_racial'}, inplace=True)

    # Return clean df
    return clean_enrollment
def enrollments_table(kipp_nj_enrollments):

	'''Cleans KIPP NJ enrollment data to match our database schema'''

	# remove the did not enroll, other, and deferred enrollment types
	clean_enrollments = kipp_nj_enrollments[~kipp_nj_enrollments.status.isin(['Other', 'Did Not Enroll', 'Deferred'])]
	# rename Withdrawn to withdrew, matriculated to matriculating

	clean_enrollments.status = clean_enrollments.status.apply(lambda x: map_value_from_dict(status_fixed_mapping, x))
	# made dates into dates
	clean_enrollments.start_date = pd.to_datetime(clean_enrollments.start_date)
	clean_enrollments.end_date = pd.to_datetime(clean_enrollments.end_date)
	clean_enrollments.date_last_verified = pd.to_datetime(clean_enrollments.date_last_verified)
	# deal with degree type
	#remove high school diploma and GED, only interested in college enrollments
	clean_enrollments = clean_enrollments[~clean_enrollments.degree_type.isin(['High School Diploma', 'GED'])]
	clean_enrollments.degree_type = clean_enrollments.degree_type.apply(lambda x: map_value_from_dict(degree_fixed_mapping, x))
	# clean up degree subject
	clean_enrollments.degree_subject= clean_enrollments.degree_type.combine(clean_enrollments.degree_subject, func = code_degree_subject)
	# clean up major
	major_dict = create_conversion_dict(config.PERSISTENCE_PATH + '/code/etl/mappers/majortranslation.csv')
	# do conversions from freetext to coded options
	clean_enrollments['major'] = clean_enrollments.major.fillna('missing').apply(lambda x: convert_free_text(major_dict, x))
	#map transfer reasons to the reasons we keep track of
	withdrawal_reasons = pd.get_dummies(clean_enrollments.transfer_reason__c).astype(bool)
	withdrawal_reasons.columns = ['withdrawal_reason_academic', 'withdrawal_reason_career', 'withdrawal_reason_financial', 'withdrawal_reason_other', 'withdrawal_reason_placement', 'withdrawal_reason_relocation', 'withdrawal_reason_social']
	withdrawal_reasons.drop(['withdrawal_reason_relocation', 'withdrawal_reason_placement', 'withdrawal_reason_other', 'withdrawal_reason_career'], axis = 1,inplace = True)
	# join the columns back into the original 
	clean_enrollments = clean_enrollments.join(withdrawal_reasons)
	clean_enrollments['withdrawal_reason_motivational'] = np.nan
	clean_enrollments['withdrawal_reason_family'] = np.nan 
	clean_enrollments['withdrawal_reason_health'] = np.nan
	clean_enrollments['withdrawal_reason_racial'] = np.nan

	# Drop invalid IPEDS id
	clean_enrollments['college_ncesid'] = clean_enrollments['college_ncesid'].convert_objects(convert_numeric=True)
	clean_enrollments.loc[clean_enrollments['college_ncesid'] > 999999, 'college_ncesid'] = np.nan
	clean_enrollments['college_ncesid'] = clean_enrollments['college_ncesid'].apply(int_with_NaN_tostr)

	clean_enrollments.drop(['transfer_reason__c', 'college_salesforce_id'],axis = 1,inplace = True)
	clean_enrollments.rename(columns={'student_salesforce_id':'kipp_nj_sf_id', 'college_ncesid': 'ipedsid'}, inplace=True)	
	return clean_enrollments
def enrollments(enrollment):

    # initialize clean dataframe
    clean_enrollment = pd.DataFrame()

    # add student and college ids
    clean_enrollment['noble_student_sf_id'] = enrollment['Student__c']
    clean_enrollment['noble_college_sf_id'] = enrollment['College__c']

    # convert date columns to datetime to do date cleaning
    enrollment['Start_Date__c'] = pd.to_datetime(enrollment['Start_Date__c'])
    enrollment['End_Date__c'] = pd.to_datetime(enrollment['End_Date__c'])
    enrollment['Date_Last_Verified__c'] = pd.to_datetime(
        enrollment['Date_Last_Verified__c'])

    #remove weird dates, add to new df
    clean_enrollment['start_date'] = enrollment.Start_Date__c.apply(
        wrong_dates_to_null)
    clean_enrollment['end_date'] = enrollment.End_Date__c.apply(
        wrong_dates_to_null)
    clean_enrollment[
        'date_last_verified'] = enrollment.Date_Last_Verified__c.apply(
            wrong_dates_to_null)

    # add status, data source, and degree type as is
    clean_enrollment['status'] = enrollment.Status__c
    clean_enrollment['data_source'] = enrollment.Data_Source__c

    # Living on campus
    clean_enrollment['living_on_campus'] = np.nan

    # Degree type, without apostrophes
    clean_enrollment['degree_type'] = enrollment.Degree_Type__c.dropna().apply(
        lambda x: str(x).replace("'", ""))

    # load degree and major dictionaries from manually coded csvs
    degree_dict = create_conversion_dict(
        config.PERSISTENCE_PATH + '/code/etl/mappers/degreetranslation.csv')
    major_dict = create_conversion_dict(
        config.PERSISTENCE_PATH + '/code/etl/mappers/majortranslation.csv')

    # do conversions from freetext to coded options
    clean_enrollment['degree_subject'] = enrollment.Degree_Text__c.fillna(
        'missing').apply(lambda x: convert_free_text(degree_dict, x))
    clean_enrollment['major'] = enrollment.Major_Text__c.fillna(
        'missing').apply(lambda x: convert_free_text(major_dict, x))

    # convert withdrawal reasons to several boolean categories
    def boolean_withdrawal(dummy_code, input_code):
        if input_code == 'missing':
            return np.nan
        else:
            codes = input_code.split(';')
            dummy_booleans = [
                True if code == dummy_code else False for code in codes
            ]
            if sum(dummy_booleans) > 0:
                return True
            else:
                return False

    withdrawal_codes = [
        'Financial', 'Academic', 'Motivational', 'Family', 'Health', 'Social',
        'Racial Conflict'
    ]
    for dummy_code in withdrawal_codes:
        clean_enrollment['withdrawal_reason_' + "_".join(dummy_code.lower(
        ).split())] = enrollment.Withdrawal_code__c.fillna('missing').apply(
            lambda x: boolean_withdrawal(dummy_code, x))
    clean_enrollment.rename(columns={
        'withdrawal_reason_racial_conflict':
        'withdrawal_reason_racial'
    },
                            inplace=True)

    # Return clean df
    return clean_enrollment
Exemple #6
0
def enrollments_table(kipp_nj_enrollments):
    '''Cleans KIPP NJ enrollment data to match our database schema'''

    # remove the did not enroll, other, and deferred enrollment types
    clean_enrollments = kipp_nj_enrollments[~kipp_nj_enrollments.status.isin(
        ['Other', 'Did Not Enroll', 'Deferred'])]
    # rename Withdrawn to withdrew, matriculated to matriculating

    clean_enrollments.status = clean_enrollments.status.apply(
        lambda x: map_value_from_dict(status_fixed_mapping, x))
    # made dates into dates
    clean_enrollments.start_date = pd.to_datetime(clean_enrollments.start_date)
    clean_enrollments.end_date = pd.to_datetime(clean_enrollments.end_date)
    clean_enrollments.date_last_verified = pd.to_datetime(
        clean_enrollments.date_last_verified)
    # deal with degree type
    #remove high school diploma and GED, only interested in college enrollments
    clean_enrollments = clean_enrollments[~clean_enrollments.degree_type.
                                          isin(['High School Diploma', 'GED'])]
    clean_enrollments.degree_type = clean_enrollments.degree_type.apply(
        lambda x: map_value_from_dict(degree_fixed_mapping, x))
    # clean up degree subject
    clean_enrollments.degree_subject = clean_enrollments.degree_type.combine(
        clean_enrollments.degree_subject, func=code_degree_subject)
    # clean up major
    major_dict = create_conversion_dict(
        config.PERSISTENCE_PATH + '/code/etl/mappers/majortranslation.csv')
    # do conversions from freetext to coded options
    clean_enrollments['major'] = clean_enrollments.major.fillna(
        'missing').apply(lambda x: convert_free_text(major_dict, x))
    #map transfer reasons to the reasons we keep track of
    withdrawal_reasons = pd.get_dummies(
        clean_enrollments.transfer_reason__c).astype(bool)
    withdrawal_reasons.columns = [
        'withdrawal_reason_academic', 'withdrawal_reason_career',
        'withdrawal_reason_financial', 'withdrawal_reason_other',
        'withdrawal_reason_placement', 'withdrawal_reason_relocation',
        'withdrawal_reason_social'
    ]
    withdrawal_reasons.drop([
        'withdrawal_reason_relocation', 'withdrawal_reason_placement',
        'withdrawal_reason_other', 'withdrawal_reason_career'
    ],
                            axis=1,
                            inplace=True)
    # join the columns back into the original
    clean_enrollments = clean_enrollments.join(withdrawal_reasons)
    clean_enrollments['withdrawal_reason_motivational'] = np.nan
    clean_enrollments['withdrawal_reason_family'] = np.nan
    clean_enrollments['withdrawal_reason_health'] = np.nan
    clean_enrollments['withdrawal_reason_racial'] = np.nan

    # Drop invalid IPEDS id
    clean_enrollments['college_ncesid'] = clean_enrollments[
        'college_ncesid'].convert_objects(convert_numeric=True)
    clean_enrollments.loc[clean_enrollments['college_ncesid'] > 999999,
                          'college_ncesid'] = np.nan
    clean_enrollments['college_ncesid'] = clean_enrollments[
        'college_ncesid'].apply(int_with_NaN_tostr)

    clean_enrollments.drop(['transfer_reason__c', 'college_salesforce_id'],
                           axis=1,
                           inplace=True)
    clean_enrollments.rename(columns={
        'student_salesforce_id': 'kipp_nj_sf_id',
        'college_ncesid': 'ipedsid'
    },
                             inplace=True)
    return clean_enrollments