<jupyter_start><jupyter_text>Is the SAT unfair?  Cleaning NYC high school data to start finding the answer   Goal of this projectThe goal of this project is to clean and consolidate several datasets relating to the SAT (Scholastic Aptitude Test) and NYC (New York City) high schools.  The final cleaned dataset will be used in separate projects to explore if the SAT is unfair to particular groups of students.  The annual SAT is used by U.S. colleges and universities to determine which students to admit into their programs.    ApproachWe will begin by exploring each of the datasets to understand the information they contain and how they can be consolidated.  We will then clean the datasets to make them ready for further analysis and modeling (i.e. correcting data types and errors, removing unnecessary characters and columns, filling in missing values etc...).  Finally, we will consolidate all of the data into a single cleaned dataset and export it to a csv (comma separated value) file that is ready to use for further exploratory data analysis, visualization and predictive modeling. Summary of resultsThe raw data consists of 8 separate datasets containing missing values, incorrect data types, data errors, unnecessary columns and a combined total of 65,875 rows and 3,861 columns.  The final result of this project is a single consolidated csv file containing 362 rows and 79 columns.  This final dataset is clean, consistent, relevant and ready to use for further analysis and modeling to explore the fairness of the SAT.   Introduction to the dataNew York City has published data on student SAT scores by high school, along with additional related datasets, which can all be found [here](https://data.cityofnewyork.us/browse?category=Education).  The following is a list of the datasets we will be cleaning and consolidating along with a brief description of each.  Each one links to the source page where the data can be downloaded:  * **[SAT scores by school](https://data.cityofnewyork.us/Education/2012-SAT-Results/f9bf-2cp4)**  (`"sat_results.csv"`):  NYC SAT results by school for the graduating seniors of 2012.  At the time of this writing, this was the most recent SAT data available.* **[High school directory](https://data.cityofnewyork.us/Education/2014-2015-DOE-High-School-Directory/n3p6-zve2)** (`"hs_directory.csv"`): Various descriptive information for each high school in NYC for the school year 2014-2015.  We are using this school year because it is the earliest this data was provided in csv format.  We are assuming the descriptive information for most schools (i.e. location) will not have changed much in just a few years.* **[Class size](https://data.cityofnewyork.us/Education/2010-2011-Class-Size-School-level-detail/urz7-pzb3)** (`"class_size.csv"`): NYC average class sizes by school for 2010-2011.  We are using this school year because this data was not provided "by school" for 2011-2012.* **[AP test results](https://data.cityofnewyork.us/Education/2012-AP-Results/9ct9-prf9)** (`"ap_results.csv"`): NYC AP (Advanced Placement) exam results for 2012.  Passing an optional AP exam in a particular subject can earn a student college credit in that subject.  * **[Graduation outcomes](https://data.cityofnewyork.us/Education/2005-2010-Graduation-Outcomes-School-Level/vh2h-md7a)** (`"graduation.csv"`): Various NYC graduation data for graduating classes 2005-2010.  These were the latest graduating years this data was available for.* **[Demographics](https://data.cityofnewyork.us/Education/2006-2012-School-Demographics-and-Accountability-S/ihfw-zy9j)** (`"demographics.csv"`): 2006-2012 NYC public school student populations by grade, special programs, ethnicity, gender etc...  * **[School survey](https://data.cityofnewyork.us/Education/2010-2011-NYC-School-Survey/mnz3-dyi8)** (`"survey_all.txt"`, `"survey_d75.txt"`): 2011 parent, teacher and student survey results for each school.  Survey questions assess each group's opinions on academic expectations, communication, engagement, safety and respect etc...  We are using the 2011 data because it was not available for 2012. Reading in the csv data First, we will read each csv data file into a Pandas Dataframe and then store them in a dictionary named `"data"`.  Using a dictionary provides efficient access to the different datasets by storing them all in one data structure.<jupyter_code>import pandas as pd

# Set the display options, read each csv file into a dataframe, store 
# them in a dictionary and view the "key" values of the dictionary.
pd.options.display.max_columns = 1000  
pd.options.display.max_rows = 1000
data_files = ["ap_results.csv", "class_size.csv", "demographics.csv", 
              "graduation.csv", "hs_directory.csv", "sat_results.csv"]
data = {}
for file in data_files:
    dataframe = pd.read_csv("../data/raw/{0}".format(file))
    key = file.replace(".csv", "")
    data[key] = dataframe
data.keys()<jupyter_output><empty_output><jupyter_text>Understanding the data  We will look at the first 5 rows of each of the dataframes to see what we can learn on initial inspection.  Understanding the `"sat_results"` dataframeWe are interested in exploring relationships between the SAT data and the other datasets. Referencing `"SAT_Results_Data_Dictionary.xlsx"` in the `"references"` folder of this project, each row provides the average SAT scores for each high school in NYC.  Each high school has a unique identification code called a `"DBN"` (district, borough, school number).  The scores are broken down into the 3 main sections of the test: Critical Reading, Math and Writing.  We can explore correlations between this data and the other dataframes to uncover any bias in the SAT.<jupyter_code># View the first 5 rows of the "sat_results" dataframe.
data["sat_results"].head()<jupyter_output><empty_output><jupyter_text>Understanding the `"ap_results"` dataframe  Referencing `"AP_Results_Data_Dictionary.xlsx"` in the `"references"` folder of this project, this dataset provides AP test results data for each NYC high school.  We can use this data to find any correlation between SAT results and AP results.  We can use the `"DBN"` column to join this data with the other dataframes.<jupyter_code># View the first 5 rows of the "ap_results" dataframe.
data["ap_results"].head()<jupyter_output><empty_output><jupyter_text>Understanding the `"class_size"` dataframe  Referencing `"Class_Size_Data_Dictionary.xlsx"` in the `"references"` folder of this project, this dataset provides average class sizes for each school, by grade and program type for grades 5-9 and 9-12, aggregated by program type and core course.  We can use this data to find any correlation between SAT results and class size.Each row represents a specific `"GRADE"` and `"PROGRAM TYPE"` for that school.  We will have to filter for the rows that contain the `"GRADE"` and `"PROGRAM TYPE"` values that are most suitable for our analysis.  There is also no `"DBN"` (district, borough, school number) column in this dataframe.  However, according to `"Class_Size_Data_Dictionary.xlsx"`, the `"CSD"` column represents the district and the `"SCHOOL CODE"` column represents the borough and school number.  This means we can create a `"DBN"` column by combining the `"CSD"` and `"SCHOOL CODE"` columns.  If `"CSD"` is only a single digit, we need to add a leading `"0"` to make the `"DBN"` consistent with the other dataframes.  This will allow us to join this data with the other dataframes.<jupyter_code># View the first 5 rows of the "class_size" dataframe.
data["class_size"].head()<jupyter_output><empty_output><jupyter_text>Understanding the `"demographics"` dataframe  Referencing `"Demographics_Data_Dictionary.xlsx"` in the `"references"` folder of this project, this dataset provides various demographic data for each NYC high school.  Some examples include: `"ell_percent"` (percentage of English language learners), `"asian_per"` (percentage of Asian students) and `"female_per"` (percentage of female students).  This data will be instrumental in exploring if the SAT is unfair towards any particular demographic groups.Each row represents a specific school year for that school.  We will have to filter for the rows that contain the school years that are most suitable for our analysis.  We can use the `"DBN"` column to join this data with the other dataframes.<jupyter_code># View the first 5 rows of the "demographics" dataframe.
data["demographics"].head()<jupyter_output><empty_output><jupyter_text>Understanding the `"graduation"` dataframe  Referencing `"Graduation_Data_Dictionary.xlsx"` in the `"references"` folder of this project, this dataset provides various graduation statistics for each NYC high school.  Some examples include: `"Total Grads - % of cohort"` (percentage of students that graduated) and `"Dropped Out - % of Cohort"` (percentage of students that dropped out).  We can use this data to find any correlation between SAT results and graduation rates.Each row represents a specific `"Demographic"` (i.e. Total Cohort, White, Black etc...) and `"Cohort"` (the school year the SAT was administered) for that school.  We will have to filter for the rows that contain the `"Demographic"` and `"Cohort"` values that are most suitable for our analysis.  We can use the `"DBN"` column to join this data with the other dataframes.<jupyter_code># View the first 5 rows of the "graduation" dataframe.
data["graduation"].head()<jupyter_output><empty_output><jupyter_text>Understanding the `"hs_directory"` dataframe  Referencing `"HS_Directory_Data_Dictionary.xlsx"` in the `"references"` folder of this project, this dataset provides various descriptive information for each NYC high school.  Some examples include: `"boro"` (borough where the school is located) and `"Location 1"` (address and geographic coordinates).  We can use `"Location 1"` to visualize the SAT results on a map of NYC.  This will let us see any geographic patterns in SAT results.Each row represents descriptive information for each NYC high school.  We can use the `"dbn"` column to join this data with the other dataframes.<jupyter_code># View the first 5 rows of the "hs_directory" dataframe.
data["hs_directory"].head()<jupyter_output><empty_output><jupyter_text>Reading in, understanding and combining the survey data  Referencing `"Survey_Data_Dictionary.xls"` in the `"references"` folder of this project, the `"survey_all.txt"` and `"survey_d75.txt"` files are datasets summarizing the results of the 2011 NYC School Survey.  This is an annual survey that asks parents, teachers and students their opinions on academic expectations, communication, engagement, safety, respect and various other topics at their respective schools.   Reading in and understanding the `"survey_all.txt"` data  The `"survey_all.txt"` data file provides the survey results for all community schools.  We will read it into a dataframe named `"all_survey"`.  Some of the columns include: `"saf_s_11"` (safety and respect score based on student responses) and `"aca_t_11"` (academic expectations score based on teacher responses).  We can use this data to find any correlation between SAT results and parents', teachers' and students' opinions about their respective schools.Each row represents the survey results for each NYC high school.  We can use the `"dbn"` column to join this data with the other dataframes.<jupyter_code># Read "survey_all.txt" into a dataframe and view the first 5 rows.
all_survey = pd.read_csv('../data/raw/survey_all.txt', delimiter='\t',
                         encoding='windows-1252')
all_survey.head()<jupyter_output><empty_output><jupyter_text>Reading in and understanding the `"survey_d75.txt"` data  The `"survey_d75.txt"` data file provides the survey results for all District 75 schools.  We will read it into a dataframe named `"d75_survey"`.  The structure of the dataframe is essentially the same as `"all_survey"`.  Although there are less total columns, the primary ones concerning parents', teachers' and students' opinions about school engagement, safety, academic expectations etc... remain common in both dataframes.<jupyter_code># Read "survey_d75.txt" into a dataframe and view the first 5 rows.
d75_survey = pd.read_csv('../data/raw/survey_d75.txt', delimiter='\t',
                         encoding='windows-1252')
d75_survey.head()<jupyter_output><empty_output><jupyter_text>Combining the survey dataframes  There is no need for us to separate the survey results for District 75 schools, so we will join `"d75_survey"` to the bottom of `"all_survey"` and create one final dataframe named `"survey"`.  We will view the first and last rows to confirm that `"survey"` contains both Non-District 75 schools (`"dbn"` does not begin with `"75"`) and District 75 schools (`"dbn"` does begin with `"75"`).<jupyter_code># Join "d75_survey" to the bottom of "all_survey", then view the first
# and last rows of the new dataframe "survey".
survey = pd.concat([all_survey, d75_survey], axis=0, sort=False)
survey.iloc[[1,-1]]<jupyter_output><empty_output><jupyter_text>Cleaning the `"survey"` dataframe  In `"survey"`, we will copy the values of `"dbn"` into a new `"DBN"` column (all capital letters) so it is consistent with the other dataframes.  There are 2,773 columns in `"survey"` but we will only need a select few for our future analysis.  Based on reviewing `"Survey_Data_Dictionary.xls"` and hypothesizing which data could possibly influence SAT results, we will only keep the following columns:  * `"DBN"`: School identification code (district, borough, school number)  * `"rr_s"`: Student response rate  * `"rr_t"`: Teacher response rate  * `"rr_p"`: Parent response rate  * `"N_s"`: Number of student respondents  * `"N_t"`: Number of teacher respondents  * `"N_p"`: Number of parent respondents  * `"saf_s_11"`: Safety and respect score based on student responses  * `"saf_t_11"`: Safety and respect score based on teacher responses* `"saf_p_11"`: Safety and respect score based on parent responses  * `"saf_tot_11"`: Safety and respect total score  * `"eng_s_11"`: Engagement score based on student responses  * `"eng_t_11"`: Engagement score based on teacher responses  * `"eng_p_11"`: Engagement score based on parent responses* `"eng_tot_11"`: Engagement total score  * `"aca_s_11"`: Academic expectations score based on student responses  * `"aca_t_11"`: Academic expectations score based on teacher responses  * `"aca_p_11"`: Academic expectations score based on parent responses* `"aca_tot_11"`: Academic expectations total score* `"com_s_11"`: Communication score based on student responses  * `"com_t_11"`: Communication score based on teacher responses* `"com_p_11"`: Communication score based on parent responses* `"com_tot_11"`: Communication total score  After filtering `"survey"` to the selected columns above, we will add it to our `"data"` dictionary along with the other dataframes.  We will then view the first 5 rows of `"survey"`.<jupyter_code># Create the "DBN" column, filter "survey" to our selected columns, add
# it to our "data" dictionary and view the first 5 rows.
survey_cols = ["DBN", "rr_s", "rr_t", "rr_p", "N_s", "N_t", "N_p",
               "saf_s_11", "saf_t_11", "saf_p_11", "saf_tot_11",
               "eng_s_11", "eng_t_11", "eng_p_11", "eng_tot_11",
               "aca_s_11", "aca_t_11", "aca_p_11", "aca_tot_11",
               "com_s_11", "com_t_11", "com_p_11", "com_tot_11"]
survey["DBN"] = survey["dbn"]
survey = survey.loc[:, survey_cols]
data["survey"] = survey
data["survey"].head()<jupyter_output><empty_output><jupyter_text>Inserting `"DBN"` columns   Inserting the `"DBN"` column into `"hs_directory"`In `"hs_directory"`, we will copy the values of the `"dbn"` column into a new `"DBN"` column (all capital letters) so it is consistent with the other dataframes.  We will then view the first 5 rows to confirm it was created properly.  The new `"DBN"` column will be at the right most end of `"hs_directory"`.<jupyter_code># Create the "DBN" column and view the first 5 rows.
data["hs_directory"]["DBN"] = data["hs_directory"]["dbn"]
data["hs_directory"].head()<jupyter_output><empty_output><jupyter_text>Inserting the `"DBN"` column into `"class_size"`  As we discovered previously, `"class_size"` doesn't have a `"DBN"` column but it can be created by joining the `"CSD"` and `"SCHOOL CODE"` columns.  If `"CSD"` is only a single digit, we need to add a leading `"0"` to make the `"DBN"` consistent with the other dataframes.  We will create a function named `pad_csd` which takes in an integer value, converts it to a string, adds a leading `"0"` (if it is only a single digit) and returns the string.  We will then apply this function to the `"CSD"` column to create a new column named `"padded_csd"`, which will contain all of the `"CSD"` values as 2-digit integer strings.  Finally, we will combine the `"padded_csd"` and `"SCHOOL CODE"` columns to create the `"DBN"` column.  We will then view the first 5 rows to confirm both columns were created properly. The new `"padded_csd"` and `"DBN"` columns will be at the right most end of `"class_size"`.<jupyter_code>def pad_csd(int):
    """Convert an integer to a 2-digit string and return the string."""
    string = str(int)
    if len(string) == 2:
        return string
    else:
        return string.zfill(2)

# Create "padded_csd" and "DBN" columns and view the first 5 rows.
data["class_size"]["padded_csd"] = data["class_size"]["CSD"].apply(pad_csd)
data["class_size"]["DBN"] = (data["class_size"]["padded_csd"]
                             + data["class_size"]["SCHOOL CODE"])
data["class_size"].head()<jupyter_output><empty_output><jupyter_text>Cleaning the `"sat_results"` dataframeWith the exception of `"DBN"` and `"SCHOOL NAME"`, every column in `"sat_results"` should have a numeric data type (`float` or `int`). We will view the data types of each column to confirm.<jupyter_code># View the column data types of "sat_results".
data["sat_results"].dtypes<jupyter_output><empty_output><jupyter_text>We will convert the last 4 columns to a numeric data type.Since we are only interested in the total SAT score for each school, we will add the separate SAT score columns together (critical reading, math and writing) to create a new column of total SAT scores named `"sat_score"`.  Finally, we will view the first 5 rows to confirm the new column was created properly.<jupyter_code># Convert the last 4 columns to a numeric data type, create the
# "sat_score" column and view the first 5 rows.
for col in data["sat_results"].columns[2:]:
    data["sat_results"][col] = pd.to_numeric(data["sat_results"][col],
                                             errors="coerce")
data["sat_results"]["sat_score"] = (
    data["sat_results"]["SAT Critical Reading Avg. Score"]
    + data["sat_results"]["SAT Math Avg. Score"]
    + data["sat_results"]["SAT Writing Avg. Score"]
)
data["sat_results"].head()<jupyter_output><empty_output><jupyter_text>Parsing geographic coordinates in `"hs_directory"`  As discussed earlier, the `"Location 1"` column of `"hs_directory"` contains the geographic coordinates (latitude, longitude) of each school.  We are going to extract these latitudes (lat) and longitudes (lon) so that we will be able to plot the SAT results of each school on a map of NYC. Extracting and converting the latitude  In the `"Location 1"` column, the geographic coordinates for each school are contained within parentheses `()`.  The latitude is the first coordinate.  We will import the `re` (regular expression) module and create a function named `get_lat`.  This function will take in a string, find all of the characters that are contained within parentheses (the coordinates), select the first coordinate (the latitude), drop the parentheses and return the cleaned latitude value.  We will then apply this function to the `"Location 1"` column to extract all of the cleaned latitude values and populate a new column named `"lat"`.  We will convert this column from a string data type to a numeric data type so that arithmetic operations can be performed on it.  We will then view the first 5 rows of the `"Location 1"` and `"lat"` columns to confirm the new column was created properly.<jupyter_code>import re

def get_lat(string):
    """Find, clean and return the lat coordinates from a string."""
    coord = re.findall("\(.+\)", string)[0]
    lat = coord.split(", ")[0]
    lat_clean = lat.replace("(", "").replace(")", "")    
    return lat_clean

# Create the "lat" column, convert to a numeric data type and view the
# first 5 rows of the relevant columns.
data["hs_directory"]["lat"] = data["hs_directory"]["Location 1"].apply(get_lat)
data["hs_directory"]["lat"] = pd.to_numeric(data["hs_directory"]["lat"],
                                            errors="coerce")
data["hs_directory"][["Location 1", "lat"]].head()<jupyter_output><empty_output><jupyter_text>Extracting and converting the longitude  All of the steps here are analogous to the ["Extracting and converting the latitude"](Extracting-and-converting-the-latitude) steps above, except we are extracting the 2nd coordinate, the longitude, and creating the new column `"lon"`.  We will view the first 5 rows of the `"Location 1"`, `"lat"` and `"lon"` columns to confirm everything looks correct.<jupyter_code>def get_lon(string):
    """Find, clean and return the lon coordinates from a string."""
    coord = re.findall("\(.+\)", string)[0]
    lon = coord.split(", ")[1]
    lon_clean = lon.replace("(", "").replace(")", "")    
    return lon_clean

# Create the "lon" column, convert to a numeric data type and view the 
# first 5 rows of the relevant columns.
data["hs_directory"]["lon"] = data["hs_directory"]["Location 1"].apply(get_lon)
data["hs_directory"]["lon"] = pd.to_numeric(data["hs_directory"]["lon"],
                                            errors="coerce")
data["hs_directory"][["Location 1", "lat", "lon"]].head()<jupyter_output><empty_output><jupyter_text>Cleaning the `"hs_directory"` dataframe We are only interested in the `"DBN"`, `"lat"` and `"lon"` columns of `"hs_directory"` so we will filter the dataframe to only include these columns.<jupyter_code># Filter "hs_directory" to our selected columns and view the first 5
# rows.
data["hs_directory"] = data["hs_directory"][["DBN", "lat", "lon"]]
data["hs_directory"].head()<jupyter_output><empty_output><jupyter_text>Cleaning the `"class_size"` dataframe  As we discussed earlier when exploring `"class_size"`, each row of the dataframe represents a specific `"GRADE"` and `"PROGRAM TYPE"` for that school.  We will have to filter for the rows that contain the `"GRADE"` and `"PROGRAM TYPE"` values that are most suitable for our analysis.  For ease and convenience we are first going to assign the `"class_size"` dataframe in our `"data"` dictionary to a variable named `"class_size"`.  We will then view the unique values of the `"GRADE "` column.<jupyter_code># Assign the "class_size" dataframe to a variable and view the unique
# values in the "GRADE " column.
class_size = data["class_size"]
list(class_size["GRADE "].unique())<jupyter_output><empty_output><jupyter_text>Similarly, we will view the unique values of the `"PROGRAM TYPE"` column.<jupyter_code># View the unique values in the "PROGRAM TYPE" column of "class_size".
list(class_size["PROGRAM TYPE"].unique())<jupyter_output><empty_output><jupyter_text>Since we are only exploring high school data, we will filter `"class_size"` to only include rows where the `"GRADE"` column is `"09-12"`.  Referencing `"Class_Size_Data_Dictionary.xlsx"` in the `"references"` folder of this project, the `"PROGRAM TYPE"` values of `"CTT"` (collaborative team teaching), `"SPEC ED"` (self-contained special education) and `"G&T"` (gifted and talented) are all specialty programs.  So we will filter the dataframe to only include the `"GEN ED"` (general education) program type.  We will then view the first 5 rows of `"class_size"` to confirm it's only including the rows we selected.<jupyter_code># Filter "class_size" to rows with our selected "GRADE" and
# "PROGRAM TYPE" values and view the first 5 rows.
class_size = class_size[class_size["GRADE "] == "09-12"]
class_size = class_size[class_size["PROGRAM TYPE"] == "GEN ED"]
class_size.head()<jupyter_output><empty_output><jupyter_text>Creating a unique `"DBN"` for each row in `"class_size"`  Each row in `"class_size"` now represents a specific `"CORE COURSE"` for that school (i.e. English 9, English 10, Integrated Algebra etc...).  This means there are still `"DBN"` values repeated across multiple rows.  We need to aggregate the values in `"class_size"` so there is a unique `"DBN"` in each row.  We will accomplish this by grouping `"class_size"` by each unique `"DBN"` value and aggregating the values by calculating the mean of each column within each `"DBN"` group.  We will then reset the index of the new dataframe.  The `"CSD"` column will not be needed for our analysis and the `"SCHOOLWIDE PUPIL-TEACHER RATIO"` column contains all missing values, so we will drop both of those columns from the dataframe.  We will assign the new `"class_size"` dataframe, with unique `"DBN"` values in each row, back to our `"data"` dictionary and view the first 5 rows.<jupyter_code>import numpy as np

# Group "class_size" by "DBN", reset the index, drop the selected
# columns, assign the dataframe back to our "data" dictionary and 
# view the first 5 rows.
class_size = class_size.groupby("DBN").agg(np.mean)
class_size.reset_index(inplace=True)
class_size.drop(columns=["CSD", "SCHOOLWIDE PUPIL-TEACHER RATIO"],
                inplace=True)
data["class_size"] = class_size
data["class_size"].head()<jupyter_output><empty_output><jupyter_text>Cleaning the `"demographics"` dataframe  As we discussed earlier when exploring `"demographics"`, each row represents a specific school year for that school. We will have to filter for the rows that contain the school years that are most suitable for our analysis.  First we will examine the unique values in the `"schoolyear"` column of `"demographics"`.<jupyter_code># View the unique values in the "schoolyear" column of "demographics".
list(data["demographics"]["schoolyear"].unique())<jupyter_output><empty_output><jupyter_text>We want the most recent data available, so we will filter the dataframe to only include the `"schoolyear"` value of `"20112012"`.  This will also ensure each row has a unique `"DBN"` value.  We also don't need `"Name"`, `"schoolyear"` or any of the columns describing the grade level, so we will drop them from the dataframe.We will then view the first 5 rows to confirm the dataframe has been condensed properly.<jupyter_code># Filter "demographics" to the "schoolyear" value of "20112012", drop
# the selected columns and view the first 5 rows.
data["demographics"] = (
    data["demographics"][data["demographics"]["schoolyear"] == 20112012]
)
drop_cols = ["Name", "schoolyear", "prek", "k", "grade1", "grade2", "grade3",
             "grade4", "grade5", "grade6", "grade7", "grade8", "grade9", 
             "grade10", "grade11", "grade12"]
data["demographics"].drop(columns=drop_cols, inplace=True)
data["demographics"].head()<jupyter_output><empty_output><jupyter_text>Correcting column data types  With the exception of `"DBN"`, every column in `"demographics"` should have a numeric data type (`float` or `int`).  We will view the data types of each column to confirm.<jupyter_code># View the column data types of "demographics".
data["demographics"].dtypes<jupyter_output><empty_output><jupyter_text>The `"fl_percent"`, `"ctt_num"` and `"selfcontained_num"` columns have `object` data types. Upon further inspection, `"fl_percent"` contains all missing values, so we will drop that column.  We will then convert the other 2 columns to numeric data types.  Finally, we will view the column data types again to confirm everything looks correct.<jupyter_code># Drop the "fl_percent" column, convert "ctt_num" and
# "selfcontained_num" to numeric data types and view the column data
# types.
data["demographics"].drop(columns="fl_percent", inplace=True)
for col in ["ctt_num", "selfcontained_num"]:
    data["demographics"][col] = pd.to_numeric(data["demographics"][col],
                                              errors="coerce")
data["demographics"].dtypes<jupyter_output><empty_output><jupyter_text>Cleaning the `"graduation"` dataframe  As we discussed earlier when exploring `"graduation"`, each row represents a specific `"Demographic"` and `"Cohort"` for that school. We will have to filter for the rows that contain the `"Demographic"` and `"Cohort"` values that are most suitable for our analysis.First we will examine the unique values in the `"Demographic"` column of `"graduation"`.<jupyter_code># View the unique values in the "Demographic" column of "graduation".
list(data["graduation"]["Demographic"].unique())<jupyter_output><empty_output><jupyter_text>Similarly, we will view the unique values in the `"Cohort"` column.<jupyter_code># View the unique values in the "Cohort" column of "graduation".
list(data["graduation"]["Cohort"].unique())<jupyter_output><empty_output><jupyter_text>We already have demographic specific data in the `"demographics"` dataframe, so we will filter `"graduation"` to include only the `"Demographic"` value of `"Total Cohort"`.  We want the most recent full-year data available, so we will filter the dataframe to include only the `"Cohort"` value of `"2006"`.  Both of these filters will also ensure that each row has a unique `"DBN"` value.  We will then drop the `"Demographic"`, `"School Name"` and `"Cohort"` columns as they will no longer be needed for our future analysis.We will view the first 5 rows to confirm the dataframe has been condensed properly.<jupyter_code># Filter "graduation" to rows with our selected "Demographic" and
# "Cohort" values, drop the selected columns and view the first 5 rows.
data["graduation"] = (
    data["graduation"][data["graduation"]["Demographic"] == "Total Cohort"]
)
data["graduation"] = data["graduation"][data["graduation"]["Cohort"] == "2006"]
drop_cols = ["Demographic", "School Name", "Cohort"]
data["graduation"].drop(columns=drop_cols, inplace=True)
data["graduation"].head()<jupyter_output><empty_output><jupyter_text>Correcting column data types  With the exception of `"DBN"`, every column in `"graduation"` should have a numeric data type (`float` or `int`). We will view the data types of each column to confirm.<jupyter_code># View the column data types of "graduation".
data["graduation"].dtypes<jupyter_output><empty_output><jupyter_text>The third to the last columns need to be converted to numeric data types.  We will remove all of the `%` symbols from those columns and then convert them from string to numeric data types.  We will then view the first 5 rows to confirm everything looks right.<jupyter_code># Remove all "%" symbols from the third to the last columns, convert
# each column to a numeric data type and view the first 5 rows.
for col in data["graduation"].columns[2:]: 
    data["graduation"][col] = (
        data["graduation"][col].apply(lambda x: x.replace("%",""))
    )
    data["graduation"][col] = pd.to_numeric(data["graduation"][col],
                                            errors="coerce")
data["graduation"].head()<jupyter_output><empty_output><jupyter_text>Cleaning the `"ap_results"` dataframe  With the exception of `"DBN"`, every column in `"ap_results"` should have a numeric data type (`float` or `int`). We will view the data types of each column to confirm.<jupyter_code># View the column data types of "ap_results".
data["ap_results"].dtypes<jupyter_output><empty_output><jupyter_text>We will drop the `"SCHOOL NAME"` column from `"ap_results"` as it will not be needed for our future analysis.We will convert the last 3 columns to a numeric data type and then view the data types for all of the columns to confirm everything looks right.<jupyter_code># Drop the "SCHOOL NAME" column, convert the last 3 columns to a numeric
# data type and view the data types of each column in "ap_results".
data["ap_results"].drop(columns="SCHOOL NAME", inplace=True)
for col in data["ap_results"].columns[1:]:
    data["ap_results"][col] = pd.to_numeric(data["ap_results"][col],
                                            errors="coerce")
data["ap_results"].dtypes<jupyter_output><empty_output><jupyter_text>Confirming unique `"DBN"` values for each dataframe  Each dataframe should have a unique `"DBN"` value in each row.  To check this, we will print the name of each dataframe along with a list of any repeated `"DBN"` values and their counts.<jupyter_code># List any repeated "DBN" values and their counts for each dataframe.
for dataframe in data.keys():
    thresh = data[dataframe]["DBN"].value_counts() > 1
    multiple_dbn = list(data[dataframe]["DBN"].value_counts()[thresh].items())
    print(dataframe, multiple_dbn)<jupyter_output>ap_results []
class_size []
demographics []
graduation []
hs_directory []
sat_results []
survey []<jupyter_text>The empty lists confirm that each dataframe has a unique `"DBN"` value in each row. Joining all of the dataframes  All of the dataframes are now ready to be joined into a single dataframe using the `"DBN"` columns.  To determine which join methods to use, we will first look at the number of rows in each dataframe.<jupyter_code># View the number of rows for each dataframe in the "data" dictionary.
for dataframe in data.keys():
    print(dataframe + ": " + str(data[dataframe].shape[0]))<jupyter_output>ap_results: 478
class_size: 583
demographics: 1509
graduation: 405
hs_directory: 435
sat_results: 478
survey: 1702<jupyter_text>Performing the left joins  The `"sat_results"` dataframe contains our target variable of SAT scores for each school.  The ultimate purpose for this final cleaned dataframe is to explore any correlations between SAT scores and the other datasets.  Another potential application is predicting SAT scores by training machine learning algorithms on the other data.  Therefore, we want to preserve as much of the `"sat_results"` data as possible.  Since `"graduation"` has considerably less rows than `"sat_results"`, we will left join it onto `"sat_results"` to create a new dataframe named `"combined"`.  The left join will preserve all of the rows from `"sat_results"` and merge in any rows from `"graduation"` that has a matching `"DBN"` value with `"sat_results"`.  Any rows with `"DBN"` values that exist in `"sat_results"` but not in `"graduation"` will be filled with `NaN` (not a number) missing values in the respective columns of `"graduation"`.  Since `"graduation"` only contains quantitative data, we can use quantitative methods to fill in those missing values (i.e. K-Nearest Neighbors).We will view the first 5 rows of the new `"combined"` dataframe.<jupyter_code># Left join "graduation" onto "sat_results" to create the "combined"
# dataframe and view the first 5 rows.
combined = data["sat_results"]
combined = combined.merge(data["graduation"], how="left", on="DBN")
combined.head()<jupyter_output><empty_output><jupyter_text>Performing the inner joins  With the exception of `"hs_directory"`, the remaining dataframes have at least as many rows as `"sat_results"`, so we will inner join them onto `"combined"`.  Using inner joins means only rows with matching `"DBN"` values between `"sat_results"` and the remaining dataframes will be included.  This prevents adding rows with missing values that we will have to deal with, while the equivalent or larger sizes of the remaining dataframes protects against losing too much `"sat_results"` data.Even though `"hs_directory"` has less rows than `"sat_results"` we are still performing an inner join, which will result in some lost `"sat_results"` data.  This is because `"hs_directory"` contains only descriptive data (i.e. geographic coordinates) and if we use a left join we would not have a reliable method to fill in the missing values (i.e. K-Nearest Neighbors).  After performing the inner joins, we will view the first 5 rows of `"combined"`.<jupyter_code># Inner join the remaining dataframes to "combined" and view the first
# 5 rows.
join_dataframes = ["ap_results", "class_size", "demographics", "survey",
                   "hs_directory"]
for dataframe in join_dataframes:
    combined = combined.merge(data[dataframe], how="inner", on="DBN")
combined.head()<jupyter_output><empty_output><jupyter_text>Filling in missing values  First we will view the number of missing values, by column, in the `"combined"` dataframe.<jupyter_code># View the number of missing values for each column in "combined".
combined.isnull().sum()<jupyter_output><empty_output><jupyter_text>There are numerous missing values, exclusively in the quantitative columns, originating from either the original raw data or from the left join operations when we combined the dataframes.   The K-Nearest Neighbors algorithmTo fill in the missing data, we will use the K-Nearest Neighbors algorithm.  This algorithm estimates missing values by taking the average of the "K" nearest observed values in the same column as the missing data (the "nearest neighbors").  These "nearest neighbors" are the rows of data that are "closest" to the target row, based on calculating a distance metric (i.e. Euclidean distance) using the values in the other columns.  The algorithm only works on quantitative data, so first we will separate `"combined"` into a dataframe named `"object_df"` (containing only `object` data types), and a dataframe named `"numeric_df"` (containing only `float` or `int` data types).  We will fill in the missing data in `"numeric_df"` using the K-Nearest Neighbors algorithm with K=5.  We will then re-join `"object_df"` with the completely filled numeric data and re-assign the resulting dataframe back to `"combined"`.  Lastly, we will view the missing values for each column in `"combined"` to confirm that none remain.<jupyter_code>from knnimpute.argpartition import knn_impute_with_argpartition as knn

# Separate "combined" into "object" and "numeric" only dataframes, fill
# the missing values in the "numeric" dataframe, re-join with the
# "object" dataframe, re-assign back to "combined" and view the missing
# values for each column.
object_df = combined.select_dtypes("object").copy()
numeric_df = combined.select_dtypes(["float64", "int64"]).copy()
filled_numeric_matrix = knn(numeric_df.values, numeric_df.isnull().values, k=5)
filled_numeric_df = pd.DataFrame(filled_numeric_matrix,
                                 columns=numeric_df.columns)
combined = object_df.join(filled_numeric_df)
combined.isnull().sum()<jupyter_output><empty_output><jupyter_text>Adding a school district column to `"combined"`  It will be useful to be able to group the data by school district, particularly for plotting data on a geographic map.  As we discovered earlier, the first 2 digits of a school's `"DBN"` represent the school district it belongs to.  So we will add a new school district column, named `"school_dist"`, by taking the first 2 digits of each value in the `"DBN"` column.  We will then view the first 5 rows of `"DBN"` and `"school_dist"` to make sure the new column was created properly.<jupyter_code># Add a "school_dist" column and view the first 5 rows of "DBN" and
# "school_dist".
combined["school_dist"] = combined["DBN"].apply(lambda string: string[:2])
combined[["DBN", "school_dist"]].head()<jupyter_output><empty_output><jupyter_text>Exporting `"combined"` to a csv file  We will now export the final cleaned `"combined"` dataframe to a csv file named `"nyc_hs_data_clean.csv"` in the `"data/processed"` folder of this project.<jupyter_code># Export the final cleaned "combined" dataframe to a csv file.
combined.to_csv("../data/processed/nyc_hs_data_clean.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09980479503478652, 'fraction_numerical': 0.010961509585064318, 'mean_word_length': 5.126801594602883, 'pattern_counts': {'":': 1, '<': 163, '<?xml version=': 0, '>': 164, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '41', 'dir': 'jupyter-structured-clean-dedup', 'id': '607569', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 11802, 'n_tokens_neox': 11194, 'n_words': 4932, 'path': 'notebooks/1.0-jt-clean-nyc-hs-data.ipynb', 'repo_name': 'JustinToribio/cleaning-nyc-hs-data', 'size': '39958'}