<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
csv_path = "Resources/purchase_data.csv"

# Read Purchasing File and store into Pandas data frame
purchase_data = pd.read_csv(csv_path)
purchase_data.head()<jupyter_output><empty_output><jupyter_text>Player Count * Display the total number of players<jupyter_code>#calculate total of players
total_players=len(purchase_data["SN"].unique())
print("Total number of players:", total_players, "players")<jupyter_output>Total number of players: 576 players<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 items values
uni_items= len(purchase_data["Item Name"].unique())
#Calculate average price
ave_price= purchase_data["Price"].mean()
#Calculate number of purchase
num_purchase= len(purchase_data)
#Calculate total revenue
tot_revenue= purchase_data["Price"].sum()
# Place all of the data found into a summary DataFrame
summary_df = pd.DataFrame({"Number of Unique Items": [uni_items],
                           "Average Price": ave_price,
                           "Number of Purchases": num_purchase,
                           "Total Revenue": tot_revenue})
#mapping values
summary_df["Average Price"] = summary_df["Average Price"].map("${:.2f}".format)
summary_df["Total Revenue"] = summary_df["Total Revenue"].map("${:,.2f}".format)
summary_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>#Calculate Percentage and Count of Male Players
male_df = purchase_data.loc[purchase_data["Gender"]=="Male", :]
num_male = len(male_df["SN"].unique())
per_male = (num_male*100)/total_players

#Calculate Percentage and Count of Female Players
female_df = purchase_data.loc[purchase_data["Gender"]=="Female", :]
num_female = len(female_df["SN"].unique())
per_female = (num_female*100)/total_players

#Calculate Percentage and Count of Other Players
num_other= total_players-(num_male+num_female)
per_other = (num_other*100)/total_players

#create a Data frame with the values
sum_gender_df = pd.DataFrame({"Gender": ["Male", "Female" , "Other/Non-Disclosed"],
                                   "Total Count": [num_male, num_female, num_other],
                                   "Percentage of Players":[per_male, per_female, per_other]
                                 })
#mapping values
sum_gender_df["Percentage of Players"] = sum_gender_df["Percentage of Players"].map("{:.2f}%".format)
sum_gender_df=sum_gender_df.set_index("Gender")
sum_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>#calculate values per female
num_pur_female= len(female_df)
ave_pri_female= female_df["Price"].mean()
tot_pur_female= female_df["Price"].sum()
#calculate avg purchase per male gender by player
group_female=female_df.groupby('SN')['Price'].sum()
avg_perperson_female=group_female.mean()

#calculate values per male
num_pur_male= len(male_df)
ave_pri_male= male_df["Price"].mean()
tot_pur_male= male_df["Price"].sum()
#calculate avg purchase per male gender by player
group_male=male_df.groupby('SN')['Price'].sum()
avg_perperson_male=group_male.mean()

#calculate values per other gender
other_df = purchase_data.loc[purchase_data["Gender"]=="Other / Non-Disclosed", :]
num_pur_other= len(other_df)
ave_pri_other= other_df["Price"].mean()
tot_pur_other= other_df["Price"].sum()
#calculate avg purchase per other gender by player
group_other=other_df.groupby('SN')['Price'].sum()
avg_perperson_other=group_other.mean()

#create a Data frame with the values
analysis_gender_df = pd.DataFrame({"Gender": ["Male", "Female" , "Other/Non-Disclosed"],
                              "Purchase Count": [num_pur_female, num_pur_male, num_pur_other],
                              "Average Purchase Price":[ave_pri_female, ave_pri_male, ave_pri_other],
                              "Total Purchase Value": [tot_pur_female, tot_pur_male, tot_pur_other],
                              "Avg Total Purchase per Person": [avg_perperson_female, avg_perperson_male, avg_perperson_other]
                             })

#mapping values
analysis_gender_df["Average Purchase Price"] = analysis_gender_df["Average Purchase Price"].map("${:.2f}".format)
analysis_gender_df["Total Purchase Value"] = analysis_gender_df["Total Purchase Value"].map("${:,.2f}".format)
analysis_gender_df["Avg Total Purchase per Person"] = analysis_gender_df["Avg Total Purchase per Person"].map("${:,.2f}".format)
analysis_gender_df=analysis_gender_df.set_index("Gender")
analysis_gender_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># Create bins in which to place values based upon TED Talk views
#ages=purchase_data["Age"].unique()
#bins = range(10,ages.max()+1,5)
bins=[0,9,14,19,24,29,34,39,45]
# Create labels for these bins
group_labels = ["<10", "10-14","15-19","20-24","25-29","30-34","35-39",">=40"]
# Slice the data and place it into bins

