<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># Heroes of Pymoli Reporting

# Dependencies and Setup
import pandas as pd

# Upload Purchase Data CSV File
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># The value_counts method counts unique values in a column
No_Players = len(purchase_data["SN"].value_counts())

# Place all of the data found into a summary DataFrame
No_Players_Summary  = pd.DataFrame({"Total Players": [No_Players ]})
No_Players_Summary<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 the number of unique items and Purchases in the DataFrame
No_Uniq_Items = len(purchase_data["Item ID"].unique())
Av_Price = purchase_data["Price"].mean()
No_Purchases = len(purchase_data["Purchase ID"].unique())
Tot_Revenue = purchase_data["Price"].sum()

# Place all of the data found into a summary DataFrame
purchasing_analysis = pd.DataFrame({"Number of Unique Items": [No_Uniq_Items],
                         "Average Price": [Av_Price],
                         "Number of Purchases": [No_Purchases],
                         "Total Revenue": [Tot_Revenue]})
purchasing_analysis<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># Grouping the Dataframe by Gender
gender_group = pd.DataFrame(purchase_data["Gender"].value_counts())
#Reset the Index
gender_group.reset_index(inplace=True)
#Assign name for the column variables
gender_group.columns = ["Gender","#Gender"]

# Calculate Gender%
perc = (purchase_data["Gender"].value_counts()/len(purchase_data["Purchase ID"].value_counts()))*100
# Store into a pandas Dataframe
gender_perc = pd.DataFrame(perc)
#Reset the Index
gender_perc.reset_index(inplace=True)
#Assign name for the column variables
gender_perc.columns = ["Gender","%Gender"]

#Merge Gender and Gender% Pandas Dataframes to get final Gender Demographics summary
Final_demo = pd.merge(gender_group,gender_perc,on = "Gender")

#Assign Percentage formatting to the %Gender column variable and Drop the index varibles
Final_demo.style.format({"%Gender": "{0:,.2f}%"}).hide_index()<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># Grouping the Dataframe by Gender and get aggregate values - Count, Mean and Sum
gender_summary = pd.DataFrame(purchase_data.groupby("Gender").agg({"Price":["count","mean","sum"]}))
#Reset the Index
gender_summary.reset_index(inplace=True)
#Assign name for the column variables
gender_summary.columns = ["Gender","Purchase Count","Average Purchase Value","Total Purchase Value"]

# Grouping the Dataframe by Gender and Get counts of Unique Players
uniqplayer_summary = purchase_data.groupby("Gender")["SN"].nunique().reset_index()

# Merge Gender Summary Data and Gender - Unique Players Summary Data
purchasing_analysis = pd.merge(gender_summary,uniqplayer_summary,on = "Gender")

# Calculate Av Total Purchase per Person with Gender - Unique Players count
purchasing_analysis ["Av Total Purchase per Person"] = purchasing_analysis["Total Purchase Value"]/purchasing_analysis ["SN"]

# Drop the Unique Players count variable(SN)
purchasing_analysis_final = purchasing_analysis .drop('SN',axis =1)

#Assign Currency formatting to the Price Values column variables and Drop the index varibles
purchasing_analysis_final.style.format({"Average Purchase Value": "${:20,.2f}", 
                             "Total Purchase Value": "${:20,.2f}", 
                             "Av Total Purchase per Person": "${:20,.2f}"}).hide_index()<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 the age bins in which Data will be held
# Bins are "<10", "10-14", "15-19", "20-24", "25-29", "30-34","35-39","40+"
bins = [0,9,14,19,24,29,34,39,100]

# Create the names for the age bins
bin_names = ["<10","10-14","15-19","20-24","25-29","30-34","35-39","40+"]

# Assign the bins to the purchase_data dataframe
purchase_data['age_bins']=pd.cut(purchase_data["Age"],bins, labels=bin_names, include_lowest=True)

# Grouping the Dataframe by Age bins and Get counts of Unique Players
bins_summary = purchase_data.groupby("age_bins")["SN"].nunique().reset_index()

# Calculating the percentages by the age group
bins_summary['Percentage of Players'] = ((bins_summary['SN'] / bins_summary['SN'].sum()) * 100).map("{0:,.2f}%".format)

