# ---
# 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

# 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)
# -

# ## Player Count

# * Display the total number of players
#

# +
#create player information
player_info=purchase_data.loc[:,['Gender','Age','SN']]

# remove duplicates
player_info=player_info.drop_duplicates()

#create dataframe
player_total=player_info.count()[0]
total_players=pd.DataFrame({'Total Players':[player_total]})
total_players
# -

# ## 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
#

# +
#Identify number of unique items
Unique_items = len(purchase_data["Item Name"].unique())

#averge Purchase Price
Avg_price = purchase_data["Price"].mean()

#Calculating the Total Number of Purchases

Number_purchases = len(purchase_data["Item Name"])

#Calculate Total Revenue
Revenue = purchase_data["Price"].sum()

#Create data frame
purchase_anaylsis=pd.DataFrame({'Number of Unique Items': [Unique_items],
                               'Total Revenue': [Revenue],
                               'Number of Purchases': [Number_purchases],
                               'Average Purchase Price': [Avg_price]})

#Formatting the data
purchase_anaylsis = purchase_anaylsis.round(2)
purchase_anaylsis['Average Purchase Price']= purchase_anaylsis['Average Purchase Price'].map('${:,.2f}'.format)
purchase_anaylsis['Total Revenue']=purchase_anaylsis['Total Revenue'].map('${:,.2f}'.format)
purchase_anaylsis = purchase_anaylsis.loc[:,['Number of Unique Items',
                                             'Average Purchase Price', 
                                             'Number of Purchases',
                                            'Total Revenue']]
purchase_anaylsis

# -

# ## Gender Demographics

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

# +
# Basic Calculations
gender_demo = player_info["Gender"].value_counts()
gender_demo_percents = player_info['Gender'].value_counts(normalize=True).mul(100).round(2).astype(str) + '%'

# Data Cleanup
gender_demo = pd.DataFrame({"Total Count": gender_demo,
                                    "Percentage of Players": gender_demo_percents})

gender_demo = gender_demo.round(2)

#Data Gender_demographics
gender_demo
# -

#
# ## 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

# +
# Create Gender Group
gender = purchase_data.groupby(["Gender"])

# Data for Table
purchase_count = gender["SN"].count()

purchase_price = gender["Price"].mean()

purchase_value = gender["Price"].sum()

# Remove all duplicates
duplicates = purchase_data.drop_duplicates(subset='SN', keep="first")
new_gender = duplicates.groupby(["Gender"])

# Find normalized data
purchase_average = (gender["Price"].sum() / new_gender["SN"].count())

# Create new DataFrame
gender_analysis = pd.DataFrame({"Purchase Count": purchase_count,
                              "Average Purchase Price": purchase_price,
                              "Total Purchase Value": purchase_value,
                              "Average Purchase Total by Person": purchase_average})
# DataFrame formatting
gender_analysis["Average Purchase Price"] = gender_analysis["Average Purchase Price"].map("${:.2f}".format)
gender_analysis["Total Purchase Value"] = gender_analysis["Total Purchase Value"].map("${:.2f}".format)
gender_analysis["Average Purchase Total by Person"] = gender_analysis["Average Purchase Total by Person"].map("${:.2f}".format)
gender_analysis = gender_analysis[["Purchase Count", "Average Purchase Price", 
                                   "Total Purchase Value", "Average Purchase Total by Person"]]
gender_analysis
# -

# ## 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
#

# +
age_bins = [0, 9.90, 14.90, 19.90, 24.9, 29.9, 34.90, 39.90, 9999999]
group_names = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", ">40"]

#Use Cut to categorize players
player_info["Age Ranges"] = pd.cut(player_info["Age"], age_bins, labels=group_names)

# Create the table data
age_demo_totals = player_info["Age Ranges"].value_counts()
age_demo_percents = ((age_demo_totals / player_total) * 100).round(2).astype(str) + '%'

age_demographics = pd.DataFrame({"Total Count": age_demo_totals, "Percent of Players": age_demo_percents})
age_demographics = age_demographics.sort_index()
age_demographics
# -

# ## 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

# +
# Binning
age_bins = [0, 9.90, 14.90, 19.90, 24.90, 29.90, 34.90, 39.90, 99999]
binLabel = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", ">40"]

