01-preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "\n",
    "# https://www.kaggle.com/timmate/avocado-prices-2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./data_csv/avocado-updated-2020.csv', parse_dates=['date'])"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>geography</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  average_price  total_volume       4046       4225      4770  \\\n",
       "0 2015-01-04           1.22      40873.28    2819.50   28287.42     49.90   \n",
       "1 2015-01-04           1.79       1373.95      57.42     153.88      0.00   \n",
       "2 2015-01-04           1.00     435021.49  364302.39   23821.16     82.15   \n",
       "3 2015-01-04           1.76       3846.69    1500.15     938.35      0.00   \n",
       "4 2015-01-04           1.08     788025.06   53987.31  552906.04  39995.03   \n",
       "\n",
       "   total_bags  small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0     9716.46     9186.93      529.53          0.0  conventional  2015   \n",
       "1     1162.65     1162.65        0.00          0.0       organic  2015   \n",
       "2    46815.79    16707.15    30108.64          0.0  conventional  2015   \n",
       "3     1408.19     1071.35      336.84          0.0       organic  2015   \n",
       "4   141136.68   137146.07     3990.61          0.0  conventional  2015   \n",
       "\n",
       "              geography  \n",
       "0                Albany  \n",
       "1                Albany  \n",
       "2               Atlanta  \n",
       "3               Atlanta  \n",
       "4  Baltimore/Washington  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31156.82"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "2819.5+28287.42+49.9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30021, 13)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2015, 2016, 2017, 2018, 2019, 2020], dtype=int64)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>features</th>\n",
       "      <th>unique values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>date</td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>average_price</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>total_volume</td>\n",
       "      <td>29883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>28785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4225</td>\n",
       "      <td>29408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4770</td>\n",
       "      <td>19022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>total_bags</td>\n",
       "      <td>29735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>small_bags</td>\n",
       "      <td>28930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>large_bags</td>\n",
       "      <td>25528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>xlarge_bags</td>\n",
       "      <td>10295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>type</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>year</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>geography</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         features  unique values\n",
       "0            date            278\n",
       "1   average_price            260\n",
       "2    total_volume          29883\n",
       "3            4046          28785\n",
       "4            4225          29408\n",
       "5            4770          19022\n",
       "6      total_bags          29735\n",
       "7      small_bags          28930\n",
       "8      large_bags          25528\n",
       "9     xlarge_bags          10295\n",
       "10           type              2\n",
       "11           year              6\n",
       "12      geography             54"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jumlah = []\n",
    "\n",
    "for i in df.columns:\n",
    "    jumlah.append(df[i].nunique())\n",
    "\n",
    "dfjumlah = pd.DataFrame(jumlah)\n",
    "dfcol = pd.DataFrame(df.columns)  \n",
    "\n",
    "unique = pd.concat([dfcol, dfjumlah], axis=1)\n",
    "unique.columns = ['features', 'unique values']\n",
    "\n",
    "unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date             2020-05-17 00:00:00\n",
       "average_price                   2.34\n",
       "total_volume             6.34289e+07\n",
       "4046                     1.93089e+07\n",
       "4225                     1.30364e+07\n",
       "4770                     1.20586e+06\n",
       "total_bags               3.16892e+07\n",
       "small_bags               2.05504e+07\n",
       "large_bags               1.33276e+07\n",
       "xlarge_bags              1.02256e+06\n",
       "type                         organic\n",
       "year                            2020\n",
       "geography        West Tex/New Mexico\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['year'] == 2020].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date             0\n",
       "average_price    0\n",
       "total_volume     0\n",
       "4046             0\n",
       "4225             0\n",
       "4770             0\n",
       "total_bags       0\n",
       "small_bags       0\n",
       "large_bags       0\n",
       "xlarge_bags      0\n",
       "type             0\n",
       "year             0\n",
       "geography        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30021, 13)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "conventional    15012\n",
       "organic         15009\n",
       "Name: type, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['type'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30021.000000\n",
       "mean         1.390930\n",
       "std          0.381347\n",
       "min          0.440000\n",
       "25%          1.110000\n",
       "50%          1.350000\n",
       "75%          1.630000\n",
       "max          3.250000\n",
       "Name: average_price, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['average_price'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015    5615\n",
       "2016    5616\n",
       "2017    5722\n",
       "2018    5292\n",
       "2019    5616\n",
       "2020    2160\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year'].value_counts().sort_index()"
   ]
  },
  {
   "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>date</th>\n",
       "      <th>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>geography</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  average_price  total_volume       4046       4225      4770  \\\n",
       "0 2015-01-04           1.22      40873.28    2819.50   28287.42     49.90   \n",
       "1 2015-01-04           1.79       1373.95      57.42     153.88      0.00   \n",
       "2 2015-01-04           1.00     435021.49  364302.39   23821.16     82.15   \n",
       "3 2015-01-04           1.76       3846.69    1500.15     938.35      0.00   \n",
       "4 2015-01-04           1.08     788025.06   53987.31  552906.04  39995.03   \n",
       "\n",
       "   total_bags  small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0     9716.46     9186.93      529.53          0.0  conventional  2015   \n",
       "1     1162.65     1162.65        0.00          0.0       organic  2015   \n",
       "2    46815.79    16707.15    30108.64          0.0  conventional  2015   \n",
       "3     1408.19     1071.35      336.84          0.0       organic  2015   \n",
       "4   141136.68   137146.07     3990.61          0.0  conventional  2015   \n",
       "\n",
       "              geography  \n",
       "0                Albany  \n",
       "1                Albany  \n",
       "2               Atlanta  \n",
       "3               Atlanta  \n",
       "4  Baltimore/Washington  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>average_price</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>1.390930</td>\n",
       "      <td>3.813466e-01</td>\n",
       "      <td>0.44</td>\n",
       "      <td>1.11</td>\n",
       "      <td>1.35</td>\n",
       "      <td>1.63</td>\n",
       "      <td>3.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_volume</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>939254.592050</td>\n",
       "      <td>3.813519e+06</td>\n",
       "      <td>84.56</td>\n",
       "      <td>14298.53</td>\n",
       "      <td>124204.74</td>\n",
       "      <td>489802.88</td>\n",
       "      <td>63716144.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4046</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>299107.331789</td>\n",
       "      <td>1.289108e+06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>782.78</td>\n",
       "      <td>10522.76</td>\n",
       "      <td>115155.65</td>\n",
       "      <td>22743616.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4225</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>284901.365483</td>\n",
       "      <td>1.169078e+06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2814.00</td>\n",
       "      <td>24567.22</td>\n",
       "      <td>140947.20</td>\n",
       "      <td>20470572.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4770</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>21629.374703</td>\n",
       "      <td>1.009188e+05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>186.75</td>\n",
       "      <td>5424.16</td>\n",
       "      <td>2546439.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_bags</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>333533.629475</td>\n",
       "      <td>1.415618e+06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8373.82</td>\n",
       "      <td>50391.31</td>\n",
       "      <td>159174.17</td>\n",
       "      <td>31689188.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>small_bags</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>232125.625877</td>\n",
       "      <td>9.505028e+05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5955.98</td>\n",
       "      <td>34255.32</td>\n",
       "      <td>112938.44</td>\n",
       "      <td>20550406.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>large_bags</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>95185.285947</td>\n",
       "      <td>4.672104e+05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>351.59</td>\n",
       "      <td>5171.12</td>\n",
       "      <td>36067.62</td>\n",
       "      <td>13327600.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xlarge_bags</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>6222.717152</td>\n",
       "      <td>3.813737e+04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>560.00</td>\n",
       "      <td>1022564.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <td>30021.0</td>\n",
       "      <td>2017.205123</td>\n",
       "      <td>1.570133e+00</td>\n",
       "      <td>2015.00</td>\n",
       "      <td>2016.00</td>\n",
       "      <td>2017.00</td>\n",
       "      <td>2019.00</td>\n",
       "      <td>2020.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 count           mean           std      min       25%  \\\n",
       "average_price  30021.0       1.390930  3.813466e-01     0.44      1.11   \n",
       "total_volume   30021.0  939254.592050  3.813519e+06    84.56  14298.53   \n",
       "4046           30021.0  299107.331789  1.289108e+06     0.00    782.78   \n",
       "4225           30021.0  284901.365483  1.169078e+06     0.00   2814.00   \n",
       "4770           30021.0   21629.374703  1.009188e+05     0.00      0.00   \n",
       "total_bags     30021.0  333533.629475  1.415618e+06     0.00   8373.82   \n",
       "small_bags     30021.0  232125.625877  9.505028e+05     0.00   5955.98   \n",
       "large_bags     30021.0   95185.285947  4.672104e+05     0.00    351.59   \n",
       "xlarge_bags    30021.0    6222.717152  3.813737e+04     0.00      0.00   \n",
       "year           30021.0    2017.205123  1.570133e+00  2015.00   2016.00   \n",
       "\n",
       "                     50%        75%          max  \n",
       "average_price       1.35       1.63         3.25  \n",
       "total_volume   124204.74  489802.88  63716144.15  \n",
       "4046            10522.76  115155.65  22743616.17  \n",
       "4225            24567.22  140947.20  20470572.61  \n",
       "4770              186.75    5424.16   2546439.11  \n",
       "total_bags      50391.31  159174.17  31689188.60  \n",
       "small_bags      34255.32  112938.44  20550406.59  \n",
       "large_bags       5171.12   36067.62  13327600.76  \n",
       "xlarge_bags         0.00     560.00   1022564.50  \n",
       "year             2017.00    2019.00      2020.00  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>30021</td>\n",
       "      <td>2</td>\n",
       "      <td>conventional</td>\n",
       "      <td>15012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>geography</th>\n",
       "      <td>30021</td>\n",
       "      <td>54</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           count unique           top   freq\n",
       "type       30021      2  conventional  15012\n",
       "geography  30021     54    Pittsburgh    556"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='O').T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "desc = []\n",
    "\n",
    "for i in df.columns:\n",
    "    desc.append([\n",
    "        i,\n",
    "        df[i].dtypes,\n",
    "        df[i].isnull().sum(),\n",
    "        round((((df[i].isnull().sum())/(len(df)))*100), 2),\n",
    "        df[i].nunique(),\n",
    "        df[i].drop_duplicates().sample(2).values\n",
    "    ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDesc = pd.DataFrame(desc, columns=['dataFeatures', 'dataType', 'null', 'nullPercentage', 'unique', 'uniqueSample'])"
   ]
  },
  {
   "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>dataFeatures</th>\n",
       "      <th>dataType</th>\n",
       "      <th>null</th>\n",
       "      <th>nullPercentage</th>\n",
       "      <th>unique</th>\n",
       "      <th>uniqueSample</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>278</td>\n",
       "      <td>[2019-08-04T00:00:00.000000000, 2019-08-18T00:...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>average_price</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>260</td>\n",
       "      <td>[3.05, 2.12]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>total_volume</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29883</td>\n",
       "      <td>[26360.29, 41523.58]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28785</td>\n",
       "      <td>[2956740.05, 148014.47]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4225</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29408</td>\n",
       "      <td>[43424.52, 1354269.05]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4770</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19022</td>\n",
       "      <td>[107625.85, 4235.9]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>total_bags</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29735</td>\n",
       "      <td>[152983.63, 16819.08]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>small_bags</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28930</td>\n",
       "      <td>[82964.19, 5548.04]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>large_bags</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25528</td>\n",
       "      <td>[2394.52, 33276.69]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>xlarge_bags</td>\n",
       "      <td>float64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10295</td>\n",
       "      <td>[373.46, 8882.09]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>type</td>\n",
       "      <td>object</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>[conventional, organic]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>year</td>\n",
       "      <td>int64</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>[2017, 2020]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>geography</td>\n",
       "      <td>object</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54</td>\n",
       "      <td>[Columbus, Seattle]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dataFeatures        dataType  null  nullPercentage  unique  \\\n",
       "0            date  datetime64[ns]     0             0.0     278   \n",
       "1   average_price         float64     0             0.0     260   \n",
       "2    total_volume         float64     0             0.0   29883   \n",
       "3            4046         float64     0             0.0   28785   \n",
       "4            4225         float64     0             0.0   29408   \n",
       "5            4770         float64     0             0.0   19022   \n",
       "6      total_bags         float64     0             0.0   29735   \n",
       "7      small_bags         float64     0             0.0   28930   \n",
       "8      large_bags         float64     0             0.0   25528   \n",
       "9     xlarge_bags         float64     0             0.0   10295   \n",
       "10           type          object     0             0.0       2   \n",
       "11           year           int64     0             0.0       6   \n",
       "12      geography          object     0             0.0      54   \n",
       "\n",
       "                                         uniqueSample  \n",
       "0   [2019-08-04T00:00:00.000000000, 2019-08-18T00:...  \n",
       "1                                        [3.05, 2.12]  \n",
       "2                                [26360.29, 41523.58]  \n",
       "3                             [2956740.05, 148014.47]  \n",
       "4                              [43424.52, 1354269.05]  \n",
       "5                                 [107625.85, 4235.9]  \n",
       "6                               [152983.63, 16819.08]  \n",
       "7                                 [82964.19, 5548.04]  \n",
       "8                                 [2394.52, 33276.69]  \n",
       "9                                   [373.46, 8882.09]  \n",
       "10                            [conventional, organic]  \n",
       "11                                       [2017, 2020]  \n",
       "12                                [Columbus, Seattle]  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfDesc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## insight data:\n",
    "\n",
    "ada 13 kolom dalam dataset:\n",
    "1. tanggal: dari januari 2015 - mei 2020\n",
    "2. average_price: harga satuan avocado (US dollar)\n",
    "3. total_volume: total penjualan avocado (lbs)\n",
    "4. 4046\n",
    "5. 4225\n",
    "6. 4770 ==> total volume penjualan avocado PLU 4470 (perbedaan antara PLU 4046, 4225, 4770 adalah ukuran avocado)\n",
    "7. total_bags: total kantong avocado terjual\n",
    "8. small_bags: total penjualan avocado dengan pembelian menggunakan kantong kecil (tidak diketahui ukuran/dimensi kantong kecil)\n",
    "9. large_bags: total penjualan avocado dengan pembelian menggunakan kantong large (tidak diketahui ukuran/dimensi kantong besar)\n",
    "10. xlarge_bags: total penjualan avocado dengan pembelian menggunakan kantong extra large (tidak diketahui ukuran/dimensi kantong extra large)\n",
    "11. type: tipe avocado (conventional dan organik)\n",
    "12. year: tahun pencatatan transaksi: 2015 - 2020\n",
    "13. geography: region kota tempat transaksi\n",
    "\n",
    "- tidak ada data null dalam semua kolom\n",
    "- dataset terdiri dari 13 kolom dan 30021 baris\n",
    "\n",
    "Data dapat dilengkapi dengan keterangan Region di USA: https://www.nationalgeographic.org/maps/united-states-regions/#:~:text=A%20common%20way%20of%20referring,West%2C%20Southeast%2C%20and%20Midwest\n",
    "\n",
    "## Problem statement:\n",
    "Suatu online grocery shopping platform ingin mengekspansi wilayah layanannya (servis area) di kota di U.S. Berikan gambaran bagaimana kondisi pasar avocado di kota-kota U.S. Dapatkah algoritma machine learning membantu decision making perusahaan terhadap pertimbangan ekspansi ke kota baru?\n",
    "\n",
    "## Tujuan:\n",
    "- Analisis price elasticity dan volatility avocado di region-region, ataupun kota-kota di U.S\n",
    "- Tentukan apakah pasar avocado di U.S dipengaruhi faktor musiman\n",
    "- Buat machine learning model untuk memprediksi harga avocado guna membantu proses decision making perusahaan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preparation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "terdapat beberapa kemungkinan kondisi pada dateset yang raw (mentah):\n",
    "- adanya missing value\n",
    "- adanya data yang tidak konsisten\n",
    "- terdapat outliers\n",
    "- data yang irrelevan terhadap target\n",
    "\n",
    "sehingga pada tahap awal perlu dilakukan data cleaning, khususnya pengecekan terhadap adanya missing value dan data yang tidak konsisten.\n",
    "\n",
    "missing value susah untuk diinterpretasikan oleh machine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ** ekstrak data pada kolom 'date' **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['month'] = df['date'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = df['date'].dt.day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['month'] = df['date'].dt.month_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>date</th>\n",
       "      <th>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>geography</th>\n",
       "      <th>Date</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-04</td>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  average_price  total_volume       4046       4225      4770  \\\n",
       "0 2015-01-04           1.22      40873.28    2819.50   28287.42     49.90   \n",
       "1 2015-01-04           1.79       1373.95      57.42     153.88      0.00   \n",
       "2 2015-01-04           1.00     435021.49  364302.39   23821.16     82.15   \n",
       "3 2015-01-04           1.76       3846.69    1500.15     938.35      0.00   \n",
       "4 2015-01-04           1.08     788025.06   53987.31  552906.04  39995.03   \n",
       "\n",
       "   total_bags  small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0     9716.46     9186.93      529.53          0.0  conventional  2015   \n",
       "1     1162.65     1162.65        0.00          0.0       organic  2015   \n",
       "2    46815.79    16707.15    30108.64          0.0  conventional  2015   \n",
       "3     1408.19     1071.35      336.84          0.0       organic  2015   \n",
       "4   141136.68   137146.07     3990.61          0.0  conventional  2015   \n",
       "\n",
       "              geography  Date    month  \n",
       "0                Albany     4  January  \n",
       "1                Albany     4  January  \n",
       "2               Atlanta     4  January  \n",
       "3               Atlanta     4  January  \n",
       "4  Baltimore/Washington     4  January  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['month_num'] = df['date'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=['date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>geography</th>\n",
       "      <th>Date</th>\n",
       "      <th>month</th>\n",
       "      <th>month_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   average_price  total_volume       4046       4225      4770  total_bags  \\\n",
       "0           1.22      40873.28    2819.50   28287.42     49.90     9716.46   \n",
       "1           1.79       1373.95      57.42     153.88      0.00     1162.65   \n",
       "2           1.00     435021.49  364302.39   23821.16     82.15    46815.79   \n",
       "3           1.76       3846.69    1500.15     938.35      0.00     1408.19   \n",
       "4           1.08     788025.06   53987.31  552906.04  39995.03   141136.68   \n",
       "\n",
       "   small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0     9186.93      529.53          0.0  conventional  2015   \n",
       "1     1162.65        0.00          0.0       organic  2015   \n",
       "2    16707.15    30108.64          0.0  conventional  2015   \n",
       "3     1071.35      336.84          0.0       organic  2015   \n",
       "4   137146.07     3990.61          0.0  conventional  2015   \n",
       "\n",
       "              geography  Date    month  month_num  \n",
       "0                Albany     4  January          1  \n",
       "1                Albany     4  January          1  \n",
       "2               Atlanta     4  January          1  \n",
       "3               Atlanta     4  January          1  \n",
       "4  Baltimore/Washington     4  January          1  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ** Tambah keterangan kolom region pada df **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Albany' 'Atlanta' 'Baltimore/Washington' 'Boise' 'Boston'\n",
      " 'Buffalo/Rochester' 'California' 'Charlotte' 'Chicago'\n",
      " 'Cincinnati/Dayton' 'Columbus' 'Dallas/Ft. Worth' 'Denver' 'Detroit'\n",
      " 'Grand Rapids' 'Great Lakes' 'Harrisburg/Scranton' 'Hartford/Springfield'\n",
      " 'Houston' 'Indianapolis' 'Jacksonville' 'Las Vegas' 'Los Angeles'\n",
      " 'Louisville' 'Miami/Ft. Lauderdale' 'Midsouth' 'Nashville'\n",
      " 'New Orleans/Mobile' 'New York' 'Northeast' 'Northern New England'\n",
      " 'Orlando' 'Philadelphia' 'Phoenix/Tucson' 'Pittsburgh' 'Plains'\n",
      " 'Portland' 'Raleigh/Greensboro' 'Richmond/Norfolk' 'Roanoke' 'Sacramento'\n",
      " 'San Diego' 'San Francisco' 'Seattle' 'South Carolina' 'South Central'\n",
      " 'Southeast' 'Spokane' 'St. Louis' 'Syracuse' 'Tampa' 'Total U.S.' 'West'\n",
      " 'West Tex/New Mexico']\n"
     ]
    }
   ],
   "source": [
    "print(df['geography'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['geography'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "terdapat data yang inkonsisten dalam kolom 'geography' >> selain data nama-nama kota di AS, di kolom ini juga terdapat data 'Total_US', dan region-region di AS (Midsouth, Northeast, Southeast, South Central, West) >> didrop sehingga mendapatkan data keseluruhan hanya city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean = df[(df['geography'] != 'Total U.S.') & (df['geography'] != 'Midsouth') & (df['geography'] != 'Northeast') & (df['geography'] != 'South Central') & (df['geography'] != 'Southeast')\n",
    "& (df['geography'] != 'West')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>geography</th>\n",
       "      <th>Date</th>\n",
       "      <th>month</th>\n",
       "      <th>month_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30012</th>\n",
       "      <td>1.63</td>\n",
       "      <td>1798.00</td>\n",
       "      <td>249.00</td>\n",
       "      <td>143.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1407.00</td>\n",
       "      <td>1111.00</td>\n",
       "      <td>295.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>Syracuse</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30013</th>\n",
       "      <td>1.12</td>\n",
       "      <td>591420.66</td>\n",
       "      <td>238039.66</td>\n",
       "      <td>16379.48</td>\n",
       "      <td>151.06</td>\n",
       "      <td>336846.03</td>\n",
       "      <td>276681.61</td>\n",
       "      <td>60134.42</td>\n",
       "      <td>30.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2020</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30014</th>\n",
       "      <td>1.19</td>\n",
       "      <td>14134.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13933.00</td>\n",
       "      <td>13933.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30019</th>\n",
       "      <td>0.89</td>\n",
       "      <td>1240709.05</td>\n",
       "      <td>430203.10</td>\n",
       "      <td>126497.28</td>\n",
       "      <td>21104.42</td>\n",
       "      <td>662904.25</td>\n",
       "      <td>395909.35</td>\n",
       "      <td>265177.09</td>\n",
       "      <td>1817.81</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2020</td>\n",
       "      <td>West Tex/New Mexico</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30020</th>\n",
       "      <td>1.58</td>\n",
       "      <td>36881.00</td>\n",
       "      <td>1147.00</td>\n",
       "      <td>1243.00</td>\n",
       "      <td>2645.00</td>\n",
       "      <td>31846.00</td>\n",
       "      <td>25621.00</td>\n",
       "      <td>6225.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>West Tex/New Mexico</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26685 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       average_price  total_volume       4046       4225      4770  \\\n",
       "0               1.22      40873.28    2819.50   28287.42     49.90   \n",
       "1               1.79       1373.95      57.42     153.88      0.00   \n",
       "2               1.00     435021.49  364302.39   23821.16     82.15   \n",
       "3               1.76       3846.69    1500.15     938.35      0.00   \n",
       "4               1.08     788025.06   53987.31  552906.04  39995.03   \n",
       "...              ...           ...        ...        ...       ...   \n",
       "30012           1.63       1798.00     249.00     143.00      0.00   \n",
       "30013           1.12     591420.66  238039.66   16379.48    151.06   \n",
       "30014           1.19      14134.00     200.00       0.00      0.00   \n",
       "30019           0.89    1240709.05  430203.10  126497.28  21104.42   \n",
       "30020           1.58      36881.00    1147.00    1243.00   2645.00   \n",
       "\n",
       "       total_bags  small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0         9716.46     9186.93      529.53         0.00  conventional  2015   \n",
       "1         1162.65     1162.65        0.00         0.00       organic  2015   \n",
       "2        46815.79    16707.15    30108.64         0.00  conventional  2015   \n",
       "3         1408.19     1071.35      336.84         0.00       organic  2015   \n",
       "4       141136.68   137146.07     3990.61         0.00  conventional  2015   \n",
       "...           ...         ...         ...          ...           ...   ...   \n",
       "30012     1407.00     1111.00      295.00         0.00       organic  2020   \n",
       "30013   336846.03   276681.61    60134.42        30.00  conventional  2020   \n",
       "30014    13933.00    13933.00        0.00         0.00       organic  2020   \n",
       "30019   662904.25   395909.35   265177.09      1817.81  conventional  2020   \n",
       "30020    31846.00    25621.00     6225.00         0.00       organic  2020   \n",
       "\n",
       "                  geography  Date    month  month_num  \n",
       "0                    Albany     4  January          1  \n",
       "1                    Albany     4  January          1  \n",
       "2                   Atlanta     4  January          1  \n",
       "3                   Atlanta     4  January          1  \n",
       "4      Baltimore/Washington     4  January          1  \n",
       "...                     ...   ...      ...        ...  \n",
       "30012              Syracuse    17      May          5  \n",
       "30013                 Tampa    17      May          5  \n",
       "30014                 Tampa    17      May          5  \n",
       "30019   West Tex/New Mexico    17      May          5  \n",
       "30020   West Tex/New Mexico    17      May          5  \n",
       "\n",
       "[26685 rows x 15 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Albany', 'Atlanta', 'Baltimore/Washington', 'Boise', 'Boston',\n",
       "       'Buffalo/Rochester', 'California', 'Charlotte', 'Chicago',\n",
       "       'Cincinnati/Dayton', 'Columbus', 'Dallas/Ft. Worth', 'Denver',\n",
       "       'Detroit', 'Grand Rapids', 'Great Lakes', 'Harrisburg/Scranton',\n",
       "       'Hartford/Springfield', 'Houston', 'Indianapolis', 'Jacksonville',\n",
       "       'Las Vegas', 'Los Angeles', 'Louisville', 'Miami/Ft. Lauderdale',\n",
       "       'Nashville', 'New Orleans/Mobile', 'New York',\n",
       "       'Northern New England', 'Orlando', 'Philadelphia',\n",
       "       'Phoenix/Tucson', 'Pittsburgh', 'Plains', 'Portland',\n",
       "       'Raleigh/Greensboro', 'Richmond/Norfolk', 'Roanoke', 'Sacramento',\n",
       "       'San Diego', 'San Francisco', 'Seattle', 'South Carolina',\n",
       "       'Spokane', 'St. Louis', 'Syracuse', 'Tampa', 'West Tex/New Mexico'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean['geography'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def region(df_clean):    \n",
    "    if df_clean['geography'] == 'Albany' or df_clean['geography'] == 'Baltimore/Washington' or df_clean['geography'] == 'Boston' or df_clean['geography'] == 'Buffalo/Rochester' or df_clean['geography'] == 'Harrisburg/Scranton' or df_clean['geography'] == 'Hartford/Springfield' or df_clean['geography'] == 'New York' or df_clean['geography'] == 'Northern New England' or df_clean['geography'] == 'Philadelphia' or df_clean['geography'] == 'Pittsburgh' or df_clean['geography'] == 'Syracuse':\n",
    "        return 'Northeastern'\n",
    "    if df_clean['geography'] == 'Atlanta' or df_clean['geography'] == 'Charlotte' or df_clean['geography'] == 'Jacksonville' or df_clean['geography'] == 'Louisville' or df_clean['geography'] == 'Miami/Ft. Lauderdale' or df_clean['geography'] == 'Nashville' or df_clean['geography'] == 'New Orleans/Mobile' or df_clean['geography'] == 'Orlando' or df_clean['geography'] == 'Raleigh/Greensboro' or df_clean['geography'] == 'Richmond/Norfolk' or df_clean['geography'] == 'Roanoke' or df_clean['geography'] == 'South Carolina' or df_clean['geography'] == 'Tampa':\n",
    "        return 'Southeastern'\n",
    "    if df_clean['geography'] == 'Boise' or df_clean['geography'] == 'California' or df_clean['geography'] == 'Denver' or df_clean['geography'] == 'Las Vegas' or df_clean['geography'] == 'Los Angeles' or df_clean['geography'] == 'Plains' or df_clean['geography'] == 'Portland' or df_clean['geography'] == 'Sacramento' or df_clean['geography'] == 'San Diego' or df_clean['geography'] == 'San Francisco' or df_clean['geography'] == 'Seattle' or df_clean['geography'] == 'Spokane':\n",
    "        return 'Western'\n",
    "    if df_clean['geography'] == 'Chicago' or df_clean['geography'] == 'Cincinnati/Dayton' or df_clean['geography'] == 'Columbus' or df_clean['geography'] == 'Detroit' or df_clean['geography'] == 'Grand Rapids' or df_clean['geography'] == 'Great Lakes' or df_clean['geography'] == 'Indianapolis' or df_clean['geography'] == 'St. Louis':\n",
    "        return 'Midwestern'\n",
    "    if df_clean['geography'] == 'Dallas/Ft. Worth' or df_clean['geography'] == 'Houston' or df_clean['geography'] == 'Phoenix/Tucson' or df_clean['geography'] == 'West Tex/New Mexico':\n",
    "        return 'Southwestern'\n",
    "#     return 'Other'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_clean['region'] = df_clean.apply(lambda df_clean: region(df_clean), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "region = df_clean.apply(lambda df_clean: region(df_clean), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full = pd.concat([df_clean, region], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full.rename(columns={0: 'region', 'geography': 'city', 'Date': 'date'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>average_price</th>\n",
       "      <th>total_volume</th>\n",
       "      <th>4046</th>\n",
       "      <th>4225</th>\n",
       "      <th>4770</th>\n",
       "      <th>total_bags</th>\n",
       "      <th>small_bags</th>\n",
       "      <th>large_bags</th>\n",
       "      <th>xlarge_bags</th>\n",
       "      <th>type</th>\n",
       "      <th>year</th>\n",
       "      <th>city</th>\n",
       "      <th>date</th>\n",
       "      <th>month</th>\n",
       "      <th>month_num</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.22</td>\n",
       "      <td>40873.28</td>\n",
       "      <td>2819.50</td>\n",
       "      <td>28287.42</td>\n",
       "      <td>49.90</td>\n",
       "      <td>9716.46</td>\n",
       "      <td>9186.93</td>\n",
       "      <td>529.53</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>Northeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.79</td>\n",
       "      <td>1373.95</td>\n",
       "      <td>57.42</td>\n",
       "      <td>153.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>1162.65</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Albany</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>Northeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.00</td>\n",
       "      <td>435021.49</td>\n",
       "      <td>364302.39</td>\n",
       "      <td>23821.16</td>\n",
       "      <td>82.15</td>\n",
       "      <td>46815.79</td>\n",
       "      <td>16707.15</td>\n",
       "      <td>30108.64</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>Southeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.76</td>\n",
       "      <td>3846.69</td>\n",
       "      <td>1500.15</td>\n",
       "      <td>938.35</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1408.19</td>\n",
       "      <td>1071.35</td>\n",
       "      <td>336.84</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2015</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>Southeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.08</td>\n",
       "      <td>788025.06</td>\n",
       "      <td>53987.31</td>\n",
       "      <td>552906.04</td>\n",
       "      <td>39995.03</td>\n",
       "      <td>141136.68</td>\n",
       "      <td>137146.07</td>\n",
       "      <td>3990.61</td>\n",
       "      <td>0.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2015</td>\n",
       "      <td>Baltimore/Washington</td>\n",
       "      <td>4</td>\n",
       "      <td>January</td>\n",
       "      <td>1</td>\n",
       "      <td>Northeastern</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30012</th>\n",
       "      <td>1.63</td>\n",
       "      <td>1798.00</td>\n",
       "      <td>249.00</td>\n",
       "      <td>143.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1407.00</td>\n",
       "      <td>1111.00</td>\n",
       "      <td>295.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>Syracuse</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>Northeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30013</th>\n",
       "      <td>1.12</td>\n",
       "      <td>591420.66</td>\n",
       "      <td>238039.66</td>\n",
       "      <td>16379.48</td>\n",
       "      <td>151.06</td>\n",
       "      <td>336846.03</td>\n",
       "      <td>276681.61</td>\n",
       "      <td>60134.42</td>\n",
       "      <td>30.00</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2020</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30014</th>\n",
       "      <td>1.19</td>\n",
       "      <td>14134.00</td>\n",
       "      <td>200.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13933.00</td>\n",
       "      <td>13933.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30019</th>\n",
       "      <td>0.89</td>\n",
       "      <td>1240709.05</td>\n",
       "      <td>430203.10</td>\n",
       "      <td>126497.28</td>\n",
       "      <td>21104.42</td>\n",
       "      <td>662904.25</td>\n",
       "      <td>395909.35</td>\n",
       "      <td>265177.09</td>\n",
       "      <td>1817.81</td>\n",
       "      <td>conventional</td>\n",
       "      <td>2020</td>\n",
       "      <td>West Tex/New Mexico</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>Southwestern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30020</th>\n",
       "      <td>1.58</td>\n",
       "      <td>36881.00</td>\n",
       "      <td>1147.00</td>\n",
       "      <td>1243.00</td>\n",
       "      <td>2645.00</td>\n",
       "      <td>31846.00</td>\n",
       "      <td>25621.00</td>\n",
       "      <td>6225.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>organic</td>\n",
       "      <td>2020</td>\n",
       "      <td>West Tex/New Mexico</td>\n",
       "      <td>17</td>\n",
       "      <td>May</td>\n",
       "      <td>5</td>\n",
       "      <td>Southwestern</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26685 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       average_price  total_volume       4046       4225      4770  \\\n",
       "0               1.22      40873.28    2819.50   28287.42     49.90   \n",
       "1               1.79       1373.95      57.42     153.88      0.00   \n",
       "2               1.00     435021.49  364302.39   23821.16     82.15   \n",
       "3               1.76       3846.69    1500.15     938.35      0.00   \n",
       "4               1.08     788025.06   53987.31  552906.04  39995.03   \n",
       "...              ...           ...        ...        ...       ...   \n",
       "30012           1.63       1798.00     249.00     143.00      0.00   \n",
       "30013           1.12     591420.66  238039.66   16379.48    151.06   \n",
       "30014           1.19      14134.00     200.00       0.00      0.00   \n",
       "30019           0.89    1240709.05  430203.10  126497.28  21104.42   \n",
       "30020           1.58      36881.00    1147.00    1243.00   2645.00   \n",
       "\n",
       "       total_bags  small_bags  large_bags  xlarge_bags          type  year  \\\n",
       "0         9716.46     9186.93      529.53         0.00  conventional  2015   \n",
       "1         1162.65     1162.65        0.00         0.00       organic  2015   \n",
       "2        46815.79    16707.15    30108.64         0.00  conventional  2015   \n",
       "3         1408.19     1071.35      336.84         0.00       organic  2015   \n",
       "4       141136.68   137146.07     3990.61         0.00  conventional  2015   \n",
       "...           ...         ...         ...          ...           ...   ...   \n",
       "30012     1407.00     1111.00      295.00         0.00       organic  2020   \n",
       "30013   336846.03   276681.61    60134.42        30.00  conventional  2020   \n",
       "30014    13933.00    13933.00        0.00         0.00       organic  2020   \n",
       "30019   662904.25   395909.35   265177.09      1817.81  conventional  2020   \n",
       "30020    31846.00    25621.00     6225.00         0.00       organic  2020   \n",
       "\n",
       "                       city  date    month  month_num        region  \n",
       "0                    Albany     4  January          1  Northeastern  \n",
       "1                    Albany     4  January          1  Northeastern  \n",
       "2                   Atlanta     4  January          1  Southeastern  \n",
       "3                   Atlanta     4  January          1  Southeastern  \n",
       "4      Baltimore/Washington     4  January          1  Northeastern  \n",
       "...                     ...   ...      ...        ...           ...  \n",
       "30012              Syracuse    17      May          5  Northeastern  \n",
       "30013                 Tampa    17      May          5  Southeastern  \n",
       "30014                 Tampa    17      May          5  Southeastern  \n",
       "30019   West Tex/New Mexico    17      May          5  Southwestern  \n",
       "30020   West Tex/New Mexico    17      May          5  Southwestern  \n",
       "\n",
       "[26685 rows x 16 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_full.to_csv('./data_csv/updated_city_region.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}