Data Cleaning

Code

import pandas as pd
import numpy as np
import os

I started by reading my data using Pandas’ read_csv. This included files for test scores, attendance, discipline, graduation rates, and ward information. Each file was loaded into a separate DataFrame for further analysis.

sch_test = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/2020 AP IB and SAT - Metric Scores.xlsx_School Metric Scores.csv')

sch_att = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/2020 Attendance - Metric Scores.xlsx_School Metric Scores.csv')

sch_dis = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/2020 Discipline - Metric Scores.xlsx_School Metric Scores.csv')

sch_grad = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/Public SY1920 ACGR Rates 4-Year.xlsx_2019-20 Rate by School.csv')

ward = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/ward_df.csv')

I printed the head of each DataFrame to get a look at the structure and content of the data. I also printed the data types for each column, which helped me identify potential issues, like mixed data types or columns needing type conversion. From this initial inspection, I noticed strings, integers, and missing values, which told me I needed extensive cleaning.

print(sch_test.head())
print('\n')
print(sch_test.dtypes)
print('\n')

print('\n')
print(sch_att.head())
print('\n')
print(sch_att.dtypes)
print('\n')

print('\n')
print(sch_dis.head())
print('\n')
print(sch_dis.head())
print('\n')

print('\n')
print(sch_grad.head())
print('\n')
print(sch_grad.dtypes)
print('\n')
                              LEA Name  LEA Code            School Name  \
0  District of Columbia Public Schools         1  Anacostia High School   
1  District of Columbia Public Schools         1  Anacostia High School   
2  District of Columbia Public Schools         1  Anacostia High School   
3  District of Columbia Public Schools         1  Anacostia High School   
4  District of Columbia Public Schools         1  Anacostia High School   

   School Code                            Student Group               Metric  \
0          450                 All Report Card Students  AP/IB Participation   
1          450           American Indian/Alaskan Native  AP/IB Participation   
2          450  American Indian/Alaskan Native - Female  AP/IB Participation   
3          450    American Indian/Alaskan Native - Male  AP/IB Participation   
4          450                                    Asian  AP/IB Participation   

  Metric Score Metric N  School Year  
0         23.4       47         2020  
1         n<10     n<10         2020  
2         n<10     n<10         2020  
3         n<10     n<10         2020  
4         n<10     n<10         2020  


LEA Name         object
LEA Code          int64
School Name      object
School Code       int64
Student Group    object
Metric           object
Metric Score     object
Metric N         object
School Year       int64
dtype: object




                              LEA Name  LEA Code              School Name  \
0  District of Columbia Public Schools         1  Aiton Elementary School   
1  District of Columbia Public Schools         1  Aiton Elementary School   
2  District of Columbia Public Schools         1  Aiton Elementary School   
3  District of Columbia Public Schools         1  Aiton Elementary School   
4  District of Columbia Public Schools         1  Aiton Elementary School   

   School Code                            Student Group          Metric  \
0          202                 All Report Card Students  90% Attendance   
1          202           American Indian/Alaskan Native  90% Attendance   
2          202  American Indian/Alaskan Native - Female  90% Attendance   
3          202    American Indian/Alaskan Native - Male  90% Attendance   
4          202                                    Asian  90% Attendance   

  Metric Score Metric N  School Year  
0         77.3      163         2020  
1         n<10     n<10         2020  
2         n<10     n<10         2020  
3         n<10     n<10         2020  
4         n<10     n<10         2020  


LEA Name         object
LEA Code          int64
School Name      object
School Code       int64
Student Group    object
Metric           object
Metric Score     object
Metric N         object
School Year       int64
dtype: object




                              LEA Name  LEA Code              School Name  \
0  District of Columbia Public Schools         1  Aiton Elementary School   
1  District of Columbia Public Schools         1  Aiton Elementary School   
2  District of Columbia Public Schools         1  Aiton Elementary School   
3  District of Columbia Public Schools         1  Aiton Elementary School   
4  District of Columbia Public Schools         1  Aiton Elementary School   

   School Code                            Student Group          Metric  \
0          202                 All Report Card Students  Bullying Count   
1          202           American Indian/Alaskan Native  Bullying Count   
2          202  American Indian/Alaskan Native - Female  Bullying Count   
3          202    American Indian/Alaskan Native - Male  Bullying Count   
4          202                                    Asian  Bullying Count   

  Metric Score Metric N  School Year  
0          <=1      244         2020  
1          <=1     n<10         2020  
2          <=1     n<10         2020  
3          <=1     n<10         2020  
4          <=1     n<10         2020  


                              LEA Name  LEA Code              School Name  \
0  District of Columbia Public Schools         1  Aiton Elementary School   
1  District of Columbia Public Schools         1  Aiton Elementary School   
2  District of Columbia Public Schools         1  Aiton Elementary School   
3  District of Columbia Public Schools         1  Aiton Elementary School   
4  District of Columbia Public Schools         1  Aiton Elementary School   

   School Code                            Student Group          Metric  \
0          202                 All Report Card Students  Bullying Count   
1          202           American Indian/Alaskan Native  Bullying Count   
2          202  American Indian/Alaskan Native - Female  Bullying Count   
3          202    American Indian/Alaskan Native - Male  Bullying Count   
4          202                                    Asian  Bullying Count   

  Metric Score Metric N  School Year  
