Video_Game_Users_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "path = '/Users/moutasemakkad/Desktop/COLNYC201904DATA3/homework/04-Pandas/Instructions/HeroesOfPymoli/Resources/purchase_data.csv'\n",
    "file = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(file).sort_values(by=['SN'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Player Count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users = df['SN']\n",
    "count_users = users.unique()\n",
    "# c = pd.DataFrame(count_users)\n",
    "# c.count()\n",
    "# len(df['SN'].unique())\n",
    "\n",
    "df_total_players = pd.DataFrame([{'Total Players':len(count_users)}])\n",
    "df_total_players"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Purchasing Analysis (Total)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Number of Unique Items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of Unique Items\n",
    "num_unique_items = len(df['Item Name'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Item Average Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average Purchase Price\n",
    "Average_purchase_price = df['Price'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Total Number of Purchases\n",
    "num_purchases = df['Item Name'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Total Revenue\n",
    "total_revenue = df['Price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Number of Unique Items</th>\n",
       "      <th>Average Price</th>\n",
       "      <th>Number of Purchases</th>\n",
       "      <th>Total Revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>179</td>\n",
       "      <td>$3.05</td>\n",
       "      <td>780</td>\n",
       "      <td>$2379.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Number of Unique Items Average Price  Number of Purchases Total Revenue\n",
       "0                     179         $3.05                  780      $2379.77"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_purchasing_analysis = pd.DataFrame({'Number of Unique Items': [num_unique_items],'Average Price': [f\"${round(Average_purchase_price,2)}\"],'Number of Purchases': [num_purchases],'Total Revenue':[f\"${total_revenue}\"]})\n",
    "df_purchasing_analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Gender Demographics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = df['Gender'].unique()\n",
    "len(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# percentage and Count of Male Players\n",
    "user_gender_df = df.drop_duplicates(subset=\"SN\")\n",
    "\n",
    "# .unique()\n",
    "# g = pd.DataFrame(group)\n",
    "# g"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "576"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total = user_gender_df['SN'].count()\n",
    "total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Male                     484\n",
       "Female                    81\n",
       "Other / Non-Disclosed     11\n",
       "Name: Gender, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Male Count\n",
    "gender_counts = user_gender_df[\"Gender\"].value_counts()\n",
    "gender_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "84.02777777777779"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Male Precentage\n",
    "male_count = gender_counts[\"Male\"]\n",
    "malePrecentage = (male_count/total)*100\n",
    "malePrecentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14.0625"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Female Count\n",
    "female_count = gender_counts[\"Female\"]\n",
    "femalePrecentage = (female_count/total)*100\n",
    "femalePrecentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count of Other / Non-Disclosed\n",
    "NonDisclosed = gender_counts[\"Other / Non-Disclosed\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.9097222222222223"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Non disclosed Precentage\n",
    "NonDisclosedPrecentage = (NonDisclosed/total)*100\n",
    "NonDisclosedPrecentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gender</th>\n",
       "      <th>Total Count</th>\n",
       "      <th>Percentage of Players</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>484</td>\n",
       "      <td>84.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>81</td>\n",
       "      <td>14.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other / Non-Disclosed</td>\n",
       "      <td>11</td>\n",
       "      <td>1.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Gender  Total Count  Percentage of Players\n",
       "0                   Male          484                  84.03\n",
       "1                 Female           81                  14.06\n",
       "2  Other / Non-Disclosed           11                   1.91"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Gender_Demographics = pd.DataFrame({\n",
    "                         'Gender':['Male', 'Female', 'Other / Non-Disclosed'],\n",
    "                         'Total Count' : [male_count, female_count, NonDisclosed],\n",
    "                         'Percentage of Players' : [round(malePrecentage,2), round(femalePrecentage,2), round(NonDisclosedPrecentage,2)]\n",
    "})\n",
    "df_Gender_Demographics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Purchasing Analysis (Gender)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2379.77"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#total  \n",
    "total_number_purchases = df.count()['Gender']\n",
    "total_sum_purchases = df['Price'].sum()\n",
    "total_sum_purchases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "last = total_num_purchases_male = df['Gender'].value_counts()['Male']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "652"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# purchase count male\n",
    "total_num_purchases_male = df['Gender'].value_counts()['Male']\n",
    "total_num_purchases_male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price    1967.64\n",
       "dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Total spent male\n",
    "total_spent_male = df.loc[df['Gender']=='Male', ['Price']].sum()\n",
    "total_spent_male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "113"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# purchase count female\n",
    "total_num_purchases_female = df['Gender'].value_counts()['Female']\n",
    "total_num_purchases_female"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price    361.94\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Total spent female\n",
    "total_spent_female = df.loc[df['Gender']=='Female', ['Price']].sum()\n",
    "total_spent_female"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# purchase count undi\n",
    "total_num_purchases_undi = df['Gender'].value_counts()['Other / Non-Disclosed']\n",
    "total_num_purchases_undi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price    50.19\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Total spent undi\n",
    "total_spent_undi = df.loc[df['Gender']=='Other / Non-Disclosed', ['Price']].sum()\n",
    "total_spent_undi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Average Purchase Total per Person Male\n",
    "maleav = df.loc[df['Gender']=='Male', ['Price']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Average Purchase Total per Person female\n",
    "femaleav = df.loc[df['Gender']=='Female', ['Price']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Average Purchase Total per Person undi\n",
    "undiav = df.loc[df['Gender']=='Other / Non-Disclosed', ['Price']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "sum_grouped = df.groupby(['SN', 'Gender'])['Price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Gender\n",
       "Female                   4.468395\n",
       "Male                     4.065372\n",
       "Other / Non-Disclosed    4.562727\n",
       "Name: Sum, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataFrame = pd.DataFrame({'Sum': sum_grouped})\n",
    "dataFrame.groupby('Gender')['Sum'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "per_person_per_gender_df = pd.DataFrame({'Mean per person-gender': dataFrame.groupby('Gender')['Sum'].mean()})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>Gender</th>\n",
       "      <th>Purchase Count</th>\n",
       "      <th>Average Purchase Price</th>\n",
       "      <th>Total Purchase Value</th>\n",
       "      <th>Mean per person-gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>652</td>\n",
       "      <td>$3.02</td>\n",
       "      <td>1967.64</td>\n",
       "      <td>4.065372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>113</td>\n",
       "      <td>$3.2</td>\n",
       "      <td>361.94</td>\n",
       "      <td>4.468395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other / Non-Disclosed</td>\n",
       "      <td>15</td>\n",
       "      <td>$3.35</td>\n",
       "      <td>50</td>\n",
       "      <td>4.562727</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Gender  Purchase Count Average Purchase Price  \\\n",
       "0                   Male             652                  $3.02   \n",
       "1                 Female             113                   $3.2   \n",
       "2  Other / Non-Disclosed              15                  $3.35   \n",
       "\n",
       "  Total Purchase Value  Mean per person-gender  \n",
       "0              1967.64                4.065372  \n",
       "1               361.94                4.468395  \n",
       "2                   50                4.562727  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Purchasing_Analysis_Gender_df = pd.DataFrame({\n",
    "    'Gender': ['Male', 'Female', 'Other / Non-Disclosed'],\n",
    "    'Purchase Count': [total_num_purchases_male, total_num_purchases_female, int(total_num_purchases_undi)],\n",
    "    'Average Purchase Price': [f'${round(float(maleav),2)}', f\"${round(float(femaleav),2)}\", f\"${round(float(undiav),2)}\"],\n",
    "    \n",
    "    'Total Purchase Value' : [f\"{round(float(total_spent_male),2)}\", float(total_spent_female), int(total_spent_undi)]\n",
    "})\n",
    "Purchasing_Analysis_Gender_df.merge(per_person_per_gender_df, on='Gender')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Age Demographics\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = [0, 9, 15, 19,24,29,34,39,49]\n",
    "labels = ['<10','10-14', '15-19','20-24','25-29', '30-34','35-39','40+']\n",
    "pd.cut(df['Age'],bins,labels=labels)\n",
    "df2 = df.sort_values(by='Purchase ID')\n",
    "\n",
    "df2['Age Range'] = pd.cut(df['Age'],bins,labels=labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Age Range</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>&lt;10</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-29</th>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-34</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-39</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40+</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Count\n",
       "Age Range       \n",
       "<10           23\n",
       "10-14         63\n",
       "15-19        101\n",
       "20-24        365\n",
       "25-29        101\n",
       "30-34         73\n",
       "35-39         41\n",
       "40+           13"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ageRangeCount = df2.groupby('Age Range')['Price'].count()\n",
    "dfff = pd.DataFrame({'Count':ageRangeCount})\n",
    "dfff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "averagePrice = df2.groupby('Age Range')['Price'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "TotalPurchaseValue = df2.groupby('Age Range')['Price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "averagePricePerPerson = df2.groupby(['Age Range','SN'])['Price'].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "averagePricePerPerson1 = averagePricePerPerson.groupby('Age Range').mean()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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 Range</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.537059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10-14</th>\n",
       "      <td>63</td>\n",
       "      <td>2.99</td>\n",
       "      <td>188.43</td>\n",
       "      <td>3.925625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-19</th>\n",
       "      <td>101</td>\n",
       "      <td>3.04</td>\n",
       "      <td>307.24</td>\n",
       "      <td>3.793086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20-24</th>\n",
       "      <td>365</td>\n",
       "      <td>3.05</td>\n",
       "      <td>1114.06</td>\n",
       "      <td>4.318062</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.805195</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.115385</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.763548</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.186667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Count  Average Purchase Price  Total Purchase Value  \\\n",
       "Age Range                                                        \n",
       "<10           23                    3.35                 77.13   \n",
       "10-14         63                    2.99                188.43   \n",
       "15-19        101                    3.04                307.24   \n",
       "20-24        365                    3.05               1114.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 Range                                 \n",
       "<10                             4.537059  \n",
       "10-14                           3.925625  \n",
       "15-19                           3.793086  \n",
       "20-24                           4.318062  \n",
       "25-29                           3.805195  \n",
       "30-34                           4.115385  \n",
       "35-39                           4.763548  \n",
       "40+                             3.186667  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataFramedemo = pd.DataFrame({\"Count\": ageRangeCount,\n",
    "                              \"Average Purchase Price\": round(averagePrice,2),\n",
    "                              \"Total Purchase Value\": TotalPurchaseValue,\n",
    "                              \"Avg Total Purchase per Person\" : averagePricePerPerson1})\n",
    "dataFramedemo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top Spenders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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 Purchase per person</th>\n",
       "      <th>Purchase Count</th>\n",
       "      <th>Average 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>18.96</td>\n",
       "      <td>5</td>\n",
       "      <td>3.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idastidru52</th>\n",
       "      <td>15.45</td>\n",
       "      <td>4</td>\n",
       "      <td>3.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chamjask73</th>\n",
       "      <td>13.83</td>\n",
       "      <td>3</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iral74</th>\n",
       "      <td>13.62</td>\n",
       "      <td>4</td>\n",
       "      <td>3.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iskadarya95</th>\n",
       "      <td>13.10</td>\n",
       "      <td>3</td>\n",
       "      <td>4.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Total Purchase per person  Purchase Count  Average Purchase Price\n",
       "SN                                                                            \n",
       "Lisosia93                        18.96               5                    3.79\n",
       "Idastidru52                      15.45               4                    3.86\n",
       "Chamjask73                       13.83               3                    4.61\n",
       "Iral74                           13.62               4                    3.40\n",
       "Iskadarya95                      13.10               3                    4.37"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summed = df.groupby('SN')['Price'].sum()\n",
    "count = df.groupby('SN')['Price'].count()\n",
    "average = df.groupby('SN')['Price'].mean()\n",
    "\n",
    "dfAverages = pd.DataFrame({'Total Purchase per person':summed})\n",
    "dfAverages = dfAverages.sort_values('Total Purchase per person', ascending=False).head(5)\n",
    "dfAverages['Purchase Count'] = count\n",
    "dfAverages['Average Purchase Price'] = round(average,2)\n",
    "dfAverages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Most Popular Items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Item Name</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>123</td>\n",
       "      <td>Twilight's Carver</td>\n",
       "      <td>2.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>175</td>\n",
       "      <td>Woeful Adamantite Claymore</td>\n",
       "      <td>4.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>128</td>\n",
       "      <td>Blazeguard, Reach of Eternity</td>\n",
       "      <td>4.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>119</td>\n",
       "      <td>Stormbringer, Dark Blade of Ending Misery</td>\n",
       "      <td>4.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>171</td>\n",
       "      <td>Scalpel</td>\n",
       "      <td>1.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Item ID                                  Item Name  Price\n",
       "467      123                          Twilight's Carver   2.28\n",
       "142      175                 Woeful Adamantite Claymore   4.48\n",
       "388      128              Blazeguard, Reach of Eternity   4.91\n",
       "28       119  Stormbringer, Dark Blade of Ending Misery   4.32\n",
       "630      171                                    Scalpel   1.79"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "most_pop_df = df[['Item ID', 'Item Name', 'Price']]\n",
    "most_pop_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Item Name\n",
       "Abyssal Shard                     13.35\n",
       "Aetherius, Boon of the Blessed    16.95\n",
       "Agatha                            18.48\n",
       "Alpha                              6.21\n",
       "Alpha, Oath of Zeal               12.15\n",
       "Name: Price, dtype: float64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_Purchase = most_pop_df.groupby('Item Name')['Price'].sum()\n",
    "total_Purchase.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Item Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Final Critic</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oathbreaker, Last Hope of the Breaking Storm</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Persuasion</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nirvana</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Extraction, Quickblade Of Trembling Hands</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Count\n",
       "Item Name                                          \n",
       "Final Critic                                     13\n",
       "Oathbreaker, Last Hope of the Breaking Storm     12\n",
       "Persuasion                                        9\n",
       "Nirvana                                           9\n",
       "Extraction, Quickblade Of Trembling Hands         9"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_count = most_pop_df.groupby('Item Name')['Price'].count()\n",
    "total_count_df = pd.DataFrame({'Count':total_count})\n",
    "total_count_df = total_count_df.sort_values('Count', ascending=False)\n",
    "total_count_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_Purchase_df = pd.DataFrame({'total_Purchase':total_Purchase})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_Purchase_df = total_Purchase_df.sort_values('total_Purchase', ascending=False).head(5)\n",
    "total_Purchase= total_Purchase_df.merge(most_pop_df, on='Item Name')\n",
    "total_Purchase = total_Purchase.drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_Purchase = total_Purchase.drop([0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>Item Name</th>\n",
       "      <th>Count</th>\n",
       "      <th>total_Purchase</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Final Critic</td>\n",
       "      <td>13</td>\n",
       "      <td>59.99</td>\n",
       "      <td>101</td>\n",
       "      <td>4.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Oathbreaker, Last Hope of the Breaking Storm</td>\n",
       "      <td>12</td>\n",
       "      <td>50.76</td>\n",
       "      <td>178</td>\n",
       "      <td>4.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nirvana</td>\n",
       "      <td>9</td>\n",
       "      <td>44.10</td>\n",
       "      <td>82</td>\n",
       "      <td>4.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fiery Glass Crusader</td>\n",
       "      <td>9</td>\n",
       "      <td>41.22</td>\n",
       "      <td>145</td>\n",
       "      <td>4.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Singed Scalpel</td>\n",
       "      <td>8</td>\n",
       "      <td>34.80</td>\n",
       "      <td>103</td>\n",
       "      <td>4.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Item Name  Count  total_Purchase  \\\n",
       "0                                  Final Critic     13           59.99   \n",
       "1  Oathbreaker, Last Hope of the Breaking Storm     12           50.76   \n",
       "2                                       Nirvana      9           44.10   \n",
       "3                          Fiery Glass Crusader      9           41.22   \n",
       "4                                Singed Scalpel      8           34.80   \n",
       "\n",
       "   Item ID  Price  \n",
       "0      101   4.19  \n",
       "1      178   4.23  \n",
       "2       82   4.90  \n",
       "3      145   4.58  \n",
       "4      103   4.35  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_count_df.merge(total_Purchase, on='Item Name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Most Profitable Items\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Item Name</th>\n",
       "      <th>total_Purchase</th>\n",
       "      <th>Item ID</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Final Critic</td>\n",
       "      <td>59.99</td>\n",
       "      <td>101</td>\n",
       "      <td>4.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Oathbreaker, Last Hope of the Breaking Storm</td>\n",
       "      <td>50.76</td>\n",
       "      <td>178</td>\n",
       "      <td>4.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Nirvana</td>\n",
       "      <td>44.10</td>\n",
       "      <td>82</td>\n",
       "      <td>4.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Fiery Glass Crusader</td>\n",
       "      <td>41.22</td>\n",
       "      <td>145</td>\n",
       "      <td>4.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Singed Scalpel</td>\n",
       "      <td>34.80</td>\n",
       "      <td>103</td>\n",
       "      <td>4.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Item Name  total_Purchase  Item ID  \\\n",
       "2                                   Final Critic           59.99      101   \n",
       "13  Oathbreaker, Last Hope of the Breaking Storm           50.76      178   \n",
       "25                                       Nirvana           44.10       82   \n",
       "34                          Fiery Glass Crusader           41.22      145   \n",
       "43                                Singed Scalpel           34.80      103   \n",
       "\n",
       "    Price  \n",
       "2    4.19  \n",
       "13   4.23  \n",
       "25   4.90  \n",
       "34   4.58  \n",
       "43   4.35  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_Purchase.sort_values(by='total_Purchase', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}