In [9]:
import pandas as pd
import geopandas as gpd
import numpy as np
import sqlite3
from sqlalchemy import create_engine
In [10]:
engine = create_engine('postgresql+psycopg2://USER:PASS@localhost:5432/caled')
num_cols = ['county_code',
'district_code',
'school_code',
'year',
'mean_scale_score',
'percentage_met_above',
'percentage_nearly_met']
shape_filename_dict = {
'2017-18': 'schooldistrict_sy1718_tl18.shp',
'2018-19': 'schooldistrict_sy1819_tl19.shp',
'2020-21': 'schooldistrict_sy2021_tl21.shp',
'2021-22': 'EDGE_SCHOOLDISTRICT_TL22_SY2122.shp',
'2022-23': 'EDGE_SCHOOLDISTRICT_TL_23_SY2223.shp',
'2023-24': 'EDGE_SCHOOLDISTRICT_TL24_SY2324.shp',
}
# Enter the schoolyears we want to move to the postgresql
schoolyears = ['2018-19', '2020-21', '2021-22', '2022-23', '2023-24']
In [11]:
def write_test_scores(info_dict, schoolyear):
year = int(schoolyear.split('-')[0])
print(schoolyear)
# Shape data from California (using County, District, and School Code) was only available for 2023-24 and 2024-25.
# So we are grabbing shape data from the national level, and we have to make it agree with the state test score
# data by using a crosswalk that tells us which national code matches up with which CA district code.
# CDSCode in the crosswalk is a single number representing County, District, and School Code.
# NCESDist in the crosswalk is the GEOID in the shape file.
# Essentially, we're getting the district name from one standard because they don't match well between the two files
# We'll use this information below but only need to read it in once:
# First, we'll read data from the crosswalk:
crosswalk = pd.read_csv('Data/pubschls.csv')
# While we're here, we're going to deal with the associated shape data so that they agree, rather than doing it in a
# separate place. We'll need to read the data, manipulate it a bit, and then rewrite new files that will get layermapped
# into geodjango
shape = gpd.read_file(f'Data/{schoolyear}/DistrictAreas/{shape_filename_dict[schoolyear]}')
# Filter by California's FIPS code, 06
shape_CA = shape[shape['STATEFP'] == '06']
# The Crosswalk data's NCESDist field needs a leading zero to match the shape data later
crosswalk['NCESDist'] = '0' + crosswalk['NCESDist']
# Change CDSCode to string to match the one we'll create later in score_data_all_students and to treat as string here
crosswalk['CDSCode'] = crosswalk['CDSCode'].astype(str)
# Filter so we only have district data (where the school code portion of CDSCode = 0000000 (7 zeroes)
crosswalk = crosswalk[crosswalk['CDSCode'].str.endswith('0000000')]
# trim our shape data to just what we need:
# GEOID, lowest and highest grade, and the geometry
crosswalk_shape_cols = crosswalk[['NCESDist', 'County', 'District']]
shape_CA_trim = shape_CA[['GEOID', 'LOGRADE', 'HIGRADE', 'geometry']]
# We need to now merge our crosswalk County and District names into the shape geodataframe
shape_CA_trim = shape_CA_trim.merge(
crosswalk_shape_cols,
left_on='GEOID',
right_on='NCESDist',
how='left'
).drop(columns=['NCESDist'])
# Add a year field to the shape data
shape_CA_trim['year'] = year
# rename tables to match our db naming convention
shape_CA_trim = shape_CA_trim.rename(columns={'GEOID': 'geoid',
'County': 'county',
'District': 'district',
'LOGRADE': 'grade_low',
'HIGRADE': 'grade_high'})
# Write this new info to a new shape file
# shape_CA_trim.to_file(f'Data/{schoolyear}/DistrictAreas/CADistrictAreas{schoolyear}.shp')
for table_name, file_name in info_dict.items():
# We'll loop through each entry in the write dict, which is a PostgreSQL table name as key and a csv filename (without .csv)
# as the value
# The fields change slightly after the 2022-23 school year so we'll change the column_dict if year is
# greater than 2022
column_dict = {'County Code': 'county_code',
'District Code': 'district_code',
'School Code': 'school_code',
'cds_code': 'cds_code',
'County': 'county',
'District': 'district',
'year': 'year',
'student_group_name': 'student_group_name',
'Grade': 'grade',
'test_name': 'test_name',
'Mean Scale Score': 'mean_scale_score',
'Percentage Standard Met and Above': 'percentage_met_above',
'Percentage Standard Nearly Met': 'percentage_nearly_met'}
# We'll need this below because of the difference between 2018 and earlier and what came after that
student_group_field = 'Student Group ID'
# Years after 2019 use ^ separator, years 2018 and earlier use , separator
# fields changed as well, so we'll need to amend the column_dict
sep = '^'
if year <= 2018:
sep = ','
# Read the CSV, which is separated by ^ instead of commas and is encoded in latin1
score_data = pd.read_csv(f'Data/{schoolyear}/{file_name}.csv', sep=sep, encoding='latin1')
# Read the Test names CSV
tests = pd.read_csv(f'Data/{schoolyear}/Tests.csv', sep='^', encoding='latin1')
# make a dictionary where key is Test ID and value is Test Name
tests_dict = {x: y for x, y in zip(tests['Test ID'], tests['Test Name'])}
# del column_dict['Student Group ID']
# 2018 and earlier, the ela math scores had Test Id instead of Test ID and Subgroup ID instead of Student Group ID
# Create a new column called test_name that is the name of test according to the Test ID
if year > 2018:
score_data['test_name'] = score_data['Test ID'].replace(tests_dict)
else:
#
if table_name == 'CalEd_sciencetestscore':
score_data['test_name'] = score_data['Test ID'].replace(tests_dict)
student_group_field = 'Demographic ID'
else:
score_data['test_name'] = score_data['Test Id'].replace(tests_dict)
student_group_field = 'Subgroup ID'
# Read in student groups data for the schoolyear
student_groups = pd.read_csv(f'Data/{schoolyear}/StudentGroups.csv', sep='^', encoding='latin1')
# Make a dictionary that is {Demographic ID: Demographic Name}
groups_dict = {x: y for x, y in zip(student_groups['Demographic ID'], student_groups['Demographic Name'])}
# Use the dictionary to replace the ID values in the dataframe with the name values
# There are a bunch of student groups. If they were boolean columns, we might be able to use them, but they only
# take up one column. Picking and choosing demographics doesn't help us with interplay, so we'll simplify
# and just look at a few: All Students, Reported Disabilities, No reported disabilities, socioeconomically disadvantaged,
# and not socioeconomically disadvantaged
score_data['student_group_name'] = score_data[student_group_field].replace(groups_dict)
# # Very few schools have the word District in them, so we'll remove it
# score_data['District Name'] = score_data['District Name'].str.replace(' District', '')
score_data_all_students = score_data[score_data[student_group_field].isin([1, 128, 99, 31, 111])].copy()
# Make a new column for school year
score_data_all_students.loc[:, 'year'] = year
# Filter so we're only dealing with districts
score_data_all_students = score_data_all_students[
(score_data_all_students['County Code'] != 0) &
(score_data_all_students['District Code'] != 0) &
(score_data_all_students['School Code'] == 0)]
# CDSCode is the three codes mashed and we districts end in 0000000, so we are going to create that field in the test data
score_data_all_students['cds_code'] = score_data_all_students['County Code'].astype(str) + \
score_data_all_students['District Code'].astype(str) + '0000000'
# make county and district name the names from the crosswalk
# make a subset
crosswalk_score_cols = crosswalk[['CDSCode', 'County', 'District']]
# Merge the County and District Name in using CDSCode
score_data_all_students = score_data_all_students.merge(
crosswalk_score_cols,
left_on='cds_code',
right_on='CDSCode',
how='left'
)
# Now we make our score data the subset of the fields we want (which is the keys of our column_dict) and rename
# them according to our column_dict
score_data_trim = score_data_all_students[column_dict.keys()].rename(columns=column_dict)
# We need to force the numeric columns to be numeric to get rid of some *s
for col in num_cols:
score_data_trim[col] = pd.to_numeric(score_data_trim[col], errors='coerce')
# Lastly, we write the resulting dataframe to the PostgreSQL table
# If the table exists, we append. This way, we can add multiple years to the database
print(f'Writing Data to {table_name}')
score_data_trim.to_sql(table_name, con=engine, if_exists='append', index=False)
print('Done')
In [12]:
# Loop through schoolyears
for schoolyear in schoolyears:
# Enter the django db names from the postgresql server as keys and the associated data filenames as values.
write_dict = {
'CalEd_elamathtestscore': f'ela_math_{schoolyear}',
'CalEd_sciencetestscore': f'science_{schoolyear}'
}
# Run through the data cleaning pipeline function and write to PostgreSQL server db
write_test_scores(write_dict, schoolyear=schoolyear)
2018-19
/tmp/ipykernel_1050501/1732645436.py:12: DtypeWarning: Columns (54,58,59,60,61) have mixed types. Specify dtype option on import or set low_memory=False.
crosswalk = pd.read_csv('Data/pubschls.csv')
Writing Data to CalEd_elamathtestscore Writing Data to CalEd_sciencetestscore Done 2020-21
/tmp/ipykernel_1050501/1732645436.py:12: DtypeWarning: Columns (54,58,59,60,61) have mixed types. Specify dtype option on import or set low_memory=False.
crosswalk = pd.read_csv('Data/pubschls.csv')
/tmp/ipykernel_1050501/1732645436.py:77: DtypeWarning: Columns (7,8,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) have mixed types. Specify dtype option on import or set low_memory=False.
score_data = pd.read_csv(f'Data/{schoolyear}/{file_name}.csv', sep=sep, encoding='latin1')
Writing Data to CalEd_elamathtestscore Writing Data to CalEd_sciencetestscore Done 2021-22
/tmp/ipykernel_1050501/1732645436.py:12: DtypeWarning: Columns (54,58,59,60,61) have mixed types. Specify dtype option on import or set low_memory=False.
crosswalk = pd.read_csv('Data/pubschls.csv')
Writing Data to CalEd_elamathtestscore Writing Data to CalEd_sciencetestscore Done 2022-23
/tmp/ipykernel_1050501/1732645436.py:12: DtypeWarning: Columns (54,58,59,60,61) have mixed types. Specify dtype option on import or set low_memory=False.
crosswalk = pd.read_csv('Data/pubschls.csv')
Writing Data to CalEd_elamathtestscore Writing Data to CalEd_sciencetestscore Done 2023-24
/tmp/ipykernel_1050501/1732645436.py:12: DtypeWarning: Columns (54,58,59,60,61) have mixed types. Specify dtype option on import or set low_memory=False.
crosswalk = pd.read_csv('Data/pubschls.csv')
Writing Data to CalEd_elamathtestscore Writing Data to CalEd_sciencetestscore Done
In [ ]: