case_study_movie_ticket.py

# -*- coding: utf-8 -*-
"""Case study_Movie ticket

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1M5WsDJU8k04xQOH3FlGqFAI0BJbT7qXB

# 1. Load data
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df_campaign = pd.read_csv('/content/drive/MyDrive/Dataset/Movie 2/campaign.csv')
df_customer = pd.read_csv('/content/drive/MyDrive/Dataset/Movie 2/customer.csv')
df_device = pd.read_csv('/content/drive/MyDrive/Dataset/Movie 2/device_detail.csv')
df_status = pd.read_csv('/content/drive/MyDrive/Dataset/Movie 2/status_detail.csv')
df_ticket = pd.read_csv('/content/drive/MyDrive/Dataset/Movie 2/ticket_history.csv')

"""#2. Data cleaning

## 2.1 Data type, NULL values, Duplicate values
"""

df_customer.info()

#Phan tich: non-null count deu bang voi so dong cua bang => ok
#Customer_id la dang int => ok
#usergender la dang object (dang text) => ok
#dob dang object => chua chinh xac vi day la ngay sinh thi phai la dang datetime

#Chuyen doi type cua dob
from datetime import datetime #buoc dau tien la import thu vien datetime
df_customer['dob'] = pd.to_datetime(df_customer['dob']) #chuyen doi type cua dob

df_customer.info()

#kiem tra duplicate values
df_customer['customer_id'].nunique()

# =>con so nay trung voi so dong cua bang customer => ok

df_campaign.info()

df_campaign.head(2)

df_campaign['campaign_id'].nunique()

df_device.info()

#model co so luong non-null it hon so dong cua bang => kiem tra xem so luong null chiem bao nhieu % => define ra mot function moi

def calc_null_rate(df):
    newdf = df.isnull().sum().to_frame('null_count')
    newdf[['null_rate']] = newdf[['null_count']] / len(df)
    return newdf.sort_values(by=['null_rate'], ascending=False)

calc_null_rate(df_device)

#ti le null cua model chiem 5% => ko qua lon nhung chung ta nen thay the bang gia tri unknown
#null cua device chi co 1 thi ta se xoa luon

df_device = df_device.fillna({'model' : 'unknown'})
df_device = df_device[df_device['device_number'].notna()]

calc_null_rate(df_device)

df_status.info()

df_status.head()

#co mot NULL tuy nhien vi day la null cua gia tri successful nen hop ly => ko can lam gi

df_ticket.info()

#cot time o dang text => thay doi thanh dang datatime
df_ticket['time']= pd.to_datetime(df_ticket['time'])

df_ticket.info()

#kiem tra duplicate => dem primary cua bang
df_ticket['ticket_id'].nunique()

#Co nhieu dong bi duplicate => ko xoa voi ma phai kiem tra xem chung co bi trung that khong
df_duplicate = df_ticket[df_ticket.duplicated(keep= False)]

df_duplicate.head()

#cac dong bi duplicate giong het nhau ve moi du lieu => co the xoa chung di duoc roi
df_ticket.drop_duplicates(inplace=True)

df_ticket['ticket_id'].nunique()

df_ticket.info()

#Ket qua cac dong da bang nhau => Ok

#Kiem tra buoc dau da xong, bh se kiem tra buoc 2
#tuy nhien chung ta co the join tat ca cac bang de kiem tra 1 lan cho nhanh

"""## 2.2 Join tables"""

df_join_customer = pd.merge(df_ticket, df_customer, how ='left', on = 'customer_id')

df_join_campaign = pd.merge(df_join_customer, df_campaign, how ='left', on = 'campaign_id')

df_join_status = pd.merge(df_join_campaign, df_status, how ='left', on = 'status_id')

df_join_all = pd.merge(df_join_status, df_device, how ='left', on = 'device_number')

#Kiem tra xem so dong co bi thay doi ko
df_join_all.count()

calc_null_rate(df_join_all)

#cot error group bi null khi don hang giao dich thanh cong
#campaign type bi null khi don hang ko co khuyen mai
#=> thay the cac gia tri null bang unknown

df_join_all = df_join_all.fillna('unknown')

calc_null_rate(df_join_all)

"""## 2.3 View all values of each columns"""

df_join_all.nunique().sort_values(ascending =False)

#Tao mot function de liet ke tat ca cac gia tri trong 1 column

specific_cols = ['movie_name','description','paying_method','campaign_type','usergender','platform', 'error_group']
for col in specific_cols:
    print(col + ' : ', np.sort(df_join_all[col].unique().astype(str)))
    print('\r')
    print('--------------------------')
    print('\r')

#Check loi chinh ta, loi duplicate => ok ko sai gi nen ko can chinh sua nua

"""#  3. Analyze

## 3.1 Customer portrait analysis

### 3.1.1 Age and gender distribution
"""

df_join_all.head(2)

#Tinh so tuoi
current_year = datetime.now()

df_join_all['age_days'] = (current_year - df_join_all['dob']).dt.days

df_join_all.head(2)

df_join_all['age'] = df_join_all['age_days'] / 365.25
df_join_all['age'] = df_join_all['age'].astype(int)
df_join_all.head(2)

#Lay ra danh sach KH kem theo do tuoi va gioi tinh
df_cus = df_join_all.drop_duplicates(subset=['customer_id']) [['customer_id', 'dob', 'age', 'usergender']]
df_cus.count()

#so sanh voi count danh sach unique o tren kia thi da bang nhau ve so luong customer_id => ok

#Kiem tra phan bo => Histogram

plt.figure(figsize=(8,4))
df_cus['age'].hist(bins = 30, color = 'cornflowerblue', grid = False)
plt.xlabel('ages')
plt.ylabel('#customers')
plt.title('Age distribution')
plt.show()

#co nguoi tren 100 tuoi => outlier
#co nguoi tren 80 di xem phim va dat ve => co binh thuong ko
#bins o do tuoi 5x cao bat thuong

# Phân bổ độ tuổi theo nhóm giới tính :
plt.figure(figsize=(8,4))

## data
male_age = df_cus[df_cus['usergender'] == 'Male']['age']
female_age = df_cus[df_cus['usergender'] == 'Female']['age']
unknown_age = df_cus[df_cus['usergender'] == 'Not verify']['age']

