Exploratory Data Analysis

Code

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from functools import reduce
from sklearn.preprocessing import StandardScaler
from scipy.stats import pearsonr
from sklearn.preprocessing import LabelEncoder
from sklearn.preprocessing import MinMaxScaler
from scipy.stats import pearsonr
from scipy.stats import f_oneway

Cleaning (Continued)

I finished my data cleaning process with multiple DataFrames, so I am reading them to start working with the cleaned data. I also set up my processed_data_path variable to make it easier to reference the folder where my processed data is stored.

attendance_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/attendance_df.csv')
crime_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/crime_df.csv')
demo_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/demo_df.csv')
discipline_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/discipline_df.csv')
econ_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/econ_df.csv')
grad_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/grad_df.csv')
grocery_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/grocery_df.csv')
housing_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/housing_df.csv')
poverty_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/poverty_df.csv')
test_df = pd.read_csv('/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/test_df.csv')

processed_data_path = '/Users/anilechoice/Documents/georgetown/5000/project-anilechoice00/data/processed-data/'

I put all my DataFrames in a list, then looped through to standardize all the column names. I stripped any extra spaces, converted the names to lowercase, and replaced underscores and colons with spaces. After that, I printed all the column names to ensure they looked the way I wanted.

# List of all your dataframes
dataframes = [attendance_df, crime_df, demo_df, discipline_df, econ_df, grad_df, grocery_df, housing_df, poverty_df, test_df]

# Standardize column names for all dataframes
for df in dataframes:
    df.columns = df.columns.str.strip().str.lower().str.replace('_', ' ').str.replace(':', '')

for i, df in enumerate(dataframes, start=1):
    print(f"DataFrame {i} columns:", df.columns)
DataFrame 1 columns: Index(['school name', 'student group', 'metric', 'metric score', 'metric n',
       'ward', 'actual count'],
      dtype='object')
DataFrame 2 columns: Index(['offense', 'ward'], dtype='object')
DataFrame 3 columns: Index(['ward', 'total population', 'black population', 'white population'], dtype='object')
DataFrame 4 columns: Index(['school name', 'student group', 'metric', 'metric score', 'metric n',
       'ward'],
      dtype='object')
DataFrame 5 columns: Index(['median household income', 'ward'], dtype='object')
DataFrame 6 columns: Index(['school name', 'graduates', 'cohort total', '2020 acgr', 'ward',
       'actual count'],
      dtype='object')
DataFrame 7 columns: Index(['store name', 'ward'], dtype='object')
DataFrame 8 columns: Index(['median home value', 'median gross rent', 'ward'], dtype='object')
DataFrame 9 columns: Index(['ward', 'poverty rate'], dtype='object')
DataFrame 10 columns: Index(['school name', 'student group', 'metric', 'metric score', 'metric n',
       'ward', 'actual count'],
      dtype='object')

I wanted to see which DataFrames had missing values, so I used isna().sum() to count them for each. I noticed a few missing values - some in columns that I decided I wouldn’t be using but some in columns that I will need, which I will address later.

print(attendance_df.isna().sum())
print(crime_df.isna().sum())
print(demo_df.isna().sum())
print(discipline_df.isna().sum())
print(econ_df.isna().sum())
print(grad_df.isna().sum())
print(grocery_df.isna().sum())
print(housing_df.isna().sum())
print(poverty_df.isna().sum())
print(test_df.isna().sum())
school name      0
student group    0
metric           0
metric score     0
metric n         8
ward             4
actual count     0
dtype: int64
offense    0
ward       0
dtype: int64
ward                0
total population    0
black population    0
white population    0
dtype: int64
school name       0
student group     0
metric            0
metric score      0
metric n          0
ward             14
dtype: int64
median household income    0
ward                       0
dtype: int64
school name     0
graduates       0
cohort total    0
2020 acgr       0
ward            5
actual count    0
dtype: int64
store name    0
ward          0
dtype: int64
median home value    0
median gross rent    0
ward                 0
dtype: int64
ward            0
poverty rate    0
dtype: int64
school name       0
student group     0
metric            0
metric score      0
metric n         22
ward              8
actual count      0
dtype: int64

I printed the rows in the graduation rate DataFrame where any values were missing so that I could look closer at the missing values. I noticed ‘State’ and ‘DCPS’ rows were missing ward values, and a few schools were missing the number of graduates.

# Find rows with missing values
missing_rows = grad_df[grad_df.isnull().any(axis=1)]

# Print the rows with missing values
print(missing_rows)
                                  school name graduates  cohort total  \
0                                       State      3405          4802   
1                                        DCPS      2180          3174   
22                                        PCS      1223          1597   
39  KIPP DC - College Preparatory Academy PCS       138           160   
40                  SEED PCS of Washington DC        28            35   

    2020 acgr ward  actual count  
0        0.71  NaN          3409  
1        0.69  NaN          2190  
22       0.77  NaN          1230  
39       0.86  NaN           138  
40       0.80  NaN            28  

I noticed the poverty rate DataFrame included a row for the District of Columbia, which I wasn’t planning to analyze. It was also inconsistent with my other DataFrames, as they don’t have a row for the entire District. I dropped it here to avoid any issues when merging the DataFrames later.

After dropping the row, I printed the head of the DataFrame to make sure it was removed.

# Print head of poverty_df
print(poverty_df.head())

# Drop the "District of Columbia" row in the original poverty_df
poverty_df = poverty_df[poverty_df['ward'] != 'District of Columbia']

# Verify the result
print(poverty_df.head())
                   ward  poverty rate
0  District of Columbia         0.079
1                Ward 1         0.063
2                Ward 2         0.043
3                Ward 3         0.042
4                Ward 4         0.091
     ward  poverty rate
1  Ward 1         0.063
2  Ward 2         0.043
3  Ward 3         0.042
4  Ward 4         0.091
5  Ward 5         0.102

I started by renaming columns in the attendance_df. discipline_df, test_df, and grad_df DataFrames to make the column names more descriptive and unique to their respective DataFrames =. For example, I added prefixes like ‘attendance,’ ‘discipline,’ or ‘test’ to distinguish similar metrics across the datasets.

Next, I filtered out rows where the ‘school name’ was ‘State,’ ‘DCPS,’ or ‘PCS,’ as these rows weren’t relevant to my analysis. I also removed a few schools, like Ballou STAY High School and Duke Ellington School of the Arts, which are the rows that I saw were missing values a couple of cells back.

attendance_df = attendance_df.rename(columns={
    'metric score': 'attendance metric score',
    'metric n': 'attendance metric n',
    'metric': 'attendance metric',
    'student group': 'attendance student group',
    'actual count': 'attendance count'
})
# Filter out rows where 'school name' is 'State', 'DCPS', or 'PCS'
filtered_grad = attendance_df[~attendance_df['school name'].isin(['State', 'DCPS', 'PCS', 'Ballou STAY High School', 'Washington Metropolitan High School', 'Benjamin Banneker High School', 'Duke Ellington School of the Arts', 'KIPP DC - Somerset College Preparatory'])]


