<jupyter_start><jupyter_text>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.<jupyter_code># Dependencies and Setup
import pandas as pd
import numpy as np

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

# Read Purchasing File and store into Pandas data frame
purchase_data = pd.read_csv(purchase_data)
purchase_data.head()<jupyter_output><empty_output><jupyter_text>Player Count * Display the total number of players<jupyter_code>player_count = len(purchase_data["SN"].unique())
player_count

player_count_table = pd.DataFrame({"Total Players": [player_count]})
player_count_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>unique_items_count = len(purchase_data["Item ID"].unique())

avg_purchase_price = purchase_data["Price"].mean()

total_purchases = len(purchase_data["Purchase ID"].unique())

total_revenue = purchase_data["Price"].sum()

Data_table = pd.DataFrame([{
    "Number of Unique Items": unique_items_count, 
    "Average Price": avg_purchase_price,
    "Number of Purchases": total_purchases,
    "Total Revenue": total_revenue,
}], columns=["Number of Unique Items", "Average Price", "Number of Purchases", "Total Revenue"])

Data_table["Average Price"] = Data_table["Average Price"].map("${0:.2f}".format)
Data_table["Total Revenue"] = Data_table["Total Revenue"].map("${0:,.2f}".format)
Data_table<jupyter_output><empty_output><jupyter_text>Gender Demographics * Percentage and Count of Male Players* Percentage and Count of Female Players* Percentage and Count of Other / Non-Disclosed<jupyter_code>male_players = purchase_data.loc[purchase_data["Gender"] == "Male"]
male_count = len(male_players["SN"].unique())
male_percent = "{:.2f}%".format(male_count / player_count * 100)

female_players = purchase_data.loc[purchase_data["Gender"] == "Female"]
female_count = len(female_players["SN"].unique())
female_percent = "{:.2f}%".format(female_count / player_count * 100)

other_players = purchase_data.loc[purchase_data["Gender"] == "Other / Non-Disclosed"]
other_count = len(other_players["SN"].unique())
other_percent = "{:.2f}%".format(other_count / player_count * 100)

gender_demographics_table = pd.DataFrame([{
    "Gender": "Male", "Total Count": male_count, 
    "Percentage of Players": male_percent}, 
    {"Gender": "Female", "Total Count": female_count, 
     "Percentage of Players": female_percent}, 
    {"Gender": "Other / Non-Disclosed", "Total Count": other_count, 
     "Percentage of Players": other_percent
    }], columns=["Gender", "Total Count", "Percentage of Players"])


gender_demographics_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>female_purchase_data = purchase_data.loc[purchase_data["Gender"] == "Female", :]
female_purchase_count = len(female_purchase_data)

avg_female_purchase_price = purchase_data.loc[purchase_data["Gender"] == "Female", ["Price"]].mean()

total_female_purchase_value = purchase_data.loc[purchase_data["Gender"] == "Female", ["Price"]].sum()

male_purchase_data = purchase_data.loc[purchase_data["Gender"] == "Male", :]
male_purchase_count = len(male_purchase_data)

avg_male_purchase_price = purchase_data.loc[purchase_data["Gender"] == "Male", ["Price"]].mean()

total_male_purchase_value = purchase_data.loc[purchase_data["Gender"] == "Male", ["Price"]].sum()

other_purchase_data = purchase_data.loc[purchase_data["Gender"] == "Other / Non-Disclosed", :]
other_purchase_count = len(other_purchase_data)

avg_other_purchase_price = purchase_data.loc[purchase_data["Gender"] == "Other / Non-Disclosed", ["Price"]].mean()

total_other_purchase_value = purchase_data.loc[purchase_data["Gender"] == "Other / Non-Disclosed", ["Price"]].sum()

avg_male_purchase_total_person = total_male_purchase_value / male_count
avg_female_purchase_total_person = total_female_purchase_value / female_count
avg_other_purchase_total_person = total_other_purchase_value / other_count