## plot
plt.hist(male_age, bins=30, alpha = 0.3, color = 'cornflowerblue', label = 'Male')
plt.hist(female_age, bins=30, alpha = 0.3, color = 'salmon', label = 'Female')
plt.hist(unknown_age, bins=30, alpha = 0.3, color = 'limegreen', label = 'Not verify')
#alpha la thong so phan tram cua mau sac hien thi

## edit
plt.title('Age distribution')
plt.xlabel('ages')
plt.ylabel('#customers')
plt.legend()
plt.show()

#nhin hai bang ta thay => nhom do tuoi 5x o bang tuoi la nhom not verify gioi tinh
#Can kiem tra xem doan mau xanh not verify nay nhu the nao

#Danh gia chi tiet nhom verify

df_gen = df_cus.groupby('usergender').agg(total_cus = ('customer_id', 'count')).sort_values(by = 'total_cus', ascending = False).reset_index()

df_gen

#Bieu do tron

plt.pie(df_gen['total_cus'], labels = df_gen['usergender'],
        colors = ['cornflowerblue', 'lightsteelblue', 'slategrey'],
        autopct = '%1.0f%%',
        startangle = 90)
plt.show()

""">**Notes** :
>- Nhom KH chua verify chiem hon 10% dan den hai truong hop:
>>- Neu ho da nhap dob thi se co data
>>- Neu ho ko nhap thi he thong se autofill la 1970 => 54 tuoi
"""

df_cus[df_cus['usergender'] == 'Not verify'].groupby('age').agg(number = ('customer_id', 'count')).reset_index().sort_values(by='number', ascending=False)

"""### 3.1.2 Age generation distribution

"""

#Logic phan loai X, Y, Z, Baby boomers => dua vao nam sinh
df_cus['age_generation'] = df_cus['dob'].apply(lambda x: 'Baby boomers' if x.year < 1965
                                               else 'Gen X' if x.year < 1981
                                               else 'Gen Y' if x.year < 1997
                                               else 'Gen Z')
df_cus.head(2)

df_gen_group = (
    df_cus[df_cus['usergender'] != 'Not verify']
    .groupby('age_generation')
    .agg(total = ('customer_id', 'count'))
    .sort_values(by='total', ascending = False)
    .reset_index()
)

df_gen_group

#Ket hop hai bieu do cung luc
plt.figure(figsize=(13,4))

#plot 1
ax1 = plt.subplot(1, 2, 1) #Chi 1 dong, co 2 cot, vi tri o thu 1
df_cus[df_cus['usergender'] != 'Not verify']['age'].hist(bins = 30, color = 'cornflowerblue', grid = False)
plt.xlabel('ages')
plt.ylabel('#customers')
plt.title('Age distribution')

#plot 2
ax2 = plt.subplot(1, 2, 2)
plt.pie(df_gen_group['total'], labels = df_gen_group['age_generation'],
        colors = ['cornflowerblue', 'lightsteelblue', 'slategrey', 'lightskyblue'],
        autopct = '%1.0f%%',
        startangle = 90)

plt.show()

"""## 3.2 Time series data - When did customers buy tickets ?

### 3.2.1 Trend by month
"""

df_join_all['month'] = pd.to_datetime(df_join_all['time']).dt.month #lay ra cot thang
df_join_all['name_day'] = pd.to_datetime(df_join_all['time']).dt.day_name() #lay ra cot ngay
df_join_all['hour'] = pd.to_datetime(df_join_all['time']).dt.hour #Lay ra cot gio
df_join_all['year_month'] = df_join_all['time'].dt.strftime('%Y-%m') #Lay ra cot nam va thang

df_join_all.head(2)

