<jupyter_start><jupyter_text>DPhi Pandas Assignment DetailsDear Learner, Congratulations, you have studied Pandas all the way through the end of this bootcamp!!! This is the final graded hands-on exercise where you will be accomplishing the below mentioned task.  Step 1: Select a Dataset of your choice*   https://www.kdnuggets.com/datasets/index.html*   https://dphi.tech/challenges/past * https://pub.towardsai.net/best-datasets-for-machine-learning-data-science-computer-vision-nlp-ai-c9541058cf4f  * https://medium.com/analytics-vidhya/top-100-open-source-datasets-for-data-science-cd5a8d67cc3dor you can pick any dataset of your interest on the internet and add reference to it  Evaluation CriteriaYour submission will be evaluated using the following criteria:* You must ensure that the dataset is tabular.  * You must ask and answer at least 3 questions about the dataset*   Your submission must include explanations using markdown cells, apart from the code.*   Your work must not be plagiarized i.e. copied for somewhere else.<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import warnings
warnings.filterwarnings("ignore")<jupyter_output><empty_output><jupyter_text>**INTRODUCTION**:This is the dataset for an online shoppig website. Each entry is for a unique session, independent of the user.A session is a group of user interactions with your website that takes place within a given time frame. A single user can open multiple sessions but not concurrently. Opening multiple pages on different tabs of the same website is counted as a single session. By default, a session ends based on time (after 30mins of inactivity or at midnight or campaign change (if a user arrives via one campaign, leaves, and then comes back via a different campaign)The administrative, informational and product related describe the types of pages of the website. The duration represents the amount of time spent on that particular page.According to Google analytics, bounce rate is the percentage of all sessions on your site in which users viewed only a single page(first page) and triggered only a single request to the Analytics server. Exit Rate is the percentage that were the last in the session, for all pageviews that lead to the page.For example, a user finds their way to a page on your website and then closes the website without clicking any button on it. Here the bounce rate is a 100%. If the user decides to click a button or two more buttons that lead to other pages of the website then the bounce rate for that first page no longer exists. An exit rate would exist for the last page before he closes the website. There is neither a bounce nor exit rate for pages in between. This is not to say that bounce and exit rates are mutually exclusive but it will depend on the number of sessions considered.Page Value is the average value for a page that a user visited before landing on the goal page or completing an Ecommerce transaction (or both). This value is intended to give you an idea of which page in your site contributed more to your site's revenue. If the session does not end up as a revenue, page value will be still be recorded as long as the user makes it to the goal page. Each page has its own pagevalue, if set.All definitions have been gotten from [Google Analytics](https://support.google.com/analytics/?hl=entopic=10737980)  **OBJECTIVE**: To understand how the actions in a session affect a user's decision to make a purchase data source: https://s3.us-west-1.wasabisys.com/dphi/datasets/179/training_data.csv?AWSAccessKeyId=<KEY>&Signature=WfvqqwJ74ua9qD4K4IDignwn6lM%3D&Expires=1645556967from google.colab import filesuploaded = files.upload() Step 2: Perform Data Cleaning/Preparation & Analysis1. Create data frame(s) from CSV files2. Perform index operations on data frame(s)3. Apply Group, merge and aggregate data frame(s)4. Check for missing and invalid values in data5. Propose a way to treat the missing and invalid values and implement the same on the dataframe5. Compute the mean, sum, range and other interesting statistics for numeric columns<jupyter_code>shop_int_data = pd.read_csv("Online Shopping data.csv")
shop_int_data.head()
#understanding the data.
shop_int_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 9864 entries, 0 to 9863
Data columns (total 18 columns):
 #   Column                   Non-Null Count  Dtype  
---  ------                   --------------  -----  
 0   Administrative           9864 non-null   int64  
 1   Administrative_Duration  9864 non-null   float64
 2   Informational            9864 non-null   int64  
 3   Informational_Duration   9864 non-null   float64
 4   ProductRelated           9864 non-null   int64  
 5   ProductRelated_Duration  9864 non-null   float64
 6   BounceRates              9864 non-null   float64
 7   ExitRates                9864 non-null   float64
 8   PageValues               9864 non-null   float64
 9   SpecialDay               9864 non-null   float64
 10  Month                    9864 non-null   object 
 11  OperatingSystems         9864 non-null   int64  
 12  Browser                  9864 non-null   int64  
 13  Region                   9864 non-null   int64  
 14  TrafficType             [...]<jupyter_text>From the info, there are no missing values. It is easy to see that there are no duplicated columns. We check for duplicated rows.<jupyter_code>#we check for duplicated rows as this can cause a bias in our analysis
shop_int_data.duplicated().sum()
#assign the variable bool_series to the data retaining only the first of duplicates. 
#False shows that there are not duplicates.
bool_series = shop_int_data.duplicated(keep='first')

#Take all False and store as the data. View the dataset without the duplicated rows
shop_int_data = shop_int_data[~bool_series]
#check again for duplicates 
print(shop_int_data.duplicated().sum(), shop_int_data.shape)<jupyter_output>0 (9772, 18)<jupyter_text>After removing the duplicates, we have 9772 rows to work with.Another cleaning is to consider the page views and their duration.We could have said if more than 1 page was viewed then the duration cannot be zero but we did not because it is possible to switch from one page to another within a minute with a fast internet connection. So, we start from two.<jupyter_code>#if more than two product related pages were viewed then the duration cannot be 0
shop_int_data[(shop_int_data['ProductRelated'] >  2) & (shop_int_data['ProductRelated_Duration'] == 0)].shape[0]<jupyter_output><empty_output><jupyter_text>There ought to be no rows for the above code just as is the case with the other types of pages.Anyway, it is easy to guess that these sessions did not yield a revenue because the product related page was opened but no further action taken. No event was recorded since the duration is 0.0. All the multiple pages must have been opened on different tabs of the browser and subsequently closed.Therefore, we shall remove this data as it can create a bias.<jupyter_code># we drop the rows where more than two product related pages were viewed and the duration was 0.
bias_data = shop_int_data[(shop_int_data['ProductRelated'] >  2) & (shop_int_data['ProductRelated_Duration'] == 0)].index

shop_int_data.drop(bias_data, inplace = True)

shop_int_data.shape[0]<jupyter_output><empty_output><jupyter_text>Another cleaning to consider is that of the page value. Looking through the dataset, we can see that there is a column for pagevalue. This means that a goal page and its corresponding value must have been set on the website. The ecommerce transaction page would also have a page value which is the price of the transaction.<jupyter_code>#if revenue equals to 1 then page value cannot be zero
shop_int_data[(shop_int_data['Revenue'] ==  1) & (shop_int_data['PageValues'] == 0)].shape[0]<jupyter_output><empty_output><jupyter_text>In the above case, we may want to assume that the pages viewed including the page for ecommerce transaction has no page value set or were not included as a goal page. But this is not the case for this dataset.It is good practice in ecommerce to set a page value for product related and transaction pages. Before deciding on whether to get rid of them, let us confirm if page values are set for a product related page.<jupyter_code>#a product related page was viewed and a pagevalue was not assigned.

shop_int_data[(shop_int_data['ProductRelated'] >  0) & (shop_int_data['PageValues'] ==  0)].shape[0]<jupyter_output><empty_output><jupyter_text>Based on the above result, it appears that there are more unset pagevalues than set pagevalues. Therefore, we shall assume that no page value is set for the product related page.  If we are sure that a page value is set (because other sessions have a page value), then page values cannot be zero when there is a revenue and/or when a product related page is viewed (greater than 0). These pages are very important to understand the performance of a website. In any circumstance, the page for the online payment is will always have a page value. Therefore, we shall clean the above data where revenue is 1 and pagevalue is 0 but keep the data where a product related page was viewed and page value is still 0.<jupyter_code>#assuming a pagevalue is set for the payment page, let us delete the rows where pagevalue is 0 and revenue is 1 because it 
#is very likely a false revenue.

false_revenue1 = shop_int_data[(shop_int_data['PageValues'] ==  0) & (shop_int_data['Revenue'] == 1)].index

shop_int_data.drop(false_revenue1, inplace = True)

shop_int_data.shape[0]
#similarly, if revenue is 0 then page value must be zero because the user did not make it to the payment page.
false_revenue2 = shop_int_data[(shop_int_data['PageValues'] >  0) & (shop_int_data['Revenue'] == 0)].index

shop_int_data.drop(false_revenue2, inplace = True)

shop_int_data.shape[0]<jupyter_output><empty_output><jupyter_text>This brings us to the end of cleaning the data. Now, let us check the info to identify the changes.<jupyter_code>shop_int_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 8520 entries, 0 to 9863
Data columns (total 18 columns):
 #   Column                   Non-Null Count  Dtype  
---  ------                   --------------  -----  
 0   Administrative           8520 non-null   int64  
 1   Administrative_Duration  8520 non-null   float64
 2   Informational            8520 non-null   int64  
 3   Informational_Duration   8520 non-null   float64
 4   ProductRelated           8520 non-null   int64  
 5   ProductRelated_Duration  8520 non-null   float64
 6   BounceRates              8520 non-null   float64
 7   ExitRates                8520 non-null   float64
 8   PageValues               8520 non-null   float64
 9   SpecialDay               8520 non-null   float64
 10  Month                    8520 non-null   object 
 11  OperatingSystems         8520 non-null   int64  
 12  Browser                  8520 non-null   int64  
 13  Region                   8520 non-null   int64  
 14  TrafficType             [...]<jupyter_text>Step 3: Exploration & Inference1. Ask at least 3 interesting questions about your dataset2. Answer the questions by computing the results using Pandas library3. Add concluding remarks  **1. How many percentage of the dataset yielded a revenue?**<jupyter_code>#all rows for positive revenue
shop_int_data[shop_int_data['Revenue'] == 1].shape[0]
shop_int_data.shape[0]
#no of rows with positive revenue times 100 divided by the total no of rows after cleaning

((shop_int_data[shop_int_data['Revenue'] == 1].shape[0]) * 100)/ shop_int_data.shape[0]<jupyter_output><empty_output><jupyter_text>14.5% of the entire dataset yielded a postive revenue. Some of the positive revenue were cleaned out as false revenue.263 revenue were from New visitors and 11 from Other visitos while the rest (965) were from returning visitors. 86% of the dataset yielded zero revenue. This means we have more data for no revenue than for revenue.Although there are less entries for revenue, most of the analysis will be based on revenue to see how the ecommerce company can further improve on them.<jupyter_code>#representation of all website visitors

visitor_numbers = shop_int_data['VisitorType'].value_counts()
plt.title("Piechart of Website's Visitors")

visitor_type = ['Returning_Visitor','New_Visitor', 'Other' ]
plt.pie(visitor_numbers, labels = visitor_type, autopct='%0.1f%%')

print('')
plt.show()<jupyter_output><jupyter_text>**2. What region did most visitors come from?**<jupyter_code>#region of all visitors
shop_int_data['Region'].value_counts()

#483 buyers from region 1.
shop_int_data[(shop_int_data['Revenue']==1)]['Region'].value_counts().head(1)<jupyter_output><empty_output><jupyter_text>Most of the website visitors are from region 1. And most of the visitors who actually bought are also from region 1. There are 9 regions in total.  **3. What is the average time needed for a visitor to decide to make a purchase?**<jupyter_code>#addition of all durations
shop_int_data['total_time'] = shop_int_data.Administrative_Duration + shop_int_data.Informational_Duration + shop_int_data.ProductRelated_Duration

shop_int_data.head()
#find max, mean, min of total_time where revenue is 1
revenue_time = shop_int_data[(shop_int_data['Revenue']==1)]['total_time']

max(revenue_time), revenue_time.mean(), min(revenue_time)<jupyter_output><empty_output><jupyter_text>This shows that the minimum duration for a user to decide to make a purchase is 15.6s, the max duration is 23340s and the average duration is 2114s. Spending a longer duration on the website also does not indicate that the user will be convinced to buy. The highest duration spent on this website is 34576.95 and it yielded 0 revenue.   **4. What browser and operating system is mostly used? How does this affect navigating the website and making a purchase?**<jupyter_code>#OS mostly used
shop_int_data['OperatingSystems'].value_counts().head(1)
#Browser mostly used
shop_int_data['Browser'].value_counts().head(1)
#displaying the data of the most used browser and OS with positive revenue
shop_int_data[(shop_int_data['OperatingSystems']==2) & (shop_int_data['Browser']==2) & (shop_int_data['Revenue']==1)].shape[0]
#when revenue is 0
shop_int_data[(shop_int_data['OperatingSystems']==2) & (shop_int_data['Browser']==2) & (shop_int_data['Revenue']==0)].shape[0]<jupyter_output><empty_output><jupyter_text>There are more zero revenue when browser 2 and OS 2 are used so this does not really affect the decision to make a purchase. However, it might make navigating the website much more easier.  **4. Using more desriptive traffic sources, analyse the dataset.** The entries of traffic type for the dataset does not give enough information. Traffic type defines the method or medium by which visitors reach the website.For this analysis and as an assumption, I have decided to group the numbers into 4 groups and assign each group a traffic medium based on Google Analytics:1. 1-5 Direct traffic2. 6-10 Organic referral3. 11-15 Social referral4. 16-20 Paid referralDirect means that they directly used the site's URL link to get to the website. It could be that they have it stored in their browser bookmarks.Organic means they got to the website through a search engine like google, bing.Social means they used a link found on social media like twitter, facebook, etc.Paid means they clicked a sponosred ad used to promote the website.<jupyter_code>#form the traffic groups and insert into the dataset
shop_int_data['TrafficType'].replace(dict.fromkeys([1, 2, 3, 4, 5], 'Direct'), inplace = True)
shop_int_data['TrafficType'].replace(dict.fromkeys([6, 7, 8, 9, 10], 'Organic'), inplace = True)
shop_int_data['TrafficType'].replace(dict.fromkeys([11, 12, 13, 14, 15], 'Social'), inplace = True)
shop_int_data['TrafficType'].replace(dict.fromkeys([16, 17, 18, 19, 20], 'Paid'), inplace = True)
shop_int_data.head()<jupyter_output><empty_output><jupyter_text>The numbers in the traffic column have been replaced by more descriptive traffic sources to a website.<jupyter_code>#we group the dataset based on traffic type
traffic_data = shop_int_data.groupby(['TrafficType'])

#This shows the first members of each group
traffic_data.first() 
#we view some members of the organic traffic group
traffic_data.get_group('Organic').head()<jupyter_output><empty_output><jupyter_text>The group is only formed in the traffic_data and does not apply to the main dataset called shop_int_data **Q.  Bounce rate is calculated for the pages through which the user enters the website which we assume to be landing page for all sessions. How often did visitors click the wrong link? How does bounce rate affect the revenue?** In reality, bounce rate is calculated when a visitor opens then closes the website. It is calculated for the first page of the website that a visitor gets to. For different users in one session, they will have different first pages depending on how they got to the website.Because this is an ecommerce website and we expect visitors to view more than one page so a high bounce rate of 1 is bad.<jupyter_code>#find the mean bounce rate
shop_int_data['BounceRates'].mean()<jupyter_output><empty_output><jupyter_text>0.02 is a quite low bounce rate. This means that the people who stayed and continued browsing after the landing page opened are more than those who bounced off for the overall sessions of the dataset.<jupyter_code>#are there bounce rates of 1 that lead to a revenue? No.
shop_int_data[(shop_int_data['BounceRates']==1) & (shop_int_data['Revenue']==1)]
#are there bounce rates higher than the mean that leads to a revenue?
shop_int_data[(shop_int_data['BounceRates'] > (shop_int_data['BounceRates'].mean())) & (shop_int_data['Revenue']==1)]['TrafficType'].shape[0]<jupyter_output><empty_output><jupyter_text>As we can see from the result of this code, there are less sessions where the bounce rate was higher than the mean bounce rate and a revenue was made. For bounce rate, we are only concerned about the initial page and how it affects the rest of the user's interaction with the website. How many people entered through this page? Did they leave or did they continue to browse the website? A low rate means they stayed and a high rate means they left.A higher percentage of visitors in the above result showed that they got to the website directly through its link, so they had no reason to leave and consequently made a purchase.  **Q) Exit rate is calculated for the pages through which the users leave the website. How often did these clicks make it to the revenue page ?** Assume the exit rate here is for the payment page. Users leave the website through this page to the external payment platform e.g Paypal. So, we want a high exit rate.  That the visitors got to the payment page does not guarentee that they will pay. A non zero page value is the only indicator that a payment was made (as we noted during the preparation of the data).<jupyter_code>#find the mean exit rate for all the sessions.
shop_int_data['ExitRates'].mean()<jupyter_output><empty_output><jupyter_text>A mean of 0.04 shows that less people left through the payment page than those who, lets say, returned to the product related page or to the landing page.  **5.) Do you think paid ads were effective for the purpose of special day?**<jupyter_code>#type of traffic for all special day visit
shop_int_data[(shop_int_data['SpecialDay'] > 0)]['TrafficType'].value_counts()
#special day purchase based on traffic type
shop_int_data[(shop_int_data['SpecialDay'] > 0) & (shop_int_data['Revenue'] == 1)]['TrafficType'].value_counts()<jupyter_output><empty_output><jupyter_text>Apparently, paid ads had no significant effect on purchases related to special day. A closer analysis shows that over 50% of the website visitors are by direct traffic. This suports the earlier analysis that 85% of the website visitors are Returning Visitors. This means that these visitors already have a direct link to the website.  **6.) Import a dataframe of age and sexes. Merge with the shop_int_data dataset and use if for the following analysis.** As an assumption, this dataset will indicate the user for each session. from google.colab import filesuploaded = files.upload()<jupyter_code>mall_customers = pd.read_csv('Mall_Customers.csv')
mall_customers.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 200 entries, 0 to 199
Data columns (total 5 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   Customer_id  200 non-null    int64 
 1   Gender       200 non-null    object
 2   Age          200 non-null    int64 
 3   Income       200 non-null    int64 
 4   Spend_score  200 non-null    int64 
dtypes: int64(4), object(1)
memory usage: 7.9+ KB<jupyter_text>The annual income is in thousand dollars.Since there are just 200 rows in the user dataset, we will create a smaller version of the shop_int_data dataset then merge with the user dataset.<jupyter_code>#lets pick 200 rows between 5800 and 7000 of the shop_int_data
shop_int_data_short = shop_int_data.loc[5800:6030]


#drop unneccsary columns
shop_int_data_short.drop(['Administrative', 'Administrative_Duration','Informational', 'Informational_Duration','BounceRates', 'ExitRates', 'PageValues', 'Month',	'OperatingSystems',	'Browser','total_time'], axis =1, inplace=True)
shop_int_data_short.columns<jupyter_output><empty_output><jupyter_text>The two dataframes: shop_int_data_short and mall_customers do not have any series in common. i.e no common column(s).So we insert the CustomerID series into the shop_int_data_short dataframe so that they can have a common column and then we use the merge function. By this way, we also get rid of the default index of the shop_int_data_short.<jupyter_code>#this creates a common column, customerID, for the two datasets

shop_int_data_short['Customer_id'] = np.linspace(1,200,200)
shop_int_data_short.columns
#the datasets get merged and the common column is not repeated
users_data = mall_customers.merge(shop_int_data_short)
users_data.head()<jupyter_output><empty_output><jupyter_text>**Q) Which gender made the most purchase and spent more time on the website?**<jupyter_code>#the number of males and females in the dataset
users_data['Gender'].value_counts()
#the number of females who made a purchase
users_data[(users_data['Gender'] =='Female') & (users_data['Revenue'] == 1)].shape[0]
#the number of males who made a purchase
users_data[(users_data['Gender'] =='Male') & (users_data['Revenue'] == 1)].shape[0]
#the total time spent by females on the website
users_data[(users_data['Gender'] =='Female')]['ProductRelated_Duration'].sum()
#the total time spent by males on the website
users_data[(users_data['Gender'] =='Male')]['ProductRelated_Duration'].sum()
#the total time spent by all males who made a purchase
users_data[(users_data['Gender'] =='Male') & (users_data['Revenue'] == 1)]['ProductRelated_Duration'].sum()
#the total time spent by all females who made a purchase
users_data[(users_data['Gender'] =='Female') & (users_data['Revenue'] == 1)]['ProductRelated_Duration'].sum()<jupyter_output><empty_output><jupyter_text>Equal number of men and women made purchases on the website but men spent more time buying than women.Generally, women spent more time on the website since there are more women than men in this dataset. **Q.) What age grade has the highest income? How does this affect their spending?**<jupyter_code>max(users_data['Income']), users_data['Income'].mean(), min(users_data['Income'])
#details of users who earned above the mean
users_data[(users_data['Income'] >= 60.56)].describe()
#details of users who earned above the mean and made a purchase
users_data[(users_data['Income'] >= 60.56) & (users_data['Revenue'] == 1)].describe()
#the income of everyone who made a purchase
users_data[users_data['Revenue'] == 1]['Income'].describe()<jupyter_output><empty_output><jupyter_text>A little over half of the dataset earn above the mean income and made a purchase.In the entire dataset, only 12 people out of 102 with an annual income above the mean made a purchase. This is closely related to those earning below the mean income.Definitely, the annual income has no effect in deciding whether to buy or not.<jupyter_code>max(users_data['Age']), users_data['Age'].mean(), min(users_data['Age'])
#age range of those who earned above the annual income and made a purchase
users_data[(users_data['Income'] > 60.56) & (users_data['Revenue'] == 1)]['Age'].describe()
#age range of those earned below the annual income and made a purchase
users_data[(users_data['Income'] <= 60.56) & (users_data['Revenue'] == 1)]['Age'].describe()
#the age range of people who made no purchase
users_data[users_data['Revenue'] == 0]['Age'].describe()<jupyter_output><empty_output><jupyter_text>More people who earned lesser than the mean income made a purchases. Most of them were below the mean age of 38. **Q.) How often were purchases done on weekends?**<jupyter_code>users_data[(users_data['Revenue'] == 1) & (users_data['Weekend'] == True)].shape[0]
users_data[(users_data['Revenue'] == 1) & (users_data['Weekend'] == False)].shape[0]<jupyter_output><empty_output>