HeroesOfPymoli.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Heroes Of Pymoli Data Analysis\n",
    "* Of the 1163 active players, the vast majority are male (84%). There also exists, a smaller, but notable proportion of female players (14%).\n",
    "\n",
    "* Our peak age demographic falls between 20-24 (44.8%) with secondary groups falling between 15-19 (18.60%) and 25-29 (13.4%).  \n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "* Instructions have been included for each segment. You do not have to follow them exactly, but they are included to help you think through the steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "\n",
    "# File to Load \n",
    "file_to_load = \"Resources/purchase_data.csv\"\n",
    "\n",
    "# Read Purchasing File and store into Pandas data frame\n",
    "purchase_data = pd.read_csv(file_to_load)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Player Count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Display the total number of players\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_data.head()"
   ]
  },
  {
   "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>Total Players</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Players\n",
       "0            576"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_players = len(purchase_data[\"SN\"].unique())\n",
    "player_count = pd.DataFrame({\"Total Players\":[total_players]})\n",
    "player_count\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Purchasing Analysis (Total)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Run basic calculations to obtain number of unique items, average price, etc.\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display the summary data frame\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style><table id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >Number of unique items</th>        <th class=\"col_heading level0 col1\" >Average price</th>        <th class=\"col_heading level0 col2\" >Number of purchases</th>        <th class=\"col_heading level0 col3\" >Total Revenue</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
       "                        <td id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00row0_col0\" class=\"data row0 col0\" >179</td>\n",
       "                        <td id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00row0_col1\" class=\"data row0 col1\" >$3.05</td>\n",
       "                        <td id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00row0_col2\" class=\"data row0 col2\" >780</td>\n",
       "                        <td id=\"T_eb130af2_a9aa_11ea_8301_8213200b2a00row0_col3\" class=\"data row0 col3\" >$2,379.77</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x1191a5d10>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "number_of_unique_items = len(purchase_data[\"Item ID\"].unique())\n",
    "average_price = purchase_data[\"Price\"].mean()\n",
    "number_of_purchases = purchase_data[\"Purchase ID\"].count()\n",
    "total_revenue = purchase_data[\"Price\"].sum()\n",
    "\n",
    "summary_df = pd.DataFrame({\"Number of unique items\":[number_of_unique_items],\n",
    "                          \"Average price\":[average_price],\n",
    "                          \"Number of purchases\":[number_of_purchases],\n",
    "                          \"Total Revenue\":[total_revenue]})\n",
    "summary_df.style.format({'Average price':\"${:,.2f}\",\n",
    "                         'Total Revenue':\"${:,.2f}\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gender Demographics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "* Percentage and Count of Male Players\n",
    "\n",
    "\n",
    "* Percentage and Count of Female Players\n",
    "\n",
    "\n",
    "* Percentage and Count of Other / Non-Disclosed\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Percentage of Players</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>14.06%</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>84.03%</td>\n",
       "      <td>484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other / Non-Disclosed</th>\n",
       "      <td>1.91%</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Percentage of Players  Count\n",
       "Gender                                            \n",
       "Female                               14.06%     81\n",
       "Male                                 84.03%    484\n",
       "Other / Non-Disclosed                 1.91%     11"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_df = purchase_data.groupby([\"Gender\"])\n",
    "unique_df = all_df.nunique()\n",
    "total_gender = unique_df[\"SN\"].sum()\n",
    "count = unique_df[\"SN\"].unique()\n",
    "percentage = unique_df[\"SN\"]/ total_gender\n",
    "final_gender = pd.DataFrame({\"Percentage of Players\": percentage,\n",
    "                            \"Count\":count})\n",
    "final_gender[\"Percentage of Players\"] = final_gender[\"Percentage of Players\"].map(\"{:,.2%}\".format) \n",
    "\n",
    "final_gender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Purchasing Analysis (Gender)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Run basic calculations to obtain purchase count, avg. purchase price, avg. purchase total per person etc. by gender\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display the summary data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "gender_type = purchase_data.groupby(\"Gender\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "female = purchase_data.groupby([\"Gender\", \"SN\"]).sum().loc[\"Female\"][\"Price\"].mean()\n",
    "male = purchase_data.groupby([\"Gender\", \"SN\"]).sum().loc[\"Male\"][\"Price\"].mean()\n",
    "other = purchase_data.groupby([\"Gender\", \"SN\"]).sum().loc[\"Other / Non-Disclosed\"][\"Price\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_gender = pd.Series([female ,male ,other])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "diff_gender = gender_type.nunique()[\"Purchase ID\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_price = gender_type.mean()[\"Price\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_val = gender_type.sum()[\"Price\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_gender.index = diff_gender.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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 Count</th>\n",
       "      <th>Average Purchase Price</th>\n",
       "      <th>Total Purchase Value</th>\n",
       "      <th>Avg Total Purchase per Person</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gender</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>$3.20</td>\n",
       "      <td>$361.94</td>\n",
       "      <td>$4.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Male</th>\n",
       "      <td>652</td>\n",
       "      <td>$3.02</td>\n",
       "      <td>$1,967.64</td>\n",
       "      <td>$4.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other / Non-Disclosed</th>\n",
       "      <td>15</td>\n",
       "      <td>$3.35</td>\n",
       "      <td>$50.19</td>\n",
       "      <td>$4.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Purchase Count Average Purchase Price  \\\n",
       "Gender                                                         \n",
       "Female                            113                  $3.20   \n",
       "Male                              652                  $3.02   \n",
       "Other / Non-Disclosed              15                  $3.35   \n",
       "\n",
       "                      Total Purchase Value Avg Total Purchase per Person  \n",
       "Gender                                                                    \n",
       "Female                             $361.94                         $4.47  \n",
       "Male                             $1,967.64                         $4.07  \n",
       "Other / Non-Disclosed               $50.19                         $4.56  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gender_demographic = pd.DataFrame()\n",
    "gender_demographic[\"Purchase Count\"] = diff_gender\n",
    "gender_demographic[\"Average Purchase Price\"] = avg_price\n",
    "gender_demographic[\"Total Purchase Value\"] = total_val\n",
    "gender_demographic[\"Avg Total Purchase per Person\"] = all_gender\n",
    "    \n",
    "gender_demographic[\"Average Purchase Price\"] = gender_demographic[\"Average Purchase Price\"].map(\"${:,.2f}\".format)\n",
    "gender_demographic[\"Total Purchase Value\"] = gender_demographic[\"Total Purchase Value\"].map(\"${:,.2f}\".format) \n",
    "gender_demographic[\"Avg Total Purchase per Person\"] = gender_demographic[\"Avg Total Purchase per Person\"].map(\"${:,.2f}\".format) \n",
    "                       \n",
    "gender_demographic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age Demographics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Establish bins for ages\n",
    "\n",
    "\n",
    "* Categorize the existing players using the age bins. Hint: use pd.cut()\n",
    "\n",
    "\n",
    "* Calculate the numbers and percentages by age group\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Optional: round the percentage column to two decimal points\n",
    "\n",
    "\n",
    "* Display Age Demographics Table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = [0,10,15,20,25,30,35,40,150]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "category = pd.cut(purchase_data[\"Age\"], bins,right = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "purchase_data['cats'] = category.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "uniques = purchase_data.groupby(['cats']).nunique()['SN']\n",
    "percents = purchase_data.groupby(['cats']).nunique()['SN'] *100 / total_players\n",
    "age_index = ['<10','10-14','15-19','20-24','25-29','30-34','35-40','40+']\n",
    "age_analysis = pd.DataFrame()\n",
    "age_analysis['Total Count'] = uniques\n",
    "age_analysis['Percentage of Players'] = percents\n",
    "# Data Formatting\n",
    "age_analysis[\"Percentage of Players\"] = age_analysis[\"Percentage of Players\"].map(\"{:,.2%}\".format) \n",
    "age_analysis.index = age_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total Count</th>\n",
       "      <th>Percentage of Players</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>&lt;10</th>\n",
       "      <td>17</td>\n",
       "      <td>295.14%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>22</td>\n",
       "      <td>381.94%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>107</td>\n",
       "      <td>1,857.64%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>258</td>\n",
       "      <td>4,479.17%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-29</th>\n",
       "      <td>77</td>\n",
       "      <td>1,336.81%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>52</td>\n",
       "      <td>902.78%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>31</td>\n",
       "      <td>538.19%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40+</th>\n",
       "      <td>12</td>\n",
       "      <td>208.33%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Total Count Percentage of Players\n",
       "<10             17               295.14%\n",
       "10-14           22               381.94%\n",
       "15-19          107             1,857.64%\n",
       "20-24          258             4,479.17%\n",
       "25-29           77             1,336.81%\n",
       "30-34           52               902.78%\n",
       "35-40           31               538.19%\n",
       "40+             12               208.33%"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Purchasing Analysis (Age)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Bin the purchase_data data frame by age\n",
    "\n",
    "\n",
    "* Run basic calculations to obtain purchase count, avg. purchase price, avg. purchase total per person etc. in the table below\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display the summary data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "purchase_count = purchase_data.groupby([\"cats\"]).count()['Purchase ID']\n",
    "avg_purchase_price = purchase_data.groupby([\"cats\"]).mean()['Price']\n",
    "total_purchase_price = purchase_data.groupby([\"cats\"]).sum()['Price']\n",
    "avg_total_per_person = purchase_data.groupby([\"SN\"]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_total_per_person['cats'] = pd.cut(avg_total_per_person['Age'],bins,right = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_total_per_person = avg_total_per_person.groupby(['cats']).mean()['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "purchase_age_analysis = pd.DataFrame()\n",
    "purchase_age_analysis['Purchase Count'] = purchase_count\n",
    "purchase_age_analysis['Average Purchase Price'] = avg_purchase_price \n",
    "purchase_age_analysis['Total Purchase Price'] = total_purchase_price\n",
    "purchase_age_analysis['Average Total Per Person'] = avg_total_per_person\n",
    "# Data Formatting\n",
    "purchase_age_analysis[\"Average Purchase Price\"] = purchase_age_analysis[\"Average Purchase Price\"].map(\"${:,.2f}\".format)\n",
    "purchase_age_analysis[\"Total Purchase Price\"] = purchase_age_analysis[\"Total Purchase Price\"].map(\"${:,.2f}\".format)\n",
    "purchase_age_analysis[\"Average Total Per Person\"] = purchase_age_analysis[\"Average Total Per Person\"].map(\"${:,.2f}\".format)\n",
    "\n",
    "\n",
    "\n",
    "purchase_age_analysis.index = age_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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 Count</th>\n",
       "      <th>Average Purchase Price</th>\n",
       "      <th>Total Purchase Price</th>\n",
       "      <th>Average Total Per Person</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>&lt;10</th>\n",
       "      <td>23</td>\n",
       "      <td>$3.35</td>\n",
       "      <td>$77.13</td>\n",
       "      <td>$3.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>28</td>\n",
       "      <td>$2.96</td>\n",
       "      <td>$82.78</td>\n",
       "      <td>$3.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>136</td>\n",
       "      <td>$3.04</td>\n",
       "      <td>$412.89</td>\n",
       "      <td>$3.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>365</td>\n",
       "      <td>$3.05</td>\n",
       "      <td>$1,114.06</td>\n",
       "      <td>$3.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-29</th>\n",
       "      <td>101</td>\n",
       "      <td>$2.90</td>\n",
       "      <td>$293.00</td>\n",
       "      <td>$2.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>73</td>\n",
       "      <td>$2.93</td>\n",
       "      <td>$214.00</td>\n",
       "      <td>$3.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>41</td>\n",
       "      <td>$3.60</td>\n",
       "      <td>$147.67</td>\n",
       "      <td>$4.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40+</th>\n",
       "      <td>13</td>\n",
       "      <td>$2.94</td>\n",
       "      <td>$38.24</td>\n",
       "      <td>$6.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Purchase Count Average Purchase Price Total Purchase Price  \\\n",
       "<10                23                  $3.35               $77.13   \n",
       "10-14              28                  $2.96               $82.78   \n",
       "15-19             136                  $3.04              $412.89   \n",
       "20-24             365                  $3.05            $1,114.06   \n",
       "25-29             101                  $2.90              $293.00   \n",
       "30-34              73                  $2.93              $214.00   \n",
       "35-40              41                  $3.60              $147.67   \n",
       "40+                13                  $2.94               $38.24   \n",
       "\n",
       "      Average Total Per Person  \n",
       "<10                      $3.73  \n",
       "10-14                    $3.17  \n",
       "15-19                    $3.23  \n",
       "20-24                    $3.11  \n",
       "25-29                    $2.77  \n",
       "30-34                    $3.48  \n",
       "35-40                    $4.07  \n",
       "40+                      $6.76  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_age_analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Top Spenders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Run basic calculations to obtain the results in the table below\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Sort the total purchase value column in descending order\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display a preview of the summary data frame\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_spenders = purchase_data.groupby('SN').sum()['Price'].sort_values(ascending = False)[:5]\n",
    "top_spenders_average = purchase_data.groupby('SN').mean()['Price'].loc[top_spenders.index]\n",
    "purchase_count = purchase_data.groupby('SN').mean()['Price'].loc[top_spenders.index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "spenders = pd.DataFrame()\n",
    "spenders['Purchase Count'] = purchase_count\n",
    "spenders['Average Purchase Price'] = top_spenders_average\n",
    "spenders['Total Purchase Price'] = top_spenders\n",
    "\n",
    "# Data Formatting\n",
    "spenders[\"Purchase Count\"] = spenders[\"Purchase Count\"].map(\"${:,.2f}\".format)\n",
    "spenders[\"Average Purchase Price\"] = spenders[\"Average Purchase Price\"].map(\"${:,.2f}\".format)\n",
    "spenders[\"Total Purchase Price\"] = spenders[\"Total Purchase Price\"].map(\"${:,.2f}\".format)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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 Count</th>\n",
       "      <th>Average Purchase Price</th>\n",
       "      <th>Total Purchase Price</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>$3.79</td>\n",
       "      <td>$3.79</td>\n",
       "      <td>$18.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idastidru52</th>\n",
       "      <td>$3.86</td>\n",
       "      <td>$3.86</td>\n",
       "      <td>$15.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chamjask73</th>\n",
       "      <td>$4.61</td>\n",
       "      <td>$4.61</td>\n",
       "      <td>$13.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iral74</th>\n",
       "      <td>$3.40</td>\n",
       "      <td>$3.40</td>\n",
       "      <td>$13.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iskadarya95</th>\n",
       "      <td>$4.37</td>\n",
       "      <td>$4.37</td>\n",
       "      <td>$13.10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Purchase Count Average Purchase Price Total Purchase Price\n",
       "SN                                                                    \n",
       "Lisosia93            $3.79                  $3.79               $18.96\n",
       "Idastidru52          $3.86                  $3.86               $15.45\n",
       "Chamjask73           $4.61                  $4.61               $13.83\n",
       "Iral74               $3.40                  $3.40               $13.62\n",
       "Iskadarya95          $4.37                  $4.37               $13.10"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spenders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Most Popular Items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Retrieve the Item ID, Item Name, and Item Price columns\n",
    "\n",
    "\n",
    "* Group by Item ID and Item Name. Perform calculations to obtain purchase count, item price, and total purchase value\n",
    "\n",
    "\n",
    "* Create a summary data frame to hold the results\n",
    "\n",
    "\n",
    "* Sort the purchase count column in descending order\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display a preview of the summary data frame\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "values = purchase_data.groupby('Item ID').agg({'Purchase ID':['count'],'Price':['max','sum']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "values.columns = ['Purchase Count','Item Price','Total Purchase']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "popular_items=values.sort_values(by = ['Purchase Count'],ascending = False)[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "item_map = purchase_data[['Item ID','Item Name']]\n",
    "item_map.index = item_map['Item ID']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "popular_items['Item Names'] = item_map.loc[popular_items.index].drop_duplicates()['Item Name']\n",
    "popular_items[\"Item Price\"] = popular_items[\"Item Price\"].map(\"${:,.2f}\".format)\n",
    "popular_items[\"Total Purchase\"] = popular_items[\"Total Purchase\"].map(\"${:,.2f}\".format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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 Count</th>\n",
       "      <th>Item Price</th>\n",
       "      <th>Total Purchase</th>\n",
       "      <th>Item Names</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>13</td>\n",
       "      <td>$4.88</td>\n",
       "      <td>$59.99</td>\n",
       "      <td>Final Critic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>12</td>\n",
       "      <td>$4.23</td>\n",
       "      <td>$50.76</td>\n",
       "      <td>Oathbreaker, Last Hope of the Breaking Storm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.58</td>\n",
       "      <td>$41.22</td>\n",
       "      <td>Fiery Glass Crusader</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>9</td>\n",
       "      <td>$3.33</td>\n",
       "      <td>$28.99</td>\n",
       "      <td>Persuasion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>9</td>\n",
       "      <td>$3.53</td>\n",
       "      <td>$31.77</td>\n",
       "      <td>Extraction, Quickblade Of Trembling Hands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Purchase Count Item Price Total Purchase  \\\n",
       "Item ID                                             \n",
       "92                   13      $4.88         $59.99   \n",
       "178                  12      $4.23         $50.76   \n",
       "145                   9      $4.58         $41.22   \n",
       "132                   9      $3.33         $28.99   \n",
       "108                   9      $3.53         $31.77   \n",
       "\n",
       "                                           Item Names  \n",
       "Item ID                                                \n",
       "92                                       Final Critic  \n",
       "178      Oathbreaker, Last Hope of the Breaking Storm  \n",
       "145                              Fiery Glass Crusader  \n",
       "132                                        Persuasion  \n",
       "108         Extraction, Quickblade Of Trembling Hands  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popular_items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Most Profitable Items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Sort the above table by total purchase value in descending order\n",
    "\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting\n",
    "\n",
    "\n",
    "* Display a preview of the data frame\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "profitable_items = values.sort_values(by = ['Total Purchase'],ascending = False)[:5]\n",
    "profitable_items['Item Names'] = item_map.loc[profitable_items.index].drop_duplicates()['Item Name']\n",
    "profitable_items[\"Item Price\"] = profitable_items[\"Item Price\"].map(\"${:,.2f}\".format)\n",
    "profitable_items[\"Total Purchase\"] = profitable_items[\"Total Purchase\"].map(\"${:,.2f}\".format)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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 Count</th>\n",
       "      <th>Item Price</th>\n",
       "      <th>Total Purchase</th>\n",
       "      <th>Item Names</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>13</td>\n",
       "      <td>$4.88</td>\n",
       "      <td>$59.99</td>\n",
       "      <td>Final Critic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>12</td>\n",
       "      <td>$4.23</td>\n",
       "      <td>$50.76</td>\n",
       "      <td>Oathbreaker, Last Hope of the Breaking Storm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.90</td>\n",
       "      <td>$44.10</td>\n",
       "      <td>Nirvana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.58</td>\n",
       "      <td>$41.22</td>\n",
       "      <td>Fiery Glass Crusader</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>8</td>\n",
       "      <td>$4.35</td>\n",
       "      <td>$34.80</td>\n",
       "      <td>Singed Scalpel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Purchase Count Item Price Total Purchase  \\\n",
       "Item ID                                             \n",
       "92                   13      $4.88         $59.99   \n",
       "178                  12      $4.23         $50.76   \n",
       "82                    9      $4.90         $44.10   \n",
       "145                   9      $4.58         $41.22   \n",
       "103                   8      $4.35         $34.80   \n",
       "\n",
       "                                           Item Names  \n",
       "Item ID                                                \n",
       "92                                       Final Critic  \n",
       "178      Oathbreaker, Last Hope of the Breaking Storm  \n",
       "82                                            Nirvana  \n",
       "145                              Fiery Glass Crusader  \n",
       "103                                    Singed Scalpel  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "profitable_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernel_info": {
   "name": "python3"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "nteract": {
   "version": "0.2.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}