#Thong ke theo thang
df_time_month = (
    df_join_all
    .groupby(['year_month'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .reset_index()
)

df_time_month.head()

#Ve bieu do mien theo thang
plt.figure(figsize=(13,4))
plt.fill_between(df_time_month['year_month'], df_time_month['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
plt.xticks(rotation = 90)
plt.show()

#Giai thich : giai doan covid dien ra => ko di xem phim duoc
#Quan sat : bi thieu mot so thang trong nam ----> can 1 bang DIM thoi gian theo thang (FULL)

#Tao bang DIM ve thoi gian de JOIN voi data ticket va ve lai chart

# Tạo bảng dimension thời gian:

# Xác định khoảng thời gian
start_date = '2019-01-01'
end_date = '2022-12-31'

# Tạo ra range thời gian từ 2 mốc start và end
date_range = pd.date_range(start=start_date, end=end_date, freq='MS')

# Lấy ra list phần tử thời gian tương ứng:
list_month = date_range.month
list_month_name = date_range.strftime('%B')
list_year = date_range.year
list_year_month = date_range.strftime('%Y-%m')

# # Khởi tạo dataframe
dim_time = pd.DataFrame({
    'month_number': list_month,
    'month_name': list_month_name,
    'year': list_year,
    'year_month': list_year_month
})

dim_time.head()

#JOIN voi bang join_all de co du data ve thoi gian

df_time_month_dim = (
    pd.merge(dim_time, df_join_all, how='left', on='year_month')
    .groupby(['year_month'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .reset_index()
)

df_time_month_dim.head()

plt.figure(figsize=(13,4))
plt.fill_between(df_time_month_dim['year_month'], df_time_month_dim['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
plt.xticks(rotation = 90)
plt.show()

#Quan sat : mac du da du gia tri date nhung nhung thang ko co du lieu van hien len mau => co the gay hieu lam
# ----> thay gia tri 0 bang gia tri NULL

df_time_month_dim.replace(0, np.nan, inplace=True)

plt.figure(figsize=(13,4))
plt.fill_between(df_time_month_dim['year_month'], df_time_month_dim['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
plt.xticks(rotation = 90)
plt.show()

"""### 3.2.2 Trend by week days"""

#Thong ke theo ngay trong tuan
df_week_day = (
    df_join_all
    .groupby(['name_day'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .reset_index()
)

df_week_day.head()

plt.figure(figsize=(13,4))
plt.fill_between(df_week_day['name_day'], df_week_day['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
plt.xticks(rotation = 90)
plt.show()

#Quan sat => cac thu dang bi lon xon
# ----> can sap xep lai theo thu tu cac ngay trong tuan

#Dinh nghia thu tu cac ngay trong tuan
week_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

#Sap xep theo thu tu cac ngay trong tuan
df_week_day['day_order'] = pd.Categorical(df_week_day['name_day'], categories=week_order, ordered=True)
df_week_day.sort_values('day_order', inplace=True)

df_week_day

plt.figure(figsize=(13,4))
plt.fill_between(df_week_day['name_day'], df_week_day['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
plt.xticks(rotation = 90)
plt.show()

#Giai thich : mn di xem nhieu vao cuoi tuan

"""### 3.2.3 Trend by hours"""

#Thong ke theo gio trong ngay
df_hour = (
    df_join_all
    .groupby(['hour'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .reset_index()
)

df_hour.head()

plt.figure(figsize=(13,4))
plt.fill_between(df_hour['hour'], df_hour['total_ticket'], color = 'cornflowerblue', alpha = 0.7)
x_values = [i for i in range(24)]
plt.xticks(x_values)
plt.show()

# Vẽ chung trên 1 frame :
plt.figure(figsize=(13, 8))

# chart 1: Tháng
ax1 = plt.subplot(2, 1, 1)
plt.fill_between(df_time_month_dim['year_month'], df_time_month_dim['total_ticket'], color = 'cornflowerblue', alpha=0.7)
plt.title('#ticket by months')
plt.xticks(rotation=90)

# chart 2: ngày
ax2 = plt.subplot(2, 2, 3) #hai hang, hai cot, vi tri thu 3
plt.fill_between(df_week_day['name_day'], df_week_day['total_ticket'], color = 'cornflowerblue', alpha=0.7)
plt.title('#ticket by week day')
plt.xticks(rotation=90)

# chart 3: giờ
ax3 = plt.subplot(2, 2, 4)
plt.fill_between(df_hour['hour'], df_hour['total_ticket'], color = 'cornflowerblue', alpha=0.7)
x_values = [i for i in range(24)]
plt.xticks(x_values)
plt.title('#ticket by hour')

plt.subplots_adjust( hspace = 0.5, top=0.8)

"""## 3.3 Factor related to the customer's purchasing process

### 3.3.1 Payment platform
"""

df_platform = (
    df_join_all[df_join_all['platform'] != 'unknown']
    .groupby(['platform'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .reset_index()
)

df_platform

# Biểu đồ cột ngang :
plt.figure(figsize=(8, 4))

plt.barh(
    df_platform['platform'], df_platform['total_ticket'],
    color = df_platform['platform'].replace({ 'mobile': 'lightskyblue',  'website': 'tomato'})
)

for index,value in enumerate(df_platform['total_ticket']):
    plt.text(value,index,str(value))

plt.title('#ticket by platform')

#Bieu do tron :
plt.figure(figsize=(8, 4))
plt.pie(
    df_platform['total_ticket'],
    labels = df_platform['platform'],
    colors = df_platform['platform'].replace({ 'mobile': 'lightskyblue',  'website': 'tomato'}),
    autopct = '%.1f%%',
    startangle=90
)
plt.show()

#Theo thoi gian :
df_platform_time = (
    df_join_all[df_join_all['platform'] != 'unknown']
    .groupby(['year_month', 'platform'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='year_month', ascending=True)
    .reset_index()
)
df_platform_time.head()

#Ve bieu do line cho mobile va website

plt.figure(figsize=(13, 4))

df_mobile_line = df_platform_time[df_platform_time['platform'] == 'mobile']
plt.plot(df_mobile_line['year_month'], df_mobile_line['total_ticket'],
         label = 'mobile', color = 'tomato',
         marker = 0, linewidth = 2, markersize = 4)

df_website_line = df_platform_time[df_platform_time['platform'] == 'website']
plt.plot(df_website_line['year_month'], df_website_line['total_ticket'],
         label = 'website', color = 'lightskyblue',
         marker = 0, linewidth = 2, markersize = 4)

plt.legend()
plt.xticks(rotation = 90)
plt.show()

#Phan tich : theo bieu do tron => payment qua mobile chiem da so
#theo bieu do line => payment qua website moi xuat hien va cung chi chiem mot so it %

"""### 3.3.2 OS version"""

#Phan loai thiet bi OS version thanh cac nhom : android, ios, unknown, browser
df_join_all['os_version'] = df_join_all['model'].apply(
    lambda
    x: 'ios' if ('iPhone' in x or 'iPod' in x)
    else 'browser' if x == 'browser'
    else 'unknown' if ('devicemodel' in x or 'unknown' in x)
    else 'android & other'
)

df_join_all['os_version'].unique()

#Group by de thong ke
df_os = (
    df_join_all
    .groupby(['os_version'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='total_ticket', ascending=True)
    .reset_index()
)

df_os

# Biểu đồ cột ngang :
plt.figure(figsize=(12, 3))

ax1 = plt.subplot(1,2,1)
plt.barh(
    df_os['os_version'], df_os['total_ticket'],
    color = df_os['os_version'].replace({ 'browser': 'lightsteelblue',  'android & other': 'lightskyblue', 'ios': 'cornflowerblue', 'unknown': 'steelblue'})
)

for index,value in enumerate(df_os['total_ticket']):
    plt.text(value,index,str(value))
plt.title('#ticket by os')

ax2 = plt.subplot(1,2,2)
plt.pie(df_os['total_ticket'], labels= df_os['os_version'],
        colors=df_os['os_version'].replace({ 'browser': 'lightsteelblue',  'android & other': 'lightskyblue', 'ios': 'cornflowerblue', 'unknown': 'steelblue'}),
        autopct='%1.0f%%',
        startangle=90)
plt.show()

df_os_time = (
    df_join_all
    .groupby(['year_month', 'os_version'])
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='year_month', ascending=True)
    .reset_index()
)

df_os_time.head(10)

#Ve bieu do mien ---> xu ly data dang PIVOT

df_os_time = (
    df_join_all
    .pivot_table( index= 'year_month', columns ='os_version', aggfunc='count', values='ticket_id')
    .reset_index()
)

df_os_time.head(10)

#Ve bieu do mien chong theo thoi gian
plt.figure(figsize=(13,4))

plt.fill_between(df_os_time['year_month'], df_os_time['ios'], color ='cornflowerblue', alpha = 0.5, label = 'ios')
plt.fill_between(df_os_time['year_month'], df_os_time['android & other'], color ='lightskyblue', alpha = 0.5, label = 'android & other')
plt.fill_between(df_os_time['year_month'], df_os_time['browser'], color ='lightsteelblue', alpha = 0.5, label = 'browser')
plt.fill_between(df_os_time['year_month'], df_os_time['unknown'], color ='steelblue', alpha = 0.5, label = 'unknown')

plt.xticks(rotation =90)
plt.show()

#Cot thoi gian van bi thieu range ve thoi gian => join voi bang thoi gian o tren de co day du thoi gian

#Bieu dien chung mot frame
plt.figure(figsize=(12, 8))

ax1 = plt.subplot(2,2,1)
plt.barh(
    df_os['os_version'], df_os['total_ticket'],
    color = df_os['os_version'].replace({ 'browser': 'lightsteelblue',  'android & other': 'lightskyblue', 'ios': 'cornflowerblue', 'unknown': 'steelblue'})
)

for index,value in enumerate(df_os['total_ticket']):
    plt.text(value,index,str(value))
plt.title('#ticket by os')

ax2 = plt.subplot(2,2,2)
plt.pie(df_os['total_ticket'], labels= df_os['os_version'],
        colors=df_os['os_version'].replace({ 'browser': 'lightsteelblue',  'android & other': 'lightskyblue', 'ios': 'cornflowerblue', 'unknown': 'steelblue'}),
        autopct='%1.0f%%',
        startangle=90)

ax3 = plt.subplot(2,1,2)
plt.fill_between(df_os_time['year_month'], df_os_time['ios'], color ='cornflowerblue', alpha = 0.5, label = 'ios')
plt.fill_between(df_os_time['year_month'], df_os_time['android & other'], color ='lightskyblue', alpha = 0.5, label = 'android & other')
plt.fill_between(df_os_time['year_month'], df_os_time['browser'], color ='lightsteelblue', alpha = 0.5, label = 'browser')
plt.fill_between(df_os_time['year_month'], df_os_time['unknown'], color ='steelblue', alpha = 0.5, label = 'unknown')

plt.xticks(rotation =90)
plt.show()

#Mac du unknown chiem kha nhieu nhung chi moi xuat hien gan day => co the la do thay doi cau truc du lieu nen ko co du lieu ghi nhan
# ---> danh gia mot van de theo nhieu goc nhieu : so luong thuc te, ti trong %, theo thoi gian

"""### 3.3.3 Payment method"""

df_method = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['paying_method'] != 'other') ]
    .groupby('paying_method')
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='total_ticket', ascending=True)
    .reset_index()
)

df_method

#Xu ly data dang Pivot de ve bieu do mien :
df_method_time = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['paying_method'] != 'other') ]
    .pivot_table( index= 'year_month', columns ='paying_method', aggfunc='count', values='ticket_id')
    .reset_index()
)

df_method_time.head(10)

#Ve bieu do mien 100%
df_method_time = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['paying_method'] != 'other') ]
    .pivot_table( index= 'year_month', columns ='paying_method', aggfunc='count', values='ticket_id')
    .reset_index()
)

df_method_time_pct = df_method_time.copy()

df_method_time_pct = df_method_time_pct.fillna(0)
df_method_time_pct['total'] = df_method_time_pct.iloc[:, 1:].sum(axis=1)

for i in df_method_time_pct.columns[1:5]:
  df_method_time_pct[i+'_pct'] = df_method_time_pct[i]/df_method_time_pct['total']

df_method_time_pct.head(10)

# biểu diễn chung 1 frame
plt.figure(figsize=(12, 8))

ax1 = plt.subplot(3,2,1)
plt.barh(
    df_method['paying_method'], df_method['total_ticket'],
    color = df_method['paying_method'].replace({ 'bank account': 'lightsteelblue',  'credit card': 'lightskyblue', 'debit card': 'cornflowerblue', 'money in app': 'steelblue'})
)

for index,value in enumerate(df_method['total_ticket']):
    plt.text(value,index,str(value))
plt.title('#ticket by method')

ax2 = plt.subplot(3,2,2)
plt.pie(df_method['total_ticket'], labels= df_method['paying_method'],
        colors=df_method['paying_method'].replace({ 'bank account': 'lightsteelblue',  'credit card': 'lightskyblue', 'debit card': 'cornflowerblue', 'money in app': 'steelblue'}),
        autopct='%1.0f%%',
        startangle=90)

ax3 = plt.subplot(3,1,2)
plt.fill_between(df_method_time['year_month'], df_method_time['bank account'], color='cornflowerblue', alpha=0.5, label='bank account')
plt.fill_between(df_method_time['year_month'], df_method_time['credit card'], color='lightskyblue', alpha=0.5, label='credit card')
plt.fill_between(df_method_time['year_month'], df_method_time['debit card'], color='lightsteelblue', alpha=0.5, label='debit')
plt.fill_between(df_method_time['year_month'], df_method_time['money in app'], color='steelblue', alpha=0.5, label='money in app')

plt.title('#ticket of method by time')
# plt.xlabel('Month')
plt.ylabel('#ticket')
plt.legend(loc='upper left')
plt.xticks(rotation=90)


ax4 = plt.subplot(3,1,3)
# vẽ biểu đồ miền 100%
plt.stackplot(df_method_time_pct['year_month'], df_method_time_pct["money in app_pct"],  df_method_time_pct['debit card_pct'], df_method_time_pct['credit card_pct'], df_method_time_pct['bank account_pct']
              , labels=['money in app', 'debit card', 'credit card', 'bank account'], colors=['royalblue', 'slategrey', 'lightsteelblue', 'cornflowerblue'], alpha=0.7)

plt.title('#ticket of method by time')
# plt.xlabel('Month')
plt.ylabel('#ticket')
plt.legend(loc='upper right', bbox_to_anchor=(1.15, 1))
plt.xticks(rotation=90)

plt.subplots_adjust(hspace = 0.7, top = 0.9)

#Phan tich : money in app luon chiem ti trong lon, nhat la tu nhung nam gan day

"""### 3.3.4 Promotion"""

df_join_all['campaign_type'].unique()

df_join_all['type']= df_join_all['campaign_type'].apply(
    lambda x: 'non-promotion' if x == 'unknown'
    else 'promotion'
)
df_join_all.head(2)

df_type = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['paying_method'] != 'other') ]
    .groupby('type')
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='total_ticket', ascending=True)
    .reset_index()
)

df_type

#xu ly data dang PIVOT de ve bieu do mien :

df_type_time = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['paying_method'] != 'other') ]
    .pivot_table( index= 'year_month', columns ='type', aggfunc='count', values='ticket_id')
    .reset_index()
)

df_type_time_pct = df_type_time.copy()

df_type_time_pct = df_type_time_pct.fillna(0)
df_type_time_pct['total'] = df_type_time_pct.iloc[:, 1:].sum(axis=1)

for i in df_type_time_pct.columns[1:3]:
  df_type_time_pct[i+'_pct'] = df_type_time_pct[i]/df_type_time_pct['total']

df_type_time_pct.head()

# biểu diễn chung 1 frame
plt.figure(figsize=(12, 8))

ax1 = plt.subplot(3,2,1)
plt.barh(
    df_type['type'], df_type['total_ticket'],
    color = df_type['type'].replace({ 'non-promotion': 'tomato',  'promotion': 'lightskyblue'})
)

for index,value in enumerate(df_type['total_ticket']):
    plt.text(value,index,str(value))
plt.title('#ticket by type')

ax2 = plt.subplot(3,2,2)
plt.pie(df_type['total_ticket'], labels= df_type['type'],
        colors=df_type['type'].replace({ 'non-promotion': 'tomato',  'promotion': 'lightskyblue'}),
        autopct='%1.0f%%',
        startangle=90)

ax3 = plt.subplot(3,1,2)
plt.plot(df_type_time['year_month'], df_type_time['non-promotion'],
         label = 'non-promotion', color = 'tomato',
         marker = 0, linewidth = 2, markersize = 4)
plt.plot(df_type_time['year_month'], df_type_time['promotion'],
         label = 'promotion', color = 'lightskyblue',
         marker = 0, linewidth = 2, markersize = 4)

plt.title('#ticket of type by time')
# plt.xlabel('Month')
plt.ylabel('#ticket')
plt.legend(loc='upper left')
plt.xticks(rotation=90)


ax4 = plt.subplot(3,1,3)
# vẽ biểu đồ miền 100%
plt.stackplot(df_type_time_pct['year_month'], df_type_time_pct['non-promotion_pct'], df_type_time_pct['promotion_pct']
              , labels=['non-promotion', 'promotion'], colors=['tomato', 'lightsteelblue'], alpha=0.7)

plt.title('#ticket of type by time')
# plt.xlabel('Month')
plt.ylabel('#ticket')
plt.legend(loc='upper right', bbox_to_anchor=(1.15, 1))
plt.xticks(rotation=90)

plt.subplots_adjust(hspace = 0.7, top = 0.9)

#Nhan xet : giai doan sau nay, promotion tang rat nhang

"""### 3.3.5 Which movie they watched ?"""

df_film_sum = (
    df_join_all[df_join_all['status_id'] == 1]
    .groupby('movie_name')
    .agg(total_ticket = ('ticket_id', 'count'),
         total_customer = ('customer_id', 'nunique'),
         revue = ('final_price', 'sum'))
    .sort_values(by='total_ticket', ascending=False)
    .reset_index()
)

df_film_sum.head()

#danh sach phim qua dai nen ko the lay duoc het => phai loc ra

list_film = df_film_sum[df_film_sum['total_ticket'] > 1000] ['movie_name'].unique()
list_selected_film = list(list_film)

list_selected_film

df_movie_time_pivot = (
    df_join_all[(df_join_all['status_id'] == 1) & (df_join_all['movie_name'].isin(list_selected_film))]
    .pivot_table( index= 'year_month', columns ='movie_name', aggfunc='count', values='ticket_id')
    .reset_index()
)

df_movie_time_pivot.head(5)

#Biểu đồ cột chồng
ax = df_movie_time_pivot.plot(x = 'year_month', kind='bar', stacked=True, figsize=(15, 6), width=0.8, alpha = 0.7)

# Set the title and labels
ax.set_title('Movie name trend')
# ax.set_xlabel('Month')
ax.set_ylabel('Number of Tickets')

# Add a legend
plt.legend(title='Movies', loc='upper right', bbox_to_anchor=(1.35, 1))

# Show the plot
plt.show()



"""## 3.4 Customer value dimension

Muc tieu : Phan tich cac chi so ve gia tri ma mot KH mang lai
*   Frequency : count, day, month
*   Monetary : total, money, total_discount
*   Success rate : number_success /total
*   Promotion rate = number_promotion / total_success
*   Discount rate = sum_discount / sum_money
"""

#khi su dung head ma bi ba cham (...) vi nhieu data qua thi phai su dung them hai cau lenh sau de in ra day du
pd.set_option('display.max_colwidth', None)
pd.set_option('display.max_columns', None)

df_join_all.head(2)

#Tinh tat ca cac chi so tren

#Tinh chi so cho nhung don hang thanh cong :

def calculate_n_promotion(x):
  return (x == 'promotion').sum()

df_success_metric = (
    df_join_all[df_join_all['status_id'] == 1]
    .assign(date = pd.to_datetime(df_join_all['time']).dt.date)  #tao ra them cot date de tinh toan
    .groupby('customer_id')
    .agg(
        n_success = ('ticket_id', 'count'),
        s_money = ('original_price', 'sum'),
        s_discount = ('discount_value', 'sum'),
        n_days = ('date', 'nunique'),
        n_month = ('year_month', 'nunique'),
        n_promotion = ('type', calculate_n_promotion)
    )
    .reset_index()
)

df_success_metric.head(2)

#Tinh cac chi so : total va cac giao dich loi

def calculate_n_failed(x):
  return (x != 1).sum() #bien status_id != 1

df_failed_metric = (
    df_join_all
    .groupby('customer_id')
    .agg(
        n_total = ('ticket_id', 'count'),
        n_failed = ('status_id', calculate_n_failed)
    )
    .reset_index()
)

df_failed_metric.head(2)

#Join hai bang

df_customer_value = (
    pd.merge(df_success_metric, df_failed_metric, how = 'left', on='customer_id')
    .fillna(0)
)
df_customer_value.head(2)

df_customer_value['success_rate'] = df_customer_value['n_success'] / df_customer_value['n_total']
df_customer_value['promotion_rate'] = df_customer_value['n_promotion'] / df_customer_value['n_success']
df_customer_value['discount_rate'] = df_customer_value['s_discount'] / df_customer_value['s_money']
df_customer_value.head(2)

#Visualize tat ca cac chi so bang Histogram :

df_customer_value.iloc[:, 1:].hist(figsize = (12, 9), grid = False, color = 'cornflowerblue', bins = 20)
plt.show()

""">- **Note:**
>>- **n_total**: Hau het cac KH mua ve rat it (1-2 lan) nhung co top nhung nguoi mua ve rat nhieu (250 ve) => check nhung nguoi nay
>>- **succes_rate**: co khoang 10% success rate = 0, tuc la ko the thanh toan thanh cong => kiem tra loi thanh toan
>>- **promotion_rate**: co hon 60000 KH chi tham gia promotion (rate = 100%)
>>- **n_promotion** : 60000 KH chi huong promotion mot lan (co lien quan gi den nhom o promotion rate = 100% khong? )

### Frequency & anomaly behavior
"""

#Voi n_total : gom nhom lai vi voi bang cu du lieu bi tap trung lech ve trai qua
df_customer_value['n_order_dis'] = df_customer_value['n_success'].apply(lambda x: 'more than 10' if x >=10 else str(x))
df_customer_value.head(2)

df_n_dis = df_customer_value.groupby('n_order_dis').agg(total_cus = ('customer_id', 'count')).reset_index()
df_n_dis

# Biểu đồ cột ngang :
plt.figure(figsize=(8, 4))

plt.barh(
    df_n_dis['n_order_dis'], df_n_dis['total_cus']
)

for index,value in enumerate(df_n_dis['total_cus']):
    plt.text(value,index,str(value))

#hau het mn chi mua ve 1 lan
#Voi nhung ng mua nhieu : Neu ho mua cung 1 luc => bat thuong. Neu ho mua dan trai => binh thuong

df_customer_value.sort_values(by='n_success', ascending= False).head(20)

#Can xem tan suat giao dich cua nhung nguoi mua tren 30 ve
#Lay ra danh sach nhung nguoi nay
#Join voi bang join_all => tim duoc lich su mua ve cua nhung nguoi nay

List_customer_massive = list(df_customer_value[df_customer_value['n_success'] > 30 ]['customer_id'].unique())
List_customer_massive

df_customer_massvie_pivot = (
    df_join_all[df_join_all['customer_id'].isin(List_customer_massive) & (df_join_all['status_id'] == 1)]
    .pivot_table(index= 'year_month', columns ='customer_id', aggfunc='count', values ='ticket_id')
    .reset_index()
)

df_customer_massvie_pivot.head()

#Biểu đồ cột chồng
ax = df_customer_massvie_pivot.plot(x = 'year_month', kind='bar', stacked=True, figsize=(15, 6), width=0.8, alpha = 0.7)

# Set the title and labels
ax.set_title('Massive customer trend')
# ax.set_xlabel('Month')
ax.set_ylabel('Number of Tickets')

# Add a legend
plt.legend(title='customer_id', loc='upper right', bbox_to_anchor=(1.35, 1))

# Show the plot
plt.show()

#Moi nguoi mua hau het deu rai rac. Co mau xanh duong mua nhieu nhat thi moi thang mua khoang 40 ve => chua co gi bat thuong
#Neu muon tim hieu ki hon thi phan tich ki profil cua nhung nguoi nay

"""### Massive promotion"""

df_customer_value['n_promo_dis'] = df_customer_value['n_promotion'].apply(lambda x: 'more than 10' if x >=10 else str(x))
df_customer_value.head(2)

df_promo_dis = df_customer_value.groupby('n_promo_dis').agg(total_cus = ('customer_id', 'count')).reset_index()
df_promo_dis

plt.figure(figsize=(8, 4))

plt.barh(
    df_promo_dis['n_promo_dis'], df_promo_dis['total_cus']
)

for index,value in enumerate(df_promo_dis['total_cus']):
    plt.text(value,index,str(value))

#Gan 60% join cac ctrinh KM. Trong khi do 90% la chi huong KM 1 lan
#Vay thi: 1. KH den mot lan roi thoi. 2. Cac ctrinh KM chi cho 1 nguoi dung 1 lan (chi danh cho KH moi) ???
#Minh co thong tin ve promotion => kiem tra thong tin nay

df_type_group = (
    df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['type'] == 'promotion')]
    .groupby('campaign_type')
    .agg(total = ('ticket_id', 'count'))
    .reset_index()
)
df_type_group

## Tinh ti le loai Km cho tung nhom KH (nhom 1, 2, 3 KM,...)
df_n_success = (
     df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['type'] == 'promotion')]
     .groupby('customer_id')
     .agg(n_promotion = ('ticket_id', 'count'))
)

