# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns;
sns.set()
import numpy as np
import matplotlib.ticker as mtick
import warnings
warnings.filterwarnings('ignore')
import os

# # Data Exploration Tasks

print(os.getcwd())

os.chdir('../data/processed')

combined_ofsted_df = pd.read_csv(os.getcwd() + '/ComponentOneData.csv')
combined_ofsted_df.shape

# ## Examine Distributions

## printing low quantile and high quantile for outliers
df = combined_ofsted_df.loc[:,combined_ofsted_df.dtypes == float]
for i, col in enumerate(df.columns):
    plt.figure(i)
    sns.distplot(df[col],kde=True)
    print(col + ' Quantile Low: ' + str(df[col].quantile(0.01)) + ' Quantitle High: ' + str(df[col].quantile(0.99)))

# +
fig, ax = plt.subplots(figsize=(18,6))
sns.distplot(combined_ofsted_df['PNORG'],kde=False,ax=ax)

ax.set_xlabel('Percentage of Girls on Roll')
ax.set_ylabel('Number of Schools')

mean = combined_ofsted_df['PNORG'].mean()
median = combined_ofsted_df['PNORG'].median()

plt.axvline(mean, color='r', linestyle='-',linewidth=3)
plt.axvline(median, color='b', linestyle='--',linewidth=2)

L = ax.legend({mean,median}, fontsize=18)
L.get_texts()[0].set_text('Mean = {}%'.format(round(mean,1)))
L.get_texts()[1].set_text('Median = {}%'.format(round(median,1)))

tick = mtick.StrMethodFormatter('{x:,.0f}%')
ax.xaxis.set_major_formatter(tick)
# -

## replacing non-numeric values with mean
## printing low quantile and high quantile for outliers
df = combined_ofsted_df.loc[:,combined_ofsted_df.dtypes == object].drop(columns=['GENDER','RELCHAR','Rating'])
for i, col in enumerate(df.columns):
    mean = round(df[col].transform(pd.to_numeric, errors='coerce').mean(),1)
    dictMean = {'NE': mean, 'SUPP': mean, 'NP': mean, 'LOWCOV': mean,'SP': mean}
    plt.figure(i)
    df[col] = df[col].replace(dictMean) # Replace placeholder with mean of the column
    df[col] = df[col].astype(float) # converting column in float so it can calculate Quantitle
    print(col + ' Quantile Low: ' + str(df[col].quantile(0.01)) + ' Quantitle High: ' + str(df[col].quantile(0.99)))
    sns.distplot(df[col],kde=True)    

# +
combined_ofsted_df[['GENDER','RELCHAR','Rating']].describe().transpose()

## 3 unique values with mostly mixed schools in gender with freq 5590
## 41 unique values with mostly None in RELCHAR with freq 2658
## 6 unique values with mostly Good Rating with freq 2719
# -

sns.countplot(x="GENDER", data=combined_ofsted_df)

fig, ax = plt.subplots(figsize=(12,6))
sns.countplot(x="RELCHAR", data=combined_ofsted_df,ax=ax)
plt.xticks(rotation=90);

fig, ax = plt.subplots(figsize=(12,6))
sns.countplot(x="Rating", data=combined_ofsted_df,ax=ax)

# ## Coorelation

# +
df = combined_ofsted_df[['PNUMFSM', 'P8MEA']]

for col in df.columns:
    mean = round(df[col].transform(pd.to_numeric, errors='coerce').mean(),1)
    dictMean = {'NE': mean, 'SUPP': mean, 'NP': mean, 'LOWCOV': mean,'SP': mean}
    df[col] = df[col].astype(object).replace(dictMean)

df['PNUMFSM'] = df['PNUMFSM'].astype(float) * 100
df['P8MEA'] = df['P8MEA'].astype(float)

# +
## Data is showing more towards on x-axis 0 and y-axis 1 and -1 
##because I have imputed missing values and placeholders with mean. 
fig, ax = plt.subplots(figsize=(12,8))
sns.regplot(data = df ,x='PNUMFSM', y='P8MEA',scatter_kws={'alpha':0.2});
# plt.xlim(-0.9)
# plt.ylim(-8)

tick = mtick.StrMethodFormatter('{x:,.0f}%')
ax.xaxis.set_major_formatter(tick)

ax.set_xlabel('Percentage of Students Receiving Free School Meals')
ax.set_ylabel('Progress 8 Adjusted Score')
# -

# **P8MEA** Progress 8 measure after adjustment for extreme scores
#
#
# **ATT8SCR** Average Attainment 8 score per pupil

# +
## replacing non-numeric values with mean so it will generate correlation
df = combined_ofsted_df[['ATT8SCR','P8MEA']]

for col in df.columns:
    mean = round(df[col].transform(pd.to_numeric, errors='coerce').mean(),1)
    dictMean = {'NE': mean, 'SUPP': mean, 'NP': mean, 'LOWCOV': mean,'SP': mean}
    df[col] = df[col].replace(dictMean)



df.astype(float).corr()
# -

# A positive correlation between a school’s Progress 8 score and its average Attainment 8 score 

# +
# imputing placeholders with -1 value so it will generate the same visualization as required
df = combined_ofsted_df[['P8_BANDING','P8MEA']]

for col in df.columns:
    placeholder = -1
    dictMean = {'NE': placeholder, 'SUPP': placeholder, 'NP': placeholder, 'LOWCOV': placeholder,'SP': placeholder}
    df[col] = df[col].replace(dictMean)
    df[col] = df[col].astype(float)

# +
fig, ax = plt.subplots(figsize=(18,8))
sns.violinplot(data = df ,x='P8_BANDING', y='P8MEA');

