import pandas as pd
import numpy as np
from pathlib import Path
import requests
import os
import openpyxlData Collection
Overview
Goals
The primary goal was to investigate the relationship between school performance metrics and socioeconomic factors across D.C. public schools. The aim was to identify key predictors of school performance and uncover patterns that could inform interventions and policy decisions, as well as group schools based on shared characteristics - by combining supervised and unsupervised learning techniques.
Motivation
Before addressing systemic educational inequalities, one must understand how socioeconomic factors influence the community at large. Schools in lower-income wards face unique challenges that impact their academic success - this should not be. These students have the right to receive a publci school education equivalent to that of their more affluent peers. The motivation for this project was an inclination to uncover the relationships in the data. Relationship that will not only aid in making data-driven insights to guide resource allocation and intervention planning, but will start broader discussions on educational equity in D.C.
Objectives
Data Cleaning:
- Standardize and merge datasets to ensure consistency and proper usability for modeling.
- Address missing values, outliers, and data misalignments.
Exploratory Data Analysis (EDA):
- Visualize and analyze the relationships between features.
- Identify trends, disparities, and correlations that will inform the next steps in modeling.
Unsupervised Learning:
- Apply clustering methods to group schools using socioeconomic and performance metrics.
- Use dimensionality reduction techniques (PCA, t-SNE) to visualize relationships and improve interpretability.
Supervised Learning:
- Build predictive models to forecast graduation rates and classify schools based on student success composite scores.
- Evaluate feature importance and uncover the most influential indicators of school performance.
Insights & Implications:
- Highlight actionable insights for educators and policymakers.
- Address disparities in outcomes across D.C. wards and recommend areas for targeted interventions.
Methods
I used a systematic approach to collect, clean, analyze, and model the data to explore the relationships between school-level and ward-level metrics and socioeconomic factors. Below, I will cover each step of the workflow:
Data Collection
I worked with multiple datasets - including attendance metrics, test metrics, discipline reports, graduation rates, as well as socioeconomic indicators such as poverty and household income. I aggregated them from various sources and then loaded them into Python using pandas, which ensured a comprehensive and reliable data collection process.
Data Cleaning
The data cleaning process was conducted with the utmost care, handling missing values, standardizing column names, and deriving variables from other features; this approach ensured the accuracy of the analysis. Some key cleaning steps include:
- Replacing placeholder values with appropriate values or removing the rows
- Aligning datasets by filtering for relevant schools, then merging them based on shared identifiers such as school names or wards
- Ensuring consistency in the categorical variables by encoding and restructuring through binary and multi-class labels for supervised learning modeling
Exploratory Data Analysis (EDA):
I employed advanced techniques to visualize different trends and relationships in the data, and these methods revealed various patterns that informed the modeling process. Key techniques are:
- Correlation heatmaps helped to identify multicollinearity and remove redundant features
- Scatter plots and histograms aided in understanding the distribution and relationships of the different school performance metrics and socioeconomic features - such as graduation rates and attendance scores
Unsupervised Learning:
To explore the patterns in the data, I applied various unsupervised learning techniques:
- Principal Component Analysis (PCA) reduced the dimensionality of the data so that I could make clear visualizations and identify the most significant components
- Clustering Methods (K-Means, DBSCAN, Hierarchical Clustering) were each used to group schools based on key components, revealing groupings by ward and performance trends
- t-SNE visualizations allowed for a detailed look at the clustering results in the reduced-dimensional space
Supervised Learning:
The supervised learning models allowed me to predict outcomes and uncover feature importance:
- Linear Regression was used to predict graduation rates as a continuous variable
- Decision Tree Regressor and Classifier provided insights into feature importance, which allows for binary classification of low-performing schools, determined by graduation rates less than 87%
- Random Forest Classifier provided a robust solution and improved the predictive performance while balancing feature importance
Technical Tools:
All analyses were conducted in Python using various libraries, such as Pandas, Numpy, and Scikit-learn, and I created the plots using Visualization libraries like Matplotlib and Plotly.
This combination of methods allowed me to clean and analyze the data, develop predictive models, and uncover meaningful patterns while maintaining a structured and replicable workflow.
Code
I sourced the socioeconomic data from the Open Data DC website and the student success data from the District of Columbia Public Schools (DCPS) website.
To start, I saved the Excel files from DC Public Schools, which will serve as the student success indicators, by looping through a list of the file paths. Since each file had mutliple sheets, I added another loop to access each sheet. After that, I saved each sheet as a CSV file to my raw data folder.
#create list of all excel file names
excel_files = ['../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx', '../../data/raw-data/2020 Discipline - Metric Scores.xlsx', '../../data/raw-data/2020 Attendance - Metric Scores.xlsx', '../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx', '../../data/raw-data/School_Wards SY1213 SY1314.xlsx']
#loop through each excel file to convert to csv files
for file_path in excel_files:
excel_file = pd.ExcelFile(file_path)
#loop through each sheet in the file
for sheet_name in excel_file.sheet_names:
#read the sheet into the data frame
excel_df = excel_file.parse(sheet_name)
output_folder = Path('../../data/raw-data/')
#create csv file name based on original excel
csv_file_name = output_folder / f"{Path(file_path)}_{sheet_name}.csv"
#save the date frame to a csv file
excel_df.to_csv(csv_file_name, index=False)
print(f"Saved {sheet_name} from {file_path} as {csv_file_name}")Saved Data Notes from ../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_Data Notes.csv
Saved Data Dictionary from ../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_Data Dictionary.csv
Saved School Metric Scores from ../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_School Metric Scores.csv
Saved LEA Metric Scores from ../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_LEA Metric Scores.csv
Saved State Metric Scores from ../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_State Metric Scores.csv
Saved Data Notes from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_Data Notes.csv
Saved Data Dictionary from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_Data Dictionary.csv
Saved School Metric Scores from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_School Metric Scores.csv
Saved LEA Metric Scores from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_LEA Metric Scores.csv
Saved State Metric Scores from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_State Metric Scores.csv
Saved State Scores by Framework from ../../data/raw-data/2020 Discipline - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Discipline - Metric Scores.xlsx_State Scores by Framework.csv
Saved Data Notes from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_Data Notes.csv
Saved Data Dictionary from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_Data Dictionary.csv
Saved School Metric Scores from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_School Metric Scores.csv
Saved School Scores by Framework from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_School Scores by Framework.csv
Saved LEA Metric Scores from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_LEA Metric Scores.csv
Saved LEA Scores by Framework from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_LEA Scores by Framework.csv
Saved State Metric Scores from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_State Metric Scores.csv
Saved State Scores by Framework from ../../data/raw-data/2020 Attendance - Metric Scores.xlsx as ../../data/raw-data/../../data/raw-data/2020 Attendance - Metric Scores.xlsx_State Scores by Framework.csv
Saved 2019-20 Rate Overview from ../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx as ../../data/raw-data/../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx_2019-20 Rate Overview.csv
Saved 2019-20 Student Group Rates from ../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx as ../../data/raw-data/../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx_2019-20 Student Group Rates.csv
Saved 2019-20 Rate by School from ../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx as ../../data/raw-data/../../data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx_2019-20 Rate by School.csv
Saved school wards from ../../data/raw-data/School_Wards SY1213 SY1314.xlsx as ../../data/raw-data/../../data/raw-data/School_Wards SY1213 SY1314.xlsx_school wards.csv
/opt/anaconda3/envs/geo_env/lib/python3.13/site-packages/openpyxl/worksheet/_reader.py:329: UserWarning: Unknown extension is not supported and will be removed
warn(msg)
I used the API from DC’s GIS service for the crime data. I started with the endpoint URL, which pulls the crime data in JSON format. I then used Python’s requests library to make a call to the API.
I added some error handling to catch any issues with the request. If the call was successful, it extracted the data that was relevant to me, which was the features key - containing all the crime records. I then pulled out the attributes for each record.
Lastly, I converted the data into a Pandas DataFrame and saved it as a CSV in my raw data folder.
# dc crime data endpoint
url = 'https://maps2.dcgis.dc.gov/dcgis/rest/services/FEEDS/MPD/MapServer/2/query?where=1%3D1&outFields=*&outSR=4326&f=json'
# API call
try:
response = requests.get(url)
response.raise_for_status() # error check
# pull data from the response
crime_data = response.json()
features = crime_data.get('features', []) # get list of features
if features:
records = [feature['attributes']for feature in features]
crime_data = pd.DataFrame(records)
raw_data_folder = '/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/'
crime_file_path = raw_data_folder + 'crime_df.csv'
crime_data.to_csv(crime_file_path, index=False, header=True)
print(f"Data saved to {crime_file_path}")
except requests.exceptions.RequestException as error:
print(f"error: {error}")Data saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/crime_df.csv
For the demographics data, I saved the file from Open Data DC to my system and imported the data using pandas’ read_csv. I used the raw data folder path I recreated earlier, added the file name for the demographics data as ‘demo_df.csv,’ and saved this path to demo_file_path. Then, I used .to_csv to save the data and included a print message to ensure it went to the correct place with the proper name.
I followed these steps for the next few DataFrames - poverty, housing, economic, and ward.
demographic_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/Demographic Characteristics of DC Wards.csv')
demo_file_path = raw_data_folder + 'demo_df.csv'
# Dataframe to csv to save
demographic_df.to_csv(demo_file_path, index=False, header=True)
# Verify success
print(f"file saved to {demo_file_path}")<bound method NDFrame.head of Name \
0 Ward 1 (2022), District of Columbia
1 Ward 2 (2022), District of Columbia
2 Ward 3 (2022), District of Columbia
3 Ward 4 (2022), District of Columbia
4 Ward 5 (2022), District of Columbia
5 Ward 6 (2022), District of Columbia
6 Ward 7 (2022), District of Columbia
7 Ward 8 (2022), District of Columbia
CITIZEN, VOTING AGE POPULATION: Citizen, 18 and over population \
0 60123
1 64857
2 59710
3 56563
4 67354
5 66999
6 66182
7 61896
CITIZEN, VOTING AGE POPULATION: Citizen, 18 and over population: Female \
0 31109
1 32175
2 32387
3 30369
4 35171
5 35126
6 35784
7 35204
CITIZEN, VOTING AGE POPULATION: Citizen, 18 and over population: Male \
0 29014
1 32682
2 27323
3 26194
4 32183
5 31873
6 30398
7 26692
Geographic Identifier GIS Identifier \
0 11001 ACS_Demographic_DC_Ward_1
1 11002 ACS_Demographic_DC_Ward_2
2 11003 ACS_Demographic_DC_Ward_3
3 11004 ACS_Demographic_DC_Ward_4
4 11005 ACS_Demographic_DC_Ward_5
5 11006 ACS_Demographic_DC_Ward_6
6 11007 ACS_Demographic_DC_Ward_7
7 11008 ACS_Demographic_DC_Ward_8
HISPANIC OR LATINO AND RACE: Total population \
0 79448
1 77904
2 79851
3 86071
4 89628
5 80772
6 89870
7 87043
HISPANIC OR LATINO AND RACE: Total population: Hispanic or Latino (of any race) \
0 16209
1 10633
2 8544
3 18197
4 9060
5 6884
6 4516
7 3125
HISPANIC OR LATINO AND RACE: Total population: Hispanic or Latino (of any race): Cuban \
0 540
1 674
2 753
3 408
4 163
5 317
6 369
7 26
HISPANIC OR LATINO AND RACE: Total population: Hispanic or Latino (of any race): Mexican \
0 3179
1 1914
2 1066
3 1647
4 2144
5 1381
6 1006
7 865
... SEX AND AGE: Total population: Sex ratio (males per 100 females) \
0 ... 97.2
1 ... 98.3
2 ... 86.7
3 ... 93.0
4 ... 91.8
5 ... 93.7
6 ... 91.2
7 ... 83.5
SEX AND AGE: Total population: Under 18 years \
0 11714
1 4566
2 13570
3 20747
4 17307
5 10222
6 21839
7 24091
SEX AND AGE: Total population: Under 5 years SHAPE.AREA SHAPE.LEN \
0 4729 0 0
1 2326 0 0
2 3864 0 0
3 5865 0 0
4 5915 0 0
5 4233 0 0
6 7021 0 0
7 7569 0 0
Total housing units SE_ANNO_CAD_DATA Shape x y
0 43600 NaN NaN NaN NaN
1 50515 NaN NaN NaN NaN
2 42661 NaN NaN NaN NaN
3 36590 NaN NaN NaN NaN
4 43276 NaN NaN NaN NaN
5 49009 NaN NaN NaN NaN
6 41450 NaN NaN NaN NaN
7 43271 NaN NaN NaN NaN
[8 rows x 101 columns]>
file saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/demo_df.csv
# Load in economic data
poverty_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/Unemployment by ward 2020.csv')
# Define file path for folder
poverty_file_path = raw_data_folder + 'poverty_df.csv'
# Dataframe to csv to save
poverty_df.to_csv(poverty_file_path, index=False, header=True)
# Verify it was successful
print(f"File saved to {poverty_file_path}")<bound method NDFrame.head of LocationType Location TimeFrame DataFormat Data
0 City District of Columbia 2020 Percent 0.079
1 City District of Columbia 2022 Percent 0.047
2 City District of Columbia 2021 Percent 0.067
3 City District of Columbia 2023 Percent 0.049
4 Ward Ward 1 2022 Percent 0.035
5 Ward Ward 1 2021 Percent 0.047
6 Ward Ward 1 2020 Percent 0.063
7 Ward Ward 1 2023 Percent 0.038
8 Ward Ward 2 2020 Percent 0.043
9 Ward Ward 2 2022 Percent 0.034
10 Ward Ward 2 2021 Percent 0.033
11 Ward Ward 2 2023 Percent 0.037
12 Ward Ward 3 2021 Percent 0.032
13 Ward Ward 3 2022 Percent 0.033
14 Ward Ward 3 2020 Percent 0.042
15 Ward Ward 3 2023 Percent 0.035
16 Ward Ward 4 2020 Percent 0.091
17 Ward Ward 4 2022 Percent 0.043
18 Ward Ward 4 2021 Percent 0.071
19 Ward Ward 4 2023 Percent 0.045
20 Ward Ward 5 2021 Percent 0.085
21 Ward Ward 5 2022 Percent 0.059
22 Ward Ward 5 2020 Percent 0.102
23 Ward Ward 5 2023 Percent 0.061
24 Ward Ward 6 2020 Percent 0.069
25 Ward Ward 6 2022 Percent 0.037
26 Ward Ward 6 2021 Percent 0.059
27 Ward Ward 6 2023 Percent 0.042
28 Ward Ward 7 2021 Percent 0.116
29 Ward Ward 7 2022 Percent 0.081
30 Ward Ward 7 2020 Percent 0.130
31 Ward Ward 7 2023 Percent 0.079
32 Ward Ward 8 2020 Percent 0.170
33 Ward Ward 8 2022 Percent 0.096
34 Ward Ward 8 2021 Percent 0.154
35 Ward Ward 8 2023 Percent 0.097>
File saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/poverty_df.csv
housing_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/ACS_5-Year_Housing_Characteristics_DC_Ward.csv')
# Define file path for folder
housing_file_path = raw_data_folder + 'housing_df.csv'
# Dataframe to csv to save
housing_df.to_csv(housing_file_path, index=False, header=True)
# Verify it was successful
print(f"File saved to {housing_file_path}")<bound method NDFrame.head of OBJECTID GEOID NAMELSAD NAME DP04_0001E \
0 1 11001 Ward 1 Ward 1 (2022), District of Columbia 43600
1 2 11002 Ward 2 Ward 2 (2022), District of Columbia 50515
2 3 11003 Ward 3 Ward 3 (2022), District of Columbia 42661
3 4 11004 Ward 4 Ward 4 (2022), District of Columbia 36590
4 5 11005 Ward 5 Ward 5 (2022), District of Columbia 43276
5 6 11006 Ward 6 Ward 6 (2022), District of Columbia 49009
6 7 11007 Ward 7 Ward 7 (2022), District of Columbia 41450
7 8 11008 Ward 8 Ward 8 (2022), District of Columbia 43271
DP04_0002E DP04_0003E DP04_0004E DP04_0005E DP04_0006E ... \
0 39257 4343 1.2 7.3 43600 ...
1 44640 5875 2.7 8.5 50515 ...
2 39099 3562 0.7 5.9 42661 ...
3 33896 2694 1.6 5.0 36590 ...
4 39485 3791 0.7 7.1 43276 ...
5 43885 5124 1.0 8.5 49009 ...
6 37374 4076 2.0 5.3 41450 ...
7 38149 5122 4.0 8.1 43271 ...
DP04_0139E DP04_0140E DP04_0141E DP04_0142E DP04_0143E \
0 3932 3512 2755 6686 749
1 4095 2754 2618 8534 1315
2 2754 2310 1579 6613 1139
3 1577 1592 1334 4449 511
4 2608 2444 1773 8340 1134
5 4297 3669 2847 7808 1191
6 2276 2263 1788 9040 1674
7 2856 2322 2791 12675 1790
GIS_ID SE_ANNO_CAD_DATA \
0 ACS_Housing_DC_Ward_1 NaN
1 ACS_Housing_DC_Ward_2 NaN
2 ACS_Housing_DC_Ward_3 NaN
3 ACS_Housing_DC_Ward_4 NaN
4 ACS_Housing_DC_Ward_5 NaN
5 ACS_Housing_DC_Ward_6 NaN
6 ACS_Housing_DC_Ward_7 NaN
7 ACS_Housing_DC_Ward_8 NaN
GLOBALID SHAPEAREA SHAPELEN
0 {4A15AC33-C52E-46AC-ADEF-2580524EC688} 0 0
1 {5890C874-568F-4DF9-A53D-79919DB299B6} 0 0
2 {05832727-9756-4906-84F7-ED2334A389DF} 0 0
3 {DDFF44D0-3668-496C-9E31-D08B8ABCF5C1} 0 0
4 {C27483B8-E949-4096-BC8B-1DD942A74842} 0 0
5 {AA8EE892-3A5A-4E57-8141-36C3FFEBE1C2} 0 0
6 {073CD593-555B-4632-8656-9D32E65287AF} 0 0
7 {202FF8BE-E235-4FC2-9DF8-AC63F124ED3E} 0 0
[8 rows x 152 columns]>
File saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/housing_df.csv
economic_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/Economic Characteristics of DC Wards.csv')
# Define file path for folder
economic_file_path = raw_data_folder + 'economic_df.csv'
# Dataframe to csv to save
economic_df.to_csv(economic_file_path, index=False, header=None)
# Verify it was successful
print(f"File saved to {economic_file_path}")<bound method NDFrame.head of Economic Characteristics of DC Wards \
0 Name
1 Ward 1 (2022), District of Columbia
2 Ward 2 (2022), District of Columbia
3 Ward 3 (2022), District of Columbia
4 Ward 4 (2022), District of Columbia
5 Ward 5 (2022), District of Columbia
6 Ward 6 (2022), District of Columbia
7 Ward 7 (2022), District of Columbia
8 Ward 8 (2022), District of Columbia
Unnamed: 1 \
0 CLASS OF WORKER: Civilian employed population...
1 53107
2 52142
3 44720
4 45552
5 47995
6 55179
7 36893
8 33810
Unnamed: 2 \
0 CLASS OF WORKER: Civilian employed population...
1 11275
2 10767
3 10389
4 11505
5 12136
6 16106
7 12083
8 9333
Unnamed: 3 \
0 CLASS OF WORKER: Civilian employed population...
1 39173
2 39284
3 31481
4 31088
5 33678
6 36971
7 23399
8 23647
Unnamed: 4 \
0 CLASS OF WORKER: Civilian employed population...
1 2583
2 1986
3 2710
4 2824
5 2125
6 2083
7 1411
8 810
Unnamed: 5 \
0 CLASS OF WORKER: Civilian employed population...
1 76
2 105
3 140
4 135
5 56
6 19
7 0
8 20
Unnamed: 6 \
0 COMMUTING TO WORK: Workers 16 years and over
1 52825
2 51712
3 44347
4 44558
5 47086
6 55629
7 36325
8 34483
Unnamed: 7 \
0 COMMUTING TO WORK: Workers 16 years and over:...
1 1574
2 944
3 1627
4 3163
5 2394
6 1481
7 2812
8 2406
Unnamed: 8 \
0 COMMUTING TO WORK: Workers 16 years and over:...
1 10381
2 7736
3 14173
4 17663
5 17383
6 11840
7 15739
8 14046
Unnamed: 9 ... \
0 COMMUTING TO WORK: Workers 16 years and over:... ...
1 30.2 ...
2 24.4 ...
3 27.3 ...
4 34.2 ...
5 31.6 ...
6 27.6 ...
7 35.5 ...
8 34.8 ...
Unnamed: 137 \
0 PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS ...
1 23.3
2 13.1
3 1.3
4 9.7
5 18
6 16.9
7 34
8 35.4
Unnamed: 138 \
0 PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS ...
1 25.2
2 15.2
3 1.5
4 10.2
5 19.7
6 23.2
7 36.2
8 36.2
Unnamed: 139 \
0 PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS ...
1 20.5
2 11.2
3 0.9
4 8.3
5 14.9
6 8
7 29.3
8 33.7
Unnamed: 140 Unnamed: 141 \
0 PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS ... SHAPE.AREA
1 12.1 0
2 16.3 0
3 17.9 0
4 16.3 0
5 22.4 0
6 12.9 0
7 29.5 0
8 32.1 0
Unnamed: 142 Unnamed: 143 Unnamed: 144 Unnamed: 145 Unnamed: 146
0 SHAPE.LEN SE_ANNO_CAD_DATA SHAPE x y
1 0 NaN NaN NaN NaN
2 0 NaN NaN NaN NaN
3 0 NaN NaN NaN NaN
4 0 NaN NaN NaN NaN
5 0 NaN NaN NaN NaN
6 0 NaN NaN NaN NaN
7 0 NaN NaN NaN NaN
8 0 NaN NaN NaN NaN
[9 rows x 147 columns]>
File saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/economic_df.csv
ward_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/schools_by_ward.csv', header=0)
ward_file_path = raw_data_folder + 'ward_df.csv'
ward_df.to_csv(ward_file_path, index=False)
print(f"File saved to {ward_file_path}")<bound method NDFrame.head of school_name ward
0 Anacostia High School Ward 8
1 Ballou High School Ward 8
2 Ballou STAY High School Ward 8
3 Benjamin Banneker High School Ward 2
4 Cardozo Education Campus Ward 1
5 Columbia Heights Education Campus Ward 1
6 Coolidge High School Ward 4
7 Duke Ellington School of the Arts Ward 2
8 Dunbar High School Ward 5
9 Eastern High School Ward 7
10 H.D. Woodson High School Ward 7
11 Luke C. Moore High School Ward 5
12 McKinley Technology High School Ward 5
13 Phelps Architecture, Construction and Engineer... Ward 5
14 Ron Brown College Preparatory High School Ward 7
15 Roosevelt High School Ward 4
16 Roosevelt STAY High School Ward 1
17 School Without Walls High School Ward 2
18 Washington Metropolitan High School Ward 1
19 Woodrow Wilson High School Ward 3
20 Capital City PCS - High School Ward 4
21 Cesar Chavez PCS for Public Policy - Parkside ... Ward 1
22 E.L. Haynes PCS - High School Ward 4
23 Friendship PCS - Collegiate Academy Ward 7
24 Friendship PCS - Technology Preparatory High S... Ward 8
25 IDEA PCS Ward 7
26 KIPP DC - Somerset College Preparatory PCS Ward 5
27 Maya Angelou PCS - High School Ward 7
28 Paul PCS - International High School Ward 4
29 Thurgood Marshall Academy PCS Ward 8
30 Washington Latin PCS - Upper School Ward 4
31 National Collegiate Preparatory PCHS Ward 8
32 Richard Wright PCS for Journalism and Media Arts Ward 6
33 BASIS DC PCS Ward 2
34 District of Columbia International School Ward 4
35 Kingsman Academy PCS Ward 6
36 Washington Leadership Academy PCS Ward 5>
I used the URL from DC’s GIS service for the grocery data. The endpoint URL pulls the grocery data in geoJSON format, which I loaded into a GeoDataFrame using geopandas’ gpd.read_file. This gave me the data in a format suitable for GIS methods and a standard DataFrame. I used the same raw data path and named the data ‘grocery_df.’
At first, I thought I would need to use GIS methods to map the grocery stores to their ward locations. Luckily, when I printed the head of the DataFrame, I realized the ward was included.
import geopandas as gpd
# Load the ward boundary shapefile
grocery_url = "https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/Business_Goods_and_Service_WebMercator/MapServer/4/query?outFields=*&where=1%3D1&f=geojson"
# Load the geojson into a geodataframe
grocery_data = gpd.read_file(grocery_url)
grocery_file_path = raw_data_folder + 'grocery_df.csv'
grocery_df = grocery_data.to_csv(grocery_file_path, index=False)
print(grocery_df.head())
# Verify it was successful
print(f"File saved to {grocery_file_path}")<bound method NDFrame.head of STORENAME ADDRESS ZIPCODE \
0 Target 7828 GEORGIA AVENUE NW 20012
1 International Progresso Market 7719 GEORGIA AVENUE NW 20012
2 Jocelyn’s Market 6404 GEORGIA AVENUE NW 20012
3 Magruder's 5626 CONNECTICUT AVENUE NW 20015
4 Safeway 5545 CONNECTICUT AVENUE NW 20015
.. ... ... ...
85 AmazonFresh 1733 14TH STREET NW 20009
86 Union Kitchen Grocery 1251 9TH STREET NW 20001
87 Union Kitchen Grocery 1924 8TH STREET NW 20005
88 Union Kitchen Grocery 1625 ECKINGTON PLACE NE 20002
89 Union Kitchen Grocery 1625 ECKINGTON PLACE NE 20002
PHONE WARD SSL NOTES PRESENT90 PRESENT95 PRESENT00 \
0 2.028924e+09 Ward 4 2960 0017 None None None None
1 2.025455e+09 Ward 4 2961 0810 None None None None
2 2.027228e+09 Ward 4 2945 0839 None None None None
3 2.024645e+09 Ward 3 1860 0013 Yes Yes Yes
4 2.022446e+09 Ward 3 1867 0092 No Yes Yes
.. ... ... ... ... ... ... ...
85 NaN Ward 2 0239 0131 None No No No
86 2.027928e+09 Ward 2 0400 0852 None No No No
87 2.027928e+09 Ward 1 0393 2017 None No No No
88 2.027928e+09 Ward 5 3576 0057 None No No No
89 2.027928e+09 Ward 6 0754 0098 None No No No
... YCOORD MAR_ID GLOBALID CREATOR \
0 ... 146280.07 300512 {CE302122-4E8D-4DC7-845B-06563B993F66} JLAY
1 ... 146169.97 285417 {2C18863D-40DE-4F66-AA59-46F1C65D67F2} JLAY
2 ... 144428.00 285334 {A0B448C3-B3FB-49FE-B8BF-1FEBA3CEA642} JLAY
3 ... 144184.19 263860 {9ABE1C46-BB48-4110-B5BA-C6363CA3BD7A} JLAY
4 ... 144078.34 263999 {902F540E-FE16-43A5-8010-CD84E94B28B7} JLAY
.. ... ... ... ... ...
85 ... 138371.30 318767 {B7BC9F16-A487-45FA-B334-3F316A686E3D} JLAY
86 ... 137691.32 301804 {4DAF45C3-D4AB-4483-9C6D-46E2ECE3B04A} JLAY
87 ... 138741.56 310465 {F74916FB-B017-4045-9BA8-3623DA4F2C9D} JLAY
88 ... 138237.10 145957 {60636EEE-00C8-44FA-A8DA-7A8896997FAD} JLAY
89 ... 136593.94 9536 {F3D6C804-C6EA-4876-9246-F757ADC92793} JLAY
CREATED EDITOR EDITED SE_ANNO_CAD_DATA OBJECTID \
0 1664402694000 JLAY 1727725255000 None 641
1 1664402694000 JLAY 1727725255000 None 642
2 1664402694000 JLAY 1727725255000 None 643
3 1664402694000 JLAY 1727725255000 None 644
4 1664402694000 JLAY 1727725255000 None 645
.. ... ... ... ... ...
85 1727725420000 JLAY 1727732365000 None 1281
86 1727730677000 JLAY 1727732365000 None 1603
87 1727730677000 JLAY 1727732365000 None 1604
88 1727730677000 JLAY 1727732365000 None 1605
89 1727730677000 JLAY 1727732365000 None 1606
geometry
0 POINT (-77.02698 38.98446)
1 POINT (-77.02607 38.98346)
2 POINT (-77.0278 38.96777)
3 POINT (-77.07646 38.96555)
4 POINT (-77.07491 38.9646)
.. ...
85 POINT (-77.03174 38.91321)
86 POINT (-77.02372 38.90709)
87 POINT (-77.02322 38.91655)
88 POINT (-77.004 38.91201)
89 POINT (-77.00221 38.8972)
[90 rows x 37 columns]>
File saved to /Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/grocery_df.csv
Datasets
District of Columbia Public Schools Data Available Upon Request