<jupyter_start><jupyter_text>Hello World!This notebook describes the effort filter out users to resurrect with Digital Marketing Clean up datade-duplicate : based on email i'd               Partitioning the Data:two methods - A) cluster the data  and see how many clusters are there: used **MeanShift method**B) Bin the data based on *age_on_platform*email capaign to Ressurrect usersApril 30th will be the cuttoff for the first_login value, for Binning  Looking around the data set<jupyter_code>%reset
# Import the required modules
import pandas as pd
import numpy as np
import scipy as sp
# simple function to read in the user data file.
# the argument parse_dates takes in a list of colums, which are to be parsed as date format
user_data_raw_csv = pd.read_csv("/home/eyebell/local_bin/janacare/janCC/datasets/Habits-Data_upto-7th-May.csv",\
                            parse_dates = [-3, -2, -1])
# import the pyexcel module
#import pyexcel as pe
#from pyexcel.ext import xls

# load the file
#records = pe.get_records(file_name="/home/eyebell/local_bin/janacare/datasets/Habits-Data_upto-7th-May.xls")
#len(records)
#for record in records:
    #print record
# data metrics
user_data_raw_csv.shape # Rows , colums
# data metrics
user_data_raw_csv.dtypes # data type of colums
user_data_to_clean = user_data_raw_csv.copy()
# Some basic statistical information on the data
#user_data_to_clean.describe()<jupyter_output><empty_output><jupyter_text>Data Clean up In the last section of looking around, I saw that a lot of rows do not have any values or have garbage values(see first row of the table above).This can cause errors when computing anything using the values in these rows, hence a clean up is required. If a the coulums *last_activity* and *first_login* are empty then drop the corresponding row !<jupyter_code># Lets check the health of the data set
user_data_to_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 53239 entries, 0 to 53238
Data columns (total 9 columns):
user_id          53239 non-null int64
first_name       53108 non-null object
last_name        5360 non-null object
username         53239 non-null object
email            53089 non-null object
phone_number     53094 non-null object
date_joined      53239 non-null datetime64[ns]
first_login      50773 non-null datetime64[ns]
last_activity    44903 non-null datetime64[ns]
dtypes: datetime64[ns](3), int64(1), object(5)
memory usage: 3.7+ MB<jupyter_text>As is visible from the last column (*age_on_platform*) data type, Pandas is not recognising it as date type format. This will make things difficult, so I delete this particular column and add a new one.Since the data in *age_on_platform* can be recreated by doing *age_on_platform* = *last_activity* - *first_login*  But on eyeballing I noticed some, cells of column *first_login* have greater value than corresponding cell of *last_activity*. These cells need to be swapped, since its not possible to have *first_login* > *last_activity*Finally the columns *first_login*, *last_activity* have missing values, as evident from above table. Since this is time data, that in my opinion should not be imputed, we will drop/delete the columns.<jupyter_code># Run a loop through the data frame and check each row for this anamoly, if found drop,
# this is being done ONLY for selected columns

import datetime

swapped_count = 0
first_login_count = 0
last_activity_count = 0
email_count = 0
userid_count = 0

for index, row in user_data_to_clean.iterrows():        
        if row.last_activity == pd.NaT or row.last_activity != row.last_activity:
            last_activity_count = last_activity_count + 1
            #print row.last_activity
            user_data_to_clean.drop(index, inplace=True)

        elif row.first_login > row.last_activity:
            user_data_to_clean.drop(index, inplace=True)
            swapped_count = swapped_count + 1

        elif row.first_login != row.first_login or row.first_login == pd.NaT:
            user_data_to_clean.drop(index, inplace=True)
            first_login_count = first_login_count + 1

        elif row.email != row.email: #or row.email == '' or row.email == ' ':
            user_data_to_clean.drop(index, inplace=True)
            email_count = email_count + 1

        elif row.user_id != row.user_id:
            user_data_to_clean.drop(index, inplace=True)
            userid_count = userid_count + 1

