<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>unique_names = purchase_data['SN'].unique()
total_players = len(unique_names)
total_players_df = pd.DataFrame({"Total Players": [total_players]})
total_players_df<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>num_unique_items = len(purchase_data['Item Name'].unique())
total_revenue = purchase_data['Price'].sum()
num_purchases = len(purchase_data['SN'])
avg_price = total_revenue/num_purchases

summary_analysis_df = pd.DataFrame({
        "Number of Unique Items": [num_unique_items],
        "Average Price": [avg_price],
        "Number of Purchases": [num_purchases],
        "Total Revenue": [total_revenue]
    })
summary_analysis_df["Average Price"] = summary_analysis_df["Average Price"].map("${:.2f}".format)
summary_analysis_df["Total Revenue"] = summary_analysis_df["Total Revenue"].map("${:,.2f}".format)
summary_analysis_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>#Non Pandas solution (or using Pandas minimally)
# total_male_players = len(purchase_data['SN'].loc[purchase_data['Gender'] == 'Male'].unique())
# total_female_players = len(purchase_data['SN'].loc[purchase_data['Gender'] == 'Female'].unique())
# total_other_players = len(purchase_data['SN'].loc[purchase_data['Gender'] == 'Other / Non-Disclosed'].unique())

# perc_male_players = (total_male_players *100) / total_players
# perc_female_players = (total_female_players *100) / total_players
# perc_other_players = (total_other_players *100) / total_players

# gender_demographics_df = pd.DataFrame({
#         "Total Count": [total_male_players, total_female_players, total_other_players],
#         "Percentage of Players": [perc_male_players, perc_female_players, perc_other_players]
#     })
# gender_demographics_df["Percentage of Players"] = \
#     gender_demographics_df["Percentage of Players"].map("{:.2f}%".format)
# gender_demographics_df

#Using Pandas
# Reduce the data so that we only have screen name and gender
gender_df = purchase_data[["SN", "Gender"]]
gender_unique_df = gender_df.drop_duplicates()
gender_unique_df.reset_index(drop=True)

# Count genders within the purchase data
gender_counts = gender_unique_df["Gender"].value_counts()
gender_percentages = (gender_counts * 100) /total_players

gender_demographics_df = pd.DataFrame({
        "Total Count": gender_counts,
        "Percentage of Players": gender_percentages,
    })
gender_demographics_df["Percentage of Players"] = \
    gender_demographics_df["Percentage of Players"].map("{:.2f}%".format)
gender_demographics_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># Solution inspired by:
# https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum
gender_analysis_df = purchase_data.groupby('Gender')

purchase_count = gender_analysis_df['Item Name'].count()
average_purchase_price = gender_analysis_df['Price'].sum() / gender_analysis_df['Item Name'].count()
total_purchase_value = gender_analysis_df['Price'].sum()

#Solution inspired by:
# https://stackoverflow.com/questions/38309729/count-unique-values-with-pandas-per-groups/38309823
avg_total_purchase_per_person = gender_analysis_df['Price'].sum() / gender_analysis_df['SN'].nunique()

# print(purchase_count)
# print(average_purchase_price)
# print(total_purchase_value)
# print(avg_total_purchase_per_person)

gender_purchasing_analysis_df = pd.DataFrame({
        "Purchase Count": purchase_count,
        "Average Purchase Price": average_purchase_price,
        "Total Purchase Value": total_purchase_value,
        "Avg Total Purchase Per Person": avg_total_purchase_per_person
})
gender_purchasing_analysis_df["Average Purchase Price"] = \
    gender_purchasing_analysis_df["Average Purchase Price"].map("${:.2f}".format)
gender_purchasing_analysis_df["Total Purchase Value"] = \
    gender_purchasing_analysis_df["Total Purchase Value"].map("${:,.2f}".format)
gender_purchasing_analysis_df["Avg Total Purchase Per Person"] = \
    gender_purchasing_analysis_df["Avg Total Purchase Per Person"].map("${:.2f}".format)

gender_purchasing_analysis_df<jupyter_output>Gender
Female                   113
Male                     652
Other / Non-Disclosed     15
Name: Item Name, dtype: int64<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_demographics_df = purchase_data.copy()

bins = [0, 10, 15, 20, 25, 30, 35, 40, 150]
labels = ['<10', '10-14', '15-19', '20-24', '25-29', '30-34', '35-39', '40+'] 

age_demographics_df["Age Group"] = pd.cut(age_demographics_df["Age"], 
                                          bins,
                                          labels=labels,
                                          include_lowest=True,
                                          right= False
                                         )
age_demographics_unique_df = age_demographics_df[['SN', 'Age', 'Age Group']].drop_duplicates()

age_demographics_org_df = age_demographics_unique_df.groupby('Age Group')
total_count = age_demographics_org_df['SN'].count()
total_percentages = (total_count * 100) /total_players
total_percentages

age_demographics_summary_df = pd.DataFrame({
        "Total Count": total_count,
        "Percentage of Players": total_percentages,
    })
