<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

# 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()<jupyter_output><empty_output><jupyter_text>Player Count<jupyter_code>players = len(purchase_data["SN"].value_counts())
total_players= {"Total Players": [players]}
total_players_df = pd.DataFrame(total_players)
total_players_df<jupyter_output><empty_output><jupyter_text>* Display the total number of 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<jupyter_code># Number of Unique Items
unique_items = len(purchase_data["Item ID"].value_counts())

# Average Price
avg_price = (purchase_data["Price"].mean())

# Number of Purchases
purchases = (purchase_data["Price"].count())

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

# Make DataFrame
purchase_totals = {"Number of Unique Items":[unique_items], "Average Price":[avg_price], "Number of Purchases":[purchases], "Total Revenue":[revenue]}
purchase_totals_df = pd.DataFrame(purchase_totals)

# Format
purchase_totals_df["Average Price"] = purchase_totals_df["Average Price"].map("${:.2f}".format)
purchase_totals_df["Total Revenue"] = purchase_totals_df["Total Revenue"].map("${:.2f}".format)
purchase_totals_df<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>gender_dem = purchase_data.groupby(["Gender"])
gender_dem.count()

# total counts by gender
male = len(purchase_data.loc[purchase_data["Gender"]=="Male", :])
female =len(purchase_data.loc[purchase_data["Gender"]=="Female", :])
other_non= len(purchase_data.loc[purchase_data["Gender"]=="Other / Non-Disclosed", :])

# percents
male_percent = (male/(male + female + other_non))*100
female_percent =(female/(male + female + other_non))*100
percent_other = (other_non/(male + female + other_non))*100

# Make DataFrame
gender_dem_df = pd.DataFrame([[male, male_percent],[female, female_percent], [other_non, percent_other]],
index=[ "Male", "Female","Other / Non-Disclosed"],
columns=["Total Count","Percentage of Players"])

# Format
gender_dem_df["Percentage of Players"]= gender_dem_df["Percentage of Players"].map("{:.2f}%".format)
gender_dem_df<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># Grouby
gender_dem.count()

# Average Purchase Price
female_avg_price = purchase_data.loc[purchase_data["Gender"]=="Female", "Price"].mean()
male_avg_price = purchase_data.loc[purchase_data["Gender"]=="Male", "Price"].mean()
non_avg_price = purchase_data.loc[purchase_data["Gender"]=="Other / Non-Disclosed", "Price"].mean()

# Total Purchase Value
female_tot_val = purchase_data.loc[purchase_data["Gender"]=="Female", "Price"].sum()
male_tot_val = purchase_data.loc[purchase_data["Gender"]=="Male", "Price"].sum()
non_tot_val = purchase_data.loc[purchase_data["Gender"]=="Other / Non-Disclosed", "Price"].sum()

# Average Purchase Total
female_avg_tot_per = female_tot_val / female
male_avg_tot_per = male_tot_val / male
non_avg_tot_per = non_tot_val / other_non

# Create DataFrame
per_gender_data_df = pd.DataFrame(
[[female, female_avg_price, female_tot_val, female_avg_tot_per],
[male, male_avg_price, male_tot_val, male_avg_tot_per],
[other_non, non_avg_price, non_tot_val, non_avg_tot_per]],
index=["Female","Male", "Other / Non-Disclosed"],
columns=["Purchase Count", "Average Purchase Price", "Total Purchase Value", "Avg Purchase Total Per Person"])

# Format dataframe columns
per_gender_data_df["Average Purchase Price"] = per_gender_data_df["Average Purchase Price"].map("${:.2f}".format)
per_gender_data_df["Total Purchase Value"] = per_gender_data_df["Total Purchase Value"].map("${:.2f}".format)
per_gender_data_df["Avg Purchase Total Per Person"] = per_gender_data_df["Avg Purchase Total Per Person"].map("${:.2f}".format)

per_gender_data_df<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># make bins for ages
age_bins = [0, 9.90, 14.90, 19.90, 24.90, 29.90, 34.90, 39.90, 99999]
group_names = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", "40+"]

purchase_data["Age"] = pd.cut(purchase_data["Age"], age_bins, labels=group_names)

# Columns
age_data = purchase_data.groupby(["Age"])
age_data.count()

# Totals Counts by Groups
under_10= len(purchase_data.loc[purchase_data["Age"]=="<10", :])
a10_14= len(purchase_data.loc[purchase_data["Age"]=="10-14", :])
a15_19= len(purchase_data.loc[purchase_data["Age"]=="15-19", :])
a20_24 = len(purchase_data.loc[purchase_data["Age"]=="20-24", :])
a25_29 = len(purchase_data.loc[purchase_data["Age"]=="25-29", :])
a30_34= len(purchase_data.loc[purchase_data["Age"]=="30-34", :])
a35_39 = len(purchase_data.loc[purchase_data["Age"]=="35-39", :])
over_40 = len(purchase_data.loc[purchase_data["Age"]=="40+", :])

# variable for total
total_age = under_10+ a10_14+ a15_19+ a20_24+ a25_29+ a30_34+ a35_39+ over_40

# percentages by groups
p_under10 = (under_10/total_age)*100
p10_14 = (a10_14/total_age)*100
p15_19 = (a15_19/total_age)*100
p20_24 = (a20_24/total_age)*100
p25_29 = (a25_29/total_age)*100
p30_34= (a30_34/total_age)*100
p35_39 = (a35_39/total_age)*100
p_over40 = (over_40/total_age)*100
# create DataFrame
age_data_df = pd.DataFrame(
[[under_10, p_under10],[a10_14,p10_14], [a15_19,p15_19], [a20_24, p20_24],
 [a25_29, p25_29],[a30_34, p30_34],[a35_39, p35_39], [over_40, p_over40]],
index=["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39","40+"],
columns=["Total Count", "Percentage of Players"])

