<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 * Display the total number of players<jupyter_code>#calculate total number of players
total_players = len(purchase_data['SN'].unique())

#create dataframe
player_count_df = pd.DataFrame({
    'Total Players': [total_players]
})

player_count_df.head()<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>#calculate number of unique items, avg. price, total purchases, and total revenue
unique_items = len(purchase_data['Item ID'].unique())
average_price = purchase_data['Price'].mean()
total_purchases = len(purchase_data['Item ID'])
total_revenue = purchase_data['Price'].sum()

#create dataframe
purchasing_analysis_df = pd.DataFrame({
    'Number of Unique Items': [unique_items],
    'Average Price': average_price,
    'Number of Purchases': total_purchases,
    'Total Revenue': total_revenue
})

#format pricing
purchasing_analysis_df["Average Price"] = purchasing_analysis_df["Average Price"].map("${:.2f}".format)
purchasing_analysis_df["Total Revenue"] = purchasing_analysis_df["Total Revenue"].map("${:.2f}".format)

purchasing_analysis_df.head()<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>#calculate percentage and count of male, female, and other players
male_users = purchase_data.loc[purchase_data['Gender'] == "Male", :]
male_count = len(male_users['SN'].unique())
male_percentage = "{:.2f}%".format((male_count/total_players) * 100)

female_users = purchase_data.loc[purchase_data['Gender'] == 'Female', :]
female_count = len(female_users['SN'].unique())
female_percentage = "{:.2f}%".format((female_count/total_players) * 100)

other_users = purchase_data.loc[purchase_data['Gender'] == "Other / Non-Disclosed", :]
other_count = len(other_users['SN'].unique())
other_percentage = "{:.2f}%".format((other_count/total_players) * 100)

#create dataframe
gender_demographics_df = pd.DataFrame({
    "Gender": ['Male', 'Female', 'Other / Non-Disclosed'], 
    "Total Count": [male_count, female_count, other_count],
    "Percentage of Players": [male_percentage, female_percentage, other_percentage]
})

#Set index of dataframe to gender and remove the index name
gender_demographics_df = gender_demographics_df.set_index('Gender')
gender_demographics_df.index.name = None

gender_demographics_df.head()<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>#calculate purchase count, avg. purchase price, total purchase value, and avg. purchase total per person by gender
male_purchase_count = len(male_users['SN'])
female_purchase_count = len(female_users['SN'])
other_purchase_count = len(other_users['SN'])

male_avg_purchase = male_users['Price'].mean()
female_avg_purchase = female_users['Price'].mean()
other_avg_purchase = other_users['Price'].mean()

male_total_purchase = male_users['Price'].sum()
female_total_purchase = female_users['Price'].sum()
other_total_purchase = other_users['Price'].sum()

male_avg_total_purchase = male_total_purchase/male_count
female_avg_total_purchase = female_total_purchase/female_count
other_avg_total_purchase = other_total_purchase/other_count

#create dataframe
purchasing_analysis_gender_df = pd.DataFrame({
    "Gender": ['Male', 'Female', 'Other / Non-Disclosed'], 
    "Purchase Count": [male_purchase_count, female_purchase_count, other_purchase_count],
    "Average Purchase Price": [male_avg_purchase, female_avg_purchase, other_avg_purchase],
    "Total Purchase Value": [male_total_purchase, female_total_purchase, other_total_purchase],
    "Avg Total Purchase per Person": [male_avg_total_purchase, female_avg_total_purchase, other_avg_total_purchase]
})

#Set index of dataframe to Gender
purchasing_analysis_gender_df = purchasing_analysis_gender_df.set_index('Gender')

#format pricing
purchasing_analysis_gender_df["Average Purchase Price"] = purchasing_analysis_gender_df["Average Purchase Price"].map("${:.2f}".format)
purchasing_analysis_gender_df["Total Purchase Value"] = purchasing_analysis_gender_df["Total Purchase Value"].map("${:.2f}".format)
purchasing_analysis_gender_df["Avg Total Purchase per Person"] = purchasing_analysis_gender_df["Avg Total Purchase per Person"].map("${:.2f}".format)

purchasing_analysis_gender_df.head()<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>#create bins and group labels
bins = [0, 9, 14, 19, 24, 29, 34, 39, 46]
group_labels = ['<10', '10-14', '15-19', '20-24', '25-29', '30-34', '35-39', '40+']

#create a new column within the dataframe for the sliced data 
purchase_data['Age Group'] = pd.cut(purchase_data['Age'], bins=bins, labels=group_labels)

#create a variable to hold a groupby object based on Age Group
age_group = purchase_data.groupby('Age Group')

