import pandas as pd
import numpy as np
import osData Cleaning
Code
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_dataframesHere, 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)