# format
age_data_df["Percentage of Players"]= age_data_df["Percentage of Players"].map("{:.2f}%".format)
age_data_df<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>age_data.count()
# average purchase price
u10_avg_price = purchase_data.loc[purchase_data["Age"]=="<10", "Price"].mean()
age1014_avg_price = purchase_data.loc[purchase_data["Age"]=="10-14", "Price"].mean()
age1519_avg_price = purchase_data.loc[purchase_data["Age"]=="15-19", "Price"].mean()
age2024_avg_price = purchase_data.loc[purchase_data["Age"]=="20-24", "Price"].mean()
age2529_avg_price = purchase_data.loc[purchase_data["Age"]=="25-29", "Price"].mean()
age3034_avg_price = purchase_data.loc[purchase_data["Age"]=="30-34", "Price"].mean()
age3539_avg_price = purchase_data.loc[purchase_data["Age"]=="35-39", "Price"].mean()
age40_avg_price = purchase_data.loc[purchase_data["Age"]=="40+", "Price"].mean()

# Total Purchase Values of each group
u10_total=purchase_data.loc[purchase_data["Age"]=="<10", "Price"].sum()
age1014_total = purchase_data.loc[purchase_data["Age"]=="10-14", "Price"].sum()
age1519_total = purchase_data.loc[purchase_data["Age"]=="15-19", "Price"].sum()
age2024_total = purchase_data.loc[purchase_data["Age"]=="20-24", "Price"].sum()
age2529_total = purchase_data.loc[purchase_data["Age"]=="25-29", "Price"].sum()
age3034_total = purchase_data.loc[purchase_data["Age"]=="30-34", "Price"].sum()
age3539_total = purchase_data.loc[purchase_data["Age"]=="35-39", "Price"].sum()
age40_total = purchase_data.loc[purchase_data["Age"]=="40+", "Price"].sum()

# Average Total Purchase per
u10_avg_tot = u10_total/ under_10
a1014_avg_tot = age1014_total/a10_14
a1519_avg_tot = age1519_total/a15_19
a2024_avg_tot = age2024_total/a20_24
a2529_avg_tot = age2529_total/a25_29
a3034_avg_tot = age3034_total/a30_34
a3539_avg_tot = age3539_total/a35_39
a40_avg_tot = age40_total/over_40

# Make DataFrame
per_age_data_df= pd.DataFrame(
[[under_10, u10_avg_price, u10_total, u10_avg_tot],
[a10_14, age1014_avg_price, age1014_total, a1014_avg_tot],
[a15_19, age1519_avg_price, age1519_total, a1519_avg_tot],
[a20_24, age2024_avg_price, age2024_total, a2024_avg_tot],
[a25_29, age2529_avg_price, age2529_total, a2024_avg_tot],
[a30_34, age3034_avg_price, age3034_total, a3034_avg_tot],
[over_40, age40_avg_price, age40_total, a40_avg_tot]],
index=["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", "40+"],
columns= ["Purchase Count", "Average Purchase Price", "Total Purchase Value", "Avg Total Purchase per Person"])

# Format
per_age_data_df["Average Purchase Price"] = per_age_data_df["Average Purchase Price"].map("${:.2f}".format)
per_age_data_df["Total Purchase Value"] = per_age_data_df["Total Purchase Value"].map("${:.2f}".format)
per_age_data_df["Avg Purchase Total Per Person"] = per_age_data_df["Avg Purchase Total Per Person"].map("${:.2f}".format)
per_age_data_df<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>total_purchase = purchase_data.groupby("SN")["Price"].sum()
purchase_count = purchase_data.groupby("SN")["Price"].count()
purchase_avg = purchase_data.groupby("SN")["Price"].mean()

# sorting total purchase value
spender_df = pd.DataFrame({"Purchase Count": purchase_count, "Average Purchase Price": purchase_avg, "Total Purchase Value": total_purchase})

# format
spender_df["Average Purchase Price"] = spender_df["Average Purchase Price"].map("${:.2f}".format)
spender_df["Total Purchase Value"] = spender_df["Total Purchase Value"].map("${:.2f}".format)
spender_df.head()<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>pop_tot = purchase_data.groupby("Item Name")["Price"].sum()
pop_count = purchase_data.groupby("Item Name")["Price"].count()

# mean
pop_price = purchase_data.groupby("Item Name")["Price"].mean()

# make DataFrame
pop_df = pd.DataFrame({"Purchase Count": pop_count, "Avg Item Price": pop_price, "Total Purchase Value": pop_tot})

# sort by count
pop_df.sort_values("Purchase Count", ascending = False, inplace=True)

# format
pop_df["Avg Item Price"] = pop_df["Avg Item Price"].map("${:.2f}".format)
pop_df["Total Purchase Value"] = pop_df["Total Purchase Value"].map("${:.2f}".format)
pop_df.head()<jupyter_output><empty_output><jupyter_text>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<jupyter_code># make new DataFrame
prof_df = pd.DataFrame({"Purchase Count": pop_count, "Avg Item Price": pop_price, "Total Purchase Value": pop_tot})

# sort by total purchase value
prof_df.sort_values("Total Purchase Value", ascending = False, inplace=True)

# format
prof_df["Avg Item Price"] = prof_df["Avg Item Price"].map("${:.2f}".format)
prof_df["Total Purchase Value"] = prof_df["Total Purchase Value"].map("${:.2f}".format)
prof_df.head()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup