Language Profiles Map Data - ACS 2015-2019.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "from sodapy import Socrata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The American Community Survey (ACS) contains data on a number of social, economic and demographic indicators. One of that is the languages spoken by limited English proficient (LEP) residents, and the LEP residents that are citizens of voting age (CVALEP).\n",
    "- This type of information is important in helping community groups target and engage with a diverse group of residents. Some examples include producing notices about vaccinations in local languages, providing interpreters who speak certain languages at various poll sites, and ensuring the representation and membership in community boards reflect the diversity of population.\n",
    "- This data comes from the ACS Public Use Microdata Sample (PUMS) 2015-2019 Data, which is available at the Public Use Microdata Area (PUMA) level. There are 55 PUMA's in New York City. \n",
    "- This notebook shows how the data at PUMA level is mapped to 59 Community Districts of NYC, the geographic level at which a lot of programming happens (budget process, land usage, service delivery etc.).\n",
    "- This data also powers the map that we helped create for NYC Civic Engagement Commission, where I served as a lead. [Click here to access the map](https://www1.nyc.gov/site/civicengagement/resources/community-district-language-map.page)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Use the Socrata API to import dataset with PUMA info from NYC Open Data Portal;\n",
    "2. Identify LEP and CVALEP Residents in NYC at PUMA Level;\n",
    "3. Aggregate Data from the PUMA level to:\n",
    "     - LEP and CVALEP % at Community District Level; and \n",
    "     - LEP and CVALEP % by Languages at Community District Level\n",
    "4. Display and export the aggregated data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To begin, first, get your Census API key from here: https://api.census.gov/data/key_signup.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "apiKey = *insert Census API key here*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, get your Socrata (the platform that supports NYC Open Data) [API key and app token from here](https://data.cityofnewyork.us/profile/edit/developer_settings) (or download and import the datasets in the sections in which they occur)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Use the Socrata API to import dataset with PUMA info from NYC Open Data Portal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the census tract to PUMA dataset from the NYC Open Data Portal\n",
    "client = Socrata(\"data.cityofnewyork.us\", insert_app_token_here)\n",
    "nta_nyc_od_link = client.get(\"8ius-dhrr\", limit=5000)\n",
    "\n",
    "nyc_geo_equivalency = pd.DataFrame(nta_nyc_od_link)\n",
    "# adding leading zeroes to make it compatible with other datasets\n",
    "nyc_geo_equivalency[\"_2010_census_tract\"] = \\\n",
    "nyc_geo_equivalency['_2010_census_tract'].map(lambda x: '{0:0>6}'.format(x))\n",
    "\n",
    "nyc_geo_equivalency[\"_2010_census_bureau_fips_county_code\"] = \\\n",
    "nyc_geo_equivalency['_2010_census_bureau_fips_county_code'].map(lambda x: '{0:0>3}'.format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "047    761\n",
       "081    669\n",
       "005    339\n",
       "061    288\n",
       "085    111\n",
       "Name: _2010_census_bureau_fips_county_code, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyc_geo_equivalency['_2010_census_bureau_fips_county_code'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorganize the data at the PUMA level\n",
    "\n",
    "puma_attributes = nyc_geo_equivalency.groupby(['borough', 'puma'])\\\n",
    "['neighborhood_tabulation_area_nta_name'].unique().reset_index()\n",
    "\n",
    "puma_attributes = puma_attributes.drop(columns=['neighborhood_tabulation_area_nta_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add a new column called 'PUMA FIPS Code' in the puma attributes df\n",
    "puma_attributes['PUMA FIPS'] = '7950000US360' + puma_attributes['puma'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(55, 3)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#there are 55 PUMA's in the city\n",
    "puma_attributes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>borough</th>\n",
       "      <th>puma</th>\n",
       "      <th>PUMA FIPS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3701</td>\n",
       "      <td>7950000US3603701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3702</td>\n",
       "      <td>7950000US3603702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3703</td>\n",
       "      <td>7950000US3603703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3704</td>\n",
       "      <td>7950000US3603704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3705</td>\n",
       "      <td>7950000US3603705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  borough  puma         PUMA FIPS\n",
       "0   Bronx  3701  7950000US3603701\n",
       "1   Bronx  3702  7950000US3603702\n",
       "2   Bronx  3703  7950000US3603703\n",
       "3   Bronx  3704  7950000US3603704\n",
       "4   Bronx  3705  7950000US3603705"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "puma_attributes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Identify LEP and CVALEP Residents in NYC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 Identify LEP residents in each PUMA (roughly approximated to a Community District)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### There are three variables to identify LEPs:\n",
    "- Language Other than English Spoken at Home (LANP)\n",
    "- Age (AGE) = +5\n",
    "- Ability to Speak English (ENG). Any level except \"Very Well\" qualifies as a limited english proficient.\n",
    "    - 2 = Well\n",
    "    - 3 = Not Well\n",
    "    - 4 = Not At All\n",
    "\n",
    "PWGTP is the Person Weight to take into account the number of persons in a household."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the limit on the PUMS data go to 100\n",
    "age_string = ''.join([('&AGEP=' + str(i)) for i in range(10, 100)])\n",
    "\n",
    "# concat puma codes into a single string\n",
    "puma_str = ','.join(puma_attributes['PUMA FIPS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# using Census API, import the data for LEPs\n",
    "\n",
    "# 5+ years old Residents who speak English less than \"Very Well\"\n",
    "# Source ACS 5 Year 2015-2019 Public Use Microdata Sample\n",
    "lep_API_summary = \"https://api.census.gov/data/2019/acs/acs5/pums?tabulate=weight(PWGTP)&col+LANP&row+ucgid&ucgid=\" + puma_str + \"&AGEP=05&AGEP=06&AGEP=07&AGEP=08&AGEP=09\" + age_string + \"&ENG=2&ENG=3&ENG=4&key=\" + apiKey\n",
    "\n",
    "#call the API and collect the response\n",
    "response_lep_summary = requests.get(lep_API_summary)\n",
    "\n",
    "#load the response into a JSON\n",
    "formattedResponse_lep_summary = json.loads(response_lep_summary.text)\n",
    "lep_summary_df = pd.DataFrame(formattedResponse_lep_summary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>130</th>\n",
       "      <th>131</th>\n",
       "      <th>132</th>\n",
       "      <th>133</th>\n",
       "      <th>134</th>\n",
       "      <th>135</th>\n",
       "      <th>136</th>\n",
       "      <th>137</th>\n",
       "      <th>138</th>\n",
       "      <th>139</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'LANP': '7300'}</td>\n",
       "      <td>{'LANP': '7039'}</td>\n",
       "      <td>{'LANP': '1970'}</td>\n",
       "      <td>{'LANP': '1231'}</td>\n",
       "      <td>{'LANP': '1132'}</td>\n",
       "      <td>{'LANP': '3420'}</td>\n",
       "      <td>{'LANP': '1730'}</td>\n",
       "      <td>{'LANP': '1515'}</td>\n",
       "      <td>{'LANP': '7019'}</td>\n",
       "      <td>{'LANP': '7032'}</td>\n",
       "      <td>...</td>\n",
       "      <td>{'LANP': '4500'}</td>\n",
       "      <td>{'LANP': '1141'}</td>\n",
       "      <td>{'LANP': '5645'}</td>\n",
       "      <td>{'LANP': '1069'}</td>\n",
       "      <td>{'LANP': '1263'}</td>\n",
       "      <td>{'LANP': '7060'}</td>\n",
       "      <td>{'LANP': '1603'}</td>\n",
       "      <td>{'LANP': '3570'}</td>\n",
       "      <td>{'LANP': '1292'}</td>\n",
       "      <td>ucgid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 140 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                0                 1                 2                 3    \\\n",
       "0  {'LANP': '7300'}  {'LANP': '7039'}  {'LANP': '1970'}  {'LANP': '1231'}   \n",
       "\n",
       "                4                 5                 6                 7    \\\n",
       "0  {'LANP': '1132'}  {'LANP': '3420'}  {'LANP': '1730'}  {'LANP': '1515'}   \n",
       "\n",
       "                8                 9    ...                 130  \\\n",
       "0  {'LANP': '7019'}  {'LANP': '7032'}  ...    {'LANP': '4500'}   \n",
       "\n",
       "                131               132               133               134  \\\n",
       "0  {'LANP': '1141'}  {'LANP': '5645'}  {'LANP': '1069'}  {'LANP': '1263'}   \n",
       "\n",
       "                135               136               137               138  \\\n",
       "0  {'LANP': '7060'}  {'LANP': '1603'}  {'LANP': '3570'}  {'LANP': '1292'}   \n",
       "\n",
       "     139  \n",
       "0  ucgid  \n",
       "\n",
       "[1 rows x 140 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_summary_df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now using an input of language codes, rename the header row to reflect languages.\n",
    "\n",
    "\n",
    "Before running this function, download the PUMS 5-year data dictionary that contains mapping of language codes to languages.\n",
    "\n",
    "For 2015-2019, the data dictionary is located here: https://www.census.gov/programs-surveys/acs/microdata/documentation/2019.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rename_header_languages(df, link_to_pums_data_dictionary):\n",
    "    '''\n",
    "    Rename columns to reflect languages. Download the \n",
    "    \n",
    "    Input:\n",
    "        df: a dataframe with data extracted from ACS PUMS 5-year.\n",
    "        link_to_pums_data_dictionary: \n",
    "    \n",
    "    Returns:\n",
    "        df: input dataframe with column names that reflect languages.\n",
    "        languages_code_dict: a dictionary where each language has a code.\n",
    "    '''\n",
    "    \n",
    "    puma_data_dict_df = pd.read_csv(link_to_pums_data_dictionary)\n",
    "    \n",
    "    # find out the codes of the languages\n",
    "    \n",
    "    languages_codes_dict = puma_data_dict_df[puma_data_dict_df['RT'] == 'LANP']\\\n",
    "        [['Record Type', 'Unnamed: 6']].iloc[2:, :].set_index('Record Type').to_dict()['Unnamed: 6']\n",
    "\n",
    "    # change header row\n",
    "    new_header = []\n",
    "    #grab the first row for the header\n",
    "    for k in df.iloc[0]:\n",
    "        \n",
    "        if type(k) == dict:\n",
    "            \n",
    "            lang_code = k['LANP']\n",
    "            language = languages_codes_dict.get(lang_code)\n",
    "            if language is None:\n",
    "                # this is how Census assigns blank values to languages\n",
    "                language = 'N/A (GQ/Vacant)'\n",
    "            #print(language)\n",
    "            new_header.append(language)\n",
    "        else:\n",
    "            #print(k)\n",
    "            new_header.append(k)\n",
    "\n",
    "    df = df[1:] #take the data less the header row\n",
    "    df.columns = new_header #set the header row as the df header\n",
    "\n",
    "    return df, languages_codes_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_summary_df_renamed, languages_code_dict = \\\n",
    "rename_header_languages(lep_summary_df, insert_location_of_pums_5_year_data_dict_here)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Afrikaans</th>\n",
       "      <th>Akan (incl. Twi)</th>\n",
       "      <th>Albanian</th>\n",
       "      <th>Aleut languages</th>\n",
       "      <th>Amharic</th>\n",
       "      <th>Apache languages</th>\n",
       "      <th>Arabic</th>\n",
       "      <th>Armenian</th>\n",
       "      <th>Assyrian Neo-Aramaic</th>\n",
       "      <th>Bengali</th>\n",
       "      <th>...</th>\n",
       "      <th>Ukrainian</th>\n",
       "      <th>Urdu</th>\n",
       "      <th>Uto-Aztecan languages</th>\n",
       "      <th>Uzbek</th>\n",
       "      <th>Vietnamese</th>\n",
       "      <th>Wolof</th>\n",
       "      <th>Yiddish</th>\n",
       "      <th>Yoruba</th>\n",
       "      <th>Zuni (2016 or earlier)</th>\n",
       "      <th>PUMA Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>281</td>\n",
       "      <td>178</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>79</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>121</td>\n",
       "      <td>...</td>\n",
       "      <td>255</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>7950000US3603701</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 140 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Afrikaans Akan (incl. Twi) Albanian Aleut languages Amharic  \\\n",
       "1         0              281      178               0      65   \n",
       "\n",
       "  Apache languages Arabic Armenian Assyrian Neo-Aramaic Bengali  \\\n",
       "1                0     79        0                   12     121   \n",
       "\n",
       "         ...        Ukrainian Urdu Uto-Aztecan languages Uzbek Vietnamese  \\\n",
       "1        ...              255   26                     0     0         69   \n",
       "\n",
       "  Wolof Yiddish Yoruba Zuni (2016 or earlier)         PUMA Code  \n",
       "1     0      10     31                      0  7950000US3603701  \n",
       "\n",
       "[1 rows x 140 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sort column names alphabetically\n",
    "\n",
    "lep_summary_df_sorted = lep_summary_df_renamed.reindex(sorted(lep_summary_df_renamed.columns), axis=1)\n",
    "_\n",
    "lep_summary_df_sorted = lep_summary_df_sorted.rename(columns={'ucgid' : 'PUMA Code'})\n",
    "lep_summary_df_sorted.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine Chinese, Mandarin and Cantonese together \n",
    "# (language interpretation services in NYC don't distinguish between various dialects as they're fluent in all)\n",
    "lep_summary_df_sorted['Chinese (incl. Mandarin, Cantonese)'] = \\\n",
    "    lep_summary_df_sorted['Chinese'] + lep_summary_df_sorted['Mandarin'] + \\\n",
    "    lep_summary_df_sorted['Cantonese']\n",
    "\n",
    "#drop the individual level Chinese, Mandarin and Cantonese languages\n",
    "lep_summary_df_sorted = lep_summary_df_sorted.drop(columns=['Chinese', 'Mandarin', 'Cantonese'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now join the NYC LEP Dataframe at the PUMA level with the PUMA data to get geographical attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_summary_puma_attrs = pd.merge(lep_summary_df_sorted, puma_attributes, \\\n",
    "                        left_on = 'PUMA Code', right_on = 'PUMA FIPS', how='left').iloc[:, :-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Afrikaans</th>\n",
       "      <th>Akan (incl. Twi)</th>\n",
       "      <th>Albanian</th>\n",
       "      <th>Aleut languages</th>\n",
       "      <th>Amharic</th>\n",
       "      <th>Apache languages</th>\n",
       "      <th>Arabic</th>\n",
       "      <th>Armenian</th>\n",
       "      <th>Assyrian Neo-Aramaic</th>\n",
       "      <th>Bengali</th>\n",
       "      <th>...</th>\n",
       "      <th>Uzbek</th>\n",
       "      <th>Vietnamese</th>\n",
       "      <th>Wolof</th>\n",
       "      <th>Yiddish</th>\n",
       "      <th>Yoruba</th>\n",
       "      <th>Zuni (2016 or earlier)</th>\n",
       "      <th>PUMA Code</th>\n",
       "      <th>Chinese (incl. Mandarin, Cantonese)</th>\n",
       "      <th>borough</th>\n",
       "      <th>puma</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>281</td>\n",
       "      <td>178</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>79</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>121</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>7950000US3603701</td>\n",
       "      <td>444</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>3701</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 140 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Afrikaans Akan (incl. Twi) Albanian Aleut languages Amharic  \\\n",
       "0         0              281      178               0      65   \n",
       "\n",
       "  Apache languages Arabic Armenian Assyrian Neo-Aramaic Bengali  ...  Uzbek  \\\n",
       "0                0     79        0                   12     121  ...      0   \n",
       "\n",
       "  Vietnamese Wolof Yiddish Yoruba Zuni (2016 or earlier)         PUMA Code  \\\n",
       "0         69     0      10     31                      0  7950000US3603701   \n",
       "\n",
       "  Chinese (incl. Mandarin, Cantonese) borough  puma  \n",
       "0                                 444   Bronx  3701  \n",
       "\n",
       "[1 rows x 140 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_summary_puma_attrs.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Melt the dataframe so that at each row is a language in a specific PUMA (ordered by language)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_summary_puma_attrs_melt = lep_summary_puma_attrs.melt(id_vars=['borough', 'puma', 'PUMA Code'])\\\n",
    "    .rename(columns={'value' : 'LEP', 'variable' : 'Language'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>borough</th>\n",
       "      <th>puma</th>\n",
       "      <th>PUMA Code</th>\n",
       "      <th>Language</th>\n",
       "      <th>LEP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3701</td>\n",
       "      <td>7950000US3603701</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3702</td>\n",
       "      <td>7950000US3603702</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  borough  puma         PUMA Code   Language LEP\n",
       "0   Bronx  3701  7950000US3603701  Afrikaans   0\n",
       "1   Bronx  3702  7950000US3603702  Afrikaans   0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_summary_puma_attrs_melt.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Identify CVALEP residents in each PUMA (roughly equivalent to a Community District)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### There are four variables to identify CVALEPs:\n",
    "- Language Other than English Spoken at Home (LANP)\n",
    "- Age (AGE) = +18 (as opposed to +5 when it came to identifying the LEPs)\n",
    "- Ability to Speak English (ENG). Any level except \"Very Well\" qualifies as a limited english proficient.\n",
    "    - 2 = Well\n",
    "    - 3 = Not Well\n",
    "    - 4 = Not At All\n",
    "- Citizen Status (CIT)\n",
    "    - 1 = Born in the US\n",
    "    - 2 = Born in Puerto Rico, Guam, the US Virgin Islands\n",
    "    - 3 = Board abroad of American parents\n",
    "    - 4 = U.S. Citizen by Naturalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "age_string_18_plus = ''.join([('&AGEP=' + str(i)) for i in range(18, 100)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 18+ years old Residents who speak English less than \"Very Well\" and are US Citizens (CVALEP)\n",
    "\n",
    "# Source ACS 5 Year 2015-2019 Public Use Microdata Sample\n",
    "cvalep_API_summary = \"https://api.census.gov/data/2019/acs/acs5/pums?tabulate=weight(PWGTP)&col+LANP&row+ucgid&ucgid=\" + puma_str + age_string_18_plus + \"&CIT=1&CIT=2&CIT=3&CIT=4&ENG=2&ENG=3&ENG=4&key=\" + apiKey\n",
    "\n",
    "#call the API and collect the response\n",
    "response_cvalep_summary = requests.get(cvalep_API_summary)\n",
    "\n",
    "#load the response into a JSON\n",
    "formattedResponse_cvalep_summary = json.loads(response_cvalep_summary.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cvalep_summary_df = pd.DataFrame(formattedResponse_cvalep_summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now using an input of language codes, rename the header row to reflect languages.\n",
    "\n",
    "\n",
    "Before running this function, download the PUMS 5-year data dictionary from here (you may have already downloaded one above)\n",
    "\n",
    "For 2015-2019, the data dictionary is located here: https://www.census.gov/programs-surveys/acs/microdata/documentation/2019.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "cvalep_summary_df_renamed, languages_code_dict = rename_header_languages(cvalep_summary_df, insert_location_of_pums_5_year_data_dict_here)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change column names alphabetically\n",
    "cvalep_summary_df_sorted = cvalep_summary_df_renamed.reindex(sorted(\\\n",
    "                        cvalep_summary_df_renamed.columns), axis=1)\n",
    "\n",
    "# combine chinese, mandarin and cantonese together\n",
    "cvalep_summary_df_sorted['Chinese (incl. Mandarin, Cantonese)'] = \\\n",
    "    cvalep_summary_df_sorted['Chinese'] + cvalep_summary_df_sorted['Mandarin'] + \\\n",
    "    cvalep_summary_df_sorted['Cantonese']\n",
    "\n",
    "# drop Chinese, Mandarin and Cantonese\n",
    "cvalep_summary_df_sorted = cvalep_summary_df_sorted.drop(columns=['Chinese', 'Mandarin', 'Cantonese'])\n",
    "\n",
    "cvalep_summary_df_sorted = cvalep_summary_df_sorted.rename(columns={'ucgid' : 'PUMA Code'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge NYC CVALEP data with the PUMA data to get geographical attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "cvalep_summary_puma_attrs = pd.merge(cvalep_summary_df_sorted, puma_attributes, \\\n",
    "    left_on = 'PUMA Code', right_on = 'PUMA FIPS', how='left').iloc[:, :-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "cvalep_summary_puma_attrs_melt = cvalep_summary_puma_attrs.melt(id_vars=['borough', 'puma', 'PUMA Code'])\\\n",
    "    .rename(columns={'value' : 'CVALEP', 'variable' : 'Language'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, merge the LEP data and the CVALEP data together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is before the BOE\n",
    "lep_and_cvalep_merged = lep_summary_puma_attrs_melt.merge(cvalep_summary_puma_attrs_melt, \\\n",
    "                    on =['borough', 'PUMA Code','puma', 'Language'], how='inner', indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          7535\n",
       "right_only       0\n",
       "left_only        0\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_merged['_merge'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50.33121522071453"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#what is the proportion of CVALEPs out of LEP residents? Over 50%\n",
    "lep_and_cvalep_merged['CVALEP'].sum() / lep_and_cvalep_merged['LEP'].sum()*100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3. Aggregate data from the PUMA level to:\n",
    "- #### 1) LEP and CVALEP % at the Community District Level; and \n",
    "- #### 2) LEP and CVALEP % by Languages at the Community District Level"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the mapping dataset from the NYC Open Data Portal on which Community District belongs to which PUMA.\n",
    "\n",
    "First, import this dataset from by clicking on this [link](https://www1.nyc.gov/assets/planning/download/office/data-maps/nyc-population/census2010/nyc2010census_tabulation_equiv.xlsx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyc_puma_to_cd = pd.read_excel(insert_location_of_community_district_to_puma_map_here, \\\n",
    "                               sheet_name= \"NTA in PUMA_\", header=6).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean the \n",
    "nyc_puma_to_cd = nyc_puma_to_cd.rename(columns={'level_0' : 'Borough', \n",
    "                                                'level_1' : '2010 Census Bureau FIPS County Code', \\\n",
    "                                                'level_2' : '2010 NYC Borough Code', \\\n",
    "                                                'PUMA \\nCode' : 'PUMA Code', \\\n",
    "                                                'Community District \\n(PUMAs approximate NYC Community  Districts and are not coterminous)' : 'Community District', \\\n",
    "                                                'NTA \\nCode' : 'NTA Code',\n",
    "                                               'Name' : 'NTA Name'})\n",
    "                                       \n",
    "nyc_puma_to_cd = nyc_puma_to_cd.drop(columns=['NTA Code', 'NTA Name'])\\\n",
    "            .groupby(['Borough',  '2010 NYC Borough Code', 'PUMA Code', 'Community District'])\\\n",
    "            .size().reset_index().drop(columns=0)\n",
    "\n",
    "nyc_puma_to_cd['PUMA Code'] = nyc_puma_to_cd['PUMA Code'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Borough</th>\n",
       "      <th>2010 NYC Borough Code</th>\n",
       "      <th>PUMA Code</th>\n",
       "      <th>Community District</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3701</td>\n",
       "      <td>BX Community District 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3702</td>\n",
       "      <td>BX Community District 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3703</td>\n",
       "      <td>BX Community District 10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3704</td>\n",
       "      <td>BX Community District 11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3705</td>\n",
       "      <td>BX Community Districts 3 &amp; 6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Borough  2010 NYC Borough Code  PUMA Code            Community District\n",
       "0   Bronx                      2       3701       BX Community District 8\n",
       "1   Bronx                      2       3702      BX Community District 12\n",
       "2   Bronx                      2       3703      BX Community District 10\n",
       "3   Bronx                      2       3704      BX Community District 11\n",
       "4   Bronx                      2       3705  BX Community Districts 3 & 6"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyc_puma_to_cd.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not all PUMA's can be mapped 1-to-1 with the CD's. There are 59 CD's and 55 PUMA's.\n",
    "\n",
    "**Question: how do you distribute 4 PUMA's to the 8 CD's?**\n",
    "\n",
    "One way to do that is by using population proportion i.e. allocate PUMA data to multiple CD's based on how much their total population falls with the PUMA."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the socrata API key from here or directly download and import the dataset into this Notebook: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_nyc_population_cd_level(cd_nyc_od_url, app_token):\n",
    "    '''\n",
    "    Using Socrata API, import the dataset containing URL of the dataset that contains info on Census Decennial\n",
    "    population at the Community District level.\n",
    "    \n",
    "    Input:\n",
    "        cd_nyc_od_url: a string containing the 4x4 link of the open data portal\n",
    "        app_token: app_token obtained from Socrata's Developer Tools\n",
    "    \n",
    "    Returns:\n",
    "        A dataframe containing the data on population for each of the 59 community district.\n",
    "    '''\n",
    "    \n",
    "    # first import the dataset on CD population\n",
    "    client = Socrata(\"data.cityofnewyork.us\", app_token)\n",
    "    cd_nyc_od_link = client.get(cd_nyc_od_url, limit=5000)\n",
    "\n",
    "    cd_nyc_population = pd.DataFrame(cd_nyc_od_link)\n",
    "\n",
    "    # only 2010\n",
    "    cd_nyc_population_only_2010 = cd_nyc_population.drop(columns=['_1970_population', \\\n",
    "                                    '_1980_population', '_1990_population', '_2000_population'])\n",
    "    cd_nyc_population_only_2010 = cd_nyc_population_only_2010.rename(\\\n",
    "                                columns={'_2010_population' : 'CD 2010 Population'})\n",
    "    cd_nyc_population_only_2010['CD 2010 Population'] = cd_nyc_population_only_2010['CD 2010 Population'].astype(int)\n",
    "    \n",
    "    return cd_nyc_population_only_2010"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get nyc population at the CD level\n",
    "cd_nyc_population_only_2010 = get_nyc_population_cd_level(\"xi7c-iiu2\", insert_app_token_here)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def separate_out_cd_lines(df_with_cd_info):\n",
    "    '''\n",
    "    Create an equivalent number of duplicate rows when a PUMA has two community districts.\n",
    "    These duplicate rows will be used to identify data at the community district level for those \n",
    "    two CD's in that PUMA (based on population apportionment, \n",
    "    i.e. the percent of population in that community district out of the total PUMA population).\n",
    "    \n",
    "    Input:\n",
    "        df_with_cd_info: A dataframe containing the LEP and CVALEP data at the PUMA level \n",
    "        along with CD attribute for each PUMA.\n",
    "    \n",
    "    Output:\n",
    "        A dataframe that duplicates the rows of the PUMA that have two CD's.\n",
    "    \n",
    "    '''\n",
    "    \n",
    "    all_rows_list = []\n",
    "    puma_codes_list = []\n",
    "\n",
    "    for idx, row in df_with_cd_info.iterrows():\n",
    "\n",
    "        # if a PUMA has multiple CD's\n",
    "        if \"&\" in row['Community District']:\n",
    "            # get codes list\n",
    "            cd_codes_list = [int(row['Community District'][-5]), int(row['Community District'][-1])] \n",
    "            \n",
    "            puma_codes_list.append(row['puma'])\n",
    "            cd_desc = row['Community District']\n",
    "\n",
    "            # create extra rows so that each community district has the same set of \n",
    "            # rows/data as its counterpart in the same PUMA\n",
    "            for code_ in cd_codes_list:\n",
    "                row['Community District'] = cd_desc[:-7] + ' ' + str(code_)\n",
    "                all_rows_list.append(list(row))\n",
    "\n",
    "\n",
    "    new_columns = df_with_cd_info.columns.tolist()\n",
    "    puma_by_cd_complete = pd.DataFrame(all_rows_list, columns=new_columns)\n",
    "    puma_by_cd_complete['Borough'] = puma_by_cd_complete['Borough'].astype(str)\n",
    "\n",
    "\n",
    "    # remove the rows with existing puma's from the df: \n",
    "    lep_and_cvalep_cd_info_clean = df_with_cd_info[~df_with_cd_info['puma'].isin(puma_codes_list)]\n",
    "    lep_and_cvalep_cd_info_clean_full = pd.concat([lep_and_cvalep_cd_info_clean, puma_by_cd_complete], axis=0)\n",
    "    \n",
    "    return lep_and_cvalep_cd_info_clean_full"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the LEP and CVALEP data with the PUMA to CD geogrphical attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_and_cvalep_merged['puma'] = lep_and_cvalep_merged['puma'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_and_cvalep_puma_and_cd_info = pd.merge(lep_and_cvalep_merged, nyc_puma_to_cd, \\\n",
    "                        left_on='puma', right_on='PUMA Code', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>borough</th>\n",
       "      <th>puma</th>\n",
       "      <th>PUMA Code_x</th>\n",
       "      <th>Language</th>\n",
       "      <th>LEP</th>\n",
       "      <th>CVALEP</th>\n",
       "      <th>_merge</th>\n",
       "      <th>Borough</th>\n",
       "      <th>2010 NYC Borough Code</th>\n",
       "      <th>PUMA Code_y</th>\n",
       "      <th>Community District</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3701</td>\n",
       "      <td>7950000US3603701</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3701</td>\n",
       "      <td>BX Community District 8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3702</td>\n",
       "      <td>7950000US3603702</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3702</td>\n",
       "      <td>BX Community District 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3703</td>\n",
       "      <td>7950000US3603703</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3703</td>\n",
       "      <td>BX Community District 10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3704</td>\n",
       "      <td>7950000US3603704</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3704</td>\n",
       "      <td>BX Community District 11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>3705</td>\n",
       "      <td>7950000US3603705</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>both</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2</td>\n",
       "      <td>3705</td>\n",
       "      <td>BX Community Districts 3 &amp; 6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  borough  puma       PUMA Code_x   Language LEP CVALEP _merge Borough  \\\n",
       "0   Bronx  3701  7950000US3603701  Afrikaans   0      0   both   Bronx   \n",
       "1   Bronx  3702  7950000US3603702  Afrikaans   0      0   both   Bronx   \n",
       "2   Bronx  3703  7950000US3603703  Afrikaans   0      0   both   Bronx   \n",
       "3   Bronx  3704  7950000US3603704  Afrikaans   0      0   both   Bronx   \n",
       "4   Bronx  3705  7950000US3603705  Afrikaans   0      0   both   Bronx   \n",
       "\n",
       "   2010 NYC Borough Code  PUMA Code_y            Community District  \n",
       "0                      2         3701       BX Community District 8  \n",
       "1                      2         3702      BX Community District 12  \n",
       "2                      2         3703      BX Community District 10  \n",
       "3                      2         3704      BX Community District 11  \n",
       "4                      2         3705  BX Community Districts 3 & 6  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_puma_and_cd_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7535, 11)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_puma_and_cd_info.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For rows (PUMA) that have two community districts (e.g. 3705 in Bronx), create a duplicate row for that PUMA for each language."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_and_cvalep_clean_full = separate_out_cd_lines(lep_and_cvalep_puma_and_cd_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8083, 11)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_clean_full.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_by_cd_analysis(lep_and_cvalep_clean_full, cd_nyc_population_only_2010):\n",
    "    '''\n",
    "    In the PUMA by Language dataframe, split the 4 PUMA's into 8 CD's and get \n",
    "    LEP and CVALEP data at the CD level.\n",
    "    \n",
    "    Input:\n",
    "        lep_and_cvalep_clean_full: A dataframe containing LEP and CVALEP data \n",
    "        at the PUMA level with duplicate rows.\n",
    "        cd_nyc_population_only_2010: A dataframe containing data from Open Data Portal \n",
    "        on the Decennial Population of Community Districts.\n",
    "        \n",
    "    Output:\n",
    "        Returns the dataframe containing the LEP and CVALEP data at the \n",
    "        Community District level (instead of the PUMA level)\n",
    "        \n",
    "    '''    \n",
    "\n",
    "    # for each CD, get their CD Code (so that we can join it with CD Decennial Population data on the Open Data Portal)\n",
    "    lep_and_cvalep_clean_full['CD Code'] = lep_and_cvalep_clean_full['Community District'].str[-2:]\n",
    "    lep_and_cvalep_clean_full['CD Code'] = lep_and_cvalep_clean_full['CD Code'].str.lstrip()\n",
    "    lep_and_cvalep_clean_full = lep_and_cvalep_clean_full.reset_index(drop='index')\n",
    "    \n",
    "    # merge the LEP and CVALEP data with the CD Decennial population data to get population proportions for multiple CD's in a single PUMA \n",
    "    lep_and_cvalep_cd_pop = pd.merge(lep_and_cvalep_clean_full, cd_nyc_population_only_2010, \\\n",
    "        left_on=['Borough', 'CD Code'], right_on = ['borough', 'cd_number'], \\\n",
    "        how='left')\n",
    "    \n",
    "    #Get sum of puma population\n",
    "    puma_temp1 = lep_and_cvalep_cd_pop[~lep_and_cvalep_cd_pop['CD 2010 Population'].duplicated()]\n",
    "    puma_2010_population = puma_temp1.groupby(['puma'])['CD 2010 Population'].sum()\\\n",
    "        .reset_index(name='PUMA 2010 Population')\n",
    "    \n",
    "    # Merge LEP and CVALEP data with the PUMA's population\n",
    "    lep_and_cvalep_cd_puma_pop = pd.merge(lep_and_cvalep_cd_pop, puma_2010_population, \\\n",
    "        on='puma', how='left')\n",
    "    \n",
    "    # now do apportionment for the four CD's\n",
    "    lep_and_cvalep_cd_puma_pop['% of PUMA 2010 Population'] = \\\n",
    "        lep_and_cvalep_cd_puma_pop['CD 2010 Population'] / lep_and_cvalep_cd_puma_pop['PUMA 2010 Population']\n",
    "\n",
    "    lep_and_cvalep_cd_puma_pop['Total LEP Population Estimate'] = \\\n",
    "        lep_and_cvalep_cd_puma_pop['LEP'] * lep_and_cvalep_cd_puma_pop['% of PUMA 2010 Population']\n",
    "\n",
    "    lep_and_cvalep_cd_puma_pop['Total CVALEP Population Estimate'] = \\\n",
    "        lep_and_cvalep_cd_puma_pop['CVALEP'] * lep_and_cvalep_cd_puma_pop['% of PUMA 2010 Population']\n",
    "\n",
    "    # add one more column i.e. Borough CD Code\n",
    "    # first add a leading zero to cd_number\n",
    "    lep_and_cvalep_cd_puma_pop['CD Code leading zero'] = \\\n",
    "        lep_and_cvalep_cd_puma_pop['CD Code'].apply(lambda x: x.zfill(2))\n",
    "    \n",
    "    lep_and_cvalep_cd_puma_pop['Borough CD Code'] = \\\n",
    "        lep_and_cvalep_cd_puma_pop['2010 NYC Borough Code'].astype(str) + \\\n",
    "        lep_and_cvalep_cd_puma_pop['CD Code leading zero'].astype(str)\n",
    "\n",
    "    lep_and_cvalep_cd_puma_pop = lep_and_cvalep_cd_puma_pop.rename(columns= \\\n",
    "        {'cd_name' : 'Community District Name'})\n",
    "    \n",
    "    return lep_and_cvalep_cd_puma_pop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "lep_and_cvalep_cd_puma_pop = create_by_cd_analysis(lep_and_cvalep_clean_full, cd_nyc_population_only_2010)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "894888.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop['Total CVALEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1777998.0"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop['Total LEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8083, 22)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4. Display and Export Aggregated Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Output % of LEP and CVALEP for each CD (Overall)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first aggregate the data from language level to the community district level\n",
    "lep_and_cvalep_by_cd_overall = lep_and_cvalep_cd_puma_pop.groupby(['Borough', 'CD Code', \\\n",
    "    'Borough CD Code', 'Community District', 'Community District Name'])\\\n",
    "    .agg({'Total CVALEP Population Estimate' : 'sum', \\\n",
    "    'Total LEP Population Estimate' : 'sum'}).reset_index()\n",
    "lep_and_cvalep_by_cd_overall_with_population = pd.merge(lep_and_cvalep_by_cd_overall, \\\n",
    "    cd_nyc_population_only_2010, left_on=['Borough', 'CD Code'], \\\n",
    "    right_on=['borough', 'cd_number'])\n",
    "\n",
    "# add new columns i.e. % of population that is LEP and CVALEP\n",
    "lep_and_cvalep_by_cd_overall_with_population['% of Population that is LEP'] = \\\n",
    "    (lep_and_cvalep_by_cd_overall_with_population['Total LEP Population Estimate'] / \\\n",
    "     lep_and_cvalep_by_cd_overall_with_population['CD 2010 Population']) * 100\n",
    "\n",
    "lep_and_cvalep_by_cd_overall_with_population['% of Population that is CVALEP'] = \\\n",
    "    (lep_and_cvalep_by_cd_overall_with_population['Total CVALEP Population Estimate'] / \\\n",
    "     lep_and_cvalep_by_cd_overall_with_population['CD 2010 Population']) * 100\n",
    "\n",
    "# filter only to the columns that are required\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall = \\\n",
    "    lep_and_cvalep_by_cd_overall_with_population[['Borough', 'Borough CD Code', \\\n",
    "    'Community District', 'Community District Name', 'CD 2010 Population', \\\n",
    "    'Total LEP Population Estimate', '% of Population that is LEP', \\\n",
    "    'Total CVALEP Population Estimate', '% of Population that is CVALEP']]\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall = lep_and_cvalep_cd_puma_pop_for_print_overall.sort_values('Borough CD Code').reset_index(drop=True)\n",
    "\n",
    "# Round off\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall['Total LEP Population Estimate'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_overall['Total LEP Population Estimate'].round().astype(int)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall['% of Population that is LEP'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_overall['% of Population that is LEP'].round(1)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall['Total CVALEP Population Estimate'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_overall['Total CVALEP Population Estimate'].round().astype(int)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_overall['% of Population that is CVALEP'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_overall['% of Population that is CVALEP'].round(1)\n",
    "\n",
    "# lep_and_cvalep_cd_puma_pop_for_print.to_csv(\"Table 1 - % of LEP and CVALEP by CD.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Borough</th>\n",
       "      <th>Borough CD Code</th>\n",
       "      <th>Community District</th>\n",
       "      <th>Community District Name</th>\n",
       "      <th>CD 2010 Population</th>\n",
       "      <th>Total LEP Population Estimate</th>\n",
       "      <th>% of Population that is LEP</th>\n",
       "      <th>Total CVALEP Population Estimate</th>\n",
       "      <th>% of Population that is CVALEP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>60978</td>\n",
       "      <td>3151</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1789</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>102</td>\n",
       "      <td>MN Community District 2</td>\n",
       "      <td>Greenwich Village, Soho</td>\n",
       "      <td>90016</td>\n",
       "      <td>4652</td>\n",
       "      <td>5.2</td>\n",
       "      <td>2642</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>103</td>\n",
       "      <td>MN Community District 3</td>\n",
       "      <td>Lower East Side, Chinatown</td>\n",
       "      <td>163277</td>\n",
       "      <td>41906</td>\n",
       "      <td>25.7</td>\n",
       "      <td>26802</td>\n",
       "      <td>16.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>104</td>\n",
       "      <td>MN Community District 4</td>\n",
       "      <td>Chelsea, Clinton</td>\n",
       "      <td>103245</td>\n",
       "      <td>8774</td>\n",
       "      <td>8.5</td>\n",
       "      <td>5252</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>105</td>\n",
       "      <td>MN Community District 5</td>\n",
       "      <td>Midtown Business District</td>\n",
       "      <td>51673</td>\n",
       "      <td>4391</td>\n",
       "      <td>8.5</td>\n",
       "      <td>2628</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Borough Borough CD Code       Community District  \\\n",
       "0  Manhattan             101  MN Community District 1   \n",
       "1  Manhattan             102  MN Community District 2   \n",
       "2  Manhattan             103  MN Community District 3   \n",
       "3  Manhattan             104  MN Community District 4   \n",
       "4  Manhattan             105  MN Community District 5   \n",
       "\n",
       "      Community District Name  CD 2010 Population  \\\n",
       "0  Battery Park City, Tribeca               60978   \n",
       "1     Greenwich Village, Soho               90016   \n",
       "2  Lower East Side, Chinatown              163277   \n",
       "3            Chelsea, Clinton              103245   \n",
       "4   Midtown Business District               51673   \n",
       "\n",
       "   Total LEP Population Estimate  % of Population that is LEP  \\\n",
       "0                           3151                          5.2   \n",
       "1                           4652                          5.2   \n",
       "2                          41906                         25.7   \n",
       "3                           8774                          8.5   \n",
       "4                           4391                          8.5   \n",
       "\n",
       "   Total CVALEP Population Estimate  % of Population that is CVALEP  \n",
       "0                              1789                             2.9  \n",
       "1                              2642                             2.9  \n",
       "2                             26802                            16.4  \n",
       "3                              5252                             5.1  \n",
       "4                              2628                             5.1  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_overall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(59, 9)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_overall.shape\n",
    "# Each row represents one of the 59 community districts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1777998"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_overall['Total LEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "894888"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_overall['Total CVALEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Output % of LEP and CVALEP for each Community District by Language"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the total LEP and CVALEP population for each community district\n",
    "total_lep_by_CD = lep_and_cvalep_cd_puma_pop.groupby(['Borough CD Code'])\\\n",
    "    ['Total LEP Population Estimate'].sum().reset_index(name='Total LEP Population by CD')\n",
    "total_cvalep_by_CD = lep_and_cvalep_cd_puma_pop.groupby(['Borough CD Code'])\\\n",
    "    ['Total CVALEP Population Estimate'].sum().reset_index(name='Total CVALEP Population by CD')\n",
    "\n",
    "lep_and_cvalep_by_language_final = pd.merge(lep_and_cvalep_cd_puma_pop, total_lep_by_CD,\\\n",
    "    on='Borough CD Code', how='left')\n",
    "\n",
    "lep_and_cvalep_by_language_final_final = pd.merge(lep_and_cvalep_by_language_final, \\\n",
    "    total_cvalep_by_CD, on ='Borough CD Code', how='left')\n",
    "\n",
    "# use the info collected above to find out % of LEP Population and CVALEP population for each language in that Community District\n",
    "lep_and_cvalep_by_language_final_final['% of LEP Population'] = \\\n",
    "    (lep_and_cvalep_by_language_final_final['Total LEP Population Estimate'] / \\\n",
    "     lep_and_cvalep_by_language_final_final['Total LEP Population by CD']) * 100\n",
    "\n",
    "lep_and_cvalep_by_language_final_final['% of CVALEP Population'] = \\\n",
    "    (lep_and_cvalep_by_language_final_final['Total CVALEP Population Estimate'] / \\\n",
    "     lep_and_cvalep_by_language_final_final['Total CVALEP Population by CD']) * 100\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang = lep_and_cvalep_by_language_final_final[['Borough', \\\n",
    "    'Borough CD Code', 'Community District', 'Community District Name', 'Language', \\\n",
    "    'Total LEP Population Estimate', '% of LEP Population', 'Total CVALEP Population Estimate', '% of CVALEP Population']]\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang = lep_and_cvalep_cd_puma_pop_for_print_by_lang.\\\n",
    "    sort_values(['Borough CD Code', 'Language']).reset_index(drop=True)\n",
    "\n",
    "# Round off final metrics\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total LEP Population Estimate'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total LEP Population Estimate'].astype(float).round().astype(int)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['% of LEP Population'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_by_lang['% of LEP Population'].astype(float).round(1)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total CVALEP Population Estimate'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total CVALEP Population Estimate'].astype(float).round().astype(int)\n",
    "\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['% of CVALEP Population'] = \\\n",
    "    lep_and_cvalep_cd_puma_pop_for_print_by_lang['% of CVALEP Population'].astype(float).round(1)\n",
    "\n",
    "#lep_and_cvalep_cd_puma_pop_for_print.to_csv(\"Table 2 - % of LEP Languages by CD.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8083, 9)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>Borough</th>\n",
       "      <th>Borough CD Code</th>\n",
       "      <th>Community District</th>\n",
       "      <th>Community District Name</th>\n",
       "      <th>Language</th>\n",
       "      <th>Total LEP Population Estimate</th>\n",
       "      <th>% of LEP Population</th>\n",
       "      <th>Total CVALEP Population Estimate</th>\n",
       "      <th>% of CVALEP Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>Akan (incl. Twi)</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>Albanian</td>\n",
       "      <td>7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>Aleut languages</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>101</td>\n",
       "      <td>MN Community District 1</td>\n",
       "      <td>Battery Park City, Tribeca</td>\n",
       "      <td>Amharic</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Borough Borough CD Code       Community District  \\\n",
       "0  Manhattan             101  MN Community District 1   \n",
       "1  Manhattan             101  MN Community District 1   \n",
       "2  Manhattan             101  MN Community District 1   \n",
       "3  Manhattan             101  MN Community District 1   \n",
       "4  Manhattan             101  MN Community District 1   \n",
       "\n",
       "      Community District Name          Language  \\\n",
       "0  Battery Park City, Tribeca         Afrikaans   \n",
       "1  Battery Park City, Tribeca  Akan (incl. Twi)   \n",
       "2  Battery Park City, Tribeca          Albanian   \n",
       "3  Battery Park City, Tribeca   Aleut languages   \n",
       "4  Battery Park City, Tribeca           Amharic   \n",
       "\n",
       "   Total LEP Population Estimate  % of LEP Population  \\\n",
       "0                              0                  0.0   \n",
       "1                              0                  0.0   \n",
       "2                              7                  0.2   \n",
       "3                              0                  0.0   \n",
       "4                              0                  0.0   \n",
       "\n",
       "   Total CVALEP Population Estimate  % of CVALEP Population  \n",
       "0                                 0                     0.0  \n",
       "1                                 0                     0.0  \n",
       "2                                 0                     0.0  \n",
       "3                                 0                     0.0  \n",
       "4                                 0                     0.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1777998"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#double check to ensure Total LEP population is 1.78 million as seen in Line 45\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total LEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "894888"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#double check to ensure Total CVALEP population is 0.89 million as seen in Line 44\n",
    "lep_and_cvalep_cd_puma_pop_for_print_by_lang['Total CVALEP Population Estimate'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## THE END."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}