# Add bins to new dataframe and groupby
bin_df = purchase_data.copy()
bin_df["Age Groups"] = pd.cut(bin_df["Age"], age_bins, labels=binLabel)
binColumn = pd.cut(bin_df["Age"], age_bins, labels=binLabel)
grouped_bin = bin_df.groupby(["Age Groups"])

# Data for table
binPurchCount = grouped_bin["Age"].count()
binPurchAver = grouped_bin["Price"].mean()
binPurchTotal = grouped_bin["Price"].sum()

# deleting duplicates for new counts
binduplicate = purchase_data.drop_duplicates(subset='SN', keep="first")
binduplicate["Age Groups"] = pd.cut(binduplicate["Age"], age_bins, labels=binLabel)
binduplicate = binduplicate.groupby(["Age Groups"])

binTPPP = (grouped_bin["Price"].sum() / binduplicate["SN"].count())
binTPPP

# Create new DF and format
Age_Demo = pd.DataFrame({"Purchase Count": binPurchCount,
                         "Average Purchase Price": binPurchAver,
                         "Total Purchase Value": binPurchTotal,
                         "Avg Price Per Person Totals": binTPPP})

Age_Demo["Average Purchase Price"] = Age_Demo["Average Purchase Price"].map("${:.2f}".format)
Age_Demo["Total Purchase Value"] = Age_Demo["Total Purchase Value"].map("${:.2f}".format)
Age_Demo["Avg Price Per Person Totals"] = Age_Demo["Avg Price Per Person Totals"].map("${:.2f}".format)
Age_Demo = Age_Demo[["Purchase Count", "Average Purchase Price", "Total Purchase Value", "Avg Price Per Person Totals"]]
Age_Demo.head(10)
# -

# ## 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
#
#

# +
top_spend = purchase_data['Item ID'].groupby(purchase_data['SN']).count()

# set the dataframe for top spender 
top_spend = pd.DataFrame(data=top_spend)
top_spend.columns = ['Purchase Count']

top_spend['Average Purchase Price'] = round(purchase_data['Price'].groupby(purchase_data['SN']).mean(),2)
top_spend['Total Purchase Value'] = purchase_data['Price'].groupby(purchase_data['SN']).sum()


top_spend.sort_values(by=['Total Purchase Value'], ascending=False, inplace=True)

top_spend['Average Purchase Price'] = top_spend['Average Purchase Price'].map('${:,.2f}'.format)
top_spend['Total Purchase Value'] = top_spend['Total Purchase Value'].map('${:,.2f}'.format)

top_spend.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, average 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
#
#

# +
# Data for table
groupItem = purchase_data.groupby(["Item ID", "Item Name"])
groupItemCount = groupItem["SN"].count()
groupPriceSum = groupItem["Price"].sum()
groupItemPPP = (groupPriceSum / groupItemCount)
groupItemValue = (groupItemPPP * groupItemCount)

# New DF with formatting
Popular_Item = pd.DataFrame({"Purchase Count": groupItemCount,
                          "Item Price": groupItemPPP,
                          "Total Purchase Value": groupItemValue})

Popular_Item = Popular_Item.sort_values("Purchase Count", ascending=False) 
Popular_Item["Item Price"] = Popular_Item["Item Price"].map("${:.2f}".format)
Popular_Item["Total Purchase Value"] = Popular_Item["Total Purchase Value"].map("${:.2f}".format)
Popular_Item = Popular_Item[["Purchase Count", "Item Price", "Total Purchase Value"]]
Popular_Item.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
#
#

# +
# Data for table
groupedItem = purchase_data.groupby(["Item ID", "Item Name"])
groupedItemCount = groupedItem["Gender"].count()
groupedSum = groupedItem["Price"].sum()
groupedItemPPP = (groupedSum / groupedItemCount)

# Make a new DF and format
Prof_Val = pd.DataFrame({"Purchase Count": groupedItemCount,
                          "Item Price": groupedItemPPP,
                          "Total Purchase Value": groupedSum})

Prof_Val = Prof_Val.sort_values("Total Purchase Value", ascending=False) 
Prof_Val["Item Price"] = Prof_Val["Item Price"].map("${:.2f}".format)
Prof_Val["Total Purchase Value"] = Prof_Val["Total Purchase Value"].map("${:.2f}".format)
Prof_Val = Prof_Val[["Purchase Count", "Item Price", "Total Purchase Value"]]
Prof_Val.head()

StarcoderdataJupyterScriptsDedupFiltered