print "last_activity_count=%d\tswapped_count=%d\tfirst_login_count=%d\temail_count=%d\tuserid_count=%d" \
% (last_activity_count, swapped_count, first_login_count, email_count, userid_count)
user_data_to_clean.shape
# Create new column 'age_on_platform' which has the corresponding value in date type format
user_data_to_clean["age_on_platform"] = user_data_to_clean["last_activity"] - user_data_to_clean["first_login"]
user_data_to_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 43809 entries, 0 to 53238
Data columns (total 10 columns):
user_id            43809 non-null int64
first_name         43806 non-null object
last_name          2385 non-null object
username           43809 non-null object
email              43809 non-null object
phone_number       43801 non-null object
date_joined        43809 non-null datetime64[ns]
first_login        43809 non-null datetime64[ns]
last_activity      43809 non-null datetime64[ns]
age_on_platform    43809 non-null timedelta64[ns]
dtypes: datetime64[ns](3), int64(1), object(5), timedelta64[ns](1)
memory usage: 3.7+ MB<jupyter_text>Validate if email i'd is correctly formatted and the email i'd really exists<jupyter_code>from validate_email import validate_email

email_count_invalid = 0
for index, row in user_data_to_clean.iterrows():        
        if not validate_email(row.email): # , verify=True)  for checking if email i'd actually exits
            user_data_to_clean.drop(index, inplace=True)
            email_count_invalid = email_count_invalid + 1
            
print "Number of email-id invalid: %d" % (email_count_invalid)

# Check the result of last operation 
user_data_to_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 43760 entries, 0 to 53238
Data columns (total 10 columns):
user_id            43760 non-null int64
first_name         43757 non-null object
last_name          2381 non-null object
username           43760 non-null object
email              43760 non-null object
phone_number       43752 non-null object
date_joined        43760 non-null datetime64[ns]
first_login        43760 non-null datetime64[ns]
last_activity      43760 non-null datetime64[ns]
age_on_platform    43760 non-null timedelta64[ns]
dtypes: datetime64[ns](3), int64(1), object(5), timedelta64[ns](1)
memory usage: 3.7+ MB<jupyter_text>Remove duplicates<jupyter_code>user_data_to_deDuplicate = user_data_to_clean.copy()
user_data_deDuplicateD = user_data_to_deDuplicate.loc[~user_data_to_deDuplicate.email.str.strip().duplicated()]
len(user_data_deDuplicateD)
user_data_deDuplicateD.info()
# Now its time to convert the timedelta64 data type column named age_on_platform to seconds
def convert_timedelta64_to_sec(td64):
    ts = (td64 / np.timedelta64(1, 's'))
    return ts

user_data_deDuplicateD_timedelta64_converted = user_data_deDuplicateD.copy()
temp_copy = user_data_deDuplicateD.copy()
user_data_deDuplicateD_timedelta64_converted.drop("age_on_platform", 1)
user_data_deDuplicateD_timedelta64_converted['age_on_platform'] = temp_copy['age_on_platform'].apply(convert_timedelta64_to_sec)