# Place the data series into a new column inside of the DataFrame
age_purchase_data=pd.read_csv(csv_path)
age_purchase_data["Ages Group"] = pd.cut(age_purchase_data["Age"], bins, labels=group_labels)
age_sn_purchase_data=age_purchase_data.drop_duplicates('SN')
# Using GroupBy in order to separate the data into fields according to "Age Group" values
grouped_age_df = age_sn_purchase_data.groupby('Ages Group')
#calculate puchase count by age 
age_purchase = grouped_age_df["SN"].count()
#calculate percent of puchase by age
percent_purchase = (age_purchase*100)/total_players
#create a Data frame with the values
age_demographics_df = pd.DataFrame({"Purchase Count": age_purchase,
                                    "Percentage Players": percent_purchase.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>grouped_age_df = age_purchase_data.groupby('Ages Group')
#calculate puchase count by age
age_purchase = grouped_age_df["SN"].count()
#calculate price average by age ranges
avg_purchase = grouped_age_df["Price"].mean()
#calculate Total purchase by age ranges
tot_purchase = grouped_age_df["Price"].sum()

#calculate Avg per person by age ranges
grouped_perperson_df = age_purchase_data.groupby(['Ages Group','SN'])['Price'].sum()
grouped_perperson_df =grouped_perperson_df.dropna(how='any')
grouped_perperson_df= grouped_perperson_df.groupby('Ages Group').mean()

#create a Data frame with the values
age_analysis_df = pd.DataFrame({"Purchase Count": age_purchase,
                                "Avg Purchase Price": avg_purchase.map("${:.2f}".format),
                                "Total Purchase Value": tot_purchase.map("${:,.2f}".format),
                                "Avg Total Purchase per Person": grouped_perperson_df.map("${:,.2f}".format)
                               })
age_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>grouped_sn_df = purchase_data.groupby(['SN'])
grouped_sn_df
#calculate puchase count by SN
sn_purchase = grouped_sn_df["Purchase ID"].count()
sn_purchase
#calculate average price by SN
avg_sn_purchase = grouped_sn_df["Price"].mean()
#calculate total puchase by SN
tot_sn_purchase = grouped_sn_df["Price"].sum()
#create a Data frame with the values
top_spenders_df = pd.DataFrame({"Purchase Count": sn_purchase,
                                "Avg Purchase Price": avg_sn_purchase.map("${:.2f}".format),
                                "Total Purchase Value": tot_sn_purchase
                                })
top_spenders_df= top_spenders_df.sort_values("Total Purchase Value", ascending=False)
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>grouped_item_df = purchase_data.groupby(['Item ID'])

#calculate purchase count by Item ID
item_purchase = grouped_item_df["Purchase ID"].count()
#calculate total puchase by Item ID
tot_item_purchase = grouped_item_df["Price"].sum()

#create a Data frame with the values
most_popular_df = pd.DataFrame({"Purchase Count": item_purchase,
                                "Total Purchase Value": tot_item_purchase
                              })
#.astype(float).map("${:,.2f}".format) 

#get the names and prices of each item
items_df = pd.DataFrame(purchase_data, columns=["Item ID", "Item Name", "Price"])
items_df=items_df.drop_duplicates('Item ID')

# Merge 
merge_most_popular_df = pd.merge(most_popular_df, items_df, on="Item ID",how="left")
merge_most_popular_df = merge_most_popular_df.set_index("Item ID")
merge_most_popular_df= merge_most_popular_df[["Item Name", "Purchase Count","Price", "Total Purchase Value"]]
merge_most_popular_df["Price"] = merge_most_popular_df["Price"].map("${:.2f}".format)
merge_most_popular_df.rename(columns={'Price': 'Item Price'}, inplace=True)
merge_most_popular_df= merge_most_popular_df.sort_values("Purchase Count", ascending=False)
merge_most_popular_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># To sort from highest to lowest.
sort_totpurchase_df = merge_most_popular_df.sort_values("Total Purchase Value", ascending=False)
sort_totpurchase_df["Total Purchase Value"] = sort_totpurchase_df["Total Purchase Value"].map("${:.2f}".format)
sort_totpurchase_df.head()
sum_gender_df.to_csv("Output/gender_summary.csv", index=False)
analysis_gender_df.to_csv("Output/gender_analysis.csv", index=False)
age_demographics_df.to_csv("Output/age_demographics.csv", index=False)
sort_totpurchase_df.head().to_csv("Output/most_profitable_items.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10789117945251775, 'fraction_numerical': 0.0065900642108820545, 'mean_word_length': 5.145898234683282, 'pattern_counts': {'":': 23, '<': 41, '<?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': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '92139', 'license': 'MIT', 'n_tokens_mistral': 3772, 'n_tokens_neox': 3527, 'n_words': 978, 'path': 'HeroesOfPymoli/HeroesOfPymoli_starter.ipynb', 'repo_name': 'galciluz/Pandas-Challenge', 'size': '11836'}