0          <=1      244         2020  
1          <=1     n<10         2020  
2          <=1     n<10         2020  
3          <=1     n<10         2020  
4          <=1     n<10         2020  




  DC 2020 4-Year Adjusted Cohort Graduation Rates, Unnamed: 1    Unnamed: 2  \
0                                        by School        NaN           NaN   
1                                              NaN        NaN           NaN   
2                                           School  Graduates  Cohort Total   
3                                            State       3405          4802   
4                                             DCPS       2180          3174   

  Unnamed: 3  
0        NaN  
1        NaN  
2  2020 ACGR  
3      70.9%  
4     68.68%  


DC 2020 4-Year Adjusted Cohort Graduation Rates,    object
Unnamed: 1                                          object
Unnamed: 2                                          object
Unnamed: 3                                          object
dtype: object

I printed the column names for each DataFrame because I was trying to understand the entire structure before I started with data manipulation. Most of the DataFrames had consistent and descriptive column names, but the graduation rate DataFrame stood out to me. I realized the column titles shifted, leaving some columns unnamed - misaligning the data.

print(sch_test.columns)
print('\n')

print('\n')
print(sch_att.columns)
print('\n')

print('\n')
print(sch_dis.columns)
print('\n')

print('\n')
print(sch_grad.columns)
print('\n')
Index(['LEA Name', 'LEA Code', 'School Name', 'School Code', 'Student Group',
       'Metric', 'Metric Score', 'Metric N', 'School Year'],
      dtype='object')




Index(['LEA Name', 'LEA Code', 'School Name', 'School Code', 'Student Group',
       'Metric', 'Metric Score', 'Metric N', 'School Year'],
      dtype='object')




Index(['LEA Name', 'LEA Code', 'School Name', 'School Code', 'Student Group',
       'Metric', 'Metric Score', 'Metric N', 'School Year'],
      dtype='object')




Index(['DC 2020 4-Year Adjusted Cohort Graduation Rates,', 'Unnamed: 1',
       'Unnamed: 2', 'Unnamed: 3'],
      dtype='object')

After noticing the misalignment in the graduation rate DataFrame, I decided to address that next. I first checked for missing values using .isnull().sum() and then dropped the unnecessary rows.

Next, I set the first row of the DataFrame as the column headers to correct the header issue. I dropped this row since I no longer needed it and then reset the index to keep things neat. Once I made this adjustment, I printed the updated DataFrame to ensure the structure looked as expected. This was a crucial step in making sch_grad usable for analysis.

print(sch_grad)
sch_grad.isnull().sum()
sch_grad.drop(0, inplace=True)
sch_grad.dropna(inplace=True)

# Set the first row as the header
sch_grad.columns = sch_grad.iloc[0]  # Assign the first row as column names
sch_grad = sch_grad[1:]  # Drop the first row since it's now the header
sch_grad.reset_index(drop=True, inplace=True)  # Reset the index
print(sch_grad)
     DC 2020 4-Year Adjusted Cohort Graduation Rates, Unnamed: 1  \
0                                           by School        NaN   
1                                                 NaN        NaN   
2                                              School  Graduates   
3                                               State       3405   
4                                                DCPS       2180   
5                               Roosevelt High School        162   
6                            H.D. Woodson High School        107   
7                                  Dunbar High School        136   
8                    School Without Walls High School        146   
9   Phelps Architecture, Construction and Engineer...         66   
10                            Ballou STAY High School       n<10   
11                Washington Metropolitan High School       n<10   
12                           Cardozo Education Campus        119   
13                          Luke C. Moore High School         12   
14                      Benjamin Banneker High School         DS   
15          Ron Brown College Preparatory High School         50   
16                         Woodrow Wilson High School        395   
17                              Anacostia High School         61   
18                               Coolidge High School         80   
19                    McKinley Technology High School        125   
20                         Roosevelt STAY High School         27   
21                                 Ballou High School        113   
22                  Columbia Heights Education Campus        179   
23                  Duke Ellington School of the Arts         DS   
24                                Eastern High School        167   
25                                                PCS       1223   
26                      Thurgood Marshall Academy PCS         55   
27          District of Columbia International School         63   
28               Paul PCS - International High School         99   
29   Richard Wright PCS for Journalism and Media Arts         53   
30  Friendship PCS - Technology Preparatory High S...         59   
31                                       BASIS DC PCS         DS   
32                               Kingsman Academy PCS         13   
33         KIPP DC - Somerset College Preparatory PCS         DS   
34                                           IDEA PCS         57   
35                     Capital City PCS - High School         71   
36                Washington Latin PCS - Upper School         67   
37                Friendship PCS - Collegiate Academy        110   
38               National Collegiate Preparatory PCHS         30   
39                     Maya Angelou PCS - High School         29   
40                          Goodwill Excel Center PCS       n<10   
41                  Washington Leadership Academy PCS         82   
42  Cesar Chavez PCS for Public Policy - Parkside ...         97   
43          KIPP DC - College Preparatory Academy PCS        138   
44                          SEED PCS of Washington DC         28   
45                      E.L. Haynes PCS - High School         78   
46  a Graduation rates are not reported for the fo...        NaN   
47  b Graduation rates are not provided for indivi...        NaN   

      Unnamed: 2 Unnamed: 3  
