# -*- coding: utf-8 -*-
"""preprocessing.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1Y5WhMX0xQagfgZDox84v_jkWowd-b8qD
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import datetime as dt

df = pd.read_csv('uber_peru_2010.csv', delimiter=';')
df.info()

new_df1 = df[['user_id','start_at','journey_id','end_state','price','rider_score',\
              'distance','duration','start_type']]

#calculate unique riders
print('The dataset has {} riders in the original data.'.format(df['user_id'].nunique()))

#filter riders for observation period
new_df1['start_at'] = pd.to_datetime(new_df1['start_at'])
dy = new_df1[new_df1['start_at'] < '2010-10-01']
dz = dy.groupby('user_id', as_index = False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[((new_df1.user_id.isin(dz['user_id'])))]
print('The dataset has {} riders for the observation period'.format(dz['user_id'].nunique()))

#remove identified initial churners
dx = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id', as_index = False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[((new_df1.user_id.isin(dx['user_id'])))]
print('The dataset has {} selected riders for the observation period'.format(dx['user_id'].nunique()))

new_df1.info()

x = new_df1.groupby('end_state')['journey_id'].count()
xpd = pd.DataFrame({'end_state':['driver cancel', 'drop off', 'failure','no show','not found','rider cancel'], 'category':[274, 16853, 235, 18, 1060, 3670]})
ax = xpd.plot.bar(x='end_state', y='category', rot=0)
plt.ylabel('count of trip')
print(x)

n = new_df1[new_df1['end_state']!='drop off']
sns.displot(n['price']).set(title = 'Unsuccessful trips')

m = new_df1[new_df1['end_state'].eq('drop off')]
sns.displot(m['price']).set(title = 'Successful trips')

#DATA PREPROCESSING

#fill missing values of end_state by the most frequency value of not drop off trips
new_df1['end_state'].fillna('rider cancel', inplace = True)

#missing values of price belongs to both successful and unsuccessful trips
# -> only replace missing values of successful trips by median due to skewed distribution
new_df1['price'] = new_df1.groupby('end_state').transform(lambda x: x.fillna(x.median()))
new_df1['price'].fillna(0, inplace = True)

#missing values of distance and duration belongs to unsuccessful trips
# -> fill by 0 value
new_df1['distance'].fillna(0, inplace = True)
new_df1['duration'].fillna(0, inplace = True)

new_df1.info()

#descriptive Statistics for numeric attributes
new_df1.describe()

#descriptive Statistics for nominal attributes
cat = new_df1[['end_state','start_type']]
cat.describe()

#DATA TRANSFORMATION (feature construction)
new_df1['start_at'].min(),new_df1['start_at'].max()
PRESENT = dt.datetime(2011,1,1)

new_df2 = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id')\
                                      .agg({'start_at': lambda date: (PRESENT - date.max()).days,\
                                 'journey_id': lambda num: len(num),
                                 'price': lambda price: price.sum(),
                                 'rider_score': lambda risco: risco.mean(skipna=True),
                                 'distance': lambda distance: distance.sum(),
                                 'duration': lambda duration: duration.sum()})

new_df2.columns
# Change column name
new_df2.columns=['Recency','Frequency','Monetary','avg_rider_score','total_distance','total_duration']

#doi kieu dl cua recency ve int
new_df2['Recency'] = new_df2['Recency'].astype(int)

#computing RFM quantile
new_df2['R_quantile'] = pd.qcut(new_df2['Recency'], 5, ['5','4','3','2','1']).astype(int)
new_df2['F_quantile'] = pd.qcut(new_df2['Frequency'].rank(method='first'), 5, ['1','2','3','4','5']).astype(int)
new_df2['M_quantile'] = pd.qcut(new_df2['Monetary'], 5, ['1','2','3','4','5']).astype(int)
new_df2['RFM_score'] = (new_df2.R_quantile + new_df2.F_quantile + new_df2.M_quantile)/3

#compute avg_rides_per_week to measure riders' activity
date1 = new_df1['start_at'].max()
date2 = new_df1['start_at'].min()
days = abs(date1 - date2).days
weeknum = days//7
new_df2['avg_rides_per_week'] = new_df2['Frequency']/weeknum

#fill missing value of avg_rider_score by median
new_df2['avg_rider_score'].fillna(new_df2['avg_rider_score'].median(), inplace = True)

#compute end_state feature
new_df3 = new_df1.groupby('user_id')['end_state'].apply(lambda x: (x=='rider cancel').sum()).reset_index(name='end_state_RiderCancel')

new_df2 = new_df2.merge(new_df3, how='left', on='user_id')

#compute start_type feature
new_df8 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='asap').sum()).reset_index(name='start_type_asap')
new_df9 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='reserved').sum()).reset_index(name='start_type_reserved')
new_df10 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='delayed').sum()).reset_index(name='start_type_delayed')

new_df2 = new_df2.merge(new_df8, how='left', on='user_id')
new_df2 = new_df2.merge(new_df9, how='left', on='user_id')
new_df2 = new_df2.merge(new_df10, how='left', on='user_id')

#compute features associated with cancellation status in general
new_df11 = new_df1[new_df1['end_state'] != 'drop off'].groupby('user_id')\
                                      .agg({'journey_id': lambda num: len(num)})
new_df11.columns
new_df11.columns=['count_fail_trip']
new_df2 = new_df2.merge(new_df11, how='left', on='user_id')

#compute features associated with charged cancellation trips
col1 = ['user_id','start_at','journey_id','price']

new_df12 = new_df1.loc[(new_df1.price > 0) & (new_df1.end_state != 'drop off')][col1]\
                  .groupby(['user_id'])\
                  .agg({'journey_id': lambda num: len(num),'price': lambda price: price.sum()}).reset_index()

new_df12.columns
new_df12.columns=['user_id','count_charged_fail_trip','total_cancel_fee']
new_df2 = new_df2.merge(new_df12, how='left', on='user_id')

#count frequency of each rider for forecasting period
col2 = ['user_id','journey_id']

new_df13 = new_df1.loc[(new_df1.start_at > '2010-09-30') & (new_df1.end_state == 'drop off')][col2]\
                  .groupby(['user_id']).agg({'journey_id': lambda num: len(num)}).reset_index()

new_df13.columns
new_df13.columns=['user_id','fre_forecast_prd']
new_df2 = new_df2.merge(new_df13, how='left', on='user_id')

new_df2['fre_forecast_prd'].fillna(0, inplace = True)

#create target feature based on forecasting period
def churn_label(row):
  if row['fre_forecast_prd'] == 0:
    label = 1
  else:
    label = 0
  return label

new_df2['status'] = new_df2.apply(churn_label, axis = 1)
new_df2['status'] = new_df2['status'].astype(str)

#handling missing value after features construction
new_df2['count_fail_trip'].fillna(0, inplace = True)
new_df2['count_charged_fail_trip'].fillna(0, inplace = True)
new_df2['total_cancel_fee'].fillna(0, inplace = True)

new_df2.info()
new_df2.head()

# visualize the target variable
g = sns.countplot(new_df2['status'])
g.set_xticklabels(['1','0'])
plt.show()

new_df2['status'].value_counts() #doesn't appear imbalance class

new_df2 = new_df2.iloc[:, np.r_[0:19,20]]
new_df2.info()

#Remove 'user_id' column
new_df2 = new_df2.iloc[: , 1:]
new_df2.info()

new_df2.describe()

sns.boxplot(new_df2.Recency)

#normalize data by z-score
from scipy.stats import zscore
new_df2_nor = new_df2.copy()
numeric_cols = new_df2_nor.select_dtypes(include=[np.number]).columns
new_df2_nor[numeric_cols] = new_df2_nor[numeric_cols].apply(zscore)

new_df2_nor.head()

new_df2.to_csv('new_df2.csv')
new_df2_nor.to_csv('new_df2_nor.csv')
b98aad53823cab39ba4d544655c5340a2d807266
<mask token>


def churn_label(row):
    if row['fre_forecast_prd'] == 0:
        label = 1
    else:
        label = 0
    return label


<mask token>

<mask token>
df.info()
<mask token>
print('The dataset has {} riders in the original data.'.format(df['user_id'
    ].nunique()))
<mask token>
print('The dataset has {} riders for the observation period'.format(dz[
    'user_id'].nunique()))
<mask token>
print('The dataset has {} selected riders for the observation period'.
    format(dx['user_id'].nunique()))
new_df1.info()
<mask token>
plt.ylabel('count of trip')
print(x)
<mask token>
sns.displot(n['price']).set(title='Unsuccessful trips')
<mask token>
sns.displot(m['price']).set(title='Successful trips')
new_df1['end_state'].fillna('rider cancel', inplace=True)
<mask token>
new_df1['price'].fillna(0, inplace=True)
new_df1['distance'].fillna(0, inplace=True)
new_df1['duration'].fillna(0, inplace=True)
new_df1.info()
new_df1.describe()
<mask token>
cat.describe()
new_df1['start_at'].min(), new_df1['start_at'].max()
<mask token>
new_df2.columns
<mask token>
new_df2['avg_rider_score'].fillna(new_df2['avg_rider_score'].median(),
    inplace=True)
<mask token>
new_df11.columns
<mask token>
new_df12.columns
<mask token>
new_df13.columns
<mask token>
new_df2['fre_forecast_prd'].fillna(0, inplace=True)


def churn_label(row):
    if row['fre_forecast_prd'] == 0:
        label = 1
    else:
        label = 0
    return label


<mask token>
new_df2['count_fail_trip'].fillna(0, inplace=True)
new_df2['count_charged_fail_trip'].fillna(0, inplace=True)
new_df2['total_cancel_fee'].fillna(0, inplace=True)
new_df2.info()
new_df2.head()
<mask token>
g.set_xticklabels(['1', '0'])
plt.show()
new_df2['status'].value_counts()
<mask token>
new_df2.info()
<mask token>
new_df2.info()
new_df2.describe()
sns.boxplot(new_df2.Recency)
<mask token>
new_df2_nor.head()
new_df2.to_csv('new_df2.csv')
new_df2_nor.to_csv('new_df2_nor.csv')

<mask token>
df = pd.read_csv('uber_peru_2010.csv', delimiter=';')
df.info()
new_df1 = df[['user_id', 'start_at', 'journey_id', 'end_state', 'price',
    'rider_score', 'distance', 'duration', 'start_type']]
print('The dataset has {} riders in the original data.'.format(df['user_id'
    ].nunique()))
new_df1['start_at'] = pd.to_datetime(new_df1['start_at'])
dy = new_df1[new_df1['start_at'] < '2010-10-01']
dz = dy.groupby('user_id', as_index=False).agg({'journey_id': lambda num:
    len(num)})
new_df1 = new_df1.loc[new_df1.user_id.isin(dz['user_id'])]
print('The dataset has {} riders for the observation period'.format(dz[
    'user_id'].nunique()))
dx = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id',
    as_index=False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[new_df1.user_id.isin(dx['user_id'])]
print('The dataset has {} selected riders for the observation period'.
    format(dx['user_id'].nunique()))
new_df1.info()
x = new_df1.groupby('end_state')['journey_id'].count()
xpd = pd.DataFrame({'end_state': ['driver cancel', 'drop off', 'failure',
    'no show', 'not found', 'rider cancel'], 'category': [274, 16853, 235, 
    18, 1060, 3670]})
ax = xpd.plot.bar(x='end_state', y='category', rot=0)
plt.ylabel('count of trip')
print(x)
n = new_df1[new_df1['end_state'] != 'drop off']
sns.displot(n['price']).set(title='Unsuccessful trips')
m = new_df1[new_df1['end_state'].eq('drop off')]
sns.displot(m['price']).set(title='Successful trips')
new_df1['end_state'].fillna('rider cancel', inplace=True)
new_df1['price'] = new_df1.groupby('end_state').transform(lambda x: x.
    fillna(x.median()))
new_df1['price'].fillna(0, inplace=True)
new_df1['distance'].fillna(0, inplace=True)
new_df1['duration'].fillna(0, inplace=True)
new_df1.info()
new_df1.describe()
cat = new_df1[['end_state', 'start_type']]
cat.describe()
new_df1['start_at'].min(), new_df1['start_at'].max()
PRESENT = dt.datetime(2011, 1, 1)
new_df2 = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id').agg({
    'start_at': lambda date: (PRESENT - date.max()).days, 'journey_id': lambda
    num: len(num), 'price': lambda price: price.sum(), 'rider_score': lambda
    risco: risco.mean(skipna=True), 'distance': lambda distance: distance.
    sum(), 'duration': lambda duration: duration.sum()})
new_df2.columns
new_df2.columns = ['Recency', 'Frequency', 'Monetary', 'avg_rider_score',
    'total_distance', 'total_duration']
new_df2['Recency'] = new_df2['Recency'].astype(int)
new_df2['R_quantile'] = pd.qcut(new_df2['Recency'], 5, ['5', '4', '3', '2',
    '1']).astype(int)
new_df2['F_quantile'] = pd.qcut(new_df2['Frequency'].rank(method='first'), 
    5, ['1', '2', '3', '4', '5']).astype(int)
new_df2['M_quantile'] = pd.qcut(new_df2['Monetary'], 5, ['1', '2', '3', '4',
    '5']).astype(int)
new_df2['RFM_score'] = (new_df2.R_quantile + new_df2.F_quantile + new_df2.
    M_quantile) / 3
date1 = new_df1['start_at'].max()
date2 = new_df1['start_at'].min()
days = abs(date1 - date2).days
weeknum = days // 7
new_df2['avg_rides_per_week'] = new_df2['Frequency'] / weeknum
new_df2['avg_rider_score'].fillna(new_df2['avg_rider_score'].median(),
    inplace=True)
new_df3 = new_df1.groupby('user_id')['end_state'].apply(lambda x: (x ==
    'rider cancel').sum()).reset_index(name='end_state_RiderCancel')
new_df2 = new_df2.merge(new_df3, how='left', on='user_id')
new_df8 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'asap').sum()).reset_index(name='start_type_asap')
new_df9 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'reserved').sum()).reset_index(name='start_type_reserved')
new_df10 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'delayed').sum()).reset_index(name='start_type_delayed')
new_df2 = new_df2.merge(new_df8, how='left', on='user_id')
new_df2 = new_df2.merge(new_df9, how='left', on='user_id')
new_df2 = new_df2.merge(new_df10, how='left', on='user_id')
new_df11 = new_df1[new_df1['end_state'] != 'drop off'].groupby('user_id').agg({
    'journey_id': lambda num: len(num)})
new_df11.columns
new_df11.columns = ['count_fail_trip']
new_df2 = new_df2.merge(new_df11, how='left', on='user_id')
col1 = ['user_id', 'start_at', 'journey_id', 'price']
new_df12 = new_df1.loc[(new_df1.price > 0) & (new_df1.end_state != 'drop off')
    ][col1].groupby(['user_id']).agg({'journey_id': lambda num: len(num),
    'price': lambda price: price.sum()}).reset_index()
new_df12.columns
new_df12.columns = ['user_id', 'count_charged_fail_trip', 'total_cancel_fee']
new_df2 = new_df2.merge(new_df12, how='left', on='user_id')
col2 = ['user_id', 'journey_id']
new_df13 = new_df1.loc[(new_df1.start_at > '2010-09-30') & (new_df1.
    end_state == 'drop off')][col2].groupby(['user_id']).agg({'journey_id':
    lambda num: len(num)}).reset_index()
new_df13.columns
new_df13.columns = ['user_id', 'fre_forecast_prd']
new_df2 = new_df2.merge(new_df13, how='left', on='user_id')
new_df2['fre_forecast_prd'].fillna(0, inplace=True)


def churn_label(row):
    if row['fre_forecast_prd'] == 0:
        label = 1
    else:
        label = 0
    return label


new_df2['status'] = new_df2.apply(churn_label, axis=1)
new_df2['status'] = new_df2['status'].astype(str)
new_df2['count_fail_trip'].fillna(0, inplace=True)
new_df2['count_charged_fail_trip'].fillna(0, inplace=True)
new_df2['total_cancel_fee'].fillna(0, inplace=True)
new_df2.info()
new_df2.head()
g = sns.countplot(new_df2['status'])
g.set_xticklabels(['1', '0'])
plt.show()
new_df2['status'].value_counts()
new_df2 = new_df2.iloc[:, np.r_[0:19, 20]]
new_df2.info()
new_df2 = new_df2.iloc[:, 1:]
new_df2.info()
new_df2.describe()
sns.boxplot(new_df2.Recency)
<mask token>
new_df2_nor = new_df2.copy()
numeric_cols = new_df2_nor.select_dtypes(include=[np.number]).columns
new_df2_nor[numeric_cols] = new_df2_nor[numeric_cols].apply(zscore)
new_df2_nor.head()
new_df2.to_csv('new_df2.csv')
new_df2_nor.to_csv('new_df2_nor.csv')

<mask token>
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import datetime as dt
df = pd.read_csv('uber_peru_2010.csv', delimiter=';')
df.info()
new_df1 = df[['user_id', 'start_at', 'journey_id', 'end_state', 'price',
    'rider_score', 'distance', 'duration', 'start_type']]
print('The dataset has {} riders in the original data.'.format(df['user_id'
    ].nunique()))
new_df1['start_at'] = pd.to_datetime(new_df1['start_at'])
dy = new_df1[new_df1['start_at'] < '2010-10-01']
dz = dy.groupby('user_id', as_index=False).agg({'journey_id': lambda num:
    len(num)})
new_df1 = new_df1.loc[new_df1.user_id.isin(dz['user_id'])]
print('The dataset has {} riders for the observation period'.format(dz[
    'user_id'].nunique()))
dx = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id',
    as_index=False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[new_df1.user_id.isin(dx['user_id'])]
print('The dataset has {} selected riders for the observation period'.
    format(dx['user_id'].nunique()))
new_df1.info()
x = new_df1.groupby('end_state')['journey_id'].count()
xpd = pd.DataFrame({'end_state': ['driver cancel', 'drop off', 'failure',
    'no show', 'not found', 'rider cancel'], 'category': [274, 16853, 235, 
    18, 1060, 3670]})
ax = xpd.plot.bar(x='end_state', y='category', rot=0)
plt.ylabel('count of trip')
print(x)
n = new_df1[new_df1['end_state'] != 'drop off']
sns.displot(n['price']).set(title='Unsuccessful trips')
m = new_df1[new_df1['end_state'].eq('drop off')]
sns.displot(m['price']).set(title='Successful trips')
new_df1['end_state'].fillna('rider cancel', inplace=True)
new_df1['price'] = new_df1.groupby('end_state').transform(lambda x: x.
    fillna(x.median()))
new_df1['price'].fillna(0, inplace=True)
new_df1['distance'].fillna(0, inplace=True)
new_df1['duration'].fillna(0, inplace=True)
new_df1.info()
new_df1.describe()
cat = new_df1[['end_state', 'start_type']]
cat.describe()
new_df1['start_at'].min(), new_df1['start_at'].max()
PRESENT = dt.datetime(2011, 1, 1)
new_df2 = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id').agg({
    'start_at': lambda date: (PRESENT - date.max()).days, 'journey_id': lambda
    num: len(num), 'price': lambda price: price.sum(), 'rider_score': lambda
    risco: risco.mean(skipna=True), 'distance': lambda distance: distance.
    sum(), 'duration': lambda duration: duration.sum()})
new_df2.columns
new_df2.columns = ['Recency', 'Frequency', 'Monetary', 'avg_rider_score',
    'total_distance', 'total_duration']
new_df2['Recency'] = new_df2['Recency'].astype(int)
new_df2['R_quantile'] = pd.qcut(new_df2['Recency'], 5, ['5', '4', '3', '2',
    '1']).astype(int)
new_df2['F_quantile'] = pd.qcut(new_df2['Frequency'].rank(method='first'), 
    5, ['1', '2', '3', '4', '5']).astype(int)
new_df2['M_quantile'] = pd.qcut(new_df2['Monetary'], 5, ['1', '2', '3', '4',
    '5']).astype(int)
new_df2['RFM_score'] = (new_df2.R_quantile + new_df2.F_quantile + new_df2.
    M_quantile) / 3
date1 = new_df1['start_at'].max()
date2 = new_df1['start_at'].min()
days = abs(date1 - date2).days
weeknum = days // 7
new_df2['avg_rides_per_week'] = new_df2['Frequency'] / weeknum
new_df2['avg_rider_score'].fillna(new_df2['avg_rider_score'].median(),
    inplace=True)
new_df3 = new_df1.groupby('user_id')['end_state'].apply(lambda x: (x ==
    'rider cancel').sum()).reset_index(name='end_state_RiderCancel')
new_df2 = new_df2.merge(new_df3, how='left', on='user_id')
new_df8 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'asap').sum()).reset_index(name='start_type_asap')
new_df9 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'reserved').sum()).reset_index(name='start_type_reserved')
new_df10 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x ==
    'delayed').sum()).reset_index(name='start_type_delayed')
new_df2 = new_df2.merge(new_df8, how='left', on='user_id')
new_df2 = new_df2.merge(new_df9, how='left', on='user_id')
new_df2 = new_df2.merge(new_df10, how='left', on='user_id')
new_df11 = new_df1[new_df1['end_state'] != 'drop off'].groupby('user_id').agg({
    'journey_id': lambda num: len(num)})
new_df11.columns
new_df11.columns = ['count_fail_trip']
new_df2 = new_df2.merge(new_df11, how='left', on='user_id')
col1 = ['user_id', 'start_at', 'journey_id', 'price']
new_df12 = new_df1.loc[(new_df1.price > 0) & (new_df1.end_state != 'drop off')
    ][col1].groupby(['user_id']).agg({'journey_id': lambda num: len(num),
    'price': lambda price: price.sum()}).reset_index()
new_df12.columns
new_df12.columns = ['user_id', 'count_charged_fail_trip', 'total_cancel_fee']
new_df2 = new_df2.merge(new_df12, how='left', on='user_id')
col2 = ['user_id', 'journey_id']
new_df13 = new_df1.loc[(new_df1.start_at > '2010-09-30') & (new_df1.
    end_state == 'drop off')][col2].groupby(['user_id']).agg({'journey_id':
    lambda num: len(num)}).reset_index()
new_df13.columns
new_df13.columns = ['user_id', 'fre_forecast_prd']
new_df2 = new_df2.merge(new_df13, how='left', on='user_id')
new_df2['fre_forecast_prd'].fillna(0, inplace=True)


def churn_label(row):
    if row['fre_forecast_prd'] == 0:
        label = 1
    else:
        label = 0
    return label


new_df2['status'] = new_df2.apply(churn_label, axis=1)
new_df2['status'] = new_df2['status'].astype(str)
new_df2['count_fail_trip'].fillna(0, inplace=True)
new_df2['count_charged_fail_trip'].fillna(0, inplace=True)
new_df2['total_cancel_fee'].fillna(0, inplace=True)
new_df2.info()
new_df2.head()
g = sns.countplot(new_df2['status'])
g.set_xticklabels(['1', '0'])
plt.show()
new_df2['status'].value_counts()
new_df2 = new_df2.iloc[:, np.r_[0:19, 20]]
new_df2.info()
new_df2 = new_df2.iloc[:, 1:]
new_df2.info()
new_df2.describe()
sns.boxplot(new_df2.Recency)
from scipy.stats import zscore
new_df2_nor = new_df2.copy()
numeric_cols = new_df2_nor.select_dtypes(include=[np.number]).columns
new_df2_nor[numeric_cols] = new_df2_nor[numeric_cols].apply(zscore)
new_df2_nor.head()
new_df2.to_csv('new_df2.csv')
new_df2_nor.to_csv('new_df2_nor.csv')

# -*- coding: utf-8 -*-
"""preprocessing.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1Y5WhMX0xQagfgZDox84v_jkWowd-b8qD
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import datetime as dt

