<jupyter_start><jupyter_text>1. Deal with missing variables.2. Consider the two variables: TOTAL_REVENUE and TOTAL_EXPENDITURE. Do these variables have outlier values?3. If you detect outliers in the TOTAL_REVENUE and TOTAL_EXPENDITURE variables, apply the techniques you learned in this checkpoint to eliminate them and validate that there are no outlier values after you handled them.4. Create another variable by subtracting the original TOTAL_EXPENDITURE from TOTAL_REVENUE (before you eliminated the outliers). You can think of it as a kind of budget deficit in education. Do you find any outlier values in this new variable? If so, eliminate them using the technique that think is most suitable.5. Now create another variable by subtracting the TOTAL_EXPENDITURE from TOTAL_REVENUE. This time, use the outlier eliminated versions of TOTAL_EXPENDITURE from TOTAL_REVENUE. In this newly created variable, can you find any outliers? If so, eliminate them.6. Compare some basic descriptive statistics of the budget variables you end up with in the third and the fourth questions. Do you see any differences?7. If your variable of interest is the budget deficit variable, which method do you think is the most appropriate in dealing with the outliers in this variable: the method in the third question or the one in the fourth question?Answer the following questions as you complete this challenge, and then submit a link to your Notebook.<jupyter_code>df.head()
#fill_list = ["ENROLL", "TOTAL_REVENUE", "FEDERAL_REVENUE",
#             "STATE_REVENUE", "LOCAL_REVENUE", "TOTAL_EXPENDITURE",
#             "INSTRUCTION_EXPENDITURE", "SUPPORT_SERVICES_EXPENDITURE",
#             "OTHER_EXPENDITURE", "CAPITAL_OUTLAY_EXPENDITURE", "GRADES_PK_G",
#             "GRADES_KG_G", "GRADES_4_G", "GRADES_8_G", "GRADES_12_G", "GRADES_1_8_G",
#             "GRADES_9_12_G", "GRADES_ALL_G"]
#
#states = education_df["STATE"].unique()
#
#for state in states:
#    education_df.loc[education_df["STATE"] == state, fill_list] = education_df.loc[education_df["STATE"] == state, fill_list].interpolate()
#
## we drop the null values after interpolation
#education_df.dropna(inplace=True)
df.info()
# finding percentages of missing values
df.isnull().sum()*100/df.isnull().count()
# backfill missing enrollment values
df['ENROLL'] = df['ENROLL'].fillna(method='ffill')
# forwardfill missing enrollment values
df['ENROLL'] = df['ENROLL'].fillna(method='bfill')
#dropping YEAR 2017 as values are missing except for 4 average values
df.drop(df[df['YEAR'] == 2017].index, inplace=True)
df.groupby('YEAR').agg({'OTHER_EXPENDITURE':'median'})
# replacing Other Expenditure missing values for 1992 with 1993 median
df['OTHER_EXPENDITURE'].fillna(value = 143312.0,inplace=True)
# Dropping states with <= value counts <=23

# dropped_states = ['PUERTO_RICO', 'VIRGIN_ISLANDS','AMERICAN_SAMOA','NORTHERN_MARIANAS','DOD_DOMESTIC','DOD_OVERSEAS'
# ,'BUREAU_OF_INDIAN_AFFAIRS','BI','DD','NORTHERN_MARIANA_ISLANDS','COMMONWEALTH_OF_MARIANAS','DOD_-_OVERSEAS','BIE'
# ,'DOD_-_DOMESTIC','BUREAU_OF_INDIAN_EDUCATION','MARIANAS','DEPARTMENT_OF_DEFENSE','DEPARTMENT_OF_DEFENSE_EDUCATION_ACTIVITY'
# ,'AS','GU','PR','DOD','DOD_-_FOREIGN','DOD_(OVERSEAS_AND_DOMESTIC_COMBINED)','VI','MP','BUREAU_OF_INDIAN_EDUCATIO','U.S._VIRGIN_ISLANDS']                          1