0            NaN        NaN  
1            NaN        NaN  
2   Cohort Total  2020 ACGR  
3           4802      70.9%  
4           3174     68.68%  
5            236      68.6%  
6            136      78.7%  
7            170      80.0%  
8            148      98.7%  
9             71      93.0%  
10            99       n<10  
11            29       n<10  
12           187      63.6%  
13           127       9.4%  
14            92       >95%  
15            65      76.9%  
16           453      87.2%  
17            98      62.2%  
18           116      69.0%  
19           127      98.4%  
20           217      12.4%  
21           182      62.1%  
22           227      78.9%  
23           123       >99%  
24           206      81.1%  
25          1597     76.58%  
26            72      76.4%  
27            73      86.3%  
28           119      83.2%  
29            68      77.9%  
30            63      93.7%  
31            50       >95%  
32            51      25.5%  
33            29       >95%  
34            71      80.3%  
35            80      88.8%  
36            83      80.7%  
37           124      88.7%  
38            60      50.0%  
39            62      46.8%  
40            46       n<10  
41           101      81.2%  
42           112      86.6%  
43           160      86.3%  
44            35      80.0%  
45            86      90.7%  
46           NaN        NaN  
47           NaN        NaN  
2                                              School Graduates Cohort Total  \
0                                               State      3405         4802   
1                                                DCPS      2180         3174   
2                               Roosevelt High School       162          236   
3                            H.D. Woodson High School       107          136   
4                                  Dunbar High School       136          170   
5                    School Without Walls High School       146          148   
6   Phelps Architecture, Construction and Engineer...        66           71   
7                             Ballou STAY High School      n<10           99   
8                 Washington Metropolitan High School      n<10           29   
9                            Cardozo Education Campus       119          187   
10                          Luke C. Moore High School        12          127   
11                      Benjamin Banneker High School        DS           92   
12          Ron Brown College Preparatory High School        50           65   
13                         Woodrow Wilson High School       395          453   
14                              Anacostia High School        61           98   
15                               Coolidge High School        80          116   
16                    McKinley Technology High School       125          127   
17                         Roosevelt STAY High School        27          217   
18                                 Ballou High School       113          182   
19                  Columbia Heights Education Campus       179          227   
20                  Duke Ellington School of the Arts        DS          123   
21                                Eastern High School       167          206   
22                                                PCS      1223         1597   
23                      Thurgood Marshall Academy PCS        55           72   
24          District of Columbia International School        63           73   
25               Paul PCS - International High School        99          119   
26   Richard Wright PCS for Journalism and Media Arts        53           68   
27  Friendship PCS - Technology Preparatory High S...        59           63   
28                                       BASIS DC PCS        DS           50   
29                               Kingsman Academy PCS        13           51   
30         KIPP DC - Somerset College Preparatory PCS        DS           29   
31                                           IDEA PCS        57           71   
32                     Capital City PCS - High School        71           80   
33                Washington Latin PCS - Upper School        67           83   
34                Friendship PCS - Collegiate Academy       110          124   
35               National Collegiate Preparatory PCHS        30           60   
36                     Maya Angelou PCS - High School        29           62   
37                          Goodwill Excel Center PCS      n<10           46   
38                  Washington Leadership Academy PCS        82          101   
39  Cesar Chavez PCS for Public Policy - Parkside ...        97          112   
40          KIPP DC - College Preparatory Academy PCS       138          160   
41                          SEED PCS of Washington DC        28           35   
42                      E.L. Haynes PCS - High School        78           86   

2  2020 ACGR  
0      70.9%  
1     68.68%  
2      68.6%  
3      78.7%  
4      80.0%  
5      98.7%  
6      93.0%  
7       n<10  
8       n<10  
9      63.6%  
10      9.4%  
11      >95%  
12     76.9%  
13     87.2%  
14     62.2%  
15     69.0%  
16     98.4%  
17     12.4%  
18     62.1%  
19     78.9%  
20      >99%  
21     81.1%  
22    76.58%  
23     76.4%  
24     86.3%  
25     83.2%  
26     77.9%  
27     93.7%  
28      >95%  
29     25.5%  
30      >95%  
31     80.3%  
32     88.8%  
33     80.7%  
34     88.7%  
35     50.0%  
36     46.8%  
37      n<10  
38     81.2%  
39     86.6%  
40     86.3%  
41     80.0%  
42     90.7%  

Once the sch_grad DataFrame was well structured, I standardized the column names across my datasets. I began with stripping any extra whitespace from the column names in sch_grad using str.strip() and then I renamed the School column to School Name to make it consistent with the other datasets.

I then moved on to sch_test and ward, which also had a school_name column and I renamed these to School Nmae as well.

sch_grad.columns = sch_grad.columns.str.strip()
sch_grad.rename(columns={'School': 'School Name'}, inplace=True)
print(sch_grad.columns)

