def create_cohort_sheet(workbook, tab_name, students, per_stats, hs, by_hs): '''Actually creates the tab with overview stats for a cohort (one row per check date)''' headline = 'Cohort summary report for ' if by_hs: headline += 'All High Schools ' if hs == 'All' else (hs + ' ') headline += 'Class of ' + students[0][9] # HS Class note = '% of class with given status on specified date' note += ' (students pursuing higher degrees show as "enrolled")' note2 = 'Shaded columns add to 100%. First two shaded columns ' note2 += 'add to "in college or graduated" on Overview' outtable = [['', '', headline], ['', '', note], ['', '', note2], [ 'Date', '#', 'Grad 4yr', 'Grad 2yr', 'Grad 2 or 4yr', 'Enrolled 2 or 4yr', 'Enrolled 4yr', 'Enrolled 2yr', 'Enrolled Trade', 'Enrolled Grad Degree', 'Grad Grad Degree', 'Grad Trade/Vocational', 'No College' ]] for i in range(per_stats[students[0][0]].semesters): date_snapshot = get_cohort_slice(students, per_stats, i) outtable.append(date_snapshot[2:]) # New send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color'] = '#D9D9D9' ws = tt.table_to_exsheet(workbook, tab_name, outtable, bold=True, header_row=3, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=((4, 5, 8, 11, 12), data_f)) ws.freeze_panes(4, 2) ws.set_column('A:A', 12) ws.set_column('E:F', 15)
def basic_summary_sheet(wb,table,by_hs=False, ba_only=False): '''Creates and formats the basic summary sheet in the workbook''' ff = {'num_format': '0.0%', 'align': 'center'} r1f = {'bold': True, 'font_size': 16} r2f = {'italic': True, 'font_size': 14} if ba_only: sheet_name = 'Four yr Overview' else: sheet_name = 'Overview' ws = tt.table_to_exsheet(wb, sheet_name, table, bold=True, header_row=2, float_format=ff, space=False, first_rows_format=r1f, second_row_format=r2f) if by_hs: ws.freeze_panes(3,3) ws.set_column('B:B',15) else: ws.freeze_panes(3,2)
def create_cohort_sheet(workbook,tab_name,students,per_stats,hs,by_hs): '''Actually creates the tab with overview stats for a cohort (one row per check date)''' headline = 'Cohort summary report for ' if by_hs: headline += 'All High Schools ' if hs == 'All' else (hs + ' ') headline += 'Class of ' + students[0][9] # HS Class note = '% of class with given status on specified date' note += ' (students pursuing higher degrees show as "enrolled")' note2 = 'Shaded columns add to 100%. First two shaded columns ' note2 += 'add to "in college or graduated" on Overview' outtable = [['','',headline], ['','',note], ['','',note2], ['Date', '#', 'Grad 4yr', 'Grad 2yr', 'Grad 2 or 4yr', 'Enrolled 2 or 4yr', 'Enrolled 4yr', 'Enrolled 2yr', 'Enrolled Trade', 'Enrolled Grad Degree', 'Grad Grad Degree', 'Grad Trade/Vocational','No College']] for i in range(per_stats[students[0][0]].semesters): date_snapshot = get_cohort_slice(students, per_stats, i) outtable.append(date_snapshot[2:]) # New send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color']='#D9D9D9' ws = tt.table_to_exsheet(workbook, tab_name, outtable, bold=True, header_row=3, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=((4,5,8,11,12),data_f)) ws.freeze_panes(4,2) ws.set_column('A:A',12) ws.set_column('E:F',15)
def basic_summary_sheet(wb, table, by_hs=False, ba_only=False): '''Creates and formats the basic summary sheet in the workbook''' ff = {'num_format': '0.0%', 'align': 'center'} r1f = {'bold': True, 'font_size': 16} r2f = {'italic': True, 'font_size': 14} if ba_only: sheet_name = 'Four yr Overview' else: sheet_name = 'Overview' ws = tt.table_to_exsheet(wb, sheet_name, table, bold=True, header_row=2, float_format=ff, space=False, first_rows_format=r1f, second_row_format=r2f) if by_hs: ws.freeze_panes(3, 3) ws.set_column('B:B', 15) else: ws.freeze_panes(3, 2)
student.append(enrolled_at) if enrolled_at == student[dC[c.Currently_Enrolled_At__c]]: student.append('NO CHANGE') else: student.append('CHANGE') sf.Contact.update(student[dC[c.Id]], {c.Currently_Enrolled_At__c: enrolled_at}) dC['EnrollmentCount'] = max(dC.values()) + 1 dC['NewEnrolledAt'] = max(dC.values()) + 1 dC['EnrollChangeStatus'] = max(dC.values()) + 1 #Reconstitute headers tt.add_header(contacts, dC) tt.add_header(accounts, dA) tt.add_header(enrollments, dE) #Now write everything to the file import xlsxwriter workbook = xlsxwriter.Workbook('FixCurrentlyEnrolledAtReport.xlsx') ws = tt.table_to_exsheet(workbook, 'Contacts', contacts, bold=True, space=True) ws.freeze_panes(1, 3) ws = tt.table_to_exsheet(workbook, 'Accounts', accounts, bold=True, space=True) ws.freeze_panes(1, 2) ws = tt.table_to_exsheet(workbook, 'Enrollments', enrollments, bold=True, space=True) ws.freeze_panes(1, 3) workbook.close()
def create_cohort_grads(workbook, con, per_stats, by_hs=False): '''Creates a single tab with summary data at 3, 4, 5, 6 yrs, and "6+"''' years = list(set(con.get_column('HS Class'))) years.sort() if date(int(min(years))+3,8,31) > date.today(): return #too early headline = 'Graduation summary report at 3, 4, 5, 6 and 6+ years' note = '% of class with given status a number of years after high school' out_table = [['','','',headline], ['','','',note], ['HS Class','Years out', '#', 'Grad 4yr', 'Grad 2yr', 'Grad Grad Degree', '4yr or Grad Degree', 'Grad Trade/Vocational','No Degree']] if by_hs: out_table[0].insert(0,'') out_table[1].insert(0,'') out_table[2].insert(0,'High School') for sem in [8, 11, 14, 17, 18]: #indices for Aug 31 3,4,5,6 years, then + for year in years: # Get yr data & check if this year has results that are old enough yr_students = list(con.get_match_rows('HS Class', year)) num_semesters = per_stats[yr_students[0][0]].semesters if num_semesters <= sem: break # no more years apply if by_hs: high_schools = list(set([student[12] for student in yr_students])) high_schools.sort() else: high_schools = [] if len(high_schools) != 1: high_schools.append('All') if sem == 18: #6+ (greatest semester out) sem_check = num_semesters - 1 else: #3-6 years out sem_check = sem for hs in high_schools: if hs == 'All': students = yr_students else: students = [s for s in yr_students if s[12] == hs] # call the slice function snapshot = get_cohort_slice(students, per_stats, sem_check, grad_only=True) # take result and add to the output table if by_hs: next_row = [hs, int(year)] else: next_row = [int(year)] sem_cases = { 8:'3 years', 11:'4 years', 14:'5 years', 17:'6 years', 18:'6+ years'} next_row.append(sem_cases[sem]) next_row.extend(snapshot[3:6]) next_row.append(snapshot[12]) next_row.append(snapshot[4]+snapshot[12]) next_row.extend(snapshot[13:]) out_table.append(next_row) # New send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color']='#D9D9D9' data_col = 7 if by_hs else 6 ws = tt.table_to_exsheet(workbook, 'Graduation Summary', out_table, bold=True, header_row=2, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=((data_col,),data_f)) if by_hs: ws.freeze_panes(3,4) ws.set_column('H:H',12) else: ws.freeze_panes(3,3) ws.set_column('G:G',12)
def create_cohort_detail_sheet(workbook,tab_name,students,per_stats,hs,by_hs, verbose=False): '''Creates a tab with the detailed semester by semester stats for an entire cohort (all should have the same number of semesters)''' headline = 'Cohort detail report for ' if by_hs: headline += 'All High Schools ' if hs == 'All' else (hs + ' ') headline += 'Class of ' + students[0][9] # HS Class note = 'Current status and semester by semester details of enrollment' note2 = 'Click on the "+" signs at the top to expand a semester' outtable = [['']*11+[headline], ['']*11+[note], ['']*11+[note2]] data_header=['Id', 'Race/ Ethnicity', 'First Gen', 'Low Income', 'Gender', 'Highest ACT', 'HS GPA', 'HS Class', 'Network ID', 'SpEd', 'High School', 'Last Name', 'First Name', 'Current Status', 'Initial PGR', 'Initial IRR', 'Initial College (10/15/'+str(students[0][9])[-2:]+')', 'Type'] if verbose: data_header.insert(16,'Initial NCES') data_header.insert(17,'Persist semesters') data_header.insert(18,'BA semester') # sort the student table by HS, Class, Last, First students_s = sorted(students, key = lambda x: x[12]+str(x[9])+ x[1] +x[0]) # get all the check date semesters that are not in august sems = [i for i in range(per_stats[students_s[0][0]].semesters) if per_stats[students_s[0][0]].check_dates[i].month != 8] extra_data_header = [] if len(sems) > 1: sem_num = 1 for i in sems[1:]: sem_num += 1 new_date = per_stats[ students_s[0][0]].check_dates[i].strftime('%m/%d/%y') extra_data_header.extend([ 'PGR Chg', 'Sem '+str(sem_num)+' '+ 'College (' + new_date + ')', 'Type', 'PGR']) data_header.extend(extra_data_header) outtable.append(data_header) for student in students_s: data_row = [student[0]] + student[3:] + student[1:3] #deference the performance class for this student per = per_stats[student[0]] data_row.append(get_short_status(per.status_semester[-1], per.grad_status_semester[-1])) cur_grad_rate = per.grad_rate_semester[0] cur_grad_rate = cur_grad_rate if cur_grad_rate else 0.0 first_data = [cur_grad_rate, per.ret_rate_semester[0], per.col_name_semester[0], per.col_type_semester[0]] if verbose: first_data.insert(2,per.col_nces_semester[0]) first_data.insert(3,per.persist_through_sem) first_data.insert(4,per.earned_BA_in_sem) data_row.extend(first_data) if len(sems) > 1: for i in sems[1:]: new_grad_rate = per.grad_rate_semester[i] new_grad_rate = new_grad_rate if new_grad_rate else 0.0 gr_change = new_grad_rate - cur_grad_rate cur_grad_rate = new_grad_rate data_row.extend([gr_change, per.col_name_semester[i], per.col_type_semester[i], per.grad_rate_semester[i]]) outtable.append(data_row) # Now send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color']='#D9D9D9' data_f_cols=(1,2,3,4,5,6,7,8,9,10) ws = tt.table_to_exsheet(workbook, tab_name, outtable, bold=True, header_row=3, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=(data_f_cols,data_f)) ws.freeze_panes(4,14) ws.set_column('A:K',8, None, {'level': 1, 'hidden': True}) ws.set_column('L:L',15, None, {'collapsed': True}) ws.set_column('M:N',15) ws.set_column('O:P',7) if verbose: ws.set_column('S:S',9) ws.set_column('T:T',30) ws.set_column('U:U',6) bi = 21 else: ws.set_column('Q:Q',30) ws.set_column('R:R',6) bi = 18 if len(sems) > 1: for i in range(len(sems)-1): if i: #not the first one ws.set_column(bi+i*4,bi+i*4,6,None,{'collapsed':True}) else: ws.set_column(bi+i*4,bi+i*4,6) ws.set_column(bi+1+i*4,bi+1+i*4,30) ws.set_column(bi+2+i*4,bi+2+i*4,6,None,{'level': 1, 'hidden': True}) ws.set_column(bi+3+i*4,bi+3+i*4,7,None,{'level': 1, 'hidden': True}) ws.set_column(bi+4+i*4,bi+4+i*4,3, None, {'collapsed':True})
def create_cohort_grads(workbook, con, per_stats, by_hs=False): '''Creates a single tab with summary data at 3, 4, 5, 6 yrs, and "6+"''' years = list(set(con.get_column('HS Class'))) years.sort() if date(int(min(years))+3,8,31) > date.today(): return #too early headline = 'Graduation summary report at 3, 4, 5, 6 and 6+ years' note = '% of class with given status a number of years after high school' out_table = [['','','',headline], ['','','',note], ['HS Class','Years out', '#', 'Grad 4yr', 'Grad 2yr', 'Grad Grad Degree', '4yr or Grad Degree', 'Grad Trade/Vocational','No Degree']] if by_hs: out_table[0].insert(0,'') out_table[1].insert(0,'') out_table[2].insert(0,'High School') for sem in [8, 11, 14, 17, 18]: #indices for Aug 31 3,4,5,6 years, then + for year in years: # Get yr data & check if this year has results that are old enough yr_students = list(con.get_match_rows('HS Class', year)) num_semesters = per_stats[yr_students[0][0]].semesters if num_semesters <= sem: break # no more years apply if by_hs: high_schools = list(set([student[12] for student in yr_students])) high_schools.sort() else: high_schools = [] if len(high_schools) != 1: high_schools.append('All') if sem == 18: #6+ (greatest semester out) sem_check = num_semesters - 1 else: #3-6 years out sem_check = sem for hs in high_schools: if hs == 'All': students = yr_students else: students = [s for s in yr_students if s[12] == hs] # call the slice function snapshot = get_cohort_slice(students, per_stats, sem_check, grad_only=True) # take result and add to the output table if by_hs: next_row = [hs, int(year)] else: next_row = [int(year)] sem_cases = { 8:'3 years', 11:'4 years', 14:'5 years', 17:'6 years', 18:'6+ years'} next_row.append(sem_cases[sem]) next_row.extend(snapshot[3:6]) next_row.append(snapshot[12]) next_row.append(snapshot[4]+snapshot[12]) next_row.extend(snapshot[13:]) out_table.append(next_row) # New send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color']='#D9D9D9' data_col = 7 if by_hs else 6 ws = tt.table_to_exsheet(workbook, 'Graduation Summary', out_table, bold=True, header_row=2, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=((data_col,),data_f)) if by_hs: ws.freeze_panes(3,4) ws.set_column('H:H',12) else: ws.freeze_panes(3,3) ws.set_column('G:G',12)
def create_cohort_detail_sheet(workbook,tab_name,students,per_stats,hs,by_hs, verbose=False): '''Creates a tab with the detailed semester by semester stats for an entire cohort (all should have the same number of semesters)''' headline = 'Cohort detail report for ' if by_hs: headline += 'All High Schools ' if hs == 'All' else (hs + ' ') headline += 'Class of ' + students[0][9] # HS Class note = 'Current status and semester by semester details of enrollment' note2 = 'Click on the "+" signs at the top to expand a semester' outtable = [['']*11+[headline], ['']*11+[note], ['']*11+[note2]] data_header=['Id', 'Race/ Ethnicity', 'First Gen', 'Low Income', 'Gender', 'Highest ACT', 'HS GPA', 'HS Class', 'Network ID', 'SpEd', 'High School', 'Last Name', 'First Name', 'Current Status', 'Initial PGR', 'Initial IRR', 'Initial College (10/15/'+str(students[0][9])[-2:]+')', 'Type'] if verbose: data_header.insert(16,'Initial NCES') data_header.insert(17,'Persist semesters') data_header.insert(18,'BA semester') # sort the student table by HS, Class, Last, First students_s = sorted(students, key = lambda x: x[12]+str(x[9])+ x[1] +x[0]) # get all the check date semesters that are not in august sems = [i for i in range(per_stats[students_s[0][0]].semesters) if per_stats[students_s[0][0]].check_dates[i].month != 8] extra_data_header = [] if len(sems) > 1: sem_num = 1 for i in sems[1:]: sem_num += 1 new_date = per_stats[ students_s[0][0]].check_dates[i].strftime('%m/%d/%y') extra_data_header.extend([ 'PGR Chg', 'Sem '+str(sem_num)+' '+ 'College (' + new_date + ')', 'Type', 'PGR']) data_header.extend(extra_data_header) outtable.append(data_header) for student in students_s: data_row = [student[0]] + student[3:] + student[1:3] #deference the performance class for this student per = per_stats[student[0]] data_row.append(get_short_status(per.status_semester[-1], per.grad_status_semester[-1])) cur_grad_rate = per.grad_rate_semester[0] cur_grad_rate = cur_grad_rate if cur_grad_rate else 0.0 first_data = [cur_grad_rate, per.ret_rate_semester[0], per.col_name_semester[0], per.col_type_semester[0]] if verbose: first_data.insert(2,per.col_nces_semester[0]) first_data.insert(3,per.persist_through_sem) first_data.insert(4,per.earned_BA_in_sem) data_row.extend(first_data) if len(sems) > 1: for i in sems[1:]: new_grad_rate = per.grad_rate_semester[i] new_grad_rate = new_grad_rate if new_grad_rate else 0.0 gr_change = new_grad_rate - cur_grad_rate cur_grad_rate = new_grad_rate data_row.extend([gr_change, per.col_name_semester[i], per.col_type_semester[i], per.grad_rate_semester[i]]) outtable.append(data_row) # Now send it all to the sheet ff = {'num_format': '0.0%', 'align': 'center'} df = {'num_format': 'mmm d yyyy'} r1f = {'bold': True, 'font_size': 20} r2f = {'italic': True, 'font_size': 12} data_f = ff.copy() data_f['bg_color']='#D9D9D9' data_f_cols=(1,2,3,4,5,6,7,8,9,10) ws = tt.table_to_exsheet(workbook, tab_name, outtable, bold=True, header_row=3, space=False, float_format=ff, date_format=df, first_rows_format=r1f, second_row_format=r2f, data_format=(data_f_cols,data_f)) ws.freeze_panes(4,14) ws.set_column('A:K',8, None, {'level': 1, 'hidden': True}) ws.set_column('L:L',15, None, {'collapsed': True}) ws.set_column('M:N',15) ws.set_column('O:P',7) if verbose: ws.set_column('S:S',9) ws.set_column('T:T',30) ws.set_column('U:U',6) bi = 21 else: ws.set_column('Q:Q',30) ws.set_column('R:R',6) bi = 18 if len(sems) > 1: for i in range(len(sems)-1): if i: #not the first one ws.set_column(bi+i*4,bi+i*4,6,None,{'collapsed':True}) else: ws.set_column(bi+i*4,bi+i*4,6) ws.set_column(bi+1+i*4,bi+1+i*4,30) ws.set_column(bi+2+i*4,bi+2+i*4,6,None,{'level': 1, 'hidden': True}) ws.set_column(bi+3+i*4,bi+3+i*4,7,None,{'level': 1, 'hidden': True}) ws.set_column(bi+4+i*4,bi+4+i*4,3, None, {'collapsed':True})
else: enrolled_at = school[dA[a.Name]] student.append(len(records)) student.append(enrolled_at) if enrolled_at == student[dC[c.Currently_Enrolled_At__c]]: student.append('NO CHANGE') else: student.append('CHANGE') sf.Contact.update(student[dC[c.Id]], {c.Currently_Enrolled_At__c: enrolled_at}) dC['EnrollmentCount']=max(dC.values())+1 dC['NewEnrolledAt']=max(dC.values())+1 dC['EnrollChangeStatus']=max(dC.values())+1 #Reconstitute headers tt.add_header(contacts, dC) tt.add_header(accounts, dA) tt.add_header(enrollments, dE) #Now write everything to the file import xlsxwriter workbook = xlsxwriter.Workbook('FixCurrentlyEnrolledAtReport.xlsx') ws =tt.table_to_exsheet(workbook, 'Contacts', contacts, bold=True, space=True) ws.freeze_panes(1,3) ws = tt.table_to_exsheet(workbook, 'Accounts', accounts, bold=True, space=True) ws.freeze_panes(1,2) ws = tt.table_to_exsheet(workbook, 'Enrollments', enrollments, bold=True, space=True) ws.freeze_panes(1,3) workbook.close()