df_n_pivot = (
     df_join_all[ (df_join_all['status_id'] == 1) & (df_join_all['type'] == 'promotion')]
     .pivot_table(index= 'customer_id', columns ='campaign_type', aggfunc='count', values ='ticket_id')
     .reset_index()
)
df_n_pivot.head()

df_n_join = (
    pd.merge(df_n_success, df_n_pivot, how = 'left', on='customer_id')
    .groupby('n_promotion')
    .agg(n_cus = ('customer_id', 'count'),
          n_voucher = ('voucher', 'sum'),
          n_d_discount = ('direct discount', 'sum'),
          n_reward_point = ('reward point', 'sum'))
    .reset_index()
)
df_n_join.head()

df_n_join['total'] = df_n_join.iloc[:, 2:].sum(axis=1)

for i in df_n_join.columns[2:5]:
  df_n_join[i+'_pct'] = df_n_join[i]/df_n_join['total']
df_n_join.head()

#Doi thanh so %
format_dict = {'total' : '{:.0f}', 'n_voucher_pct':'{:.0%}', 'n_d_discount_pct':'{:.0%}', 'n_reward_point_pct':'{:.0%}' }

#heatmap cho cac bang

(
    df_n_join
    .drop(columns = ['n_voucher', 'n_d_discount', 'n_reward_point'])
    .style
    .format(format_dict)
    .background_gradient(subset = ['n_voucher_pct', 'n_d_discount_pct', 'n_reward_point_pct'], cmap = 'PuBu')
    .background_gradient(subset = ['total'], cmap = 'YlGn')
)