sch_test.rename(columns={'school_name': 'School Name'}, inplace=True)
print(sch_test.columns)

ward.rename(columns={'school_name': 'School Name'}, inplace=True)
print(ward.columns)
Index(['School Name', 'Graduates', 'Cohort Total', '2020 ACGR'], dtype='object', name=2)
Index(['LEA Name', 'LEA Code', 'School Name', 'School Code', 'Student Group',
       'Metric', 'Metric Score', 'Metric N', 'School Year'],
      dtype='object')
Index(['School Name', 'ward'], dtype='object')

Since these datasets had information ranging from elementary to high school, I decided to look only at the high schools so that the metrics across data points were consistent. To do this, I used the sch_grad DataFrame as a reference, since it was the one DataFrame that contained only high school data. I sed .isin() to filter sch_test, sch_att, and sch_dis Dataframes to only keep rows where the school name matched one from sch_grad. This ensures that all my datasets and aligned and consist of the same group of schools.

After filtering, I double-checked fot any missing values in the new high school DataFrames using .isna().sum().sum().

# Filter all df by grad data frame to get only the high schools
high_schools_only_test = sch_test[sch_test['School Name'].isin(sch_grad['School Name'])]
high_schools_only_att = sch_att[sch_att['School Name'].isin(sch_grad['School Name'])]
high_schools_only_dis = sch_dis[sch_dis['School Name'].isin(sch_grad['School Name'])]

# Make sure theres no missing values
print(high_schools_only_att.isna().sum().sum())
print(high_schools_only_dis.isna().sum().sum())
print(high_schools_only_test.isna().sum().sum())

0
0
0

Here, I dropped columns that I would not be using.

# Drop LEA Name and LEA Code from all dataframes
high_schools_only_att = high_schools_only_att.drop(columns=['LEA Name', 'LEA Code', 'School Code', 'School Year'])
high_schools_only_dis = high_schools_only_dis.drop(columns=['LEA Name', 'LEA Code', 'School Code', 'School Year'])
high_schools_only_test = high_schools_only_test.drop(columns=['LEA Name', 'LEA Code', 'School Code', 'School Year'])

I filtered out any rows that contained ‘n<10’ since this would not be useful for my analysis. This helped me see what data I had to work with - specifically student groups.

# Filter out rows where metric columns contain 'n<10'
high_schools_only_att = high_schools_only_att[~high_schools_only_att['Metric Score'].str.contains('n<10', na=False)]
high_schools_only_att = high_schools_only_att[~high_schools_only_att['Metric N'].str.contains('n<10', na=False)]

high_schools_only_dis = high_schools_only_dis[~high_schools_only_dis['Metric Score'].str.contains('n<10', na=False)]
high_schools_only_dis = high_schools_only_dis[~high_schools_only_dis['Metric N'].str.contains('n<10', na=False)]

high_schools_only_test = high_schools_only_test[~high_schools_only_test['Metric Score'].str.contains('n<10', na=False)]
high_schools_only_test = high_schools_only_test[~high_schools_only_test['Metric N'].str.contains('n<10', na=False)]

In the previous step, I realized the main student groups with data were Black students, White students, and all report card students. So, I decided to filter for only those rows, as they would be the most useful to me.

# Filter to only black and white students
groups_to_keep = [
    "All Report Card Students",
    "White - Male",
    "White - Female",
    "Black/African American - Female",
    "Black/African American - Male"
]

high_schools_only_att = high_schools_only_att[high_schools_only_att['Student Group'].isin(groups_to_keep)]

high_schools_only_dis = high_schools_only_dis[high_schools_only_dis['Student Group'].isin(groups_to_keep)]

high_schools_only_test = high_schools_only_test[high_schools_only_test['Student Group'].isin(groups_to_keep)]

The discipline Dataframe has many rows, but I decided the rows with the counts of incidents would be most useful to my analysis, so I filtered for those rows.

# Filter the discipline df to keep only the count rows
high_schools_only_dis = high_schools_only_dis[~high_schools_only_dis['Metric'].str.endswith("Rate", na=False)]

Here, I am printing the shape so I have an idea of the amount of rows and columns.

print(high_schools_only_att.shape)
print(high_schools_only_dis.shape)
print(high_schools_only_test.shape)
(103, 5)
(385, 5)
(158, 5)

After researching the schools, I discovered that Goodwill Excel is an adult learning facility. I decided to drop that school from my data since I wanted to focus on schools for adolescents.

# Drop Goodwill Excel since it is an adult learning facility
# List of DataFrames to process
dataframes = [high_schools_only_att, high_schools_only_dis, high_schools_only_test, sch_grad]  # Replace with your actual DataFrame variables

# Drop rows containing "Goodwill Excel Center PCS" in the 'School Name' column for each DataFrame
filtered_dataframes = [
    df[~df['School Name'].str.contains("Goodwill Excel Center PCS", na=False)]
    for df in dataframes
]

# Assign back to the original DataFrames
high_schools_only_att, high_schools_only_dis, high_schools_only_test, sch_grad = filtered_dataframes

Here, I am again printing the shape to get an idea of the amount of rows and columns.

