HeroesOfPymoli_starter(CB).ipynb

{
 "cells": [
  {
   "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": 291,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "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  "
      ],
      "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>"
     },
     "metadata": {},
     "execution_count": 291
    }
   ],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "\n",
    "# File to Load (Remember to Change These)\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)\n",
    "purchase_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Player Count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Display the total number of players\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "SN             576\n",
       "Purchase ID    576\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 292
    }
   ],
   "source": [
    "#Total number of players based on the Purchase ID\n",
    "players_df = purchase_data.groupby(by='SN', as_index=False).agg({'Purchase ID': pd.Series.nunique})\n",
    "players_df.count()\n",
    "\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": 293,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Average Age      22.714103\n",
       "Average Price     3.050987\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 293
    }
   ],
   "source": [
    "\n",
    "#total revenue\n",
    "    #list(purchase_data)\n",
    "# purchase_price_df = purchase_data.loc[:,['Price']]\n",
    "# purchase_tr_df = purchase_price_df.rename(columns={\"Price\":\" Total Revenue\"})\n",
    "# final_rev_df = purchase_tr_df.sum()\n",
    "# round(final_rev_df.head(), 2)\n",
    "\n",
    "# #total purchases\n",
    "# purchase_count = purchase_data.loc[:,[\"Purchase ID\"]]\n",
    "# purchase_count_final = purchase_count.count()\n",
    "# purchase_count_final.head()\n",
    "# purchase_count = purchase_count.rename(columns={\"Purchase ID\":\" Total Purchases\"})\n",
    "# final_count_df = purchase_count.count()\n",
    "# final_count_df.head()\n",
    "\n",
    "# #average age and average price\n",
    "purchase_ageprice_df = purchase_data.loc[:,['Age', 'Price' ]]\n",
    "purchase_ageprice_df = purchase_ageprice_df.rename(columns={\"Age\":\"Average Age\", \"Price\":\"Average Price\"})\n",
    "final_average_df = purchase_ageprice_df.mean()\n",
    "#final_average_df[\"Average Price\"] = final_average_df[\"Average Price\"].map(\"${:.2f}\".format)\n",
    "final_average_df.head()\n",
    "\n",
    "#merge all data on one table for show\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gender Demographics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 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": 294,
   "metadata": {},
   "outputs": [],
   "source": [
    "#provided"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "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  "
      ],
      "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>"
     },
     "metadata": {},
     "execution_count": 295
    }
   ],
   "source": [
    "purchase_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                       Total Count Percentage of Players\n",
       "Gender                                                  \n",
       "Male                           484               84.03 %\n",
       "Female                          81               14.06 %\n",
       "Other / Non-Disclosed           11                1.91 %"
      ],
      "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    <tr>\n      <th>Gender</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Male</th>\n      <td>484</td>\n      <td>84.03 %</td>\n    </tr>\n    <tr>\n      <th>Female</th>\n      <td>81</td>\n      <td>14.06 %</td>\n    </tr>\n    <tr>\n      <th>Other / Non-Disclosed</th>\n      <td>11</td>\n      <td>1.91 %</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 296
    }
   ],
   "source": [
    "# list(purchase_data)\n",
    "\n",
    "gender_df = purchase_data.loc[:,[\"SN\", 'Gender']]\n",
    "gender_df = gender_df.drop_duplicates()\n",
    "gender_df = gender_df.groupby('Gender')['Gender'].agg(['count'])\n",
    "gender_total_df = gender_df.sum()\n",
    "\n",
    "gender_df.insert(1, \"Percent\" , ((gender_df['count']/gender_df['count'].sum())*100).map(\"{:.2f} %\".format), True)\n",
    "gender_df = gender_df.reindex(index=['Male','Female','Other / Non-Disclosed'])\n",
    "gender_df = gender_df.rename(columns={\"count\":\"Total Count\", \"Percent\":\"Percentage of Players\"})\n",
    "gender_df.head()\n"
   ]
  },
  {
   "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": 297,
   "metadata": {},
   "outputs": [],
   "source": [
    "#provided"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                       Purchase Count  Average Purchase Price  \\\n",
       "Gender                                                          \n",
       "Female                            113                3.203009   \n",
       "Male                              652                3.017853   \n",
       "Other / Non-Disclosed              15                3.346000   \n",
       "\n",
       "                       Total Purchase Value Avg Total Purchase per Person  \n",
       "Gender                                                                     \n",
       "Female                               361.94                         $4.47  \n",
       "Male                                1967.64                         $4.07  \n",
       "Other / Non-Disclosed                 50.19                         $4.56  "
      ],
      "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.203009</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.017853</td>\n      <td>1967.64</td>\n      <td>$4.07</td>\n    </tr>\n    <tr>\n      <th>Other / Non-Disclosed</th>\n      <td>15</td>\n      <td>3.346000</td>\n      <td>50.19</td>\n      <td>$4.56</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 311
    }
   ],
   "source": [
    "\n",
    "gender_pur_df = purchase_data.loc[:,['SN','Gender','Price']]\n",
    "\n",
    "\n",
    "# Create the Dataframes\n",
    "gender_pur_count_df = gender_pur_df.groupby(['Gender']).count()          \n",
    "gender_pur_sum_df = gender_pur_df.groupby('Gender')['Price'].sum()\n",
    "gender_pur_avg_df = gender_pur_df.groupby('Gender')['Price'].mean()\n",
    "\n",
    "\n",
    "\n",
    "#Merge the data using \n",
    "merge_pur_df = pd.merge(gender_pur_count_df,gender_pur_avg_df, on='Gender', how=\"left\")\n",
    "del merge_pur_df[\"Price_x\"]\n",
    "merge_pur_df = pd.merge(merge_pur_df,gender_pur_sum_df, on='Gender', how=\"left\")\n",
    "\n",
    "merge_pur_df.insert(3, \"Avg Total Purchase per Person\" , (merge_pur_df['Price']/gender_df['Total Count']).map(\"${:.2f}\".format), True)\n",
    "\n",
    "merge_pur_df = merge_pur_df.rename(columns={\n",
    "    \"SN\":\"Purchase Count\",\n",
    "    \"Price_y\":\"Average Purchase Price\", \n",
    "    \"Price\": \"Total Purchase Value\"\n",
    "    })\n",
    "\n",
    "# merge_pur_df = merge_pur_df[\"Total Purchase Value\"].map('${:,.2f}'.format) \n",
    "\n",
    "\n",
    "\n",
    "merge_pur_df"
   ]
  },
  {
   "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": 6,
   "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>2.95%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>22</td>\n",
       "      <td>3.82%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>107</td>\n",
       "      <td>18.58%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>258</td>\n",
       "      <td>44.79%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-29</th>\n",
       "      <td>77</td>\n",
       "      <td>13.37%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>52</td>\n",
       "      <td>9.03%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-39</th>\n",
       "      <td>31</td>\n",
       "      <td>5.38%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40+</th>\n",
       "      <td>12</td>\n",
       "      <td>2.08%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Total Count Percentage of Players\n",
       "<10             17                 2.95%\n",
       "10-14           22                 3.82%\n",
       "15-19          107                18.58%\n",
       "20-24          258                44.79%\n",
       "25-29           77                13.37%\n",
       "30-34           52                 9.03%\n",
       "35-39           31                 5.38%\n",
       "40+             12                 2.08%"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 357,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      Total Count Percentage of Players\n",
       "                                       \n",
       "Bin                                    \n",
       "<10            17                 2.95%\n",
       "10-14          22                 3.82%\n",
       "15-19         107                18.58%\n",
       "20-24         258                44.79%\n",
       "25-29          77                13.37%\n",
       "30-34          52                 9.03%\n",
       "35-39          31                 5.38%\n",
       "40+            12                 2.08%"
      ],
      "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>Total Count</th>\n      <th>Percentage of Players</th>\n    </tr>\n    <tr>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n    <tr>\n      <th>Bin</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>&lt;10</th>\n      <td>17</td>\n      <td>2.95%</td>\n    </tr>\n    <tr>\n      <th>10-14</th>\n      <td>22</td>\n      <td>3.82%</td>\n    </tr>\n    <tr>\n      <th>15-19</th>\n      <td>107</td>\n      <td>18.58%</td>\n    </tr>\n    <tr>\n      <th>20-24</th>\n      <td>258</td>\n      <td>44.79%</td>\n    </tr>\n    <tr>\n      <th>25-29</th>\n      <td>77</td>\n      <td>13.37%</td>\n    </tr>\n    <tr>\n      <th>30-34</th>\n      <td>52</td>\n      <td>9.03%</td>\n    </tr>\n    <tr>\n      <th>35-39</th>\n      <td>31</td>\n      <td>5.38%</td>\n    </tr>\n    <tr>\n      <th>40+</th>\n      <td>12</td>\n      <td>2.08%</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 357
    }
   ],
   "source": [
    "# create the bins\n",
    "bin = [0, 10, 15, 20, 25, 30, 35, 40, 10000000]\n",
    "group_names = [\"<10\", \"10-14\", \"15-19\", \"20-24\", \"25-29\", \"30-34\", \"35-39\", \"40+\"]\n",
    "\n",
    "age_df = purchase_data.loc[:,['SN', 'Age']]\n",
    "age_df = age_df.drop_duplicates()\n",
    "\n",
    "age_df['Bin'] = pd.cut(age_df['Age'], bin, labels = group_names, right=False, include_lowest=True)\n",
    "\n",
    "del age_df[\"SN\"]\n",
    "\n",
    "age_df = age_df.groupby(['Bin']).agg(['count'])\n",
    "age_total_df = age_df.sum()\n",
    "\n",
    "age_df = age_df.rename(columns={\n",
    "    \"Age\":\"Total Count\",\n",
    "    \"count\":\"\",\n",
    "    })\n",
    "\n",
    "age_df.insert(1, \"Percentage of Players\" , ((age_df['Total Count']/age_df['Total Count'].sum())*100).map(\"{:.2f}%\".format), True)\n",
    "\n",
    "age_df"
   ]
  },
  {
   "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": 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>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>Age Ranges</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>$4.54</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.76</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.86</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>$4.32</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>$3.81</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>$4.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-39</th>\n",
       "      <td>41</td>\n",
       "      <td>$3.60</td>\n",
       "      <td>$147.67</td>\n",
       "      <td>$4.76</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>$3.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Purchase Count Average Purchase Price Total Purchase Value  \\\n",
       "Age Ranges                                                              \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-39                  41                  $3.60              $147.67   \n",
       "40+                    13                  $2.94               $38.24   \n",
       "\n",
       "           Avg Total Purchase per Person  \n",
       "Age Ranges                                \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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       count   Price_x  Price_y Avg Total Purchase per Person\n",
       "Bin                                                          \n",
       "<10       23  3.353478    77.13                         $3.35\n",
       "10-14     28  2.956429    82.78                         $2.96\n",
       "15-19    136  3.035956   412.89                         $3.04\n",
       "20-24    365  3.052219  1114.06                         $3.05\n",
       "25-29    101  2.900990   293.00                         $2.90\n",
       "30-34     73  2.931507   214.00                         $2.93\n",
       "35-39     41  3.601707   147.67                         $3.60\n",
       "40+       13  2.941538    38.24                         $2.94"
      ],
      "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>count</th>\n      <th>Price_x</th>\n      <th>Price_y</th>\n      <th>Avg Total Purchase per Person</th>\n    </tr>\n    <tr>\n      <th>Bin</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>&lt;10</th>\n      <td>23</td>\n      <td>3.353478</td>\n      <td>77.13</td>\n      <td>$3.35</td>\n    </tr>\n    <tr>\n      <th>10-14</th>\n      <td>28</td>\n      <td>2.956429</td>\n      <td>82.78</td>\n      <td>$2.96</td>\n    </tr>\n    <tr>\n      <th>15-19</th>\n      <td>136</td>\n      <td>3.035956</td>\n      <td>412.89</td>\n      <td>$3.04</td>\n    </tr>\n    <tr>\n      <th>20-24</th>\n      <td>365</td>\n      <td>3.052219</td>\n      <td>1114.06</td>\n      <td>$3.05</td>\n    </tr>\n    <tr>\n      <th>25-29</th>\n      <td>101</td>\n      <td>2.900990</td>\n      <td>293.00</td>\n      <td>$2.90</td>\n    </tr>\n    <tr>\n      <th>30-34</th>\n      <td>73</td>\n      <td>2.931507</td>\n      <td>214.00</td>\n      <td>$2.93</td>\n    </tr>\n    <tr>\n      <th>35-39</th>\n      <td>41</td>\n      <td>3.601707</td>\n      <td>147.67</td>\n      <td>$3.60</td>\n    </tr>\n    <tr>\n      <th>40+</th>\n      <td>13</td>\n      <td>2.941538</td>\n      <td>38.24</td>\n      <td>$2.94</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 434
    }
   ],
   "source": [
    "age_pur_df = purchase_data.loc[:,['SN', 'Age', 'Price']]\n",
    "\n",
    "age_pur_df['Bin'] = pd.cut(age_pur_df['Age'], bin, labels = group_names, right=False, include_lowest=True)\n",
    "\n",
    "\n",
    "age_pur_count_df = age_pur_df.groupby('Bin')['Bin'].agg(['count'])\n",
    "age_pur_avg_df = age_pur_df.groupby('Bin')['Price'].mean()\n",
    "\n",
    "age_pur_sum_df = age_pur_df.groupby('Bin')['Price'].sum()\n",
    "\n",
    "\n",
    "\n",
    "# del age_pur_df[\"SN\"]\n",
    "\n",
    "\n",
    "\n",
    "# #Merge the data using \n",
    "merge_age_df = pd.merge(age_pur_count_df,age_pur_avg_df, on='Bin', how=\"right\")\n",
    "merge_age_df = pd.merge(merge_age_df,age_pur_sum_df, on='Bin', how=\"left\")\n",
    "\n",
    "merge_age_df.insert(3, \"Avg Total Purchase per Person\" , (merge_age_df['Price_y']/merge_age_df['count']).map(\"${:.2f}\".format), True)\n",
    "\n",
    "age_pur_sum_df = age_pur_sum_df.map(\"${:.2f} \".format)\n",
    "age_pur_avg_df = age_pur_avg_df.map(\"${:.2f} \".format)\n",
    "\n",
    "# merge_age_df = merge_age_df.rename(columns={\n",
    "#     \"Price_x\":\"Average Purchase Price\",\n",
    "#     \"count\":\"Purchase Count\",\n",
    "#       \"Price_y\":\"Total Purchase Value\"\n",
    "#     })\n",
    "\n",
    "#---------------------------------------------------------------------------------------------------------\n",
    "merge_age_df\n",
    "\n"
   ]
  },
  {
   "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": 8,
   "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",
       "    </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>$3.79</td>\n",
       "      <td>$18.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idastidru52</th>\n",
       "      <td>4</td>\n",
       "      <td>$3.86</td>\n",
       "      <td>$15.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chamjask73</th>\n",
       "      <td>3</td>\n",
       "      <td>$4.61</td>\n",
       "      <td>$13.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iral74</th>\n",
       "      <td>4</td>\n",
       "      <td>$3.40</td>\n",
       "      <td>$13.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iskadarya95</th>\n",
       "      <td>3</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 Value\n",
       "SN                                                                     \n",
       "Lisosia93                 5                  $3.79               $18.96\n",
       "Idastidru52               4                  $3.86               $15.45\n",
       "Chamjask73                3                  $4.61               $13.83\n",
       "Iral74                    4                  $3.40               $13.62\n",
       "Iskadarya95               3                  $4.37               $13.10"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 477,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              Purchase Count  Average Purchase Price  Price_y\n",
       "SN                                                           \n",
       "Lisosia93                  5                3.792000    18.96\n",
       "Iral74                     4                3.405000    13.62\n",
       "Idastidru52                4                3.862500    15.45\n",
       "Asur53                     3                2.480000     7.44\n",
       "Inguron55                  3                3.703333    11.11\n",
       "...                      ...                     ...      ...\n",
       "Hala31                     1                1.020000     1.02\n",
       "Haisurra41                 1                4.400000     4.40\n",
       "Hailaphos89                1                3.810000     3.81\n",
       "Haestyphos66               1                1.970000     1.97\n",
       "Iskosian40                 1                3.070000     3.07\n",
       "\n",
       "[576 rows x 3 columns]"
      ],
      "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>Price_y</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>3.792000</td>\n      <td>18.96</td>\n    </tr>\n    <tr>\n      <th>Iral74</th>\n      <td>4</td>\n      <td>3.405000</td>\n      <td>13.62</td>\n    </tr>\n    <tr>\n      <th>Idastidru52</th>\n      <td>4</td>\n      <td>3.862500</td>\n      <td>15.45</td>\n    </tr>\n    <tr>\n      <th>Asur53</th>\n      <td>3</td>\n      <td>2.480000</td>\n      <td>7.44</td>\n    </tr>\n    <tr>\n      <th>Inguron55</th>\n      <td>3</td>\n      <td>3.703333</td>\n      <td>11.11</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>Hala31</th>\n      <td>1</td>\n      <td>1.020000</td>\n      <td>1.02</td>\n    </tr>\n    <tr>\n      <th>Haisurra41</th>\n      <td>1</td>\n      <td>4.400000</td>\n      <td>4.40</td>\n    </tr>\n    <tr>\n      <th>Hailaphos89</th>\n      <td>1</td>\n      <td>3.810000</td>\n      <td>3.81</td>\n    </tr>\n    <tr>\n      <th>Haestyphos66</th>\n      <td>1</td>\n      <td>1.970000</td>\n      <td>1.97</td>\n    </tr>\n    <tr>\n      <th>Iskosian40</th>\n      <td>1</td>\n      <td>3.070000</td>\n      <td>3.07</td>\n    </tr>\n  </tbody>\n</table>\n<p>576 rows × 3 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 477
    }
   ],
   "source": [
    "top_df = purchase_data.loc[:,['SN', 'Price']]\n",
    "\n",
    "\n",
    "top_count_df = top_df.groupby('SN')['SN'].agg(['count'])\n",
    "top_avg_df = top_df.groupby('SN')['Price'].mean()\n",
    "top_sum_df = top_df.groupby('SN')['Price'].sum()\n",
    "\n",
    "merge_top_df = pd.merge(top_count_df,top_avg_df, on='SN', how=\"right\")\n",
    "merge_top_df = pd.merge(merge_top_df,top_sum_df, on='SN', how=\"left\")\n",
    "\n",
    "\n",
    "merge_top_df = merge_top_df.sort_values(by=['count'], ascending=False)\n",
    "\n",
    "merge_top_df = merge_top_df.rename(columns={\n",
    "      \"count\":\"Purchase Count\",\n",
    "      \"Price_x\":\"Average Purchase Price\"\n",
    "    #   \"Price_y\":\"Total Purchase Value\"\n",
    "    })\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "merge_top_df\n"
   ]
  },
  {
   "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, average 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": 9,
   "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>Purchase Count</th>\n",
       "      <th>Item Price</th>\n",
       "      <th>Total Purchase Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item ID</th>\n",
       "      <th>Item Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <th>Final Critic</th>\n",
       "      <td>13</td>\n",
       "      <td>$4.61</td>\n",
       "      <td>$59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <th>Oathbreaker, Last Hope of the Breaking Storm</th>\n",
       "      <td>12</td>\n",
       "      <td>$4.23</td>\n",
       "      <td>$50.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <th>Fiery Glass Crusader</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.58</td>\n",
       "      <td>$41.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <th>Persuasion</th>\n",
       "      <td>9</td>\n",
       "      <td>$3.22</td>\n",
       "      <td>$28.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <th>Extraction, Quickblade Of Trembling Hands</th>\n",
       "      <td>9</td>\n",
       "      <td>$3.53</td>\n",
       "      <td>$31.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Purchase Count  \\\n",
       "Item ID Item Name                                                     \n",
       "92      Final Critic                                             13   \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm             12   \n",
       "145     Fiery Glass Crusader                                      9   \n",
       "132     Persuasion                                                9   \n",
       "108     Extraction, Quickblade Of Trembling Hands                 9   \n",
       "\n",
       "                                                     Item Price  \\\n",
       "Item ID Item Name                                                 \n",
       "92      Final Critic                                      $4.61   \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm      $4.23   \n",
       "145     Fiery Glass Crusader                              $4.58   \n",
       "132     Persuasion                                        $3.22   \n",
       "108     Extraction, Quickblade Of Trembling Hands         $3.53   \n",
       "\n",
       "                                                     Total Purchase Value  \n",
       "Item ID Item Name                                                          \n",
       "92      Final Critic                                               $59.99  \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm               $50.76  \n",
       "145     Fiery Glass Crusader                                       $41.22  \n",
       "132     Persuasion                                                 $28.99  \n",
       "108     Extraction, Quickblade Of Trembling Hands                  $31.77  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 512,
   "metadata": {},
   "outputs": [
    {
     "output_type": "error",
     "ename": "KeyError",
     "evalue": "'Item ID'",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2894\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2895\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2896\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Item ID'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-512-c966ce429e26>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[0mmerge_item_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmerge_item_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'count'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mascending\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 15\u001b[1;33m \u001b[0mmerge_item_df\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mmerge_item_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmerge_item_df\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Item ID'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     16\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     17\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2900\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnlevels\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2901\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2902\u001b[1;33m             \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2903\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2904\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2895\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcasted_key\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2896\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2897\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2898\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2899\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mtolerance\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Item ID'"
     ]
    }
   ],
   "source": [
    "item_df = purchase_data.loc[:,['Item ID', \"Item Name\", 'Price']]\n",
    "\n",
    "\n",
    "item_count_df = item_df.groupby('Item Name')['Item ID'].agg(['count'])\n",
    "item_avg_df = item_df.groupby('Item Name')['Price'].mean()\n",
    "item_sum_df = item_df.groupby('Item Name')['Price'].sum()\n",
    "# item_id_df = item_df.groupby('Item ID')['Item ID'].Value\n",
    "\n",
    "merge_item_df = pd.merge(item_count_df,item_avg_df, on='Item Name', how=\"inner\")\n",
    "merge_item_df = pd.merge(merge_item_df,item_sum_df, on='Item Name', how=\"inner\")\n",
    "\n",
    "\n",
    "merge_item_df = merge_item_df.sort_values(by=['count'], ascending=False)\n",
    "\n",
    "merge_item_df= merge_item_df.insert(0, merge_item_df['Item ID'], True)\n",
    "\n",
    "\n",
    "# merge_item_df = merge_item_df.rename(columns={\n",
    "#       \"count\":\"Purchase Count\",\n",
    "#       \"Price_x\":\"Average Purchase Price\"\n",
    "#     #   \"Price_y\":\"Total Purchase Value\"\n",
    "#     })\n",
    "\n",
    "\n",
    "\n",
    "merge_item_df.head(10)"
   ]
  },
  {
   "source": [
    "## Most Profitable Items"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 511,
   "outputs": []
  },
  {
   "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": 10,
   "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>Purchase Count</th>\n",
       "      <th>Item Price</th>\n",
       "      <th>Total Purchase Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item ID</th>\n",
       "      <th>Item Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <th>Final Critic</th>\n",
       "      <td>13</td>\n",
       "      <td>$4.61</td>\n",
       "      <td>$59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <th>Oathbreaker, Last Hope of the Breaking Storm</th>\n",
       "      <td>12</td>\n",
       "      <td>$4.23</td>\n",
       "      <td>$50.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <th>Nirvana</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.90</td>\n",
       "      <td>$44.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <th>Fiery Glass Crusader</th>\n",
       "      <td>9</td>\n",
       "      <td>$4.58</td>\n",
       "      <td>$41.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <th>Singed Scalpel</th>\n",
       "      <td>8</td>\n",
       "      <td>$4.35</td>\n",
       "      <td>$34.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Purchase Count  \\\n",
       "Item ID Item Name                                                     \n",
       "92      Final Critic                                             13   \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm             12   \n",
       "82      Nirvana                                                   9   \n",
       "145     Fiery Glass Crusader                                      9   \n",
       "103     Singed Scalpel                                            8   \n",
       "\n",
       "                                                     Item Price  \\\n",
       "Item ID Item Name                                                 \n",
       "92      Final Critic                                      $4.61   \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm      $4.23   \n",
       "82      Nirvana                                           $4.90   \n",
       "145     Fiery Glass Crusader                              $4.58   \n",
       "103     Singed Scalpel                                    $4.35   \n",
       "\n",
       "                                                     Total Purchase Value  \n",
       "Item ID Item Name                                                          \n",
       "92      Final Critic                                               $59.99  \n",
       "178     Oathbreaker, Last Hope of the Breaking Storm               $50.76  \n",
       "82      Nirvana                                                    $44.10  \n",
       "145     Fiery Glass Crusader                                       $41.22  \n",
       "103     Singed Scalpel                                             $34.80  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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.8.5-final"
  },
  "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
}