# ---
# 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 glob
from collections import defaultdict

import pandas as pd
import numpy as np
from easydict import EasyDict

import warnings
warnings.filterwarnings("ignore")

import pickle
# -

excel_files = glob.glob('*.xlsx')


def create_course_col(df):
    subject = df['Subject']
    df['Subject'] = df['Subject'].astype(str)
    df['subject'] = [s[s.find("(") + 1:s.find(")")] for s in subject]
    df['Catalog Nbr'] = df['Catalog Nbr'].astype(str)
    df['course'] = df['subject'] + ' ' + df['Catalog Nbr']
    df.sort_values(by=['course'], inplace=True)
    return df


for file in excel_files:
    df = pd.read_excel(file)    
    if 'Catalog Nbr' not in df.columns:
        df.rename(columns={'Catalog': 'Catalog Nbr',
                           'Descr': 'title'}, inplace=True)
        create_course_col(df)
        df.to_csv(f'{file}.csv', index=False)
    else: print(file)

csv_files = glob.glob('*.csv')

csv_files


# ### Concat Each Requirement

def concat_each_requirement(files):
    
    file_list = []
    
    for file in files:
        df = pd.read_csv(file)
        file_list.append(df)
    
    df = pd.concat(file_list, axis=0, ignore_index=True)
    
    return df


# ### Read Scraped Course Description Files

def read_scraped_course_des_files(file):
    df = pd.read_csv(file)
    df.dropna(subset=['catalog_number'], inplace=True)
    df = df.fillna('')
    df['requirements_distribution'] = df['requirements_distribution'] + ', ' + df['other']
    df['requirements_distribution'] = [x.split(', ') for x in df['requirements_distribution']]
    
    subject = df['subject']
    df['subject'] = df['subject'].astype(str)
    df['subject'] = [s[s.find("(") + 1:s.find(")")] for s in subject]
    df['catalog_number'] = [int(i) for i in df['catalog_number']]
    df['catalog_number'] = df['catalog_number'].astype(str)
    df['course'] = df['subject'] + ' ' + df['catalog_number']
    df.sort_values(by=['course'], inplace=True)
    return df


f_21 = read_scraped_course_des_files('f_21_des.csv')
w_22 = read_scraped_course_des_files('w_22_des.csv')

# ### Create dataframes for each requirement

# + tags=[]
# Look at all the requirements listed
{x for l in w_22['requirements_distribution'] for x in l}

# + tags=[]
fywr = concat_each_requirement(['FYWR CLST 20.xlsx.csv', 'FYWR CLST 3509.xlsx.csv'])
# -

ulwr = pd.read_csv('Courses with ULWR Course Attributes.xlsx.csv').fillna('')

qr1 = concat_each_requirement(['QR1 CLST 9561 Exclusions.xlsx.csv', 'QR1 CLST 1817.xlsx.csv', 'QR1 CLST 1331.xlsx.csv'])

qr2 = pd.read_csv('QR2 CLST 1329.xlsx.csv')
re = concat_each_requirement(['RE CLST 4621.xlsx.csv', 'RE CLST 4620.xlsx.csv', 'RE CLST 4609.xlsx.csv'])
lr = concat_each_requirement(['Lang Req CLST 0179.xlsx.csv', 'Lang Req CLST 3474.xlsx.csv', 'Lang Req CLST 0176.xlsx.csv', 'Lang Req CLST 8140.xlsx.csv'])
ns = pd.read_csv('NS Courses Offered FA21.xlsx.csv')
ss = pd.read_csv('SS Courses Offered FA21.xlsx.csv')
hu = pd.read_csv('HU Courses Offered FA21.xlsx.csv')
msa = pd.read_csv('MSA Courses Offered FA21.xlsx.csv')
ce = pd.read_csv('CE Courses Offered FA21.xlsx.csv')
interdisciplinary = pd.read_csv('ID Courses Offered FA21.xlsx.csv')