gender_purchasing_analysis_table = pd.DataFrame([{
    "Gender": "Female", "Purchase Count": female_purchase_count, 
    "Average Purchase Price": "${:.2f}".format(avg_female_purchase_price[0]), 
    "Total Purchase Value": "${:.2f}".format(total_female_purchase_value[0]), 
    "Avg Total Purchase per Person": "${:.2f}".format(avg_female_purchase_total_person[0])}, 
    {"Gender": "Male", "Purchase Count": male_purchase_count, 
     "Average Purchase Price": "${:.2f}".format(avg_male_purchase_price[0]), 
     "Total Purchase Value": "${:,.2f}".format(total_male_purchase_value[0]), 
     "Avg Total Purchase per Person": "${:.2f}".format(avg_male_purchase_total_person[0])}, 
    {"Gender": "Other / Non-Disclosed", "Purchase Count": other_purchase_count, 
     "Average Purchase Price": "${:.2f}".format(avg_other_purchase_price[0]), 
     "Total Purchase Value": "${:.2f}".format(total_other_purchase_value[0]), 
     "Avg Total Purchase per Person": "${:.2f}".format(avg_other_purchase_total_person[0])
    }], columns=["Gender", "Purchase Count", "Average Purchase Price", "Total Purchase Value", "Avg Total Purchase per Person"])


gender_purchasing_analysis_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>age_bins = [0, 9, 14, 19, 24, 29, 34, 39]
groups_names = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39"]


purchase_data["Age Group"] = pd.cut(purchase_data["Age"], bins=age_bins, labels=groups_names)
purchase_data


age_group = purchase_data.groupby("Age Group")

total_count_age = age_group["SN"].nunique()

percentage_by_age = round(total_count_age / player_count * 100,2)

age_demographics_table = pd.DataFrame({
    "Total Count": total_count_age, 
    "Percentage of Players": percentage_by_age
})

age_demographics_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>bins = [0, 9, 14, 19, 24, 29, 34, 39]
groups_names = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39"]

purchase_data["Age Group"] = pd.cut(purchase_data["Age"], bins=age_bins, labels=groups_names)

age_purchase_count = age_group["SN"].count()

avg_age_purchase_price = round(age_group["Price"].mean(),2)

total_age_purchase_value = round(age_group["Price"].sum(),2)

avg_total_age_purchase_person = round(total_age_purchase_value / total_count_age,2)

age_purchasing_analysis_table = pd.DataFrame({
    "Purchase Count": age_purchase_count, 
    "Average Purchase Price": avg_age_purchase_price,
    "Total Purchase Value": total_age_purchase_value,
    "Avg Total Purchase per Person": avg_total_age_purchase_person
})


age_purchasing_analysis_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>bins = [0, 9, 14, 19, 24, 29, 34, 39]
groups_names = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39"]

purchase_data["Age Group"] = pd.cut(purchase_data["Age"], bins=age_bins, labels=groups_names)

age_purchase_count = age_group["SN"].count()

avg_age_purchase_price = round(age_group["Price"].mean(),2)

total_age_purchase_value = round(age_group["Price"].sum(),2)

avg_total_age_purchase_person = round(total_age_purchase_value / total_count_age,2)

age_purchasing_analysis_table = pd.DataFrame({
    "Purchase Count": age_purchase_count, 
    "Average Purchase Price": avg_age_purchase_price,
    "Total Purchase Value": total_age_purchase_value,
    "Avg Total Purchase per Person": avg_total_age_purchase_person
})

age_purchasing_analysis_table<jupyter_output><empty_output><jupyter_text>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<jupyter_code>top_spenders = purchase_data.groupby("SN")

spender_purchase_count = top_spenders["Purchase ID"].count()

average_spender_purchase_price = round(top_spenders["Price"].mean(),2)

total_spender_purchase_value = top_spenders["Price"].sum()

top_spenders_table = pd.DataFrame({ 
    "Purchase Count": spender_purchase_count,
    "Average Purchase Price": average_spender_purchase_price,
    "Total Purchase Value": total_spender_purchase_value
})


sort_top_spenders<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12496187086934418, 'fraction_numerical': 0.01677681748856126, 'mean_word_length': 6.837450199203187, 'pattern_counts': {'":': 42, '<': 40, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '168478', 'license': 'ADSL', 'n_tokens_mistral': 3394, 'n_tokens_neox': 3100, 'n_words': 771, 'path': 'HeroesOfPymoli/HeroesOfPymoli.ipynb', 'repo_name': 'michaeldduszynski/pandas-challenge', 'size': '9835'}