#Tat ca cac nhom KH deu su dung loai discount rat nhieu => best option
#---> Danh gia ve retention cua KH => co quay lai ko, chien luoc MKT co hieu qua ko

"""## 3.5 Customer retention

### Cohort Analysis

*   Danh gia ti le giu chan KH (danh gia theo chieu doc: neu ti le nay ngay cang tang tuc la san pham hoac dich vu da duoc cai thien)
*   Tim ra cac yeu to anh huong den retention (danh gia theo chieu ngang: Tai sao cung mot san pham ma nhom KH nay lai su dung nhieu hon nhom KH khac, co li do nao ko ?)
"""

from operator import attrgetter
import matplotlib.colors as mcolors
import seaborn as sns

# Bước 1: Tính toán các thông tin: cohort (first_month), current_month, subsequent month

df_selected_time = df_join_all[ (df_join_all['time'] < '2020-01-01') & (df_join_all['status_id'] == 1)]
df_selected_time['first_month'] = df_selected_time.groupby('customer_id')['time'].transform('min').dt.to_period('M')
df_selected_time['current_month'] = df_selected_time['time'].dt.to_period('M')
df_selected_time['subsequent_month'] = (df_selected_time['current_month'] - df_selected_time['first_month']).apply(attrgetter('n'))

# bước 2: Group by cohort

