{
"metadata": {
"name": "",
"signature": "sha256:10348502399fa1356ea0f92bff5aa4a9036059619b337a747de5f414baff2786"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"California Demographics Descriptive Statistics"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Summary**\n",
"\n",
"We furthered our analysis on referrals/suspensions by analyzing the California data set. \n",
"\n",
"By doing descriptive statistics on the data set of total referrals by schools we found out that the standard deviation of these data sets are actually higher than their mean. Since the entries must be nonzeroes, this implies that a small groups of schools whcih have an exceptionally high number of referrals brought up the standard deviation. In hope of explaining this unusualness, we looked at the racial composition of schools and found out that schools with higher percentage of ethnic group 5 and 6 are more likely to have higher referrals per person compared to other schools.\n",
"\n",
"*This shows the process of extracting useful information from the California Discipline and Referrals Dataset. Methods for extraction and visualization are found below*"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import pandas as pd\n",
"import pylab as P\n",
"import matplotlib.pyplot as plt\n",
"import numpy as np\n",
"from collections import Counter\n",
"from nltk import FreqDist\n",
"%matplotlib inline "
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Some methods I wrote for an easier time subsetting and concatnating datasets. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"*See comments for individual descriptions.*"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# read in file as pandas dataframe\n",
"def read_in_file(filename):\n",
" data = pd.read_csv(filename)\n",
" return data\n",
"\n",
"# for sanity checks of data within columns\n",
"# prints out the unique categorical values found in specified columns\n",
"def get_unique_values(df, verbose=False):\n",
" column_names = list(df)\n",
" agg_levels = pd.unique(df[column_names[0]])\n",
" names = pd.unique(df[column_names[2]])\n",
" discip_type = pd.unique(df[column_names[3]])\n",
" ethnic_groups = pd.unique(df[column_names[4]])\n",
" if verbose:\n",
" print(\"Aggregate Levels are: %s\" % agg_levels)\n",
" print(\"Names of schools are: %s\" % names)\n",
" print(\"Discipline types are: %s\" % discip_type)\n",
" print(\"Ethnic groups are: %s\" % ethnic_groups)\n",
" return\n",
"\n",
"# subset the data by AGGREGATION LEVEL\n",
"# D=Local educational agency totals (includes districts and direct funded charter schools)\n",
"# O=County totals\n",
"# S=SchoolTotals\n",
"# T=State totals\n",
"def subset_by_agg_level(df):\n",
" column_names = list(df)\n",
" county_data = df[df.AggegateLevel == 'O']\n",
" district_data = df[df.AggegateLevel == 'D']\n",
" school_data = df[df.AggegateLevel == 'S']\n",
" state_data = df[df.AggegateLevel == 'T']\n",
" # label columns in subset:\n",
" county_data.columns = column_names\n",
" district_data.columns = column_names\n",
" school_data.columns = column_names\n",
" state_data.columns = column_names\n",
" return county_data, district_data, school_data, state_data\n",
"\n",
"# export a specified dataframe to csv\n",
"def export_df_to_csv(df, output_filename):\n",
" df.to_csv(output_filename, index=False, header=True)\n",
" return\n",
"\n",
"# grab a subset of a dataframe by column indices\n",
"def subset_cols_dataframe(df, col_indexes):\n",
" subset = df.iloc[:, col_indexes]\n",
" return subset\n",
"\n",
"# append together an array of dataframes(any number) in order\n",
"# example: array_df = [dataframe1, dataframe2, dataframe3]\n",
"def append_dataframes(array_df):\n",
" temp = array_df[0]\n",
" for i in range(1, len(array_df)):\n",
" temp = temp.append(array_df[i])\n",
" return temp"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 2
},
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"Parsing the California Dataset text files into csv form:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# this chunk of code just takes the input csv and subsets it\n",
"ca14 = read_in_file('CA_discip14.csv') # 2014 California Data saved in ca14\n",
"county_data14, district_data14, school_data14, state_data14 = subset_by_agg_level(ca14)\n",
"ca13 = read_in_file('CA_discip13.csv') # 2013 California Data saved in ca13\n",
"county_data13, district_data13, school_data13, state_data13 = subset_by_agg_level(ca13)\n",
"ca12 = read_in_file('CA_discip12.csv') # 2012 California Data saved in ca12\n",
"county_data12, district_data12, school_data12, state_data12 = subset_by_agg_level(ca12)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 3
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Getting a quick overview of values contained in the data (sanity check):"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"print(\"2012 data:\")\n",
"get_unique_values(ca12, verbose=True)\n",
"print(\"2013 data:\")\n",
"get_unique_values(ca13, verbose=True)\n",
"print(\"2014 data:\")\n",
"get_unique_values(ca14, verbose=True)\n",
"print(\"-----------------------------------------------------------------------------------------\")"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"2012 data:\n",
"Aggregate Levels are: ['O' 'D' 'S' 'T' nan]\n",
"Names of schools are: ['Alameda' 'Alpine' 'Amador' ..., 'Wheatland Community Day High' 'State'\n",
" nan]\n",
"Discipline types are: ['E' 'I' 'O' nan]\n",
"Ethnic groups are: [ 2. 6. 0. 4. 5. 1. 3. 9. 7. nan]\n",
"2013 data:\n",
"Aggregate Levels are: ['O' 'D' 'S' 'T' nan]\n",
"Names of schools are: ['Alameda' 'Alpine' 'Amador' ..., 'Wheatland Community Day High' 'State'\n",
" nan]\n",
"Discipline types are: ['E' 'I' 'O' nan]\n",
"Ethnic groups are: [ 2. 6. 4. 5. 1. 3. 9. 7. 0. nan]\n",
"2014 data:\n",
"Aggregate Levels are: ['O' 'D' 'S' 'T' nan]\n",
"Names of schools are: ['Alameda' 'Alpine' 'Amador' ..., 'Wheatland Community Day High' 'State'\n",
" nan]\n",
"Discipline types are: ['E' 'I' 'O' nan]\n",
"Ethnic groups are: [ 2. 6. 0. 4. 5. 3. 9. 7. 1. nan]\n",
"-----------------------------------------------------------------------------------------\n"
]
}
],
"prompt_number": 4
},
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"Querying the Imported Data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"\n",
"Since the data imported makes sense (categorial values are as expected, did not split names incorrectly, etc), now we want to **subset the data** with respect to columns we actually need for each analyses. \n",
"* I made a method earlier called **subset_cols_dataframe(df, col_indexes)** to do this easily. \n",
"* There is also a method called **append_dataframes(array_df)** which takes in any size array of dataframes and appends them together. I use this method to put together all 3 years worth of data."
]
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Looking at Each School's Total Referrals Over Time"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# creating the useful dataframe for visualizing change in schools over time\n",
"indices_schools = [2, -2,-1] # for this information we only need the name of school, total referrals, and year\n",
"\n",
"schools14 = subset_cols_dataframe(school_data14, indices_schools) # slicing out columns for each year\n",
"schools13 = subset_cols_dataframe(school_data13, indices_schools)\n",
"schools12 = subset_cols_dataframe(school_data12, indices_schools)\n",
"\n",
"school_totals = append_dataframes([schools14, schools13, schools12]) # appending all years together"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 5
},
{
"cell_type": "heading",
"level": 3,
"metadata": {},
"source": [
"Now add up the totals with respect to each school:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"by_school = school_totals.groupby(['Name','Year']).sum()\n",
"print(\"Preview of groupby product: \")\n",
"print(by_school[0:10])"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"Preview of groupby product: \n",
" Total\n",
"Name Year \n",
"100 Black Men of the Bay Area Community 2013 0\n",
" 2014 0\n",
"180 Program 2013 0\n",
"A. E. Arnold Elementary 2012 0\n",
" 2013 0\n",
" 2014 0\n",
"A. G. Currie Middle 2012 84\n",
" 2013 88\n",
" 2014 137\n",
"A. J. Cook Elementary 2013 0\n"
]
}
],
"prompt_number": 6
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Descriptive statistics on the totals for each school every year:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"print(by_school.describe())"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
" Total\n",
"count 20885.000000\n",
"mean 51.283649\n",
"std 115.911895\n",
"min 0.000000\n",
"25% 0.000000\n",
"50% 14.000000\n",
"75% 54.000000\n",
"max 3536.000000\n"
]
}
],
"prompt_number": 7
},
{
"cell_type": "heading",
"level": 3,
"metadata": {},
"source": [
"*Thoughts on these numbers:*\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As you can see, there is a **very high standard deviation** of referrals between schools. What does this tell us? \n",
"* The range of data, especially at the tails, is very large\n",
"* Why? Look at the **max number** of referrals at one school.\n",
"\n",
"Most schools (75%) have **54 referrals or less a year**."
]
},
{
"cell_type": "heading",
"level": 3,
"metadata": {},
"source": [
"Lets take a closer look at what is causing this skew in the data:"
]
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"*Schools with the the most referrals:*"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"by_school.sort('Total', ascending=False).head(50)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" | \n",
" Total | \n",
"
\n",
" \n",
" | Name | \n",
" Year | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | Franklin Middle | \n",
" 2012 | \n",
" 3536 | \n",
"
\n",
" \n",
" | Garey High | \n",
" 2012 | \n",
" 2927 | \n",
"
\n",
" \n",
" | Blaker-Kinser Junior High | \n",
" 2012 | \n",
" 2312 | \n",
"
\n",
" \n",
" | Merrill F. West High | \n",
" 2012 | \n",
" 2248 | \n",
"
\n",
" \n",
" | Vallejo High | \n",
" 2012 | \n",
" 1963 | \n",
"
\n",
" \n",
" | Southwest High | \n",
" 2013 | \n",
" 1917 | \n",
"
\n",
" \n",
" | Central Union High | \n",
" 2012 | \n",
" 1890 | \n",
"
\n",
" \n",
" | Franklin Middle | \n",
" 2014 | \n",
" 1682 | \n",
"
\n",
" \n",
" | Encina Preparatory High | \n",
" 2012 | \n",
" 1660 | \n",
"
\n",
" \n",
" | Fontana High | \n",
" 2012 | \n",
" 1626 | \n",
"
\n",
" \n",
" | East Bakersfield High | \n",
" 2013 | \n",
" 1617 | \n",
"
\n",
" \n",
" | Oasis Community | \n",
" 2013 | \n",
" 1597 | \n",
"
\n",
" \n",
" | Charles W. Tewinkle Middle | \n",
" 2012 | \n",
" 1550 | \n",
"
\n",
" \n",
" | Los Banos Junior High | \n",
" 2012 | \n",
" 1479 | \n",
"
\n",
" \n",
" | Eisenhower Senior High | \n",
" 2012 | \n",
" 1448 | \n",
"
\n",
" \n",
" | Southwest High | \n",
" 2012 | \n",
" 1429 | \n",
"
\n",
" \n",
" | Calexico High | \n",
" 2013 | \n",
" 1322 | \n",
"
\n",
" \n",
" | Silverado High | \n",
" 2012 | \n",
" 1310 | \n",
"
\n",
" \n",
" | San Joaquin County Community | \n",
" 2012 | \n",
" 1299 | \n",
"
\n",
" \n",
" | Hogan Middle | \n",
" 2014 | \n",
" 1279 | \n",
"
\n",
" \n",
" | Phoenix High Community Day | \n",
" 2012 | \n",
" 1264 | \n",
"
\n",
" \n",
" | Bakersfield High | \n",
" 2013 | \n",
" 1230 | \n",
"
\n",
" \n",
" | Solano Middle | \n",
" 2012 | \n",
" 1157 | \n",
"
\n",
" \n",
" | Tioga Middle | \n",
" 2012 | \n",
" 1155 | \n",
"
\n",
" \n",
" | Franklin Middle | \n",
" 2013 | \n",
" 1152 | \n",
"
\n",
" \n",
" | John Muir Middle | \n",
" 2012 | \n",
" 1138 | \n",
"
\n",
" \n",
" | South High | \n",
" 2013 | \n",
" 1123 | \n",
"
\n",
" \n",
" | Alisal High | \n",
" 2013 | \n",
" 1121 | \n",
"
\n",
" \n",
" | Washington Middle | \n",
" 2012 | \n",
" 1120 | \n",
"
\n",
" \n",
" | San Leandro High | \n",
" 2012 | \n",
" 1115 | \n",
"
\n",
" \n",
" | Eisenhower Senior High | \n",
" 2013 | \n",
" 1103 | \n",
"
\n",
" \n",
" | Victor Valley High | \n",
" 2012 | \n",
" 1076 | \n",
"
\n",
" \n",
" | Cobalt Institute of Math and Science Academy | \n",
" 2012 | \n",
" 1074 | \n",
"
\n",
" \n",
" | Estancia High | \n",
" 2012 | \n",
" 1055 | \n",
"
\n",
" \n",
" | Ukiah High | \n",
" 2012 | \n",
" 1041 | \n",
"
\n",
" \n",
" | Gifford C. Cole Middle | \n",
" 2012 | \n",
" 1033 | \n",
"
\n",
" \n",
" | Nidorf Barry J. Juvenile Hall | \n",
" 2012 | \n",
" 1014 | \n",
"
\n",
" \n",
" | Calexico High | \n",
" 2012 | \n",
" 1012 | \n",
"
\n",
" \n",
" | Littlerock High | \n",
" 2013 | \n",
" 1000 | \n",
"
\n",
" \n",
" | Sequoia Middle | \n",
" 2012 | \n",
" 990 | \n",
"
\n",
" \n",
" | Los Padrinos Juvenile Hall | \n",
" 2013 | \n",
" 979 | \n",
"
\n",
" \n",
" | Modesto High | \n",
" 2012 | \n",
" 968 | \n",
"
\n",
" \n",
" | Lincoln High | \n",
" 2012 | \n",
" 954 | \n",
"
\n",
" \n",
" | Bear Creek High | \n",
" 2013 | \n",
" 953 | \n",
"
\n",
" \n",
" | Alisal High | \n",
" 2012 | \n",
" 953 | \n",
"
\n",
" \n",
" | Littlerock High | \n",
" 2014 | \n",
" 949 | \n",
"
\n",
" \n",
" | Fred C. Beyer High | \n",
" 2012 | \n",
" 947 | \n",
"
\n",
" \n",
" | Lindsay Senior High | \n",
" 2013 | \n",
" 942 | \n",
"
\n",
" \n",
" | Saddleback High | \n",
" 2012 | \n",
" 938 | \n",
"
\n",
" \n",
" | Silverado High | \n",
" 2014 | \n",
" 933 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" Weapons | \n",
" Drugs | \n",
" ViolenceWithInjury | \n",
" ViolenceWithoutInjury | \n",
" OtherNonDefiance | \n",
" OtherDefiance | \n",
"
\n",
" \n",
" \n",
" \n",
" | count | \n",
" 27.000000 | \n",
" 27.00000 | \n",
" 27.000000 | \n",
" 27.000000 | \n",
" 27.000000 | \n",
" 27.000000 | \n",
"
\n",
" \n",
" | mean | \n",
" 1698.888889 | \n",
" 5519.62963 | \n",
" 5588.814815 | \n",
" 22907.000000 | \n",
" 3082.592593 | \n",
" 29629.111111 | \n",
"
\n",
" \n",
" | std | \n",
" 2744.328001 | \n",
" 9428.57981 | \n",
" 8030.540436 | \n",
" 33888.038374 | \n",
" 4863.285675 | \n",
" 48120.322021 | \n",
"
\n",
" \n",
" | min | \n",
" 76.000000 | \n",
" 199.00000 | \n",
" 270.000000 | \n",
" 1053.000000 | \n",
" 133.000000 | \n",
" 965.000000 | \n",
"
\n",
" \n",
" | 25% | \n",
" 159.000000 | \n",
" 503.50000 | \n",
" 456.500000 | \n",
" 1558.500000 | \n",
" 287.500000 | \n",
" 1992.500000 | \n",
"
\n",
" \n",
" | 50% | \n",
" 324.000000 | \n",
" 1012.00000 | \n",
" 1015.000000 | \n",
" 4315.000000 | \n",
" 597.000000 | \n",
" 4697.000000 | \n",
"
\n",
" \n",
" | 75% | \n",
" 1801.500000 | \n",
" 5563.50000 | \n",
" 10269.000000 | \n",
" 41724.000000 | \n",
" 4322.000000 | \n",
" 43781.000000 | \n",
"
\n",
" \n",
" | max | \n",
" 9432.000000 | \n",
" 31517.00000 | \n",
" 25863.000000 | \n",
" 111628.000000 | \n",
" 15880.000000 | \n",
" 190815.000000 | \n",
"
\n",
" \n",
"
\n",
"