user_data_deDuplicateD_timedelta64_converted.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 40495 entries, 0 to 53238
Data columns (total 10 columns):
user_id            40495 non-null int64
first_name         40492 non-null object
last_name          2111 non-null object
username           40495 non-null object
email              40495 non-null object
phone_number       40487 non-null object
date_joined        40495 non-null datetime64[ns]
first_login        40495 non-null datetime64[ns]
last_activity      40495 non-null datetime64[ns]
age_on_platform    40495 non-null float64
dtypes: datetime64[ns](3), float64(1), int64(1), object(5)
memory usage: 3.4+ MB<jupyter_text>Clustering using Mean shiftfrom sklearn.cluster import MeanShift, estimate_bandwidthx = [1,1,5,6,1,5,10,22,23,23,50,51,51,52,100,112,130,500,512,600,12000,12230]x = pd.Series(user_data_deDuplicateD_timedelta64_converted['age_on_platform'])X = np.array(zip(x,np.zeros(len(x))), dtype=np.int)'''--bandwidth = estimate_bandwidth(X, quantile=0.2)ms = MeanShift(bandwidth=bandwidth, bin_seeding=True)ms.fit(X)labels = ms.labels_cluster_centers = ms.cluster_centers_labels_unique = np.unique(labels)n_clusters_ = len(labels_unique)for k in range(n_clusters_):    my_members = labels == k    print "cluster {0} : lenght = {1}".format(k, len(X[my_members, 0]))    print "cluster {0}: {1}".format(k, X[my_members, 0])    cluster_sorted = sorted(X[my_members, 0])    print "cluster {0} : Max = {2} days & Min {1} days".format(k, cluster_sorted[0]*1.15741e-5, cluster_sorted[-1]*1.15741e-5)''' The following bandwidth can be automatically detected usingbandwidth = estimate_bandwidth(X, quantile=0.7)ms = MeanShift(bandwidth=bandwidth, bin_seeding=True)ms.fit(X)labels = ms.labels_cluster_centers = ms.cluster_centers_labels_unique = np.unique(labels)n_clusters_ = len(labels_unique)print("number of estimated clusters : %d" % n_clusters_)for k in range(n_clusters_):    my_members = labels == k    print "cluster {0} : lenght = {1}".format(k, len(X[my_members, 0]))    cluster_sorted = sorted(X[my_members, 0])    print "cluster {0} : Min = {1} days & Max {2} days".format(k, cluster_sorted[0]*1.15741e-5, cluster_sorted[-1]*1.15741e-5) Plot resultimport matplotlib.pyplot as pltfrom itertools import cycle%matplotlib inlineplt.figure(1)plt.clf()colors = cycle('bgrcmykbgrcmykbgrcmykbgrcmyk')for k, col in zip(range(n_clusters_), colors):    my_members = labels == k    cluster_center = cluster_centers[k]    plt.plot(X[my_members, 0], X[my_members, 1], col + '.')    plt.plot(cluster_center[0], cluster_center[1], 'o', markerfacecolor=col,             markeredgecolor='k', markersize=14)plt.title('Estimated number of clusters: %d' % n_clusters_)plt.show()<jupyter_code># Clustering using Kmeans, not working
'''
y = [1,1,5,6,1,5,10,22,23,23,50,51,51,52,100,112,130,500,512,600,12000,12230]
y_float = map(float, y)
x = range(len(y))
x_float = map(float, x)

m = np.matrix([x_float, y_float]).transpose()


from scipy.cluster.vq import kmeans
kclust = kmeans(m, 5)

kclust[0][:, 0]

assigned_clusters = [abs(cluster_indices - e).argmin() for e in x]
'''<jupyter_output><empty_output><jupyter_text>Binning based on **age_on_platform** day 1; day 2; week 1; week 2; week 3; week 4; week 6; week 8; week 12; 3 months; 6 months; 1 year;<jupyter_code>user_data_binned = user_data_deDuplicateD_timedelta64_converted.copy()
                   
# function to convert age_on_platform in seconds to hours
convert_sec_to_hr = lambda x: x/3600
user_data_binned["age_on_platform"] = user_data_binned['age_on_platform'].map(convert_sec_to_hr).copy()

# filter rows based on first_login value after 30th April
user_data_binned_post30thApril = user_data_binned[user_data_binned.first_login < datetime.datetime(2016, 4, 30)]