df = pd.read_csv('uber_peru_2010.csv', delimiter=';')
df.info()

new_df1 = df[['user_id','start_at','journey_id','end_state','price','rider_score',\
              'distance','duration','start_type']]

#calculate unique riders
print('The dataset has {} riders in the original data.'.format(df['user_id'].nunique()))

#filter riders for observation period
new_df1['start_at'] = pd.to_datetime(new_df1['start_at'])
dy = new_df1[new_df1['start_at'] < '2010-10-01']
dz = dy.groupby('user_id', as_index = False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[((new_df1.user_id.isin(dz['user_id'])))]
print('The dataset has {} riders for the observation period'.format(dz['user_id'].nunique()))

#remove identified initial churners
dx = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id', as_index = False).agg({'journey_id': lambda num: len(num)})
new_df1 = new_df1.loc[((new_df1.user_id.isin(dx['user_id'])))]
print('The dataset has {} selected riders for the observation period'.format(dx['user_id'].nunique()))

new_df1.info()

x = new_df1.groupby('end_state')['journey_id'].count()
xpd = pd.DataFrame({'end_state':['driver cancel', 'drop off', 'failure','no show','not found','rider cancel'], 'category':[274, 16853, 235, 18, 1060, 3670]})
ax = xpd.plot.bar(x='end_state', y='category', rot=0)
plt.ylabel('count of trip')
print(x)

n = new_df1[new_df1['end_state']!='drop off']
sns.displot(n['price']).set(title = 'Unsuccessful trips')

m = new_df1[new_df1['end_state'].eq('drop off')]
sns.displot(m['price']).set(title = 'Successful trips')

#DATA PREPROCESSING

#fill missing values of end_state by the most frequency value of not drop off trips
new_df1['end_state'].fillna('rider cancel', inplace = True)

#missing values of price belongs to both successful and unsuccessful trips
# -> only replace missing values of successful trips by median due to skewed distribution
new_df1['price'] = new_df1.groupby('end_state').transform(lambda x: x.fillna(x.median()))
new_df1['price'].fillna(0, inplace = True)

#missing values of distance and duration belongs to unsuccessful trips
# -> fill by 0 value
new_df1['distance'].fillna(0, inplace = True)
new_df1['duration'].fillna(0, inplace = True)

new_df1.info()

#descriptive Statistics for numeric attributes
new_df1.describe()

#descriptive Statistics for nominal attributes
cat = new_df1[['end_state','start_type']]
cat.describe()

#DATA TRANSFORMATION (feature construction)
new_df1['start_at'].min(),new_df1['start_at'].max()
PRESENT = dt.datetime(2011,1,1)

new_df2 = new_df1[new_df1['end_state'].eq('drop off')].groupby('user_id')\
                                      .agg({'start_at': lambda date: (PRESENT - date.max()).days,\
                                 'journey_id': lambda num: len(num),
                                 'price': lambda price: price.sum(),
                                 'rider_score': lambda risco: risco.mean(skipna=True),
                                 'distance': lambda distance: distance.sum(),
                                 'duration': lambda duration: duration.sum()})

new_df2.columns
# Change column name
new_df2.columns=['Recency','Frequency','Monetary','avg_rider_score','total_distance','total_duration']

#doi kieu dl cua recency ve int
new_df2['Recency'] = new_df2['Recency'].astype(int)

#computing RFM quantile
new_df2['R_quantile'] = pd.qcut(new_df2['Recency'], 5, ['5','4','3','2','1']).astype(int)
new_df2['F_quantile'] = pd.qcut(new_df2['Frequency'].rank(method='first'), 5, ['1','2','3','4','5']).astype(int)
new_df2['M_quantile'] = pd.qcut(new_df2['Monetary'], 5, ['1','2','3','4','5']).astype(int)
new_df2['RFM_score'] = (new_df2.R_quantile + new_df2.F_quantile + new_df2.M_quantile)/3

#compute avg_rides_per_week to measure riders' activity
date1 = new_df1['start_at'].max()
date2 = new_df1['start_at'].min()
days = abs(date1 - date2).days
weeknum = days//7
new_df2['avg_rides_per_week'] = new_df2['Frequency']/weeknum

#fill missing value of avg_rider_score by median
new_df2['avg_rider_score'].fillna(new_df2['avg_rider_score'].median(), inplace = True)

#compute end_state feature
new_df3 = new_df1.groupby('user_id')['end_state'].apply(lambda x: (x=='rider cancel').sum()).reset_index(name='end_state_RiderCancel')

new_df2 = new_df2.merge(new_df3, how='left', on='user_id')

#compute start_type feature
new_df8 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='asap').sum()).reset_index(name='start_type_asap')
new_df9 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='reserved').sum()).reset_index(name='start_type_reserved')
new_df10 = new_df1.groupby('user_id')['start_type'].apply(lambda x: (x=='delayed').sum()).reset_index(name='start_type_delayed')

