US_honey_1995_to_2021.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "57cd5c42",
   "metadata": {},
   "source": [
    "# Wrangling honey dataset of United States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "864a5ca1",
   "metadata": {},
   "source": [
    "#### Annually USDA publishes data related to honeybee production in its website. But the published report are in separate pdf or txt formats. Today, I am playing with those published reports trying to obtain a fully clean datasets "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "480c564c",
   "metadata": {},
   "source": [
    "Download reports of each year from 1996 to 2022 from here: \n",
    "\n",
    "https://usda.library.cornell.edu/concern/publications/hd76s004z?locale=en&page=3#release-items"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0fe8bee",
   "metadata": {},
   "source": [
    "##### alternatively, these files can be downloaded from my github repo also"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d22e0fa",
   "metadata": {},
   "source": [
    "## Don't forget to provide feedbacks as it is my first notebook on kaggle !"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f9daffa",
   "metadata": {},
   "source": [
    "In each .txt files too much unwanted information is present. So , first of all we need to extract only tabular data present in certain lines.\n",
    "Unfortunately, required tabular data containing state name, yield, number of colony, etc are present in different line number in different text files.\n",
    "So we need to determine the line number from which required data starts and ends. Those number are used while defining function to clean the data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55353a52",
   "metadata": {},
   "source": [
    "## Defining a function to convert downloaded txt files into a pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "7a6cbc12",
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_cleaner(file,year,start,end):\n",
    "    import pandas as pd\n",
    "        \n",
    "    with open(file) as f:\n",
    "        full_content = f.readlines()\n",
    "        # retrieving useful lines only\n",
    "        content = full_content[start:end]\n",
    "        \n",
    "        my_contents = []\n",
    "        for i in content:\n",
    "            j = \"#\".join(i.split())\n",
    "            my_contents.append(j)\n",
    "\n",
    "        # converting list into dataframe\n",
    "        df = pd.DataFrame(my_contents)\n",
    "        splitted = df[0].str.split(\"#\",expand=True)\n",
    "        \n",
    "        # dropping unwanted column\n",
    "        df1 = splitted.drop(1, axis=1)\n",
    "\n",
    "        # assigning labels to column\n",
    "        headings = ['state','colonies_number','yield_per_colony','production','stocks','average_price','value_of_production']\n",
    "        df1.columns = headings\n",
    "        \n",
    "        # adding new column year\n",
    "        df1[\"year\"] = pd.Series([year for x in range(len(df1.index))])\n",
    "\n",
    "        # saving dataframe as csv file\n",
    "        df1.to_csv(f'{year}.csv')  \n",
    "        \n",
    "        #returning shape of the final dataframe\n",
    "        print(df1.shape)\n",
    "\n",
    "           \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "863eeaca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(44, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('1996.txt',1995,133,177)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "b04f900b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(44, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('1997.txt',1996,136,180)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9469156f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(43, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('1998.txt',1997,145,188)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "bc64fd1c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(43, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('1999.txt',1998,150,193)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "f6e5dd1f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(43, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2000.txt',1999,143,186)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "d1684621",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(43, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2001.txt',2000,147,190)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9b86e18b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(43, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2002.txt',2001,153,196)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "e52ba545",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(44, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2003.txt',2002,152,196)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "282a7c4c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(44, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2004.txt',2003,158,202)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "03988b47",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(40, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2005.txt',2004,155,195)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "da7a44ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2006.txt',2005,158,199)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e51976e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2007.txt',2006,153,194)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f4febd41",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2008.txt',2007,140,181)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "7632e6e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2009.txt',2008,143,184)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "bf72c348",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(41, 8)\n"
     ]
    }
   ],
   "source": [
    "data_cleaner('2010.txt',2009,141,182)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "496b112c",
   "metadata": {},
   "source": [
    "## combining downloaded csv files into single csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "4d6b68a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_files = ['1995.csv','1996.csv','1997.csv','1998.csv','1999.csv','2000.csv','2001.csv','2002.csv','2003.csv','2004.csv','2005.csv','2006.csv','2007.csv','2008.csv','2009.csv']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "88a9d541",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(csv_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "1300b6ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   state colonies_number yield_per_colony production stocks average_price  \\\n",
      "0     AL              16               58        928     28            62   \n",
      "1     AZ              52               79      4,108    986            68   \n",
      "2     AR              50               60      3,000    900            64   \n",
      "3     CA             420               93     39,060  4,687            60   \n",
      "4     CO              45               60      2,700  1,404            68   \n",
      "..   ...             ...              ...        ...    ...           ...   \n",
      "36    VA               6               39        234     56           328   \n",
      "37    WA              62               44      2,728  1,064           149   \n",
      "38    WV               5               37        185     33           267   \n",
      "39    WI              63               60      3,780  1,588           151   \n",
      "40    WY              37               48      1,776    391           143   \n",
      "\n",
      "   value_of_production  Unnamed: 0    year  \n",
      "0                  575         0.0  1995.0  \n",
      "1                2,793         1.0  1995.0  \n",
      "2                1,920         2.0  1995.0  \n",
      "3               23,436         3.0  1995.0  \n",
      "4                1,836         4.0  1995.0  \n",
      "..                 ...         ...     ...  \n",
      "36                 768        36.0  2009.0  \n",
      "37               4,065        37.0  2009.0  \n",
      "38                 494        38.0  2009.0  \n",
      "39               5,708        39.0  2009.0  \n",
      "40               2,540        40.0  2009.0  \n",
      "\n",
      "[636 rows x 9 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n",
      "/tmp/ipykernel_9164/2653401489.py:10: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_combined = df_combined.append(variable)\n"
     ]
    }
   ],
   "source": [
    "headings = ['state','colonies_number','yield_per_colony','production','stocks','average_price','value_of_production']\n",
    "df_combined = pd.DataFrame(columns = headings)\n",
    "\n",
    "for i in csv_files:\n",
    "    variable = \"df\"+i[0:4]\n",
    "    combined.append(variable)\n",
    "#     print(variable)\n",
    "    variable = pd.read_csv(i)\n",
    "#     print(variable)\n",
    "    df_combined = df_combined.append(variable)\n",
    "\n",
    "# print(combined)\n",
    "\n",
    "print(df_combined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "bbc03596",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(636, 9)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "898d3793",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state', 'colonies_number', 'yield_per_colony', 'production', 'stocks',\n",
       "       'average_price', 'value_of_production', 'Unnamed: 0', 'year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "0301f9a9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 636 entries, 0 to 40\n",
      "Series name: Unnamed: 0\n",
      "Non-Null Count  Dtype  \n",
      "--------------  -----  \n",
      "636 non-null    float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 9.9 KB\n"
     ]
    }
   ],
   "source": [
    "df_combined['Unnamed: 0'].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16021c4a",
   "metadata": {},
   "source": [
    "##### removing unwanted column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "c0799139",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined.drop(['Unnamed: 0'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "63c125c0",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>16</td>\n",
       "      <td>58</td>\n",
       "      <td>928</td>\n",
       "      <td>28</td>\n",
       "      <td>62</td>\n",
       "      <td>575</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>52</td>\n",
       "      <td>79</td>\n",
       "      <td>4,108</td>\n",
       "      <td>986</td>\n",
       "      <td>68</td>\n",
       "      <td>2,793</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>50</td>\n",
       "      <td>60</td>\n",
       "      <td>3,000</td>\n",
       "      <td>900</td>\n",
       "      <td>64</td>\n",
       "      <td>1,920</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>420</td>\n",
       "      <td>93</td>\n",
       "      <td>39,060</td>\n",
       "      <td>4,687</td>\n",
       "      <td>60</td>\n",
       "      <td>23,436</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CO</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>2,700</td>\n",
       "      <td>1,404</td>\n",
       "      <td>68</td>\n",
       "      <td>1,836</td>\n",
       "      <td>1995.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>VA</td>\n",
       "      <td>6</td>\n",
       "      <td>39</td>\n",
       "      <td>234</td>\n",
       "      <td>56</td>\n",
       "      <td>328</td>\n",
       "      <td>768</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>WA</td>\n",
       "      <td>62</td>\n",
       "      <td>44</td>\n",
       "      <td>2,728</td>\n",
       "      <td>1,064</td>\n",
       "      <td>149</td>\n",
       "      <td>4,065</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>WV</td>\n",
       "      <td>5</td>\n",
       "      <td>37</td>\n",
       "      <td>185</td>\n",
       "      <td>33</td>\n",
       "      <td>267</td>\n",
       "      <td>494</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>WI</td>\n",
       "      <td>63</td>\n",
       "      <td>60</td>\n",
       "      <td>3,780</td>\n",
       "      <td>1,588</td>\n",
       "      <td>151</td>\n",
       "      <td>5,708</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>WY</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>1,776</td>\n",
       "      <td>391</td>\n",
       "      <td>143</td>\n",
       "      <td>2,540</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>636 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state colonies_number yield_per_colony production stocks average_price  \\\n",
       "0     AL              16               58        928     28            62   \n",
       "1     AZ              52               79      4,108    986            68   \n",
       "2     AR              50               60      3,000    900            64   \n",
       "3     CA             420               93     39,060  4,687            60   \n",
       "4     CO              45               60      2,700  1,404            68   \n",
       "..   ...             ...              ...        ...    ...           ...   \n",
       "36    VA               6               39        234     56           328   \n",
       "37    WA              62               44      2,728  1,064           149   \n",
       "38    WV               5               37        185     33           267   \n",
       "39    WI              63               60      3,780  1,588           151   \n",
       "40    WY              37               48      1,776    391           143   \n",
       "\n",
       "   value_of_production    year  \n",
       "0                  575  1995.0  \n",
       "1                2,793  1995.0  \n",
       "2                1,920  1995.0  \n",
       "3               23,436  1995.0  \n",
       "4                1,836  1995.0  \n",
       "..                 ...     ...  \n",
       "36                 768  2009.0  \n",
       "37               4,065  2009.0  \n",
       "38                 494  2009.0  \n",
       "39               5,708  2009.0  \n",
       "40               2,540  2009.0  \n",
       "\n",
       "[636 rows x 8 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aa10bfe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "a311ba6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state                   object\n",
       "colonies_number         object\n",
       "yield_per_colony        object\n",
       "production              object\n",
       "stocks                  object\n",
       "average_price           object\n",
       "value_of_production     object\n",
       "year                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "fda314aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined['year'] = df_combined['year'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "d60e3c98",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>16</td>\n",
       "      <td>58</td>\n",
       "      <td>928</td>\n",
       "      <td>28</td>\n",
       "      <td>62</td>\n",
       "      <td>575</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>52</td>\n",
       "      <td>79</td>\n",
       "      <td>4,108</td>\n",
       "      <td>986</td>\n",
       "      <td>68</td>\n",
       "      <td>2,793</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>50</td>\n",
       "      <td>60</td>\n",
       "      <td>3,000</td>\n",
       "      <td>900</td>\n",
       "      <td>64</td>\n",
       "      <td>1,920</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>420</td>\n",
       "      <td>93</td>\n",
       "      <td>39,060</td>\n",
       "      <td>4,687</td>\n",
       "      <td>60</td>\n",
       "      <td>23,436</td>\n",
       "      <td>1995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CO</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>2,700</td>\n",
       "      <td>1,404</td>\n",
       "      <td>68</td>\n",
       "      <td>1,836</td>\n",
       "      <td>1995.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>VA</td>\n",
       "      <td>6</td>\n",
       "      <td>39</td>\n",
       "      <td>234</td>\n",
       "      <td>56</td>\n",
       "      <td>328</td>\n",
       "      <td>768</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>WA</td>\n",
       "      <td>62</td>\n",
       "      <td>44</td>\n",
       "      <td>2,728</td>\n",
       "      <td>1,064</td>\n",
       "      <td>149</td>\n",
       "      <td>4,065</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>WV</td>\n",
       "      <td>5</td>\n",
       "      <td>37</td>\n",
       "      <td>185</td>\n",
       "      <td>33</td>\n",
       "      <td>267</td>\n",
       "      <td>494</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>WI</td>\n",
       "      <td>63</td>\n",
       "      <td>60</td>\n",
       "      <td>3,780</td>\n",
       "      <td>1,588</td>\n",
       "      <td>151</td>\n",
       "      <td>5,708</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>WY</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>1,776</td>\n",
       "      <td>391</td>\n",
       "      <td>143</td>\n",
       "      <td>2,540</td>\n",
       "      <td>2009.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>636 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state colonies_number yield_per_colony production stocks average_price  \\\n",
       "0     AL              16               58        928     28            62   \n",
       "1     AZ              52               79      4,108    986            68   \n",
       "2     AR              50               60      3,000    900            64   \n",
       "3     CA             420               93     39,060  4,687            60   \n",
       "4     CO              45               60      2,700  1,404            68   \n",
       "..   ...             ...              ...        ...    ...           ...   \n",
       "36    VA               6               39        234     56           328   \n",
       "37    WA              62               44      2,728  1,064           149   \n",
       "38    WV               5               37        185     33           267   \n",
       "39    WI              63               60      3,780  1,588           151   \n",
       "40    WY              37               48      1,776    391           143   \n",
       "\n",
       "   value_of_production    year  \n",
       "0                  575  1995.0  \n",
       "1                2,793  1995.0  \n",
       "2                1,920  1995.0  \n",
       "3               23,436  1995.0  \n",
       "4                1,836  1995.0  \n",
       "..                 ...     ...  \n",
       "36                 768  2009.0  \n",
       "37               4,065  2009.0  \n",
       "38                 494  2009.0  \n",
       "39               5,708  2009.0  \n",
       "40               2,540  2009.0  \n",
       "\n",
       "[636 rows x 8 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "d43e30b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined['year'] = pd.to_numeric(df_combined['year'],downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "b0ffaf37",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined['year'] = df_combined['year'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "7a95a986",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state                  object\n",
       "colonies_number        object\n",
       "yield_per_colony       object\n",
       "production             object\n",
       "stocks                 object\n",
       "average_price          object\n",
       "value_of_production    object\n",
       "year                   object\n",
       "dtype: object"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "1978d054",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'colonies_number'"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.columns[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "8c1e3c54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      16\n",
       "1      52\n",
       "2      50\n",
       "3     420\n",
       "4      45\n",
       "     ... \n",
       "36      6\n",
       "37     62\n",
       "38      5\n",
       "39     63\n",
       "40     37\n",
       "Name: colonies_number, Length: 636, dtype: object"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined[df_combined.columns[1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b2b0e59",
   "metadata": {},
   "source": [
    "##### converting datatypes of the columns :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "23c0555b",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_combined[df_combined.columns[1]] = pd.to_numeric(df_combined[df_combined.columns[1]],downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "8a842072",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_combined[df_combined.columns[2]] = pd.to_numeric(df_combined[df_combined.columns[2]],downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "7c73544d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[3]] = df_combined[df_combined.columns[3]].str.replace(',', '').astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "a5ae9d77",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[4]] = df_combined[df_combined.columns[4]].str.replace(',', '').astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "ca65355c",
   "metadata": {},
   "outputs": [],
   "source": [
    " df_combined[df_combined.columns[5]] = pd.to_numeric(df_combined[df_combined.columns[5]],downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "0bff260e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[6]] = df_combined[df_combined.columns[6]].str.replace(',', '').astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "b7f6015b",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>16</td>\n",
       "      <td>58</td>\n",
       "      <td>928</td>\n",
       "      <td>28</td>\n",
       "      <td>62</td>\n",
       "      <td>575</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>52</td>\n",
       "      <td>79</td>\n",
       "      <td>4108</td>\n",
       "      <td>986</td>\n",
       "      <td>68</td>\n",
       "      <td>2793</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>50</td>\n",
       "      <td>60</td>\n",
       "      <td>3000</td>\n",
       "      <td>900</td>\n",
       "      <td>64</td>\n",
       "      <td>1920</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>420</td>\n",
       "      <td>93</td>\n",
       "      <td>39060</td>\n",
       "      <td>4687</td>\n",
       "      <td>60</td>\n",
       "      <td>23436</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CO</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>2700</td>\n",
       "      <td>1404</td>\n",
       "      <td>68</td>\n",
       "      <td>1836</td>\n",
       "      <td>1995</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>VA</td>\n",
       "      <td>6</td>\n",
       "      <td>39</td>\n",
       "      <td>234</td>\n",
       "      <td>56</td>\n",
       "      <td>328</td>\n",
       "      <td>768</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>WA</td>\n",
       "      <td>62</td>\n",
       "      <td>44</td>\n",
       "      <td>2728</td>\n",
       "      <td>1064</td>\n",
       "      <td>149</td>\n",
       "      <td>4065</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>WV</td>\n",
       "      <td>5</td>\n",
       "      <td>37</td>\n",
       "      <td>185</td>\n",
       "      <td>33</td>\n",
       "      <td>267</td>\n",
       "      <td>494</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>WI</td>\n",
       "      <td>63</td>\n",
       "      <td>60</td>\n",
       "      <td>3780</td>\n",
       "      <td>1588</td>\n",
       "      <td>151</td>\n",
       "      <td>5708</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>WY</td>\n",
       "      <td>37</td>\n",
       "      <td>48</td>\n",
       "      <td>1776</td>\n",
       "      <td>391</td>\n",
       "      <td>143</td>\n",
       "      <td>2540</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>636 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  colonies_number  yield_per_colony  production  stocks  \\\n",
       "0     AL               16                58         928      28   \n",
       "1     AZ               52                79        4108     986   \n",
       "2     AR               50                60        3000     900   \n",
       "3     CA              420                93       39060    4687   \n",
       "4     CO               45                60        2700    1404   \n",
       "..   ...              ...               ...         ...     ...   \n",
       "36    VA                6                39         234      56   \n",
       "37    WA               62                44        2728    1064   \n",
       "38    WV                5                37         185      33   \n",
       "39    WI               63                60        3780    1588   \n",
       "40    WY               37                48        1776     391   \n",
       "\n",
       "    average_price  value_of_production  year  \n",
       "0              62                  575  1995  \n",
       "1              68                 2793  1995  \n",
       "2              64                 1920  1995  \n",
       "3              60                23436  1995  \n",
       "4              68                 1836  1995  \n",
       "..            ...                  ...   ...  \n",
       "36            328                  768  2009  \n",
       "37            149                 4065  2009  \n",
       "38            267                  494  2009  \n",
       "39            151                 5708  2009  \n",
       "40            143                 2540  2009  \n",
       "\n",
       "[636 rows x 8 columns]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "9297a89c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state                  object\n",
       "colonies_number         int16\n",
       "yield_per_colony        int16\n",
       "production              int64\n",
       "stocks                  int64\n",
       "average_price           int16\n",
       "value_of_production     int64\n",
       "year                   object\n",
       "dtype: object"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67eb5edb",
   "metadata": {},
   "source": [
    "##### multiplying colonies_number, production, stocks and value_of_production by 1,000 to get exact value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "44a48495",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[1]] = df_combined[df_combined.columns[1]].apply(lambda x: x*1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "fb6b56db",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[3]] = df_combined[df_combined.columns[3]].apply(lambda x: x*1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "e6de3223",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[4]] = df_combined[df_combined.columns[4]].apply(lambda x: x*1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "d6d1545c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined[df_combined.columns[6]] = df_combined[df_combined.columns[6]].apply(lambda x: x*1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "979ada16",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>16000</td>\n",
       "      <td>58</td>\n",
       "      <td>928000</td>\n",
       "      <td>28000</td>\n",
       "      <td>62</td>\n",
       "      <td>575000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AZ</td>\n",
       "      <td>52000</td>\n",
       "      <td>79</td>\n",
       "      <td>4108000</td>\n",
       "      <td>986000</td>\n",
       "      <td>68</td>\n",
       "      <td>2793000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AR</td>\n",
       "      <td>50000</td>\n",
       "      <td>60</td>\n",
       "      <td>3000000</td>\n",
       "      <td>900000</td>\n",
       "      <td>64</td>\n",
       "      <td>1920000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>420000</td>\n",
       "      <td>93</td>\n",
       "      <td>39060000</td>\n",
       "      <td>4687000</td>\n",
       "      <td>60</td>\n",
       "      <td>23436000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CO</td>\n",
       "      <td>45000</td>\n",
       "      <td>60</td>\n",
       "      <td>2700000</td>\n",
       "      <td>1404000</td>\n",
       "      <td>68</td>\n",
       "      <td>1836000</td>\n",
       "      <td>1995</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>VA</td>\n",
       "      <td>6000</td>\n",
       "      <td>39</td>\n",
       "      <td>234000</td>\n",
       "      <td>56000</td>\n",
       "      <td>328</td>\n",
       "      <td>768000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>WA</td>\n",
       "      <td>62000</td>\n",
       "      <td>44</td>\n",
       "      <td>2728000</td>\n",
       "      <td>1064000</td>\n",
       "      <td>149</td>\n",
       "      <td>4065000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>WV</td>\n",
       "      <td>5000</td>\n",
       "      <td>37</td>\n",
       "      <td>185000</td>\n",
       "      <td>33000</td>\n",
       "      <td>267</td>\n",
       "      <td>494000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>WI</td>\n",
       "      <td>63000</td>\n",
       "      <td>60</td>\n",
       "      <td>3780000</td>\n",
       "      <td>1588000</td>\n",
       "      <td>151</td>\n",
       "      <td>5708000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>WY</td>\n",
       "      <td>37000</td>\n",
       "      <td>48</td>\n",
       "      <td>1776000</td>\n",
       "      <td>391000</td>\n",
       "      <td>143</td>\n",
       "      <td>2540000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>636 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state  colonies_number  yield_per_colony  production   stocks  \\\n",
       "0     AL            16000                58      928000    28000   \n",
       "1     AZ            52000                79     4108000   986000   \n",
       "2     AR            50000                60     3000000   900000   \n",
       "3     CA           420000                93    39060000  4687000   \n",
       "4     CO            45000                60     2700000  1404000   \n",
       "..   ...              ...               ...         ...      ...   \n",
       "36    VA             6000                39      234000    56000   \n",
       "37    WA            62000                44     2728000  1064000   \n",
       "38    WV             5000                37      185000    33000   \n",
       "39    WI            63000                60     3780000  1588000   \n",
       "40    WY            37000                48     1776000   391000   \n",
       "\n",
       "    average_price  value_of_production  year  \n",
       "0              62               575000  1995  \n",
       "1              68              2793000  1995  \n",
       "2              64              1920000  1995  \n",
       "3              60             23436000  1995  \n",
       "4              68              1836000  1995  \n",
       "..            ...                  ...   ...  \n",
       "36            328               768000  2009  \n",
       "37            149              4065000  2009  \n",
       "38            267               494000  2009  \n",
       "39            151              5708000  2009  \n",
       "40            143              2540000  2009  \n",
       "\n",
       "[636 rows x 8 columns]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86d7e5a4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "974e143e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "51e0b740",
   "metadata": {},
   "source": [
    "### as we have clean data from 2009 to 2021 in kaggle already, let's combine this dataframe with that dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52ef5ec5",
   "metadata": {},
   "source": [
    "link to the kaggle dataset: https://www.kaggle.com/datasets/mohitpoudel/honey-production-in-us-20102021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ea1a4d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "bc7a23c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# downloading kaggle dataset:\n",
    "downloaded_data = pd.read_csv(\"US_honey_production_dataset.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "72bc776d",
   "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>Unnamed: 0</th>\n",
       "      <th>state</th>\n",
       "      <th>colony_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>productions</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_prod</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>9000</td>\n",
       "      <td>54</td>\n",
       "      <td>73000</td>\n",
       "      <td>73000</td>\n",
       "      <td>240.00</td>\n",
       "      <td>1166000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>24000</td>\n",
       "      <td>77</td>\n",
       "      <td>665000</td>\n",
       "      <td>665000</td>\n",
       "      <td>152.00</td>\n",
       "      <td>2809000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>25000</td>\n",
       "      <td>60</td>\n",
       "      <td>360000</td>\n",
       "      <td>360000</td>\n",
       "      <td>147.00</td>\n",
       "      <td>2205000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>California</td>\n",
       "      <td>410000</td>\n",
       "      <td>67</td>\n",
       "      <td>6318000</td>\n",
       "      <td>6318000</td>\n",
       "      <td>155.00</td>\n",
       "      <td>42579000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>34000</td>\n",
       "      <td>56</td>\n",
       "      <td>533000</td>\n",
       "      <td>533000</td>\n",
       "      <td>152.00</td>\n",
       "      <td>2894000</td>\n",
       "      <td>2010</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>474</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>40</td>\n",
       "      <td>79000</td>\n",
       "      <td>79000</td>\n",
       "      <td>8.23</td>\n",
       "      <td>1975000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>475</td>\n",
       "      <td>Washington</td>\n",
       "      <td>96000</td>\n",
       "      <td>32</td>\n",
       "      <td>1206000</td>\n",
       "      <td>1206000</td>\n",
       "      <td>2.52</td>\n",
       "      <td>7741000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>476</td>\n",
       "      <td>WestVirginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>43</td>\n",
       "      <td>136000</td>\n",
       "      <td>136000</td>\n",
       "      <td>4.80</td>\n",
       "      <td>1238000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>477</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>42000</td>\n",
       "      <td>47</td>\n",
       "      <td>750000</td>\n",
       "      <td>750000</td>\n",
       "      <td>2.81</td>\n",
       "      <td>5547000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>478</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>38000</td>\n",
       "      <td>58</td>\n",
       "      <td>242000</td>\n",
       "      <td>242000</td>\n",
       "      <td>2.07</td>\n",
       "      <td>4562000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>479 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0         state  colony_number  yield_per_colony  productions  \\\n",
       "0             0       Alabama           9000                54        73000   \n",
       "1             1       Arizona          24000                77       665000   \n",
       "2             2      Arkansas          25000                60       360000   \n",
       "3             3    California         410000                67      6318000   \n",
       "4             4      Colorado          34000                56       533000   \n",
       "..          ...           ...            ...               ...          ...   \n",
       "474         474      Virginia           6000                40        79000   \n",
       "475         475    Washington          96000                32      1206000   \n",
       "476         476  WestVirginia           6000                43       136000   \n",
       "477         477     Wisconsin          42000                47       750000   \n",
       "478         478       Wyoming          38000                58       242000   \n",
       "\n",
       "      stocks  average_price  value_of_prod  year  \n",
       "0      73000         240.00        1166000  2010  \n",
       "1     665000         152.00        2809000  2010  \n",
       "2     360000         147.00        2205000  2010  \n",
       "3    6318000         155.00       42579000  2010  \n",
       "4     533000         152.00        2894000  2010  \n",
       "..       ...            ...            ...   ...  \n",
       "474    79000           8.23        1975000  2021  \n",
       "475  1206000           2.52        7741000  2021  \n",
       "476   136000           4.80        1238000  2021  \n",
       "477   750000           2.81        5547000  2021  \n",
       "478   242000           2.07        4562000  2021  \n",
       "\n",
       "[479 rows x 9 columns]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "downloaded_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "867fbe20",
   "metadata": {},
   "outputs": [],
   "source": [
    "downloaded_data.drop(\"Unnamed: 0\",axis = 1,inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "e8d50332",
   "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>state</th>\n",
       "      <th>colony_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>productions</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_prod</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>9000</td>\n",
       "      <td>54</td>\n",
       "      <td>73000</td>\n",
       "      <td>73000</td>\n",
       "      <td>240.00</td>\n",
       "      <td>1166000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>24000</td>\n",
       "      <td>77</td>\n",
       "      <td>665000</td>\n",
       "      <td>665000</td>\n",
       "      <td>152.00</td>\n",
       "      <td>2809000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>25000</td>\n",
       "      <td>60</td>\n",
       "      <td>360000</td>\n",
       "      <td>360000</td>\n",
       "      <td>147.00</td>\n",
       "      <td>2205000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>410000</td>\n",
       "      <td>67</td>\n",
       "      <td>6318000</td>\n",
       "      <td>6318000</td>\n",
       "      <td>155.00</td>\n",
       "      <td>42579000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>34000</td>\n",
       "      <td>56</td>\n",
       "      <td>533000</td>\n",
       "      <td>533000</td>\n",
       "      <td>152.00</td>\n",
       "      <td>2894000</td>\n",
       "      <td>2010</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>40</td>\n",
       "      <td>79000</td>\n",
       "      <td>79000</td>\n",
       "      <td>8.23</td>\n",
       "      <td>1975000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Washington</td>\n",
       "      <td>96000</td>\n",
       "      <td>32</td>\n",
       "      <td>1206000</td>\n",
       "      <td>1206000</td>\n",
       "      <td>2.52</td>\n",
       "      <td>7741000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>WestVirginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>43</td>\n",
       "      <td>136000</td>\n",
       "      <td>136000</td>\n",
       "      <td>4.80</td>\n",
       "      <td>1238000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>42000</td>\n",
       "      <td>47</td>\n",
       "      <td>750000</td>\n",
       "      <td>750000</td>\n",
       "      <td>2.81</td>\n",
       "      <td>5547000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>38000</td>\n",
       "      <td>58</td>\n",
       "      <td>242000</td>\n",
       "      <td>242000</td>\n",
       "      <td>2.07</td>\n",
       "      <td>4562000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>479 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            state  colony_number  yield_per_colony  productions   stocks  \\\n",
       "0         Alabama           9000                54        73000    73000   \n",
       "1         Arizona          24000                77       665000   665000   \n",
       "2        Arkansas          25000                60       360000   360000   \n",
       "3      California         410000                67      6318000  6318000   \n",
       "4        Colorado          34000                56       533000   533000   \n",
       "..            ...            ...               ...          ...      ...   \n",
       "474      Virginia           6000                40        79000    79000   \n",
       "475    Washington          96000                32      1206000  1206000   \n",
       "476  WestVirginia           6000                43       136000   136000   \n",
       "477     Wisconsin          42000                47       750000   750000   \n",
       "478       Wyoming          38000                58       242000   242000   \n",
       "\n",
       "     average_price  value_of_prod  year  \n",
       "0           240.00        1166000  2010  \n",
       "1           152.00        2809000  2010  \n",
       "2           147.00        2205000  2010  \n",
       "3           155.00       42579000  2010  \n",
       "4           152.00        2894000  2010  \n",
       "..             ...            ...   ...  \n",
       "474           8.23        1975000  2021  \n",
       "475           2.52        7741000  2021  \n",
       "476           4.80        1238000  2021  \n",
       "477           2.81        5547000  2021  \n",
       "478           2.07        4562000  2021  \n",
       "\n",
       "[479 rows x 8 columns]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "downloaded_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "48116c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's have a look at column names of both dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "fa2d8375",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state', 'colonies_number', 'yield_per_colony', 'production', 'stocks',\n",
       "       'average_price', 'value_of_production', 'year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "01dd095a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state', 'colony_number', 'yield_per_colony', 'productions', 'stocks',\n",
       "       'average_price', 'value_of_prod', 'year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "downloaded_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "bca10ce9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True, False,  True, False,  True,  True, False,  True])"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "downloaded_data.columns == df_combined.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6454006a",
   "metadata": {},
   "source": [
    "#### making column names same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "b7b5f1be",
   "metadata": {},
   "outputs": [],
   "source": [
    "downloaded_data.columns = df_combined.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0280238",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "18b36fd6",
   "metadata": {},
   "source": [
    "### converting abbreviations in 'state' column to their full form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "91b126c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AL' 'AZ' 'AR' 'CA' 'CO' 'FL' 'GA' 'HI' 'ID' 'IL' 'IN' 'IA' 'KS' 'KY'\n",
      " 'LA' 'ME' 'MD' 'MI' 'MN' 'MS' 'MO' 'MT' 'NE' 'NV' 'NJ' 'NM' 'NY' 'NC'\n",
      " 'ND' 'OH' 'OK' 'OR' 'PA' 'SC' 'SD' 'TN' 'TX' 'UT' 'VT' 'VA' 'WA' 'WV'\n",
      " 'WI' 'WY']\n"
     ]
    }
   ],
   "source": [
    "print(df_combined['state'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "e60c43b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Alabama' 'Arizona' 'Arkansas' 'California' 'Colorado' 'Florida'\n",
      " 'Georgia' 'Hawaii' 'Idaho' 'Illinois' 'Indiana' 'Iowa' 'Kansas'\n",
      " 'Kentucky' 'Louisiana' 'Maine' 'Michigan' 'Minnesota' 'Mississippi'\n",
      " 'Missouri' 'Montana' 'Nebraska' 'NewJersey' 'NewMexico' 'NewYork'\n",
      " 'NorthCarolina' 'NorthDakota' 'Ohio' 'Oregon' 'Pennsylvania'\n",
      " 'SouthDakota' 'Tennessee' 'Texas' 'Utah' 'Vermont' 'Virginia'\n",
      " 'Washington' 'WestVirginia' 'Wisconsin' 'Wyoming' 'SouthCarolina']\n"
     ]
    }
   ],
   "source": [
    "print(downloaded_data['state'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "bd86987f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_combined['state'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "75e330bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(downloaded_data['state'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "321a635c",
   "metadata": {},
   "outputs": [],
   "source": [
    "US_states = {'AL':'Alabama', 'AZ':'Arizona', 'AR':'Arkansas', 'CA':'California', 'CO':'Colorado', 'FL':'Florida',\n",
    "            'GA':'Georgia', 'HI':'Hawaii', 'ID':'Idaho', 'IL':'Illinois', 'IN':'Indiana', 'IA':'Iowa', 'KS':'Kansas',\n",
    "            'KY':'Kentucky', 'LA':'Louisiana', 'ME':'Maine', 'MD':'Maryland', 'MI':'Michigan', 'MN':'Minnesota',\n",
    "            'MS':'Mississippi','MO':'Missouri', 'MT':'Montana', 'NE':'Nebraska', 'NV':'Nevada', 'NJ':'NewJersey',\n",
    "            'NM':'NewMexico', 'NY':'NewYork', 'NC':'NorthCarolina', 'ND':'NorthDakota', 'OH':'Ohio', 'OK':'Oklahoma',\n",
    "            'OR':'Oregon', 'PA':'Pennsylvania', 'SC':'SouthCarolina', 'SD':'SouthDakota', 'TN':'Tennessee', 'TX':'Texas',\n",
    "            'UT':'Utah', 'VT':'Vermont','VA':'Virginia','WA':'Washington','WV':'WestVirginia','WI':'Wisconsin','WY':'Wyoming'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "0ada7ef4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(US_states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "39ebf720",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined['state'] = df_combined['state'].replace(US_states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "5656f89a",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>16000</td>\n",
       "      <td>58</td>\n",
       "      <td>928000</td>\n",
       "      <td>28000</td>\n",
       "      <td>62</td>\n",
       "      <td>575000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>52000</td>\n",
       "      <td>79</td>\n",
       "      <td>4108000</td>\n",
       "      <td>986000</td>\n",
       "      <td>68</td>\n",
       "      <td>2793000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50000</td>\n",
       "      <td>60</td>\n",
       "      <td>3000000</td>\n",
       "      <td>900000</td>\n",
       "      <td>64</td>\n",
       "      <td>1920000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>420000</td>\n",
       "      <td>93</td>\n",
       "      <td>39060000</td>\n",
       "      <td>4687000</td>\n",
       "      <td>60</td>\n",
       "      <td>23436000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>45000</td>\n",
       "      <td>60</td>\n",
       "      <td>2700000</td>\n",
       "      <td>1404000</td>\n",
       "      <td>68</td>\n",
       "      <td>1836000</td>\n",
       "      <td>1995</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>39</td>\n",
       "      <td>234000</td>\n",
       "      <td>56000</td>\n",
       "      <td>328</td>\n",
       "      <td>768000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Washington</td>\n",
       "      <td>62000</td>\n",
       "      <td>44</td>\n",
       "      <td>2728000</td>\n",
       "      <td>1064000</td>\n",
       "      <td>149</td>\n",
       "      <td>4065000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>WestVirginia</td>\n",
       "      <td>5000</td>\n",
       "      <td>37</td>\n",
       "      <td>185000</td>\n",
       "      <td>33000</td>\n",
       "      <td>267</td>\n",
       "      <td>494000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>63000</td>\n",
       "      <td>60</td>\n",
       "      <td>3780000</td>\n",
       "      <td>1588000</td>\n",
       "      <td>151</td>\n",
       "      <td>5708000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>37000</td>\n",
       "      <td>48</td>\n",
       "      <td>1776000</td>\n",
       "      <td>391000</td>\n",
       "      <td>143</td>\n",
       "      <td>2540000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>636 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           state  colonies_number  yield_per_colony  production   stocks  \\\n",
       "0        Alabama            16000                58      928000    28000   \n",
       "1        Arizona            52000                79     4108000   986000   \n",
       "2       Arkansas            50000                60     3000000   900000   \n",
       "3     California           420000                93    39060000  4687000   \n",
       "4       Colorado            45000                60     2700000  1404000   \n",
       "..           ...              ...               ...         ...      ...   \n",
       "36      Virginia             6000                39      234000    56000   \n",
       "37    Washington            62000                44     2728000  1064000   \n",
       "38  WestVirginia             5000                37      185000    33000   \n",
       "39     Wisconsin            63000                60     3780000  1588000   \n",
       "40       Wyoming            37000                48     1776000   391000   \n",
       "\n",
       "    average_price  value_of_production  year  \n",
       "0              62               575000  1995  \n",
       "1              68              2793000  1995  \n",
       "2              64              1920000  1995  \n",
       "3              60             23436000  1995  \n",
       "4              68              1836000  1995  \n",
       "..            ...                  ...   ...  \n",
       "36            328               768000  2009  \n",
       "37            149              4065000  2009  \n",
       "38            267               494000  2009  \n",
       "39            151              5708000  2009  \n",
       "40            143              2540000  2009  \n",
       "\n",
       "[636 rows x 8 columns]"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "a9b0f7f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "my_df = pd.concat([df_combined,downloaded_data])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "83eb6bd9",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>16000</td>\n",
       "      <td>58</td>\n",
       "      <td>928000</td>\n",
       "      <td>28000</td>\n",
       "      <td>62.00</td>\n",
       "      <td>575000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>52000</td>\n",
       "      <td>79</td>\n",
       "      <td>4108000</td>\n",
       "      <td>986000</td>\n",
       "      <td>68.00</td>\n",
       "      <td>2793000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50000</td>\n",
       "      <td>60</td>\n",
       "      <td>3000000</td>\n",
       "      <td>900000</td>\n",
       "      <td>64.00</td>\n",
       "      <td>1920000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>420000</td>\n",
       "      <td>93</td>\n",
       "      <td>39060000</td>\n",
       "      <td>4687000</td>\n",
       "      <td>60.00</td>\n",
       "      <td>23436000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>45000</td>\n",
       "      <td>60</td>\n",
       "      <td>2700000</td>\n",
       "      <td>1404000</td>\n",
       "      <td>68.00</td>\n",
       "      <td>1836000</td>\n",
       "      <td>1995</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>40</td>\n",
       "      <td>79000</td>\n",
       "      <td>79000</td>\n",
       "      <td>8.23</td>\n",
       "      <td>1975000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Washington</td>\n",
       "      <td>96000</td>\n",
       "      <td>32</td>\n",
       "      <td>1206000</td>\n",
       "      <td>1206000</td>\n",
       "      <td>2.52</td>\n",
       "      <td>7741000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>WestVirginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>43</td>\n",
       "      <td>136000</td>\n",
       "      <td>136000</td>\n",
       "      <td>4.80</td>\n",
       "      <td>1238000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>42000</td>\n",
       "      <td>47</td>\n",
       "      <td>750000</td>\n",
       "      <td>750000</td>\n",
       "      <td>2.81</td>\n",
       "      <td>5547000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>38000</td>\n",
       "      <td>58</td>\n",
       "      <td>242000</td>\n",
       "      <td>242000</td>\n",
       "      <td>2.07</td>\n",
       "      <td>4562000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1115 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            state  colonies_number  yield_per_colony  production   stocks  \\\n",
       "0         Alabama            16000                58      928000    28000   \n",
       "1         Arizona            52000                79     4108000   986000   \n",
       "2        Arkansas            50000                60     3000000   900000   \n",
       "3      California           420000                93    39060000  4687000   \n",
       "4        Colorado            45000                60     2700000  1404000   \n",
       "..            ...              ...               ...         ...      ...   \n",
       "474      Virginia             6000                40       79000    79000   \n",
       "475    Washington            96000                32     1206000  1206000   \n",
       "476  WestVirginia             6000                43      136000   136000   \n",
       "477     Wisconsin            42000                47      750000   750000   \n",
       "478       Wyoming            38000                58      242000   242000   \n",
       "\n",
       "     average_price  value_of_production  year  \n",
       "0            62.00               575000  1995  \n",
       "1            68.00              2793000  1995  \n",
       "2            64.00              1920000  1995  \n",
       "3            60.00             23436000  1995  \n",
       "4            68.00              1836000  1995  \n",
       "..             ...                  ...   ...  \n",
       "474           8.23              1975000  2021  \n",
       "475           2.52              7741000  2021  \n",
       "476           4.80              1238000  2021  \n",
       "477           2.81              5547000  2021  \n",
       "478           2.07              4562000  2021  \n",
       "\n",
       "[1115 rows x 8 columns]"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "74983926",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Alabama', 'Arizona', 'Arkansas', 'California', 'Colorado',\n",
       "       'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana',\n",
       "       'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland',\n",
       "       'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana',\n",
       "       'Nebraska', 'Nevada', 'NewJersey', 'NewMexico', 'NewYork',\n",
       "       'NorthCarolina', 'NorthDakota', 'Ohio', 'Oklahoma', 'Oregon',\n",
       "       'Pennsylvania', 'SouthCarolina', 'SouthDakota', 'Tennessee',\n",
       "       'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington',\n",
       "       'WestVirginia', 'Wisconsin', 'Wyoming'], dtype=object)"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_df.state.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "07f352fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = my_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "a8c6a0f0",
   "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>state</th>\n",
       "      <th>colonies_number</th>\n",
       "      <th>yield_per_colony</th>\n",
       "      <th>production</th>\n",
       "      <th>stocks</th>\n",
       "      <th>average_price</th>\n",
       "      <th>value_of_production</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>16000</td>\n",
       "      <td>58</td>\n",
       "      <td>928000</td>\n",
       "      <td>28000</td>\n",
       "      <td>62.00</td>\n",
       "      <td>575000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>52000</td>\n",
       "      <td>79</td>\n",
       "      <td>4108000</td>\n",
       "      <td>986000</td>\n",
       "      <td>68.00</td>\n",
       "      <td>2793000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>50000</td>\n",
       "      <td>60</td>\n",
       "      <td>3000000</td>\n",
       "      <td>900000</td>\n",
       "      <td>64.00</td>\n",
       "      <td>1920000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>420000</td>\n",
       "      <td>93</td>\n",
       "      <td>39060000</td>\n",
       "      <td>4687000</td>\n",
       "      <td>60.00</td>\n",
       "      <td>23436000</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>45000</td>\n",
       "      <td>60</td>\n",
       "      <td>2700000</td>\n",
       "      <td>1404000</td>\n",
       "      <td>68.00</td>\n",
       "      <td>1836000</td>\n",
       "      <td>1995</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1110</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>40</td>\n",
       "      <td>79000</td>\n",
       "      <td>79000</td>\n",
       "      <td>8.23</td>\n",
       "      <td>1975000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1111</th>\n",
       "      <td>Washington</td>\n",
       "      <td>96000</td>\n",
       "      <td>32</td>\n",
       "      <td>1206000</td>\n",
       "      <td>1206000</td>\n",
       "      <td>2.52</td>\n",
       "      <td>7741000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1112</th>\n",
       "      <td>WestVirginia</td>\n",
       "      <td>6000</td>\n",
       "      <td>43</td>\n",
       "      <td>136000</td>\n",
       "      <td>136000</td>\n",
       "      <td>4.80</td>\n",
       "      <td>1238000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1113</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>42000</td>\n",
       "      <td>47</td>\n",
       "      <td>750000</td>\n",
       "      <td>750000</td>\n",
       "      <td>2.81</td>\n",
       "      <td>5547000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1114</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>38000</td>\n",
       "      <td>58</td>\n",
       "      <td>242000</td>\n",
       "      <td>242000</td>\n",
       "      <td>2.07</td>\n",
       "      <td>4562000</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1115 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  colonies_number  yield_per_colony  production   stocks  \\\n",
       "0          Alabama            16000                58      928000    28000   \n",
       "1          Arizona            52000                79     4108000   986000   \n",
       "2         Arkansas            50000                60     3000000   900000   \n",
       "3       California           420000                93    39060000  4687000   \n",
       "4         Colorado            45000                60     2700000  1404000   \n",
       "...            ...              ...               ...         ...      ...   \n",
       "1110      Virginia             6000                40       79000    79000   \n",
       "1111    Washington            96000                32     1206000  1206000   \n",
       "1112  WestVirginia             6000                43      136000   136000   \n",
       "1113     Wisconsin            42000                47      750000   750000   \n",
       "1114       Wyoming            38000                58      242000   242000   \n",
       "\n",
       "      average_price  value_of_production  year  \n",
       "0             62.00               575000  1995  \n",
       "1             68.00              2793000  1995  \n",
       "2             64.00              1920000  1995  \n",
       "3             60.00             23436000  1995  \n",
       "4             68.00              1836000  1995  \n",
       "...             ...                  ...   ...  \n",
       "1110           8.23              1975000  2021  \n",
       "1111           2.52              7741000  2021  \n",
       "1112           4.80              1238000  2021  \n",
       "1113           2.81              5547000  2021  \n",
       "1114           2.07              4562000  2021  \n",
       "\n",
       "[1115 rows x 8 columns]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "938d6c6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv('US_honey_dataset_update.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "701d7516",
   "metadata": {},
   "source": [
    "# THANK YOU"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}