# #Assign name for the column variables
bins_summary.columns = ["Age Ranges","Total Count","Percentage of Players"]

#Drop the index varibles
bins_summary.style.hide_index()<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># Group by the Age bins and get the aggregate values Total,Av price and Total Purchase value
bins_summary2 = pd.DataFrame(purchase_data.groupby("age_bins").agg({"Price":["count","mean","sum"]}))

#Reset the Index
bins_summary2.reset_index(inplace=True)

#Assign name for the column variables
bins_summary2.columns = ["Age Ranges","Purchase Count","Average Purchase Value","Total Purchase Value"]

# Merge Age bins Uniq Players Data from the previous Age demographics report and Age bins Summary Data by Price
bins_summary2_final = pd.merge(bins_summary,bins_summary2,on = "Age Ranges")

# Calculate Av Total Purchase per Person with Age Bins - Unique Players count
bins_summary2_final["Av Total Purchase per Person"] = bins_summary2_final["Total Purchase Value"]/bins_summary2_final["Total Count"]

# Drop the columns not required
bins_summary2_final2 = bins_summary2_final.drop(['Total Count','Percentage of Players'],axis =1)

#Assign Currency formatting to the Price Values column variables and Drop the index varibles
bins_summary2_final2.style.format({"Average Purchase Value": "${:20,.2f}", 
                      "Total Purchase Value": "${:20,.2f}", 
                           "Av Total Purchase per Person": "${:20,.2f}"}).hide_index()<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># Group by the Players and get the aggregate Purchase Count,Av price and Total Purchase value
topspender_summary = pd.DataFrame(purchase_data.groupby("SN").agg({"Price":["count","mean","sum"]}))
#Reset the Index
topspender_summary.reset_index(inplace=True)
#Assign name for the column variables
topspender_summary.columns = ["SN","Purchase Count","Average Purchase Price","Total Purchase Value"]

#Sort the Total Purchase Value variable by descending order to display the top 5 spenders 
topspender = topspender_summary.sort_values(by='Total Purchase Value', ascending=False)

#Assign Currency formatting to the Price Values column variables and Drop the index varibles
topspender.head().style.format({"Average Purchase Price": "${:20,.2f}", 
                             "Total Purchase Value": "${:20,.2f}"}).hide_index()<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, 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># Group by multiple columns(Item ID and Item Name) and get the aggregate Purchase Count,Av price and Total Purchase value
popularitems_summary = pd.DataFrame(purchase_data.groupby(['Item ID','Item Name']).agg({"Price":["count","mean","sum"]}))
#Reset the Index
popularitems_summary.reset_index(inplace=True)
#Assign name for the column variables
popularitems_summary.columns = ["Item ID","Item Name","Purchase Count","Average Purchase Price","Total Purchase Value"]

#Sort the Purchase Count by descending order to display the top 5 popular Items and associated ID's 
mostpopular = popularitems_summary.sort_values(by='Purchase Count', ascending=False)

#Assign Currency formatting to the Price Values column variables and Drop the index varibles
mostpopular.head().style.format({"Average Purchase Price": "${:20,.2f}", 
                             "Total Purchase Value": "${:20,.2f}"}).hide_index()<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># Use the popularitems_summary dataframe to sort it by the total purchase value to get the most profitable items
mostprofitable = popularitems_summary.sort_values(by='Total Purchase Value', ascending=False)

#Assign Currency formatting to the Price Values column variables and Drop the index varibles
mostprofitable.head().style.format({"Average Purchase Price": "${:20,.2f}", 
                             "Total Purchase Value": "${:20,.2f}"}).hide_index()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10135644310474755, 'fraction_numerical': 0.01281085154483798, 'mean_word_length': 5.719620253164557, 'pattern_counts': {'":': 22, '<': 43, '<?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': '554880', 'license': 'MIT', 'n_tokens_mistral': 3143, 'n_tokens_neox': 2876, 'n_words': 1062, 'path': 'HeroesOfPymoli/HeroesOfPymoli.ipynb', 'repo_name': 'srivegunta/pandas-challenge', 'size': '10616'}