print(high_schools_only_att.shape)
print(high_schools_only_dis.shape)
print(high_schools_only_test.shape)
print(sch_grad.shape)
(102, 5)
(378, 5)
(158, 5)
(42, 4)

With the datasets cleaned and filtered, I merged the ward information with each DataFrame using left join on the school name column for my cleaned attendance, discipline, test and graduation data.

attendance_df = high_schools_only_att.merge(ward, on='School Name', how='left')
discipline_df = high_schools_only_dis.merge(ward, on='School Name', how='left')
test_df = high_schools_only_test.merge(ward, on='School Name', how='left')
grad_df = sch_grad.merge(ward, on='School Name', how='left')
print(attendance_df.shape)
print(discipline_df.shape)
print(test_df.shape)
print(grad_df.shape)
(102, 6)
(378, 6)
(158, 6)
(42, 5)

Next, I cleaned up the Metric Score and Metric N columns in the test_df. I checked for unique values in these columns to see what may need to be cleaned, such as symbols like > or < and the placeholder DS.

To address this, I replaced DS in Metric N with NaN and converted the column to a numeric type for calculations. For Metric Score, I removed various symbols and scaled the values by dividing them by 100 so they were proportions since they came as percentages. After I cleaned the columns, I used them to calculate a new column, Actual Count, which represents the estimated number of students based on the metric score and the total number of students (Metric N)

Finally, I filled any missing values in Actual Count with 0 and ensured the columns were an integer type.

# Check for values with '>' or '<' in the column
print(test_df['Metric Score'].unique())
print(test_df['Metric N'].unique())

# Replace 'DS' with NaN in Metric N
test_df['Metric N'] = test_df['Metric N'].replace('DS', np.nan).astype(float)

# Clean and convert Metric Score
test_df['Metric Score'] = test_df['Metric Score'].str.replace(r'[=><%]', '', regex=True).astype(float)
test_df['Metric Score'] = (test_df['Metric Score'] / 100).round(2)

# Calculate the actual count
test_df['Actual Count'] = (test_df['Metric Score'] * test_df['Metric N']).round()

# Handle missing values
test_df['Actual Count'] = test_df['Actual Count'].fillna(0).astype(int)  # Fill missing with 0
print(test_df['Actual Count'].unique())
['23.4' '<5%' '17.14' '19.14' '2.43' '26.82' '>95%' '58.97' '80.64'
 '44.09' '35.44' '2.04' '25.17' '97.63' '28.35' '11.29' '57.52' '38.33'
 '38.09' '22.72' '94.26' '44.57' '81.25' '66.66' '24.19' '79.03' '34.57'
 '12.24' '3.52' '36.61' '60.89' '3.17' '45.5' '43.56' '8' '<1%' '32.17'
 '>99%' '34.98' '76.92' '40' '92.3' '67.21' '20' '11.11' '71.42' '8.51'
 '53.19' '38.4' '41.07' '3.55' '34.91' '86.47' '96.03' '92' '87.75'
 '86.11' '93.87' '88.88' '82.33' '76.99' '90.69' '81.34' '47.1' '75.29'
 '80.91' '83.33' '88.23' '46.66' '64.15' '12' '57.33' '29.41' '16.66'
 '4.76' '55.23' '53.33' '11.53' '57.65' '6.2' '13.95' '59.68' '24.13'
 '22.22' '15' '61.66' '37.28' '52.14' '38.5' '2.08' '8.33' '33.33' '50'
 '48.71' '8.57' '60.95' '7.4' '45.16' '43.07' '18.36' '11.94' '71.64'
 '73.33' '>=90%' '81.81' '84.54' '88.57' '90.32' '88.46' '15.21' '21.73'
 '17.77' '5.26' '36.84' '79.5' '87.5' '91.11' '90' '91.17' '72.72' '82.85'
 '52.83' '13.41' '56.09']
['47' '23' '70' '94' '37' '123' '92' '234' '93' 'DS' '127' '79' '147'
 '169' '268' '186' '60' '21' '88' '122' '175' '16' '12' '124' '107' '49'
 '142' '156' '32' '189' '101' '50' '115' '126' '283' '130' '61' '45' '63'
 '138' '56' '141' '392' '100' '385' '739' '215' '193' '414' '78' '85' '75'
 '53' '68' '18' '105' '111' '145' '129' '58' '36' '59' '140' '144' '30'
 '27' '31' '65' '67' '110' '35' '46' '57' '161' '22' '76' '159' '82']
[ 11   1   4  12  18   2  33  87 138  75   0  56  28   3  37 166  20 108
  23   8 115  79  13  30  98   6  53  95  44 125  99  52 120  41   9   7
  25  59 140 337  97  92 129 316 569 196 156 195  64 335  65  35  34  43
   5  58  50  40  46  77  14  22  73  38  48  55  94  31  69  10  21  45
  62  16  72  84]

I followed similar steps with the attendance_df as I did with the test_df to clean up the Metric Score and Metric N columns and calculate the Actual Count. I replaced DC in Metric N with NaN and converted the column to numeric. Next, I cleaned up Metric Score by removing symbols like >, <, and %, and scaled the values to proportions.

