pytrends_multiple_keywords.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Library & Connect to Google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install pytrends\n",
    "import pandas as pd\n",
    "from pytrends.request import TrendReq\n",
    "\n",
    "pytrends = TrendReq(hl='en-US', tz=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Keyword List"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "directory = \"/Users/rfdanti/Documents/Online Course/GoogleTrends/\"\n",
    "filename = \"list_keywords_food.csv\"\n",
    "kw_file = pd.read_csv(directory+filename)\n",
    "kw_list = kw_file[\"Keywords\"].values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keywords</th>\n",
       "      <th>Type</th>\n",
       "      <th>Origin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burger</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Grilled Cheese</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hot dog</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mac and Cheese</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cheesecake</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Keywords             Type    Origin\n",
       "0          Burger            Mains  American\n",
       "1  Grilled Cheese            Mains  American\n",
       "2         Hot dog            Mains  American\n",
       "3  Mac and Cheese            Mains  American\n",
       "4      Cheesecake  Desserts/Drinks  American"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kw_file.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building Payload & Collecting Data of Keywords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gtrends_overtime(full_list, key_ref, save_name=\"\", directory=\"\", category=0, time='all', loc=''):\n",
    "    #iterate every 4 item in list plus a keyword reference as the relative comparison\n",
    "    i = 0\n",
    "    while i < len(kw_list):\n",
    "        l = kw_list[i:(i+4)]\n",
    "        l.append(key_ref)\n",
    "        pytrends.build_payload(l, cat=category, timeframe=time, geo=loc, gprop='')\n",
    "        df_time = pytrends.interest_over_time()\n",
    "        df_time.reset_index(inplace=True)\n",
    "        df_time_name = \"gtrends_overtime\"+str(save_name)+str((i+4)//4)+\".csv\"\n",
    "        df_time.to_csv(directory+df_time_name, index = False)\n",
    "        i += 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_overtime(kw_list, 'Noodles', \"_worldwide_\", directory,\n",
    "                 category=71, time='all', loc='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_overtime(kw_list, 'Noodles', \"_US_\", directory,\n",
    "                 category=71, time='all', loc='US')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_overtime(kw_list, 'Noodles', \"_ID_\", directory,\n",
    "                 category=71, time='all', loc='ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gtrends_region(kw_list, key_ref, save_name=\"\", directory=\"\",\n",
    "                   category=0, time='all', loc='', res='COUNTRY'):\n",
    "    #iterate every 4 item in list plus a keyword reference as the relative comparison\n",
    "    i = 0\n",
    "    while i < len(kw_list):\n",
    "        l = kw_list[i:(i+4)]\n",
    "        l.append(key_ref)\n",
    "        pytrends.build_payload(l, cat=category, timeframe=time, geo=loc, gprop='')\n",
    "        # resolution can be set to 'REGION' when loc is set to specific country, e.g. 'US'\n",
    "        df_region = pytrends.interest_by_region(resolution=res, inc_low_vol=True, inc_geo_code=False)\n",
    "        df_region.reset_index(inplace=True)\n",
    "        df_region_name = \"gtrends_region\"+str(save_name)+str((i+4)//4)+\".csv\"\n",
    "        df_region.to_csv(df_region_name, index = False)\n",
    "        i += 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_region(kw_list, 'Noodles', \"_worldwide_\", directory,\n",
    "               category=71, time='all', loc='', res='COUNTRY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_region(kw_list, 'Noodles', \"_US_\", directory,\n",
    "               category=71, time='all', loc='US', res='REGION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "gtrends_region(kw_list, 'Noodles', \"_ID_\", directory,\n",
    "               category=71, time='all', loc='ID', res='REGION')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Integration, Normalization & Cleaning Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def combine_wbase(directory, base_name, n_file, filename):\n",
    "    df1 = pd.read_csv(directory+base_name+str(1)+\".csv\")\n",
    "    for i in range(n_file-1):\n",
    "        df2 = pd.read_csv(directory+base_name+str(i+2)+\".csv\")\n",
    "        df1 = pd.concat([df1, df2], axis=1, sort=False)\n",
    "    df_name = filename\n",
    "    # Saving the merged file or you can simply return the dataframe\n",
    "    df1.to_csv(df_name, index = False)\n",
    "    #return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>isPartial</th>\n",
       "      <th>date.1</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>...</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>isPartial.23</th>\n",
       "      <th>date.24</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "      <th>isPartial.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>25</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>58</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>78</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>78</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>80</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>99</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>19</td>\n",
       "      <td>100</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>97</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>100</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>18</td>\n",
       "      <td>94</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>94</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>26</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>16</td>\n",
       "      <td>85</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>90</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>True</td>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>70</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>15</td>\n",
       "      <td>83</td>\n",
       "      <td>True</td>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>83</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>199 rows × 175 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0    2004-01-01      15               0        3               0        5   \n",
       "1    2004-02-01      14               0        3               0        5   \n",
       "2    2004-03-01      16               0        3               0        5   \n",
       "3    2004-04-01      18               0        3               0        4   \n",
       "4    2004-05-01      17               0        3               0        5   \n",
       "..          ...     ...             ...      ...             ...      ...   \n",
       "194  2020-03-01      73               2        5              10       24   \n",
       "195  2020-04-01      80               3        7              13       30   \n",
       "196  2020-05-01      97               3        7              11       28   \n",
       "197  2020-06-01      90               2        7              10       26   \n",
       "198  2020-07-01      90               4        9              10       25   \n",
       "\n",
       "     isPartial      date.1  Cheesecake  Chow Mein  ...  Pad Thai  Noodles.23  \\\n",
       "0        False  2004-01-01          26          2  ...         4          17   \n",
       "1        False  2004-02-01          29          1  ...         4          16   \n",
       "2        False  2004-03-01          25          2  ...         4          16   \n",
       "3        False  2004-04-01          30          1  ...         4          13   \n",
       "4        False  2004-05-01          27          1  ...         4          15   \n",
       "..         ...         ...         ...        ...  ...       ...         ...   \n",
       "194      False  2020-03-01          58          4  ...        13          78   \n",
       "195      False  2020-04-01          99          6  ...        19         100   \n",
       "196      False  2020-05-01         100          6  ...        18          94   \n",
       "197      False  2020-06-01          80          6  ...        16          85   \n",
       "198       True  2020-07-01          70          5  ...        15          83   \n",
       "\n",
       "     isPartial.23     date.24 Tom Yum  Khao Pad  Thai Curry  \\\n",
       "0           False  2004-01-01       2         0           6   \n",
       "1           False  2004-02-01       1         0           6   \n",
       "2           False  2004-03-01       0         0           5   \n",
       "3           False  2004-04-01       0         0           4   \n",
       "4           False  2004-05-01       1         0           3   \n",
       "..            ...         ...     ...       ...         ...   \n",
       "194         False  2020-03-01       3         0          12   \n",
       "195         False  2020-04-01       4         0          17   \n",
       "196         False  2020-05-01       4         0          16   \n",
       "197         False  2020-06-01       3         0          14   \n",
       "198          True  2020-07-01       3         0          13   \n",
       "\n",
       "     Mango Sticky Rice  Noodles.24  isPartial.24  \n",
       "0                    0          17         False  \n",
       "1                    1          16         False  \n",
       "2                    0          16         False  \n",
       "3                    1          13         False  \n",
       "4                    0          15         False  \n",
       "..                 ...         ...           ...  \n",
       "194                  1          78         False  \n",
       "195                  2         100         False  \n",
       "196                  2          94         False  \n",
       "197                  1          85         False  \n",
       "198                  1          83          True  \n",
       "\n",
       "[199 rows x 175 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/\",\n",
    "              \"gtrends_overtime_worldwide_\", 25, \"gtrends_overtime_worlwide_merged.csv\")\n",
    "combined = pd.read_csv(\"gtrends_overtime_worlwide_merged.csv\")\n",
    "combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_US = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/\",\n",
    "              \"gtrends_overtime_US_\", 25, \"gtrends_overtime_US_merged.csv\")\n",
    "combined_US = pd.read_csv(\"gtrends_overtime_US_merged.csv\")\n",
    "\n",
    "combined_ID = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/\",\n",
    "              \"gtrends_overtime_ID_\", 25, \"gtrends_overtime_ID_merged.csv\")\n",
    "combined_ID = pd.read_csv(\"gtrends_overtime_ID_merged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geoName</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>geoName.1</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>...</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>geoName.24</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>58</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>Albania</td>\n",
       "      <td>79</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>Albania</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>American Samoa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>American Samoa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>57</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>34</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>28</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 150 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            geoName  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0       Afghanistan      58               0       12               0       30   \n",
       "1           Albania      56               0       10               6       28   \n",
       "2           Algeria      70               0       18               2       10   \n",
       "3    American Samoa       0               0        0               0        0   \n",
       "4           Andorra      91               0        0               0        9   \n",
       "..              ...     ...             ...      ...             ...      ...   \n",
       "245  Western Sahara       0               0        0               0        0   \n",
       "246           Yemen      70               0        0               0       30   \n",
       "247          Zambia      43               0        9              14       34   \n",
       "248        Zimbabwe      54               0        6              12       28   \n",
       "249   Åland Islands     100               0        0               0        0   \n",
       "\n",
       "          geoName.1  Cheesecake  Chow Mein  Dimsum  ...  Paella  Leche Frita  \\\n",
       "0       Afghanistan          41          0       0  ...       0            0   \n",
       "1           Albania          79          0       0  ...      28            0   \n",
       "2           Algeria          89          0       0  ...      87            0   \n",
       "3    American Samoa           0          0       0  ...       0            0   \n",
       "4           Andorra          76          0       0  ...      32            4   \n",
       "..              ...         ...        ...     ...  ...     ...          ...   \n",
       "245  Western Sahara           0          0       0  ...       0            0   \n",
       "246           Yemen          57          0       0  ...       0            0   \n",
       "247          Zambia          51          0       0  ...       0            0   \n",
       "248        Zimbabwe          55          0       0  ...       0            0   \n",
       "249   Åland Islands         100          0       0  ...     100            0   \n",
       "\n",
       "    Pad Thai  Noodles.23      geoName.24  Tom Yum  Khao Pad  Thai Curry  \\\n",
       "0          0         100     Afghanistan        0         0           0   \n",
       "1          0          65         Albania        0         0           0   \n",
       "2          0          11         Algeria        0         0           0   \n",
       "3          0           0  American Samoa        0         0           0   \n",
       "4          0           5         Andorra        0         0           0   \n",
       "..       ...         ...             ...      ...       ...         ...   \n",
       "245        0           0  Western Sahara        0         0           0   \n",
       "246        0         100           Yemen        0         0           0   \n",
       "247        0         100          Zambia        0         0           0   \n",
       "248        0         100        Zimbabwe        0         0           0   \n",
       "249        0           0   Åland Islands        0         0           0   \n",
       "\n",
       "    Mango Sticky Rice  Noodles.24  \n",
       "0                   0         100  \n",
       "1                   0         100  \n",
       "2                   0         100  \n",
       "3                   0           0  \n",
       "4                   0         100  \n",
       "..                ...         ...  \n",
       "245                 0           0  \n",
       "246                 0         100  \n",
       "247                 0         100  \n",
       "248                 0         100  \n",
       "249                 0           0  \n",
       "\n",
       "[250 rows x 150 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_geo = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/\",\n",
    "              \"gtrends_region_worldwide_\", 25, \"gtrends_region_worlwide_merged.csv\")\n",
    "combined_geo = pd.read_csv(\"gtrends_region_worlwide_merged.csv\")\n",
    "combined_geo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>geoName</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>geoName.1</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>...</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>geoName.24</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>62</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>61</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>88</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>52</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>49</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>57</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>61</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>81</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>62</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>55</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>83</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>64</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "      <td>California</td>\n",
       "      <td>60</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>76</td>\n",
       "      <td>California</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 150 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      geoName  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0     Alabama      62               2        6              12       18   \n",
       "1      Alaska      52               3        6              14       25   \n",
       "2     Arizona      64               2        6              10       18   \n",
       "3    Arkansas      62               3        5              10       20   \n",
       "4  California      64               3        5               9       19   \n",
       "\n",
       "    geoName.1  Cheesecake  Chow Mein  Dimsum  ...  Paella  Leche Frita  \\\n",
       "0     Alabama          61          3       0  ...       5            0   \n",
       "1      Alaska          49          5       0  ...       4            0   \n",
       "2     Arizona          61          4       0  ...       6            0   \n",
       "3    Arkansas          55          3       0  ...       4            0   \n",
       "4  California          60          5       0  ...       9            0   \n",
       "\n",
       "  Pad Thai  Noodles.23  geoName.24  Tom Yum  Khao Pad  Thai Curry  \\\n",
       "0        6          88     Alabama        1         0           4   \n",
       "1       39          57      Alaska        0         0          15   \n",
       "2       10          81     Arizona        4         0           8   \n",
       "3       11          83    Arkansas        1         0           7   \n",
       "4       12          76  California        3         0          11   \n",
       "\n",
       "  Mango Sticky Rice  Noodles.24  \n",
       "0                 0          95  \n",
       "1                 0          85  \n",
       "2                 0          88  \n",
       "3                 0          92  \n",
       "4                 1          85  \n",
       "\n",
       "[5 rows x 150 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_geo_US = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/Data/\",\n",
    "              \"gtrends_region_US_\", 25, \"gtrends_region_US_merged.csv\")\n",
    "combined_geo_US = pd.read_csv(\"gtrends_region_US_merged.csv\")\n",
    "combined_geo_US.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>geoName</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>geoName.1</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>...</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>geoName.24</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aceh</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Aceh</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>96</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Aceh</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bali</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>Bali</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>...</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>61</td>\n",
       "      <td>Bali</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bangka Belitung Islands</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Bangka Belitung Islands</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Bangka Belitung Islands</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Banten</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>Banten</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>...</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>70</td>\n",
       "      <td>Banten</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bengkulu</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Bengkulu</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>86</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Bengkulu</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 150 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   geoName  Burger  Grilled Cheese  Hot dog  Mac and Cheese  \\\n",
       "0                     Aceh     100               0        0               0   \n",
       "1                     Bali      78               0        5               2   \n",
       "2  Bangka Belitung Islands     100               0        0               0   \n",
       "3                   Banten      88               0        2               4   \n",
       "4                 Bengkulu     100               0        0               0   \n",
       "\n",
       "   Noodles                geoName.1  Cheesecake  Chow Mein  Dimsum  ...  \\\n",
       "0        0                     Aceh           4          0      96  ...   \n",
       "1       15                     Bali          26          0      45  ...   \n",
       "2        0  Bangka Belitung Islands          31          0      69  ...   \n",
       "3        6                   Banten          21          0      73  ...   \n",
       "4        0                 Bengkulu          14          0      86  ...   \n",
       "\n",
       "   Paella  Leche Frita Pad Thai  Noodles.23               geoName.24  Tom Yum  \\\n",
       "0       0            0        0           0                     Aceh        0   \n",
       "1      11            0       21          61                     Bali       22   \n",
       "2       0            0        0           0  Bangka Belitung Islands        0   \n",
       "3      11            0       16          70                   Banten       29   \n",
       "4       0            0        0           0                 Bengkulu        0   \n",
       "\n",
       "   Khao Pad  Thai Curry Mango Sticky Rice  Noodles.24  \n",
       "0         0           0                 0           0  \n",
       "1         0           0                10          68  \n",
       "2         0           0                 0           0  \n",
       "3         0           0                25          46  \n",
       "4         0           0                 0           0  \n",
       "\n",
       "[5 rows x 150 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_geo_ID = combine_wbase(\"/Users/rfdanti/Documents/Online Course/GoogleTrends/Data/\",\n",
    "              \"gtrends_region_ID_\", 25, \"gtrends_region_ID_merged.csv\")\n",
    "combined_geo_ID = pd.read_csv(\"gtrends_region_ID_merged.csv\")\n",
    "combined_geo_ID.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tidy up IsPartial column for data overtime\n",
    "def partial(df, n_file):\n",
    "    for i in range(n_file-1):\n",
    "        df = df.drop(columns=\"isPartial.\"+str(i+1)+\"\")\n",
    "    if df.isPartial.tail(1).bool() == True:\n",
    "        df = df.drop(df.isPartial.tail(1).index, axis=0)\n",
    "    df = df.drop(columns=\"isPartial\")\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>date.1</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>...</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>date.24</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>25</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>84</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>59</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>58</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>78</td>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>80</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>99</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>100</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>97</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>100</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>94</td>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>26</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>85</td>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198 rows × 150 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0    2004-01-01      15               0        3               0        5   \n",
       "1    2004-02-01      14               0        3               0        5   \n",
       "2    2004-03-01      16               0        3               0        5   \n",
       "3    2004-04-01      18               0        3               0        4   \n",
       "4    2004-05-01      17               0        3               0        5   \n",
       "..          ...     ...             ...      ...             ...      ...   \n",
       "193  2020-02-01      84               2        6               9       24   \n",
       "194  2020-03-01      73               2        5              10       24   \n",
       "195  2020-04-01      80               3        7              13       30   \n",
       "196  2020-05-01      97               3        7              11       28   \n",
       "197  2020-06-01      90               2        7              10       26   \n",
       "\n",
       "         date.1  Cheesecake  Chow Mein  Dimsum  ...  Paella  Leche Frita  \\\n",
       "0    2004-01-01          26          2       0  ...      12            1   \n",
       "1    2004-02-01          29          1       0  ...      13            1   \n",
       "2    2004-03-01          25          2       1  ...      12            1   \n",
       "3    2004-04-01          30          1       1  ...      10            1   \n",
       "4    2004-05-01          27          1       0  ...      13            1   \n",
       "..          ...         ...        ...     ...  ...     ...          ...   \n",
       "193  2020-02-01          59          5       2  ...      19            1   \n",
       "194  2020-03-01          58          4       2  ...      17            1   \n",
       "195  2020-04-01          99          6       5  ...      28            5   \n",
       "196  2020-05-01         100          6       5  ...      29            1   \n",
       "197  2020-06-01          80          6       4  ...      27            0   \n",
       "\n",
       "    Pad Thai  Noodles.23     date.24  Tom Yum  Khao Pad  Thai Curry  \\\n",
       "0          4          17  2004-01-01        2         0           6   \n",
       "1          4          16  2004-02-01        1         0           6   \n",
       "2          4          16  2004-03-01        0         0           5   \n",
       "3          4          13  2004-04-01        0         0           4   \n",
       "4          4          15  2004-05-01        1         0           3   \n",
       "..       ...         ...         ...      ...       ...         ...   \n",
       "193       14          78  2020-02-01        3         0          12   \n",
       "194       13          78  2020-03-01        3         0          12   \n",
       "195       19         100  2020-04-01        4         0          17   \n",
       "196       18          94  2020-05-01        4         0          16   \n",
       "197       16          85  2020-06-01        3         0          14   \n",
       "\n",
       "    Mango Sticky Rice  Noodles.24  \n",
       "0                   0          17  \n",
       "1                   1          16  \n",
       "2                   0          16  \n",
       "3                   1          13  \n",
       "4                   0          15  \n",
       "..                ...         ...  \n",
       "193                 1          78  \n",
       "194                 1          78  \n",
       "195                 2         100  \n",
       "196                 2          94  \n",
       "197                 1          85  \n",
       "\n",
       "[198 rows x 150 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined = partial(combined,25)\n",
    "combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_US = partial(combined_US,25)\n",
    "combined_ID = partial(combined_ID,25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "normalisation formula => \n",
    "new_value[column][n] = old_value[column][n] * (df[key_ref][n]/df[key_ref+\".\"+str(i)+\"\"][n])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "def normalise(df, n_file, key_ref, col='date'):\n",
    "    li = []\n",
    "    # Checking the relative popularity between comparisons\n",
    "    for i in range(n_file-1):    \n",
    "        df = df.drop(columns=col+\".\"+str(i+1)+\"\")\n",
    "        # Appending the list if relative popularity of the keyword reference is different\n",
    "        if df[key_ref+\".\"+str(i+1)+\"\"][0] == df[key_ref][0]:\n",
    "            pass\n",
    "        else:\n",
    "            li.append(i+1)\n",
    "    \n",
    "    # Normalizing relative popularity when the relative popularity of the keyword reference is different         \n",
    "    for l in li:\n",
    "        k = df.columns.get_loc(key_ref+\".\"+str(l)+\"\")\n",
    "        for n in range(len(df.index)):\n",
    "            # Computing relative popularity by normalizing according to the reference\n",
    "            if df.iloc[n,(k)] > 0:\n",
    "                for m in range(5):\n",
    "                    df.iloc[n,(k-4+m)] = (df.iloc[n,(k-4+m)] * (df[key_ref][n]/df.iloc[n,(k)]))\n",
    "            else:\n",
    "                for m in range(5):\n",
    "                    df.iloc[n,(k-4+m)] = (df.iloc[n,(k-4+m)] * (df[key_ref][n]/0.01))\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>Vermicelli roll</th>\n",
       "      <th>...</th>\n",
       "      <th>Jamon</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.764706</td>\n",
       "      <td>3.529412</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>1.176471</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.764706</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>16.111111</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.562500</td>\n",
       "      <td>4.062500</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.875000</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>13.888889</td>\n",
       "      <td>1.111111</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>3.750000</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.562500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>3.076923</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>1.230769</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.230769</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>84</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "      <td>31.466667</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>1.066667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.615385</td>\n",
       "      <td>5.846154</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>4.307692</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.692308</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>30.933333</td>\n",
       "      <td>2.133333</td>\n",
       "      <td>1.066667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.923077</td>\n",
       "      <td>5.230769</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.692308</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>80</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>30</td>\n",
       "      <td>52.105263</td>\n",
       "      <td>3.157895</td>\n",
       "      <td>2.631579</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.900000</td>\n",
       "      <td>8.400000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>5.700000</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1.200000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.100000</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>97</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>28</td>\n",
       "      <td>52.830189</td>\n",
       "      <td>3.169811</td>\n",
       "      <td>2.641509</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.659574</td>\n",
       "      <td>8.638298</td>\n",
       "      <td>0.297872</td>\n",
       "      <td>5.361702</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1.191489</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.765957</td>\n",
       "      <td>0.595745</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>90</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>26</td>\n",
       "      <td>43.333333</td>\n",
       "      <td>3.250000</td>\n",
       "      <td>2.166667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.894118</td>\n",
       "      <td>8.258824</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.894118</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.917647</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.282353</td>\n",
       "      <td>0.305882</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>198 rows × 126 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0    2004-01-01      15               0        3               0        5   \n",
       "1    2004-02-01      14               0        3               0        5   \n",
       "2    2004-03-01      16               0        3               0        5   \n",
       "3    2004-04-01      18               0        3               0        4   \n",
       "4    2004-05-01      17               0        3               0        5   \n",
       "..          ...     ...             ...      ...             ...      ...   \n",
       "193  2020-02-01      84               2        6               9       24   \n",
       "194  2020-03-01      73               2        5              10       24   \n",
       "195  2020-04-01      80               3        7              13       30   \n",
       "196  2020-05-01      97               3        7              11       28   \n",
       "197  2020-06-01      90               2        7              10       26   \n",
       "\n",
       "     Cheesecake  Chow Mein    Dimsum  Vermicelli roll  ...     Jamon  \\\n",
       "0     13.000000   1.000000  0.000000              0.0  ...  1.764706   \n",
       "1     16.111111   0.555556  0.000000              0.0  ...  1.562500   \n",
       "2     13.888889   1.111111  0.555556              0.0  ...  1.250000   \n",
       "3     15.000000   0.500000  0.500000              0.0  ...  0.923077   \n",
       "4     15.000000   0.555556  0.000000              0.0  ...  1.333333   \n",
       "..          ...        ...       ...              ...  ...       ...   \n",
       "193   31.466667   2.666667  1.066667              0.0  ...  4.615385   \n",
       "194   30.933333   2.133333  1.066667              0.0  ...  4.923077   \n",
       "195   52.105263   3.157895  2.631579              0.0  ...  6.900000   \n",
       "196   52.830189   3.169811  2.641509              0.0  ...  5.659574   \n",
       "197   43.333333   3.250000  2.166667              0.0  ...  4.894118   \n",
       "\n",
       "       Paella  Leche Frita  Pad Thai  Noodles.23   Tom Yum  Khao Pad  \\\n",
       "0    3.529412     0.294118  1.176471         5.0  0.588235       0.0   \n",
       "1    4.062500     0.312500  1.250000         5.0  0.312500       0.0   \n",
       "2    3.750000     0.312500  1.250000         5.0  0.000000       0.0   \n",
       "3    3.076923     0.307692  1.230769         4.0  0.000000       0.0   \n",
       "4    4.333333     0.333333  1.333333         5.0  0.333333       0.0   \n",
       "..        ...          ...       ...         ...       ...       ...   \n",
       "193  5.846154     0.307692  4.307692        24.0  0.923077       0.0   \n",
       "194  5.230769     0.307692  4.000000        24.0  0.923077       0.0   \n",
       "195  8.400000     1.500000  5.700000        30.0  1.200000       0.0   \n",
       "196  8.638298     0.297872  5.361702        28.0  1.191489       0.0   \n",
       "197  8.258824     0.000000  4.894118        26.0  0.917647       0.0   \n",
       "\n",
       "     Thai Curry  Mango Sticky Rice  Noodles.24  \n",
       "0      1.764706           0.000000         5.0  \n",
       "1      1.875000           0.312500         5.0  \n",
       "2      1.562500           0.000000         5.0  \n",
       "3      1.230769           0.307692         4.0  \n",
       "4      1.000000           0.000000         5.0  \n",
       "..          ...                ...         ...  \n",
       "193    3.692308           0.307692        24.0  \n",
       "194    3.692308           0.307692        24.0  \n",
       "195    5.100000           0.600000        30.0  \n",
       "196    4.765957           0.595745        28.0  \n",
       "197    4.282353           0.305882        26.0  \n",
       "\n",
       "[198 rows x 126 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normalised = normalise(combined, n_file=25, key_ref=\"Noodles\", col='date')\n",
    "normalised"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "normalised_US = normalise(combined_US, n_file=25, key_ref=\"Noodles\", col='date')\n",
    "normalised_ID = normalise(combined_ID, n_file=25, key_ref=\"Noodles\", col='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>geoName</th>\n",
       "      <th>Burger</th>\n",
       "      <th>Grilled Cheese</th>\n",
       "      <th>Hot dog</th>\n",
       "      <th>Mac and Cheese</th>\n",
       "      <th>Noodles</th>\n",
       "      <th>Cheesecake</th>\n",
       "      <th>Chow Mein</th>\n",
       "      <th>Dimsum</th>\n",
       "      <th>Vermicelli roll</th>\n",
       "      <th>...</th>\n",
       "      <th>Jamon</th>\n",
       "      <th>Paella</th>\n",
       "      <th>Leche Frita</th>\n",
       "      <th>Pad Thai</th>\n",
       "      <th>Noodles.23</th>\n",
       "      <th>Tom Yum</th>\n",
       "      <th>Khao Pad</th>\n",
       "      <th>Thai Curry</th>\n",
       "      <th>Mango Sticky Rice</th>\n",
       "      <th>Noodles.24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>58</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>20.847458</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>105.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.015385</td>\n",
       "      <td>12.061538</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>80.909091</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.818182</td>\n",
       "      <td>79.090909</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>28.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>106.200000</td>\n",
       "      <td>57.600000</td>\n",
       "      <td>7.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>39.767442</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>34</td>\n",
       "      <td>35.387755</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>28</td>\n",
       "      <td>34.222222</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 126 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            geoName  Burger  Grilled Cheese  Hot dog  Mac and Cheese  Noodles  \\\n",
       "0       Afghanistan      58               0       12               0       30   \n",
       "1           Albania      56               0       10               6       28   \n",
       "2           Algeria      70               0       18               2       10   \n",
       "3    American Samoa       0               0        0               0        0   \n",
       "4           Andorra      91               0        0               0        9   \n",
       "..              ...     ...             ...      ...             ...      ...   \n",
       "245  Western Sahara       0               0        0               0        0   \n",
       "246           Yemen      70               0        0               0       30   \n",
       "247          Zambia      43               0        9              14       34   \n",
       "248        Zimbabwe      54               0        6              12       28   \n",
       "249   Åland Islands     100               0        0               0        0   \n",
       "\n",
       "     Cheesecake  Chow Mein  Dimsum  Vermicelli roll  ...       Jamon  \\\n",
       "0     20.847458        0.0     0.0              0.0  ...    0.000000   \n",
       "1    105.333333        0.0     0.0              0.0  ...    3.015385   \n",
       "2     80.909091        0.0     0.0              0.0  ...    1.818182   \n",
       "3      0.000000        0.0     0.0              0.0  ...    0.000000   \n",
       "4     28.500000        0.0     0.0              0.0  ...  106.200000   \n",
       "..          ...        ...     ...              ...  ...         ...   \n",
       "245    0.000000        0.0     0.0              0.0  ...    0.000000   \n",
       "246   39.767442        0.0     0.0              0.0  ...    0.000000   \n",
       "247   35.387755        0.0     0.0              0.0  ...    0.000000   \n",
       "248   34.222222        0.0     0.0              0.0  ...    0.000000   \n",
       "249    0.000000        0.0     0.0              0.0  ...    0.000000   \n",
       "\n",
       "        Paella  Leche Frita  Pad Thai  Noodles.23  Tom Yum  Khao Pad  \\\n",
       "0     0.000000          0.0       0.0        30.0      0.0       0.0   \n",
       "1    12.061538          0.0       0.0        28.0      0.0       0.0   \n",
       "2    79.090909          0.0       0.0        10.0      0.0       0.0   \n",
       "3     0.000000          0.0       0.0         0.0      0.0       0.0   \n",
       "4    57.600000          7.2       0.0         9.0      0.0       0.0   \n",
       "..         ...          ...       ...         ...      ...       ...   \n",
       "245   0.000000          0.0       0.0         0.0      0.0       0.0   \n",
       "246   0.000000          0.0       0.0        30.0      0.0       0.0   \n",
       "247   0.000000          0.0       0.0        34.0      0.0       0.0   \n",
       "248   0.000000          0.0       0.0        28.0      0.0       0.0   \n",
       "249   0.000000          0.0       0.0         0.0      0.0       0.0   \n",
       "\n",
       "     Thai Curry  Mango Sticky Rice  Noodles.24  \n",
       "0           0.0                0.0        30.0  \n",
       "1           0.0                0.0        28.0  \n",
       "2           0.0                0.0        10.0  \n",
       "3           0.0                0.0         0.0  \n",
       "4           0.0                0.0         9.0  \n",
       "..          ...                ...         ...  \n",
       "245         0.0                0.0         0.0  \n",
       "246         0.0                0.0        30.0  \n",
       "247         0.0                0.0        34.0  \n",
       "248         0.0                0.0        28.0  \n",
       "249         0.0                0.0         0.0  \n",
       "\n",
       "[250 rows x 126 columns]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normalised_geo = normalise(combined_geo, n_file=25, key_ref=\"Noodles\", col='geoName')\n",
    "normalised_geo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "normalised_geo_US = normalise(combined_geo_US, n_file=25, key_ref=\"Noodles\", col='geoName')\n",
    "normalised_geo_ID = normalise(combined_geo_ID, n_file=25, key_ref=\"Noodles\", col='geoName')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tidy(df, n_file, key_ref, kw_file, col='date'):\n",
    "    for i in range(n_file-1):    \n",
    "        df = df.drop(columns=key_ref+\".\"+str(i+1)+\"\")\n",
    "    df=pd.melt(df,id_vars=[col],var_name='Keywords', value_name='RelativePopularity')\n",
    "    df = df.merge(kw_file, on=\"Keywords\")\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>RelativePopularity</th>\n",
       "      <th>Type</th>\n",
       "      <th>Origin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19795</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19796</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19797</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19798</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.595745</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19799</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.305882</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19800 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date           Keywords  RelativePopularity             Type  \\\n",
       "0      2004-01-01             Burger           15.000000            Mains   \n",
       "1      2004-02-01             Burger           14.000000            Mains   \n",
       "2      2004-03-01             Burger           16.000000            Mains   \n",
       "3      2004-04-01             Burger           18.000000            Mains   \n",
       "4      2004-05-01             Burger           17.000000            Mains   \n",
       "...           ...                ...                 ...              ...   \n",
       "19795  2020-02-01  Mango Sticky Rice            0.307692  Desserts/Drinks   \n",
       "19796  2020-03-01  Mango Sticky Rice            0.307692  Desserts/Drinks   \n",
       "19797  2020-04-01  Mango Sticky Rice            0.600000  Desserts/Drinks   \n",
       "19798  2020-05-01  Mango Sticky Rice            0.595745  Desserts/Drinks   \n",
       "19799  2020-06-01  Mango Sticky Rice            0.305882  Desserts/Drinks   \n",
       "\n",
       "         Origin  \n",
       "0      American  \n",
       "1      American  \n",
       "2      American  \n",
       "3      American  \n",
       "4      American  \n",
       "...         ...  \n",
       "19795      Thai  \n",
       "19796      Thai  \n",
       "19797      Thai  \n",
       "19798      Thai  \n",
       "19799      Thai  \n",
       "\n",
       "[19800 rows x 5 columns]"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overtime = tidy(normalised, 25, \"Noodles\", kw_file, col='date')\n",
    "overtime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "overtime_US = tidy(normalised_US, 25, \"Noodles\", kw_file, col='date')\n",
    "overtime_ID = tidy(normalised_ID, 25, \"Noodles\", kw_file, col='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "scrolled": true
   },
   "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>geoName</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>RelativePopularity</th>\n",
       "      <th>Type</th>\n",
       "      <th>Origin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Burger</td>\n",
       "      <td>58.0</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Burger</td>\n",
       "      <td>56.0</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>Burger</td>\n",
       "      <td>70.0</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>Burger</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Burger</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24995</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24996</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24997</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24998</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24999</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25000 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              geoName           Keywords  RelativePopularity             Type  \\\n",
       "0         Afghanistan             Burger                58.0            Mains   \n",
       "1             Albania             Burger                56.0            Mains   \n",
       "2             Algeria             Burger                70.0            Mains   \n",
       "3      American Samoa             Burger                 0.0            Mains   \n",
       "4             Andorra             Burger                91.0            Mains   \n",
       "...               ...                ...                 ...              ...   \n",
       "24995  Western Sahara  Mango Sticky Rice                 0.0  Desserts/Drinks   \n",
       "24996           Yemen  Mango Sticky Rice                 0.0  Desserts/Drinks   \n",
       "24997          Zambia  Mango Sticky Rice                 0.0  Desserts/Drinks   \n",
       "24998        Zimbabwe  Mango Sticky Rice                 0.0  Desserts/Drinks   \n",
       "24999   Åland Islands  Mango Sticky Rice                 0.0  Desserts/Drinks   \n",
       "\n",
       "         Origin  \n",
       "0      American  \n",
       "1      American  \n",
       "2      American  \n",
       "3      American  \n",
       "4      American  \n",
       "...         ...  \n",
       "24995      Thai  \n",
       "24996      Thai  \n",
       "24997      Thai  \n",
       "24998      Thai  \n",
       "24999      Thai  \n",
       "\n",
       "[25000 rows x 5 columns]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidied_geo = tidy(normalised_geo, 25, \"Noodles\", kw_file, col='geoName')\n",
    "tidied_geo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_US = tidy(normalised_geo_US, 25, \"Noodles\", kw_file, col='geoName')\n",
    "geo_ID = tidy(normalised_geo_ID, 25, \"Noodles\", kw_file, col='geoName')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>RelativePopularity</th>\n",
       "      <th>Type</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Loc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004-01-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>Worldwide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004-02-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>Worldwide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004-03-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>Worldwide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004-04-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>Worldwide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004-05-01</td>\n",
       "      <td>Burger</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>Worldwide</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19795</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19796</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19797</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19798</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.888889</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19799</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>1.777778</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59400 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date           Keywords  RelativePopularity             Type  \\\n",
       "0      2004-01-01             Burger           15.000000            Mains   \n",
       "1      2004-02-01             Burger           14.000000            Mains   \n",
       "2      2004-03-01             Burger           16.000000            Mains   \n",
       "3      2004-04-01             Burger           18.000000            Mains   \n",
       "4      2004-05-01             Burger           17.000000            Mains   \n",
       "...           ...                ...                 ...              ...   \n",
       "19795  2020-02-01  Mango Sticky Rice            0.875000  Desserts/Drinks   \n",
       "19796  2020-03-01  Mango Sticky Rice            0.857143  Desserts/Drinks   \n",
       "19797  2020-04-01  Mango Sticky Rice            0.875000  Desserts/Drinks   \n",
       "19798  2020-05-01  Mango Sticky Rice            0.888889  Desserts/Drinks   \n",
       "19799  2020-06-01  Mango Sticky Rice            1.777778  Desserts/Drinks   \n",
       "\n",
       "         Origin        Loc  \n",
       "0      American  Worldwide  \n",
       "1      American  Worldwide  \n",
       "2      American  Worldwide  \n",
       "3      American  Worldwide  \n",
       "4      American  Worldwide  \n",
       "...         ...        ...  \n",
       "19795      Thai         ID  \n",
       "19796      Thai         ID  \n",
       "19797      Thai         ID  \n",
       "19798      Thai         ID  \n",
       "19799      Thai         ID  \n",
       "\n",
       "[59400 rows x 6 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overtime[\"Loc\"] = \"Worldwide\"\n",
    "overtime_US[\"Loc\"] = \"US\"\n",
    "overtime_ID[\"Loc\"] = \"ID\"\n",
    "\n",
    "overtime_all = pd.concat([overtime, overtime_US], axis=0, sort=False)\n",
    "overtime_all = pd.concat([overtime_all, overtime_ID], axis=0, sort=False)\n",
    "\n",
    "overtime_all.to_csv(\"gtrends_food_overtime_all.csv\", index = False)\n",
    "overtime_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>geoName</th>\n",
       "      <th>geoSum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>430.135493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2175.964817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2475.080297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>1152.239261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Western Sahara</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>401.735841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>657.143670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>863.195132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>100.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>250 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            geoName       geoSum\n",
       "0       Afghanistan   430.135493\n",
       "1           Albania  2175.964817\n",
       "2           Algeria  2475.080297\n",
       "3    American Samoa     0.000000\n",
       "4           Andorra  1152.239261\n",
       "..              ...          ...\n",
       "245  Western Sahara     0.000000\n",
       "246           Yemen   401.735841\n",
       "247          Zambia   657.143670\n",
       "248        Zimbabwe   863.195132\n",
       "249   Åland Islands   100.000000\n",
       "\n",
       "[250 rows x 2 columns]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo = tidied_geo.fillna(0)\n",
    "geoSum = pd.DataFrame(geo.groupby(['geoName']).sum()).reset_index()\n",
    "geoSum.columns = ['geoName', 'geoSum']\n",
    "geoSum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "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>geoName</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>RelativePopularity</th>\n",
       "      <th>Type</th>\n",
       "      <th>Origin</th>\n",
       "      <th>geoSum</th>\n",
       "      <th>PercentageBreakdown</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Burger</td>\n",
       "      <td>58.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>430.135493</td>\n",
       "      <td>0.134841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Grilled Cheese</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>430.135493</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Hot dog</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>430.135493</td>\n",
       "      <td>0.027898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Mac and Cheese</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>American</td>\n",
       "      <td>430.135493</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Cheesecake</td>\n",
       "      <td>20.847458</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>American</td>\n",
       "      <td>430.135493</td>\n",
       "      <td>0.048467</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24995</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Pad Thai</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Soup/Noodles</td>\n",
       "      <td>Thai</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24996</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Tom Yum</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Soup/Noodles</td>\n",
       "      <td>Thai</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24997</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Khao Pad</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>Thai</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24998</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Thai Curry</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Mains</td>\n",
       "      <td>Thai</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24999</th>\n",
       "      <td>Åland Islands</td>\n",
       "      <td>Mango Sticky Rice</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Desserts/Drinks</td>\n",
       "      <td>Thai</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             geoName           Keywords  RelativePopularity             Type  \\\n",
       "0        Afghanistan             Burger           58.000000            Mains   \n",
       "1        Afghanistan     Grilled Cheese            0.000000            Mains   \n",
       "2        Afghanistan            Hot dog           12.000000            Mains   \n",
       "3        Afghanistan     Mac and Cheese            0.000000            Mains   \n",
       "4        Afghanistan         Cheesecake           20.847458  Desserts/Drinks   \n",
       "...              ...                ...                 ...              ...   \n",
       "24995  Åland Islands           Pad Thai            0.000000     Soup/Noodles   \n",
       "24996  Åland Islands            Tom Yum            0.000000     Soup/Noodles   \n",
       "24997  Åland Islands           Khao Pad            0.000000            Mains   \n",
       "24998  Åland Islands         Thai Curry            0.000000            Mains   \n",
       "24999  Åland Islands  Mango Sticky Rice            0.000000  Desserts/Drinks   \n",
       "\n",
       "         Origin      geoSum  PercentageBreakdown  \n",
       "0      American  430.135493             0.134841  \n",
       "1      American  430.135493             0.000000  \n",
       "2      American  430.135493             0.027898  \n",
       "3      American  430.135493             0.000000  \n",
       "4      American  430.135493             0.048467  \n",
       "...         ...         ...                  ...  \n",
       "24995      Thai  100.000000             0.000000  \n",
       "24996      Thai  100.000000             0.000000  \n",
       "24997      Thai  100.000000             0.000000  \n",
       "24998      Thai  100.000000             0.000000  \n",
       "24999      Thai  100.000000             0.000000  \n",
       "\n",
       "[25000 rows x 7 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo = pd.merge(geo, geoSum, on=['geoName'])\n",
    "geo['PercentageBreakdown'] = geo['RelativePopularity']/geo['geoSum']\n",
    "geo.to_csv(\"gtrends_food_geo.csv\", index = False)\n",
    "geo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_US = geo_US.fillna(0)\n",
    "geo_US.to_csv(\"gtrends_food_geo_US.csv\", index = False)\n",
    "\n",
    "geo_ID = geo_ID.fillna(0)\n",
    "geo_ID.to_csv(\"gtrends_food_geo_ID.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check out the DataViz in Tableau public: \n",
    "https://public.tableau.com/profile/ristanti.ramadanti#!/vizhome/100FoodAroundTheWorld/Dashboard1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}