# + tags=[]
def comprehensive_list_merge(requirement, requirement_df, number_required):
    
    w_22_requirement_df = w_22[w_22['requirements_distribution'].map(lambda x: requirement in x)]
    f_21_requirement_df = f_21[f_21['requirements_distribution'].map(lambda x: requirement in x)]
    
    # Merge requirement_df with w_22_requirement_df
    merge1 = w_22_requirement_df.merge(requirement_df[['course', 'title']], on=['course', 'title'], how='outer')
    
    # Concat this df with f_21_requirement_df
    merge2 = pd.concat([merge1, f_21_requirement_df], axis=0, ignore_index=True)
    
    # Drop duplicate courses
    merge2.drop_duplicates(subset='course', inplace=True)
    
    # Keep only necessary columns
    merge2 = merge2[['course', 'title', 'description', 'requirements_distribution', 'credits']]
    
    # Fill na with the requirement
    merge2['requirements_distribution'] = merge2['requirements_distribution'].fillna(requirement)
    
    # Fill na in credits column with medians
    merge2['credits'] = pd.to_numeric(merge2['credits'], errors='coerce')
    merge2['credits'] = merge2['credits'].transform(lambda x: x.fillna(x.median()))
    
    # Add col requirement
    merge2['df_requirement'] = requirement
    
    merge2['Require'] = number_required
    
    return merge2


# -

fywr_df = comprehensive_list_merge('FYWR', fywr, 'One')
ulwr_df = comprehensive_list_merge('ULWR', ulwr, 'One')
qr1_df = comprehensive_list_merge('QR/1', qr1, 'One')
qr2_df = comprehensive_list_merge('QR/2', qr2, 'Two')
re_df = comprehensive_list_merge('RE', re, 'One')
lr_df = comprehensive_list_merge('Lang Req', lr, 'Four')
ns_df = comprehensive_list_merge('NS', ns, 'Two')
ss_df = comprehensive_list_merge('SS', ss, 'Two')
hu_df = comprehensive_list_merge('HU', hu, 'Two')
msa_df = comprehensive_list_merge('MSA', msa, 'One')
ce_df = comprehensive_list_merge('CE', ce, 'One')
id_df = comprehensive_list_merge('ID', interdisciplinary, 'One')


def concat_dfs_add_col(requirement):
    df = pd.concat([w_22[w_22['requirements_distribution'].map(lambda x: requirement in x)], f_21[f_21['requirements_distribution'].map(lambda x: requirement in x)]], axis=0, ignore_index=True)
    df['df_requirement'] = requirement
    df = df[['course', 'title', 'description', 'requirements_distribution', 'credits', 'df_requirement']]
    return df


bs_df = concat_dfs_add_col('BS')
cbl_df = concat_dfs_add_col('CBL')
ex_df = concat_dfs_add_col('Experiential')
honors_df = concat_dfs_add_col('Honors')
ind_df = concat_dfs_add_col('Independent')
mini_df = concat_dfs_add_col('Minicourse')
sus_df = concat_dfs_add_col('Sustain')


# ### Generate LSA Degree Requirements

