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()
