<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

# Load csv file
purchase_data = "Resources/purchase_data.csv"

# Read Purchasing File and store into Pandas data frame
purchase_data_df = pd.read_csv(purchase_data)

# rename dataframe 
df = pd.DataFrame(purchase_data_df)

#data_file_df = pd.read_csv
purchase_data_df.head()<jupyter_output><empty_output><jupyter_text>Player Count * Display the total number of players<jupyter_code>#Number of total players (# of unique items)
player_count = purchase_data_df["SN"].nunique()
    
    
player_count<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># Count of unique items

unique_item = purchase_data_df["Item Name"].nunique()

unique_item

# Calculate average price

average_price = purchase_data_df["Price"].mean()

average_price
#Calculate sum of total individual transactions

purchase_count = purchase_data_df["Purchase ID"].count()

purchase_count

# Calculate sum of total revenue

total_revenue = purchase_data_df['Price'].sum()

total_revenue
#Create summary table
summary_table = pd.DataFrame({"Unique Items":unique_item,"Total Transactions":purchase_count,"Average Price":[average_price], "Total Revenue": [total_revenue]})
                                 


#improve formatting by adding $
summary_table["Average Price"] = summary_table["Average Price"].map("${0:,.2f}".format)
summary_table["Total Revenue"] = summary_table["Total Revenue"].map("${0:,.2f}".format)

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>#Group by Gender by total number

#Group data by Gender and delete duplicates
grouped_df = df.groupby(["Gender"])
gender_group = grouped_df.nunique()

#Total Gender
total_gender = unique_df["SN"].sum()

#Percentage and Count of Players
count = unique_df["SN"].unique()
percentage = unique_df["SN"]/ total_gender

#Create new dataframe
updated_gender = pd.DataFrame({"Percentage of Players": percentage,
                            "Count":count})
#Change percentage format and re order columns
updated_gender["Percentage of Players"] = updated_gender["Percentage of Players"].map("{:,.2%}".format) 
#Print final dataframe
updated_gender

# Count unique number of male, female and other players
#gen = purchase_data_df["Gender"].value_counts()<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>#total purchase amount by gender
total_counts = df.groupby(["Gender"]).sum()["Price"]

total_counts.head()


# Average price per gender

average_price = df.groupby(["Gender"]).mean()["Price"]

average_price

# Purchase count and average price breakdown by gender

#Create updated dataframe
gender_total = pd.DataFrame({"Average Purchase Price": average_price,"Total Purchase Price":total_counts,})
 
#Update formatting and reorder columns
    
gender_total["Average Purchase Price"] = gender_total["Average Purchase Price"].map("${:,.2f}".format) 
gender_total["Total Purchase Price"] = gender_total["Total Purchase Price"].map("${:,.2f}".format) 

gender_total = gender_total[["Average Purchase Price", "Total Purchase Price",]]

gender_total.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>df = pd.DataFrame(purchase_data_df)
#Age Demographic Analysis

#Reference updated dataframe excluding duplicates
updated_df = df.drop_duplicates("SN")

#The below each broken into bins of 4 years (i.e. <10, 10-14, 15-19, etc.)
bins = [0,9,14,19,24,29,34,39,100]
groups = ["<10", "10-14", "15-19", "20-24", "25-29", "30-34", "35-39", "40+"]

#Create a  new column for age groups and then groupby Age Groups
updated_df["Age Groups"] = pd.cut(updated_df["Age"], bins, labels=groups)
age_df = updated_df.groupby(["Age Groups"])

total_age = unique_df["Age"].sum()

#Purchase Count
age_purchase = updated_df["Age Groups"].value_counts()

#Percentage of Users
age_percentage = age_purchase / total_gender

age_demographics = pd.DataFrame({"Total Count": age_purchase,
                             "Percentage of Players":age_percentage})

age_demographics["Percentage of Players"] = age_demographics["Percentage of Players"].map("{:,.2%}".format) 


age_demographics

# Show table that reflects bins
df["Age Ranges"] = pd.cut(df["Age"], bins, labels= group_labels,include_lowest=True,) 
df
# Create a GroupBy object based upon quanity of in age range

age_group = df.groupby("Age Ranges")

print(age_group["Age"].count())<jupyter_output>Age Ranges
<10       23
10-14     28
15-19    136
20-24    365
25-29    101
30-34     73
35-39     41
40+       13
Name: Age, dtype: int64<jupyter_text>Purchasing Analysis (Age)<jupyter_code># Show range of ages and the average of age and price in each range 
av_ageprice = age_group[["Age", "Price",]].mean()