df.drop(df[df['STATE'] == 'BUREAU_OF_INDIAN_EDUCATIO'].index, inplace=True)
df.isnull().sum()
df.STATE.value_counts()
# interpolating grades columns
df["GRADES_PK_G"] = df["GRADES_PK_G"].interpolate(method='ffill')
df["GRADES_KG_G"] = df["GRADES_KG_G"].interpolate(method='ffill')
df["GRADES_4_G"] = df["GRADES_4_G"].interpolate(method='ffill')
df["GRADES_8_G"] = df["GRADES_8_G"].interpolate(method='ffill')
df["GRADES_12_G"] = df["GRADES_12_G"].interpolate(method='ffill')
df["GRADES_1_8_G"] = df["GRADES_1_8_G"].interpolate(method='ffill')
df["GRADES_9_12_G"] = df["GRADES_9_12_G"].interpolate(method='ffill')
df["GRADES_ALL_G"] = df["GRADES_ALL_G"].interpolate(method='ffill')
# interpolating average grades columns
#df["AVG_MATH_4_SCORE"] = df["AVG_MATH_4_SCORE"].interpolate(method='ffill')
#df["AVG_MATH_8_SCORE"] = df["AVG_MATH_8_SCORE"].interpolate(method='ffill')
#df["AVG_READING_4_SCORE"] = df["AVG_READING_4_SCORE"].interpolate(method='ffill')
df["AVG_READING_8_SCORE"] = df["AVG_READING_8_SCORE"].interpolate(method='ffill')<jupyter_output><empty_output><jupyter_text>2. Consider the two variables: TOTAL_REVENUE and TOTAL_EXPENDITURE. Do these variables have outlier values?ANSWER: By boxplots and Tukey's methods both variables have outliers.<jupyter_code>plt.boxplot(df['TOTAL_EXPENDITURE'])
plt.title('TOTAL_EXPENDITURE')
plt.show()
plt.boxplot(df['TOTAL_REVENUE'])
plt.title('TOTAL_REVENUE')
plt.show()
# NumPy's `percentile()` method returns the 
# values of the given percentiles. In this case,
# give `75` and `25` as parameters, which corresponds 
# to the third and the first quartiles.
q75, q25 = np.percentile(df["TOTAL_EXPENDITURE"], [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((df["TOTAL_EXPENDITURE"] > max_val) 
                      | (df["TOTAL_EXPENDITURE"] < min_val))[0]))
    ))
# NumPy's `percentile()` method returns the 
# values of the given percentiles. In this case,
# give `75` and `25` as parameters, which corresponds 
# to the third and the first quartiles.
q75, q25 = np.percentile(df["TOTAL_REVENUE"], [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((df["TOTAL_REVENUE"] > max_val) 
                      | (df["TOTAL_REVENUE"] < min_val))[0]))
    ))<jupyter_output>The score threshold is: 1.0
Number of outliers is: 146
The score threshold is: 1.5
Number of outliers is: 110
The score threshold is: 2.0
Number of outliers is: 74
The score threshold is: 2.5
Number of outliers is: 52
The score threshold is: 3.0
Number of outliers is: 45
The score threshold is: 3.5
Number of outliers is: 40
The score threshold is: 4.0
Number of outliers is: 37
The score threshold is: 4.5
Number of outliers is: 32<jupyter_text>3. If you detect outliers in the TOTAL_REVENUE and TOTAL_EXPENDITURE variables, apply the techniques you learned in this checkpoint to eliminate them and validate that there are no outlier values after you handled them.ANSWER:  After applying a log transform to both variables, the data is normally distributed and without outliers.<jupyter_code>plt.figure(figsize = (15,5))

plt.subplot(1,2,1)
plt.hist(df["TOTAL_EXPENDITURE"])
plt.title("Histogram of TOTAL_EXPENDITURE")

plt.subplot(1,2,2)
plt.hist(np.log(df["TOTAL_EXPENDITURE"]))
plt.title("Histogram of log transformed TOTAL_EXPENDITURE")
plt.show()
plt.figure(figsize = (15,5))

plt.subplot(1,2,1)
plt.hist(df["TOTAL_REVENUE"])
plt.title("Histogram of TOTAL_REVENUE")

plt.subplot(1,2,2)
plt.hist(np.log(df["TOTAL_REVENUE"]))
plt.title("Histogram of log transformed TOTAL_REVENUE")
plt.show()
# Take the log of TOTAL_EXPENDITURE and calculate
# the quartiles accordingly.
q75, q25 = np.percentile(np.log(df["TOTAL_EXPENDITURE"]), [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((np.log(df["TOTAL_EXPENDITURE"]) > max_val) 
                      | (np.log(df["TOTAL_EXPENDITURE"]) < min_val))[0]))
    ))
# Take the log of TOTAL_REVENUE and calculate
# the quartiles accordingly.
q75, q25 = np.percentile(np.log(df["TOTAL_REVENUE"]), [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((np.log(df["TOTAL_REVENUE"]) > max_val) 
                      | (np.log(df["TOTAL_REVENUE"]) < min_val))[0]))
    ))<jupyter_output>The score threshold is: 1.0
