Data 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

import pandas as pd
import numpy as np
from pathlib import Path
import requests
import os
import openpyxl

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