# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

# %store -r student_courses
# %store -r student_majors
# %store -r stem_paths
# %store -r jacobs
# %store -r jacobs_ethnicities
# %store -r bcdi
# %store -r waivers

# stacked bar plot: DESINV Course Enrollment Numbers by Ethnicity and Year
'''
graph_data = jacobs_ethnicities.groupby(['year.name','Ethnicity'], sort=False).count().unstack()['ppsk']
graph_data.plot(kind='bar', stacked=True, figsize=(12,8), xlabel='Year', ylabel='Number of Students', title='DESINV Course Enrollment Numbers by Ethnicity and Year')
'''

stem_pathways = stem_paths[:]

# make sure to have the stem_pathways (file with ethnicities) and student_courses (file with student course data) variables set
your_course = student_courses[(student_courses['course.subject'] == 'Engineering') & (student_courses['course.number'] == '27')]
# you can change 'ethnic.l1' if you need a more detailed ethnicity breakdown
your_course = your_course[['ppsk']].drop_duplicates(subset='ppsk').merge(stem_pathways[['ppsk','ethnic.l1']], on='ppsk')
your_course.groupby('ethnic.l1').count()[['ppsk']].transpose().plot(kind='bar', stacked=True, figsize=(12,8), xlabel='', ylabel='Number of Students', title='Your course\'s Ethnicity')


# +
# Fall 2017 DES INV Courses by Ethnicity

fall2017 = jacobs_ethnicities[jacobs_ethnicities['year.name'] == '2017 Fall'].groupby(['course.number','Ethnicity']).count().unstack()['year.name'].fillna(0)
fall2017 = fall2017[fall2017.index.isin(['198','98','10', '15', '190'])].reindex(['198','98','10', '15', '190'])
fall2017.plot(kind='bar', stacked=True, figsize=(12,8), xlabel='DESINV Course Number', ylabel='Number of Students', title='Fall 2017 DESINV Courses by Ethnicity')
# -

fall2017

test = fall2017.copy()
test['Total'] = test.sum(axis=1)

hmmm = test['Underrepresented Minority']

# +
urm = {'10': [], '15':[], '98':[], '190':[], '198':[]}

'''urm10 = []
urm15 = []
urm98 = []
urm190 = []
urm198 = []'''
# iterate through each semester
for year in jacobs_ethnicities['year.name'].unique():
    temp = jacobs_ethnicities[jacobs_ethnicities['year.name'] == year].groupby(['course.number','Ethnicity']).count().unstack()['year.name'].fillna(0)
    temp = temp[temp.index.isin(['198','98','10', '15', '190'])].reindex(['10', '15', '98', '190', '198'])
    temp['Total'] = temp.sum(axis=1)
    for class in temp.index:
        urm[class] = 
# -



student_majors



# +
undergrads = jacobs_ethnicities[jacobs_ethnicities["undergrad.status"] == 'Undergraduate']
first_courses = undergrads.groupby('ppsk', sort=False).first()
first_courses = first_courses.merge(student_majors[['ppsk','major']], on='ppsk')

# comparison to see first and last majors for any given student
#last_courses = undergrads.groupby('ppsk', sort=False).last()
#first_courses.merge(last_courses['year.name'], on='ppsk', how = 'left')[['year.name_x','year.name_y']]#.duplicated()
# -

first_courses

test = pd.DataFrame()

test['Total'] = first_courses.groupby(['major','Ethnicity']).count().unstack()['ppsk'].fillna(0).drop('Unknown',axis=1).sum(axis=1)#.sort_values('Underrepresented Minority', ascending = False)

test.sort_values(by='Total',ascending = False)

first_courses.groupby(['major','Ethnicity']).count().unstack()['ppsk'].fillna(0).sort_values('Underrepresented Minority', ascending = False)

test.sort_values(by='Total',ascending=False).head(6)

#index = ['Electrical Eng & Comp Sci', 'Letters & Sci Undeclared', 'L&S Computer Science', 'Cognitive Science', 'Mechanical Engineering', 'Art']
graphing = first_courses.groupby(['major','Ethnicity']).count().unstack()['ppsk'].fillna(0)
#graphing = graphing[graphing.index.isin(index)].reindex(index)
graphing.drop('Unknown',axis=1,inplace=True)

graphing['Total'] = graphing.sum(axis=1)

graphing.sort_values(by='Total', ascending = False).head(10)

graphing = graphing.sort_values(by='Total', ascending = False).drop('Total',axis=1)

# +
cleaned_jacobs = jacobs.loc[jacobs['ppsk'] != 'MISSING']
cleaned_jacobs['ppsk'] = cleaned_jacobs['ppsk'].astype(int)

wow = cleaned_jacobs.merge(student_majors[['ppsk','major']], on='ppsk', how='inner').drop_duplicates(subset='ppsk').merge(stem_paths[['ppsk','ethnic.l1','gender']], on='ppsk', how='left').fillna("Unknown").rename(columns = {'ethnic.l1':"Ethnicity"}).groupby(['major', 'Ethnicity']).count().unstack()['ppsk'].fillna(0)
wow['Total'] = wow.sum(axis=1)
# -

wow.sort_values(by='Total', ascending=False).drop('Total',axis=1).head().plot(kind = 'barh',figsize=(12,10), title='Top Jacobs Majors; Breakdown of Ethnicity', xlabel="").set_xlabel('Number of Students')

all_props = cleaned_jacobs.merge(student_majors[['ppsk','major']], on='ppsk', how='inner').drop_duplicates(subset='ppsk').merge(stem_paths[['ppsk','ethnic.l1','gender']], on='ppsk', how='left').fillna("Unknown").rename(columns = {'ethnic.l1':"Ethnicity"}).groupby(['major', 'Ethnicity']).count().unstack()['ppsk'].fillna(0)
all_props['Total'] = all_props.sum(axis=1)
all_props['URM Prop'] = all_props['Underrepresented Minority']/all_props['Total']
all_props.sort_values(by='URM Prop', ascending=False).query('Total > 5').head(10)

StarcoderdataJupyterScriptsDedupFiltered