# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernel_info:
#     name: python3
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ### Note
# * 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.

# +
# Dependencies and Setup
import pandas as pd
import numpy as np

# File to Load (Remember to Change These)
file_to_load = "Resources/purchase_data.csv"

# Read Purchasing File and store into Pandas data frame
purchase_data = pd.read_csv(file_to_load)
purchase_data.head()
# -

# ## Player Count

# * Display the total number of players
#

purchase_data['SN'].nunique()

# ## Purchasing Analysis (Total)

# * Run basic calculations to obtain number of unique items, average price, etc.
#
#
# * Create a summary data frame to hold the results
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display the summary data frame
#

# +
numberofuniqueitem=purchase_data['Item ID'].nunique()
averageprice = round(purchase_data["Price"].mean(),2)
numberofpurchases = purchase_data["Purchase ID"].count()
TotalRevenue = purchase_data['Price'].sum()


summary_table = pd.DataFrame({"Number of Unique Items": [numberofuniqueitem],
                              "Average Price": [averageprice],
                              "Number of Purchases": numberofpurchases,
                               "Total Revenue": TotalRevenue })
summary_table["Average Price"] = summary_table["Average Price"].astype(float).map(
    "${:,.2f}".format) 
summary_table["TotalRevenue"] = summary_table["Total Revenue"].astype(float).map(
    "${:,.2f}".format)                             
summary_table


# -

# ## Gender Demographics

# * Percentage and Count of Male Players
#
#
# * Percentage and Count of Female Players
#
#
# * Percentage and Count of Other / Non-Disclosed
#
#
#

# +
# Male = purchase_data.query('Gender == "Male"')['SN'].agg(['nunique'])
# MalePercent = round( (Male * 100) / (purchase_data['SN'].nunique()),2 )
# MalePercent = MalePercent.astype(str) + '%'
# Female = purchase_data.query('Gender == "Female"')['SN'].agg(['nunique'])
# FemalePercent = round( (Female * 100) / (purchase_data['SN'].nunique()),2 )
# FemalePercent = FemalePercent.astype(str) + '%'
# Other = purchase_data.query('Gender == "Other / Non-Disclosed"')['SN'].agg(['nunique'])
# OtherPercent = round( (Other * 100) / (purchase_data['SN'].nunique()),2 )
# OtherPercent = OtherPercent.astype(str) + '%'

# Genderdf = pd.DataFrame({
#          'Gender': ['Male', 'Female', 'Other / Non-Disclosed'],
#          'Total Count': [Male,Female,Other],
#          'Percentage of Players': [MalePercent,FemalePercent,OtherPercent],
# })

# Genderdf

gender = purchase_data.groupby("Gender")["SN"].nunique()
count = gender/purchase_data['SN'].nunique()
percent = count.mul(100).round(2).astype(str) + '%'

summary_table = pd.DataFrame({
                              "Total Count": gender,
                               "Percentage of Players": percent })    
summary_table = summary_table.sort_values(by = ['Total Count'], ascending=[False])

summary_table
# -

#
# ## Purchasing Analysis (Gender)

# * Run basic calculations to obtain purchase count, avg. purchase price, avg. purchase total per person etc. by gender
#
#
#
#
# * Create a summary data frame to hold the results
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display the summary data frame

# +
count = purchase_data.groupby(['Gender'])['Purchase ID'].count()
average = purchase_data.groupby(['Gender'])['Price'].mean().round(2)
total = purchase_data.groupby(['Gender'])['Price'].sum()

AvgRangeTotal = (total / purchase_data.groupby("Gender")["SN"].nunique() ).round(2)

summary_table = pd.DataFrame({"Purchase Count": count,
                            "Average Purchase Price": average ,
                             "Total Purchase Value": total,
                             "Avg Total Purchase per Person" : AvgRangeTotal })
summary_table["Average Purchase Price"] = summary_table["Average Purchase Price"].astype(float).map(
    "${:,.2f}".format) 
summary_table["Total Purchase Value"] = summary_table["Total Purchase Value"].astype(float).map(
    "${:,.2f}".format) 
summary_table["Avg Total Purchase per Person"] = summary_table["Avg Total Purchase per Person"].astype(float).map(
    "${:,.2f}".format) 
summary_table

# -

# ## Age Demographics

# * Establish bins for ages
#
#
# * Categorize the existing players using the age bins. Hint: use pd.cut()
#
#
# * Calculate the numbers and percentages by age group
#
#
# * Create a summary data frame to hold the results
#
#
# * Optional: round the percentage column to two decimal points
#
#
# * Display Age Demographics Table
#

# +
bins = [0,9,14, 19, 24, 29,34,39,1000]

group_names = ["<10","10-14","15-19","20-24","25-29","30-34","35-39","+40"]
purchase_data["Age Ranges"] = pd.cut(purchase_data["Age"], bins, labels=group_names)