df_cohor = (
    df_selected_time
    .groupby(['first_month', 'current_month', 'subsequent_month'])
    .agg(n_customers = ('customer_id', 'nunique'))
    .reset_index(drop=False)
)

# Pivot table
df_cohort_pivot = (
    df_cohor
    .pivot_table(index = 'first_month', columns = 'subsequent_month', values='n_customers')
)

# chuyển sang %
cohort_size = df_cohort_pivot.iloc[:,0]
retention_matrix = df_cohort_pivot.divide(cohort_size, axis = 0)

# Vẽ biểu đồ cohort
with sns.axes_style("white"):
    fig, ax = plt.subplots(1, 2, figsize=(12, 8), sharey=True, gridspec_kw={'width_ratios': [1, 11]})

    # retention matrix
    sns.heatmap(retention_matrix,
                mask=retention_matrix.isnull(),
                annot=True,
                fmt='.0%',
                cmap='YlGnBu',
                ax=ax[1])
    ax[1].set_title('Monthly Cohorts: User Retention 2019', fontsize=16)
    ax[1].set(xlabel='subsequent months',
              ylabel='')

    # cohort size
    cohort_size_df = pd.DataFrame(cohort_size).rename(columns={0: 'original customers'})
    white_cmap = mcolors.ListedColormap(['white'])
    sns.heatmap(cohort_size_df,
                annot=True,
                cbar=False,
                fmt='g',
                cmap=white_cmap,
                alpha=0.5,
                ax=ax[0])

    fig.tight_layout()