new_df2 = new_df2.merge(new_df8, how='left', on='user_id')
new_df2 = new_df2.merge(new_df9, how='left', on='user_id')
new_df2 = new_df2.merge(new_df10, how='left', on='user_id')

#compute features associated with cancellation status in general
new_df11 = new_df1[new_df1['end_state'] != 'drop off'].groupby('user_id')\
                                      .agg({'journey_id': lambda num: len(num)})
new_df11.columns
new_df11.columns=['count_fail_trip']
new_df2 = new_df2.merge(new_df11, how='left', on='user_id')

#compute features associated with charged cancellation trips
col1 = ['user_id','start_at','journey_id','price']

new_df12 = new_df1.loc[(new_df1.price > 0) & (new_df1.end_state != 'drop off')][col1]\
                  .groupby(['user_id'])\
                  .agg({'journey_id': lambda num: len(num),'price': lambda price: price.sum()}).reset_index()

new_df12.columns
new_df12.columns=['user_id','count_charged_fail_trip','total_cancel_fee']
new_df2 = new_df2.merge(new_df12, how='left', on='user_id')

#count frequency of each rider for forecasting period
col2 = ['user_id','journey_id']

new_df13 = new_df1.loc[(new_df1.start_at > '2010-09-30') & (new_df1.end_state == 'drop off')][col2]\
                  .groupby(['user_id']).agg({'journey_id': lambda num: len(num)}).reset_index()