av_ageprice
#Total number of transactions  by age
total_age = age_group[["Age"]].count()

total_age
#Age group by percentage of total purchasers

agegroup_percentage_table = df["Age Ranges"].value_counts(normalize=True) * 100

agegroup_percentage_table<jupyter_output><empty_output><jupyter_text>* 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 Average Purchase Price

age_df = updated_df.groupby(["Age Groups"])

#Average purchase price
age_average_price = age_df["Price"].mean()

#Total purchase value
age_price = age_df["Price"].sum()

#Create new dataframe
age_analysis = pd.DataFrame({"Purchase Count": age_purchase,
                             "Average Purchase Price":age_average_price,
                            "Total Purchase Value":age_price,})

#Clean up formatting
age_analysis["Average Purchase Price"] = age_analysis["Average Purchase Price"].map("${:,.2f}".format) 
age_analysis["Total Purchase Value"] = age_analysis["Total Purchase Value"].map("${:,.2f}".format) 


#Reorder Columns
age_analysis = age_analysis[["Purchase Count", "Average Purchase Price", "Total Purchase Value",]]


age_analysis
#<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 Spender Analysis

#Show the the top spenders by:

# Count the purchase count(frequency) by user
purchase_count_user = df.groupby("SN")["Price"].count()

# Caluculate average price spent by user
purchase_price_user = df.groupby("SN")["Price"].mean()

# Calculate total price spent by user
total_purchase_user = df.groupby("SN")["Price"].sum()


# Create a summary of top spenders
user_summary = pd.DataFrame({"Purchase Count" : purchase_count_user,
                             "Average Purchase Price": purchase_price_user,
                             "Total Purchase Value": total_purchase_user})

# Sort by total purchase value
user_summary = user_summary.sort_values(by = "Total Purchase Value",ascending = False)

# Change the format to currency
user_summary[["Average Purchase Price","Total Purchase Value"]] \
= user_summary[["Average Purchase Price","Total Purchase Value"]].applymap("${:.2f}".format)

# Display the summary
user_summary.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

#Set index of columns

grouped_id = df.set_index(["Item ID", "Item Name"])

grouped_id = grouped_id.groupby(level=["Item ID", "Item Name"])

#Find total amount spent per user
total_price_id = grouped_id.sum()["Price"]

#Find avgerage amount spent per user
avg_price_id = grouped_id.mean()["Price"]

#Find purchase total purchase count per user
count_id = grouped_id.count()["Price"]


#Update new dataframe
items_df = pd.DataFrame({"Count":count_id,"Average Purchase Price":avg_price_id,"Total Purchase Price": total_price_id})


#Sort by total purchase price
sorted_items = items_df.sort_values("Count",ascending=False)

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

#Reorder Columns
sorted_items = sorted_items[["Count", "Average Purchase Price", "Total Purchase Price"]]


#Display top 5
sorted_items.head(5)<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 Profitable Items 

#Set and index columns
grouped_id = df.set_index(["Item ID", "Item Name"])

grouped_id = grouped_id.groupby(level=["Item ID", "Item Name"])

#Find total spent per user
total_price_id = grouped_id.sum()["Price"]

#Find avg spent per user
avg_price_id = grouped_id.mean()["Price"]

#Find purchase count per user
count_id = grouped_id.count()["Price"]

#Create new dataframe
items_df = pd.DataFrame({ "Count":count_id,"Average Purchase Price":avg_price_id,"Total Purchase Price": total_price_id,})


#Sort by total purchase price
sorted_items = items_df.sort_values("Total Purchase Price",ascending=False)

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

#Reorder Columns
sorted_items = sorted_items[["Count", "Average Purchase Price", "Total Purchase Price"]]

#Display top 5
sorted_items.head(5)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10938753677453865, 'fraction_numerical': 0.011322100383346707, 'mean_word_length': 5.7254196642685855, 'pattern_counts': {'":': 21, '<': 47, '<?xml version=': 0, '>': 44, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '125629', 'license': 'Unlicense', 'n_tokens_mistral': 3549, 'n_tokens_neox': 3283, 'n_words': 1056, 'path': 'panda-challenge/HOMEWORKFG.ipynb', 'repo_name': 'CFG17/panda-challenge', 'size': '11217'}