Number of outliers is: 25
The score threshold is: 1.5
Number of outliers is: 0
The score threshold is: 2.0
Number of outliers is: 0
The score threshold is: 2.5
Number of outliers is: 0
The score threshold is: 3.0
Number of outliers is: 0
The score threshold is: 3.5
Number of outliers is: 0
The score threshold is: 4.0
Number of outliers is: 0
The score threshold is: 4.5
Number of outliers is: 0<jupyter_text>4. Create another variable by subtracting the original TOTAL_EXPENDITURE from TOTAL_REVENUE (before you eliminated the outliers). You can think of it as a kind of budget deficit in education. Do you find any outlier values in this new variable? If so, eliminate them using the technique that think is most suitable.ANSWER:  Subtracting TE from TR before eliminating outliers created outliers on both ends reflecting the many deficits.<jupyter_code>df['deficit_with_outliers']= df['TOTAL_REVENUE'] - df['TOTAL_EXPENDITURE']
plt.subplot(1,2,1)

plt.hist(df["deficit_with_outliers"])
plt.title("Histogram of deficit_with_outliers")
plt.boxplot(df['deficit_with_outliers'])
plt.title('deficit_with_outliers')
plt.show()
# Take the log of the deficit_with_outliers and calculate
# the quartiles accordingly.
q75, q25 = np.percentile(np.log(df["deficit_with_outliers"]), [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((np.log(df["deficit_with_outliers"]) > max_val) 
                      | (np.log(df["deficit_with_outliers"]) < min_val))[0]))
    ))<jupyter_output>The score threshold is: 1.0
Number of outliers is: 0
The score threshold is: 1.5
Number of outliers is: 0
The score threshold is: 2.0
Number of outliers is: 0
The score threshold is: 2.5
Number of outliers is: 0
The score threshold is: 3.0
Number of outliers is: 0
The score threshold is: 3.5
Number of outliers is: 0
The score threshold is: 4.0
Number of outliers is: 0
The score threshold is: 4.5
Number of outliers is: 0<jupyter_text>5. Now create another variable by subtracting the TOTAL_EXPENDITURE from TOTAL_REVENUE. This time, use the outlier eliminated versions of TOTAL_EXPENDITURE from TOTAL_REVENUE. In this newly created variable, can you find any outliers? If so, eliminate them.ANSWER: Subtracting TE from TR even after removing outliers created outliers reflecting many states deficits but there are less outliers.<jupyter_code>df['deficit_without_outliers']= np.log(df['TOTAL_REVENUE']) - np.log(df['TOTAL_EXPENDITURE'])
plt.subplot(1,2,1)
plt.hist(df["deficit_without_outliers"])
plt.title("Histogram of deficit_without_outliers")
plt.boxplot(df['deficit_without_outliers'])
plt.title('deficit_without_outliers')
plt.show()
# Take the log of the deficit_without_outliers and calculate
# the quartiles accordingly.
q75, q25 = np.percentile(np.log(df["deficit_without_outliers"]), [75 ,25])
iqr = q75 - q25

for threshold in np.arange(1,5,0.5):
    min_val = q25 - (iqr*threshold)
    max_val = q75 + (iqr*threshold)
    print("The score threshold is: {}".format(threshold))
    print("Number of outliers is: {}".format(
        len((np.where((np.log(df["deficit_without_outliers"]) > max_val) 
                      | (np.log(df["deficit_without_outliers"]) < min_val))[0]))
    ))<jupyter_output>The score threshold is: 1.0
Number of outliers is: 0
The score threshold is: 1.5
Number of outliers is: 0
The score threshold is: 2.0
Number of outliers is: 0
The score threshold is: 2.5
Number of outliers is: 0
The score threshold is: 3.0
Number of outliers is: 0
The score threshold is: 3.5
Number of outliers is: 0
The score threshold is: 4.0
Number of outliers is: 0
The score threshold is: 4.5
Number of outliers is: 0<jupyter_text>5. Compare some basic descriptive statistics of the budget variables you end up with in the third and the fourth questions. Do you see any differences?ANSWER:  Removing outliers and then subtracting greatly reduced the variability or spread of the deficit variable. It increased the mean and std to near zero.6.  If your variable of interest is the budget deficit variable, which method do you think is the most appropriate in dealing with the outliers in this variable: the method in the third question or the one in the fourth question?ANSWER: According to Tukey's method, neither produced outliers at minimun thresholds = 1 so I would likely use deficit with outliers as it would help to increase interpretability.<jupyter_code>df.deficit_with_outliers.describe()
df.deficit_without_outliers.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11547911547911548, 'fraction_numerical': 0.026412776412776413, 'mean_word_length': 5.223124701385571, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '271674', 'license': 'MIT', 'n_tokens_mistral': 4899, 'n_tokens_neox': 4353, 'n_words': 1323, 'path': 'week5_eda/data_cleaning_3_outliers.ipynb', 'repo_name': 'mgalaviz1/my_work', 'size': '13024'}