discipline_df = discipline_df.rename(columns={
    'metric score': 'discipline metric score',
    'metric n': 'discipline metric n',
    'metric': 'discipline metric',
    'student group': 'discipline student group'
})
# Filter out rows where 'school name' is 'State', 'DCPS', or 'PCS' and schools with missing values
discipline_df = discipline_df[~discipline_df['school name'].isin(['State', 'DCPS', 'PCS', 'Ballou STAY High School', 'Washington Metropolitan High School', 'Benjamin Banneker High School', 'Duke Ellington School of the Arts', 'KIPP DC - Somerset College Preparatory'])]


test_df = test_df.rename(columns={
    'metric score': 'test metric score',
    'metric n': 'test metric n',
    'metric': 'test metric',
    'student group': 'test student group',
    'actual count': 'test count'
})
# Filter out rows where 'school name' is 'State', 'DCPS', or 'PCS' and schools with missing values
test_df = test_df[~test_df['school name'].isin(['State', 'DCPS', 'PCS', 'Ballou STAY High School', 'Washington Metropolitan High School', 'Benjamin Banneker High School', 'Duke Ellington School of the Arts', 'KIPP DC - Somerset College Preparatory'])]


grad_df = grad_df.rename(columns={
    '2020 acgr': 'graduate rate'
})
# Filter out rows where 'school name' is 'State', 'DCPS', or 'PCS' and schools with missing values
grad_df = grad_df[~grad_df['school name'].isin(['State', 'DCPS', 'PCS', 'Ballou STAY High School', 'Washington Metropolitan High School', 'Benjamin Banneker High School', 'Duke Ellington School of the Arts', 'KIPP DC - Somerset College Preparatory'])]

To prepare to merge the data for analysis, I filtered and transformed each DataFrame into a standardized format using the melt() function. This helped consolidate indicators so that I could compare across wards. I focused on “All Report Card Students” for the DataFrames since a lot of the other demographic groups did not have values for analysis. It was easier to analyze the schools as a whole instead of by demographic since all DataFrames had a row for all students.

Attendance Data: I filtered to focus on ’In-Seat Attendance”. Then, I melted the data to create columns for indicators and their schools, grouped by ward.

Discipline Data: I calculated the total discipline metric scores for each school in each ward using groupby(), then melted the summary to align it with the format of the attendance data.

Test Data: I filtered test_df to include only “AP/IB Participation” for all students, then melted it like the others.

Graduation Data: I selected the relevant columns, including graduation rates, school names, wards, and counts. Then, I melted the data to focus on graduation rates by ward.

Grocery Data: I counted the number of grocery stores in each ward using group by () and melted the results into the same structure as the others.

Crime: I calculated the total number of crimes in each ward and melted the data.

Finally, I printed the head of each melted DataFrame to ensure that the transformations worked as I intended. This process puts all the DataFrames in a consistent format so that they are ready to be merged and analyzed.

# filter the attendance df
filtered_attendance = attendance_df[
    (attendance_df['attendance metric'] == 'In-Seat Attendance') &
    (attendance_df['attendance student group'] == 'All Report Card Students')
]

melted_attendance = filtered_attendance.melt(
    id_vars=['ward'],
    value_vars='attendance metric score',
    var_name='indicator',
    value_name='score')

# sum the Metric Score for each School Name
discipline_summary = discipline_df.groupby(['school name', 'ward'], as_index=False)['discipline metric score'].sum()

# Melt the discipline_summary DataFrame
melted_discipline = discipline_summary.melt(
    id_vars=['ward'],  # Columns to keep
    value_vars=['discipline metric score'],  # Columns to melt
    var_name='indicator',  # Name of the new 'indicator' column
    value_name='score'  # Name of the new 'value' column
)



# Filter for 'All Report Card Students' and 'AP/IB Participation'
filtered_test = test_df[
    (test_df['test student group'] == 'All Report Card Students') &
    (test_df['test metric'] == 'AP/IB Participation')
]

melted_test = filtered_test.melt(
    id_vars=['ward'],
    value_vars=['test metric score'],
    var_name='indicator',
    value_name='score'
)



filtered_grad = grad_df[['graduate rate','school name', 'ward', 'actual count']]

# Display the cleaned DataFrame
print(filtered_grad.head())


melted_grad = filtered_grad.melt(
    id_vars=['ward'],
    value_vars=['graduate rate'],
    var_name='indicator',
    value_name='score'
)

# Count the number of grocery stores by ward
grocery_counts = grocery_df.groupby('ward').size().reset_index(name='grocery store count')

melted_grocery = grocery_counts.melt(
    id_vars=['ward'],
    value_vars=['grocery store count'],
    var_name='indicator',
    value_name='score'
)

# Count the number of crimes by ward
crime_counts = crime_df.groupby('ward').size().reset_index(name='crime count')

melted_crime = crime_counts.melt(
    id_vars=['ward'],
    value_vars=['crime count'],
    var_name='indicator',
    value_name='score'
)

# Display the results
print("Melted Attendance:")
print(melted_attendance.head())
print("\nMelted Discipline:")
print(melted_discipline.head())
print("\nMelted Test:")
print(melted_test.head())
print("\nMelted Grad Rate:")
print(melted_grad.head())
print("\nMelted Grocery Count:")
print(melted_grocery.head())
print("\nMelted Crime Count:")
print(melted_crime.head())
   graduate rate                                        school name    ward  \
2           0.69                              Roosevelt High School  Ward 4   
3           0.79                           H.D. Woodson High School  Ward 7   
4           0.80                                 Dunbar High School  Ward 5   
5           0.99                   School Without Walls High School  Ward 2   
6           0.93  Phelps Architecture, Construction and Engineer...  Ward 5   

   actual count  
2           163  
3           107  
4           136  
5           147  
6            66  
Melted Attendance:
     ward                indicator  score
0  Ward 8  attendance metric score   0.61
1  Ward 8  attendance metric score   0.61
2  Ward 2  attendance metric score   0.96
3  Ward 1  attendance metric score   0.77
4  Ward 1  attendance metric score   0.87

Melted Discipline:
     ward                indicator  score
0  Ward 8  discipline metric score     96
1  Ward 2  discipline metric score     47
2  Ward 8  discipline metric score      9
3  Ward 4  discipline metric score    105
4  Ward 1  discipline metric score    288

Melted Test:
     ward          indicator  score
0  Ward 8  test metric score   0.23
1  Ward 8  test metric score   0.19
2  Ward 1  test metric score   0.44
3  Ward 1  test metric score   0.98
4  Ward 4  test metric score   0.38

Melted Grad Rate:
     ward      indicator  score
0  Ward 4  graduate rate   0.69
1  Ward 7  graduate rate   0.79
2  Ward 5  graduate rate   0.80
3  Ward 2  graduate rate   0.99
4  Ward 5  graduate rate   0.93

Melted Grocery Count:
     ward            indicator  score
0  Ward 1  grocery store count     14
1  Ward 2  grocery store count     17
2  Ward 3  grocery store count     16
3  Ward 4  grocery store count     12
4  Ward 5  grocery store count     11