Once both columns were cleaned, I calculated the Actual Count column to get the number of students based on the metric score and the total student count (Metric N). To handle missing values, I filled them with 0 and ensured the columns were formatted as integers.

print(attendance_df['Metric Score'].unique())

# Replace 'DS' with NaN in Metric N
attendance_df['Metric N'] = attendance_df['Metric N'].replace('DS', np.nan).astype(float)

# Clean and convert Metric Score
attendance_df['Metric Score'] = attendance_df['Metric Score'].str.replace(r'[=><%]', '', regex=True).astype(float)
attendance_df['Metric Score'] = (attendance_df['Metric Score'] / 100).round(2)

# Calculate the actual count
attendance_df['Actual Count'] = (attendance_df['Metric Score'] * attendance_df['Metric N']).round()

# Handle missing values
attendance_df['Actual Count'] = attendance_df['Actual Count'].fillna(0).astype(int)  # Fill missing with 0
['12.39' '8.58' '1.2' '88.33' '>=90%' '36.67' '54.8' '63.63' '47.32'
 '64.7' '73.91' '72.72' '17' '21.97' '20.39' '11.67' '76.75' '61.24'
 '30.55' '33.04' '4.94' '76.09' '75' '82.99' '1.19' '60.25' '66.66'
 '68.26' '70.53' '58.01' '67.88' '79' '84.17' '52.59' '72.86' '66.46'
 '6.5' '72.27' '87.65' '74.67' '81.92' '81.42' '93.44' '43.85' '61.71'
 '91.53' '91.33' '94.92' '80.3' '82.72' '86.86' '14.69' '61.16' '60.78'
 '61.02' '95.61' '97.7' '76.53' '87.03' '90.9' '84.44' '90.31' '91.49'
 '92.11' '71.32' '68.24' '74.73' '92.96' '97.08' '90.5' '77.17' '74.77'
 '92.9' '92.74' '93.28' '87.95' '90.23' '90.51' '90.95' '88.6' '89.9'
 '93.05' '93.79' '87.81' '91.23' '90.62' '91.74' '95.76' '92.25' '93.69'
 '92.98' '95.6' '85.81' '89.86' '95.72' '95.66' '95.96' '93.2' '93.78'
 '94' '89.02']

I printed the values of metric score in discicipline_df to see if there were any symbols. Since I noticed some, I stripped these values of the symbols.

print(discipline_df['Metric Score'].unique())

# Remove '=' and '<', then convert to numeric
discipline_df['Metric Score'] = discipline_df['Metric Score'].str.replace(r'[<=]', '', regex=True)
['<=1' '17' '22' '53' '2' '5' '11' '24' '12' '93' '178' '3' '57' '130' '4'
 '20' '10' '18' '50' '97' '6' '80' '135' '7' '86' '149' '16' '44' '30'
 '39' '49' '13' '26' '36' '82' '19' '28' '8' '46' '126' '15' '27' '35'
 '43' '54' '9' '55' '176' '262' '124' '32' '78' '94' '42' '66' '41' '14'
 '48' '21' '104' '77' '68' '109']

For grad_df, I focused on cleaning and standardizing the data to create the Actual Count column. I cleaned the 2020 ACGR column by removing symbols and converting the values into proportions. For the placeholder n<10, I replaced it with the median value of the column.

Next, I converted the Cohort Total column to numeric so it was ready for calculations. I used the clean 2020 ACGR and Cohort Total columns to calculate the Actual Count by multiplying the values. Finally, I filled any missing values in Actual Count with 0.

# Remove '>' and '<' and treat them as their boundary values
grad_df['2020 ACGR'] = (grad_df['2020 ACGR']
                        .str.replace(r'[=><%n]', '', regex=True)
                        .astype(float) / 100).round(2)

# Replace 'n<10' with a numeric value (e.g., median of the column)
median_value = grad_df.loc[grad_df['2020 ACGR'] != 'n<10', '2020 ACGR'].median()
grad_df['2020 ACGR'] = grad_df['2020 ACGR'].replace('n<10', median_value)

grad_df['2020 ACGR'] = pd.to_numeric(grad_df['2020 ACGR'], errors='coerce')

# Convert Cohort Total and Graduates to numeric
grad_df['Cohort Total'] = pd.to_numeric(grad_df['Cohort Total'], errors='coerce')

# Calculate the actual count based on the clean data
grad_df['Actual Count'] = (grad_df['2020 ACGR'] * grad_df['Cohort Total']).round()

# Handle missing values in 'Actual Count'
grad_df['Actual Count'] = grad_df['Actual Count'].fillna(0).astype(int)

I saved all the new processed datasets to the processed data folder.

# Save clean df to processed-data folder
processed_data_path = '/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/'

test_df_path = processed_data_path + 'test_df.csv'
attendance_df_path = processed_data_path + 'attendance_df.csv'
discipline_df_path = processed_data_path + 'discipline_df.csv'
grad_df_path = processed_data_path + 'grad_df.csv'
ward_df_path = processed_data_path + 'ward_df.csv'

test_df.to_csv(test_df_path, index=False)
attendance_df.to_csv(attendance_df_path, index=False)
discipline_df.to_csv(discipline_df_path, index=False)
grad_df.to_csv(grad_df_path, index=False)