new_df13.columns
new_df13.columns=['user_id','fre_forecast_prd']
new_df2 = new_df2.merge(new_df13, how='left', on='user_id')

new_df2['fre_forecast_prd'].fillna(0, inplace = True)

#create target feature based on forecasting period
def churn_label(row):
  if row['fre_forecast_prd'] == 0:
    label = 1
  else:
    label = 0
  return label

new_df2['status'] = new_df2.apply(churn_label, axis = 1)
new_df2['status'] = new_df2['status'].astype(str)

#handling missing value after features construction
new_df2['count_fail_trip'].fillna(0, inplace = True)
new_df2['count_charged_fail_trip'].fillna(0, inplace = True)
new_df2['total_cancel_fee'].fillna(0, inplace = True)

new_df2.info()
new_df2.head()

# visualize the target variable
g = sns.countplot(new_df2['status'])
g.set_xticklabels(['1','0'])
plt.show()

new_df2['status'].value_counts() #doesn't appear imbalance class

new_df2 = new_df2.iloc[:, np.r_[0:19,20]]
new_df2.info()

#Remove 'user_id' column
new_df2 = new_df2.iloc[: , 1:]
new_df2.info()

new_df2.describe()

sns.boxplot(new_df2.Recency)

#normalize data by z-score
from scipy.stats import zscore
new_df2_nor = new_df2.copy()
numeric_cols = new_df2_nor.select_dtypes(include=[np.number]).columns
new_df2_nor[numeric_cols] = new_df2_nor[numeric_cols].apply(zscore)

new_df2_nor.head()

new_df2.to_csv('new_df2.csv')
new_df2_nor.to_csv('new_df2_nor.csv')