<jupyter_start><jupyter_text>Project 1: Standardized Test Analysis ---  Part 1Part 1 requires knowledge of basic Python.---  Problem Statement The state of California (California Department of Education) has many different school districts. They require the following analyses: (1) standardized test performance (both ACT and SAT) for the various districts in the state (2) data on students eligible for Free and Reduced Priced Meals (FRPM) for the various districts in the state, so they can identify whether there is a correlation between FRPM status and test performance in the schools, and consider providing additional support to the students in need.  Contents:- [Background](Background)- [Data Import & Cleaning](Data-Import-and-Cleaning)- [Exploratory Data Analysis](Exploratory-Data-Analysis)- [Data Visualization](Visualize-the-Data)- [Conclusions and Recommendations](Conclusions-and-Recommendations)  Background The SAT and ACT are standardized tests that many colleges and universities in the United States require for their admissions process. This score is used along with other materials such as grade point average (GPA) and essay responses to determine whether or not a potential student will be accepted to the university.The SAT has two sections of the test: Evidence-Based Reading and Writing and Math ([*source*](https://www.princetonreview.com/college/sat-sections)). The ACT has 4 sections: English, Mathematics, Reading, and Science, with an additional optional writing section ([*source*](https://www.act.org/content/act/en/products-and-services/the-act/scores/understanding-your-scores.html)). They have different score ranges, which you can read more about on their websites or additional outside sources (a quick Google search will help you understand the scores for each test):* [SAT](https://collegereadiness.collegeboard.org/sat)* [ACT](https://www.act.org/content/act/en.html)Standardized tests have long been a controversial topic for students, administrators, and legislators. Since the 1940's, an increasing number of colleges have been using scores from sudents' performances on tests like the SAT and the ACT as a measure for college readiness and aptitude ([*source*](https://www.minotdailynews.com/news/local-news/2017/04/a-brief-history-of-the-sat-and-act/)). Supporters of these tests argue that these scores can be used as an objective measure to determine college admittance. Opponents of these tests claim that these tests are not accurate measures of students potential or ability and serve as an inequitable barrier to entry. Lately, more and more schools are opting to drop the SAT/ACT requirement for their Fall 2021 applications ([*read more about this here*](https://www.cnn.com/2020/04/14/us/coronavirus-colleges-sat-act-test-trnd/index.html)). SAT score range:  Evidence-Based Reading and Writing: 200-800  Math: 200-800  (optional)Essay:2-8  Pre-2016, the scoring structure was different. However, data is from 2017 hence our data is not affected.ACT score range:  For each of the four sections: Between 1-36 which is average of correct answers in the section  Composite score: average of scores for each section  Scores are rounded to the nearest whole number  (optional) Writing: 4 domain-level scores ranged 2-12  1 subject-level score which is an average of the 4 domain-level scores  Choose your DataThere are 10 datasets included in the [`data`](./data/) folder for this project. You are required to pick **at least two** of these to complete your analysis. Feel free to use more than two if you would like, or add other relevant datasets you find online.* [`act_2017.csv`](./data/act_2017.csv): 2017 ACT Scores by State* [`act_2018.csv`](./data/act_2018.csv): 2018 ACT Scores by State* [`act_2019.csv`](./data/act_2019.csv): 2019 ACT Scores by State* [`act_2019_ca.csv`](./data/act_2019_ca.csv): 2019 ACT Scores in California by School* [`sat_2017.csv`](./data/sat_2017.csv): 2017 SAT Scores by State* [`sat_2018.csv`](./data/sat_2018.csv): 2018 SAT Scores by State* [`sat_2019.csv`](./data/sat_2019.csv): 2019 SAT Scores by State* [`sat_2019_by_intended_college_major.csv`](./data/sat_2019_by_intended_college_major.csv): 2019 SAT Scores by Intended College Major* [`sat_2019_ca.csv`](./data/sat_2019_ca.csv): 2019 SAT Scores in California by School* [`sat_act_by_college.csv`](./data/sat_act_by_college.csv): Ranges of Accepted ACT & SAT Student Scores by Colleges  DataWe will be using a subset of the below datasets where:1. The schools exist in both the ACT and FRPM dataset or both the SAT and FRPM dataset;2. There was at least 1 SAT or ACT taker within the school    act_2019_ca.csv  2019 ACT Scores in California by School  [Source](https://web.archive.org/web/20201223024015/http://www3.cde.ca.gov/researchfiles/satactap/act19.xlsx) [Data Dictionary](https://web.archive.org/web/20201224005341/https://www.cde.ca.gov/ds/sp/ai/reclayoutact19.asp)  Contains County/District/School information, enrollment, and performance statistics of ACT scores in California for 2019sat_2019_ca.csv  2019 SAT Scores in California by School  [Source](https://web.archive.org/web/20201223024015/http://www3.cde.ca.gov/researchfiles/satactap/sat19.xlsx) [Data Dictionary](https://web.archive.org/web/20201224004530/https://www.cde.ca.gov/ds/sp/ai/reclayoutsat19.asp)  Contains County/District/School information, enrollment, and performance statistics of SAT scores in California for 2019frpm1819.csv  Free or Reduced Price Meals (FRPM) student data in California by School  [Source](https://web.archive.org/web/20201016183202/https://www.cde.ca.gov/ds/sd/sd/filessp.asp) [Data Dictionary](https://web.archive.org/web/20201017154628/https://www.cde.ca.gov/ds/sd/sd/filesspfrpm.asp)  Contains County/District/School information, enrollment, and statistics on Students who are eligible to receive FRPM either automatically based on their status or based on their application for the National School Lunch Program (NSLP), or who are determined to meet the same income eligibility criteria as the NSLP, through their local schools. This dataset includes schools where there are no students eligible to receive FRPM.      Outside Research Based on your problem statement and your chosen datasets, spend some time doing outside research on state policies or additional information that might be relevant. Summarize your findings below. If you bring in any outside tables or charts, make sure you are explicit about having borrowed them. If you quote any text, make sure that it renders as being quoted. **Make sure that you cite your sources.** The Free and Reduced Priced Meal program is part of the National School Breakfast and Lunch Program for California. The United States Department of Agriculture provides funding to support five school meal and milk programs to assist schools, districts, and other non-profit agencies in providing nutritious meals and milk to children at reasonable prices or free to qualified applicants.  [Source](https://www.benefits.gov/benefit/1953)    Low-income children are eligible to receive reduced-price or free meals at school. Children in households with incomes below 130 percent of the poverty level or those receiving SNAP or TANF qualify for free meals. Those with family incomes between 130 and 185 percent of the poverty line qualify for reduced-price meals. When school is out of session in the summertime, children continue to receive nutritious meals and snacks through the Summer Food Service Program.  [Source](https://www.feedingamerica.org/take-action/advocate/federal-hunger-relief-programs/national-school-lunch-program)    Poverty rate in California in 2019 was 11.8%  [Source](https://www.statista.com/statistics/205434/poverty-rate-in-california/)  Coding Challenges1. Manually calculate mean:    Write a function that takes in values and returns the mean of the values. Create a list of numbers that you test on your function to check to make sure your function works!        *Note*: Do not use any mean methods built-in to any Python libraries to do this! This should be done without importing any additional libraries.<jupyter_code># Code:
def calc_mean(numbers_list):   
    # mean = sum of numbers divided by count of numbers
    #sum is used to find the sum of the numbers list
    #len is used to find the count of the numbers list
    return sum(numbers_list)/len(numbers_list)
check_list = [2,4,6,8,10]
calc_mean(check_list)<jupyter_output><empty_output><jupyter_text>2. Manually calculate standard deviation:    The formula for standard deviation is below:    $$\sigma = \sqrt{\frac{1}{n}\sum_{i=1}^n(x_i - \mu)^2}$$    Where $x_i$ represents each value in the dataset, $\mu$ represents the mean of all values in the dataset and $n$ represents the number of values in the dataset.    Write a function that takes in values and returns the standard deviation of the values using the formula above. Hint: use the function you wrote above to calculate the mean! Use the list of numbers you created above to test on your function.        *Note*: Do not use any standard deviation methods built-in to any Python libraries to do this! This should be done without importing any additional libraries.<jupyter_code># Code:
def std_dev(dataset):
    mean = calc_mean(dataset) #calculate the mean of the dataset
    values = [] #create empty list for each value - mean squared
    for num in dataset: #iterate through data set to calculate each value - mean squared
        values.append((num-mean)**2) 
#formula for standard dev: squareroot of (sum of values-  mean squared divided by number of values)
    standard_dev = (sum(values)/len(dataset))**(1/2)
    return standard_dev

std_dev([1,2,3,4,5,6,7,1,2,3,4,5]) #test<jupyter_output><empty_output><jupyter_text>3. Data cleaning function:        Write a function that takes in a string that is a number and a percent symbol (ex. '50%', '30.5%', etc.) and converts this to a float that is the decimal approximation of the percent. For example, inputting '50%' in your function should return 0.5, '30.5%' should return 0.305, etc. Make sure to test your function to make sure it works!You will use these functions later on in the project!<jupyter_code># Code:
def str_to_float(value):
    return float(value.replace('%', ''))/100
# replace to remove the % symbol and float to turn it into a float
# /100 for decimal approximation
str_to_float('17.23%')<jupyter_output><empty_output><jupyter_text>---  Part 2Part 2 requires knowledge of Pandas, EDA, data cleaning, and data visualization.--- *All libraries used should be added here*<jupyter_code># Imports:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import scipy.stats as stats<jupyter_output><empty_output><jupyter_text>Data Import and Cleaning  Data Import & CleaningImport the datasets that you selected for this project and go through the following steps at a minimum. You are welcome to do further cleaning as you feel necessary:1. Display the data: print the first 5 rows of each dataframe to your Jupyter notebook.2. Check for missing values.3. Check for any obvious issues with the observations (keep in mind the minimum & maximum possible values for each test/subtest).4. Fix any errors you identified in steps 2-3.5. Display the data types of each feature.6. Fix any incorrect data types found in step 5.    - Fix any individual values preventing other columns from being the appropriate type.    - If your dataset has a column of percents (ex. '50%', '30.5%', etc.), use the function you wrote in Part 1 (coding challenges, number 3) to convert this to floats! *Hint*: use `.map()` or `.apply()`.7. Rename Columns.    - Column names should be all lowercase.    - Column names should not contain spaces (underscores will suffice--this allows for using the `df.column_name` method to access columns in addition to `df['column_name']`).    - Column names should be unique and informative.8. Drop unnecessary rows (if needed).9. Merge dataframes that can be merged.10. Perform any additional cleaning that you feel is necessary.11. Save your cleaned and merged dataframes as csv files.<jupyter_code># Code:
ca_sat = pd.read_csv('../data/sat_2019_ca.csv') #read the csv
ca_sat.columns = [col.lower() for col in ca_sat.columns] #change all column headings to lowercase
ca_sat.dropna(axis = 0, subset=['sname'], inplace=True) #remove any schools with no school_name(or code)
# remove unnecessary columns
ca_sat.drop(['unnamed: 25','cds','rtype','enroll11', 'numtsttakr11','numerwbenchmark11','pcterwbenchmark11','nummathbenchmark11','pctmathbenchmark11','totnumbothbenchmark11','pctbothbenchmark11'], axis = 1, inplace = True)
ca_sat.drop(2036, axis = 0, inplace =True) #drop the extra empty row
ca_sat.replace(['*','N/A'], np.nan, inplace = True) #replacing * and N/A with nan so that statistics are not affected
ca_sat[['scode','ccode','cdcode']] = ca_sat[['scode','ccode','cdcode']].astype(np.int64).astype(str) #convert from float to str, removing decimal
ca_sat = ca_sat.astype({"numerwbenchmark12": float, "nummathbenchmark12": float, "totnumbothbenchmark12" : float, "pcterwbenchmark12": float, "pctmathbenchmark12": float, "pctbothbenchmark12": float}) #change datatype to float
#renaming columns for ease of reading
new_sat_columns = ['county_code', 'district_code','school_code','school_name','district_name','county_name','enrollment','num_sat_takers','sat_num_read_write_benchmark','sat_perc_read_write_benchmark', 'sat_num_math_benchmark','sat_perc_math_benchmark','sat_total_num_both_benchmarks','sat_perc_both_benchmarks','year']
ca_sat.columns = new_sat_columns
#change percentage columns to between 0-1
ca_sat = ca_sat.apply(lambda x: x/100 if x.name in['sat_perc_read_write_benchmark', 'sat_perc_math_benchmark','sat_perc_both_benchmarks'] else x)
ca_sat.tail()
ca_act = pd.read_csv('../data/act_2019_ca.csv') #read the csv
ca_act.drop(['Unnamed: 17','CDS','RType'], axis = 1, inplace = True) #remove unnecessary columns
ca_act.columns = [col.lower() for col in ca_act.columns] #change all column headings to lowercase
ca_act.dropna(axis = 0, subset=['scode'], inplace=True) #remove any schools with missing school_code (or name)
ca_act.replace(['*','N/A'], np.nan, inplace = True) #replacing * with nan so that statistics are not affected
ca_act[['scode','ccode','cdcode']] = ca_act[['scode','ccode','cdcode']].astype(np.int64).astype(str) #convert from float to str, removing decimal
ca_act = ca_act.astype({"avgscrread": float, "avgscreng": float, "avgscrmath": float, "avgscrsci": float, "numge21": float, "pctge21": float}) #change datatype to float
#renaming columns for ease of reading
new_act_columns = ['county_code', 'district_code','school_code','school_name','district_name','county_name','enrollment','num_act_takers','act_avg_score_read','act_avg_score_eng','act_avg_score_math','act_avg_score_sci','act_num_above_21','act_perc_above_21','year']
ca_act.columns = new_act_columns
#change percentage columns to between 0-1
ca_act = ca_act.apply(lambda x: x/100 if x.name in['act_perc_above_21'] else x)
ca_act.head()
frpm_data = pd.read_csv('../data/frpm1819.csv') #read the csv
#remove unnecessary columns
frpm_data.drop(['Unnamed: 7','Unnamed: 8','Unnamed: 9','Unnamed: 10','Unnamed: 11','Unnamed: 12','Unnamed: 13','Unnamed: 14','Unnamed: 15','Unnamed: 16','Unnamed: 22','Unnamed: 23','Unnamed: 24','Unnamed: 25','Unnamed: 26','Unnamed: 27'], axis = 1, inplace=True)
frpm_data.drop(0, axis = 0, inplace=True) #remove first row which is just header names
#change column names for ease of reading
new_column_names = ['year', 'county_code', 'district_code','school_code','county_name','district_name','school_name','enrollment','free_meal_count','free_meal_perc','frpm_count','frpm_perc']
frpm_data.columns = new_column_names
#remove ',' from the columns to allow changing to integer
columns_to_amend = ['enrollment', 'free_meal_count', 'frpm_count']
frpm_data[columns_to_amend] = frpm_data[columns_to_amend].applymap(lambda string : string.replace(',', ''))
frpm_data = frpm_data.astype({'enrollment': int, 'free_meal_count': int, 'frpm_count': int})
#change 'year' values to match other dataframes
frpm_data['year'] = frpm_data['year'].apply(lambda string : string.replace('-2019','-19'))
#change str_to_float_ for percentages
frpm_data[['free_meal_perc','frpm_perc']] = frpm_data[['free_meal_perc','frpm_perc']].applymap(lambda x : str_to_float(x))
#filter out school codes 0 and 1 which have insufficient information
frpm_data = frpm_data[~(frpm_data.school_code == '1')&~(frpm_data.school_code =='0')]
frpm_data.describe()

#ensure ca_sat and ca_act have the same string data as frpm_data
ca_sat.loc[ca_sat.school_code.isin(frpm_data.school_code), ['county_code','district_code','school_name','county_name','district_name','enrollment']] = frpm_data[['county_code','district_code','school_name','county_name','district_name','enrollment']]
ca_act.loc[ca_act.school_code.isin(frpm_data.school_code), ['county_code','district_code','school_name','county_name','district_name','enrollment']] = frpm_data[['county_code','district_code','school_name','county_name','district_name','enrollment']]

#merge of frpm_data intersect (sat union act) to find all schools available in act and sat which are also in frpm_data
#merge sat and act datasets by matching school_name, removing duplicate columns
sat_act = pd.merge(ca_act, ca_sat, how='outer', on='school_code',suffixes=('', '_DROP')).filter(regex='^(?!.*_DROP)')
#merge sat_act and frpm datasets by matching school_code, removing duplicate columns
sat_act_frpm_merged = frpm_data.merge(sat_act, how='inner', on='school_code',suffixes=('', '_DROP')).filter(regex='^(?!.*_DROP)')
#remove unnecessary columns - there is only one year and we do not need to know the county, and district codes
sat_act_frpm_merged.drop(['year','county_code','district_code','free_meal_count','free_meal_perc'], axis = 1, inplace=True)
#remove rows where both sat and act scores were nan - no data can be extracted
sat_act_frpm_merged.dropna(axis = 0, subset=['act_avg_score_read','sat_num_read_write_benchmark'], how='all', inplace=True)
#save the csv
sat_act_frpm_merged.to_csv(r'..\data\sat_act_frpm_merged.csv', index=False)
print(sat_act_frpm_merged.info())
sat_act_frpm_merged.head()

# #merge sat and act datasets by matching school_name, removing duplicate columns
# sat_act_merged = ca_sat.merge(ca_act, how='inner', on='school_name',suffixes=('', '_DROP')).filter(regex='^(?!.*_DROP)')
# #merge sat_act_merged and frpm datasets by matching school_code, removing duplicate columns
# sat_act_frpm_merged = sat_act_merged.merge(frpm_data, how='inner', on='school_code',suffixes=('', '_DROP')).filter(regex='^(?!.*_DROP)')
# #remove unnecessary columns - there is only one year
# sat_act_frpm_merged.drop(['year','county_code','district_code','school_code'], axis = 1, inplace=True)
# #remove rows where both sat and act scores were nan - no data can be extracted
# sat_act_frpm_merged.dropna(axis = 0, subset=['act_avg_score_read','sat_num_read_write_benchmark'], how='all', inplace=True)
# print(sat_act_frpm_merged.info())
# sat_act_frpm_merged.describe()

#individually merging the datasets in the order (sat + cat) + frpm yields 1503 entries while the method above yielded 1519 entries<jupyter_output><empty_output><jupyter_text>Data DictionaryNow that we've fixed our data, and given it appropriate names, let's create a [data dictionary](http://library.ucmerced.edu/node/10249). A data dictionary provides a quick overview of features/variables/columns, alongside data types and descriptions. The more descriptive you can be, the more useful this document is.Example of a Fictional Data Dictionary Entry: |Feature|Type|Dataset|Description||---|---|---|---||**county_pop**|*integer*|2010 census|The population of the county (units in thousands, where 2.5 represents 2500 people).| |**per_poverty**|*float*|2010 census|The percent of the county over the age of 18 living below the 200% of official US poverty rate (units percent to two decimal places 98.10 means 98.1%)|[Here's a quick link to a short guide for formatting markdown in Jupyter notebooks](https://jupyter-notebook.readthedocs.io/en/stable/examples/Notebook/Working%20With%20Markdown%20Cells.html).Provided is the skeleton for formatting a markdown table, with columns headers that will help you create a data dictionary to quickly summarize your data, as well as some examples. **This would be a great thing to copy and paste into your custom README for this project.***Note*: if you are unsure of what a feature is, check the source of the data! This can be found in the README.  Data Dictionary|Feature|Type|Dataset|Description||:---|---|---|---||**school_code**|object|SAT/ACT/FRPM| The code of the California School||**school_name**|object|SAT/ACT/FRPM| The name of the California School||**district_name**|object|SAT/ACT/FRPM| The name of the California District||**county_name**|object|SAT/ACT/FRPM| The name of the California County||**enrollment**|float|SAT/ACT/FRPM| The number of 12th grade students enrolled in the School in the 2018-19 School Year||**num_sat_takers**|float|SAT|The number of 12th grade students, by school, who took the SAT in the 2018-19 School Year||**perc_sat_takers**|float|SAT|The percentage of 12th grade students, by school, who took the SAT in the 2018-19 School Year||**sat_num_read_write_benchmark**|float|SAT|The number of 12th grade students, by School, that took the SAT for Evidence-based Reading and Writing and scored above the mean score threshold set by the College Board in the 2018-19 School Year.||**sat_perc_read_write_benchmark**|float|SAT|The percentage of 12th grade students, by School, that took the SAT for Evidence-based Reading and Writing and scored above the mean score threshold set by the College Board in the 2018-19 School Year.||**sat_num_math_benchmark**|float|SAT|The number of 12th grade students, by School, that took the SAT for Mathematics and scored above the mean score threshold set by the College Board in the 2018-19 School Year.||**sat_perc_math_benchmark**|float|SAT|The percentage of 12th grade students, by School, that took the SAT for Mathematics and scored above the mean score threshold set by the College Board in the 2018-19 School Year.||**sat_total_num_both_benchmarks**|float|SAT|The number of 12th grade students, by School, that took the SAT and scored above the mean total score threshold set by the College Board in the 2018-19 School Year.||**sat_perc_both_benchmarks**|float|SAT|The percentage of 12th grade students, by School, that took the SAT and scored above the mean total score threshold set by the College Board in the 2018-19 School Year.||**num_act_takers**|float|ACT|The number of 12th grade students, by school, who took the ACT in the 2018-19 School Year||**perc_act_takers**|float|ACT|The percentage of 12th grade students, by school, who took the ACT in the 2018-19 School Year||**act_avg_score_read**|float|ACT|The average Reading score of 12th grade students, by School, that took the ACT in the 2018-19 School Year.||**act_avg_score_eng**|float|ACT|The average English score of 12th grade students, by School, that took the ACT in the 2018-19 School Year.||**act_avg_score_math**|float|ACT|The average Mathematics score of 12th grade students, by School, that took the ACT in the 2018-19 School Year.||**act_avg_score_sci**|float|ACT|The average Science score of 12th grade students, by School, that took the ACT in the 2018-19 School Year.||**act_num_above_21**|float|ACT|The number of 12th grade students, by School, that took the ACT and achieved a composite score above 21 in the 2018-19 School Year.||**act_perc_above_21**|float|ACT|The percentage of 12th grade students, by School, that took the ACT and achieved a composite score above 21 in the 2018-19 School Year.||**frpm_count**|int|FRPM|The number of 12th grade students, by School, who meet household income or categorical eligibility criteria for free meals or reduced meals based on one or more of the valid reasons in the 2018-19 School Year.||**frpm_perc**|float|FRPM|The percentage of 12th grade students, by School, who meet household income or categorical eligibility criteria for free meals or reduced meals based on one or more of the valid reasons in the 2018-19 School Year.||**low**|object|FRPM| Classification of whether the school has a frpm_perc at or below the 25th percentile.|**med**|object|FRPM| Classification of whether the school has a frpm_perc at or below the 50th percentile.|**high**|object|FRPM| Classification of whether the school has a frpm_perc at or below the 75th percentile.|**very_high**|object|FRPM| Classification of whether the school has a frpm_perc above the 75th percentile.  Exploratory Data AnalysisComplete the following steps to explore your data. You are welcome to do more EDA than the steps outlined here as you feel necessary:1. Summary Statistics.2. Use a **dictionary comprehension** to apply the standard deviation function you create in part 1 to each numeric column in the dataframe.  **No loops**.    - Assign the output to variable `sd` as a dictionary where:         - Each column name is now a key         - That standard deviation of the column is the value         - *Example Output :* `{'ACT_Math': 120, 'ACT_Reading': 120, ...}`3. Investigate trends in the data.    - Using sorting and/or masking (along with the `.head()` method to avoid printing our entire dataframe), consider questions relevant to your problem statement. Some examples are provided below (but feel free to change these questions for your specific problem):        - Which states have the highest and lowest participation rates for the 2017, 2019, or 2019 SAT and ACT?        - Which states have the highest and lowest mean total/composite scores for the 2017, 2019, or 2019 SAT and ACT?        - Do any states with 100% participation on a given test have a rate change year-to-year?        - Do any states show have >50% participation on *both* tests each year?        - Which colleges have the highest median SAT and ACT scores for admittance?        - Which California school districts have the highest and lowest mean test scores?    - **You should comment on your findings at each step in a markdown cell below your code block**. Make sure you include at least one example of sorting your dataframe by a column, and one example of using boolean filtering (i.e., masking) to select a subset of the dataframe.<jupyter_code>#Code:
#filter to only percentage data
sat_act_frpm_merged_perc = sat_act_frpm_merged.filter(axis=1, like='perc').copy()
#create new column with percentage of sat and act test takers
sat_act_frpm_merged_perc['perc_sat_takers'] = sat_act_frpm_merged['num_sat_takers']/sat_act_frpm_merged['enrollment']
sat_act_frpm_merged_perc['perc_act_takers'] = sat_act_frpm_merged['num_act_takers']/sat_act_frpm_merged['enrollment']
#save the csv
sat_act_frpm_merged_perc.to_csv(r'..\data\sat_act_frpm_merged_perc.csv', index = False)
#remove the count from the dataframe
perc_stats = sat_act_frpm_merged_perc.describe().drop('count')
display(perc_stats)<jupyter_output><empty_output><jupyter_text>---Interpreting perc_stats1. The schools in our scope have an average of nearly 60% FRPM eligible students. In fact, around 50% of the schools in our scope have more than 63.9% FRPM eligible students.2. There are no schools in our scope with no FRPM eligible students3. There are schools in our scope with 100% FRPM eligible students4. There was a low average percentage of students who took the SATs and/or the ACTs, at 9.5% and 4.9% respectively, with the maximum being 50.8% and 46.7% respectively.5. SAT Evidence-based reading and writing had the highest percentage of students meeting the benchmark at 66.7%, while Maths and both were under 45%6. The average percentage of students scoring above 21 (composite) in the ACT was 51.3%7. Min percentage for SAT or ACT takers can be 0% as the students may have taken the other test<jupyter_code>#sorting by percentage of students eligible for FRPM to find the top and bottom 5 schools
sat_act_frpm_merged.sort_values(by='frpm_perc')
sat_act_frpm_top_bot_5 = sat_act_frpm_merged.sort_values(by='frpm_perc').head().append(sat_act_frpm_merged.sort_values(by='frpm_perc').tail())
display(sat_act_frpm_top_bot_5)
#printing out the top 5 and bot 5 schools by FRPM percentage
top_5 = sat_act_frpm_top_bot_5.school_name.head().to_list()
bot_5 = sat_act_frpm_top_bot_5.school_name.tail().to_list()
print(f'The 5 CA schools with the lowest percentage of FRPM eligible students are: {top_5}')
print(f'The 5 CA schools with the highest percentage of FRPM eligible students are: {bot_5}')<jupyter_output><empty_output><jupyter_text>---The 5 CA schools with the lowest percentage of FRPM eligible students are: 'Piedmont High', 'Saratoga High', 'Campolindo High', 'Monte Vista High', 'San Ramon Valley High'. These schools have an FRPM percentage of less than 5%. Their percentage of students scoring above 21 for the ACT is over 85%, and for the SATs, the percentage of students meeting the benchmark is over 78%.  The 5 CA schools with the highest percentage of FRPM eligible students are: 'Alliance Judy Ivie Burton Technology Academy High', 'Sylmar Biotech Health Academy', 'Baldwin Park High', 'Sierra Vista High', 'California School for the Deaf-Fremont'. These schools have an FRPM percentage of over 98%. Their percentage of students scoring above 21 for the ACT is under 48%, and for the SATs, the percentage of students meeting the benchmark is under 43%.     It appears that where there is a higher percentage of students who are eligible for FRPM within the schools, a lower percentage of students achieve ACT scores above 21, or meet the SAT benchmarks<jupyter_code>#create function to sort very_high, high, med, low
def high_med_low(v):
    if v <= frpm_25:
        return 'low' #low if 25th percentile or lower
    elif v <= frpm_median:
        return 'med' #med if between 25th percentile and 50th percentile (latter inclusive)
    elif v <= frpm_75:
        return 'high' #high if between 50th percentile and 75th percentile (latter inclusive)
    else:
        return 'very_high' #very_high if above 75th percentile
#adding column for percentage of students eligible for FRPM into bins (based on quartile)
frpm_25 = perc_stats['frpm_perc']['25%']
frpm_median = perc_stats['frpm_perc']['50%']
frpm_75 = perc_stats['frpm_perc']['75%']
sat_act_frpm_merged_perc['frpm_low_med_high'] = sat_act_frpm_merged_perc['frpm_perc'].map(high_med_low)
#group by high or low status
groups = sat_act_frpm_merged_perc.groupby('frpm_low_med_high')
high_low_mean = groups.mean()
high_low_mean.sort_values('frpm_perc')

# np.where(sat_act_frpm_merged_perc['frpm_perc'] < frpm_median, 'low', 'high')<jupyter_output><empty_output><jupyter_text>After having classified the schools by their FRPM student percentage, it appears that the previous trend observed in the top and bottom 5 schools continues - that higher average frpm_perc shows lower average ACT and SAT achievements.  It is interesting to note that schools with a very high FRPM student percentage appear to have a higher average percentage of students taking the SATs and ACTs.<jupyter_code>#identify total number of students in the dataset
student_pop = sat_act_frpm_merged.enrollment.sum()
#identify total number of frpm eligible students in the dataset
frpm_student_pop = sat_act_frpm_merged.frpm_count.sum()
#identify total number of non-frpm eligible students in the dataset
non_frpm_student_pop = student_pop - frpm_student_pop
#convert to percentages
frpm_student_pop_perc = frpm_student_pop/student_pop
non_frpm_student_pop_perc = non_frpm_student_pop/student_pop
print(f'There is a total student enrollment of {student_pop} in our dataset.')
print(f'There is a total FRPM count of {frpm_student_pop} in the dataset.')
print(f'This means that in our dataset, {round(non_frpm_student_pop_perc*100)}% of the students are not FRPM eligible while {round(frpm_student_pop_perc*100)}% of the students are FRPM eligible.')<jupyter_output>There is a total student enrollment of 1852294 in our dataset.
There is a total FRPM count of 1037346 in the dataset.
This means that in our dataset, 44% of the students are not FRPM eligible while 56% of the students are FRPM eligible.<jupyter_text>Among the 1283 schools within our dataset, the total student population is 1852294 and the total FRPM count is 1037346. This works out to around 56% of the entire student population in the dataset being eligible for FRPM.If there is indeed a negative correlation between FRPM percentages and ACT and SAT performance, then the population of students needing help is quite large.  Hypothesis testing 𝐻0: There is no correlation between percentage of FRPM eligible students in Californian schools and percentage of students in the same schools who scored above 21 for the ACT or met the college board benchmark for the SAT  𝐻0: **rho** = 0 𝐻𝐴: There is a correlation between percentage of FRPM eligible students in Californian schools and percentage of students in the same schools who scored above 21 for the ACT or met the college board benchmark for the SAT  𝐻𝐴: **rho** ≠ 0Independent variable = frpm_perc  Dependent variables = act_perc_above_21, sat_perc_both_benchmarksSignificance level: 2.5% (adjusted down for the Bonferroni correction)<jupyter_code>#test frpm_perc with act_perc_above_21
#first filter to only schools where students took the ACT to avoid nan data
act_frpm = sat_act_frpm_merged_perc[sat_act_frpm_merged_perc['act_perc_above_21'] > 0]
#conduct Pearson's correlation coefficient test
ttest1 = stats.pearsonr(act_frpm['frpm_perc'], act_frpm['act_perc_above_21'])
#identify the correlation coefficient
t1 = ttest1[0]
#identify the p-value
p1 = ttest1[1]
#filter to only schools where students took the SAT to avoid nan data
sat_frpm = sat_act_frpm_merged_perc[sat_act_frpm_merged_perc['sat_perc_both_benchmarks'] > 0]
#conduct Pearson's correlation coefficient test
ttest2 = stats.pearsonr(sat_frpm['frpm_perc'], sat_frpm['sat_perc_both_benchmarks'])
#identify the correlation coefficient
t2 = ttest2[0]
#identify the p-value
p2 = ttest2[1]
print(f'The correlation coefficient between frpm_perc and act_perc_above_21 is {t1} and the p-value is {p1}.') 
print(f'The correlation coefficient between frpm_perc and sat_perc_both_benchmarks is {t2} and the p-value is {p2}.')<jupyter_output>The correlation coefficient between frpm_perc and act_perc_above_21 is -0.8401169556698439 and the p-value is 1.6064809915999597e-269.
The correlation coefficient between frpm_perc and sat_perc_both_benchmarks is -0.8369529417771113 and the p-value is 0.0.<jupyter_text>Given the extremely low p-values of both tests, the null hypothesis can be rejected and we can accept the alternative hypothesis that there is a correlation between percentage of FRPM eligible students in Californian schools and percentage of students in the same schools who scored above 21 for the ACT or met the college board benchmark for the SAT.This correlation is strongly negative as given in the correlation coefficients above. ---  Visualize the DataThere's not a magic bullet recommendation for the right number of plots to understand a given dataset, but visualizing your data is *always* a good idea. Not only does it allow you to quickly convey your findings (even if you have a non-technical audience), it will often reveal trends in your data that escaped you when you were looking only at numbers. It is important to not only create visualizations, but to **interpret your visualizations** as well.**Every plot should**:- Have a title- Have axis labels- Have appropriate tick labels- Text is legible in a plot- Plots demonstrate meaningful and valid relationships- Have an interpretation to aid understandingHere is an example of what your plots should look like following the above guidelines. Note that while the content of this example is unrelated, the principles of visualization hold:![](https://snag.gy/hCBR1U.jpg)*Interpretation: The above image shows that as we increase our spending on advertising, our sales numbers also tend to increase. There is a positive correlation between advertising spending and sales.*---Here are some prompts to get you started with visualizations. Feel free to add additional visualizations as you see fit:1. Use Seaborn's heatmap with pandas `.corr()` to visualize correlations between all numeric features.    - Heatmaps are generally not appropriate for presentations, and should often be excluded from reports as they can be visually overwhelming. **However**, they can be extremely useful in identify relationships of potential interest (as well as identifying potential collinearity before modeling).    - Please take time to format your output, adding a title. Look through some of the additional arguments and options. (Axis labels aren't really necessary, as long as the title is informative).2. Visualize distributions using histograms. If you have a lot, consider writing a custom function and use subplots.    - *OPTIONAL*: Summarize the underlying distributions of your features (in words & statistics)         - Be thorough in your verbal description of these distributions.         - Be sure to back up these summaries with statistics.         - We generally assume that data we sample from a population will be normally distributed. Do we observe this trend? Explain your answers for each distribution and how you think this will affect estimates made from these data.3. Plot and interpret boxplots.     - Boxplots demonstrate central tendency and spread in variables. In a certain sense, these are somewhat redundant with histograms, but you may be better able to identify clear outliers or differences in IQR, etc.    - Multiple values can be plotted to a single boxplot as long as they are of the same relative scale (meaning they have similar min/max values).    - Each boxplot should:        - Only include variables of a similar scale        - Have clear labels for each variable        - Have appropriate titles and labels4. Plot and interpret scatter plots to view relationships between features. Feel free to write a custom function, and subplot if you'd like. Functions save both time and space.    - Your plots should have:        - Two clearly labeled axes        - A proper title        - Colors and symbols that are clear and unmistakable5. Additional plots of your choosing.    - Are there any additional trends or relationships you haven't explored? Was there something interesting you saw that you'd like to dive further into? It's likely that there are a few more plots you might want to generate to support your narrative and recommendations that you are building toward. **As always, make sure you're interpreting your plots as you go**.<jupyter_code>#show a table of the percentage stats data
display(perc_stats)
#plot the data into a boxplot
perc_stats.boxplot(figsize=(8,6), rot=14)
plt.title("Boxplot of percentages from the dataset",fontsize=20)
plt.xlabel("Statistic")
plt.ylabel("Percentage")<jupyter_output><empty_output><jupyter_text>Interpreting the boxplots:The distributions for the various percentages appear to be non-normal, although sat_perc_both_benchmarks is close to normal.Most of the schools in the dataset have a high percentage of FRPM students, with a median of around 63.9%, and 75% of the schools having a FRPM student population of over  38.65%.Most of the schools with students taking the SATs in the dataset have median sat_perc_both_benchmarks lower than 0.5, and this appears to be due to less students meeting the SAT benchmark for Mathematics rather than Evidence-based Reading and Writing, which has a higher median than the former.Most of the schools with students taking the ACTs in the dataset have median act_perc_above_21 around 0.5.  While there were schools where 100% of students met the SAT benchmark for Maths and both subjects, these were considered outliers as they were above 1.5 times the interquartile range.  The percentage of students who took the SATs or ACTs among the schools was low, with the average being 9.5% and 4.9% respectively. Having close to 50% of the students taking the SATs or ACTs is considered an outlier.<jupyter_code>#plotting a histogram of the counts of the percentages to check distribution
sat_act_frpm_merged_perc.hist(figsize = (18,10),bins=25, sharex = True, sharey=True)
plt.suptitle('Histogram of counts of schools for the various percentages')
plt.xlabel('Percentage')
plt.ylabel('Count')<jupyter_output><empty_output><jupyter_text>---Based on the above histograms, the percentages for each category do not appear to be normally distributed. This could be due to the fact that we are looking at percentages, and a beta distribution would likely fit the data better.<jupyter_code>#investigate correlations with a correlations heatmap
sat_act_frpm_merged.corr() #correlation of dataset
#create mask to mask the upper triangle which shows the same information
mask = np.triu(np.ones(sat_act_frpm_merged.corr().shape)) #create mask for the correlation
plt.figure(figsize=(12,10))
sns.heatmap(sat_act_frpm_merged.corr(),annot=True, mask=mask) #plot heatmap for correlation
plt.title("Heatmap of correlations", fontsize=24)<jupyter_output><empty_output><jupyter_text>Interpreting the heatmap:There appears to be a clear negative correlation between FRPM (especially percentage) and meeting the SAT benchmarks or achieving higher ACT scores<jupyter_code># Plot a scatter plot to show all the frpm_perc data
plt.figure(figsize=(10,10))
ax = plt.gca()
plt.title("Scatter plot of FRPM percentage and test performance percentage", fontsize = 15)
ax1 = sat_act_frpm_merged.plot(kind='scatter', x=['frpm_perc'], y=['act_perc_above_21'], color='r', ax=ax, label = 'act_perc_above_21', alpha=0.7)    
ax2 = sat_act_frpm_merged.plot(kind='scatter', x=['frpm_perc'], y=['sat_perc_read_write_benchmark'], color='g', ax=ax, label = 'sat_perc_read_write_benchmark', alpha=0.3)    
ax3 = sat_act_frpm_merged.plot(kind='scatter', x=['frpm_perc'], y=['sat_perc_math_benchmark'], color='b', ax=ax, label = 'sat_perc_math_benchmark', alpha=0.7)
ax4 = sat_act_frpm_merged.plot(kind='scatter', x=['frpm_perc'], y=['sat_perc_both_benchmarks'], color='y', ax=ax, label = 'sat_perc_both_benchmarks', alpha=0.7)
plt.ylabel('Percentage meeting the score or benchmark')
plt.xlabel('Percentage of FRPM-eligible students in the school')<jupyter_output><empty_output><jupyter_text>Similar to what was seen in the heatmap, there appears to be a negative correlation between percentage of FRPM eligible students in the school and the percentage of students meeting the SAT benchmarks or scoring above 21 for the ACTs.  Students seem to have performed better in the SAT evidenced-based reading and writing test<jupyter_code>#plot a bar chart for the top 5 and bottom 5 schools in frpm_perc
plt.figure(figsize=(10,6))
ax = plt.gca()
sat_act_frpm_top_bot_5[['act_perc_above_21','sat_perc_read_write_benchmark','sat_perc_math_benchmark','sat_perc_both_benchmarks']].plot(kind='bar', ax = ax, rot = 40)
plt.xticks(ticks = (0,1,2,3,4,5,6,7,8,9), labels = sat_act_frpm_top_bot_5.school_name)
plt.title('Bar chart showing Bottom 5 (left) and top 5 (right) schools in terms of FRPM percentage and their percentages of students meeting the benchmarks', fontsize = 14)
plt.ylabel('Percentage meeting the score or benchmark')
plt.xlabel('School')<jupyter_output><empty_output><jupyter_text>The bottom 5 schools means that their FRPM percentage student population are the lowest, while the top 5 have the highest FRPM percentages.  Once again we can see the trend that when the FRPM percentage is high, the percentage of students achieving the benchmarks is lower than when the FRPM percentage is low. In fact, nearly 100% of students in the schools with the lowest FRPM percentages who took the SATs or ACTs met the benchmarks, while less than 50% of the students in the schools with the highest FRPM percentages who took the SATs or ACTs met the benchmarks (excluding Evidence-based reading and writing).<jupyter_code>#plot a bar chart for the various bins which we have classified the schools on based on their frpm_perc
plt.figure(figsize=(10,6))
ax = plt.gca()
high_low_mean.sort_values(by='frpm_perc').drop('frpm_perc', axis = 1).plot(kind='bar', ax = ax, rot=0)
plt.title("Bar chart of percentages for low, medium, high, and very high FRPM schools", fontsize = 15)
plt.ylabel('Percentage meeting the score or benchmark')
plt.xlabel('Percentage of FRPM-eligible students in the school sorted in bins')<jupyter_output><empty_output><jupyter_text>Looking at the bar charts where the schools have been grouped into bins based on their FRPM percentage, the trend once again shows itself, that schools with lower FRPM percentage appear to perform better than those with higher FRPM percentage.<jupyter_code># Create stacked bar chart to show frpm and non-frpm student population
plt.bar(1, non_frpm_student_pop_perc, color='#b5ffb9', edgecolor='white', width=barWidth, label='Non-FRPM student population')
plt.bar(1, frpm_student_pop_perc, bottom=non_frpm_student_pop_perc, color='#a3acff', edgecolor='white', width=barWidth, label='FRPM student population')
plt.xticks(ticks=[])
plt.xlabel("Student population")
plt.ylabel("Percentage")
plt.title("FRPM and non-FRPM student population in the dataset")
plt.legend(loc='upper left', bbox_to_anchor=(1,1), ncol=1)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0854862055353305, 'fraction_numerical': 0.024935304180007896, 'mean_word_length': 5.5980321227029375, 'pattern_counts': {'":': 11, '<': 71, '<?xml version=': 0, '>': 70, 'https://': 21, 'lorem ipsum': 0, 'www.': 14, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '43094', 'license': 'Apache-2.0', 'n_tokens_mistral': 14060, 'n_tokens_neox': 12784, 'n_words': 5397, 'path': 'code/starter-code.ipynb', 'repo_name': 'calrencet/standardizedtestperformance', 'size': '45598'}