nerds-learning-final.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NERDS Data Analysis with Python and Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#these are the libraries needed to do this work. Are there more?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This project is intended to gauge whether there are funding discrepancies within Bexar County Independent School Districts when it comes to per-pupil spending and economic disadvantage rates. <br> \n",
    "If a discrepancy is detected (low correlation coefficient…threshold TBD…or there are outlier schools on the resulting scatterplot, we can work with an education reporter to investigate further. <br>\n",
    "The data come from two sources: \n",
    "- The NERD$ data come from Georgetown University's [National Education Resource Database on School Spending Organization](https://georgetown.app.box.com/s/1dknmu4bjltrehzdygh63xnzebcki4ni/file/1060770984817) and represent the 2018-2019 academic year. <br>\n",
    "- The Economic Disadvantage Data comes from [Square Meals.org](https://data.texas.gov/dataset/School-Nutrition-Programs-Contact-Information-and-/jezb-2499) and also represent the 2018-2019 academic year.<br>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "nerds_full = pd.read_csv(\"essential-data/tx-nerds-2018_19.csv\", dtype={'schoolid_stateassigned':str})\n",
    "ed_full = pd.read_csv(\"essential-data/tx-sch_lunch-2018-19.csv\", dtype={'SiteID':str, 'distid':str})\n",
    "\n",
    "ed_full['school_id'] = ed_full['distid'] + ed_full['SiteID'].str.zfill(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ed_full.groupby(['SiteCounty','distid'])['SiteISP'].size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#I don't know how to quickly make this: I performed the groupby in the previous cell, cut and pasted it into these brackets, and then deleted all the other crap. It seems like there should have been another way.\n",
    "#Allie cut and pasted from Excel. \n",
    "isds_tokeep = [130901, 15901, 15904, 15907, 15908, 15909, 15910, 15911, 15912, 15913, 15914, 15915, 15916, 15917, 163908, 46902, 46902, 94902, 130901, 163908]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ed_bexar = ed_full.loc[ed_full['distid'].isin(isds_tokeep)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CEID', 'distname', 'SiteID', 'schoolname', 'distid', 'CECounty',\n",
       "       'SiteCounty', 'SiteISP', 'school_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_bexar.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['distid', 'schoolid_stateassigned', 'distname', 'schoolname',\n",
       "       'enroll_raw_TX', 'pp_stloc_raw_TX'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nerds_full.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CEID', 'distname', 'SiteID', 'schoolname', 'distid', 'CECounty',\n",
       "       'SiteCounty', 'SiteISP', 'school_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_full.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_data = pd.merge(nerds_full, ed_full, left_on='schoolid_stateassigned',right_on='school_id',how='right',suffixes=('_nerds','_ed'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_data.to_csv(\"/Users/wratko/Documents/projects/tx-school_funding/jupyter-notebooks/essential-data/complete_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['distid_nerds', 'schoolid_stateassigned', 'distname_nerds',\n",
       "       'schoolname_nerds', 'enroll_raw_TX', 'pp_stloc_raw_TX', 'CEID',\n",
       "       'distname_ed', 'SiteID', 'schoolname_ed', 'distid_ed', 'CECounty',\n",
       "       'SiteCounty', 'SiteISP', 'school_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HISD BOOT DAEP MS',\n",
       " 'TEJEDA JUNIOR ACADEMY',\n",
       " 'HISD BOOT DAEP HS',\n",
       " 'HAC DAEP MS',\n",
       " 'COLLIER DAEP',\n",
       " 'LUCKEY RANCH ELEMENTARY',\n",
       " 'BEXAR COUNTY JJAEP',\n",
       " 'LOSOYA MIDDLE SCHOOL']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(complete_data.loc[~(pd.to_numeric(complete_data['pp_stloc_raw_TX'],errors='coerce').notnull())]['schoolname_ed'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_data = complete_data.loc[pd.to_numeric(complete_data['pp_stloc_raw_TX'], errors='coerce').notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_data['pp_stloc_raw_TX'] = complete_data['pp_stloc_raw_TX'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/wg/0c8vsdgx6cj6d68fjsm44xnh0000gq/T/ipykernel_18479/2155754127.py:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  bexar_corr = complete_data.groupby('distname_nerds')['pp_stloc_raw_TX','SiteISP'].corr().reset_index()\n"
     ]
    }
   ],
   "source": [
    "bexar_corr = complete_data.groupby('distname_nerds')['pp_stloc_raw_TX','SiteISP'].corr().reset_index()\n",
    "bexar_corr = bexar_corr.loc[bexar_corr['level_1'] == 'pp_stloc_raw_TX']\n",
    "bexar_corr = bexar_corr[['distname_nerds','level_1','SiteISP']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distname_nerds</th>\n",
       "      <th>level_1</th>\n",
       "      <th>SiteISP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>EAST CENTRAL ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.717635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>SOUTHSIDE ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.658392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALAMO HEIGHTS ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.553086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>SCHERTZ-CIBOLO-U CITY ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.456589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>NORTH EAST ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.352962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>JUDSON ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.281779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>SOUTHWEST ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.271387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>SOUTH SAN ANTONIO ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.220388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>MEDINA VALLEY ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.166267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>COMAL ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.093254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOERNE ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.088475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NORTHSIDE ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>0.003781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>SAN ANTONIO ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>-0.017022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>HARLANDALE ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>-0.139447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>SOMERSET ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>-0.638109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>LACKLAND ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FT SAM HOUSTON ISD</td>\n",
       "      <td>pp_stloc_raw_TX</td>\n",
       "      <td>-1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               distname_nerds          level_1   SiteISP\n",
       "6            EAST CENTRAL ISD  pp_stloc_raw_TX  0.717635\n",
       "30              SOUTHSIDE ISD  pp_stloc_raw_TX  0.658392\n",
       "0           ALAMO HEIGHTS ISD  pp_stloc_raw_TX  0.553086\n",
       "24  SCHERTZ-CIBOLO-U CITY ISD  pp_stloc_raw_TX  0.456589\n",
       "18             NORTH EAST ISD  pp_stloc_raw_TX  0.352962\n",
       "12                 JUDSON ISD  pp_stloc_raw_TX  0.281779\n",
       "32              SOUTHWEST ISD  pp_stloc_raw_TX  0.271387\n",
       "28      SOUTH SAN ANTONIO ISD  pp_stloc_raw_TX  0.220388\n",
       "16          MEDINA VALLEY ISD  pp_stloc_raw_TX  0.166267\n",
       "4                   COMAL ISD  pp_stloc_raw_TX  0.093254\n",
       "2                  BOERNE ISD  pp_stloc_raw_TX  0.088475\n",
       "20              NORTHSIDE ISD  pp_stloc_raw_TX  0.003781\n",
       "22            SAN ANTONIO ISD  pp_stloc_raw_TX -0.017022\n",
       "10             HARLANDALE ISD  pp_stloc_raw_TX -0.139447\n",
       "26               SOMERSET ISD  pp_stloc_raw_TX -0.638109\n",
       "14               LACKLAND ISD  pp_stloc_raw_TX -1.000000\n",
       "8          FT SAM HOUSTON ISD  pp_stloc_raw_TX -1.000000"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bexar_corr.sort_values('SiteISP', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "23c9b771267491a6d8b0211014be734acb79cef3e9f9f53b1bbe656671aa7312"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}