grouping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master=pd.read_csv(\"final2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>isholnum</th>\n",
       "      <th>sku</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_cogs</th>\n",
       "      <th>sunrise</th>\n",
       "      <th>sunset</th>\n",
       "      <th>HeatIndexC</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>humidity</th>\n",
       "      <th>tempC</th>\n",
       "      <th>Avgtemp</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>3646</td>\n",
       "      <td>ART._HYGIENIC</td>\n",
       "      <td>BELLA HERBS 12 Z WERBENA</td>\n",
       "      <td>1</td>\n",
       "      <td>2.39</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>17048</td>\n",
       "      <td>ART._HYGIENIC</td>\n",
       "      <td>BELLA PODPASKI BLUE MAX</td>\n",
       "      <td>2</td>\n",
       "      <td>2.85</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>ART._HYGIENIC</td>\n",
       "      <td>BELLA PODPASKI NORMAL 20 B/SKRZYD</td>\n",
       "      <td>2</td>\n",
       "      <td>1.76</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>55</td>\n",
       "      <td>ART._HYGIENIC</td>\n",
       "      <td>BELLA PODPASKI NORMAL MAXI 10</td>\n",
       "      <td>4</td>\n",
       "      <td>2.14</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>54</td>\n",
       "      <td>ART._HYGIENIC</td>\n",
       "      <td>BELLA PODPASKI NOVA MAXI 10</td>\n",
       "      <td>1</td>\n",
       "      <td>2.66</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  isholnum    sku          group  \\\n",
       "0  2018-01-01         1   3646  ART._HYGIENIC   \n",
       "1  2018-01-01         1  17048  ART._HYGIENIC   \n",
       "2  2018-01-01         1     41  ART._HYGIENIC   \n",
       "3  2018-01-01         1     55  ART._HYGIENIC   \n",
       "4  2018-01-01         1     54  ART._HYGIENIC   \n",
       "\n",
       "                                name quantity  unit_cogs   sunrise    sunset  \\\n",
       "0           BELLA HERBS 12 Z WERBENA        1       2.39  08:52:00  16:31:00   \n",
       "1            BELLA PODPASKI BLUE MAX        2       2.85  08:52:00  16:31:00   \n",
       "2  BELLA PODPASKI NORMAL 20 B/SKRZYD        2       1.76  08:52:00  16:31:00   \n",
       "3      BELLA PODPASKI NORMAL MAXI 10        4       2.14  08:52:00  16:31:00   \n",
       "4        BELLA PODPASKI NOVA MAXI 10        1       2.66  08:52:00  16:31:00   \n",
       "\n",
       "  HeatIndexC  cloudcover  humidity  tempC  Avgtemp  Unnamed: 14  \n",
       "0          6          79        89      8      5.5          NaN  \n",
       "1          6          79        89      8      5.5          NaN  \n",
       "2          6          79        89      8      5.5          NaN  \n",
       "3          6          79        89      8      5.5          NaN  \n",
       "4          6          79        89      8      5.5          NaN  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df_master.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ART._HYGIENIC', 'ARTICLE_OF_HOUSEHOLD', 'BEER', 'BREAD',\n",
       "       'CANNED_PATE', 'CHEMISTRY', 'CHEWING_GUM_LOLIPOPS', 'CHIPS_FLAKES',\n",
       "       'CIGARETTES', 'COFFEE TEA', 'COOKIES_BULK', 'DAIRY_CHESSE',\n",
       "       'DISHES_FOR_CHILDREN', 'DRINK_JUICE', 'EGGS', 'FISH', 'GENERAL',\n",
       "       'GENERAL_FOOD', 'GROATS_RICE_PASTA', 'ICE_CREAMS_FROZEN',\n",
       "       'KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH', 'LUNCH DINING DISHES',\n",
       "       'MEAT', 'MILK', 'OCCASIONAL', 'OILS', 'PACKAGES', \"PET'S FOOD\",\n",
       "       'PHONES_ADJUSTMENTS', 'SOCKS_THIGHTS', 'SPICES', 'SWEETS',\n",
       "       'TABLETS', 'VEGETABLES', 'VODKA_ALCOHOL', 'WINE_ALCOHOL 18%'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"group\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SWEETS                                       2298\n",
       "DAIRY_CHESSE                                 2297\n",
       "CHEMISTRY                                    2152\n",
       "DRINK_JUICE                                  2044\n",
       "GENERAL_FOOD                                 1956\n",
       "ICE_CREAMS_FROZEN                            1334\n",
       "VEGETABLES                                    940\n",
       "GENERAL                                       905\n",
       "BEER                                          900\n",
       "KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       "CIGARETTES                                    850\n",
       "SPICES                                        775\n",
       "GROATS_RICE_PASTA                             635\n",
       "VODKA_ALCOHOL                                 627\n",
       "CHIPS_FLAKES                                  509\n",
       "COFFEE TEA                                    482\n",
       "BREAD                                         461\n",
       "MEAT                                          427\n",
       "FISH                                          373\n",
       "CANNED_PATE                                   361\n",
       "CHEWING_GUM_LOLIPOPS                          332\n",
       "WINE_ALCOHOL 18%                              280\n",
       "TABLETS                                       265\n",
       "COOKIES_BULK                                  214\n",
       "OCCASIONAL                                    204\n",
       "OILS                                          170\n",
       "SOCKS_THIGHTS                                 165\n",
       "ART._HYGIENIC                                 101\n",
       "PET'S FOOD                                     69\n",
       "DISHES_FOR_CHILDREN                            37\n",
       "LUNCH DINING DISHES                            36\n",
       "PHONES_ADJUSTMENTS                             35\n",
       "ARTICLE_OF_HOUSEHOLD                           24\n",
       "EGGS                                           24\n",
       "PACKAGES                                       20\n",
       "MILK                                            1\n",
       "Name: group, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"group\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"group\"].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_counts=[df[\"group\"].value_counts()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SWEETS                                       2298\n",
       " DAIRY_CHESSE                                 2297\n",
       " CHEMISTRY                                    2152\n",
       " DRINK_JUICE                                  2044\n",
       " GENERAL_FOOD                                 1956\n",
       " ALCOHOL                                      1807\n",
       " ICE_CREAMS_FROZEN                            1334\n",
       " POULTRY                                      1185\n",
       " VEGETABLES                                    940\n",
       " GENERAL                                       905\n",
       " KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       " CIGARETTES                                    850\n",
       " SPICES                                        775\n",
       " GROATS_RICE_PASTA                             635\n",
       " CHIPS_FLAKES                                  509\n",
       " COFFEE TEA                                    482\n",
       " BREAD                                         461\n",
       " CHEWING_GUM_LOLIPOPS                          332\n",
       " TABLETS                                       265\n",
       " COOKIES_BULK                                  214\n",
       " OCCASIONAL                                    204\n",
       " OILS                                          170\n",
       " SOCKS_THIGHTS                                 165\n",
       " ART._HYGIENIC                                 101\n",
       " PET'S FOOD                                     69\n",
       " DISHES_FOR_CHILDREN                            37\n",
       " LUNCH DINING DISHES                            36\n",
       " PHONES_ADJUSTMENTS                             35\n",
       " ARTICLE_OF_HOUSEHOLD                           24\n",
       " PACKAGES                                       20\n",
       " MILK                                            1\n",
       " Name: group, dtype: int64]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace(['WINE_ALCOHOL 18%', 'BEER','VODKA','VODKA_ALCOHOL'], ['ALCOHOL','ALCOHOL','ALCOHOL','ALCOHOL'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"group\"].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SWEETS                                       2298\n",
       "DAIRY_CHESSE                                 2297\n",
       "CHEMISTRY                                    2152\n",
       "DRINK_JUICE                                  2044\n",
       "GENERAL_FOOD                                 1956\n",
       "ALCOHOL                                      1807\n",
       "ICE_CREAMS_FROZEN                            1334\n",
       "VEGETABLES                                    940\n",
       "GENERAL                                       905\n",
       "KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       "CIGARETTES                                    850\n",
       "SPICES                                        775\n",
       "GROATS_RICE_PASTA                             635\n",
       "CHIPS_FLAKES                                  509\n",
       "COFFEE TEA                                    482\n",
       "BREAD                                         461\n",
       "MEAT                                          427\n",
       "FISH                                          373\n",
       "CANNED_PATE                                   361\n",
       "CHEWING_GUM_LOLIPOPS                          332\n",
       "TABLETS                                       265\n",
       "COOKIES_BULK                                  214\n",
       "OCCASIONAL                                    204\n",
       "OILS                                          170\n",
       "SOCKS_THIGHTS                                 165\n",
       "ART._HYGIENIC                                 101\n",
       "PET'S FOOD                                     69\n",
       "DISHES_FOR_CHILDREN                            37\n",
       "LUNCH DINING DISHES                            36\n",
       "PHONES_ADJUSTMENTS                             35\n",
       "ARTICLE_OF_HOUSEHOLD                           24\n",
       "EGGS                                           24\n",
       "PACKAGES                                       20\n",
       "MILK                                            1\n",
       "Name: group, dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"group\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace(['MEAT', 'FISH','CANNED_PATE','EGGS'], ['POULTRY','POULTRY','POULTRY','POULTRY'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"group\"].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SWEETS                                       2298\n",
       "DAIRY_CHESSE                                 2297\n",
       "CHEMISTRY                                    2152\n",
       "DRINK_JUICE                                  2044\n",
       "GENERAL_FOOD                                 1956\n",
       "ALCOHOL                                      1807\n",
       "ICE_CREAMS_FROZEN                            1334\n",
       "POULTRY                                      1185\n",
       "VEGETABLES                                    940\n",
       "GENERAL                                       905\n",
       "KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       "CIGARETTES                                    850\n",
       "SPICES                                        775\n",
       "GROATS_RICE_PASTA                             635\n",
       "CHIPS_FLAKES                                  509\n",
       "COFFEE TEA                                    482\n",
       "BREAD                                         461\n",
       "CHEWING_GUM_LOLIPOPS                          332\n",
       "TABLETS                                       265\n",
       "COOKIES_BULK                                  214\n",
       "OCCASIONAL                                    204\n",
       "OILS                                          170\n",
       "SOCKS_THIGHTS                                 165\n",
       "ART._HYGIENIC                                 101\n",
       "PET'S FOOD                                     69\n",
       "DISHES_FOR_CHILDREN                            37\n",
       "LUNCH DINING DISHES                            36\n",
       "PHONES_ADJUSTMENTS                             35\n",
       "ARTICLE_OF_HOUSEHOLD                           24\n",
       "PACKAGES                                       20\n",
       "MILK                                            1\n",
       "Name: group, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"group\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SWEETS                                       2298\n",
       " DAIRY_CHESSE                                 2297\n",
       " CHEMISTRY                                    2152\n",
       " DRINK_JUICE                                  2044\n",
       " GENERAL_FOOD                                 1956\n",
       " ALCOHOL                                      1807\n",
       " ICE_CREAMS_FROZEN                            1334\n",
       " POULTRY                                      1185\n",
       " VEGETABLES                                    940\n",
       " GENERAL                                       905\n",
       " KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       " CIGARETTES                                    850\n",
       " SPICES                                        775\n",
       " GROATS_RICE_PASTA                             635\n",
       " CHIPS_FLAKES                                  509\n",
       " COFFEE TEA                                    482\n",
       " BREAD                                         461\n",
       " CHEWING_GUM_LOLIPOPS                          332\n",
       " TABLETS                                       265\n",
       " COOKIES_BULK                                  214\n",
       " OCCASIONAL                                    204\n",
       " OILS                                          170\n",
       " SOCKS_THIGHTS                                 165\n",
       " ART._HYGIENIC                                 101\n",
       " PET'S FOOD                                     69\n",
       " DISHES_FOR_CHILDREN                            37\n",
       " LUNCH DINING DISHES                            36\n",
       " PHONES_ADJUSTMENTS                             35\n",
       " ARTICLE_OF_HOUSEHOLD                           24\n",
       " PACKAGES                                       20\n",
       " MILK                                            1\n",
       " Name: group, dtype: int64]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SWEETS                                       2298\n",
       "DAIRY_CHESSE                                 2297\n",
       "CHEMISTRY                                    2152\n",
       "DRINK_JUICE                                  2044\n",
       "GENERAL_FOOD                                 1956\n",
       "ALCOHOL                                      1807\n",
       "ICE_CREAMS_FROZEN                            1334\n",
       "POULTRY                                      1185\n",
       "VEGETABLES                                    940\n",
       "GENERAL                                       905\n",
       "KETCH_CONCETRATE_MUSTARD_MAJO_HORSERADISH     877\n",
       "CIGARETTES                                    850\n",
       "SPICES                                        775\n",
       "GENERAL_ITEMS                                 658\n",
       "GROATS_RICE_PASTA                             635\n",
       "CHIPS_FLAKES                                  509\n",
       "COFFEE TEA                                    482\n",
       "BREAD                                         461\n",
       "CHEWING_GUM_LOLIPOPS                          332\n",
       "TABLETS                                       265\n",
       "COOKIES_BULK                                  214\n",
       "OCCASIONAL                                    204\n",
       "Name: group, dtype: int64"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.replace([\"OILS\",\"SOCKS_THIGHTS\",\"ART._HYGIENIC\",\"PET'S FOOD\",\"DISHES_FOR_CHILDREN\",\"LUNCH DINING DISHES\",\"PHONES_ADJUSTMENTS\",\"ARTICLE_OF_HOUSEHOLD\",\"PACKAGES\",\"MILK\"],[\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\",\"GENERAL_ITEMS\"],inplace=True)\n",
    "(df[\"group\"].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Was trying to write a fucntion below to do it (grouping eveerything under 200 units to one) automatically, but then it didnt' work out so I did it manually above "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# values = df['group'].value_counts().keys().tolist()\n",
    "# counts = df['group'].value_counts().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SWEETS\n",
      "2298\n"
     ]
    }
   ],
   "source": [
    "# for i in values,counts:\n",
    "# #      if i[0]<200:\n",
    "#         print ((i[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #a function to replace all the groups which are less 200 in quantitiy to one group\n",
    "# def replace_groups(group_c,data):\n",
    "#     for group in group_c:\n",
    "#         if"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df=pd.read_csv(\"Final_Weather_Data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "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>isholnum</th>\n",
       "      <th>sku</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_cogs</th>\n",
       "      <th>sunrise</th>\n",
       "      <th>sunset</th>\n",
       "      <th>HeatIndexC</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>humidity</th>\n",
       "      <th>tempC</th>\n",
       "      <th>Avgtemp</th>\n",
       "      <th>Unnamed: 14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>3646</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA HERBS 12 Z WERBENA</td>\n",
       "      <td>1</td>\n",
       "      <td>2.39</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>17048</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI BLUE MAX</td>\n",
       "      <td>2</td>\n",
       "      <td>2.85</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL 20 B/SKRZYD</td>\n",
       "      <td>2</td>\n",
       "      <td>1.76</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>55</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL MAXI 10</td>\n",
       "      <td>4</td>\n",
       "      <td>2.14</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>54</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NOVA MAXI 10</td>\n",
       "      <td>1</td>\n",
       "      <td>2.66</td>\n",
       "      <td>08:52:00</td>\n",
       "      <td>16:31:00</td>\n",
       "      <td>6</td>\n",
       "      <td>79</td>\n",
       "      <td>89</td>\n",
       "      <td>8</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  isholnum    sku          group  \\\n",
       "0  2018-01-01         1   3646  GENERAL_ITEMS   \n",
       "1  2018-01-01         1  17048  GENERAL_ITEMS   \n",
       "2  2018-01-01         1     41  GENERAL_ITEMS   \n",
       "3  2018-01-01         1     55  GENERAL_ITEMS   \n",
       "4  2018-01-01         1     54  GENERAL_ITEMS   \n",
       "\n",
       "                                name quantity  unit_cogs   sunrise    sunset  \\\n",
       "0           BELLA HERBS 12 Z WERBENA        1       2.39  08:52:00  16:31:00   \n",
       "1            BELLA PODPASKI BLUE MAX        2       2.85  08:52:00  16:31:00   \n",
       "2  BELLA PODPASKI NORMAL 20 B/SKRZYD        2       1.76  08:52:00  16:31:00   \n",
       "3      BELLA PODPASKI NORMAL MAXI 10        4       2.14  08:52:00  16:31:00   \n",
       "4        BELLA PODPASKI NOVA MAXI 10        1       2.66  08:52:00  16:31:00   \n",
       "\n",
       "  HeatIndexC  cloudcover  humidity  tempC  Avgtemp  Unnamed: 14  \n",
       "0          6          79        89      8      5.5          NaN  \n",
       "1          6          79        89      8      5.5          NaN  \n",
       "2          6          79        89      8      5.5          NaN  \n",
       "3          6          79        89      8      5.5          NaN  \n",
       "4          6          79        89      8      5.5          NaN  "
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "#doing this because the original df contains the previous data we had which was absolutely wrong . So yeah.\n",
    "\n",
    "df_to_merge=(df[\"date\"],df[\"group\"],df[\"name\"],df[\"quantity\"],df[\"unit_cogs\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_merge=pd.DataFrame(list(df_to_merge))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>23170</th>\n",
       "      <th>23171</th>\n",
       "      <th>23172</th>\n",
       "      <th>23173</th>\n",
       "      <th>23174</th>\n",
       "      <th>23175</th>\n",
       "      <th>23176</th>\n",
       "      <th>23177</th>\n",
       "      <th>23178</th>\n",
       "      <th>23179</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>...</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>group</th>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>...</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>ALCOHOL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>BELLA HERBS 12 Z WERBENA</td>\n",
       "      <td>BELLA PODPASKI BLUE MAX</td>\n",
       "      <td>BELLA PODPASKI NORMAL 20 B/SKRZYD</td>\n",
       "      <td>BELLA PODPASKI NORMAL MAXI 10</td>\n",
       "      <td>BELLA PODPASKI NOVA MAXI 10</td>\n",
       "      <td>BELLA PODPASKI PERF.BLUE 10</td>\n",
       "      <td>EVA MYDLO W PLYNIE Z DOZ 500 MML</td>\n",
       "      <td>PLYN DO ZATLUSZCZONYCH POWIERZCHNI  750</td>\n",
       "      <td>PLYN KAMIEN I RDZA 750 ML</td>\n",
       "      <td>PODPASKI BELLA  NOVA 10 SZT</td>\n",
       "      <td>...</td>\n",
       "      <td>WINO RAFFAELLO 0_2 SLODKIE</td>\n",
       "      <td>WINO ROYAL 0_75 B/WYTRAWNE</td>\n",
       "      <td>WINO SOLARRA CZ/PÓS? 0_75 L</td>\n",
       "      <td>WINO SOPHIA 0_75 L B/S?ODKIE</td>\n",
       "      <td>WINO SPECIAL CHERRY 1 L</td>\n",
       "      <td>WINO TOTINO CHERRY VERMOUTH 1L</td>\n",
       "      <td>WINO WALDWEIN JEZYNA 0_75 L SLODKIE</td>\n",
       "      <td>WINO WALDWEIN MALINA 0_75 L SLODKIE</td>\n",
       "      <td>WODKA DRINK SOBIESKI 025ML CRANBERRY4.4%</td>\n",
       "      <td>WODKA DRINK SOBIESKI KAMIKAZE.250 ML</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quantity</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unit_cogs</th>\n",
       "      <td>2.39</td>\n",
       "      <td>2.85</td>\n",
       "      <td>1.76</td>\n",
       "      <td>2.14</td>\n",
       "      <td>2.66</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.71</td>\n",
       "      <td>5.49</td>\n",
       "      <td>5.49</td>\n",
       "      <td>2.05</td>\n",
       "      <td>...</td>\n",
       "      <td>3.25</td>\n",
       "      <td>10.13</td>\n",
       "      <td>5.99</td>\n",
       "      <td>6.01</td>\n",
       "      <td>6.97</td>\n",
       "      <td>9.8</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.6</td>\n",
       "      <td>2.89</td>\n",
       "      <td>2.89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23180 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              0                        1      \\\n",
       "date                     2018-01-01               2018-01-01   \n",
       "group                 GENERAL_ITEMS            GENERAL_ITEMS   \n",
       "name       BELLA HERBS 12 Z WERBENA  BELLA PODPASKI BLUE MAX   \n",
       "quantity                          1                        2   \n",
       "unit_cogs                      2.39                     2.85   \n",
       "\n",
       "                                       2                              3      \\\n",
       "date                              2018-01-01                     2018-01-01   \n",
       "group                          GENERAL_ITEMS                  GENERAL_ITEMS   \n",
       "name       BELLA PODPASKI NORMAL 20 B/SKRZYD  BELLA PODPASKI NORMAL MAXI 10   \n",
       "quantity                                   2                              4   \n",
       "unit_cogs                               1.76                           2.14   \n",
       "\n",
       "                                 4                            5      \\\n",
       "date                        2018-01-01                   2018-01-01   \n",
       "group                    GENERAL_ITEMS                GENERAL_ITEMS   \n",
       "name       BELLA PODPASKI NOVA MAXI 10  BELLA PODPASKI PERF.BLUE 10   \n",
       "quantity                             1                            1   \n",
       "unit_cogs                         2.66                         2.85   \n",
       "\n",
       "                                      6      \\\n",
       "date                             2018-01-01   \n",
       "group                         GENERAL_ITEMS   \n",
       "name       EVA MYDLO W PLYNIE Z DOZ 500 MML   \n",
       "quantity                                  1   \n",
       "unit_cogs                              3.71   \n",
       "\n",
       "                                             7                          8      \\\n",
       "date                                    2018-01-01                 2018-01-01   \n",
       "group                                GENERAL_ITEMS              GENERAL_ITEMS   \n",
       "name       PLYN DO ZATLUSZCZONYCH POWIERZCHNI  750  PLYN KAMIEN I RDZA 750 ML   \n",
       "quantity                                         2                          1   \n",
       "unit_cogs                                     5.49                       5.49   \n",
       "\n",
       "                                 9      ...                       23170  \\\n",
       "date                        2018-01-01  ...                  2018-01-12   \n",
       "group                    GENERAL_ITEMS  ...                     ALCOHOL   \n",
       "name       PODPASKI BELLA  NOVA 10 SZT  ...  WINO RAFFAELLO 0_2 SLODKIE   \n",
       "quantity                             1  ...                           1   \n",
       "unit_cogs                         2.05  ...                        3.25   \n",
       "\n",
       "                                23171                        23172  \\\n",
       "date                       2018-01-12                   2018-01-12   \n",
       "group                         ALCOHOL                      ALCOHOL   \n",
       "name       WINO ROYAL 0_75 B/WYTRAWNE  WINO SOLARRA CZ/PÓS? 0_75 L   \n",
       "quantity                            1                            1   \n",
       "unit_cogs                       10.13                         5.99   \n",
       "\n",
       "                                  23173                    23174  \\\n",
       "date                         2018-01-12               2018-01-12   \n",
       "group                           ALCOHOL                  ALCOHOL   \n",
       "name       WINO SOPHIA 0_75 L B/S?ODKIE  WINO SPECIAL CHERRY 1 L   \n",
       "quantity                              1                        1   \n",
       "unit_cogs                          6.01                     6.97   \n",
       "\n",
       "                                    23175  \\\n",
       "date                           2018-01-12   \n",
       "group                             ALCOHOL   \n",
       "name       WINO TOTINO CHERRY VERMOUTH 1L   \n",
       "quantity                                1   \n",
       "unit_cogs                             9.8   \n",
       "\n",
       "                                         23176  \\\n",
       "date                                2018-01-12   \n",
       "group                                  ALCOHOL   \n",
       "name       WINO WALDWEIN JEZYNA 0_75 L SLODKIE   \n",
       "quantity                                     2   \n",
       "unit_cogs                                  7.6   \n",
       "\n",
       "                                         23177  \\\n",
       "date                                2018-01-12   \n",
       "group                                  ALCOHOL   \n",
       "name       WINO WALDWEIN MALINA 0_75 L SLODKIE   \n",
       "quantity                                     1   \n",
       "unit_cogs                                  7.6   \n",
       "\n",
       "                                              23178  \\\n",
       "date                                     2018-01-12   \n",
       "group                                       ALCOHOL   \n",
       "name       WODKA DRINK SOBIESKI 025ML CRANBERRY4.4%   \n",
       "quantity                                          1   \n",
       "unit_cogs                                      2.89   \n",
       "\n",
       "                                          23179  \n",
       "date                                 2018-01-12  \n",
       "group                                   ALCOHOL  \n",
       "name       WODKA DRINK SOBIESKI KAMIKAZE.250 ML  \n",
       "quantity                                      2  \n",
       "unit_cogs                                  2.89  \n",
       "\n",
       "[5 rows x 23180 columns]"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_to_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "#transposing the dataset becasue you can see how it is above\n",
    "df_to_merge=df_to_merge.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "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>monthly_Avgtemp</th>\n",
       "      <th>monthly_avg_FeelsLikeC</th>\n",
       "      <th>monthly_avg_HeatIndexC</th>\n",
       "      <th>monthly_avg_cloudcover</th>\n",
       "      <th>monthly_avg_humidity</th>\n",
       "      <th>ishol/week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.420</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>-3.290</td>\n",
       "      <td>-7.61</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>76.04</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>0.165</td>\n",
       "      <td>-4.03</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>73.23</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>12.080</td>\n",
       "      <td>10.37</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>72.47</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>15.965</td>\n",
       "      <td>15.42</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>73.23</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>17.065</td>\n",
       "      <td>17.10</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>72.30</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>19.725</td>\n",
       "      <td>19.90</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>75.97</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>19.920</td>\n",
       "      <td>20.06</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>69.97</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-09</td>\n",
       "      <td>15.835</td>\n",
       "      <td>14.73</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>69.23</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-01-10</td>\n",
       "      <td>10.710</td>\n",
       "      <td>8.19</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>71.65</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>3.970</td>\n",
       "      <td>0.77</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>80.60</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>0.370</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date  monthly_Avgtemp  monthly_avg_FeelsLikeC  \\\n",
       "0   2018-01-01           -0.420                   -4.84   \n",
       "1   2018-01-02           -3.290                   -7.61   \n",
       "2   2018-01-03            0.165                   -4.03   \n",
       "3   2018-01-04           12.080                   10.37   \n",
       "4   2018-01-05           15.965                   15.42   \n",
       "5   2018-01-06           17.065                   17.10   \n",
       "6   2018-01-07           19.725                   19.90   \n",
       "7   2018-01-08           19.920                   20.06   \n",
       "8   2018-01-09           15.835                   14.73   \n",
       "9   2018-01-10           10.710                    8.19   \n",
       "10  2018-01-11            3.970                    0.77   \n",
       "11  2018-01-12            0.370                   -3.61   \n",
       "\n",
       "    monthly_avg_HeatIndexC  monthly_avg_cloudcover  monthly_avg_humidity  \\\n",
       "0                        1                       2                 82.81   \n",
       "1                        0                       2                 76.04   \n",
       "2                        1                       2                 73.23   \n",
       "3                        1                       1                 72.47   \n",
       "4                        2                       0                 73.23   \n",
       "5                        2                       1                 72.30   \n",
       "6                        2                       1                 75.97   \n",
       "7                        2                       0                 69.97   \n",
       "8                        2                       1                 69.23   \n",
       "9                        1                       1                 71.65   \n",
       "10                       1                       1                 80.60   \n",
       "11                       1                       3                 88.58   \n",
       "\n",
       "    ishol/week  \n",
       "0            9  \n",
       "1            8  \n",
       "2            9  \n",
       "3           10  \n",
       "4           11  \n",
       "5            9  \n",
       "6            9  \n",
       "7            9  \n",
       "8           10  \n",
       "9            8  \n",
       "10           9  \n",
       "11          12  "
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',\n",
       "       '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08',\n",
       "       '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_to_merge[\"date\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merging the dataset into one so that we can finally use it\n",
    "\n",
    "df_merged= pd.merge(weather_df, df_to_merge,how=\"left\",on=\"date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23180, 11)"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "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>monthly_Avgtemp</th>\n",
       "      <th>monthly_avg_FeelsLikeC</th>\n",
       "      <th>monthly_avg_HeatIndexC</th>\n",
       "      <th>monthly_avg_cloudcover</th>\n",
       "      <th>monthly_avg_humidity</th>\n",
       "      <th>ishol/week</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_cogs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA HERBS 12 Z WERBENA</td>\n",
       "      <td>1</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI BLUE MAX</td>\n",
       "      <td>2</td>\n",
       "      <td>2.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL 20 B/SKRZYD</td>\n",
       "      <td>2</td>\n",
       "      <td>1.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL MAXI 10</td>\n",
       "      <td>4</td>\n",
       "      <td>2.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NOVA MAXI 10</td>\n",
       "      <td>1</td>\n",
       "      <td>2.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  monthly_Avgtemp  monthly_avg_FeelsLikeC  \\\n",
       "0  2018-01-01            -0.42                   -4.84   \n",
       "1  2018-01-01            -0.42                   -4.84   \n",
       "2  2018-01-01            -0.42                   -4.84   \n",
       "3  2018-01-01            -0.42                   -4.84   \n",
       "4  2018-01-01            -0.42                   -4.84   \n",
       "\n",
       "   monthly_avg_HeatIndexC  monthly_avg_cloudcover  monthly_avg_humidity  \\\n",
       "0                       1                       2                 82.81   \n",
       "1                       1                       2                 82.81   \n",
       "2                       1                       2                 82.81   \n",
       "3                       1                       2                 82.81   \n",
       "4                       1                       2                 82.81   \n",
       "\n",
       "   ishol/week          group                               name quantity  \\\n",
       "0           9  GENERAL_ITEMS           BELLA HERBS 12 Z WERBENA        1   \n",
       "1           9  GENERAL_ITEMS            BELLA PODPASKI BLUE MAX        2   \n",
       "2           9  GENERAL_ITEMS  BELLA PODPASKI NORMAL 20 B/SKRZYD        2   \n",
       "3           9  GENERAL_ITEMS      BELLA PODPASKI NORMAL MAXI 10        4   \n",
       "4           9  GENERAL_ITEMS        BELLA PODPASKI NOVA MAXI 10        1   \n",
       "\n",
       "  unit_cogs  \n",
       "0      2.39  \n",
       "1      2.85  \n",
       "2      1.76  \n",
       "3      2.14  \n",
       "4      2.66  "
      ]
     },
     "execution_count": 217,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged=df_merged[[\"date\",\"ishol/week\",\"group\",\"name\",\"quantity\",\"unit_cogs\",\"monthly_Avgtemp\",\"monthly_avg_FeelsLikeC\",\"monthly_avg_HeatIndexC\",\"monthly_avg_cloudcover\",\"monthly_avg_humidity\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "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>ishol/week</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_cogs</th>\n",
       "      <th>monthly_Avgtemp</th>\n",
       "      <th>monthly_avg_FeelsLikeC</th>\n",
       "      <th>monthly_avg_HeatIndexC</th>\n",
       "      <th>monthly_avg_cloudcover</th>\n",
       "      <th>monthly_avg_humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA HERBS 12 Z WERBENA</td>\n",
       "      <td>1</td>\n",
       "      <td>2.39</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI BLUE MAX</td>\n",
       "      <td>2</td>\n",
       "      <td>2.85</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL 20 B/SKRZYD</td>\n",
       "      <td>2</td>\n",
       "      <td>1.76</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NORMAL MAXI 10</td>\n",
       "      <td>4</td>\n",
       "      <td>2.14</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>9</td>\n",
       "      <td>GENERAL_ITEMS</td>\n",
       "      <td>BELLA PODPASKI NOVA MAXI 10</td>\n",
       "      <td>1</td>\n",
       "      <td>2.66</td>\n",
       "      <td>-0.42</td>\n",
       "      <td>-4.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>82.81</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23175</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>12</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>WINO TOTINO CHERRY VERMOUTH 1L</td>\n",
       "      <td>1</td>\n",
       "      <td>9.8</td>\n",
       "      <td>0.37</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23176</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>12</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>WINO WALDWEIN JEZYNA 0_75 L SLODKIE</td>\n",
       "      <td>2</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.37</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23177</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>12</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>WINO WALDWEIN MALINA 0_75 L SLODKIE</td>\n",
       "      <td>1</td>\n",
       "      <td>7.6</td>\n",
       "      <td>0.37</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23178</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>12</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>WODKA DRINK SOBIESKI 025ML CRANBERRY4.4%</td>\n",
       "      <td>1</td>\n",
       "      <td>2.89</td>\n",
       "      <td>0.37</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23179</th>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>12</td>\n",
       "      <td>ALCOHOL</td>\n",
       "      <td>WODKA DRINK SOBIESKI KAMIKAZE.250 ML</td>\n",
       "      <td>2</td>\n",
       "      <td>2.89</td>\n",
       "      <td>0.37</td>\n",
       "      <td>-3.61</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>88.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23180 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date  ishol/week          group  \\\n",
       "0      2018-01-01           9  GENERAL_ITEMS   \n",
       "1      2018-01-01           9  GENERAL_ITEMS   \n",
       "2      2018-01-01           9  GENERAL_ITEMS   \n",
       "3      2018-01-01           9  GENERAL_ITEMS   \n",
       "4      2018-01-01           9  GENERAL_ITEMS   \n",
       "...           ...         ...            ...   \n",
       "23175  2018-01-12          12        ALCOHOL   \n",
       "23176  2018-01-12          12        ALCOHOL   \n",
       "23177  2018-01-12          12        ALCOHOL   \n",
       "23178  2018-01-12          12        ALCOHOL   \n",
       "23179  2018-01-12          12        ALCOHOL   \n",
       "\n",
       "                                           name quantity unit_cogs  \\\n",
       "0                      BELLA HERBS 12 Z WERBENA        1      2.39   \n",
       "1                       BELLA PODPASKI BLUE MAX        2      2.85   \n",
       "2             BELLA PODPASKI NORMAL 20 B/SKRZYD        2      1.76   \n",
       "3                 BELLA PODPASKI NORMAL MAXI 10        4      2.14   \n",
       "4                   BELLA PODPASKI NOVA MAXI 10        1      2.66   \n",
       "...                                         ...      ...       ...   \n",
       "23175            WINO TOTINO CHERRY VERMOUTH 1L        1       9.8   \n",
       "23176       WINO WALDWEIN JEZYNA 0_75 L SLODKIE        2       7.6   \n",
       "23177       WINO WALDWEIN MALINA 0_75 L SLODKIE        1       7.6   \n",
       "23178  WODKA DRINK SOBIESKI 025ML CRANBERRY4.4%        1      2.89   \n",
       "23179      WODKA DRINK SOBIESKI KAMIKAZE.250 ML        2      2.89   \n",
       "\n",
       "       monthly_Avgtemp  monthly_avg_FeelsLikeC  monthly_avg_HeatIndexC  \\\n",
       "0                -0.42                   -4.84                       1   \n",
       "1                -0.42                   -4.84                       1   \n",
       "2                -0.42                   -4.84                       1   \n",
       "3                -0.42                   -4.84                       1   \n",
       "4                -0.42                   -4.84                       1   \n",
       "...                ...                     ...                     ...   \n",
       "23175             0.37                   -3.61                       1   \n",
       "23176             0.37                   -3.61                       1   \n",
       "23177             0.37                   -3.61                       1   \n",
       "23178             0.37                   -3.61                       1   \n",
       "23179             0.37                   -3.61                       1   \n",
       "\n",
       "       monthly_avg_cloudcover  monthly_avg_humidity  \n",
       "0                           2                 82.81  \n",
       "1                           2                 82.81  \n",
       "2                           2                 82.81  \n",
       "3                           2                 82.81  \n",
       "4                           2                 82.81  \n",
       "...                       ...                   ...  \n",
       "23175                       3                 88.58  \n",
       "23176                       3                 88.58  \n",
       "23177                       3                 88.58  \n",
       "23178                       3                 88.58  \n",
       "23179                       3                 88.58  \n",
       "\n",
       "[23180 rows x 11 columns]"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23180, 11)"
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.to_csv(\"MERGED_FINAL_DATASET.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}