purchase_data_group = purchase_data.groupby("Age Ranges")
age_group_total = purchase_data_group["SN"].nunique()


count = age_group_total/purchase_data['SN'].nunique()
percent = count.mul(100).round(2).astype(str) + '%'

summary_table = pd.DataFrame({
                              "Total Count": age_group_total,
                               "Percentage of Players": percent })                          
summary_table
# -

# ## Purchasing Analysis (Age)

# * Bin the purchase_data data frame by age
#
#
# * Run basic calculations to obtain purchase count, avg. purchase price, avg. purchase total per person etc. in the table below
#
#
# * Create a summary data frame to hold the results
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display the summary data frame

# +
count = purchase_data.groupby(['Age Ranges'])['Purchase ID'].count()
average = purchase_data.groupby(['Age Ranges'])['Price'].mean().round(2)
total = purchase_data.groupby(['Age Ranges'])['Price'].sum()

AvgRangeTotal = (total / purchase_data.groupby("Age Ranges")["SN"].nunique() ).round(2)

summary_table = pd.DataFrame({"Purchase Count": count,
                            "Average Purchase Price": average ,
                             "Total Purchase Value": total,
                             "Avg Total Purchase per Person": AvgRangeTotal })
summary_table["Average Purchase Price"] = summary_table["Average Purchase Price"].astype(float).map(
    "${:,.2f}".format) 
summary_table["Total Purchase Value"] = summary_table["Total Purchase Value"].astype(float).map(
    "${:,.2f}".format) 
summary_table["Avg Total Purchase per Person"] = summary_table["Avg Total Purchase per Person"].astype(float).map(
    "${:,.2f}".format) 
summary_table
# -

# ## Top Spenders

# * Run basic calculations to obtain the results in the table below
#
#
# * Create a summary data frame to hold the results
#
#
# * Sort the total purchase value column in descending order
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display a preview of the summary data frame
#
#

# +

new_df = purchase_data.groupby('SN').agg({'Purchase ID' : 'count','Price':'sum' })
avg = (new_df['Price'] / new_df['Purchase ID'] ). round(2)
new_df['Average Purchase Price'] = avg 
new_df = new_df.rename(columns={"Purchase ID": "Purchase Count", "Price": "Total Purchase Value"})
new_df = new_df.sort_values(by = ['Total Purchase Value'], ascending=[False])

new_df = new_df[['Purchase Count', 'Average Purchase Price', 'Total Purchase Value']]


new_df["Total Purchase Value"] = new_df["Total Purchase Value"].astype(float).map("${:,.2f}".format) 
new_df["Average Purchase Price"] = new_df["Average Purchase Price"].astype(float).map("${:,.2f}".format) 

new_df.head()
# -

# ## Most Popular Items

# * Retrieve the Item ID, Item Name, and Item Price columns
#
#
# * Group by Item ID and Item Name. Perform calculations to obtain purchase count, item price, and total purchase value
#
#
# * Create a summary data frame to hold the results
#
#
# * Sort the purchase count column in descending order
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display a preview of the summary data frame
#
#

# +
new_df = purchase_data.groupby(['Item ID','Item Name']).agg({'Purchase ID' : 'count','Price':'sum' })
avg = (new_df['Price'] / new_df['Purchase ID'] ). round(2)
new_df['Average Purchase Price'] = avg 
new_df = new_df.rename(columns={"Purchase ID": "Purchase Count", "Price": "Total Purchase Value"})
new_df = new_df.sort_values(by = ['Purchase Count'], ascending=[False])

new_df = new_df[['Purchase Count', 'Average Purchase Price', 'Total Purchase Value']]


new_df["Total Purchase Value"] = new_df["Total Purchase Value"].astype(float).map("${:,.2f}".format) 
new_df["Average Purchase Price"] = new_df["Average Purchase Price"].astype(float).map("${:,.2f}".format) 

new_df.head()
# -

# ## Most Profitable Items

# * Sort the above table by total purchase value in descending order
#
#
# * Optional: give the displayed data cleaner formatting
#
#
# * Display a preview of the data frame
#
#

# +
new_df = purchase_data.groupby(['Item ID','Item Name']).agg({'Purchase ID' : 'count','Price':'sum' })
avg = (new_df['Price'] / new_df['Purchase ID'] ). round(2)
new_df['Average Purchase Price'] = avg 
new_df = new_df.rename(columns={"Purchase ID": "Purchase Count", "Price": "Total Purchase Value"})
new_df = new_df.sort_values(by = ['Total Purchase Value'], ascending=[False])

new_df = new_df[['Purchase Count', 'Average Purchase Price', 'Total Purchase Value']]


new_df["Total Purchase Value"] = new_df["Total Purchase Value"].astype(float).map("${:,.2f}".format) 
new_df["Average Purchase Price"] = new_df["Average Purchase Price"].astype(float).map("${:,.2f}".format) 

new_df.head()
# -



StarcoderdataJupyterScriptsDedupFiltered