# ---
# 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)
purchase_data.head()
# -

# ## Player Count

# * Display the total number of players
#

# +
# Calculate total players
total_players = len(pd.unique(pd.Series(purchase_data["SN"])))
total = {"Total Players": [total_players]}

total_df=pd.DataFrame(total, columns=["Total Players"])
total_df
# -

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

# +
# Purchasing Analysis (Total)
number_of_unique_items = purchase_data["Item ID"].max()
average_price = purchase_data["Price"].mean()
total_num_of_purchases = purchase_data["Purchase ID"].count()
total_revenue = purchase_data["Price"].sum()
summary = {"Number of Unique Items": [number_of_unique_items], 
           "Average Purchase Price": [average_price],
           "Total Number of Purchases": [total_num_of_purchases], 
           "Total Revenue": [total_revenue]
}

summary_df = pd.DataFrame(summary, columns=["Number of Unique Items","Average Purchase Price",
                                          "Total Number of Purchases","Total Revenue"
])
summary_df['Average Purchase Price'] = summary_df['Average Purchase Price'].map('${:.2f}'.format)
summary_df['Total Revenue'] = summary_df['Total Revenue'].map('${:.2f}'.format)
summary_df
# -

# ## Gender Demographics

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

# +
# Gender Demographics
male = purchase_data 
male_data = male.loc[male["Gender"]=="Male",:]
male_count = len(male_data["SN"].unique())
male_percent = (male_count/total_players)*100

female = purchase_data 
female_data = female.loc[female["Gender"]=="Female",:]
female_count = len(female_data["SN"].unique())
female_percent = (female_count/total_players)*100

other = purchase_data 
other_data = other.loc[other["Gender"]=="Other / Non-Disclosed",:]
other_count = len(other_data["SN"].unique())
other_percent = (other_count/total_players)*100

gender_demographics = {"Total Count": [male_count, female_count, other_count], 
           "Percentage of Players": [male_percent, female_percent, other_percent]
}
gender_demographics_df = pd.DataFrame(gender_demographics, columns=["Total Count", "Percentage of Players"])
gender_demographics_df = pd.DataFrame(gender_demographics, index = ["Male", "Female", "Other / Non-Disclosed"])
gender_demographics_df['Percentage of Players'] = gender_demographics_df['Percentage of Players'].map('{:.2f}%'.format)
gender_demographics_df                                      
# -

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

# +
# Purchasing Analysis (Gender)
male_purchase = len(male_data["Gender"])
male_avg_price = male_data["Price"].mean()
male_total = male_data['Price'].sum()
male_avg_person = male_total/male_count

female_purchase = len(female_data["Gender"])
female_avg_price = female_data["Price"].mean()
female_total = female_data['Price'].sum()
female_avg_person = female_total/female_count

other_purchase = len(other_data["Gender"])
other_avg_price = other_data["Price"].mean()
other_total = other_data['Price'].sum()
other_avg_person = other_total/other_count

purchase_analysis = {"Purchase Count": [male_purchase, female_purchase, other_purchase], 
           "Average Purchase Price": [male_avg_price, female_avg_price, other_avg_price],
                     "Total Purchase Value":[male_total, female_total, other_total],
                     "Avg. Total Purchase per Person": [male_avg_person, female_avg_person, other_avg_person]
}

purchase_analysis_df = pd.DataFrame(purchase_analysis, columns=["Purchase Count",
                                                                "Average Purchase Price",
                                                                "Total Purchase Value",
                                                               "Avg. Total Purchase per Person"]
)
purchase_analysis_df = pd.DataFrame(purchase_analysis, index = ["Male", "Female", "Other / Non-Disclosed"])
purchase_analysis_df['Average Purchase Price'] = purchase_analysis_df['Average Purchase Price'].map('${:.2f}'.format)
purchase_analysis_df['Total Purchase Value'] = purchase_analysis_df['Total Purchase Value'].map('${:.2f}'.format)
purchase_analysis_df['Avg. Total Purchase per Person'] = purchase_analysis_df['Avg. Total Purchase per Person'].map('${:.2f}'.format)
purchase_analysis_df
# -

