{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Purchase ID</th>\n",
       "      <th>SN</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Item Name</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Lisim78</td>\n",
       "      <td>20</td>\n",
       "      <td>Male</td>\n",
       "      <td>108</td>\n",
       "      <td>Extraction, Quickblade Of Trembling Hands</td>\n",
       "      <td>3.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Lisovynya38</td>\n",
       "      <td>40</td>\n",
       "      <td>Male</td>\n",
       "      <td>143</td>\n",
       "      <td>Frenzied Scimitar</td>\n",
       "      <td>1.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Ithergue48</td>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>92</td>\n",
       "      <td>Final Critic</td>\n",
       "      <td>4.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Chamassasya86</td>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>100</td>\n",
       "      <td>Blindscythe</td>\n",
       "      <td>3.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Iskosia90</td>\n",
       "      <td>23</td>\n",
       "      <td>Male</td>\n",
       "      <td>131</td>\n",
       "      <td>Fury</td>\n",
       "      <td>1.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Purchase ID             SN  Age Gender  Item ID  \\\n",
       "0            0        Lisim78   20   Male      108   \n",
       "1            1    Lisovynya38   40   Male      143   \n",
       "2            2     Ithergue48   24   Male       92   \n",
       "3            3  Chamassasya86   24   Male      100   \n",
       "4            4      Iskosia90   23   Male      131   \n",
       "\n",
       "                                   Item Name  Price  \n",
       "0  Extraction, Quickblade Of Trembling Hands   3.53  \n",
       "1                          Frenzied Scimitar   1.56  \n",
       "2                               Final Critic   4.88  \n",
       "3                                Blindscythe   3.27  \n",
       "4                                       Fury   1.44  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "data_file = \"Resources/purchase_data.csv\"\n",
    "purchases_df = pd.read_csv(data_file)\n",
    "purchases_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Purchase ID    0\n",
       "SN             0\n",
       "Age            0\n",
       "Gender         0\n",
       "Item ID        0\n",
       "Item Name      0\n",
       "Price          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checking for any NULL values\n",
    "purchases_df.isnull().sum()  #No NULLS in file, yay!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "576"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get unique names of players, in the SN column\n",
    "gamers_count = purchases_df[\"SN\"].nunique()\n",
    "\n",
    "gamers_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "780"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get average purchase price from the Price column, count lines first\n",
    "purchase_num = purchases_df[\"SN\"].count()\n",
    "purchase_num       #Setting a variable for it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.050987179487176"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_ave = purchases_df[\"Price\"].mean() #this give me the average\n",
    "purchase_ave"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2379.77"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_rev = purchases_df[\"Price\"].sum() #this gives me the total of the purchases, by adding the Price column\n",
    "total_rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>No. unique items</th>\n",
       "      <th>Average price</th>\n",
       "      <th>No. of purchases</th>\n",
       "      <th>Total revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576</td>\n",
       "      <td>3.050987</td>\n",
       "      <td>780</td>\n",
       "      <td>2379.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   No. unique items  Average price  No. of purchases  Total revenue\n",
       "0               576       3.050987               780        2379.77"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_analysis = {\"No. unique items\": [gamers_count],\n",
    "                     \"Average price\": [purchase_ave],\n",
    "                     \"No. of purchases\": [purchase_num],\n",
    "                     \"Total revenue\": [total_rev]}\n",
    "purchase_analysis_df = pd.DataFrame(purchase_analysis, columns=[\"No. unique items\", \"Average price\",\n",
    "                                                                \"No. of purchases\", \"Total revenue\"])\n",
    "purchase_analysis_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Price_count</th>\n",
       "      <th>Price_sum</th>\n",
       "      <th>Price_mean</th>\n",
       "      <th>SN_unique</th>\n",
       "      <th>Unique users</th>\n",
       "      <th>Users %</th>\n",
       "      <th>Ave. $ per person</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>113</td>\n",
       "      <td>$361.94</td>\n",
       "      <td>$3.20</td>\n",
       "      <td>[Lisassa64, Reunasu60, Reulae52, Assosia88, Ph...</td>\n",
       "      <td>81</td>\n",
       "      <td>14.06</td>\n",
       "      <td>$4.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>652</td>\n",
       "      <td>$1,967.64</td>\n",
       "      <td>$3.02</td>\n",
       "      <td>[Lisim78, Lisovynya38, Ithergue48, Chamassasya...</td>\n",
       "      <td>484</td>\n",
       "      <td>84.03</td>\n",
       "      <td>$4.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other / Non-Disclosed</th>\n",
       "      <td>15</td>\n",
       "      <td>$50.19</td>\n",
       "      <td>$3.35</td>\n",
       "      <td>[Chanosian48, Siarithria38, Haerithp41, Sundim...</td>\n",
       "      <td>11</td>\n",
       "      <td>1.91</td>\n",
       "      <td>$4.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Price_count  Price_sum Price_mean  \\\n",
       "Gender                                                     \n",
       "Female                         113    $361.94      $3.20   \n",
       "Male                           652  $1,967.64      $3.02   \n",
       "Other / Non-Disclosed           15     $50.19      $3.35   \n",
       "\n",
       "                                                               SN_unique  \\\n",
       "Gender                                                                     \n",
       "Female                 [Lisassa64, Reunasu60, Reulae52, Assosia88, Ph...   \n",
       "Male                   [Lisim78, Lisovynya38, Ithergue48, Chamassasya...   \n",
       "Other / Non-Disclosed  [Chanosian48, Siarithria38, Haerithp41, Sundim...   \n",
       "\n",
       "                       Unique users  Users % Ave. $ per person  \n",
       "Gender                                                          \n",
       "Female                           81    14.06             $4.47  \n",
       "Male                            484    84.03             $4.07  \n",
       "Other / Non-Disclosed            11     1.91             $4.56  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting the count, sum, an average figures by gender. Need to group by, then use the aggregation function to apply built in \n",
    "#functions to multiple columns. Once I group by and aggregate, the dataframe will be a multilevel one, so I need to apply the\n",
    "#join function to access the data in a single level in order to apply other functions to the values in each column\n",
    "\n",
    "purchases_df.groupby(\"Gender\").agg({\"Price\":[\"count\", \"sum\", \"mean\"], \"SN\": \"unique\"})\n",
    "gender_df = purchases_df.groupby(\"Gender\").agg({\"Price\":[\"count\", \"sum\", \"mean\"], \"SN\": \"unique\"})\n",
    "#gender_df.columns.map(\"_\".join)\n",
    "gender_df.columns = gender_df.columns.map(\"_\".join)\n",
    "gender_df[\"Unique users\"] = gender_df[\"SN_unique\"].apply(len)\n",
    "gender_df[\"Users %\"] = round((gender_df[\"Unique users\"] / gamers_count)*100,2)\n",
    "gender_df[\"Ave. $ per person\"] = gender_df[\"Price_sum\"] / gender_df[\"Unique users\"]\n",
    "\n",
    "#Applying formatting to columns\n",
    "gender_df[\"Price_mean\"] = gender_df[\"Price_mean\"].map(\"${:,.2f}\".format)\n",
    "gender_df[\"Price_sum\"] = gender_df[\"Price_sum\"].map(\"${:,.2f}\".format)\n",
    "gender_df[\"Ave. $ per person\"] = gender_df[\"Ave. $ per person\"].map(\"${:,.2f}\".format)\n",
    "gender_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    20-24\n",
       "1      40+\n",
       "2    20-24\n",
       "3    20-24\n",
       "4    20-24\n",
       "Name: Age, dtype: category\n",
       "Categories (8, object): [>10 < 10-14 < 15-19 < 20-24 < 25-29 < 30-34 < 35-39 < 40+]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create bins for the age groups\n",
    "bins= [0,9,14,19,24,29,34,39,200]\n",
    "group_labels = [\">10\", \"10-14\", \"15-19\", \"20-24\", \"25-29\", \"30-34\", \"35-39\", \"40+\"]\n",
    "pd.cut(purchases_df[\"Age\"], bins, labels=group_labels).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Purchase ID</th>\n",
       "      <th>SN</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Item Name</th>\n",
       "      <th>Price</th>\n",
       "      <th>Age group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Lisim78</td>\n",
       "      <td>20</td>\n",
       "      <td>Male</td>\n",
       "      <td>108</td>\n",
       "      <td>Extraction, Quickblade Of Trembling Hands</td>\n",
       "      <td>3.53</td>\n",
       "      <td>20-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Lisovynya38</td>\n",
       "      <td>40</td>\n",
       "      <td>Male</td>\n",
       "      <td>143</td>\n",
       "      <td>Frenzied Scimitar</td>\n",
       "      <td>1.56</td>\n",
       "      <td>40+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Ithergue48</td>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>92</td>\n",
       "      <td>Final Critic</td>\n",
       "      <td>4.88</td>\n",
       "      <td>20-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Chamassasya86</td>\n",
       "      <td>24</td>\n",
       "      <td>Male</td>\n",
       "      <td>100</td>\n",
       "      <td>Blindscythe</td>\n",
       "      <td>3.27</td>\n",
       "      <td>20-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Iskosia90</td>\n",
       "      <td>23</td>\n",
       "      <td>Male</td>\n",
       "      <td>131</td>\n",
       "      <td>Fury</td>\n",
       "      <td>1.44</td>\n",
       "      <td>20-24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Purchase ID             SN  Age Gender  Item ID  \\\n",
       "0            0        Lisim78   20   Male      108   \n",
       "1            1    Lisovynya38   40   Male      143   \n",
       "2            2     Ithergue48   24   Male       92   \n",
       "3            3  Chamassasya86   24   Male      100   \n",
       "4            4      Iskosia90   23   Male      131   \n",
       "\n",
       "                                   Item Name  Price Age group  \n",
       "0  Extraction, Quickblade Of Trembling Hands   3.53     20-24  \n",
       "1                          Frenzied Scimitar   1.56       40+  \n",
       "2                               Final Critic   4.88     20-24  \n",
       "3                                Blindscythe   3.27     20-24  \n",
       "4                                       Fury   1.44     20-24  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchases_df[\"Age group\"]= pd.cut(purchases_df[\"Age\"], bins, labels=group_labels)\n",
    "purchases_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>Price_count</th>\n",
       "      <th>Price_sum</th>\n",
       "      <th>Price_mean</th>\n",
       "      <th>SN_unique</th>\n",
       "      <th>Unique users</th>\n",
       "      <th>Ave. $ per person</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Age group</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>&gt;10</th>\n",
       "      <td>23</td>\n",
       "      <td>$77.13</td>\n",
       "      <td>$3.35</td>\n",
       "      <td>[Eusri44, Haillyrgue51, Seuthep89, Heudai45, T...</td>\n",
       "      <td>17</td>\n",
       "      <td>$4.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>28</td>\n",
       "      <td>$82.78</td>\n",
       "      <td>$2.96</td>\n",
       "      <td>[Lirtossa84, Aeralria27, Reulae52, Zhisrisu83,...</td>\n",
       "      <td>22</td>\n",
       "      <td>$3.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>136</td>\n",
       "      <td>$412.89</td>\n",
       "      <td>$3.04</td>\n",
       "      <td>[Idai61, Sondim73, Aidaillodeu39, Yaliru88, Ji...</td>\n",
       "      <td>107</td>\n",
       "      <td>$3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>365</td>\n",
       "      <td>$1,114.06</td>\n",
       "      <td>$3.05</td>\n",
       "      <td>[Lisim78, Ithergue48, Chamassasya86, Iskosia90...</td>\n",
       "      <td>258</td>\n",
       "      <td>$4.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-29</th>\n",
       "      <td>101</td>\n",
       "      <td>$293.00</td>\n",
       "      <td>$2.90</td>\n",
       "      <td>[Lisirra87, Yasrisu92, Phaelap26, Chamirraya83...</td>\n",
       "      <td>77</td>\n",
       "      <td>$3.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>73</td>\n",
       "      <td>$214.00</td>\n",
       "      <td>$2.93</td>\n",
       "      <td>[Chamalo71, Siala43, Idaisuir85, Iaralrgue74, ...</td>\n",
       "      <td>52</td>\n",
       "      <td>$4.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-39</th>\n",
       "      <td>41</td>\n",
       "      <td>$147.67</td>\n",
       "      <td>$3.60</td>\n",
       "      <td>[Itheria73, Chanosian48, Saesrideu94, Siarithr...</td>\n",
       "      <td>31</td>\n",
       "      <td>$4.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40+</th>\n",
       "      <td>13</td>\n",
       "      <td>$38.24</td>\n",
       "      <td>$2.94</td>\n",
       "      <td>[Lisovynya38, Eyrian71, Jiskjask85, Isursuir31...</td>\n",
       "      <td>12</td>\n",
       "      <td>$3.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Price_count  Price_sum Price_mean  \\\n",
       "Age group                                      \n",
       ">10                 23     $77.13      $3.35   \n",
       "10-14               28     $82.78      $2.96   \n",
       "15-19              136    $412.89      $3.04   \n",
       "20-24              365  $1,114.06      $3.05   \n",
       "25-29              101    $293.00      $2.90   \n",
       "30-34               73    $214.00      $2.93   \n",
       "35-39               41    $147.67      $3.60   \n",
       "40+                 13     $38.24      $2.94   \n",
       "\n",
       "                                                   SN_unique  Unique users  \\\n",
       "Age group                                                                    \n",
       ">10        [Eusri44, Haillyrgue51, Seuthep89, Heudai45, T...            17   \n",
       "10-14      [Lirtossa84, Aeralria27, Reulae52, Zhisrisu83,...            22   \n",
       "15-19      [Idai61, Sondim73, Aidaillodeu39, Yaliru88, Ji...           107   \n",
       "20-24      [Lisim78, Ithergue48, Chamassasya86, Iskosia90...           258   \n",
       "25-29      [Lisirra87, Yasrisu92, Phaelap26, Chamirraya83...            77   \n",
       "30-34      [Chamalo71, Siala43, Idaisuir85, Iaralrgue74, ...            52   \n",
       "35-39      [Itheria73, Chanosian48, Saesrideu94, Siarithr...            31   \n",
       "40+        [Lisovynya38, Eyrian71, Jiskjask85, Isursuir31...            12   \n",
       "\n",
       "          Ave. $ per person  \n",
       "Age group                    \n",
       ">10                   $4.54  \n",
       "10-14                 $3.76  \n",
       "15-19                 $3.86  \n",
       "20-24                 $4.32  \n",
       "25-29                 $3.81  \n",
       "30-34                 $4.12  \n",
       "35-39                 $4.76  \n",
       "40+                   $3.19  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Groupping by Age groups now to get the count, sum, and mean values by ages to calculate average purchase per person,\n",
    "#and the count of users under each age group. Once again, I will need to join or flatten the levels in the dataframe\n",
    "#in order to access the values in different columns and apply functions to them.\n",
    "\n",
    "purchases_df.groupby(\"Age group\").agg({\"Price\": [\"count\", \"sum\", \"mean\"], \"SN\": \"unique\"})\n",
    "groups_df = purchases_df.groupby(\"Age group\").agg({\"Price\": [\"count\", \"sum\", \"mean\"], \"SN\": \"unique\"})\n",
    "groups_df.columns.map(\"_\".join)\n",
    "groups_df.columns = groups_df.columns.map(\"_\".join)\n",
    "groups_df[\"Unique users\"] = groups_df[\"SN_unique\"].apply(len)\n",
    "groups_df[\"Ave. $ per person\"] = groups_df[\"Price_sum\"] / groups_df[\"Unique users\"]\n",
    "groups_df\n",
    "\n",
    "#Formatting values in columns\n",
    "groups_df[\"Price_sum\"] = groups_df[\"Price_sum\"].map(\"${:,.2f}\".format)\n",
    "groups_df[\"Price_mean\"] = groups_df[\"Price_mean\"].map(\"${:,.2f}\".format)\n",
    "groups_df[\"Ave. $ per person\"] = groups_df[\"Ave. $ per person\"].map(\"${:,.2f}\".format)\n",
    "groups_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>Price_count</th>\n",
       "      <th>Price_sum</th>\n",
       "      <th>Price_mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SN</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lisosia93</th>\n",
       "      <td>5</td>\n",
       "      <td>$18.96</td>\n",
       "      <td>$3.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idastidru52</th>\n",
       "      <td>4</td>\n",
       "      <td>$15.45</td>\n",
       "      <td>$3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chamjask73</th>\n",
       "      <td>3</td>\n",
       "      <td>$13.83</td>\n",
       "      <td>$4.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iral74</th>\n",
       "      <td>4</td>\n",
       "      <td>$13.62</td>\n",
       "      <td>$3.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iskadarya95</th>\n",
       "      <td>3</td>\n",
       "      <td>$13.10</td>\n",
       "      <td>$4.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Price_count Price_sum Price_mean\n",
       "SN                                           \n",
       "Lisosia93              5    $18.96      $3.79\n",
       "Idastidru52            4    $15.45      $3.86\n",
       "Chamjask73             3    $13.83      $4.61\n",
       "Iral74                 4    $13.62      $3.40\n",
       "Iskadarya95            3    $13.10      $4.37"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Groupping by users to get total purchases by person and sort in descending order to identify the top 5 spenders.\n",
    "purchases_df.groupby(\"SN\").agg({\"Price\": [\"count\", \"sum\", \"mean\"]})\n",
    "spenders_df = purchases_df.groupby(\"SN\").agg({\"Price\": [\"count\", \"sum\", \"mean\"]})\n",
    "spenders_df\n",
    "#joining attributes in a single level\n",
    "spenders_df.columns.map(\"_\".join)\n",
    "spenders_df.columns = spenders_df.columns.map(\"_\".join)\n",
    "spenders_df = spenders_df.sort_values(\"Price_sum\", ascending=False)\n",
    "#Formatting columns \n",
    "spenders_df[\"Price_sum\"] = spenders_df[\"Price_sum\"].map(\"${:,.2f}\".format)\n",
    "spenders_df[\"Price_mean\"] = spenders_df[\"Price_mean\"].map(\"${:,.2f}\".format)\n",
    "spenders_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Price_count</th>\n",
       "      <th>Price_sum</th>\n",
       "      <th>Price_mean</th>\n",
       "      <th>Item ID_unique</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Final Critic</th>\n",
       "      <td>13</td>\n",
       "      <td>$59.99</td>\n",
       "      <td>$4.61</td>\n",
       "      <td>[92, 101]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oathbreaker, Last Hope of the Breaking Storm</th>\n",
       "      <td>12</td>\n",
       "      <td>$50.76</td>\n",
       "      <td>$4.23</td>\n",
       "      <td>[178]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Persuasion</th>\n",
       "      <td>9</td>\n",
       "      <td>$28.99</td>\n",
       "      <td>$3.22</td>\n",
       "      <td>[141, 132]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nirvana</th>\n",
       "      <td>9</td>\n",
       "      <td>$44.10</td>\n",
       "      <td>$4.90</td>\n",
       "      <td>[82]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Extraction, Quickblade Of Trembling Hands</th>\n",
       "      <td>9</td>\n",
       "      <td>$31.77</td>\n",
       "      <td>$3.53</td>\n",
       "      <td>[108]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Price_count Price_sum  \\\n",
       "Item Name                                                             \n",
       "Final Critic                                           13    $59.99   \n",
       "Oathbreaker, Last Hope of the Breaking Storm           12    $50.76   \n",
       "Persuasion                                              9    $28.99   \n",
       "Nirvana                                                 9    $44.10   \n",
       "Extraction, Quickblade Of Trembling Hands               9    $31.77   \n",
       "\n",
       "                                             Price_mean Item ID_unique  \n",
       "Item Name                                                               \n",
       "Final Critic                                      $4.61      [92, 101]  \n",
       "Oathbreaker, Last Hope of the Breaking Storm      $4.23          [178]  \n",
       "Persuasion                                        $3.22     [141, 132]  \n",
       "Nirvana                                           $4.90           [82]  \n",
       "Extraction, Quickblade Of Trembling Hands         $3.53          [108]  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Groupping by items at last, to find the 5 top most sold items. Following the same dynamics, I'll group by item, applying \n",
    "#functions to columns, then joining if multilevel and add other columns/calculations if needed\n",
    "purchases_df.groupby(\"Item Name\").agg({\"Price\":[\"count\", \"sum\", \"mean\"], \"Item ID\": \"unique\"})\n",
    "most_sold = purchases_df.groupby(\"Item Name\").agg({\"Price\":[\"count\", \"sum\", \"mean\"], \"Item ID\": \"unique\"})\n",
    "most_sold.columns.map(\"_\".join)\n",
    "most_sold.columns = most_sold.columns.map(\"_\".join)\n",
    "most_sold = most_sold.sort_values(\"Price_count\", ascending=False)\n",
    "\n",
    "#Formatting columns as needed\n",
    "most_sold[\"Price_sum\"] = most_sold[\"Price_sum\"].map(\"${:,.2f}\".format)\n",
    "most_sold[\"Price_mean\"] = most_sold[\"Price_mean\"].map(\"${:,.2f}\".format)\n",
    "\n",
    "most_sold.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Conda [PythonData]",
   "language": "python",
   "name": "pythondata"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
