<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>#total number of players 
total_players = purchase_data["SN"].nunique()

total_players<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>#total number of unique items
total_items = purchase_data["Item ID"].nunique()

#Average price 
average_price = round(purchase_data["Price"].mean(),2)

#Number of purchase
number_purchases =len(purchase_data["Purchase ID"])

#Total revenue 
total_revenue = purchase_data["Price"].sum()
#Create summary table 
summary_table = pd.DataFrame({"Number of Unique Items": [total_items],"Average Price": "$" + str(average_price),"Number of Purchases":[number_purchases],"Total Revenue":"$" + str(total_revenue)})
summary_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>#Create a new dataframe with only players 
regrouped_df = purchase_data.groupby("Gender")["SN"].nunique()
print(regrouped_df)
#Gender counts 
gender_count = [484,81,11]
p=[]
for c in range(len(gender_count)):
    value = round((gender_count[c] /total_players)*100,2)
    p.append(value)
print(p)
#Create a Gender Demographics Summary table
gender_df = pd.DataFrame({"Total Count": gender_count,"Percentage of Players": p},index = ["Male","Female","Other / Non-Disclosed"])
gender_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>purchase_df = purchase_data.groupby("Gender")

#Count for number of purchases of gender 
purchase_count = purchase_df["Purchase ID"].count()

#Average purchase price 
avg_price = round(purchase_df["Price"].mean(),2)

#Total purchase value 
total_price = round(purchase_df["Price"].sum(),2)

#Average total per person 
avg_total=[]
gender_count = [81,484,11]

for c in range(len(gender_count)):
    value = round((total_price[c]/gender_count[c]),2)
    value = "$" + str(value)
    avg_total.append(value)
#formatting the cells 
avg_price = avg_price.map("${:,.2f}".format)
total_price = total_price.map("${:,.2f}".format)
#Create a Purchasing Analysis table
purchase_df = pd.DataFrame({"Purchase Count": purchase_count,"Average Purchase Price": avg_price, "Total Purchase Value": total_price,
                           "Avg Total Purchase per Person": avg_total})
purchase_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>#Creating bins 
bins = [0,9,14,19,24,29,34,39,50]
age_groups = ["<10","10-14","15-19","20-24","25-29","30-34","35-39","40+"]

#Calculating total count
age_df = purchase_data.loc[:,["Gender","SN","Age"]].drop_duplicates()
age_df

age_df["Age group"] = pd.cut(purchase_data["Age"],bins,labels=age_groups)
total_count = age_df.groupby("Age group")["Gender"].count()

#Calculating percentage 
percent = round((total_count/total_players)*100,2)
#summary table
age_summary = pd.DataFrame({"Total Count": total_count, "Percentage of Players": percent})
age_summary<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>#Getting the data
purch_df = purchase_data.loc[:,["Gender","SN","Age","Item ID", "Price"]]
#Bin the data in different age groups 
bins = [0,9,14,19,24,29,34,39,50]
age_groups = ["<10","10-14","15-19","20-24","25-29","30-34","35-39","40+"] 

#calculate purchase count 
purch_df["Age group"] = pd.cut(purchase_data["Age"],bins,labels=age_groups)
purch_group = purch_df.groupby("Age group")

purch_count = purch_group["Item ID"].count()
#calculate average purchase price 
purch_price = round(purch_group["Price"].mean(),2)

#total purchase value 
purch_val = round(purch_group["Price"].sum(),2)

#average total purchase per person 
total_purch = round(purch_val/total_count,2)
#formatting the dataframe
purch_price = purch_price.map("${:,.2f}".format)
purch_val = purch_val.map("${:,.2f}".format)
total_purch = total_purch.map("${:,.2f}".format)
#create a summary table 
purch_summary = pd.DataFrame({"Purhcase count": purch_count,"Average Purchase Price": purch_price, 
                              "Total purchase value": purch_val, "Avg Total Purchase per Person": total_purch})
purch_summary<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 "SN"
top = purchase_data.groupby("SN")
top_count = top["Item ID"].count()

#average price per person
total_price = top["Price"].sum()
avg_price = round(total_price/top_count,2)

#total purchase value 
total_val = top["Price"].sum()
#formatting the cells 
avg_price = avg_price.map("${:,.2f}".format)
total_val = total_val.map("${:,.2f}".format)
#create a summary table
purch_summary = pd.DataFrame({"Purchase Count": top_count, "Average Purchase Price": avg_price, 
                              "Total Purchase Value": total_val})

purch_summary.sort_values("Total Purchase Value", axis = 0, ascending = False, 
                 inplace = True) 
purch_summary<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>#Grouping by Item ID and Item Name #NEED HELP# 
popitem_df = purchase_data.loc[:,["Item ID","Item Name","Price"]]
pop_grouped = popitem_df.groupby(["Item ID", "Item Name"])

#creating needed series
pop_sum = pop_grouped.sum()["Price"]
pop_average = pop_grouped.mean()["Price"]
pop_count = pop_grouped.count()["Price"]

#creating a new dataframe
pop_summary = pd.DataFrame({"Purchase Count": pop_count, "Item Price": pop_average, 
                            "Total Purchase Value": pop_sum})
pop_summary
pop_sorted = pop_summary.sort_values("Purchase Count", axis = 0,ascending = False)
pop_sorted<jupyter_output><empty_output><jupyter_text>Most Profitable Items<jupyter_code>prof_summary = pd.DataFrame({"Purchase Count": pop_count, "Item Price": pop_average, 
                            "Total Purchase Value": pop_sum})
prof_sorted = pop_summary.sort_values("Total Purchase Value", axis = 0,ascending = False)
prof_sorted<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11848862802641233, 'fraction_numerical': 0.015651748593788213, 'mean_word_length': 6.075259515570934, 'pattern_counts': {'":': 25, '<': 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': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '451451', 'license': 'ADSL', 'n_tokens_mistral': 2667, 'n_tokens_neox': 2510, 'n_words': 742, 'path': 'HeroesOfPymoli_starter.ipynb', 'repo_name': 'amagais/pandas-challenge', 'size': '8178'}