for index, row in user_data_binned_post30thApril.iterrows():
    if row["age_on_platform"] < 25:
        user_data_binned_post30thApril.set_value(index, 'bin', 1)
        
    elif row["age_on_platform"] >= 25 and row["age_on_platform"] < 49:
        user_data_binned_post30thApril.set_value(index, 'bin', 2)    
        
    elif row["age_on_platform"] >= 49 and row["age_on_platform"] < 169: #168 hrs = 1 week
        user_data_binned_post30thApril.set_value(index, 'bin', 3)
        
    elif row["age_on_platform"] >=169 and row["age_on_platform"] < 337: # 336 hrs = 2 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 4)
        
    elif row["age_on_platform"] >=337 and row["age_on_platform"] < 505: # 504 hrs = 3 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 5)
        
    elif row["age_on_platform"] >=505 and row["age_on_platform"] < 673: # 672 hrs = 4 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 6)
        
    elif row["age_on_platform"] >=673 and row["age_on_platform"] < 1009: # 1008 hrs = 6 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 7)
        
    elif row["age_on_platform"] >=1009 and row["age_on_platform"] < 1345: # 1344 hrs = 8 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 8)
        
    elif row["age_on_platform"] >=1345 and row["age_on_platform"] < 2017: # 2016 hrs = 12 weeks
        user_data_binned_post30thApril.set_value(index, 'bin', 9)
        
    elif row["age_on_platform"] >=2017 and row["age_on_platform"] < 4381: # 4380 hrs = 6 months
        user_data_binned_post30thApril.set_value(index, 'bin', 10)
        
    elif row["age_on_platform"] >=4381 and row["age_on_platform"] < 8761: # 8760 hrs = 12 months
        user_data_binned_post30thApril.set_value(index, 'bin', 11)
        
    elif row["age_on_platform"] > 8761: # Rest, ie. beyond 1 year
        user_data_binned_post30thApril.set_value(index, 'bin', 12)
        
    else:
        user_data_binned_post30thApril.set_value(index, 'bin', 0)
    
user_data_binned_post30thApril.info()
print "Number of users with age_on_platform equal to 1 day or less, aka 0th day = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 1])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 1].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_0day.csv", index=False)
print "Number of users with age_on_platform between 1st and 2nd days = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 2])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 2].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_1st-day.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 2 complete days and less than 1 week = %d" % \
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 3])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 3].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_1st-week.csv", index=False)
print "Number of users with age_on_platform between 2nd week = %d" % \
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 4])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 4].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_2nd-week.csv", index=False)
print "Number of users with age_on_platform between 3rd weeks = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 5])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 5].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_3rd-week.csv", index=False)
print "Number of users with age_on_platform between 4th weeks = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 6])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 6].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_4th-week.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 4 weeks and less than 6 weeks = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 7])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 7].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_4th-to-6th-week.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 6 weeks and less than 8 weeks = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 8])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 8].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_6th-to-8th-week.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 8 weeks and less than 12 weeks = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 9])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 9].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_8th-to-12th-week.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 12 weeks and less than 6 months = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 10])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 10].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_12thweek-to-6thmonth.csv", index=False)
print "Number of users with age_on_platform greater than or equal to 6 months and less than 1 year = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 11])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 11].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_6thmonth-to-1year.csv", index=False)
print "Number of users with age_on_platform greater than 1 year = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 12])
user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 12].to_csv\
("/home/eyebell/local_bin/janacare/janCC/datasets/user_retention_email-campaign/user_data_binned_post30thApril_beyond-1year.csv", index=False)
print "Number of users with age_on_platform is wierd = %d" %\
len(user_data_binned_post30thApril[user_data_binned_post30thApril.bin == 0])
# Save dataframe with binned values as CSV
#user_data_binned_post30thApril.to_csv('user_data_binned_post30thApril.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09704526972079154, 'fraction_numerical': 0.050365952832746, 'mean_word_length': 5.099867724867725, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 42, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '416225', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 7056, 'n_tokens_neox': 6354, 'n_words': 1442, 'path': 'Janacare_Habits_dataset_upto-7May2016.ipynb', 'repo_name': 'gprakhar/janCC', 'size': '18445'}