#----> Retention rat thap
#Co the cty chi dang dau tu vao thu hut KH moi
#Hau nhu KH deu tham gia KM nhung KH ko quay lai
#---> So sanh xem tap KH promotion va KH organic co khac gi nhau ko

"""### Compare: Retention of promotion customers & organic customers"""

#By payment method
df_pie_promo = (
    df_join_all[(df_join_all['status_id'] ==1) & (df_join_all['time'] > '2022-01-01')]
    .groupby('type')
    .agg(total_ticket = ('ticket_id', 'count'))
    .sort_values(by='total_ticket', ascending= False)
    .reset_index()
)

#Ve bieu do pie
plt.figure(figsize=(6,3))
plt.pie(df_pie_promo['total_ticket'],
        labels = df_pie_promo['type'],
        autopct='%1.0f%%',
        colors=df_pie_promo['type'].replace({ 'non-promotion': 'tomato',  'promotion': 'lightskyblue'}),
        startangle=90)

plt.title('Percentage by type')
plt.show()

#Phan biet hai nhom bang cach dua vao ve dau tien
#---> Danh so thu tu ticket cua KH

df_data_check =  (
    df_join_all[(df_join_all['status_id'] ==1) & (df_join_all['time'] > '2022-01-01')][['customer_id', 'ticket_id', 'time', 'type']]
    .sort_values(by=['customer_id', 'time'])
)

df_data_check.head()

df_data_check['row_number'] = df_data_check.groupby('customer_id').cumcount() + 1
df_data_check.head()

#Tim KH den tu promotion => filter type = promo va row = 1
df_data_check[ (df_data_check['type'] == 'promotion') & (df_data_check['row_number'] == 1)]['customer_id'].nunique()

df_pie_promo

46189 / 52045

#Co 88% KH tham gia promotion la KH den voi rap chieu phim qua promotion
#Trong 46189 nay co bao nhieu nguoi quay lai ?

df_first_promo_list = df_data_check[(df_data_check['type'] == 'promotion') & (df_data_check['row_number'] == 1)]['customer_id']
df_first_promo_list.drop_duplicates(inplace=True)

df_first_promo_check = pd.merge(df_data_check, df_first_promo_list, how = 'inner', on='customer_id')
df_first_promo_check.head()

