<jupyter_start><jupyter_text>HOMEWORK 2 - ADM<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import methods
import datetime<jupyter_output><empty_output><jupyter_text>READ THE DATA<jupyter_code>df_names = ["./datasets/2019-Nov.csv", "./datasets/2019-Oct.csv"]<jupyter_output><empty_output><jupyter_text>UNDERSTAND THE DATA  The data that we handle for this homework come from an online store. We are going to analyze two months: October and November. For each month we have different features that we have described below:<jupyter_code>methods.describe_df(df_names)<jupyter_output>Info for October
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 42448764 entries, 0 to 42448763
Data columns (total 9 columns):
 #   Column         Dtype  
---  ------         -----  
 0   event_time     object 
 1   event_type     object 
 2   product_id     int64  
 3   category_id    int64  
 4   category_code  object 
 5   brand          object 
 6   price          float64
 7   user_id        int64  
 8   user_session   object 
dtypes: float64(1), int64(3), object(5)
memory usage: 2.8+ GB
None 

The total number of null values in each column is:
event_time              0
event_type              0
product_id              0
category_id             0
category_code    13515609
brand             6117080
price                   0
user_id                 0
user_session            2
dtype: int64 

Info for November
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 67501979 entries, 0 to 67501978
Data columns (total 9 columns):
 #   Column         Dtype  
---  ------         -----  
 0  [...]<jupyter_text>We notice that there are some null values, but these are only inside two specific columns: *category_code* and *brand*. Since, for each question, we decide which columns we needed to read to answer it, we decided to handle null values inside a specific question only in the cases where they were actually relevant. (If we do so it will be specified in the *methods.py* file)  QUESTION 1 Which is the rate of complete funnels? First of all we read the DataFrame, importing only the columns that we need. In this particular case, for the subquestions 1.1-1.2-1.3 we just need *user_session*,*event_type* and *product_id*<jupyter_code>df1 = methods.loadAllDatasets(df_names, ['user_session','event_type','product_id'])<jupyter_output><empty_output><jupyter_text>While, for the subquestions 1.4 and 1.5 we need *user_session*,*event_type*, *product_id* and *event_time*. We also need to parse the last column so that it will be recognized as a date and not as a string<jupyter_code>df2 = methods.loadAllDatasetsWithParser(df_names)<jupyter_output><empty_output><jupyter_text>**RQ1.1** What’s the operation users repeat more on average within a session? Produce a plot that shows the average number of times users perform each operation (view/removefromchart etc etc)<jupyter_code>methods.avg_operations_performed(df1)<jupyter_output><empty_output><jupyter_text>As we can notice in the graph above, in average, users tend to view products a lot, but, only in few occasions, the put them in the cart. Also, after one item is inside a cart is not garanteed to be purchased, in fact that operation has a lower average.The event type *remove from cart* has an average of 0 beacuse in the months of October and November never happened. &nbsp; **RQ1.2** How many times, on average, a user views a product before adding it to the cart?<jupyter_code>avg = methods.avg_views_before_cart(df1)