# ## 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 Demographics
age_demographics = purchase_data
bins = [0,9,14,19,24,29,34,39,150]
age_labels = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", "40+"]
age_demographics["Age Groups"] = pd.cut(purchase_data["Age"], bins, labels=age_labels)
for x in age_demographics:
    a_data = age_demographics.loc[age_demographics["Age Groups"]=="<10",:]
    a_count = len(a_data["SN"].unique())
    a_percent = (a_count/total_players)*100
    b_data = age_demographics.loc[age_demographics["Age Groups"]=="10-14",:]
    b_count = len(b_data["SN"].unique())
    b_percent = (b_count/total_players)*100
    c_data = age_demographics.loc[age_demographics["Age Groups"]=="15-19",:]
    c_count = len(c_data["SN"].unique())
    c_percent = (c_count/total_players)*100
    d_data = age_demographics.loc[age_demographics["Age Groups"]=="20-24",:]
    d_count = len(d_data["SN"].unique())
    d_percent = (d_count/total_players)*100    
    e_data = age_demographics.loc[age_demographics["Age Groups"]=="25-29",:]
    e_count = len(e_data["SN"].unique())
    e_percent = (e_count/total_players)*100
    f_data = age_demographics.loc[age_demographics["Age Groups"]=="30-34",:]
    f_count = len(f_data["SN"].unique())
    f_percent = (f_count/total_players)*100        
    g_data = age_demographics.loc[age_demographics["Age Groups"]=="35-39",:]
    g_count = len(g_data["SN"].unique())
    g_percent = (g_count/total_players)*100
    h_data = age_demographics.loc[age_demographics["Age Groups"]=="40+",:]
    h_count = len(h_data["SN"].unique())
    h_percent = (h_count/total_players)*100    

age_dem = {"Total Count": [a_count, b_count, c_count,d_count,e_count,f_count,g_count,h_count], 
           "Percentage of Players": [a_percent, b_percent, c_percent,d_percent,e_percent,f_percent,g_percent,h_percent],
}

age_demographics_df = pd.DataFrame(age_dem, columns=["Total Count", "Percentage of Players"])
age_demographics_df = pd.DataFrame(age_dem, index = ["<10", "10-14", "15-19", "20-24","25-29", "30-34", "35-39", "40+"])
age_demographics_df['Percentage of Players'] = age_demographics_df['Percentage of Players'].map('{:.2f}%'.format)
age_demographics_df
# -

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

# +
# Purchasing Analysis (Age)
a_purchase = len(a_data["Gender"])
a_avg_price = a_data["Price"].mean()
a_total = a_data['Price'].sum()
a_avg_person = a_total/a_count

b_purchase = len(b_data["Gender"])
b_avg_price = b_data["Price"].mean()
b_total = b_data['Price'].sum()
b_avg_person = b_total/b_count

c_purchase = len(c_data["Gender"])
c_avg_price = c_data["Price"].mean()
c_total = c_data['Price'].sum()
c_avg_person = c_total/c_count

d_purchase = len(d_data["Gender"])
d_avg_price = d_data["Price"].mean()
d_total = d_data['Price'].sum()
d_avg_person = d_total/d_count

e_purchase = len(e_data["Gender"])
e_avg_price = e_data["Price"].mean()
e_total = e_data['Price'].sum()
e_avg_person = e_total/e_count

f_purchase = len(f_data["Gender"])
f_avg_price = f_data["Price"].mean()
f_total = f_data['Price'].sum()
f_avg_person = f_total/f_count

g_purchase = len(g_data["Gender"])
g_avg_price = g_data["Price"].mean()
g_total = g_data['Price'].sum()
g_avg_person = g_total/g_count

h_purchase = len(h_data["Gender"])
h_avg_price = h_data["Price"].mean()
h_total = h_data['Price'].sum()
h_avg_person = h_total/h_count

age_analysis = {"Purchase Count": [a_purchase, b_purchase, c_purchase, d_purchase,e_purchase,f_purchase,g_purchase,h_purchase], 
           "Average Purchase Price": [a_avg_price, b_avg_price, c_avg_price,d_avg_price,e_avg_price,f_avg_price,g_avg_price,h_avg_price ],
                     "Total Purchase Value":[a_total, b_total, c_total,d_total,e_total,f_total,g_total,h_total],
                     "Avg. Total Purchase per Person": [a_avg_person, b_avg_person, c_avg_person,d_avg_person,e_avg_person,f_avg_person,g_avg_person,h_avg_person]
}

age_analysis_df = pd.DataFrame(age_analysis, columns=["Purchase Count",
                                                                "Average Purchase Price",
                                                                "Total Purchase Value",
                                                               "Avg. Total Purchase per Person"]
)
age_analysis_df = pd.DataFrame(age_analysis, index = ["<10", "10-14", "15-19", "20-24","25-29", "30-34", "35-39", "40+"])
age_analysis_df['Average Purchase Price'] = age_analysis_df['Average Purchase Price'].map('${:.2f}'.format)
age_analysis_df['Total Purchase Value'] = age_analysis_df['Total Purchase Value'].map('${:.2f}'.format)
age_analysis_df['Avg. Total Purchase per Person'] = age_analysis_df['Avg. Total Purchase per Person'].map('${:.2f}'.format)
age_analysis_df
# -

# ## 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 Spenders
grouped_purchase_data = purchase_data.groupby(["SN"])

purchase_count = pd.DataFrame(grouped_purchase_data["SN"].count())
purchase_count = purchase_count.rename(columns={"SN": "Purchase Count"})
purchase_count=purchase_count.reset_index(drop=False)