# +
def gen_lsa_requirement(quant):
    
    rng = np.random.default_rng()
    
    one_list = [fywr_df, ulwr_df, re_df]

    courses_taken = []
    require = []
    title = []
    credit = []

    # FYWR, ULWR, RE requirements, 1 course each
    for req_df in one_list:

        req_course = rng.choice(req_df['course'], size=1, replace=False)[0]
        req = req_df[req_df['course'] == req_course]['df_requirement'].values[0]
        course_title = req_df[req_df['course'] == req_course]['title'].values[0]
        req_credit = req_df[req_df['course'] == req_course]['credits'].values[0]

        courses_taken.append(req_course)
        require.append(req)
        title.append(course_title)
        credit.append(req_credit)
    
    # QR requirement, 1 of QR/1 or 2 of QR/2
    if quant == 'QR/1':

        qr1_course = rng.choice(qr1_df['course'], size=1, replace=False)[0]
        qr1 = 'QR/1'
        qr1_title = qr1_df[qr1_df['course'] == qr1_course]['title'].values[0]
        qr1_credit = qr1_df[qr1_df['course'] == qr1_course]['credits'].values[0]

        courses_taken.append(qr1_course)
        require.append(qr1)
        title.append(qr1_title)
        credit.append(qr1_credit)

    if quant == 'QR/2':

        qr2_course = rng.choice(qr2_df['course'], size=2, replace=False)

        qr21_course = qr2_course[0]
        qr21 = 'QR/2'
        qr21_title = qr2_df[qr2_df['course'] == qr21_course]['title'].values[0]
        qr21_credit = qr2_df[qr2_df['course'] == qr21_course]['credits'].values[0]

        qr22_course = qr2_course[1]
        qr22 = 'QR/2'
        qr22_title = qr2_df[qr2_df['course'] == qr22_course]['title'].values[0]
        qr22_credit = qr2_df[qr2_df['course'] == qr22_course]['credits'].values[0]

        courses_taken.extend([qr21_course, qr22_course])
        require.extend([qr21, qr22])
        title.extend([qr21_title, qr22_title])
        credit.extend([qr21_credit, qr22_credit])
        
    # Language requirement, fourth-term language course (or credits for four courses)
    
    lr_courses = rng.choice(lr_df['course'], size=4, replace=False)
    lr = np.repeat('LR', 4)
    
    lr_titles = []
    for lr_course in lr_courses:
        lr_title = lr_df[lr_df['course'] == lr_course]['title'].values[0]
        lr_titles.append(lr_title)
    
    lr_credits = []
    for lr_course in lr_courses:
        lr_credit = lr_df[lr_df['course'] == lr_course]['credits'].values[0]
        lr_credits.append(lr_credit)
        
    courses_taken.extend(lr_courses)
    require.extend(lr)
    title.extend(lr_titles)
    credit.extend(lr_credits)
        
    # Area Distribution requirement
    # 7 credits in each of NS, SS, and HU for a total of 21 credits or more
    
    # NS
    total_ns_credits = 0
    ns_courses = []
    ns_reqs = []
    ns_course_credits = []
    ns_course_titles = []

    while total_ns_credits < 7:
        ns_course = rng.choice(ns_df['course'], size=1, replace=False)[0]
        ns_req = ns_df[ns_df['course'] == ns_course]['df_requirement'].values[0]
        ns_course_credit = ns_df[ns_df['course'] == ns_course]['credits'].values[0]
        ns_course_title = ns_df[ns_df['course'] == ns_course]['title'].values[0]
        total_ns_credits+=ns_course_credit
        
        ns_courses.append(ns_course)
        ns_reqs.append(ns_req)
        ns_course_credits.append(ns_course_credit)
        ns_course_titles.append(ns_course_title)
        
    # SS
    total_ss_credits = 0
    ss_courses = []
    ss_reqs = []
    ss_course_credits = []
    ss_course_titles = []

    while total_ss_credits < 7:
        ss_course = rng.choice(ss_df['course'], size=1, replace=False)[0]
        ss_req = ss_df[ss_df['course'] == ss_course]['df_requirement'].values[0]
        ss_course_credit = ss_df[ss_df['course'] == ss_course]['credits'].values[0]
        ss_course_title = ss_df[ss_df['course'] == ss_course]['title'].values[0]
        total_ss_credits+=ss_course_credit
        
        ss_courses.append(ss_course)
        ss_reqs.append(ss_req)
        ss_course_credits.append(ss_course_credit)
        ss_course_titles.append(ss_course_title)
        
    # HU
    total_hu_credits = 0
    hu_courses = []
    hu_reqs = []
    hu_course_credits = []
    hu_course_titles = []

    while total_hu_credits < 7:
        hu_course = rng.choice(hu_df['course'], size=1, replace=False)[0]
        hu_req = hu_df[hu_df['course'] == hu_course]['df_requirement'].values[0]
        hu_course_credit = hu_df[hu_df['course'] == hu_course]['credits'].values[0]
        hu_course_title = hu_df[hu_df['course'] == hu_course]['title'].values[0]
        total_hu_credits+=hu_course_credit
        
        hu_courses.append(hu_course)
        hu_reqs.append(hu_req)
        hu_course_credits.append(hu_course_credit)
        hu_course_titles.append(hu_course_title)
        
    # 3 additional credits in three of five areas NS, SS, HU, MSA, and CE for total of 9 credits or more
    
    three_additional_credits = [ns_df, ss_df, hu_df, msa_df, ce_df]
    
    three_areas = rng.choice(three_additional_credits, size=3, replace=False)
    
    area_1_df = three_areas[0]
    area_1_course = rng.choice(area_1_df['course'], size=1, replace=False)[0]
    area_1_req = area_1_df[area_1_df['course'] == area_1_course]['df_requirement'].values[0]
    area_1_credit = area_1_df[area_1_df['course'] == area_1_course]['credits'].values[0]
    area_1_title = area_1_df[area_1_df['course'] == area_1_course]['title'].values[0]

    area_2_df = three_areas[1]
    area_2_course = rng.choice(area_2_df['course'], size=1, replace=False)[0]
    area_2_req = area_2_df[area_2_df['course'] == area_2_course]['df_requirement'].values[0]
    area_2_credit = area_2_df[area_2_df['course'] == area_2_course]['credits'].values[0]
    area_2_title = area_2_df[area_2_df['course'] == area_2_course]['title'].values[0]

    area_3_df = three_areas[2]
    area_3_course = rng.choice(area_3_df['course'], size=1, replace=False)[0]
    area_3_req = area_3_df[area_3_df['course'] == area_3_course]['df_requirement'].values[0]
    area_3_credit = area_3_df[area_3_df['course'] == area_3_course]['credits'].values[0]
    area_3_title = area_3_df[area_3_df['course'] == area_3_course]['title'].values[0]

    # Extend all these courses to the list
    courses_taken.extend([area_1_course, area_2_course, area_3_course])
    courses_taken = courses_taken + ns_courses + ss_courses + hu_courses
    
    require.extend([area_1_req, area_2_req, area_3_req])
    require = require + ns_reqs + ss_reqs + hu_reqs
    
    title.extend([area_1_title, area_2_title, area_3_title])
    title = title + ns_course_titles + ss_course_titles + hu_course_titles
    
    credit.extend([area_1_credit, area_2_credit, area_3_credit])
    credit = credit + ns_course_credits + ss_course_credits + hu_course_credits 

    # Create a df
    require_df = pd.DataFrame({'Subject/Catalog': courses_taken, 'Course List Description': require, 
                                   'Credits': credit, 'Course Title': title})
    
    require_df['Rating'] = np.random.uniform(low=2, high=5, size=(len(require_df)))
    
