<jupyter_start><jupyter_text>1. Determine all the variable types and find the fraction of the missing values for each variable.2. Notice that the data has a time dimension (year). For this assignment, forget about time and treat all the observations as if they're from the same year. Choose a strategy to deal with the missing values for each variables. For which variables would filling in the missing values with some value make sense? For which might tossing out the records entirely make sense?3. Now, take into account the time factor. Replicate your second answer but this time fill in the missing values by using a statistic that is calculated within the year of the observation. For example, if you want to fill a missing value for a variable with the mean of that variable, calculate the mean by using only the observations for that specific year.4. This time, fill in the missing values using interpolation (extrapolation).5. Compare your results for the 2nd, 3rd, and 4th questions. Do you find any meaningful differences?<jupyter_code>import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import warnings

warnings.filterwarnings('ignore')
postgres_user = 'dsbc_student'
postgres_pw = '<PASSWORD>'
postgres_host = '172.16.31.10'
postgres_port = '5432'
postgres_db = 'useducation'

engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(
    postgres_user, postgres_pw, postgres_host, postgres_port, postgres_db))

education_df = pd.read_sql_query('select * from useducation',con=engine)

# no need for an open connection, 
# as we're only doing a single query
engine.dispose()
education_df.info()
education_df.isna().mean()*100<jupyter_output><empty_output><jupyter_text>2. Notice that the data has a time dimension (year). For this assignment, forget about time and treat all the observations as if they're from the same year. Choose a strategy to deal with the missing values for each variables. For which variables would filling in the missing values with some value make sense? For which might tossing out the records entirely make sense? For tossing out - I'd remove ll the AVG_'X' columns that have over 60% null values. Since the data is organized by state over years (and we are ignoring time in this instance) we can't reasonably fill that many rows with values and be accurate.There are also some rows of data I'd look to drop, specifically ones with mostly NaN values such as '2009_DOD_-_ DOMESTIC' and '2009_GUAM'. For drops, I'd drop nulls in the low percentage columns like Grades_8_G as the null values probably correlate with the rows mentioned in my last example. They also account for such a small amount of data that it wont affect the outcome tremendously.In fact, I'd go as far as to drop the FEDERAL REVENUE nulls as that 14% null seems consistent across the dataframe and likely represents the same rows. There is also such a big discrepency in Revenue between states that neither interpolation nor imputation make much sense. I could imputate by mean on a state-state level basis if I chose to ignore time.I think enrollment would be okay to imputate with the state's mean. It seems to be a pretty consistent measure<jupyter_code>education_df[education_df['OTHER_EXPENDITURE'].isnull()]
education_state = education_df.groupby(['YEAR'])

education_df[education_df['STATE'] == 'ALABAMA']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05772646536412078, 'fraction_numerical': 0.011249259917110717, 'mean_word_length': 5.2, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '480618', 'license': 'MIT', 'n_tokens_mistral': 920, 'n_tokens_neox': 860, 'n_words': 477, 'path': 'Data Cleaning 2.ipynb', 'repo_name': 'sgf-afk/Class_Assignments', 'size': '3380'}