(
    df_first_promo_check[ (df_first_promo_check['row_number'] == 2)]['customer_id'].nunique()
    /
    df_first_promo_check['customer_id'].nunique()
)

#13% KH quay lai ke tu lan dau tham gia promotion
#Nhom organic co khac biet ko ?

df_first_non_promo = df_data_check[(df_data_check['type'] == 'non-promotion') & (df_data_check['row_number'] == 1)]['customer_id']
df_first_non_promo.drop_duplicates(inplace=True)

df_first_non_promo_check = pd.merge(df_data_check, df_first_non_promo, how = 'inner', on='customer_id')

(
    df_first_non_promo_check[ (df_first_non_promo_check['row_number'] == 2)]['customer_id'].nunique()
    /
    df_first_non_promo_check['customer_id'].nunique()
)

#12% => ti le retention giua hai nhom ko chenh nhau

"""

*   97% promotion trong nam 2022 danh cho KH lan dau
*   Tuy nhien chi co 13% KH quay tro lai, ti le nay o KH organic la 12%
---> Cty dang chu trong vao thu hut KH moi nhung chua day manh viec giu chan KH cu
"""

#ko co du thong tin de danh gia ve cac promotion va profil KH

"""## 3.6 Payment success rate

### Overview
"""

def calculate_n_success(x):
    return (x == 1).sum()

df_sr = (
    df_join_all
    .groupby('year_month')
    .agg(n_ords = ('ticket_id', 'count'), # total ticket
         n_success = ('status_id', calculate_n_success)) # success ticket
    .assign(success_rate=lambda x: (x['n_success'] / x['n_ords']) * 100) # tạo thêm 1 column tính SR
    .reset_index()
)

# Vẽ biểu đồ cột và đường

from matplotlib.ticker import PercentFormatter

fig, ax1 = plt.subplots(figsize=(15,4))

ax1.bar(df_sr['year_month'], df_sr['n_ords'], color="cornflowerblue")
plt.ylabel('#tickets', fontsize=11)
plt.xticks(rotation='vertical')

ax2 = ax1.twinx()
ax2.plot(df_sr['year_month'], df_sr['success_rate'], color="tomato", marker="o", ms=3)
ax2.yaxis.set_major_formatter(PercentFormatter())

plt.ylabel('success rate', fontsize=11)
plt.title('Payment success rate by months', fontsize=14)

#SR tang dan ve thoi gian sau nay

"""### Error trend"""

#Phan bo nhom loi :
df_error_group = (
    df_join_all[df_join_all['status_id'] != 1]
    .groupby(['year_month', 'error_group'])
    .agg(n_ords = ('ticket_id', 'count'))
    .sort_values(by='year_month', ascending=True)
    .reset_index()
)

df_error_group.head()

df_error_group['error_group'].unique()

error_color_pairs =  [('external', 'tomato'), ('customer', 'skyblue'), ('internal', 'green')]

plt.figure(figsize=(15, 4))
for error, color in error_color_pairs:
  df_eff_line = df_error_group[df_error_group['error_group'] == error]
  plt.plot(df_eff_line['year_month'], df_eff_line['n_ords'], color=color, label=error, marker = 'o', linewidth=2, markersize=2)

plt.legend(loc='upper right', bbox_to_anchor=(1.15, 1))
plt.title('Total tickets of error by group')
plt.xlabel('Year')
plt.ylabel('Number of tickets')
plt.xticks(rotation=90)
plt.show()

#Loi internal la loi he thong thi rat it
#Loi external tang cao theo so KH

#Phan bo ma loi :
df_error = (
    df_join_all[df_join_all['status_id'] != 1]
    .groupby(['year_month', 'description'])
    .agg(n_ords = ('ticket_id', 'count'))
    .sort_values(by='year_month', ascending=True)
    .reset_index()
)

df_error.head()

df_error['description'].unique()

error_color_pairs =  [
    ('Insufficient funds in customer account. Please add more funds and try the transaction again.', 'tomato'),
    ('No response from your bank', 'skyblue'),
    ('Password locked due to multiple incorrect attempts. Choose Forgot Password to unlock.', 'green'),
    ('Payment failed from bank', 'yellow'),
    ('Payment overdue', 'peru'),
    ('Transaction temporarily limited', 'purple'),
    ('Need verify your account to continue', 'gray')]

plt.figure(figsize=(15, 4))
for error, color in error_color_pairs:
  df_eff_line = df_error[df_error['description'] == error]
  plt.plot(df_eff_line['year_month'], df_eff_line['n_ords'], color=color, label=error, marker = 'o', linewidth=2, markersize=2)

plt.legend(loc='upper right', bbox_to_anchor=(1.3, 1))
plt.title('Total error by month')
plt.xlabel('Year')
plt.ylabel('Number of tickets')
plt.xticks(rotation=90)
plt.show()

#Failed from bank chiem da so => Kho kiem soat duoc
#Co mot nhom KH gap success rate = 0 va ho tu bo ko tiep tuc thanh toan => kiem tra them

"""### SR = 0% => why?"""

df_customer_value.head()

list_sr_0 = list(df_customer_value[df_customer_value['success_rate'] < 0.1]['customer_id'].unique())

df_customer_value[df_customer_value['success_rate'] < 0.1]['customer_id'].nunique()

def calculate_n_promotion(x):
    return (x == 'promotion').sum()

df_sr_0_metric = (
    df_join_all[df_join_all['customer_id'].isin(list_sr_0)]
    .groupby('customer_id')
    .agg(n_orders = ('ticket_id', 'count'),
        s_money = ('original_price', 'sum'),
        s_discount = ('discount_value', 'sum'),
        n_promotion = ('type', calculate_n_promotion)
    )
    .reset_index()
)

df_sr_0_metric.head()

df_sr_0_metric['promotion_rate'] = df_sr_0_metric['n_promotion'] / df_sr_0_metric['n_orders']
df_sr_0_metric['discount_rate'] = df_sr_0_metric['s_discount'] / df_sr_0_metric['s_money']

df_sr_0_metric.iloc[:, 1:].hist(figsize=(12, 8), grid=False, bins=20)
plt.show()

#Loi o dau do nen du lieu bi ko hien ra (ko con du lieu SR = 0)