avg_purchase_price = pd.DataFrame(grouped_purchase_data["Price"].mean())
avg_purchase_price=avg_purchase_price.reset_index(drop=False)
avg_purchase_price=avg_purchase_price.rename(columns={"Price": "Average Purchase Price"})

total_purchase = pd.DataFrame(grouped_purchase_data["Price"].sum())
total_purchase=total_purchase.reset_index(drop=False)
total_purchase=total_purchase.rename(columns={"Price": "Total Purchase Value"})

top_spenders = pd.merge(purchase_count, avg_purchase_price, on="SN")
top_spenders_df = pd.merge(top_spenders, total_purchase, on="SN")
top_spenders_df = top_spenders_df.set_index('SN')

summary = top_spenders_df.sort_values(['Total Purchase Value'], ascending=False)
summary['Average Purchase Price'] = summary['Average Purchase Price'].map('${:.2f}'.format)
summary['Total Purchase Value'] = summary['Total Purchase Value'].map('${:.2f}'.format)
summary.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
#
#

# +
# Most Popular Items
most_pop=purchase_data[["Item ID", "Item Name", "Price"]]
item = most_pop.set_index(['Item Name'])
item = item.sort_values(by="Item ID", ascending=True)
item_df = item.drop_duplicates(["Item ID"])
item_price = list(item_df["Price"])

grouped_most_pop = most_pop.groupby(["Item ID","Item Name"])

most_pop_count = pd.DataFrame(grouped_most_pop["Price"].count())
most_pop_count = most_pop_count.rename(columns={"Price": "Purchase Count"})
most_pop_count=most_pop_count.reset_index(drop=False)
most_pop_count=most_pop_count.set_index("Item ID")

total_purchase_pop = pd.DataFrame(grouped_most_pop["Price"].sum())
total_purchase_pop=total_purchase_pop.rename(columns={"Price": "Total Purchase Value"})
total_purchase_pop=total_purchase_pop.reset_index(drop=False)
purchase_value =list(total_purchase_pop["Total Purchase Value"])

most_pop_count["Item Price"] = item_price
most_pop_count["Total Purchase Value"] = purchase_value
most_pop_count=most_pop_count.reset_index(drop=False)
most_pop_count = most_pop_count.set_index(["Item ID", "Item Name"])
most_pop_count = most_pop_count.sort_values(by="Purchase Count", ascending=False)
most_pop_count['Item Price'] = most_pop_count['Item Price'].map('${:.2f}'.format)
most_pop_count['Total Purchase Value'] = most_pop_count['Total Purchase Value'].map('${:.2f}'.format)
most_pop_count
# -

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

# +
# Most Profitable Items
most_pop=purchase_data[["Item ID", "Item Name", "Price"]]
item = most_pop.set_index(['Item Name'])
item = item.sort_values(by="Item ID", ascending=True)
item_df = item.drop_duplicates(["Item ID"])
item_price = list(item_df["Price"])

grouped_most_pop = most_pop.groupby(["Item ID","Item Name"])

most_pop_count = pd.DataFrame(grouped_most_pop["Price"].count())
most_pop_count = most_pop_count.rename(columns={"Price": "Purchase Count"})
most_pop_count=most_pop_count.reset_index(drop=False)
most_pop_count=most_pop_count.set_index("Item ID")

total_purchase_pop = pd.DataFrame(grouped_most_pop["Price"].sum())
total_purchase_pop=total_purchase_pop.rename(columns={"Price": "Total Purchase Value"})
total_purchase_pop=total_purchase_pop.reset_index(drop=False)
purchase_value =list(total_purchase_pop["Total Purchase Value"])

most_pop_count["Item Price"] = item_price
most_pop_count["Total Purchase Value"] = purchase_value
most_pop_count=most_pop_count.reset_index(drop=False)
most_pop_count = most_pop_count.set_index(["Item ID", "Item Name"])
most_pop_count = most_pop_count.sort_values(by="Total Purchase Value", ascending=False)
most_pop_count['Item Price'] = most_pop_count['Item Price'].map('${:.2f}'.format)
most_pop_count['Total Purchase Value'] = most_pop_count['Total Purchase Value'].map('${:.2f}'.format)
most_pop_count

# +
# My three observable trends based on the data are:
#    1.There are more Male players, age demographics shows high number of players aged between 20-24.
#    2.Oathbreaker, Last Hope of the Breaking Storm is the most popular and profitable item, 
#      the second place of popularity devided between another three items.
#      I would suggest to review profitable items and add more similar items in order to increase profit.
#    3.The players age is increasing with max at 24, then again dropping down. Players in 25-29 and 30-34 age groups
#      spend enough money in order to consider their prioritized items for fututre changes.

StarcoderdataJupyterScriptsDedupFiltered