<jupyter_start><jupyter_text>Data Cleaning      Load Libraries<jupyter_code>import pandas as pd
import numpy as np

#importing liabraries for graphical representation
import matplotlib.pyplot as plt
import seaborn as sns
sns.set(style="darkgrid")
%matplotlib inline

%%HTML
<style type="text/css">
table.dataframe td, table.dataframe th {
    border: 1px  black solid !important;
  color: black !important;
}
</style><jupyter_output><empty_output><jupyter_text>Data Understanding  - user_agent: device, os system, etc. used by customer- referred_to_url: site for touchpoint- created_at: reference date- visitor_source: channel source- conv__life:  stage of funnel (1 is PRIMARY RESPONSE LABEL)- conv__life_date: date of conversion- exit_survey_source_category: exit survey response for source- exit_survey_submitted_product: exit survey product type- exit_survey_source: exit source detailed response from source_category- product_visited: product associated with referral site     Load the data into a dataframe<jupyter_code>data_file = '../data/data_mta_mta_data_000000000001.csv'
df = pd.read_csv(data_file)
#number of observations and features

print("Data Shape")
print("----------")
print("Number of columns:", df.shape[1])
print("Number of observations:", df.shape[0])

df.head(5)<jupyter_output>Data Shape
----------
Number of columns: 15
Number of observations: 2661982<jupyter_text>Data Preprocessing & Analysis     Data Analysis We have to make sure the data is suitable and well prepared for any analysis or model we want to use it for.   - **Print a concise summary of a DataFrame**          This method prints information about a DataFrame including the index dtype and column dtypes, non-null values and memory usage.<jupyter_code>#Segregating the numeric and categorical variable names
numeric_var_names = [key for key in dict(df.dtypes) if dict(df.dtypes)[key] in ['float64', 'int64', 'float32', 'int32']]
catgorical_var_names = [key for key in dict(df.dtypes) if dict(df.dtypes)[key] in ['object']]
print("There are %d numeric features and %d categorical features" %(len(numeric_var_names),len(catgorical_var_names)), "\n \n")


#checking for the data types and file size.
df.info(verbose=True)<jupyter_output>There are 3 numeric features and 12 categorical features 
 

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2661982 entries, 0 to 2661981
Data columns (total 15 columns):
 #   Column                         Dtype  
---  ------                         -----  
 0   user_analytics_id              object 
 1   user_agent                     object 
 2   referred_to_url                object 
 3   created_at                     object 
 4   visitor_source                 object 
 5   conv_1_life                    float64
 6   conv_1_life_date               object 
 7   conv_2_life                    float64
 8   conv_2_life_date               object 
 9   conv_3_life                    float64
 10  conv_3_life_date               object 
 11  exit_survey_source_category    object 
 12  exit_survey_submitted_product  object 
 13  exit_survey_source             object 
 14  product_visited                object 
dtypes: float64(3), object(12)
memory usage: 304.6+ MB<jupyter_text>Unique Values - **Check the unique values for each column**Find out the columns that has too few/ too much unique data. Inspect these columns to decide whether to remove them or not<jupyter_code>unique_rate = df.apply(lambda x: len(pd.unique(x)),axis = 0).sort_values(ascending = False) #unique rate and sort
unique_rate<jupyter_output><empty_output><jupyter_text>- **Inference**Let us take a look at unique values for some column and the type of values contained in _ and _<jupyter_code>def get_unique(unique_list,df):
    for var in unique_list:
        print(var,"  :  ",df[var].unique(),"\n")
        
unique_list = ['product_visited','exit_survey_submitted_product','visitor_source','exit_survey_source_category']
get_unique(unique_list,df)<jupyter_output>product_visited   :   ['other' 'life' 'home' 'auto'] 

exit_survey_submitted_product   :   [nan 'life' 'Home and Auto' 'renters' 'disability'] 