Here I am loading in my socioeconomic datasets using pandas’ read_csv.

crime_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/crime_df.csv')
demo_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/demo_df.csv')
econ_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/economic_df.csv', header=0)
grocery_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/grocery_df.csv')
housing_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/housing_df.csv')
poverty_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/raw-data/poverty_df.csv')

To make it easier to work with ward specific tasks, I created a variable that contained a list of ward names, ward 1 through ward 8, so help me when mapping or aggregating by ward.

# List of ward names
ward_names = [f"Ward {i}" for i in range(1,9)]

I started cleaning poverty_df by renaming the columns to make them more descriptive and easier to work with. Specifically, I renamed Location to ward and Data to poverty_rate. Next, I filtered the DataFrame to include only rows where the year was 2020, as that is the year I focus on in my analysis.

I selected only the ward and poverty_rate columns from the filtered data to create a DataFrame containing only the essential information. Lastly, I saved the clean version as poverty_df.csv to my processed data folder.

print(poverty_df.columns)
print(poverty_df.head())
poverty_df.rename(columns={'Location': 'ward'}, inplace=True)
poverty_df.rename(columns={'Data': 'poverty_rate'}, inplace=True)

poverty_df = poverty_df[poverty_df['TimeFrame'] == 2020]
final_poverty_df = poverty_df[['ward', 'poverty_rate']]

poverty_file_path = processed_data_path + 'poverty_df.csv'
final_poverty_df.to_csv(poverty_file_path, index=False)
Index(['LocationType', 'Location', 'TimeFrame', 'DataFormat', 'Data'], dtype='object')
  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

For grocery_df, I started with selecting only the storename and ward columns and then renamed them for consistency and readability. I then saved this new, clean DataFrame as grocery_df to my processed data folder.

# Select and rename the columns
grocery_df = grocery_df[['STORENAME', 'WARD']]
grocery_df = grocery_df.rename(columns={"STORENAME": "STORE NAME"})

grocery_file_path = processed_data_path + 'grocery_df.csv'
grocery_df.to_csv(grocery_file_path, index=False)

For crime_df I made the ward column lowercase to keep the column name consistent across datasets and then formatted the values by add “ward” to make them more descriptive and aligned with my other datasets. (I do end up having to encode this).

Next, I filtered the DataFrame to include only the offense and ward columns since these are the columns I will need for my analysis. Lastly, I saved the clean dataset as crime_df.csv to the process data folder.

print(crime_df.columns)

# Rename column and format ward values
crime_df = crime_df.rename(columns={"WARD": "ward"})
crime_df['ward'] = "Ward " + crime_df['ward'].astype(str)

# Keep only offense and ward for final df
final_crime_df = crime_df[['OFFENSE', 'ward']]
print(final_crime_df.head)

crime_df_path = processed_data_path + 'crime_df.csv'
final_crime_df.to_csv(crime_df_path, index=False)
Index(['CCN', 'REPORT_DAT', 'SHIFT', 'METHOD', 'OFFENSE', 'BLOCK', 'XBLOCK',
       'YBLOCK', 'WARD', 'ANC', 'DISTRICT', 'PSA', 'NEIGHBORHOOD_CLUSTER',
       'BLOCK_GROUP', 'CENSUS_TRACT', 'VOTING_PRECINCT', 'LATITUDE',
       'LONGITUDE', 'BID', 'START_DATE', 'END_DATE', 'OBJECTID',
       'OCTO_RECORD_ID'],
      dtype='object')
<bound method NDFrame.head of                         OFFENSE    ward
0                  THEFT F/AUTO  Ward 3
1                   THEFT/OTHER  Ward 6
2                  THEFT F/AUTO  Ward 6
3                      HOMICIDE  Ward 6
4    ASSAULT W/DANGEROUS WEAPON  Ward 7
..                          ...     ...
995                 THEFT/OTHER  Ward 7
996                   SEX ABUSE  Ward 2
997                 THEFT/OTHER  Ward 1
998                 THEFT/OTHER  Ward 7
999                 THEFT/OTHER  Ward 8

[1000 rows x 2 columns]>

To clean demo_df, I normalized the column names by stripping extra whitespace, then converting them to lowercase. Then, I selected the columns that were most relevant to my analysis such as total population, Black population, and White population.

Next, I cleaned the ward names using regrex pattern to make sure they were formatted correctly for analysis. After this, I dropped the name column since I now have the ward column.

Finally, I saved the clean DataFrame as demo_df.csv to the processed data folder.

# Normalize column names by stripping whitespace and converting to lowercase
demo_df.columns = demo_df.columns.str.strip().str.lower()
print("normalized columns:", demo_df.columns)

demo_df = demo_df[[
    'name',
    'hispanic or latino and race:  total population',
    'hispanic or latino and race:  total population:  not hispanic or latino:  black or african american alone',
    'hispanic or latino and race:  total population:  not hispanic or latino:  white alone'
]].copy()

# Extract and clean ward names
demo_df['ward'] = demo_df['name'].str.extract(r'(Ward \d+)').squeeze()
print(demo_df['ward'])