Melted Crime Count:
     ward    indicator  score
0  Ward 1  crime count    105
1  Ward 2  crime count    183
2  Ward 3  crime count     68
3  Ward 4  crime count     92
4  Ward 5  crime count    173

Since these DataFrames were straightforward and included only the rows I needed, all I had to do was melt them. Like in the previous cell, I used the ‘ward’ column as the identifier. Each data frame was melted to focus on its specific indicator.

Demographics Data: Total population, Black population, and White population were included.

Economic Data: Focused on median household income.

Housing: Focused on median home value and gross median rent

Poverty Data: Focused on poverty data.

Lastly, I printed the heads of the melted DataFrames to confirm everything looked correct.

melted_demo = demo_df.melt(
    id_vars=['ward'],
    value_vars=['total population', 'black population', 'white population'],
    var_name='indicator',
    value_name='score'
)

melted_econ = econ_df.melt(
    id_vars=['ward'],
    value_vars='median household income',
    var_name='indicator',
    value_name='score'
)

melted_housing = housing_df.melt(
    id_vars=['ward'],
    value_vars=['median home value', 'median gross rent'],
    var_name='indicator',
    value_name='score'
)

melted_poverty = poverty_df.melt(
    id_vars=['ward'],
    value_vars=['poverty rate'],
    var_name='indicator',
    value_name='score'
)

# Display the results
print("Melted Demographic:")
print(melted_demo.head())
print("\nMelted Economic:")
print(melted_econ.head())
print("\nMelted Housing:")
print(melted_housing.head())
print("\nMelted Poverty:")
print(melted_poverty.head())
Melted Demographic:
     ward         indicator  score
0  Ward 1  total population  79448
1  Ward 2  total population  77904
2  Ward 3  total population  79851
3  Ward 4  total population  86071
4  Ward 5  total population  89628

Melted Economic:
     ward                indicator   score
0  Ward 1  median household income  118395
1  Ward 2  median household income  128048
2  Ward 3  median household income  141393
3  Ward 4  median household income  115068
4  Ward 5  median household income   92398

Melted Housing:
     ward          indicator    score
0  Ward 1  median home value   783300
1  Ward 2  median home value   741700
2  Ward 3  median home value  1118400
3  Ward 4  median home value   792700
4  Ward 5  median home value   671600

Melted Poverty:
     ward     indicator  score
0  Ward 1  poverty rate  0.063
1  Ward 2  poverty rate  0.043
2  Ward 3  poverty rate  0.042
3  Ward 4  poverty rate  0.091
4  Ward 5  poverty rate  0.102

To accurately represent the scores across wards, I calculated weighted averages for attendance, test scores, and graduation rates. This approach ensures that scores are weighted by the number of students or counts rather than treating each school equally regardless of size.

Attendance: I grouped the data by ward and calculated a weighted average of the attendance metric score using the attendance count as weights.

Test Scores: I calculated the weighted average of the test metric scores using the test count as weights.

Graduation Rates: For graduation rates, I weighted the scores by the actual number of graduates in each school.

For each calculation, I used groupby(‘ward’) and a lambda function to compute the weighted sum for each group. The results were then reset into a clean DataFrame with descriptive column names like ‘weighted attendance score,’ ‘weighted test score’, and ‘weighted grad score.’

# Weighted average for attendance
attendance_aggregated = (
    filtered_attendance
    .groupby('ward')
    .apply(lambda group: 
        (group['attendance metric score'] * group['attendance count']).sum() / group['attendance count'].sum()
    )
    .reset_index(name='weighted attendance score')
)

# Weighted average for test scores
test_aggregated = (
    filtered_test
    .groupby('ward')
    .apply(lambda group: 
        (group['test metric score'] * group['test count']).sum() / group['test count'].sum()
    )
    .reset_index(name='weighted test score')
)