age_demographics_summary_df["Percentage of Players"] = \
    age_demographics_summary_df["Percentage of Players"].map("{:.2f}%".format)
age_demographics_summary_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>purchase_data.head()

age_demographics_df = purchase_data.copy()

bins = [0, 10, 15, 20, 25, 30, 35, 40, 150]
labels = ['<10', '10-14', '15-19', '20-24', '25-29', '30-34', '35-39', '40+']

age_demographics_df["Age Group"] = pd.cut(age_demographics_df["Age"], 
                                          bins,
                                          labels=labels,
                                          include_lowest=True,
                                          right= False
                                         )
age_demographics_df
age_demographics_grouped_df = age_demographics_df.groupby('Age Group')
age_demographics_grouped_df.count()

purchase_count = age_demographics_grouped_df['Item Name'].count()
average_purchase_price = age_demographics_grouped_df['Price'].sum() / age_demographics_grouped_df['Item Name'].count()
total_purchase_value = age_demographics_grouped_df['Price'].sum()

# #Solution inspired by:
# # https://stackoverflow.com/questions/38309729/count-unique-values-with-pandas-per-groups/38309823
avg_total_purchase_per_person = age_demographics_grouped_df['Price'].sum() / age_demographics_grouped_df['SN'].nunique()

# print(purchase_count)
# print(average_purchase_price)
# print(total_purchase_value)
# print(avg_total_purchase_per_person)

age_purchasing_analysis_df = pd.DataFrame({
        "Purchase Count": purchase_count,
        "Average Purchase Price": average_purchase_price,
        "Total Purchase Value": total_purchase_value,
        "Avg Total Purchase Per Person": avg_total_purchase_per_person
})
age_purchasing_analysis_df["Average Purchase Price"] = \
    age_purchasing_analysis_df["Average Purchase Price"].map("${:.2f}".format)
age_purchasing_analysis_df["Total Purchase Value"] = \
    age_purchasing_analysis_df["Total Purchase Value"].map("${:,.2f}".format)
age_purchasing_analysis_df["Avg Total Purchase Per Person"] = \
    age_purchasing_analysis_df["Avg Total Purchase Per Person"].map("${:.2f}".format)

age_purchasing_analysis_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>top_spenders_df = purchase_data.copy()
top_spenders_df
top_spenders_grouped_df = top_spenders_df.groupby('SN')

purchase_count = top_spenders_grouped_df['Item Name'].count()
average_purchase_price = top_spenders_grouped_df['Price'].sum() / top_spenders_grouped_df['Item Name'].count()
total_purchase_value = top_spenders_grouped_df['Price'].sum()

# print(purchase_count)
# print(average_purchase_price)
# print(total_purchase_value)

top_spenders_analysis_df = pd.DataFrame({
        "Purchase Count": purchase_count,
        "Average Purchase Price": average_purchase_price,
        "Total Purchase Value": total_purchase_value
})

#Sort before you clean up formatting 
top_spenders_analysis_df = top_spenders_analysis_df.sort_values(by=['Total Purchase Value'], ascending = False)

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

top_spenders_analysis_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>most_popular_items_df = purchase_data.copy()
most_popular_items_df['Item Name'].unique()
most_popular_items_df
most_popular_items_grouped_df = most_popular_items_df.groupby('Item ID')
most_popular_items_grouped_df = most_popular_items_df.groupby(['Item ID', 'Item Name'])

purchase_count = most_popular_items_grouped_df['Item Name'].count()
average_purchase_price = most_popular_items_grouped_df['Price'].sum() / most_popular_items_grouped_df['Item Name'].count()
total_purchase_value = most_popular_items_grouped_df['Price'].sum()

# print(purchase_count)
# print(average_purchase_price)
# print(total_purchase_value)

most_popular_items_analysis_df = pd.DataFrame({
        "Purchase Count": purchase_count,
        "Average Purchase Price": average_purchase_price,
        "Total Purchase Value": total_purchase_value
})

#Sort before you clean up formatting 
most_popular_items_analysis_cleaned_df = most_popular_items_analysis_df.sort_values(by=['Purchase Count'], ascending = False)

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

most_popular_items_analysis_cleaned_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>most_popular_items_analysis_df.head()

most_profitable_items_analysis_cleaned_df = most_popular_items_analysis_df.sort_values(by=['Total Purchase Value'], ascending = False)

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

most_profitable_items_analysis_cleaned_df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10560832202623248, 'fraction_numerical': 0.013116236996834011, 'mean_word_length': 5.465399610136452, 'pattern_counts': {'":': 25, '<': 42, '<?xml version=': 0, '>': 40, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '293356', 'license': 'MIT', 'n_tokens_mistral': 4328, 'n_tokens_neox': 3999, 'n_words': 896, 'path': 'HeroesOfPymoli/HeroesOfPymoli.ipynb', 'repo_name': 'Jagjeet/pandas-challenge', 'size': '13266'}