ax.set_xlabel('Progress 8 Banding (1 = Well Above Average, 5 = Well Below Average, -1 = No Banding)')
ax.set_ylabel('Progress 8 Measure After Adjustment')
# -

# Discover which Local Authority (LA) has the highest average Attainment 8 score and the highest Progress 8 score. Likewise, find the LA with the lowest scores and the one with the biggest range of values.

# +
## replacing non-numeric values with mean
dfLaAndP8 = combined_ofsted_df[['LA','P8MEA','ATT8SCR']]

mean = round(dfLaAndP8['P8MEA'].transform(pd.to_numeric, errors='coerce').mean(),1)
dictMean = {'NE': mean, 'SUPP': mean, 'NP': mean, 'LOWCOV': mean,'SP': mean}
dfLaAndP8['P8MEA'] = dfLaAndP8['P8MEA'].replace(dictMean).astype(float)

mean = round(dfLaAndP8['ATT8SCR'].transform(pd.to_numeric, errors='coerce').mean(),1)
dictMean = {'NE': mean, 'SUPP': mean, 'NP': mean, 'LOWCOV': mean,'SP': mean}
dfLaAndP8['ATT8SCR'] = dfLaAndP8['ATT8SCR'].replace(dictMean).astype(float)

# +
## Directory changed
# -

os.chdir('../raw')

dfLACodes = pd.read_csv(os.getcwd() + '/la_and_region_codes_meta.csv')

merge1_df = pd.merge(dfLaAndP8,dfLACodes, how='left', left_on='LA', right_on='LEA')
merge1_df['P8MEA'] = merge1_df['P8MEA'].fillna(merge1_df['P8MEA'].mean())
merge1_df['ATT8SCR'] = merge1_df['ATT8SCR'].fillna(merge1_df['ATT8SCR'].mean())

# Local Authority (LA) has the highest average Attainment 8 score and the highest Progress 8 score

highestAttainment = merge1_df[merge1_df['ATT8SCR'] == merge1_df['ATT8SCR'].max()]
print('Highest Avg Attainment 8 Score: '+  highestAttainment['ATT8SCR'].to_string(index=False) + ' where LA Name: '+  highestAttainment['LA Name'].to_string(index=False))

highestProgress = merge1_df[merge1_df['P8MEA'] == merge1_df['P8MEA'].max()]
print('Highest Progress 8 Score: '+  highestProgress['P8MEA'].to_string(index=False) + ' where LA Name: '+  highestProgress['LA Name'].to_string(index=False))

# LA with the lowest scores

# +
lowestAttainment = merge1_df[merge1_df['ATT8SCR'] == merge1_df['ATT8SCR'].min()]

print('Lowest Avg Attainment 8 Score: '+  lowestAttainment['ATT8SCR'].to_string(index=False) + ' where LA Name: '+  lowestAttainment['LA Name'].to_string(index=False))
# -

lowestProgress = merge1_df[merge1_df['P8MEA'] == merge1_df['P8MEA'].min()]
print('Lowest Progress 8 Score: '+  lowestProgress['P8MEA'].to_string(index=False) + ' where LA Name: '+  lowestProgress['LA Name'].to_string(index=False))

# The one with the biggest range of values

merge1_df[merge1_df['ATT8SCR'] <= (merge1_df['ATT8SCR'].max() - merge1_df['ATT8SCR'].min())][['LA Name','ATT8SCR']].sample(10)

merge1_df[merge1_df['P8MEA'] <= (merge1_df['P8MEA'].max() - merge1_df['P8MEA'].min())][['LA Name','P8MEA']].sample(10)

# Regions have the highest and lowest Attainment 8 scores and Progress 8 scores.

print('Highest Avg Attainment 8 Score: '+  highestAttainment['ATT8SCR'].to_string(index=False) + ' where REGION NAME: '+  highestAttainment['REGION NAME'].to_string(index=False))

print('Highest Progress 8 Score: '+  highestProgress['P8MEA'].to_string(index=False) + ' where REGION NAME: '+  highestProgress['REGION NAME'].to_string(index=False))

print('Lowest Avg Attainment 8 Score: '+  lowestAttainment['ATT8SCR'].to_string(index=False) + ' where REGION NAME: '+  lowestAttainment['REGION NAME'].to_string(index=False))

print('Lowest Progress 8 Score: '+  lowestProgress['P8MEA'].to_string(index=False) + ' where REGION NAME: '+  lowestProgress['REGION NAME'].to_string(index=False))

# There are many regions which are parts of larger regions. Combine all the “London” regions into one region called “London”, all the “Yorkshire” regions into one called “Yorkshire” and all those regions which are labelled A, B, C or D (e.g. “South West B”) into one region.

merge1_df.loc[merge1_df['REGION NAME'].str.startswith('London') == True,'REGION NAME'] = 'London'
merge1_df.loc[merge1_df['REGION NAME'].str.contains('Yorkshire') == True,'REGION NAME'] = 'Yorkshire'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('South West') == True,'REGION NAME'] = 'South West'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('South East') == True,'REGION NAME'] = 'South East'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('East of England') == True,'REGION NAME'] = 'East of England'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('North West') == True,'REGION NAME'] = 'North West'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('West Midlands') == True,'REGION NAME'] = 'West Midlands'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('North East') == True,'REGION NAME'] = 'North East'
merge1_df.loc[merge1_df['REGION NAME'].str.startswith('East Midlands') == True,'REGION NAME'] = 'East Midlands'

fig, ax = plt.subplots(figsize=(18,8))
sns.barplot(x=merge1_df['REGION NAME'],y=merge1_df['ATT8SCR'],data=merge1_df,ci=95)
ax.set_ylabel('Average Attainment 8 Score')
ax.set_xlabel('Wider Region')

StarcoderdataJupyterScriptsDedupFiltered