print(f"In average, a user views a product {avg} times before adding it to the cart.")<jupyter_output>In average, a user views a product 1.874 times before adding it to the cart.<jupyter_text>&nbsp; **RQ1.3** What’s the probability that products added once to the cart are effectively bought?<jupyter_code>avg = methods.avg_purchase_after_cart(df1)
print(f"The probability that products added once to the cart are effectively bought is: {avg}")<jupyter_output>The probability that products added once to the cart are effectively bought is: 0.38<jupyter_text>&nbsp; **RQ1.4** What’s the average time an item stays in the cart before being removed?<jupyter_code>methods.avg_time_cart_before_removal(df2)<jupyter_output><empty_output><jupyter_text>**!!** This function can not be runned if we take only into account October and November because, in these months the event remove_from_cart does not exists as we can see below:<jupyter_code>df2.event_type.unique()<jupyter_output><empty_output><jupyter_text>If we try and run it we will get an error since we are performing operations (in particular divisions) with an empty series.&nbsp; **RQ1.5** How much time passes on average between the first view time and a purchase/addition to cart? There may be different interpretation for this question: we decided to calculate the time that passes between the first view and the action add to cart / purchase for a same product id **inside** the same user_session First we want to know the average time between the events *view* and *cart* for the same product:<jupyter_code>avg = fn.avg_time_between_view_and_cart(df2)
print(f"The average time that passes between the first time that an item is viewed and the moment in which that item is added to the cart is approximately: {round(avg,2)} mins")<jupyter_output><empty_output><jupyter_text>&nbsp;Then we want to know the average time between the events *view* and *purchase* for the same product:<jupyter_code>avg = fn.avg_time_between_view_and_purchase(df2)
print(f"The average time that passes between the first time that an item is viewed and the moment in which that item is purchased is approximately: {round(avg,2)} mins")<jupyter_output><empty_output><jupyter_text>&nbsp; So, to answer the main question: we noticed that users tend to view a product an average of 1.874 times before adding it to the cart. Once an item is added to the cart, it is not garanteed to be purchased, in fact only with probability 0.38 this happens. The rest of the times the item just stays in the cart and it could expire (removed automatically) or is removed from it manually by the user.In conclusion the rate of complete funnels, which is given by completed operations (from view to purchase) over number of total oprations is low.  QUESTION 2 **RQ2.1** What are the categories of the most trending products overall? For each month visualize this information through a plot showing the number of sold products per category. Before executing our functions we import our datasets and select only the useful columns: ***'category_code', 'event_type', 'product_id'***.This operation is made because the csv files are heavy to load and use. Also because we will use the same dataset with the same columns for all the subquestions<jupyter_code>#import november dataset
ndt_selection = methods.loadOneDataset(df_names[0], ['category_code', 'event_type', 'product_id'])<jupyter_output><empty_output><jupyter_text>We imported also the october dataset<jupyter_code>#import october dataset
odt_selection = methods.loadOneDataset(df_names[1], ['category_code', 'event_type', 'product_id'])<jupyter_output><empty_output><jupyter_text>&nbsp;To obtain our answer we restricted the dataset to the "event_type" column, in our case equal to "purchase".  ***NOVEMBER***<jupyter_code>#plot the categories of the most trending products overall
ndt_select = methods.restrict_dt(ndt_selection, "purchase", 10)<jupyter_output><empty_output><jupyter_text>Ww can now show the plot that we want for this question.<jupyter_code>methods.plot_n_categories(ndt_select)<jupyter_output><empty_output><jupyter_text>We see that the category **electronics** is the most sold.  ***OCTOBER***<jupyter_code>#plot the categories of the most trending products overall
odt_select = methods.restrict_dt(odt_selection, "purchase", 10)<jupyter_output><empty_output><jupyter_text>Show the plot that we want for this question.<jupyter_code>methods.plot_n_categories(odt_select)<jupyter_output><empty_output><jupyter_text>We see that the category **electronics** is the most sold. &nbsp; **RQ2.2** Plot the most visited subcategories. In the same way, we display the subcategories and do the same thing that we saw in the last question.  ***NOVEMBER***<jupyter_code>#plot the most visited 30 subcategories
sub_ndf = methods.dt_subcategories(ndt_selection)<jupyter_output><empty_output><jupyter_text>Show the plot that we want for this question.<jupyter_code>methods.plot_n_subcategories(sub_ndf, 30)<jupyter_output><empty_output><jupyter_text>In this plot we see that the **smartphone** subcategory is the most sold in November. &nbsp;  ***OCTOBER***<jupyter_code>#plot the most visited 30 subcategories
sub_odf = methods.dt_subcategories(odt_selection)<jupyter_output><empty_output><jupyter_text>Show the plot that we want for this question.<jupyter_code>methods.plot_n_subcategories(sub_odf, 30)<jupyter_output><empty_output><jupyter_text>In this plot we see that the **smartphone** subcategory is the most sold in October. &nbsp; **RQ2.3** What are the 10 most sold products per category?  ***NOVEMBER*** For each category we want to print the 10 most sold products, but in order to have a good visualization, we decided to print only the results for the first five categories.<jupyter_code>#the 10 most sold products per category
methods.most_sold_products_per_category(sub_ndf, 10)<jupyter_output>The category accessories has:
product_id
18300021    53
28401112    48
18300155    42
18300460    38
28400759    36
18300141    27
28400912    24
28401075    23
18300214    23
18300076    20
Name: category_code, dtype: int64
The category apparel has:
product_id
28720716    98
54900013    89
28716978    74
54900004    74
28719635    69
28716666    65
28719606    64
28716519    64
28719076    59
28713229    58
Name: category_code, dtype: int64
The category appliances has:
product_id
3700926    1764
3600661    1742
3601405    1140
3600666    1131
2900958     903
3700766     895
3601603     895
3600163     879
2702277     675
3701134     624
Name: category_code, dtype: int64
The category auto has:
product_id
6000094    1100
6000227     489
4700478     483
5701166     274
6000004     274
6000229     253
5701128     246
5701002     244
6000157     211
5701086     182
Name: category_code, dtype: int64
The category computers has:
product_id
1307310    1329
1307545     879
1307188     829
13070[...]<jupyter_text>***OCTOBER*** For each category we want to print the 10 most sold products, but in order to have a good visualization, we decided to print only the results for the first five categories, as we did for November.<jupyter_code>#the 10 most sold products per category
methods.most_sold_products_per_category(sub_odf, 10)<jupyter_output>The category accessories has:
product_id
18300155    63
18300021    34
52900016    31
28300780    24
49800017    23
28300432    21
18300595    17
28400774    16
18300496    16
18300214    16
Name: category_code, dtype: int64
The category apparel has:
product_id
28718083    72
28715756    46
28712682    45
28715827    40
28717034    39
28715757    39
28715829    38
28716983    38
28703609    38
54900011    37
Name: category_code, dtype: int64
The category appliances has:
product_id
3700926    1675
3600661    1482
3600163    1017
3600666     877
2900536     831
3601405     768
3601485     627
2701657     566
3601244     559
3701134     543
Name: category_code, dtype: int64
The category auto has:
product_id
6000094    785
4700478    411
5701128    382
6000227    360
5701166    304
4700630    300
4700589    235
6000229    214
6000004    206
5700518    165
Name: category_code, dtype: int64
The category computers has:
product_id
1307310    1003
1307073     864
1307366     722
1307067     651[...]<jupyter_text>QUESTION 3 For each category, what’s the brand whose prices are higher on average? To answer these questions we only need the columns 'category_code','brand','price'. For this reason we decided to read only these 3 when uploading the dataframes<jupyter_code>df3 = methods.loadAllDatasets(df_names, ['category_code','price','brand'])<jupyter_output><empty_output><jupyter_text>&nbsp; **RQ3.1** Write a function that asks the user a category in input and returns a plot indicating the average price of the products sold by the brand. First thing first, we apply a function that chooses randomly one category_code from all the ones available<jupyter_code>category = methods.choose_category(df3)
category<jupyter_output><empty_output><jupyter_text>Now that we have our category, we call a function that shows the average price of the products sold by each brand inside it<jupyter_code>methods.avg_price(category, df3)<jupyter_output><empty_output><jupyter_text>So, for example, if we select the category code **apparel.shirt**, there are several brands that offers it. Among all of those, the one that, in average, offers products with a higher price, is **weekend**&nbsp; **RQ3.2** Find, for each category, the brand with the highest average price. Return all the results in ascending order by price.<jupyter_code>methods.highest_avg_price(df3)<jupyter_output><empty_output><jupyter_text>The Data Frame in output has 3 different columns:- **0**: is representing the **category_code**- **1**: is representing the **brand** with the highest average price in that specific category code- **2**: is representing the **price** associated with that specific brandSo, for example, inside the category *accessories.umbrellas*, we can see that the highest price in averege is offered by *hoco* (25,71$), and this category is the fifth cheapest af all.The results are also sorted so that the prices are in order from the smallest to the largest value.&nbsp;  QUESTION 4 ***How much does each brand earn per month? Write a function that given the name of a brand in input returns, for each month, its profit.*** Before starting with the homework request, we pick a brand name by input for showing its profit for each month<jupyter_code>#which brand do you want to search.. randomize the choice!
brand_to_search = input("Choose a brand to see its profit: ")<jupyter_output>Choose a brand to see its profit: apple<jupyter_text>Before executing our functions we import our datasets and we select only the useful columns: ***'event_type', 'brand', 'price'***.This operation is made because the csv files are heavy to load and use. Also because we will use the same dataset with the same columns for all the subquestions<jupyter_code>#import november dataset
ndt_selection = methods.loadOneDataset(df_names[0], ['event_type', 'brand', 'price'])<jupyter_output><empty_output><jupyter_text>We imported also the october dataset<jupyter_code>#import october dataset
odt_selection = methods.loadOneDataset(df_names[1], ['event_type', 'brand', 'price'])<jupyter_output><empty_output><jupyter_text>To obtain our answer we restricted the dataset to the "event_type" column, in our case equal to "purchase" compare also with the brand_to_search.  ***NOVEMBER***<jupyter_code>#how much does each brand earn per month?
new_ndt_sum = methods.restrict_bypurchase_brand(ndt_selection, brand_to_search)<jupyter_output><empty_output><jupyter_text>How much is the profit of your brand?<jupyter_code>print(f"The {brand_to_search} has a profit of: {new_ndt_sum}$")<jupyter_output>The apple has a profit of: 127512524$<jupyter_text>***OCTOBER***<jupyter_code>#how much does each brand earn per month?
new_oct_sum = methods.restrict_bypurchase_brand(odt_selection, brand_to_search)<jupyter_output><empty_output><jupyter_text>How much is the profit of your brand?<jupyter_code>print(f"The {brand_to_search} has a profit of: {new_oct_sum}$")<jupyter_output>The apple has a profit of: 111209268$<jupyter_text>***Is the average price of products of different brands significantly different?*** We will see...  ***NOVEMBER***<jupyter_code>#see the average for each brand, is significant?
new_ndt_mean = methods.restrict_bypurchase_brand_avg(ndt_selection)
new_ndt_mean<jupyter_output><empty_output><jupyter_text>***OCTOBER***<jupyter_code>#see the average for each brand, is significant?
new_odt_mean = methods.restrict_bypurchase_brand_avg(odt_selection)
new_odt_mean<jupyter_output><empty_output><jupyter_text>***We can say that the brand name is significant for the product type (it's obvious).*** &nbsp;**RQ4.1** Using the function you just created, find the top 3 brands that have suffered the biggest losses in earnings between one month and the next, specifing both the loss percentage and the 2 months (e.g., brand_1 lost 20% between march and april).<jupyter_code>biggest_lose = methods.big_lose(ndt_selection, odt_selection)<jupyter_output><empty_output><jupyter_text>Now we plot the top 3 brands that have suffered the biggest losses in earnings between october and november.<jupyter_code>methods.summarize(biggest_lose)<jupyter_output>The brand jonnesway has lose the 2862 % between october and november
The brand sunfull has lose the 2399 % between october and november
The brand evga has lose the 2338 % between october and november<jupyter_text>&nbsp; QUESTION 5  In what part of the day is your store most visited? To answer the question, we only need to import two columns: *event_type* and *even_time*. We also need to parse the dates<jupyter_code>df5 = methods.import_dataset5(df_names)<jupyter_output><empty_output><jupyter_text>Now we can start analyzing our data. We can create different plots that show the most visited time of the day and the most visited day of the week<jupyter_code>methods.most_visited_time(df5)<jupyter_output><empty_output><jupyter_text>In this first plot we notice that usually people are more active it the afternoon, in fact the three most visited times of the day are the ones from 3PM to 5PM<jupyter_code>methods.most_visited_day(df5)<jupyter_output><empty_output><jupyter_text>In this second analysis we noticed that over all the days of the week **Friday** and **Saturday** are the one with more views *Notice that on the x-axis the day of the week are indicated by numbers from 0 to 6; where 0 corresponds to Monday and 6 to Sunday*.&nbsp;Now we can 'merge' the two results and see, for each day of the week the average views of our online store splitted by hour. **RQ5.1** Create a plot that for each day of the week shows the hourly average of visitors your store has.<jupyter_code>methods.avg_visitors_perday(df5)<jupyter_output><empty_output><jupyter_text>**Comment:** we can see how, on average, the hours of the day where the store is most visited are between 3pm and 5pm. This is valid for every day of the week. We can also notice that the graph follows pretty much the same shape during all week: the views start increasing quickly from midnight untill there is a 'plateau' for some hours (5am - 1pm). From 1pm there is a peak in the early afternoon that ends with a drastic drop of view at around 6pm that continues untill 12pm where we have the lowest values.**To answer the question**, we suggest to invest in ads and othere marketing strategies in the early hours of the afternoon in each day, because, as the graphics suggests, those are the hours with the most affluence.  QUESTION 6 ***What's the conversion rate of your online store?*** First of all... What is the conversion rate? **Conversion rate** of a product is given by the purchase rate over the number of times the product has been visited. ...and what is the purchase rate? **Purchase rate** is the proportion of purchases versus a base metric such as users, sessions, email subscribers, etc. with a generic formula being PR = P/N where P is the number of purchases and N is the number of events during which a conversion could have occurred.<jupyter_code>purchase_rate = methods.purchase_rate(df_names)<jupyter_output><empty_output><jupyter_text>now we calculate the conversion rate **RQ6.1** Find the overall conversion rate of your store.<jupyter_code>conversion_rate = methods.conversion_rate(purchase_rate, df_names)
conversion_rate<jupyter_output><empty_output><jupyter_text>The value returned is a good value to understand what is going on in our online store.. this is pretty low.. **RQ6.2** Plot the number of purchases of each category and show the conversion rate of each category in decreasing order.<jupyter_code>purchase_dt = methods.percategory_show_purchases(df_names)<jupyter_output><empty_output><jupyter_text>Now we will prepare our conversion rate per each category. We want to have the number of categories, then we will make the conversion rate.<jupyter_code>eachcategory_num = methods.number_categories(df_names)
eachcategory_num<jupyter_output><empty_output><jupyter_text>the conversion rate is for each category in **decreasing** oder<jupyter_code>cvrate = methods.conversion_rate_percategory(df_names, eachcategory_num)
cvrate<jupyter_output><empty_output><jupyter_text>we can say that there are lower purchase rates presented in our online store  QUESTION 7 The Pareto principle states that for many outcomes roughly 80% of consequences come from 20% of the causes. Also known as 80/20 rule, in e-commerce simply means that most of your business, around 80%, likely comes from about 20% of your customers.**Prove that the pareto principle applies to your store.** To answer this question we first have to import only the columns of the concatenated dataset that we need. In this case those are: *event_type*, *user_id*, *price* and *product_id*<jupyter_code>df7 = methods.loadAllDatasets(df_names,['event_type','price','user_id','product_id'])<jupyter_output><empty_output><jupyter_text>Now we can calculate the total profits of our store given from all the purchases made by all the users in two months. We also compute the 80% of them<jupyter_code>purchases = df7[df7.event_type=='purchase'].price.sum()
pur_80 = .8*purchases
print(f'80% of the profit corresponds to {int(pur_80)}$')<jupyter_output>80% of the profit corresponds to 404121914$<jupyter_text>We can now calculate how much the top 20% of the users (the ones that spent the most) spent during the whole months of October and November<jupyter_code>profit_from_top_users = methods.proof_pareto_principle(df7)
print(f'20% of the top users spend {int(profit_from_top_users)}$')<jupyter_output>20% of the top users spend 364222233$
{'fraction_non_alphanumeric': 0.06978520715377011, 'fraction_numerical': 0.05468679787903298, 'mean_word_length': 5.297396717600453, 'pattern_counts': {'":': 0, '<': 196, '<?xml version=': 0, '>': 196, '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': '51', 'dir': 'jupyter-structured-clean-dedup', 'id': '8386', 'license': 'MIT', 'n_tokens_mistral': 7333, 'n_tokens_neox': 6511, 'n_words': 2708, 'path': 'ADM-HOMEWORK2.ipynb', 'repo_name': 'giuliacasale/ADM-HW2', 'size': '22254'}