# ---
# 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
# ---

# %matplotlib inline

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("~/projects/us-education-datasets-unification-project/data/us-education-datasets-unification-project/states_all.csv")
df_orig = df.copy()

df.shape

print('THERE ARE OUTLIERS PRESENT THAT NEED TO BE REMOVED')
print('There are: ')
print(df['PRIMARY_KEY'].duplicated(keep=False).sum(), 'duplicated PRIMARY_KEY s')
print('Unique duplicated keys: \n', df[df['PRIMARY_KEY'].duplicated(keep=False)]['PRIMARY_KEY'].unique())
df[df['PRIMARY_KEY'].duplicated(keep=False)]

df['avg_total_expenditure'] = df['TOTAL_EXPENDITURE'] / df['GRADES_ALL_G']
print('2 rows have unreasonably high avg_total_expenditure')
df[df['avg_total_expenditure'] > 100]
drop_rows= df[df['avg_total_expenditure'] > 100].index.to_list()
print(drop_rows)

# +
plt.hist('avg_total_expenditure', bins=50, log=True, data=df);
plt.show()


# Drop the 2 outlier rows
print('Before drop:', df.shape)
df.drop(axis=0, labels= drop_rows, inplace=True)
print('After drop:', df.shape)

plt.hist('avg_total_expenditure', bins=50, log=True, data=df);
plt.show()

print('THERE ARE OUTLIERS PRESENT THAT NEED TO BE REMOVED')
print('There are: ')
print(df['PRIMARY_KEY'].duplicated(keep=False).sum(), 'duplicated PRIMARY_KEY s')
print('Unique duplicated keys: \n', df[df['PRIMARY_KEY'].duplicated(keep=False)]['PRIMARY_KEY'].unique())
df[df['PRIMARY_KEY'].duplicated(keep=False)]

# +
hist_df = df[df['STATE']=='DISTRICT_OF_COLUMBIA']
plt.hist('GRADES_ALL_G', data=hist_df, log=False, bins=50);
plt.show()

print('2 rows in DISTRICT_OF_COLUMBIAs data have unreasonably small GRADES_ALL_G')
# -

print('These DISTRICT_OF_COLUMBIA duplicated keys have unreasonably small GRADES_ALL_G')
print(df[ (df['STATE'] == 'DISTRICT_OF_COLUMBIA') & (df['GRADES_ALL_G'] <  40000)]['GRADES_ALL_G'])
df[ (df['STATE'] == 'DISTRICT_OF_COLUMBIA') & (df['GRADES_ALL_G'] <  40000)]

# +
print('Drop these bad rows.')
print('Before drop df.shape=', df.shape)

drop_index= df[(df['STATE'] == 'DISTRICT_OF_COLUMBIA') & (df['GRADES_ALL_G'] <  40000)].index.to_list()
print(drop_index)

df.drop(axis=0, labels= drop_index, inplace=True)
print('After drop df.shape=', df.shape)

# +
hist_df = df[df['STATE']=='DISTRICT_OF_COLUMBIA']
plt.hist('GRADES_ALL_G', data=hist_df, log=False, bins=50);
plt.show()

print('REMOVED: 2 rows in DISTRICT_OF_COLUMBIAs data HAD unreasonably small GRADES_ALL_G')
# -

print(df.shape, df_orig.shape)

print('THERE ARE OUTLIERS PRESENT THAT NEED TO BE REMOVED')
print('There are: ')
print(df['PRIMARY_KEY'].duplicated(keep=False).sum(), 'duplicated PRIMARY_KEY s')
print('Unique duplicated keys: \n', df[df['PRIMARY_KEY'].duplicated(keep=False)]['PRIMARY_KEY'].unique())
df[df['PRIMARY_KEY'].duplicated(keep=False)]

# +
print('The rows are identical except that one has NaN s in place of entries in some columns')
print('Drop the row with more NaN s')

drop_rows= df[(df['PRIMARY_KEY'] == '2010_DISTRICT_OF_COLUMBIA') & (df['GRADES_ALL_G'].isnull()) ].index.to_list()

df[(df['PRIMARY_KEY'] == '2010_DISTRICT_OF_COLUMBIA') & (df['GRADES_ALL_G'].isnull()) ]

# +

print('Shape before: ', df.shape)
df.drop(axis=0, labels=drop_rows, inplace=True)
print('Shape after: ', df.shape)

# -

print('THERE ARE OUTLIERS PRESENT THAT NEED TO BE REMOVED')
print('There are: ')
print(df['PRIMARY_KEY'].duplicated(keep=False).sum(), 'duplicated PRIMARY_KEY s')
print('Unique duplicated keys: \n', df[df['PRIMARY_KEY'].duplicated(keep=False)]['PRIMARY_KEY'].unique())
df[df['PRIMARY_KEY'].duplicated(keep=False)]

print('Remove non-Original columns')
print('Shape before: ', df.shape)
df = df[df_orig.columns]
print('Shape after: ', df.shape)

print(len(df_orig) - len(df), ' rows removed')
print('Original: ', df_orig.shape)
print('Final: ', df.shape)

df.to_csv("~/projects/us-education-datasets-unification-project/data/us-education-datasets-unification-project/states_all-cleaned.csv", \
         index=False)



StarcoderdataJupyterScriptsDedupFiltered