visitor_source   :   ['Organic - SEO' 'Email' 'Influencer' 'Partnerships' 'Earned Media'
 'Direct' 'Paid Email' 'Paid Search - NB' 'Facebook' 'YouTube' 'Display'
 'Connected TV/TV' 'Paid Search - B' 'Blog' 'Organic - Brand' 'Lead Gen'
 'Referral' 'Gmail' 'Paid Social' 'Other' 'Native' 'Internal' 'Warm Lead'
 'Social'] 

exit_survey_source_category   :   [nan 'google_or_other_search_engine' 'mailer' 'online_article' 'other'
 'instagram' 'facebook' 'quora' 'email' 'pandora' 'podcast'
 'friend_family_co-worker_told_me_about_it' 'my_financial_advisor'
 'newsletters' 'tv_commercial' 'youtube' 'roku_apple_tv_etc'
 'billboard_or_outdoor_advertisement' 'radio' 'linkedin' 'twitter'
 'streeteasy' 'perkspot' 'gridwise' 'subway_ad' 'elevator_or_office_lobby'
 'search_engine' 'nextdoor' 'apple_news' 'ad_on_a_website' 'ad_online'
 'personal_referral' 'spoti[...]<jupyter_text>----    Null Values<jupyter_code>#Columns with Null Values
df.columns[df.isna().any()].tolist()
import missingno as msno 
msno.matrix(df, figsize=(9, 4),  fontsize=10)<jupyter_output><empty_output><jupyter_text>-----    Fill Null Values     conversion Conversion is a binary columns: - if the user has converted = 1- if the user has not converted = 1<jupyter_code>def replace_nan(df, col_list, fill_val):
    for var in col_list:
        df[var].fillna(fill_val, inplace=True)
        
col_list = ['conv_1_life','conv_2_life','conv_3_life']



replace_nan(df, col_list, fill_val = 0)<jupyter_output><empty_output><jupyter_text>referred_to_url<jupyter_code>df.head(1)<jupyter_output><empty_output><jupyter_text>Identify 'visitor_source' for which the referred to URL is null<jupyter_code>df[df['referred_to_url'].isnull()]['visitor_source'].unique()<jupyter_output><empty_output><jupyter_text>URL is missing for many visitor_sources.<jupyter_code>df[df['visitor_source'] == 'Direct']['referred_to_url'].unique()
df[df['visitor_source'] == 'Warm Lead']['referred_to_url'].unique()<jupyter_output><empty_output><jupyter_text>Inference: For a user with visitor source 'Direct', we can notice that referred to URL is '/'  Fill nan values in referred_to_url as following1. referred_to_url == Direct when visitor_source == '/'2. referred_to_url == unknown for any other nan/missing values<jupyter_code>df.loc[df.visitor_source == 'Direct','referred_to_url'] = '/'
df['referred_to_url'].fillna('unknown', inplace=True)<jupyter_output><empty_output><jupyter_text>user_agent<jupyter_code>df['user_agent'].fillna('unknown', inplace=True)<jupyter_output><empty_output><jupyter_text>exit_survey_source_category | exit_survey_submitted_product | exit_survey_source<jupyter_code>df['exit_survey_source_category'].fillna('unknown', inplace=True)
df['exit_survey_submitted_product'].fillna('unknown', inplace=True)
df['exit_survey_source'].fillna('unknown', inplace=True)<jupyter_output><empty_output><jupyter_text>---      Check Sessions for users who Converted<jupyter_code>df[df['conv_1_life'] == 1]['user_analytics_id'].unique()<jupyter_output><empty_output><jupyter_text>If the user has converted, is it Consistent for all the entries?<jupyter_code>df[df['user_analytics_id']== '0000876502e244daac0d7c4fafc91392']['conv_1_life'].unique()<jupyter_output><empty_output><jupyter_text>Convert Data Type for Date columns     - **conversion_date**<jupyter_code>df['conv_1_life_date'] = pd.to_datetime(df['conv_1_life_date'])
df['conv_2_life_date'] = pd.to_datetime(df['conv_2_life_date'])
df['conv_3_life_date'] = pd.to_datetime(df['conv_3_life_date'])<jupyter_output><empty_output><jupyter_text>- **created_at** Create Date and Time columns from 'created_at'<jupyter_code>df['created_at'] = df['created_at'].map(lambda x: x.rstrip(' UTC'))
df['created_at'] = pd.to_datetime(df['created_at'])<jupyter_output><empty_output><jupyter_text>Create new columns<jupyter_code>#1.Created a Date and Time column from created_at

df['Time'],df['Date']= df['created_at'].apply(lambda x:x.time()), df['created_at'].apply(lambda x:x.date())


df['time_diff_sec'] = df.groupby('user_analytics_id')['created_at'].diff()

df.head(1)
df['time_diff_sec'] = df.groupby('user_analytics_id')['created_at'].diff()<jupyter_output><empty_output><jupyter_text>Sort      It is important to sort the dataframe before processing it to study about the user's data points and duplicates. Let us sort the Dataframe with user_id, Date and Time<jupyter_code>df.sort_values(['user_analytics_id','Date','Time'], inplace=True)
df.reset_index(drop=True, inplace=True)
df.head(4)<jupyter_output><empty_output><jupyter_text>Analyze the time difference between the two clicks   We can see that the most common time difference between the two clicks is < 60 min<jupyter_code>#Plot Time difference < 60 mins

plt.figure(figsize=(8,5))
sns.kdeplot(df[df['time_diff_sec'] <= pd.Timedelta(60,'m')]['time_diff_sec'].dt.components['minutes'], color="darkturquoise", shade=True)
plt.legend(['Time between the two clicks'])
plt.title('Density Plot of Time (min) between the two clicks ')
plt.xlabel('Time in min')
plt.show()<jupyter_output><empty_output><jupyter_text>We can see that the most common time difference between the two clicks is < 60 sec<jupyter_code>#Plot Time difference < 60 mins

plt.figure(figsize=(8,5))
sns.kdeplot(df[df['time_diff_sec'] <= pd.Timedelta(60,'s')]['time_diff_sec'].dt.components['seconds'], color="darkturquoise", shade=True)
plt.legend(['Time between the two clicks'])
plt.title('Density Plot of Time (sec) between the two clicks ')
plt.xlabel('Time in sec')
plt.show()<jupyter_output><empty_output><jupyter_text>The Time difference indicates Duplicate rows which might be caused due to:        1. website redirecting creates additional touchpoints within a short timeframe (< 1 minute).    2. user click on the campaign link within a short timeframe (< 1 minute).       ---       Change 1    referred_to_url<jupyter_code>df[df['user_analytics_id']=='000ddd7a158043beac42f14d418cd574']['referred_to_url'].unique()<jupyter_output><empty_output><jupyter_text>We can see below that these users have different Referred URL's with in the same session.<jupyter_code>df['referred_to_url'] = df['referred_to_url'].astype('str')
df['referred_to_url'] = df['referred_to_url'].apply(lambda x: x + '/' if not x.endswith('/') else x)<jupyter_output><empty_output><jupyter_text>To solve: we could add ‘/’ at the end of the string if not already present.<jupyter_code>df[df['user_analytics_id']=='000ddd7a158043beac42f14d418cd574']['referred_to_url'].unique()<jupyter_output><empty_output><jupyter_text>Change 2    Currently, the product_visited column is created from referred_to_url as mentioned below:<jupyter_code>#Import library
from IPython.display import Image

# Load image from local storage
Image(filename = "../code.png", width = 600, height = 300)<jupyter_output><empty_output><jupyter_text>product_visited<jupyter_code>df[df['product_visited'] == 'life']['referred_to_url'].unique()<jupyter_output><empty_output><jupyter_text>Let us identify the referred_to_url that contains the word "life" but is not categorized as life in the product visited column<jupyter_code>product_visited_df = pd.DataFrame(df[(df['referred_to_url'].str.contains("life", na=False)) & (df['product_visited'] != 'life')][['referred_to_url','product_visited']]\
             .groupby(['product_visited'])['referred_to_url'].unique()).reset_index()



product_visited_df
print("auto: ", len(list(product_visited_df[product_visited_df['product_visited'] == 'auto']['referred_to_url'][0])))
print("home: ", len( list(product_visited_df[product_visited_df['product_visited'] == 'home']['referred_to_url'][1])))
print("other: ", len( list(product_visited_df[product_visited_df['product_visited'] == 'other']['referred_to_url'][2])))<jupyter_output>auto:  1
home:  3
other:  15149<jupyter_text>**product_visited = auto**Check unique referred_to_url for product visited 'auto'<jupyter_code>product_visited_df[product_visited_df['product_visited']== 'auto']['referred_to_url'][0].tolist()<jupyter_output><empty_output><jupyter_text>**product_visited = home**Check unique referred_to_url for product visited 'home'<jupyter_code>product_visited_df[product_visited_df['product_visited']== 'home']['referred_to_url'][1].tolist()<jupyter_output><empty_output><jupyter_text>**product_visited = other**Check unique referred_to_url for product visited 'auto'- /umbrella/v1/gold-rush/lincoln-national-life/- /blog/new-parent-additional-life-insurance/- /investopedia-life-protective- /blog/is-pet-life-insurance-worth-it/- /creditkarma-life/- /nerdwallet-life/- /blog/what-happens-when-youre-owed-life-insurance-money-dont-know-it/**life insurance related**- /policy-center/life/confirmation- /bestowlife- /policy-center/life- /physicianonfirelife/**non-life insurance related**- /defrancolife/- /whatsinsidelife/      Note 1:- If we categorized URLs that contain the word “life” we can increase the TP & FP equally. - Alternative: If we categorized URLs that contain the word “-life” we can increase the TP.     Note 1:- If we plan to categorize URLs that contain the word “life”, we have to make sure that it is not already categorized into auto / home / life. - In other words, it should be only applied where the product_visited is **NULL (OTHERS)**<jupyter_code>#product_visited_df[product_visited_df['product_visited']== 'other']['referred_to_url'][2].tolist()<jupyter_output><empty_output><jupyter_text>Replace product_visited = Life<jupyter_code>df.loc[(df['referred_to_url'].str.contains("-life", na=False)) & (df['product_visited'] == 'other'), 'product_visited'] = 'life'<jupyter_output><empty_output><jupyter_text>------   Change 3    Duplicates      Let us analyze the rows with less than 60 sec time difference<jupyter_code>#Plot Time difference < 60 mins

plt.figure(figsize=(8,5))
sns.kdeplot(df[df['time_diff_sec'] <= pd.Timedelta(60,'s')]['time_diff_sec'].dt.components['seconds'], color="darkturquoise", shade=True)
plt.legend(['Time between the two clicks'])
plt.title('Density Plot of Time (sec) between the two clicks ')
plt.xlabel('Time in sec')
plt.show()<jupyter_output><empty_output><jupyter_text>Plot the number of times the user visited the site within 60 seconds.<jupyter_code>#Plot Time difference < 5 mins

plt.figure(figsize=(8,5))
sns.kdeplot(df[df['time_diff_sec'] <= pd.Timedelta(60,'s')].user_analytics_id.value_counts().unique(), color="blue", shade=True)
#plt.legend(['Number of times user visited'])
plt.title('Density Plot of number of times user visited')
plt.xlabel('Number of Times user visited')
plt.show()<jupyter_output><empty_output><jupyter_text>-------       Create Sessions    A session is a group of user interactions with your website that take place within a given time frame (a single session can contain multiple page views)We are following two methods by which a session ends:- Time-based expiration:    - After 30 minutes of inactivity    - At midnight        - Campaign change:    - If a user arrives via one campaign, leaves, and then comes back via a different campaign.            A single user can open multiple sessions. Those sessions can occur on the same day, or over several days, weeks, or months. As soon as one session ends, there is then an opportunity to start a new session.<jupyter_code># Sorting is needed, otherwise .diff() will output wrong results
df.sort_values(['user_analytics_id', 'created_at'], inplace=True)

# Timestamp diff in seconds
diff_timestamp = df.groupby(['user_analytics_id', 'Date'])['created_at'].diff()

# indexes where new session_id will be created
new_session = (diff_timestamp.isnull()) | (diff_timestamp >= pd.Timedelta(30, 'm'))

# Create unique session_id for every user
df['session_id'] = df.loc[new_session, ['user_analytics_id', 'created_at']] \
    .groupby('user_analytics_id').rank(method='first').astype(int)

# Propagate last valid observation forward (replace NaN)
df['session_id'] = df['session_id'].fillna(method='ffill').astype(int)<jupyter_output><empty_output><jupyter_text><jupyter_code>import pandas as pd
df = pd.read_csv("Created_30mSessions.csv")
df.drop('Unnamed: 0',axis=1 , inplace=True)<jupyter_output><empty_output><jupyter_text>Analysis         Example 1          USER: e92e0324865f41ae85c1feda74f566f6<jupyter_code>data = df[df['user_analytics_id']== 'e92e0324865f41ae85c1feda74f566f6']
data[['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']].reset_index(drop=True)[:20]<jupyter_output><empty_output><jupyter_text>---------   Example 2         USER: 1d9a49b7cd79499da2eecbfcc932209e     Note: Here, Session 3: has different “referred_to_url” source<jupyter_code>data = df[df['user_analytics_id']== '1d9a49b7cd79499da2eecbfcc932209e']
data[data['session_id']==3][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']].reset_index(drop=True)[0:5]<jupyter_output><empty_output><jupyter_text>-------   Example 3         USER: 05b42d0620e1422486a5db58b5849db2          There is hardly any difference between the two sessions<jupyter_code>data = df[df['user_analytics_id']== '05b42d0620e1422486a5db58b5849db2']
data = data[data['session_id']==1].reset_index(drop=True)

len(data)
df[df['user_analytics_id']== '05b42d0620e1422486a5db58b5849db2'][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']][1099:1101].reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Extended over period of 1 day<jupyter_code>df[df['user_analytics_id']== '05b42d0620e1422486a5db58b5849db2'][1099:1100][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']]
df[df['user_analytics_id']== '05b42d0620e1422486a5db58b5849db2'][1100:1101][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']]<jupyter_output><empty_output><jupyter_text>Example 4         USER: 05b42d0620e1422486a5db58b5849db2        There are 1100 records in Session 1<jupyter_code>data = df[df['user_analytics_id']== '05b42d0620e1422486a5db58b5849db2']
data = data[data['session_id']==1].reset_index(drop=True)
data[0:1][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']]

data[-1:][['user_analytics_id', 'user_agent', 'referred_to_url', 'visitor_source','product_visited', 'Time', 'Date','session_id']]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13149847094801223, 'fraction_numerical': 0.028435001877783143, 'mean_word_length': 6.327044025157233, 'pattern_counts': {'":': 0, '<': 172, '<?xml version=': 0, '>': 161, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '40', 'dir': 'jupyter-structured-clean-dedup', 'id': '327392', 'license': 'MIT', 'n_tokens_mistral': 6694, 'n_tokens_neox': 6241, 'n_words': 1384, 'path': 'Data_Cleaning_v2.ipynb', 'repo_name': 'gunjandesai20/gunjandesai20.github.io', 'size': '18639'}