#calculate total count and percentage of players based on each bin
total_count_per_bin = age_group['SN'].nunique()
percentage_per_bin = (total_count_per_bin/(total_players)) * 100

#create dataframe
age_demographics_df = pd.DataFrame({
    'Total Count': total_count_per_bin,
    'Percentage of Players': percentage_per_bin
})

#remove the index name
age_demographics_df.index.name = None

#format percentages
age_demographics_df["Percentage of Players"] = age_demographics_df["Percentage of Players"].map("{:.2f}%".format)

age_demographics_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>#calculate purchase count, avg. purchase price, total purchase value, and avg. total purchase per bin
purchase_count_per_bin = age_group['Item ID'].count()
avg_purchase_per_bin = age_group['Price'].mean()
total_purchase_per_bin = age_group['Price'].sum()
avg_total_purchase_per_bin = total_purchase_per_bin/total_count_per_bin

#create data frame
purchasing_analysis_age_df = pd.DataFrame({
    "Purchase Count": purchase_count_per_bin,
    "Average Purchase Price": avg_purchase_per_bin,
    "Total Purchase Value": total_purchase_per_bin,
    "Avg Total Purchase per Person": avg_total_purchase_per_bin
})

#change index name
purchasing_analysis_age_df.index.name = "Age Ranges"

#format pricing
purchasing_analysis_age_df["Average Purchase Price"] = purchasing_analysis_age_df["Average Purchase Price"].map("${:.2f}".format)
purchasing_analysis_age_df["Total Purchase Value"] = purchasing_analysis_age_df["Total Purchase Value"].map("${:.2f}".format)
purchasing_analysis_age_df["Avg Total Purchase per Person"] = purchasing_analysis_age_df["Avg Total Purchase per Person"].map("${:.2f}".format)

purchasing_analysis_age_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>#create a variable to hold a groupby object based on SN 
sn_group = purchase_data.groupby('SN')

#calculate purchase count, avg. purchase price, and total purchase value per SN
purchase_count_per_sn = sn_group['Item ID'].count()
avg_purchase_per_sn = sn_group['Price'].mean()
total_purchase_per_sn = sn_group['Price'].sum()

#create data frame
top_spenders_df = pd.DataFrame({
    "Purchase Count": purchase_count_per_sn,
    "Average Purchase Price": avg_purchase_per_sn,
    "Total Purchase Value": total_purchase_per_sn,
})

#sort data frame in descending order
top_spenders_df = top_spenders_df.sort_values("Total Purchase Value", ascending=False)

#format pricing
top_spenders_df["Average Purchase Price"] = top_spenders_df["Average Purchase Price"].map("${:.2f}".format)
top_spenders_df["Total Purchase Value"] = top_spenders_df["Total Purchase Value"].map("${:.2f}".format)

top_spenders_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>#create a variable to hold a groupby object based on Item ID
item_id_group = purchase_data.groupby(['Item ID', 'Item Name'])

#calculate purchase count and total purchase value per Item ID
purchase_count_per_item = item_id_group['Item ID'].count()
total_purchase_per_item = item_id_group['Price'].sum()

#create dataframe
most_popular_items_df = pd.DataFrame({
    'Purchase Count': purchase_count_per_item,
    'Item Price': total_purchase_per_item/purchase_count_per_item,
    'Total Purchase Value': total_purchase_per_item
})

#sort data frame in descending order
popular_items_df = most_popular_items_df.sort_values("Purchase Count", ascending=False)

#format pricing
popular_items_df['Item Price'] = popular_items_df['Item Price'].map("${:.2f}".format)
popular_items_df["Total Purchase Value"] = popular_items_df["Total Purchase Value"].map("${:.2f}".format)

popular_items_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>#sort data frame in descending order by Total Purchase Value
popular_items_df = most_popular_items_df.sort_values("Total Purchase Value", ascending=False)

#format pricing
popular_items_df['Item Price'] = popular_items_df['Item Price'].map("${:.2f}".format)
popular_items_df["Total Purchase Value"] = popular_items_df["Total Purchase Value"].map("${:.2f}".format)

popular_items_df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10487548228691687, 'fraction_numerical': 0.006488951245177131, 'mean_word_length': 7.2764876632801165, 'pattern_counts': {'":': 15, '<': 42, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '563568', 'license': 'ADSL', 'n_tokens_mistral': 3670, 'n_tokens_neox': 3435, 'n_words': 971, 'path': 'HeroesOfPymoli/HeroesOfPymoli_starter.ipynb', 'repo_name': 'gjumaan/pandas-challenge', 'size': '11404'}