#     total_credits = sum(credit)

    return require_df


# -

def gen_student(number_student, quant):
    
    gen_student_df_dict = {}

    for i in range(number_student):
        gen_df = gen_lsa_requirement(quant)
        gen_student_df_dict[i] = gen_df
        
    return gen_student_df_dict


qr1_students = gen_student(2500, 'QR/1')

qr2_students = gen_student(2500, 'QR/2')

pickle.dump(qr1_students, open("qr1_students.pickle", "wb"))

pickle.dump(qr2_students, open("qr2_students.pickle", "wb"))



# ### Generate profile clusters

f_21_merged = pd.read_csv('f_21_merge.csv')
w_22_merged = pd.read_csv('w_22_merge.csv')

# ### Create a comprehensive course list and their requirements distribution (with repeated courses if meet more than one requirement)

com_course_list = pd.concat([fywr_df, ulwr_df, qr1_df, qr2_df, re_df, lr_df, ns_df, ss_df, hu_df, msa_df, ce_df, id_df, bs_df,
    cbl_df, ex_df, honors_df, ind_df, mini_df, sus_df])

com_course_list.drop(columns=['description', 'requirements_distribution'], inplace=True)

com_course_list.columns = ['Subject/Catalog', 'Course Title', 'Credits', 'Course List Description', 'Require']

to_replace = com_course_list['Course List Description'].unique().tolist()

to_replace

replace_with = ['First-Year Writing Requirement', 'Upper-level Writing Requirement', 'Full Quantitative Reasoning Credit (QR/1)', 'Half Quantitative Reasoning Credit (QR/2)',
'Race and Ethnicity', 'Language Requirement', 'Natural Science', 'Social Science', 'Humanities', 'Mathematical and Symbolic Analysis',
'Creative Expression', 'Interdisciplinary', 'Bachelor of Science Eligibility', 'Community Based Learning', 'Experiential', 'Honors', 'Independent',
'Minicourse', 'Sustainability']

com_course_list['Course List Description'].replace(to_replace, replace_with, inplace=True)

com_course_list.to_csv('com_course_list_w_requirements.csv', index=False)



StarcoderdataJupyterScriptsDedupFiltered