grocery_eda copy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import plotly.express as px\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load in data\n",
    "\n",
    "tract_zip_dat = pd.read_csv('trackzip.csv') # data from US bureau to be used to convert censustract to zipcode\n",
    "\n",
    "grocery_dat = pd.read_csv('grocery.csv') # data from US FDA "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>CensusTract</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Urban</th>\n",
       "      <th>Pop2010</th>\n",
       "      <th>OHU2010</th>\n",
       "      <th>GroupQuartersFlag</th>\n",
       "      <th>NUMGQTRS</th>\n",
       "      <th>PCTGQTRS</th>\n",
       "      <th>LILATracts_1And10</th>\n",
       "      <th>...</th>\n",
       "      <th>TractSeniors</th>\n",
       "      <th>TractWhite</th>\n",
       "      <th>TractBlack</th>\n",
       "      <th>TractAsian</th>\n",
       "      <th>TractNHOPI</th>\n",
       "      <th>TractAIAN</th>\n",
       "      <th>TractOMultir</th>\n",
       "      <th>TractHispanic</th>\n",
       "      <th>TractHUNV</th>\n",
       "      <th>TractSNAP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6059001101</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4523</td>\n",
       "      <td>1467</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.707495</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>573.0</td>\n",
       "      <td>3274.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>950.0</td>\n",
       "      <td>2072.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6059001102</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3144</td>\n",
       "      <td>1063</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.349873</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>346.0</td>\n",
       "      <td>2074.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6059001103</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4489</td>\n",
       "      <td>1384</td>\n",
       "      <td>0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>5.546892</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>602.0</td>\n",
       "      <td>2813.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1336.0</td>\n",
       "      <td>2753.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6059001201</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>5505</td>\n",
       "      <td>1412</td>\n",
       "      <td>0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.217984</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>319.0</td>\n",
       "      <td>2744.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2359.0</td>\n",
       "      <td>4439.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6059001202</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3586</td>\n",
       "      <td>912</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>283.0</td>\n",
       "      <td>1816.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1551.0</td>\n",
       "      <td>2868.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 147 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CensusTract       State         County  Urban  Pop2010  OHU2010  \\\n",
       "0   6059001101  California  Orange County      1     4523     1467   \n",
       "1   6059001102  California  Orange County      1     3144     1063   \n",
       "2   6059001103  California  Orange County      1     4489     1384   \n",
       "3   6059001201  California  Orange County      1     5505     1412   \n",
       "4   6059001202  California  Orange County      1     3586      912   \n",
       "\n",
       "   GroupQuartersFlag  NUMGQTRS  PCTGQTRS  LILATracts_1And10  ...  \\\n",
       "0                  0      32.0  0.707495                  0  ...   \n",
       "1                  0      11.0  0.349873                  0  ...   \n",
       "2                  0     249.0  5.546892                  0  ...   \n",
       "3                  0      12.0  0.217984                  0  ...   \n",
       "4                  0       0.0  0.000000                  0  ...   \n",
       "\n",
       "   TractSeniors  TractWhite  TractBlack  TractAsian  TractNHOPI  TractAIAN  \\\n",
       "0         573.0      3274.0        65.0       198.0         2.0       34.0   \n",
       "1         346.0      2074.0        32.0       298.0         3.0       41.0   \n",
       "2         602.0      2813.0        58.0       221.0        17.0       44.0   \n",
       "3         319.0      2744.0       143.0       206.0         4.0       49.0   \n",
       "4         283.0      1816.0        52.0       132.0         1.0       34.0   \n",
       "\n",
       "   TractOMultir  TractHispanic  TractHUNV  TractSNAP  \n",
       "0         950.0         2072.0       10.0       49.0  \n",
       "1         696.0         1452.0       19.0       36.0  \n",
       "2        1336.0         2753.0       21.0      150.0  \n",
       "3        2359.0         4439.0       87.0      231.0  \n",
       "4        1551.0         2868.0       51.0      137.0  \n",
       "\n",
       "[5 rows x 147 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grocery_dat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        90001\n",
       "1        90001\n",
       "2        90001\n",
       "3        90001\n",
       "4        90001\n",
       "         ...  \n",
       "16017    96161\n",
       "16018    96161\n",
       "16019    96161\n",
       "16020    96161\n",
       "16021    96162\n",
       "Name: ZIP, Length: 16022, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tract_zip_dat['ZIP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "147"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(grocery_dat.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PCTGQTRS', 'LILATracts_1And10', 'LILATracts_halfAnd10',\n",
       "       'LILATracts_1And20', 'LILATracts_Vehicle', 'HUNVFlag',\n",
       "       'LowIncomeTracts'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grocery_dat.columns[8:15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42644"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grocery_dat.isnull().sum().sum()"
   ]
  },
  {
   "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>CensusTract</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Urban</th>\n",
       "      <th>Pop2010</th>\n",
       "      <th>OHU2010</th>\n",
       "      <th>GroupQuartersFlag</th>\n",
       "      <th>NUMGQTRS</th>\n",
       "      <th>PCTGQTRS</th>\n",
       "      <th>LILATracts_1And10</th>\n",
       "      <th>...</th>\n",
       "      <th>TractSeniors</th>\n",
       "      <th>TractWhite</th>\n",
       "      <th>TractBlack</th>\n",
       "      <th>TractAsian</th>\n",
       "      <th>TractNHOPI</th>\n",
       "      <th>TractAIAN</th>\n",
       "      <th>TractOMultir</th>\n",
       "      <th>TractHispanic</th>\n",
       "      <th>TractHUNV</th>\n",
       "      <th>TractSNAP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6059001101</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4523</td>\n",
       "      <td>1467</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.707495</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>573.0</td>\n",
       "      <td>3274.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>950.0</td>\n",
       "      <td>2072.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6059001102</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3144</td>\n",
       "      <td>1063</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.349873</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>346.0</td>\n",
       "      <td>2074.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6059001103</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4489</td>\n",
       "      <td>1384</td>\n",
       "      <td>0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>5.546892</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>602.0</td>\n",
       "      <td>2813.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1336.0</td>\n",
       "      <td>2753.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6059001201</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>5505</td>\n",
       "      <td>1412</td>\n",
       "      <td>0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.217984</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>319.0</td>\n",
       "      <td>2744.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2359.0</td>\n",
       "      <td>4439.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6059001202</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3586</td>\n",
       "      <td>912</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>283.0</td>\n",
       "      <td>1816.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1551.0</td>\n",
       "      <td>2868.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>6059110604</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>8117</td>\n",
       "      <td>2580</td>\n",
       "      <td>0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>1.133424</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1228.0</td>\n",
       "      <td>3120.0</td>\n",
       "      <td>237.0</td>\n",
       "      <td>4002.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>1337.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>6059110605</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>6638</td>\n",
       "      <td>2153</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.165713</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>893.0</td>\n",
       "      <td>1610.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>4650.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>293.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>579</th>\n",
       "      <td>6059110606</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4590</td>\n",
       "      <td>1223</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.130719</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>230.0</td>\n",
       "      <td>1611.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>1113.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1578.0</td>\n",
       "      <td>2707.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>6059110607</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3845</td>\n",
       "      <td>1234</td>\n",
       "      <td>0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.728218</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>324.0</td>\n",
       "      <td>1566.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>1244.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>895.0</td>\n",
       "      <td>1446.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>6059980000</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>52.631579</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>582 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CensusTract       State         County  Urban  Pop2010  OHU2010  \\\n",
       "0     6059001101  California  Orange County      1     4523     1467   \n",
       "1     6059001102  California  Orange County      1     3144     1063   \n",
       "2     6059001103  California  Orange County      1     4489     1384   \n",
       "3     6059001201  California  Orange County      1     5505     1412   \n",
       "4     6059001202  California  Orange County      1     3586      912   \n",
       "..           ...         ...            ...    ...      ...      ...   \n",
       "577   6059110604  California  Orange County      1     8117     2580   \n",
       "578   6059110605  California  Orange County      1     6638     2153   \n",
       "579   6059110606  California  Orange County      1     4590     1223   \n",
       "580   6059110607  California  Orange County      1     3845     1234   \n",
       "581   6059980000  California  Orange County      1       19        5   \n",
       "\n",
       "     GroupQuartersFlag  NUMGQTRS   PCTGQTRS  LILATracts_1And10  ...  \\\n",
       "0                    0      32.0   0.707495                  0  ...   \n",
       "1                    0      11.0   0.349873                  0  ...   \n",
       "2                    0     249.0   5.546892                  0  ...   \n",
       "3                    0      12.0   0.217984                  0  ...   \n",
       "4                    0       0.0   0.000000                  0  ...   \n",
       "..                 ...       ...        ...                ...  ...   \n",
       "577                  0      92.0   1.133424                  0  ...   \n",
       "578                  0      11.0   0.165713                  0  ...   \n",
       "579                  0       6.0   0.130719                  0  ...   \n",
       "580                  0      28.0   0.728218                  0  ...   \n",
       "581                  0      10.0  52.631579                  0  ...   \n",
       "\n",
       "     TractSeniors  TractWhite  TractBlack  TractAsian  TractNHOPI  TractAIAN  \\\n",
       "0           573.0      3274.0        65.0       198.0         2.0       34.0   \n",
       "1           346.0      2074.0        32.0       298.0         3.0       41.0   \n",
       "2           602.0      2813.0        58.0       221.0        17.0       44.0   \n",
       "3           319.0      2744.0       143.0       206.0         4.0       49.0   \n",
       "4           283.0      1816.0        52.0       132.0         1.0       34.0   \n",
       "..            ...         ...         ...         ...         ...        ...   \n",
       "577        1228.0      3120.0       237.0      4002.0         5.0       23.0   \n",
       "578         893.0      1610.0        61.0      4650.0         5.0       19.0   \n",
       "579         230.0      1611.0       229.0      1113.0         9.0       50.0   \n",
       "580         324.0      1566.0        92.0      1244.0        15.0       33.0   \n",
       "581           1.0        12.0         1.0         5.0         0.0        0.0   \n",
       "\n",
       "     TractOMultir  TractHispanic  TractHUNV  TractSNAP  \n",
       "0           950.0         2072.0       10.0       49.0  \n",
       "1           696.0         1452.0       19.0       36.0  \n",
       "2          1336.0         2753.0       21.0      150.0  \n",
       "3          2359.0         4439.0       87.0      231.0  \n",
       "4          1551.0         2868.0       51.0      137.0  \n",
       "..            ...            ...        ...        ...  \n",
       "577         730.0         1337.0        0.0       34.0  \n",
       "578         293.0          510.0       19.0        9.0  \n",
       "579        1578.0         2707.0       46.0      286.0  \n",
       "580         895.0         1446.0       11.0       60.0  \n",
       "581           1.0            2.0        0.0        0.0  \n",
       "\n",
       "[582 rows x 36 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "washed_grocery = grocery_dat.dropna(axis=1)\n",
    "washed_grocery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PCTGQTRS', 'LILATracts_1And10', 'LILATracts_halfAnd10',\n",
       "       'LILATracts_1And20', 'LILATracts_Vehicle', 'HUNVFlag',\n",
       "       'LowIncomeTracts'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "washed_grocery.columns[8:15]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(washed_grocery.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CensusTract', 'State', 'County', 'Urban', 'Pop2010', 'OHU2010',\n",
       "       'GroupQuartersFlag', 'NUMGQTRS', 'PCTGQTRS', 'LILATracts_1And10',\n",
       "       'LILATracts_halfAnd10', 'LILATracts_1And20', 'LILATracts_Vehicle',\n",
       "       'HUNVFlag', 'LowIncomeTracts', 'PovertyRate', 'LA1and10', 'LAhalfand10',\n",
       "       'LA1and20', 'LATracts_half', 'LATracts1', 'LATracts10', 'LATracts20',\n",
       "       'LATractsVehicle_20', 'TractLOWI', 'TractKids', 'TractSeniors',\n",
       "       'TractWhite', 'TractBlack', 'TractAsian', 'TractNHOPI', 'TractAIAN',\n",
       "       'TractOMultir', 'TractHispanic', 'TractHUNV', 'TractSNAP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "washed_grocery.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\anony\\AppData\\Local\\Temp\\ipykernel_21636\\3606417751.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  washed_grocery.loc[:, 'ZipCode'] = washed_grocery['CensusTract'].map(census_tract_to_zip)\n"
     ]
    },
    {
     "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>CensusTract</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Urban</th>\n",
       "      <th>Pop2010</th>\n",
       "      <th>OHU2010</th>\n",
       "      <th>GroupQuartersFlag</th>\n",
       "      <th>NUMGQTRS</th>\n",
       "      <th>PCTGQTRS</th>\n",
       "      <th>LILATracts_1And10</th>\n",
       "      <th>...</th>\n",
       "      <th>TractWhite</th>\n",
       "      <th>TractBlack</th>\n",
       "      <th>TractAsian</th>\n",
       "      <th>TractNHOPI</th>\n",
       "      <th>TractAIAN</th>\n",
       "      <th>TractOMultir</th>\n",
       "      <th>TractHispanic</th>\n",
       "      <th>TractHUNV</th>\n",
       "      <th>TractSNAP</th>\n",
       "      <th>ZipCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6059001101</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4523</td>\n",
       "      <td>1467</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.707495</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3274.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>950.0</td>\n",
       "      <td>2072.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>90631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6059001102</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3144</td>\n",
       "      <td>1063</td>\n",
       "      <td>0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.349873</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2074.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>696.0</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>90631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6059001103</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>4489</td>\n",
       "      <td>1384</td>\n",
       "      <td>0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>5.546892</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2813.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>221.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1336.0</td>\n",
       "      <td>2753.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>90631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6059001201</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>5505</td>\n",
       "      <td>1412</td>\n",
       "      <td>0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.217984</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2744.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2359.0</td>\n",
       "      <td>4439.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>90631.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6059001202</td>\n",
       "      <td>California</td>\n",
       "      <td>Orange County</td>\n",
       "      <td>1</td>\n",
       "      <td>3586</td>\n",
       "      <td>912</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1816.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>1551.0</td>\n",
       "      <td>2868.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>90633.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CensusTract       State         County  Urban  Pop2010  OHU2010  \\\n",
       "0   6059001101  California  Orange County      1     4523     1467   \n",
       "1   6059001102  California  Orange County      1     3144     1063   \n",
       "2   6059001103  California  Orange County      1     4489     1384   \n",
       "3   6059001201  California  Orange County      1     5505     1412   \n",
       "4   6059001202  California  Orange County      1     3586      912   \n",
       "\n",
       "   GroupQuartersFlag  NUMGQTRS  PCTGQTRS  LILATracts_1And10  ...  TractWhite  \\\n",
       "0                  0      32.0  0.707495                  0  ...      3274.0   \n",
       "1                  0      11.0  0.349873                  0  ...      2074.0   \n",
       "2                  0     249.0  5.546892                  0  ...      2813.0   \n",
       "3                  0      12.0  0.217984                  0  ...      2744.0   \n",
       "4                  0       0.0  0.000000                  0  ...      1816.0   \n",
       "\n",
       "   TractBlack  TractAsian  TractNHOPI  TractAIAN  TractOMultir  TractHispanic  \\\n",
       "0        65.0       198.0         2.0       34.0         950.0         2072.0   \n",
       "1        32.0       298.0         3.0       41.0         696.0         1452.0   \n",
       "2        58.0       221.0        17.0       44.0        1336.0         2753.0   \n",
       "3       143.0       206.0         4.0       49.0        2359.0         4439.0   \n",
       "4        52.0       132.0         1.0       34.0        1551.0         2868.0   \n",
       "\n",
       "   TractHUNV  TractSNAP  ZipCode  \n",
       "0       10.0       49.0  90631.0  \n",
       "1       19.0       36.0  90631.0  \n",
       "2       21.0      150.0  90631.0  \n",
       "3       87.0      231.0  90631.0  \n",
       "4       51.0      137.0  90633.0  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a mapping dictionary from census tract to zip code using US_geo_dat\n",
    "census_tract_to_zip = dict(zip(tract_zip_dat['TRACT'], tract_zip_dat['ZIP']))\n",
    "\n",
    "# Map zip codes to grocery_dat based on matching census tract and census key\n",
    "washed_grocery.loc[:, 'ZipCode'] = washed_grocery['CensusTract'].map(census_tract_to_zip)\n",
    "\n",
    "washed_grocery.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "51657823.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "washed_grocery['ZipCode'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(washed_grocery['ZipCode']=='NaN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CensusTract', 'State', 'County', 'Urban', 'Pop2010', 'OHU2010',\n",
       "       'GroupQuartersFlag', 'NUMGQTRS', 'PCTGQTRS', 'LILATracts_1And10',\n",
       "       'LILATracts_halfAnd10', 'LILATracts_1And20', 'LILATracts_Vehicle',\n",
       "       'HUNVFlag', 'LowIncomeTracts', 'PovertyRate', 'LA1and10', 'LAhalfand10',\n",
       "       'LA1and20', 'LATracts_half', 'LATracts1', 'LATracts10', 'LATracts20',\n",
       "       'LATractsVehicle_20', 'TractLOWI', 'TractKids', 'TractSeniors',\n",
       "       'TractWhite', 'TractBlack', 'TractAsian', 'TractNHOPI', 'TractAIAN',\n",
       "       'TractOMultir', 'TractHispanic', 'TractHUNV', 'TractSNAP', 'ZipCode'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "washed_grocery.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_columns = ['CensusTract', 'State', 'County', 'LILATracts_1And10', 'LILATracts_halfAnd10', 'LILATracts_1And20', 'LILATracts_Vehicle', 'ZipCode']\n",
    "\n",
    "new_grocery = washed_grocery[selected_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CensusTract', 'State', 'County', 'LILATracts_1And10',\n",
       "       'LILATracts_halfAnd10', 'LILATracts_1And20', 'LILATracts_Vehicle',\n",
       "       'ZipCode'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_grocery.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_grocery.to_csv('grocery_zip.csv',index=False)"
   ]
  }
 ],
 "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}