grad_aggregated = (
    filtered_grad
    .groupby('ward')
    .apply(lambda group:
        (group['graduate rate'] * group['actual count']).sum() / group['actual count'].sum()
    )
    .reset_index(name='weighted grad score')
)
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/2996669866.py:5: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.
  .apply(lambda group:
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/2996669866.py:15: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.
  .apply(lambda group:
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/2996669866.py:24: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.
  .apply(lambda group:

I merged the aggregated DataFrames for attendance, test scores, and graduation rates into a single DataFrame, aligning them by work. This combined the weighted scores for each indicator into one place.

To prepare the merged DataFrame, I used the melt() function. This transformed the wide format (with separate columns for each indicator) into a long format, with columns for ‘ward,’ ‘indicator,’ and ‘score.’ The long format makes it easier to work with the data for comparison and plotting.

Finally, I printed the head of the merged and melted DataFrames to confirm everything worked as I intended.

# Merge attendance and test aggregated data
aggregated_df = pd.merge(attendance_aggregated, test_aggregated, on='ward', how='outer')

# Merge the result with graduation aggregated data
aggregated_df = pd.merge(aggregated_df, grad_aggregated, on='ward', how='outer')

print(aggregated_df.head())

# Melt the merged DataFrame
aggregated_df = pd.melt(
    aggregated_df,
    id_vars=['ward'],  # Columns to keep
    var_name='indicator',  # Name for the new column that holds variable names
    value_name='score'  # Name for the new column that holds values
)

print(aggregated_df.head())
     ward  weighted attendance score  weighted test score  weighted grad score
0  Ward 1                   0.846135             0.798017             0.724455
1  Ward 2                   0.938432             0.982184             0.980154
2  Ward 3                   0.880000             0.820000             0.870000
3  Ward 4                   0.883593             0.611682             0.793011
4  Ward 5                   0.856182             0.845709             0.862886
     ward                  indicator     score
0  Ward 1  weighted attendance score  0.846135
1  Ward 2  weighted attendance score  0.938432
2  Ward 3  weighted attendance score  0.880000
3  Ward 4  weighted attendance score  0.883593
4  Ward 5  weighted attendance score  0.856182

I combined the melted DataFrames (attendance, discipline, grocery, crime, demographics, economic, housing, and poverty data) into a single list to bring all the data together. I concatenated them into one DataFrame using pd.concat().

Next, I created a pivot table (wardlevel_cat_df) to organize the data at the ward level. This table uses ‘ward’ as the index, ‘indicator’ as columns, and ‘score’ as the values which structured the data in a way that lets me see all the indicators for each ward in a clean and organized format.

Finally, I printed the resulting pivot table.

final_combined_df = [
    aggregated_df,
    melted_discipline,
    melted_grocery,
    melted_crime,
    melted_demo,
    melted_econ,
    melted_housing,
    melted_poverty   
]

cat_df = pd.concat(final_combined_df, ignore_index=True)

wardlevel_cat_df = cat_df.pivot_table(
    index='ward', 
    columns='indicator', 
    values='score'
).reset_index()

print(wardlevel_cat_df)
indicator    ward  black population  crime count  discipline metric score  \
0          Ward 1           18058.0        105.0               162.250000   
1          Ward 2            8458.0        183.0                39.500000   
2          Ward 3            6599.0         68.0               278.000000   
3          Ward 4           40887.0         92.0               104.428571   
4          Ward 5           52894.0        173.0               137.333333   
5          Ward 6           18470.0        134.0                41.000000   
6          Ward 7           74989.0        129.0               118.166667   
7          Ward 8           71525.0        116.0                55.200000   

indicator  grocery store count  median gross rent  median home value  \
0                         14.0             2003.0           783300.0   
1                         17.0             2329.0           741700.0   
2                         16.0             2079.0          1118400.0   
3                         12.0             1560.0           792700.0   
4                         11.0             1608.0           671600.0   
5                         12.0             2392.0           848000.0   
6                          4.0             1190.0           449000.0   
7                          4.0             1288.0           392700.0   

indicator  median household income  poverty rate  total population  \
0                         118395.0         0.063           79448.0   
1                         128048.0         0.043           77904.0   
2                         141393.0         0.042           79851.0   
3                         115068.0         0.091           86071.0   
4                          92398.0         0.102           89628.0   
5                         133590.0         0.069           80772.0   
6                          62243.0         0.130           89870.0   
7                          48609.0         0.170           87043.0   

indicator  weighted attendance score  weighted grad score  \
0                           0.846135             0.724455   
1                           0.938432             0.980154   
2                           0.880000             0.870000   
3                           0.883593             0.793011   
4                           0.856182             0.862886   
5                           0.900000             0.677576   
6                           0.794396             0.798904   
7                           0.775443             0.692264   

indicator  weighted test score  white population  
0                     0.798017           36131.0  
1                     0.982184           47709.0  
2                     0.820000           55750.0  
3                     0.611682           21551.0  
4                     0.845709           21057.0  
5                     0.180000           47680.0  
6                     0.545874            5987.0  
7                     0.287179            7865.0  

I set the file path for the wardlevel_cat_df DataFrame, and then I used .to_csv() to save it in my processed data folder for future analysis. This DataFrame will serve as the DataFrame that still contains the categorical values and is not normalized.

wardlevel_cat_path = processed_data_path + 'wardlevel_cat_df.csv'
wardlevel_cat_df.to_csv(wardlevel_cat_path, index=False)

I decided to create another DataFrame with normalized indicators, and I also encoded the wards numerically.

Normalization: I used the StandardScaler to normalize the scores for specific indicators, such as grocery store counts, crime counts, median household income, and weighted scores for attendance, tests, and graduation rates. I normalized these scores to ensure all indicators are on the same scale, essential for clustering and machine learning.

I applied the normalization separately for each indicator using a groupby() operation on the ‘indicator’ column. I did this so that each was scaled independently, and the normalized scores were added as a new column called ‘normalized score.’

Encoded Wards: I numerically encoded the ‘ward’ column using the LabelEncoder in order to assign a unique integer to each ward. This is another essential step for algorithms that require numeric inputs.

Finally, I printed the head of the resulting DataFrame to check that the normalization and encoding were correctly applied.

# Initialize Min-Max Scaler
scaler = StandardScaler()

final_df = pd.concat(final_combined_df, ignore_index=True)

# Apply normalization to each indicator separately
def normalize_group(group):
    group['normalized score'] = scaler.fit_transform(group[['score']]).flatten()
    return group

final_df = final_df.groupby('indicator', group_keys=False).apply(normalize_group)


# Encode ward numerically
label_encoder = LabelEncoder()
final_df['ward encoded'] = label_encoder.fit_transform(final_df['ward'])

print(final_df.head())
     ward                  indicator     score  normalized score  ward encoded
0  Ward 1  weighted attendance score  0.846135         -0.260389             0
1  Ward 2  weighted attendance score  0.938432          1.568955             1
2  Ward 3  weighted attendance score  0.880000          0.410822             2
3  Ward 4  weighted attendance score  0.883593          0.482027             3
4  Ward 5  weighted attendance score  0.856182         -0.061260             4
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/2675682030.py:11: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.
  final_df = final_df.groupby('indicator', group_keys=False).apply(normalize_group)

To organize the normalized data, I created a pivot table (wardlevel_df), which uses the encoded ward values (ward encoded) as the index and the indicators as columns, with their normalized scores as the values - this structure provides a clean, ward-level view of all the indicators, making it easier to analyze relationships and patterns across wards.

Then, I printed the wardlevel_df to confirm that the pivot table was created successfully.

wardlevel_df = final_df.pivot_table(
    index='ward encoded', 
    columns='indicator', 
    values='normalized score'
).reset_index()

print(wardlevel_df)
indicator  ward encoded  black population  crime count  \
0                     0         -0.713111    -0.549857   
1                     1         -1.084623     1.594586   
2                     2         -1.156565    -1.567094   
3                     3          0.170354    -0.907265   
4                     4          0.635016     1.319658   
5                     5         -0.697167     0.247436   
6                     6          1.490075     0.109971   
7                     7          1.356021    -0.247436   

indicator  discipline metric score  grocery store count  median gross rent  \
0                         0.618471             0.597438           0.458071   
1                        -0.832808             1.249189           1.216580   
2                         1.986988             1.031938           0.634902   
3                        -0.065155             0.162938          -0.572662   
4                         0.323880            -0.054313          -0.460980   
5                        -0.815074             0.162938           1.363162   
6                         0.097271            -1.575064          -1.433545   
7                        -0.647186            -1.575064          -1.205528   

indicator  median home value  median household income  poverty rate  \
0                   0.273488                 0.421431     -0.619639   
1                   0.079422                 0.724407     -1.100912   
2                   1.836742                 1.143264     -1.124976   
3                   0.317339                 0.317007      0.054143   
4                  -0.247597                -0.394532      0.318844   
5                   0.575316                 0.898353     -0.475257   
6                  -1.286034                -1.341001      0.992626   
7                  -1.548676                -1.768928      1.955172   

indicator  total population  weighted attendance score  weighted grad score  \
0                 -0.963581                  -0.260389            -0.786304   
1                 -1.303614                   1.568955             1.878423   
2                 -0.874829                   0.410822             0.730472   
3                  0.494991                   0.482027            -0.071855   
4                  1.278343                  -0.061260             0.656333   
5                 -0.671999                   0.807226            -1.274848   
6                  1.331638                  -1.285860            -0.010446   
7                  0.709053                  -1.661521            -1.121775   

indicator  weighted test score  white population  
0                     0.619341          0.317723  
1                     1.314056          0.967106  
2                     0.702267          1.418107  
3                    -0.083549         -0.500036  
4                     0.799246         -0.527743  
5                    -1.711937          0.965480  
6                    -0.331788         -1.372985  
7                    -1.307635         -1.267652  

I again used the processed data path in order to save the ward level DataFrame to my processed data folder.

wardlevel_file_path = processed_data_path + 'wardlevel_df.csv'
wardlevel_df.to_csv(wardlevel_file_path, index=False)

I started by selecting the relevant columns (school name, scores, and wards) from each filtered DataFrame for attendance, test scores, graduation rates, and discipline metrics to analyze student success indicators at the school level. This gave me the key information necessary for analysis.

Next, I merged these DataFrames into a single student_success_df using a list of the selected DataFrames and the reduce() function. The merge was performed on ‘school name’ and ‘ward’ to align the data across schools.

After merging, I dropped rows with missing values (NaN) to ensure the final DataFrame was clean and ready for analysis. Finally, I printed the DataFrame’s info and previewed the first few rows to verify that the merge and cleaning steps worked as I intended.

# print(filtered_attendance.columns.unique)
ml_attendance = filtered_attendance[['school name','attendance metric score','ward']]
# print(ml_attendance)

# print(filtered_test.columns.unique)
ml_test = filtered_test[['school name', 'test metric score', 'ward']]
# print(ml_test)

# print(filtered_grad.columns.unique)
ml_grad = filtered_grad[['school name', 'graduate rate', 'ward']]
# print(ml_grad)

print(discipline_summary.columns.unique)
ml_discipline = discipline_summary[['school name', 'discipline metric score', 'ward']]
# print(ml_discipline)


# List of all student indicator DataFrames
dfs_to_merge = [ml_attendance, ml_test, ml_grad, ml_discipline]

# Merge all DataFrames
student_success_df = reduce(lambda left, right: pd.merge(left, right, on=['school name', 'ward'], how='outer'), dfs_to_merge)

# print(student_success_df.head())

# Drop rows with NaN values
student_success_df = student_success_df.dropna()

# Verify the result
print(student_success_df.info())
print(student_success_df.head())
<bound method Index.unique of Index(['school name', 'ward', 'discipline metric score'], dtype='object')>
<class 'pandas.core.frame.DataFrame'>
Index: 28 entries, 0 to 36
Data columns (total 6 columns):
 #   Column                   Non-Null Count  Dtype  
---  ------                   --------------  -----  
 0   school name              28 non-null     object 
 1   attendance metric score  28 non-null     float64
 2   ward                     28 non-null     object 
 3   test metric score        28 non-null     float64
 4   graduate rate            28 non-null     float64
 5   discipline metric score  28 non-null     float64
dtypes: float64(4), object(2)
memory usage: 1.5+ KB
None
                      school name  attendance metric score    ward  \
0           Anacostia High School                     0.61  Ward 8   
1                    BASIS DC PCS                     0.96  Ward 2   
2              Ballou High School                     0.61  Ward 8   
4  Capital City PCS - High School                     0.91  Ward 4   
5        Cardozo Education Campus                     0.77  Ward 1   

   test metric score  graduate rate  discipline metric score  
0               0.23           0.62                     96.0  
1               0.95           0.95                     47.0  
2               0.19           0.62                      9.0  
4               0.47           0.89                    105.0  
5               0.44           0.64                    288.0  

I calculated the number of unique grocery stores and the total number of crimes for each ward:

Grocery Store Counts: - I grouped the grocery_df by ‘ward’ and used .nunique() on the ‘store name’ column to count the unique stores in each ward. - Then, I reset the index to turn the result into a clean DataFrame. - To make the column name more descriptive, I renamed ‘store name’ to ‘grocery store count.’

Crime Counts: - I grouped the crime_df by ‘ward’ and used .count() on the ‘offense’ column to get the total number of crimes reported in each ward. - I reset the index and renamed the ‘offense’ column to ‘crime count’ for clarity.

grocery_store_counts = grocery_df.groupby('ward')['store name'].nunique().reset_index()

print(grocery_store_counts.columns)

grocery_store_counts.rename(columns={'store name': 'grocery store count'}, inplace=True)


crime_counts = crime_df.groupby('ward')['offense'].count().reset_index()

crime_counts.rename(columns={'offense': 'crime count'}, inplace=True)
Index(['ward', 'store name'], dtype='object')

Here, I printed the DataFrames I just created to make sure they were correct.

print(grocery_store_counts)
print(crime_counts)
     ward  grocery store count
0  Ward 1                   12
1  Ward 2                   12
2  Ward 3                   11
3  Ward 4                   10
4  Ward 5                   11
5  Ward 6                    9
6  Ward 7                    3
7  Ward 8                    4
     ward  crime count
0  Ward 1          105
1  Ward 2          183
2  Ward 3           68
3  Ward 4           92
4  Ward 5          173
5  Ward 6          134
6  Ward 7          129
7  Ward 8          116

I merged the socioeconomic DataFrames to have a single dataset with all the indicators aligned by ward. I started with demo_df and merged it with econ_df on the ‘ward’ column. Then, I added housing_df and poverty_df, and finally, grocery_store_counts and crime_counts.

The final socioeconomic_df contains each ward’s combined socioeconomic indicators (ie, demographics, economic indicators, housing, poverty rates, grocery store counts, and crime counts) into one DataFrame. Lastly, I printed the Dataframe to confirm all the columns were included and the data was merged correctly.

# Merging socioeconomic dataframes
socioeconomic_df = demo_df.merge(econ_df, on='ward')
socioeconomic_df = socioeconomic_df.merge(housing_df, on='ward')
socioeconomic_df = socioeconomic_df.merge(poverty_df, on='ward')
socioeconomic_df = socioeconomic_df.merge(grocery_store_counts, on='ward')
socioeconomic_df = socioeconomic_df.merge(crime_counts, on='ward')

print(socioeconomic_df)
     ward  total population  black population  white population  \
0  Ward 1             79448             18058             36131   
1  Ward 2             77904              8458             47709   
2  Ward 3             79851              6599             55750   
3  Ward 4             86071             40887             21551   
4  Ward 5             89628             52894             21057   
5  Ward 6             80772             18470             47680   
6  Ward 7             89870             74989              5987   
7  Ward 8             87043             71525              7865   

   median household income  median home value  median gross rent  \
0                   118395             783300               2003   
1                   128048             741700               2329   
2                   141393            1118400               2079   
3                   115068             792700               1560   
4                    92398             671600               1608   
5                   133590             848000               2392   
6                    62243             449000               1190   
7                    48609             392700               1288   

   poverty rate  grocery store count  crime count  
0         0.063                   12          105  
1         0.043                   12          183  
2         0.042                   11           68  
3         0.091                   10           92  
4         0.102                   11          173  
5         0.069                    9          134  
6         0.130                    3          129  
7         0.170                    4          116  

Finally, I merged my student success DataFrame with the socioeconomic DataFrame on ‘ward.’ I now have a dataset organized at the school level while still including wards so that I can still group that way when necessary.

I printed the head of the final DataFrame to confirm that the merge worked as expected and that all the key indicators were included.

# Merge the student success data with socioeconomic data on 'ward'
final_df = student_success_df.merge(socioeconomic_df, on='ward')

print(final_df.head())
                      school name  attendance metric score    ward  \
0           Anacostia High School                     0.61  Ward 8   
1                    BASIS DC PCS                     0.96  Ward 2   
2              Ballou High School                     0.61  Ward 8   
3  Capital City PCS - High School                     0.91  Ward 4   
4        Cardozo Education Campus                     0.77  Ward 1   

   test metric score  graduate rate  discipline metric score  \
0               0.23           0.62                     96.0   
1               0.95           0.95                     47.0   
2               0.19           0.62                      9.0   
3               0.47           0.89                    105.0   
4               0.44           0.64                    288.0   

   total population  black population  white population  \
0             87043             71525              7865   
1             77904              8458             47709   
2             87043             71525              7865   
3             86071             40887             21551   
4             79448             18058             36131   

   median household income  median home value  median gross rent  \
0                    48609             392700               1288   
1                   128048             741700               2329   
2                    48609             392700               1288   
3                   115068             792700               1560   
4                   118395             783300               2003   

   poverty rate  grocery store count  crime count  
0         0.170                    4          116  
1         0.043                   12          183  
2         0.170                    4          116  
3         0.091                   10           92  
4         0.063                   12          105  

EDA

Here, I plotted a graph to look at the relationship between graduation rates of the different schools and the median household income of their respective wards.

Interpretations:

Positive Correlation:

Higher median household incomes are generally associated with higher graduation rates.

Ward Differences:

Wards like Ward 8 and 7 have lower graduation rates and lower median household incomes which reflect potential socioeconomic disparities.

In contrast, Ward 2 and Ward 3 cluster at the higher end of both graduation rate and median household income.

The clustering of points suggests distinct groupings based on ‘ward.’

sns.lmplot(data=final_df, 
           x='graduate rate', 
           y='median household income', 
           hue='ward')
plt.title("Graduation Rate vs. Median Houshold Income by Ward")
plt.show()

Similarly to the previous DataFrame, I wanted to normalize the data for consistent scaling across all indicators. This time, I was normalizing entire columns.

I created a list of the columns I wanted to normalize, which included all the indicators. Then, I used StandardScaler to apply normalization across all these columns in the final DataFrame.

I finished this by printing the head of the DataFame to ensure everything was normalized correctly.

# Initialize Min-Max Scaler
scaler = StandardScaler()

# List of columns to normalize
columns_to_normalize = [
    'grocery store count', 
    'crime count', 
    'median household income', 
    'median home value', 
    'median gross rent', 
    'discipline metric score',
    'total population',
    'black population',
    'white population',
    'attendance metric score',
    'test metric score',
    'graduate rate',
    'discipline metric score'
]

# Apply normalization to each column
final_df[columns_to_normalize] = scaler.fit_transform(final_df[columns_to_normalize])


print(final_df.head())
                      school name  attendance metric score    ward  \
0           Anacostia High School                -2.402762  Ward 8   
1                    BASIS DC PCS                 1.116979  Ward 2   
2              Ballou High School                -2.402762  Ward 8   
3  Capital City PCS - High School                 0.614159  Ward 4   
4        Cardozo Education Campus                -0.793738  Ward 1   

   test metric score  graduate rate  discipline metric score  \
0          -1.071328      -1.610130                -0.234854   
1           1.460674       1.234536                -0.800150   
2          -1.211995      -1.610130                -1.238544   
3          -0.227328       0.717324                -0.131024   
4          -0.332828      -1.437726                 1.980187   

   total population  black population  white population  \
0          0.326186          1.034757         -0.954451   
1         -1.938591         -1.667535          1.766317   
2          0.326186          1.034757         -0.954451   
3          0.085310         -0.278019         -0.019895   
4         -1.555965         -1.256195          0.975707   

   median household income  median home value  median gross rent  \
0                -1.460012          -1.365572          -0.860898   
1                 1.128581           0.475564           1.985455   
2                -1.460012          -1.365572          -0.860898   
3                 0.705615           0.744613          -0.117182   
4                 0.814029           0.695023           1.094090   

   poverty rate  grocery store count  crime count  
0         0.170            -1.168424    -0.219525  
1         0.043             1.068558     1.881640  
2         0.170            -1.168424    -0.219525  
3         0.091             0.509313    -0.972181  
4         0.063             1.068558    -0.564492  

Next, I printed a correlation matrix for the DataFrame with schools.

Strong Positive Correlations:

Median Household Income has strong positive correlations with: - Median Home Value (0.97) - Median Gross Rent (0.84) These findings imply that wealthier wards tend to have high home values and rent costs, which could have significant implications for educational resources and opportunities. The strong correlation (0.93) between the Black Population and Poverty Rate highlights systemic disparities that need to be addressed for a more equitable education system.

Strong Negative Correlations:

The high negative correlation between the White and Black populations (0.97) reflects the racial divide across wards. This could indicate that certain wards are more racially homogenous, which may have implications for the diversity and inclusivity of the educational environment. Poverty rate shares a negative correlation with wealth indicators; Median Household Income (-0.95), Median Home Value (-0.90), and Median Gross Rent (-0.85).

Education Metrics:

Graduation rate correlates positively with the following: - Attendance metric Score (0.63) - Test Metric Score (0.59) - This indicates that higher attendance and test scores are associated with higher graduation rates. - Poverty rate has a moderate negative correlation with Graduation Rate (-0.45), suggesting that poverty may impact educational outcomes.

Grocery Store & Crime Counts:

Grocery Store Count has a moderate positive correlation with Test Metric Score (0.57) and Attendance Metric Score (0.39). This might reflect better resources in wards with more grocery stores, as those wards also have higher median household income values. While Crime Count did not show any substantially strong correlations with other indicators, it did have a minor positive correlation with Test Metric Score (0.27). This suggests that in areas with higher crime rates, there may be a slight negative impact on test scores, which could be due to factors such as safety concerns affecting student attendance and concentration.

Key Takeaway:

The matrix highlights significant relationships between socioeconomic factors and educational outcomes. This reinforces the interconnectedness of social and economic disparities with student success indicators. I also used this correlation matrix to identify potential issues with collinearity. Highly correlated features can introduce redundancy in machine learning models, so I dropped those columns to avoid this.

numeric_columns = final_df.select_dtypes(include=['float64', 'int64']).columns
numeric_df = final_df[numeric_columns]

plt.figure(figsize=(12, 8))
sns.heatmap(numeric_df.corr(), annot=True, fmt=".2f", cmap="coolwarm")
plt.title("Correlation Matrix")
plt.show()

Based on the matrix, I selected the following features to drop: - Total Population: A high correlation exists between the Black population and the White population columns. - Median Home Value and Gross Rent: Both strongly correlated with Median Homehold Income. - Crime Count: Weak correlations with most indicators, contributing minimal unique information.

I dropped these columns in the cell to create a refined DataFrame (refined_df) for machine learning.

# Features to drop
features_to_drop = ['total population', 'median home value', 'median gross rent', 'crime count']

refined_df = final_df.drop(columns=features_to_drop)

print(refined_df.head())
                      school name  attendance metric score    ward  \
0           Anacostia High School                -2.402762  Ward 8   
1                    BASIS DC PCS                 1.116979  Ward 2   
2              Ballou High School                -2.402762  Ward 8   
3  Capital City PCS - High School                 0.614159  Ward 4   
4        Cardozo Education Campus                -0.793738  Ward 1   

   test metric score  graduate rate  discipline metric score  \
0          -1.071328      -1.610130                -0.234854   
1           1.460674       1.234536                -0.800150   
2          -1.211995      -1.610130                -1.238544   
3          -0.227328       0.717324                -0.131024   
4          -0.332828      -1.437726                 1.980187   

   black population  white population  median household income  poverty rate  \
0          1.034757         -0.954451                -1.460012         0.170   
1         -1.667535          1.766317                 1.128581         0.043   
2          1.034757         -0.954451                -1.460012         0.170   
3         -0.278019         -0.019895                 0.705615         0.091   
4         -1.256195          0.975707                 0.814029         0.063   

   grocery store count  
0            -1.168424  
1             1.068558  
2            -1.168424  
3             0.509313  
4             1.068558  

I then saved final_df to the processed data folder, which I’ll be using for my supervised and unsupervised learning.

final_df_path = processed_data_path + 'ml_df.csv'
refined_df.to_csv(final_df_path, index=False)

The following bar chart shows the number of schools in each ward. I also decided to show the proportion of the Black population per ward. First, I grouped grad_df by ward and calculated the average black proportion for each ward. This was so I could assign color to each bar based on the mean proportion.

‘mcolor.Normalize’ scales the Black proportion values to a range between 0 and 1 for the colormap since colormap requires values between 0 and 1. I then assigned a color from the coolwarm gradient based on the normalized value. Next, I created a dictionary where each ward is mapped to its corresponding color based on the Black population proportion. This is to assign a specific color to each ward in the chart.

‘bar_color’ counts the number of schools in each ward, sorted so that it ascends for wards 1 to 8, and then maps the ward names to their corresponding color using the ward_color_map. This ensures each bar in the chart is colored based on its ward’s Black population proportion.

Finally, I plotted the bar chart to show the frequency of schools in each ward, and color=bar_colors is what colors each bar based on the bar_color list.

I also added a legend so that we can see what each color is indicative of.

School Distribution:
  • The color gradient indicates the proportion of the Black population, with blue shades representing lower proportions and red shades representing higher proportions.
  • Wards 7 and 8 have high Black population proportions and higher numbers of schools.
Ward-Specific Patterns:
  • Ward 4 has the highest school count but with a relatively neutral color, indicating a balanced demographic. Ward 5 is also close to neutral but leans more red.
  • Wards 1, 2, 3, and 6 have fewer schools and lower Black population proportions, as represented by the blue colors.
import matplotlib.cm as cm
import matplotlib.colors as mcolors

# Calculate Black proportion
demo_df['black proportion'] = demo_df['black population'] / demo_df['total population']

# Merge it into grad_df
grad_df = grad_df.merge(
    demo_df[['ward', 'black proportion']],
    on='ward',
    how='left'
)

# Aggregate black proportion by ward
ward_black_proportion = grad_df.groupby('ward')['black proportion'].mean().reset_index()

# Normalize black proportion based on aggregated values
norm = mcolors.Normalize(vmin=ward_black_proportion['black proportion'].min(),
                         vmax=ward_black_proportion['black proportion'].max())
colors = [cm.coolwarm(norm(value)) for value in ward_black_proportion['black proportion']]

# Create a mapping of ward to color
ward_color_map = dict(zip(ward_black_proportion['ward'], colors))

# Map the colors to wards for the bar chart
bar_colors = grad_df['ward'].value_counts().sort_index().index.map(ward_color_map)

# Create a bar plot
fig, ax = plt.subplots(figsize=(10, 6))
grad_df['ward'].value_counts().sort_index().plot(
    kind='bar',
    color=bar_colors,
    ax=ax
)
ax.set_title("Frequency of Schools by Ward (Colored by Black Population Proportion)")
ax.set_xlabel("Ward")
ax.set_ylabel("School Count")

# Add a colorbar legend
sm = cm.ScalarMappable(cmap="coolwarm", norm=norm)
sm.set_array([])
cbar = fig.colorbar(sm, ax=ax, pad=0.02)
cbar.set_label("Black Population Proportion")

plt.show()

The following chart shows the average in-seat attendance for schools across each ward where bar represents a school and is color-coded in order to group by ward.

Overall Trend:

Some Schools are doing well, with attendance close to 100%, while others are struggling.

Differences Between Wards:
  • Ward 2 and Ward 3 stand out with consistently higher attendance rates
  • Ward 7 and Ward 8 seem to have more schools with lower attendance rates
School-Level Differences:

Even within the same ward, some schools have much higher attendance than others - for example, Wards 4 and 8 have a mix of attendance rates. This shows that issues could be school-specific in some cases.

# Aggregate attendance data by Ward
attendance_summary = filtered_attendance.groupby(['ward', 'school name'])['attendance metric score'].mean().reset_index()

# Plot a bar chart
plt.figure(figsize=(12, 8))
sns.barplot(
    data=attendance_summary,
    x='school name',  # school on the x-axis
    y='attendance metric score',  # Metric Score on the y-axis
    hue='ward',  # Different wards represented as colors
    dodge=False  # Avoid stacking
)

# Add labels and title
plt.xlabel('Ward', fontsize=14)
plt.ylabel('Average In-Seat Attendance (%)', fontsize=14)
plt.title('Average In-Seat Attendance by Ward and School (2020)', fontsize=16)
plt.xticks(rotation=90)  # Rotate x-axis labels for better readability
plt.legend(title='School Name', bbox_to_anchor=(1.05, 1), loc='upper left')  # Move legend outside
plt.tight_layout()

# Show the plot
plt.show()

Here, I calculated the average graduation rate by ward using groupby(), then the mean, and then created a bar plot.

Interpretations: - Ward 2 has the highest graduation rate, close to 100% - Ward 6 has the lowest graduation rate, at about 50% - Wards 1 and 8 also lag with rates around 60% and 70% - Wards 3, 4, 5, and 7 fall somewhere in the middle, with averages between 75% and 85%

avg_grad_by_ward = filtered_grad.groupby('ward')['graduate rate'].mean().reset_index()

plt.figure(figsize=(10,6))
sns.barplot(data=avg_grad_by_ward, x='ward', y='graduate rate', palette='cool')
plt.title('Average Graduation Rates by Ward (2020)')
plt.xlabel('Ward')
plt.ylabel('Average Graduation Rate (%)')
plt.xticks(rotation=0)
plt.show()
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/1763506867.py:4: FutureWarning: 

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.barplot(data=avg_grad_by_ward, x='ward', y='graduate rate', palette='cool')

The following bar chart shows the number of discipline incidents reported per ward. To calculate this, I used .sum().

Interpretations: - Ward 5 has the most incidents, with over 800 in counting, a lot compared to other wards - Wards 7 and 1 are also substantially higher, with more than 600 incidents - Wards 2 and 6 have much lower totals - Ward 6 is just nearing 100, which is interesting since it also has lower AP/IB participation. – This may be due to smaller class sizes or smaller schools in general and, therefore, fewer incidents and, unfortunately, fewer resources. - Wards 3, 4, and 8 fall in the middle, with totals ranging between 400 and 500 incidents.

# Group by discipline metric score by ward to get total
discipline_by_ward = discipline_summary.groupby(['ward'], as_index=False)['discipline metric score'].sum().reset_index()

# # sort by discipline total
# discipline_by_ward = discipline_by_ward.sort_values(by='discipline metric score', ascending=False)

# Plotting
plt.figure(figsize=(10,6))
sns.barplot(data=discipline_by_ward, x='ward', y='discipline metric score', palette='cool')

# Customize
plt.title('Total Discipline Incidents By Ward (2020)')
plt.xlabel('Ward')
plt.ylabel('Total Incidents')
plt.show()
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/413252027.py:9: FutureWarning: 

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.barplot(data=discipline_by_ward, x='ward', y='discipline metric score', palette='cool')

I calculated the average AP/IB participation metric across the wards using group by.

Interpretations:

  • Ward 2 leads again, with almost 100% of students participating in advanced placement courses.
  • Wards 6, 7, and 8 have lower participation rates, with 8 at the bottom.
  • Wards 3, 4, and 5 are doing pretty well, with participation rates closer to 60-70%
# Group by test metric scores by ward to calculate average AP participation
test_summary = filtered_test.groupby('ward', as_index=False)['test metric score'].mean().reset_index()

# Sort by participation rate
# test_summary = test_summary.sort_values(by='test metric score', ascending=False)

# Plotting
plt.figure(figsize=(10,6))
sns.barplot(data=test_summary, x='ward', y='test metric score', palette='cool')

# Customizing the plot
plt.title('Average AP/IB Participation Rate by Ward (2020)', fontsize=16)
plt.xlabel('Ward', fontsize=12)
plt.ylabel('Average Participation Rate (%)', fontsize=12)
plt.ylim(0, 1)  # Since the metric is a percentage converted to a decimal
plt.tight_layout()

# Show the plot
plt.show()
/var/folders/ph/mfm_2j1s57n8ylwflpqw64840000gn/T/ipykernel_72077/3331951230.py:9: FutureWarning: 

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.barplot(data=test_summary, x='ward', y='test metric score', palette='cool')

I used .describe() on the ‘attendance,’ ‘discipline,’ and ‘test’ datasets to get the summary statistics for each:

Attendance Summary:
  • Mean: About 86% of students attended school
  • Range: Attendance rates vary from a low of 61% to a high of 96%
  • Most schools appear to have high attendance, with 75% above 84%, but there are still a few outliers at the lower end
Discipline Summary:
  • Mean: Schools reported around 110 discipline incidents
  • Range: The number of incidents spans from 9 to 293
  • Spread:
    • Half the schools report less than 78 incidents
    • The top 25% of schools have over 172 incidents - indicating that discipline issues are concentrated in a smaller number of schools
Test Summary:
  • Mean: About 53% of students participate in AP/IB programs
  • Range: Participation ranges from 5% to 99%
  • Spread:
    • 50% of schools have participation below 47%
    • The top 71% of schools are doing better, with participation rates above 71%.
# Summary statistics for filtered_attendance
attendance_summary = filtered_attendance['attendance metric score'].describe()

# Summary statistics for discipline_summary
discipline_summary_stats = discipline_summary['discipline metric score'].describe()

# Summary statistics for filtered_test
test_summary_stats = filtered_test['test metric score'].describe()

# Display summary statistics
print("Attendance Summary:")
print(attendance_summary)

print("\nDiscipline Summary:")
print(discipline_summary_stats)

print("\nTest Summary:")
print(test_summary_stats)
Attendance Summary:
count    33.000000
mean      0.860909
std       0.097927
min       0.610000
25%       0.840000
50%       0.900000
75%       0.930000
max       0.960000
Name: attendance metric score, dtype: float64

Discipline Summary:
count     33.000000
mean     109.939394
std       85.891989
min        9.000000
25%       47.000000
50%       78.000000
75%      172.000000
max      293.000000
Name: discipline metric score, dtype: float64

Test Summary:
count    30.000000
mean      0.532000
std       0.279672
min       0.050000
25%       0.357500
50%       0.470000
75%       0.715000
max       0.990000
Name: test metric score, dtype: float64

In the following cell, I ran another correlation matrix, observing on the ward level. Let’s go over some strong negative and positive correlations and some interesting insights from the correlation matrix.

Strong Positive Correlations:

The Poverty rate and the Black population have a correlation coefficient of about 0.95, which indicates that wards with higher poverty tend to have a higher proportion of the Black population.

We also see that Median Household Income and Grocery Store Count have a correlation coefficient of about 0.93, which indicates that as median household income increases, so does the number of grocery stores in the area.

Strong Negative Correlations:

Poverty Rate and Grocery Store Count have a correlation coefficient of about -0.93, reinforcing our previous observation that there are fewer grocery stores in areas with higher poverty rates.

We also have Attendance Metric Score and Black population, with a correlation coefficient of about -0.80. This indicates a lack of attendance within wards with a higher Black population.

Conclusion

Key Findings

Strong Positive Correlations:

  • Poverty Rate and Black Population show a high positive correlation (~0.95), meaning wards with higher poverty rates also have a higher proportion of Black residents.

  • Median Household Income and Grocery Store Count have a positive correlation (~0.93), which suggests that wealthier wards have better access to grocery stores.

Strong Negative Correlations:

  • Poverty Rate and Grocery Store Count are negatively correlated (~ -0.93) - reinforcing the previous observation that wards suffering from poverty also lack grocery store access.

  • Attendance Metric Score and Black Population have a negative correlation (- ~0.80), which indicates lower attendance rates in wards with higher Black populations.

Interesting Trends:

  • Ward 3 maintained a high graduation rate despite high discipline scores, while Ward 8 had substantially fewer discipline incidents and a much lower graduation rate.

  • Crime Count showed weak correlations with most features, which leads me to wonder if this could indicate underreporting or inconsistencies in the crime data.

Pearson Correlation Tests:

  • A positive correlation of 0.39 (P-value 0.043) between Grocery Store Count and Weighted Attendance Score suggests that better grocery access may influence attendance rates.

Issues in the Data:

Missing Values - Some datasets had missing rows or NaN values, particularly the graduation rate and discipline datasets, so I did my best to rectify this - whether that was dropping the rows or filling the values to maintain data quality

Disparities Across Wards - Certain schools and wards consistently underperform, while others show better success metrics which could introduce bias during modeling.

Crime Data - As mentioned before, the weak correlations with crime counts are a cause for concern

Next Steps

Feature Selection and Standardization

I focused on selecting relevant, meaningful features that were not highly correlated with each other to avoid multicollinearity. Features were selected based on their correlation with student success indicators, which was realized through the heatmap for the correlation matrix.

Final Features:

  • Student Success Indicators: Weighted Attendance Score, Weighted Test Score, Weighted Graduation Rate
  • Socioeconomic Features: Median Household Income and Poverty Rate
  • Demographics: Black Population and White Population
  • Community: Grocery Store Count
  • Ward: Kept for analysis and grouping purposes

Additionally, I standardized all the numeric features using StandardScaler to ensure that all features were scaled consistently, preventing any one feature from dominating the model simply because of its range of values.