demo_df.columns = ['Name', 'Total Population', 'Black Population', 'White Population', 'Ward']
# Drop name column and keep ward column
demo_df = demo_df.drop(columns=['Name'])
demo_df = demo_df[['Ward', 'Total Population', 'Black Population', 'White Population']]
crime_df = crime_df.rename(columns={"Ward": "ward"})


demo_file_path = processed_data_path + 'demo_df.csv'

demo_df.to_csv(demo_file_path, index=False)
normalized columns: Index(['name',
       'citizen, voting age population:  citizen, 18 and over population',
       'citizen, voting age population:  citizen, 18 and over population:  female',
       'citizen, voting age population:  citizen, 18 and over population:  male',
       'geographic identifier', 'gis identifier',
       'hispanic or latino and race:  total population',
       'hispanic or latino and race:  total population:  hispanic or latino (of any race)',
       'hispanic or latino and race:  total population:  hispanic or latino (of any race):  cuban',
       'hispanic or latino and race:  total population:  hispanic or latino (of any race):  mexican',
       ...
       'sex and age:  total population:  sex ratio (males per 100 females)',
       'sex and age:  total population:  under 18 years',
       'sex and age:  total population:  under 5 years', 'shape.area',
       'shape.len', 'total housing units', 'se_anno_cad_data', 'shape', 'x',
       'y'],
      dtype='object', length=101)
0    Ward 1
1    Ward 2
2    Ward 3
3    Ward 4
4    Ward 5
5    Ward 6
6    Ward 7
7    Ward 8
Name: ward, dtype: object

with econ_df, I started by searching for the column that had to do with median household income - I had a hard time locating this manually. Once I found it, I was able to select it, as well as the column with the wards. I then added a ward column using the list of wards I recreated earlier.

Next, I renamed the columns for clarity then I saved the new DataFrame as econ_df to the processed data folder.


# print(econ_df.head)
print(econ_df.columns)

for col in econ_df.columns:
    if "Median household income" in col:
        print(col)


# Extract relevant columns
econ_df = econ_df[[
    "Name", 
    "INCOME AND BENEFITS (IN 2022 INFLATION-ADJUSTED DOLLARS):  Total households:  Median household income (dollars)"
]]

econ_df['WARD'] = ward_names
econ_df = econ_df.iloc[:, 1:]


econ_df = econ_df.rename(columns={"WARD": "ward"})
econ_df = econ_df.rename(columns={'INCOME AND BENEFITS (IN 2022 INFLATION-ADJUSTED DOLLARS):  Total households:  Median household income (dollars)': 'median household income'})


econ_file_path = processed_data_path + 'econ_df.csv'

econ_df.to_csv(econ_file_path, index=False)
Index(['Name',
       'CLASS OF WORKER:  Civilian employed population 16 years and over',
       'CLASS OF WORKER:  Civilian employed population 16 years and over:  Government workers',
       'CLASS OF WORKER:  Civilian employed population 16 years and over:  Private wage and salary workers',
       'CLASS OF WORKER:  Civilian employed population 16 years and over:  Self-employed in own not incorporated business workers',
       'CLASS OF WORKER:  Civilian employed population 16 years and over:  Unpaid family workers',
       'COMMUTING TO WORK:  Workers 16 years and over',
       'COMMUTING TO WORK:  Workers 16 years and over:  Car, truck, or van -- carpooled',
       'COMMUTING TO WORK:  Workers 16 years and over:  Car, truck, or van -- drove alone',
       'COMMUTING TO WORK:  Workers 16 years and over:  Mean travel time to work (minutes)',
       ...
       'PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL:  All people:  Under 18 years:  Related children of the householder under 18 years',
       'PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL:  All people:  Under 18 years:  Related children of the householder under 18 years:  Related children of the householder 5 to 17 years',
       'PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL:  All people:  Under 18 years:  Related children of the householder under 18 years:  Related children of the householder under 5 years',
       'PERCENTAGE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL:  All people:  Unrelated individuals 15 years and over',
       'SHAPE.AREA', 'SHAPE.LEN', 'SE_ANNO_CAD_DATA', 'SHAPE', 'x', 'y'],
      dtype='object', length=147)
INCOME AND BENEFITS (IN 2022 INFLATION-ADJUSTED DOLLARS):  Total households:  Median household income (dollars)

For housing_df, I first had to identify the codings for the column names, which was a feat within itself. Once I was able to distingish the ones I needed, I put the names into a list and renamed them for clarity. I then used the ward list frome earlier to map each ward to its according economic information. Finally, I saved the clean housing_df to the processed data folder.

columns_to_keep = [
    'NAME', 'DP04_0089E', 'DP04_0134E'
]
housing_df = housing_df[columns_to_keep]

# Rename the columns
housing_df = housing_df.rename(columns={
    'DP04_0089E': 'Median_Home_Value',
    'DP04_0134E': 'Median_Gross_Rent'
})


housing_df['WARD'] = ward_names
housing_df = housing_df.iloc[:, 1:]

# Rename column and format ward values
housing_df = housing_df.rename(columns={"WARD": "ward"})

housing_file_path = processed_data_path + 'housing_df.csv'
housing_df.to_csv(housing_file_path, index=False)