02 - Data Wrangling/02 - Data Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 1: Prosecco title length"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>The hypothesis:</b> <br>\"Manufacturers of poor Prosecco wines attempt to compensate for the low quality of their wines by choosing long and fancy names.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>title</th>\n",
       "      <th>price</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bellussi NV Extra Dry  (Prosecco di Valdobbiad...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paladin 2007 Millesimato Brut Prosecco (Veneto)</td>\n",
       "      <td>20.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Perlage 2008 Col di Manza Extra Dry Millesimat...</td>\n",
       "      <td>22.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sant Eurosia 2007 Brut  (Prosecco di Valdobbia...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sant Eurosia 2007 Millesimato Dry  (Prosecco d...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>Mionetto NV Brut  (Prosecco del Veneto)</td>\n",
       "      <td>12.0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>Moletto NV Frizzante Prosecco (Marca Trevigiana)</td>\n",
       "      <td>14.0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>Perlage NV Canah Brut  (Prosecco di Valdobbiad...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>Valdo NV Extra Dry  (Prosecco del Veneto)</td>\n",
       "      <td>10.0</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>Col Vetoraz Spumanti NV  Prosecco Superiore di...</td>\n",
       "      <td>38.0</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>236 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  price  points\n",
       "0    Bellussi NV Extra Dry  (Prosecco di Valdobbiad...   15.0      86\n",
       "1      Paladin 2007 Millesimato Brut Prosecco (Veneto)   20.0      86\n",
       "2    Perlage 2008 Col di Manza Extra Dry Millesimat...   22.0      86\n",
       "3    Sant Eurosia 2007 Brut  (Prosecco di Valdobbia...   16.0      86\n",
       "4    Sant Eurosia 2007 Millesimato Dry  (Prosecco d...   18.0      86\n",
       "..                                                 ...    ...     ...\n",
       "231            Mionetto NV Brut  (Prosecco del Veneto)   12.0      85\n",
       "232   Moletto NV Frizzante Prosecco (Marca Trevigiana)   14.0      85\n",
       "233  Perlage NV Canah Brut  (Prosecco di Valdobbiad...   19.0      85\n",
       "234          Valdo NV Extra Dry  (Prosecco del Veneto)   10.0      85\n",
       "235  Col Vetoraz Spumanti NV  Prosecco Superiore di...   38.0      91\n",
       "\n",
       "[236 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading dataset\n",
    "wine_df = pd.read_csv('../Datasets/winemag-data-130k-v2.csv')\n",
    "\n",
    "# Creating a new dataframe with Prosecco wines only\n",
    "prosecco_df = wine_df.loc[wine_df['variety'] == 'Prosecco']\n",
    "prosecco_df.reset_index(inplace=True)\n",
    "prosecco_df = prosecco_df[['title', 'price', 'points']]\n",
    "prosecco_df"
   ]
  },
  {
   "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>title</th>\n",
       "      <th>price</th>\n",
       "      <th>points</th>\n",
       "      <th>title_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>Col Vetoraz Spumanti NV  Prosecco Superiore di...</td>\n",
       "      <td>38.0</td>\n",
       "      <td>91</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Ruggeri &amp; C. 2007 Giustino B. Extra Dry  (Pros...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>91</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Adami NV  Prosecco Superiore di Cartizze</td>\n",
       "      <td>32.0</td>\n",
       "      <td>90</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Bisol NV Cartizze  (Prosecco Superiore di Cart...</td>\n",
       "      <td>41.0</td>\n",
       "      <td>90</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>Bortolomiol NV  Prosecco Superiore di Cartizze</td>\n",
       "      <td>30.0</td>\n",
       "      <td>90</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>Bortolomiol 2006 Cartizze  (Prosecco Superiore...</td>\n",
       "      <td>35.0</td>\n",
       "      <td>90</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Astoria NV Cartizze  (Prosecco Superiore di Ca...</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>Astoria 2006 Millesimato Extra Dry  (Prosecco ...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>90</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>Bisol NV Crede  (Prosecco di Valdobbiadene)</td>\n",
       "      <td>25.0</td>\n",
       "      <td>90</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Sorelle Bronca NV Extra Dry Particella 68  (Pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Bortolomiol 2008 Cartizze Dry  (Prosecco Super...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>90</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Sorelle Bronca NV Particella 68 Extra Dry  (Pr...</td>\n",
       "      <td>24.0</td>\n",
       "      <td>90</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Adami NV Cartizze Dry  (Prosecco Superiore di ...</td>\n",
       "      <td>32.0</td>\n",
       "      <td>90</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Bisol 2007 Cartizze  (Prosecco Superiore di Ca...</td>\n",
       "      <td>48.0</td>\n",
       "      <td>90</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Ruggeri &amp; C. NV  Prosecco Superiore di Cartizze</td>\n",
       "      <td>30.0</td>\n",
       "      <td>90</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Nino Franco 2007 Rive di San Floriano Brut  (P...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>90</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>Bortolomiol 2006 Cartizze  (Prosecco Superiore...</td>\n",
       "      <td>35.0</td>\n",
       "      <td>90</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  price  points  \\\n",
       "235  Col Vetoraz Spumanti NV  Prosecco Superiore di...   38.0      91   \n",
       "123  Ruggeri & C. 2007 Giustino B. Extra Dry  (Pros...   36.0      91   \n",
       "162           Adami NV  Prosecco Superiore di Cartizze   32.0      90   \n",
       "216  Bisol NV Cartizze  (Prosecco Superiore di Cart...   41.0      90   \n",
       "215     Bortolomiol NV  Prosecco Superiore di Cartizze   30.0      90   \n",
       "180  Bortolomiol 2006 Cartizze  (Prosecco Superiore...   35.0      90   \n",
       "179  Astoria NV Cartizze  (Prosecco Superiore di Ca...   21.0      90   \n",
       "178  Astoria 2006 Millesimato Extra Dry  (Prosecco ...   20.0      90   \n",
       "163        Bisol NV Crede  (Prosecco di Valdobbiadene)   25.0      90   \n",
       "39   Sorelle Bronca NV Extra Dry Particella 68  (Pr...    NaN      90   \n",
       "103  Bortolomiol 2008 Cartizze Dry  (Prosecco Super...   30.0      90   \n",
       "145  Sorelle Bronca NV Particella 68 Extra Dry  (Pr...   24.0      90   \n",
       "144  Adami NV Cartizze Dry  (Prosecco Superiore di ...   32.0      90   \n",
       "143  Bisol 2007 Cartizze  (Prosecco Superiore di Ca...   48.0      90   \n",
       "124    Ruggeri & C. NV  Prosecco Superiore di Cartizze   30.0      90   \n",
       "104  Nino Franco 2007 Rive di San Floriano Brut  (P...   30.0      90   \n",
       "154  Bortolomiol 2006 Cartizze  (Prosecco Superiore...   35.0      90   \n",
       "\n",
       "     title_length  \n",
       "235            55  \n",
       "123            68  \n",
       "162            40  \n",
       "216            51  \n",
       "215            46  \n",
       "180            59  \n",
       "179            53  \n",
       "178            76  \n",
       "163            43  \n",
       "39             70  \n",
       "103            63  \n",
       "145            70  \n",
       "144            55  \n",
       "143            53  \n",
       "124            47  \n",
       "104            71  \n",
       "154            59  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding a new column with title length \n",
    "for row in prosecco_df.iterrows():\n",
    "    index = row[0]\n",
    "    title = row[1][0]\n",
    "    title_length = len(title)\n",
    "    prosecco_df.loc[index, 'title_length'] = title_length\n",
    "\n",
    "prosecco_df['title_length'] = prosecco_df['title_length'].astype(int)\n",
    "\n",
    "\n",
    "# Creating two new dataframes: \n",
    "# One for good prosecco (rating > 89) and one for bad prosecco (rating < 85)\n",
    "good_prosecco = prosecco_df.loc[prosecco_df['points'] > 89].sort_values(['points'], ascending=False)\n",
    "bad_prosecco = prosecco_df.loc[prosecco_df['points'] < 85].sort_values(['points'], ascending=False)\n",
    "good_prosecco.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>title</th>\n",
       "      <th>price</th>\n",
       "      <th>points</th>\n",
       "      <th>title_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Bellenda NV San Fermo Brut  (Prosecco di Coneg...</td>\n",
       "      <td>19.0</td>\n",
       "      <td>84</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Cantina San Martino NV Pittaro Extra Dry  (Pro...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>84</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Lisabella NV Gran Resèe Prosecco (Colli Trevig...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>84</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Mionetto NV Certified Organic Extra Dry Prosec...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>84</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Tiamo NV Extra Dry Prosecco (Veneto)</td>\n",
       "      <td>14.0</td>\n",
       "      <td>84</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Collalbrigo NV Extra Dry  (Prosecco di Conegli...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>84</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Canella NV Extra Dry  (Prosecco di Conegliano)</td>\n",
       "      <td>18.0</td>\n",
       "      <td>84</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Terra Serena NV Extra Dry  (Prosecco di Conegl...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>84</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Col Saliz NV Extra Dry  (Prosecco di Valdobbia...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>84</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Varaschin NV  Prosecco Superiore di Cartizze</td>\n",
       "      <td>29.0</td>\n",
       "      <td>84</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>Tenuta Santomè NV Extra Dry  (Prosecco del Ven...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Perlage NV Col di Manza Extra Dry  (Prosecco d...</td>\n",
       "      <td>22.0</td>\n",
       "      <td>84</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>Villa Granda NV Frizzante Prosecco (Colli Trev...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>Toffoli NV Brut  (Prosecco di Conegliano e Val...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>84</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Zonin NV Prosecco (Italy)</td>\n",
       "      <td>12.0</td>\n",
       "      <td>84</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Carmina NV Brut  (Prosecco di Conegliano e Val...</td>\n",
       "      <td>15.0</td>\n",
       "      <td>83</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Tosti NV Prosecco (Italy)</td>\n",
       "      <td>15.0</td>\n",
       "      <td>83</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Varaschin NV Brut  (Prosecco di Valdobbiadene)</td>\n",
       "      <td>19.0</td>\n",
       "      <td>83</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Carmina NV Brut  (Prosecco di Conegliano e Val...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>82</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Maschio dei Cavalieri NV Maschio dei Cavalieri...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>82</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  price  points  \\\n",
       "134  Bellenda NV San Fermo Brut  (Prosecco di Coneg...   19.0      84   \n",
       "93   Cantina San Martino NV Pittaro Extra Dry  (Pro...   15.0      84   \n",
       "94   Lisabella NV Gran Resèe Prosecco (Colli Trevig...   12.0      84   \n",
       "95   Mionetto NV Certified Organic Extra Dry Prosec...   16.0      84   \n",
       "117               Tiamo NV Extra Dry Prosecco (Veneto)   14.0      84   \n",
       "214  Collalbrigo NV Extra Dry  (Prosecco di Conegli...   16.0      84   \n",
       "213     Canella NV Extra Dry  (Prosecco di Conegliano)   18.0      84   \n",
       "131  Terra Serena NV Extra Dry  (Prosecco di Conegl...   12.0      84   \n",
       "132  Col Saliz NV Extra Dry  (Prosecco di Valdobbia...   15.0      84   \n",
       "133       Varaschin NV  Prosecco Superiore di Cartizze   29.0      84   \n",
       "212  Tenuta Santomè NV Extra Dry  (Prosecco del Ven...    NaN      84   \n",
       "146  Perlage NV Col di Manza Extra Dry  (Prosecco d...   22.0      84   \n",
       "211  Villa Granda NV Frizzante Prosecco (Colli Trev...    NaN      84   \n",
       "148  Toffoli NV Brut  (Prosecco di Conegliano e Val...   18.0      84   \n",
       "149                          Zonin NV Prosecco (Italy)   12.0      84   \n",
       "161  Carmina NV Brut  (Prosecco di Conegliano e Val...   15.0      83   \n",
       "32                           Tosti NV Prosecco (Italy)   15.0      83   \n",
       "147     Varaschin NV Brut  (Prosecco di Valdobbiadene)   19.0      83   \n",
       "164  Carmina NV Brut  (Prosecco di Conegliano e Val...   16.0      82   \n",
       "129  Maschio dei Cavalieri NV Maschio dei Cavalieri...   20.0      82   \n",
       "\n",
       "     title_length  \n",
       "134            68  \n",
       "93             63  \n",
       "94             51  \n",
       "95             57  \n",
       "117            36  \n",
       "214            50  \n",
       "213            46  \n",
       "131            67  \n",
       "132            51  \n",
       "133            44  \n",
       "212            50  \n",
       "146            62  \n",
       "211            53  \n",
       "148            57  \n",
       "149            25  \n",
       "161            57  \n",
       "32             25  \n",
       "147            46  \n",
       "164            57  \n",
       "129            75  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_prosecco.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Good prosecco mean title length: 57.588235294117645\n",
      "Bad prosecco mean title length: 53.73913043478261\n"
     ]
    }
   ],
   "source": [
    "# Calculating the mean title length for good and bad prosecco wines\n",
    "good_prosecco_len_sum = 0\n",
    "for row in good_prosecco.iterrows():\n",
    "    index = row[0]\n",
    "    title_length = row[1][3]\n",
    "    good_prosecco_len_sum += title_length\n",
    "\n",
    "good_prosecco_len_avg = good_prosecco_len_sum / len(good_prosecco)\n",
    "print(f'Good prosecco mean title length: {good_prosecco_len_avg}')\n",
    "\n",
    "bad_prosecco_len_sum = 0\n",
    "for row in bad_prosecco.iterrows():\n",
    "    index = row[0]\n",
    "    title_length = row[1][3]\n",
    "    bad_prosecco_len_sum += title_length\n",
    "\n",
    "bad_prosecco_len_avg = bad_prosecco_len_sum / len(bad_prosecco)\n",
    "print(f'Bad prosecco mean title length: {bad_prosecco_len_avg}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Conclusion:</b> <br>It seems that the hypothesis was wrong, as the data indicates the opposite: That better Proseccos have longer names.  <br><br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 2: Ramen reviews \n",
    "<i> Which country has the best ramen?</i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Stars</th>\n",
       "      <th>q10</th>\n",
       "      <th>q90</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>4.350000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.800</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sarawak</th>\n",
       "      <td>4.333333</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.800</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cambodia</th>\n",
       "      <td>4.200000</td>\n",
       "      <td>3.500</td>\n",
       "      <td>5.000</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>4.127564</td>\n",
       "      <td>3.125</td>\n",
       "      <td>5.000</td>\n",
       "      <td>156.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Singapore</th>\n",
       "      <td>4.126147</td>\n",
       "      <td>3.200</td>\n",
       "      <td>5.000</td>\n",
       "      <td>109.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indonesia</th>\n",
       "      <td>4.067460</td>\n",
       "      <td>3.250</td>\n",
       "      <td>5.000</td>\n",
       "      <td>126.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>3.981605</td>\n",
       "      <td>3.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>352.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Myanmar</th>\n",
       "      <td>3.946429</td>\n",
       "      <td>2.975</td>\n",
       "      <td>5.000</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <td>3.875000</td>\n",
       "      <td>3.475</td>\n",
       "      <td>4.175</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hong Kong</th>\n",
       "      <td>3.801825</td>\n",
       "      <td>2.750</td>\n",
       "      <td>5.000</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>3.766019</td>\n",
       "      <td>2.750</td>\n",
       "      <td>5.000</td>\n",
       "      <td>309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>3.750000</td>\n",
       "      <td>3.750</td>\n",
       "      <td>3.750</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>3.730000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bangladesh</th>\n",
       "      <td>3.714286</td>\n",
       "      <td>3.250</td>\n",
       "      <td>4.000</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taiwan</th>\n",
       "      <td>3.665402</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>224.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>3.638889</td>\n",
       "      <td>3.000</td>\n",
       "      <td>4.350</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poland</th>\n",
       "      <td>3.625000</td>\n",
       "      <td>3.250</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>3.611111</td>\n",
       "      <td>2.950</td>\n",
       "      <td>4.150</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dubai</th>\n",
       "      <td>3.583333</td>\n",
       "      <td>3.350</td>\n",
       "      <td>3.750</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>3.583333</td>\n",
       "      <td>3.500</td>\n",
       "      <td>3.700</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holland</th>\n",
       "      <td>3.562500</td>\n",
       "      <td>3.500</td>\n",
       "      <td>3.675</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nepal</th>\n",
       "      <td>3.553571</td>\n",
       "      <td>3.325</td>\n",
       "      <td>4.175</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Estonia</th>\n",
       "      <td>3.500000</td>\n",
       "      <td>3.300</td>\n",
       "      <td>3.700</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ghana</th>\n",
       "      <td>3.500000</td>\n",
       "      <td>3.500</td>\n",
       "      <td>3.500</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>3.457043</td>\n",
       "      <td>2.000</td>\n",
       "      <td>4.750</td>\n",
       "      <td>323.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>3.421893</td>\n",
       "      <td>1.750</td>\n",
       "      <td>4.500</td>\n",
       "      <td>169.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>3.395161</td>\n",
       "      <td>2.000</td>\n",
       "      <td>4.250</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thailand</th>\n",
       "      <td>3.384817</td>\n",
       "      <td>2.000</td>\n",
       "      <td>4.750</td>\n",
       "      <td>191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Philippines</th>\n",
       "      <td>3.329787</td>\n",
       "      <td>1.900</td>\n",
       "      <td>4.500</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colombia</th>\n",
       "      <td>3.291667</td>\n",
       "      <td>2.875</td>\n",
       "      <td>3.625</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>3.250000</td>\n",
       "      <td>3.050</td>\n",
       "      <td>3.450</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>3.187963</td>\n",
       "      <td>2.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>3.138636</td>\n",
       "      <td>2.025</td>\n",
       "      <td>4.000</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pakistan</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.400</td>\n",
       "      <td>3.600</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UK</th>\n",
       "      <td>2.997101</td>\n",
       "      <td>1.500</td>\n",
       "      <td>4.050</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>2.483333</td>\n",
       "      <td>0.500</td>\n",
       "      <td>3.500</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>2.243902</td>\n",
       "      <td>0.250</td>\n",
       "      <td>3.500</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nigeria</th>\n",
       "      <td>1.500000</td>\n",
       "      <td>1.500</td>\n",
       "      <td>1.500</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Stars    q10    q90  Count\n",
       "                   mean                     \n",
       "Country                                     \n",
       "Brazil         4.350000  4.000  4.800    5.0\n",
       "Sarawak        4.333333  4.000  4.800    3.0\n",
       "Cambodia       4.200000  3.500  5.000    5.0\n",
       "Malaysia       4.127564  3.125  5.000  156.0\n",
       "Singapore      4.126147  3.200  5.000  109.0\n",
       "Indonesia      4.067460  3.250  5.000  126.0\n",
       "Japan          3.981605  3.000  5.000  352.0\n",
       "Myanmar        3.946429  2.975  5.000   14.0\n",
       "Fiji           3.875000  3.475  4.175    4.0\n",
       "Hong Kong      3.801825  2.750  5.000  137.0\n",
       "South Korea    3.766019  2.750  5.000  309.0\n",
       "United States  3.750000  3.750  3.750    1.0\n",
       "Mexico         3.730000  3.000  4.000   25.0\n",
       "Bangladesh     3.714286  3.250  4.000    7.0\n",
       "Taiwan         3.665402  2.000  5.000  224.0\n",
       "Germany        3.638889  3.000  4.350   27.0\n",
       "Poland         3.625000  3.250  4.000    4.0\n",
       "Hungary        3.611111  2.950  4.150    9.0\n",
       "Dubai          3.583333  3.350  3.750    3.0\n",
       "Finland        3.583333  3.500  3.700    3.0\n",
       "Holland        3.562500  3.500  3.675    4.0\n",
       "Nepal          3.553571  3.325  4.175   14.0\n",
       "Estonia        3.500000  3.300  3.700    2.0\n",
       "Ghana          3.500000  3.500  3.500    2.0\n",
       "USA            3.457043  2.000  4.750  323.0\n",
       "China          3.421893  1.750  4.500  169.0\n",
       "India          3.395161  2.000  4.250   31.0\n",
       "Thailand       3.384817  2.000  4.750  191.0\n",
       "Philippines    3.329787  1.900  4.500   47.0\n",
       "Colombia       3.291667  2.875  3.625    6.0\n",
       "Sweden         3.250000  3.050  3.450    3.0\n",
       "Vietnam        3.187963  2.000  4.000  108.0\n",
       "Australia      3.138636  2.025  4.000   22.0\n",
       "Pakistan       3.000000  2.400  3.600    9.0\n",
       "UK             2.997101  1.500  4.050   69.0\n",
       "Netherlands    2.483333  0.500  3.500   15.0\n",
       "Canada         2.243902  0.250  3.500   41.0\n",
       "Nigeria        1.500000  1.500  1.500    1.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading dataset\n",
    "ramen = pd.read_csv('../Datasets/ramen-ratings.csv')\n",
    "ramen['Stars'].replace(to_replace='Unrated', value=0, inplace=True)\n",
    "ramen['Stars'] = ramen['Stars'].astype(float)\n",
    "\n",
    "# Cleaning dataset. Replacing null values for ratings (Unrated) with 0. Set 'Stars' to float\n",
    "ramen.drop(labels='Review #', axis=1, inplace=True)\n",
    "ramen['Stars'].replace(to_replace='Unrated', value=0, inplace=True)\n",
    "ramen['Stars'] = ramen['Stars'].astype(float)\n",
    "\n",
    "# Grouping data by country and aggregating the mean and the 10th and 90th quantiles\n",
    "ramen_country_stats = ramen.groupby(by=['Country'], sort=True).agg([np.mean])\n",
    "ramen_country_stats['q10'] = ramen.groupby(by=['Country'], sort=True).describe(percentiles=[0.1])[('Stars',   '10%')]\n",
    "ramen_country_stats['q90'] = ramen.groupby(by=['Country'], sort=True).describe(percentiles=[0.9])[('Stars',   '90%')]\n",
    "\n",
    "ramen_country_stats['Count'] = ramen.groupby(by=['Country'], sort=True).describe()[('Stars', 'count')]\n",
    "ramen_country_stats.sort_values(by=[('Stars', 'mean'), 'q90'], axis=0, ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Conclusion:</b> On average, Brazil has the best ramen with a mean rating of 4.35. They also have a very high 10th percentile, so if you get a hold of Brazilian ramen, you know that it's going to be good. However, the entire country only sells 5 different ramen products with a 90th percentile of 4.8, while Malaysia sells more than 150 different ramen products. Malaysia has a slightly lower average rating of 4.13, but has a higher 90th percentile of 5.00. I would therefore recommend Cambodian ramen on a general basis, because it will most likely be easier to find in stores than Brazilian ramen, while at the same time supplying a wider range of top-of-the-line ramen products. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b><i>But which style of ramen is most popular in each country?</i></b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>%</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th>Style</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Australia</th>\n",
       "      <th>Cup</th>\n",
       "      <td>0.772727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pack</th>\n",
       "      <td>0.227273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bangladesh</th>\n",
       "      <th>Pack</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Brazil</th>\n",
       "      <th>Cup</th>\n",
       "      <td>0.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pack</th>\n",
       "      <td>0.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <th>Pack</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Vietnam</th>\n",
       "      <th>Bowl</th>\n",
       "      <td>0.185185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cup</th>\n",
       "      <td>0.074074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pack</th>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tray</th>\n",
       "      <td>0.018519</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>87 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            %\n",
       "Country       Style          \n",
       "Australia     Cup    0.772727\n",
       "              Pack   0.227273\n",
       "Bangladesh    Pack   1.000000\n",
       "Brazil        Cup    0.400000\n",
       "              Pack   0.600000\n",
       "...                       ...\n",
       "United States Pack   1.000000\n",
       "Vietnam       Bowl   0.185185\n",
       "              Cup    0.074074\n",
       "              Pack   0.722222\n",
       "              Tray   0.018519\n",
       "\n",
       "[87 rows x 1 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading a fresh dataset\n",
    "ramen = pd.read_csv('../Datasets/ramen-ratings.csv')\n",
    "\n",
    "# Counting the number of different ramen products in each country, as well as the number of different styles \n",
    "ramen_country_style = ramen.groupby(by=['Country', 'Style']).count().drop(['Brand', 'Variety', 'Stars', 'Top Ten'], axis=1)\n",
    "ramen_country_totals = ramen.groupby(by=['Country']).count().drop(['Style', 'Brand', 'Variety', 'Stars', 'Top Ten'], axis=1)\n",
    "\n",
    "# Calculating the proportion for each different style in each country\n",
    "for row1 in ramen_country_style.iterrows():\n",
    "    index = row1[0]\n",
    "    country = row1[0][0]\n",
    "    review_count = float(row1[1][0])\n",
    "\n",
    "    for row2 in ramen_country_totals.iterrows():\n",
    "        total = float(row2[1][0])\n",
    "        totals_country = row2[0]\n",
    "\n",
    "        if country == totals_country:\n",
    "            ramen_country_style.loc[index, '%'] = review_count / total\n",
    "\n",
    "# Dropping the Review # column as we don't care about review count\n",
    "ramen_country_style.drop(['Review #'], axis=1, inplace=True)\n",
    "\n",
    "# Displaying the result\n",
    "ramen_country_style"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 3: Customer data: Men vs Women"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b> Hypothesis: </b> \"Men order more than women at restaurants. \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing data:\n",
      " item_count    0.029682\n",
      "gender        0.131905\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gender</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>2.381009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>2.414087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        item_count\n",
       "gender            \n",
       "Female    2.381009\n",
       "Male      2.414087"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading data\n",
    "orders = pd.read_csv('../Datasets/orders.csv', low_memory=False)\n",
    "customers = pd.read_csv('../Datasets/customers.csv')\n",
    "\n",
    "# Preparing datasets for join operation. \n",
    "customers.rename(columns={'akeed_customer_id': 'customer_id'}, inplace=True)\n",
    "customers = customers.loc[:, ['customer_id', 'gender']]\n",
    "orders = orders.loc[:, ['customer_id', 'item_count']]\n",
    "# The spelling of the genders is not consistent. Fixing that with regex\n",
    "customers['gender'] = customers.gender.str.replace('(F|f)emale.*', 'Female', regex=True)\n",
    "customers['gender'] = customers.gender.str.replace('^(M|m)ale.*', 'Male', regex=True)\n",
    "customers['gender'] = customers.gender.str.replace('\\s+', '<NA>', regex=True)\n",
    "\n",
    "# Perform join so that we can calculate the average item count for each gender\n",
    "merged = orders.join(customers.set_index('customer_id'), on='customer_id')\n",
    "merged.drop('customer_id', axis=1, inplace=True)\n",
    "\n",
    "merged.replace(to_replace='<NA>', value=pd.NA, regex=True, inplace=True)\n",
    "print('Missing data:\\n' , merged.isnull().sum() / merged.count().sum())\n",
    "# Removing missing values, as we cannot use this data for the calculation. \n",
    "# 13% of the gender data is messing, but as the data most likely is missing at random, the impact should be negligible. \n",
    "merged.dropna(inplace=True)\n",
    "\n",
    "# Aggregate the mean item count for each gender\n",
    "merged_grouped = merged.groupby('gender').agg(np.mean)\n",
    "merged_grouped.head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Conclusion:</b> The hypothesis was wrong. Men and women order about the same number of items. <br><br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Excercise 4: Track billboard data from the glorious year 2000\n",
    "Out of all the artists that reached the billboards in the year 2000, which artist held the highest average billboard rank?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>Week</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Independent Women Part I</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-09-23</td>\n",
       "      <td>2000-11-18</td>\n",
       "      <td>x1st.week</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santana</td>\n",
       "      <td>Maria, Maria</td>\n",
       "      <td>4:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-02-12</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>x1st.week</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Savage Garden</td>\n",
       "      <td>I Knew I Loved You</td>\n",
       "      <td>4:07</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-10-23</td>\n",
       "      <td>2000-01-29</td>\n",
       "      <td>x1st.week</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Madonna</td>\n",
       "      <td>Music</td>\n",
       "      <td>3:45</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>2000-09-16</td>\n",
       "      <td>x1st.week</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>Come On Over Baby (All I Want Is You)</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-05</td>\n",
       "      <td>2000-10-14</td>\n",
       "      <td>x1st.week</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       artist.inverted                                  track  time genre  \\\n",
       "0      Destiny's Child               Independent Women Part I  3:38  Rock   \n",
       "1              Santana                           Maria, Maria  4:18  Rock   \n",
       "2        Savage Garden                     I Knew I Loved You  4:07  Rock   \n",
       "3              Madonna                                  Music  3:45  Rock   \n",
       "4  Aguilera, Christina  Come On Over Baby (All I Want Is You)  3:38  Rock   \n",
       "\n",
       "  date.entered date.peaked       Week  Rank  \n",
       "0   2000-09-23  2000-11-18  x1st.week  78.0  \n",
       "1   2000-02-12  2000-04-08  x1st.week  15.0  \n",
       "2   1999-10-23  2000-01-29  x1st.week  71.0  \n",
       "3   2000-08-12  2000-09-16  x1st.week  41.0  \n",
       "4   2000-08-05  2000-10-14  x1st.week  57.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading dataset\n",
    "billboard = pd.read_csv('../Datasets/billboard.csv')\n",
    "\n",
    "# The column headers are values, not variable names. Tidying dataframe\n",
    "# Adding all week values to a single list variable\n",
    "weeks_listed = billboard.columns.tolist()[7:]\n",
    "melted = pd.melt(billboard, id_vars=[\"artist.inverted\", \"track\", 'time', 'genre', 'date.entered', 'date.peaked'],\n",
    "                 value_vars=weeks_listed, var_name=\"Week\", value_name=\"Rank\")\n",
    "\n",
    "melted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genre</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>Week</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Destiny's Child</td>\n",
       "      <td>Independent Women Part I</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-09-23</td>\n",
       "      <td>2000-11-18</td>\n",
       "      <td>1</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Santana</td>\n",
       "      <td>Maria, Maria</td>\n",
       "      <td>4:18</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-02-12</td>\n",
       "      <td>2000-04-08</td>\n",
       "      <td>1</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Savage Garden</td>\n",
       "      <td>I Knew I Loved You</td>\n",
       "      <td>4:07</td>\n",
       "      <td>Rock</td>\n",
       "      <td>1999-10-23</td>\n",
       "      <td>2000-01-29</td>\n",
       "      <td>1</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Madonna</td>\n",
       "      <td>Music</td>\n",
       "      <td>3:45</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-12</td>\n",
       "      <td>2000-09-16</td>\n",
       "      <td>1</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aguilera, Christina</td>\n",
       "      <td>Come On Over Baby (All I Want Is You)</td>\n",
       "      <td>3:38</td>\n",
       "      <td>Rock</td>\n",
       "      <td>2000-08-05</td>\n",
       "      <td>2000-10-14</td>\n",
       "      <td>1</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       artist.inverted                                  track  time genre  \\\n",
       "0      Destiny's Child               Independent Women Part I  3:38  Rock   \n",
       "1              Santana                           Maria, Maria  4:18  Rock   \n",
       "2        Savage Garden                     I Knew I Loved You  4:07  Rock   \n",
       "3              Madonna                                  Music  3:45  Rock   \n",
       "4  Aguilera, Christina  Come On Over Baby (All I Want Is You)  3:38  Rock   \n",
       "\n",
       "  date.entered date.peaked Week  Rank  \n",
       "0   2000-09-23  2000-11-18    1  78.0  \n",
       "1   2000-02-12  2000-04-08    1  15.0  \n",
       "2   1999-10-23  2000-01-29    1  71.0  \n",
       "3   2000-08-12  2000-09-16    1  41.0  \n",
       "4   2000-08-05  2000-10-14    1  57.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Converting week variables from messy strings to integers\n",
    "for row in melted.iterrows():\n",
    "    index = row[0]\n",
    "    week_str = row[1][6]\n",
    "    week_int = int(re.sub('[^0-9]', '', week_str))\n",
    "    melted.loc[index, 'Week'] = week_int\n",
    "\n",
    "melted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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></th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>artist.inverted</th>\n",
       "      <th>track</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Santana</th>\n",
       "      <th>Maria, Maria</th>\n",
       "      <td>10.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Madonna</th>\n",
       "      <th>Music</th>\n",
       "      <td>13.458333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N'Sync</th>\n",
       "      <th>Bye Bye Bye</th>\n",
       "      <td>14.260870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elliott, Missy \"Misdemeanor\"</th>\n",
       "      <th>Hot Boyz</th>\n",
       "      <td>14.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Destiny's Child</th>\n",
       "      <th>Independent Women Part I</th>\n",
       "      <td>14.821429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iglesias, Enrique</th>\n",
       "      <th>Be With You</th>\n",
       "      <td>15.850000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaliyah</th>\n",
       "      <th>Try Again</th>\n",
       "      <td>16.656250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Savage Garden</th>\n",
       "      <th>I Knew I Loved You</th>\n",
       "      <td>17.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston, Whitney</th>\n",
       "      <th>My Love Is Your Love</th>\n",
       "      <td>17.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pink</th>\n",
       "      <th>There U Go</th>\n",
       "      <td>18.625000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            Rank\n",
       "artist.inverted              track                              \n",
       "Santana                      Maria, Maria              10.500000\n",
       "Madonna                      Music                     13.458333\n",
       "N'Sync                       Bye Bye Bye               14.260870\n",
       "Elliott, Missy \"Misdemeanor\" Hot Boyz                  14.333333\n",
       "Destiny's Child              Independent Women Part I  14.821429\n",
       "Iglesias, Enrique            Be With You               15.850000\n",
       "Aaliyah                      Try Again                 16.656250\n",
       "Savage Garden                I Knew I Loved You        17.363636\n",
       "Houston, Whitney             My Love Is Your Love      17.857143\n",
       "Pink                         There U Go                18.625000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouping the rows by \"artist.inverted\" and \"track\", and computing the average rank for each artist,\n",
    "# Then sorting the resulting dataframe from lowest to highest average rank and displaying the top 10 artists\n",
    "stats = melted.groupby(['artist.inverted', 'track']).agg(np.mean).sort_values(by='Rank', ascending=True)\n",
    "stats.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Conclusion:</b> Out of all the great artists of the year 2000, Santana held the highest average billboard rank, followed by Madonna and N'Sync! "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "9e13d24989e9aa84deaafec43a9be4b3b9f46f3756b11886b1eace5ab5637549"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}