Exploring Consumer Purchases Patterns using GridDB and Python

import griddb_python as griddb

import gc
import numpy as np
import pandas as pd
from pandas.io.json import json_normalize
from scipy.stats import norm
import json
import datetime
import random

import squarify
import seaborn as sns
import matplotlib.pyplot as plt
from plotly.offline import init_notebook_mode, iplot
import plotly.graph_objs as go
from plotly import tools

import warnings
warnings.filterwarnings('ignore')
%matplotlib inline

factory = griddb.StoreFactory.get_instance()

Initialize the GridDB container (enter your database credentials)
try:
    gridstore = factory.get_store(host=host_name, port=your_port, 
            cluster_name=cluster_name, username=admin, 
            password=admin)

    info = griddb.ContainerInfo("customer_data",
                    [["channelGrouping", griddb.Type.STRING],["geoNetwork", griddb.Type.STRING],["device", griddb.Type.STRING],
                     ["date", griddb.Type.TIMESTAMP],["sessionId", griddb.Type.STRING],["socialEngagementType", griddb.Type.STRING],
                     ["totals", griddb.Type.STRING],["trafficSource", griddb.Type.STRING],["visitStartTime", griddb.Type.TIMESTAMP],
                     ["fullVisitorId", griddb.Type.INTEGER],["visitNumber", griddb.Type.INTEGER],["visitId", griddb.Type.INTEGER],
                    griddb.ContainerType.COLLECTION, True)
    cont = gridstore.put_container(info) 
    data = pd.read_csv("False.csv")
    #Add data
    for i in range(len(data)):
        ret = cont.put(data.iloc[i, :])
        print("Data added successfully")

sql_statement = ('SELECT * FROM customer_data.csv')
df = pd.read_sql_query(sql_statement, cont)

df = pd.read_csv("customer_data.csv")

df.head()

def load_df(nrows=None):
    
    Columns = ['device', 'geoNetwork', 'totals', 'trafficSource']
    df = pd.read_csv('customer_data.csv',
                     converters={column: json.loads for column in Columns}, 
                     dtype={'fullVisitorId': 'str'}, nrows=nrows)
    
    for column in Columns:
        column_as_df = json_normalize(df[column])
        column_as_df.columns = [f"{column}_{subcolumn}" for subcolumn in column_as_df.columns]
        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)
    return df

df = load_df()
df.head()

# converting date column into a proper format
def convert_to_datetime(frame):
    frame['date'] = frame['date'].astype(str)
    frame['date'] = frame['date'].apply(lambda x : x[:4] + "-" + x[4:6] + "-" + x[6:])
    frame['date'] = pd.to_datetime(frame['date'])
    return frame

df = convert_to_datetime(df)

#converting totals_transactionRevenue into int64 datatype and filling missing values as 0
df['totals_transactionRevenue'] = df['totals_transactionRevenue'].fillna(0).astype('int64')

#Analysing missing data
def missing_plot(frame, set_name, palette):
    nan_ratio = frame.isna().sum()/len(frame)
    nan_ratio = nan_ratio.to_frame().reset_index().rename({'index': 'column', 0: 'Percentage'},axis=1)
    nan_ratio.sort_values(by=['Percentage'], ascending=False, inplace=True)
    plt.figure(figsize=(8,6))
    plt.title("% Columns Missing Values")
    ax = sns.barplot(x='Percentage', y='column', orient='h',
                     data=nan_ratio[nan_ratio['Percentage'] > 0],
                     palette= palette)

missing_plot(df, 'df', 'Greens_d')

# This plot shows the number of visits for each channel
def barplot_percentage(count_feat, color1= 'rgb(55, 83, 109)', 
                       color2= 'rgb(26, 118, 255)',num_bars= None):

    df_channel = 100*df[count_feat].value_counts()/len(df)
    df_channel = df_channel.to_frame().reset_index()
    if num_bars:
        df_channel = df_channel.head(num_bars)

    trace0 = go.Bar(
        x=df_channel['index'],
        y=df_channel[count_feat],
        name='df set',
        marker=dict(color=color1)
    )


    layout = go.Layout(
        title='{} grouping'.format(count_feat),
        xaxis=dict(
            tickfont=dict(size=14, color='rgb(107, 107, 107)')
        ),
        yaxis=dict(
            title='Percentage of visits',
            titlefont=dict(size=16, color='rgb(107, 107, 107)'),
            tickfont=dict(size=14, color='rgb(107, 107, 107)')
        ),
        legend=dict(
            x=1.0,
            y=1.0,
            bgcolor='rgba(255, 255, 255, 0)',
            bordercolor='rgba(255, 255, 255, 0)'
        ),
        barmode='group',
        bargap=0.15,
        bargroupgap=0.1
    )

    fig = go.Figure(data=[trace0], layout=layout)
    iplot(fig)
barplot_percentage('channelGrouping')

# Now we will analyse the time series data

tmp_df = df['date'].value_counts().to_frame().reset_index().sort_values('index')
tmp_df = tmp_df.rename(columns = {"date" : "visits"}).rename(columns = {"index" : "date"})

# Plot visits
trace1 = go.Scatter(x=tmp_df.date.astype(str), y=tmp_df.visits,
                    opacity = 0.8, line = dict(color = '#ff751a'), name= 'df')

traces = [trace1]

layout = dict(
    title= "Visits by date",
    xaxis=dict(
        rangeselector=dict(
            buttons=list([
                dict(count=1, label='1m', step='month', stepmode='backward'),
                dict(count=3, label='3m', step='month', stepmode='backward'),
                dict(count=6, label='6m', step='month', stepmode='backward'),
                dict(step='all')
            ])
        ),
        rangeslider=dict(visible = True),
        type='date'
    )
)

fig = dict(data= traces, layout=layout)
iplot(fig)


# Revenue by time
df_date_sum = df.groupby('date')['totals_transactionRevenue'].sum().to_frame().reset_index()
# Plot
trace_date = go.Scatter(x=df_date_sum.date.astype(str), 
                        y=df_date_sum['totals_transactionRevenue'].apply(lambda x: np.log(x)), opacity = 0.8)
layout = dict(
    title= "Log Revenue by date",
    xaxis=dict(
        rangeselector=dict(
            buttons=list([
                dict(count=1, label='1m', step='month', stepmode='backward'),
                dict(count=3, label='3m', step='month', stepmode='backward'),
                dict(count=6, label='6m', step='month', stepmode='backward'),
                dict(step='all')
            ])
        ),
        rangeslider=dict(visible = True),
        type='date'
    )
)

fig = dict(data= [trace_date], layout=layout)
iplot(fig)

# Flag visits with revenue
df['has_revenue'] = df['totals_transactionRevenue'].apply(lambda x: 1 if x > 0 else 0)

channel_order = ['Referral', 'Display', 'Paid Search', 'Direct', 'Organic Search', '(Other)', 'Social', 'Affiliates']
plt.figure(figsize=(10,4))
plt.title("% visits with revenue / channel")
sns.barplot(x='channelGrouping', y='has_revenue', data=df, order=channel_order, palette='Greens_d')
revenue_channel = df.groupby('channelGrouping')['totals_transactionRevenue'].sum()
revenue_channel = revenue_channel.to_frame().reset_index()
plt.figure(figsize=(10,4))
plt.title("Mean revenue / channel")
ax = sns.barplot(x='channelGrouping', y='totals_transactionRevenue', data=revenue_channel, order=channel_order, palette='Greens_d')

df['revenue_status']=df.totals_transactionRevenue.apply(lambda x: 0 if x==0 else 1)

#Revenue generated by different Operating systems

df_OS=df[['device_operatingSystem','totals_transactionRevenue','revenue_status']].groupby(df.device_operatingSystem).aggregate({'totals_transactionRevenue':['mean'],
                                                                                                              'revenue_status':['count']}).reset_index()
df_OS.columns=['device_operatingSystem','totals_transactionRevenue_mean','revenue_status_count']
df1=df_OS.sort_values(by='totals_transactionRevenue_mean',ascending=False)[df_OS.totals_transactionRevenue_mean>0]
display(df1.style.format(formatter))

plt.subplots(figsize=(20,5))
plt.subplot(1,2,1)
plt.title('REVENUE MEAN BY OPERATING SYSTEM',color='b',fontsize=12)
plt.xlabel('Operating Systems',color='b',fontsize=12)
plt.ylabel('Mean Revenue',color='b',fontsize=12)
plt.bar(range(len(df1)),df1.totals_transactionRevenue_mean,color='grey')
plt.xticks(range(len(df1)),df1.device_operatingSystem,rotation=90,fontsize=12)
plt.yticks(fontsize=12)


plt.subplot(1,2,2)
plt.title('NUMBER OF TRANSACTIONS WITH REVENUE BY OPERATING SYSTEM',color='b',fontsize=12)
plt.xlabel('Operating Systems',color='b',fontsize=12)
plt.ylabel('Count of Transactions with Revenue',color='b',fontsize=12)
plt.bar(range(len(df1)),df1.revenue_status_count,color='orange')
plt.yticks(fontsize=12)
plt.xticks(range(len(df1)),df1.device_operatingSystem,rotation=90,fontsize=12)
plt.show()

df_browser=df[['device_browser','totals_transactionRevenue','revenue_status']].groupby(df.device_browser).aggregate({'totals_transactionRevenue':['mean'],
                                                                                                              'revenue_status':['count']}).reset_index()
df_browser.columns=['device_browser','totals_transactionRevenue_mean','revenue_status_count']
df1=df_browser.sort_values(by='totals_transactionRevenue_mean',ascending=False)[df_browser.totals_transactionRevenue_mean>0]
formatter = {'totals_transactionRevenue_mean':'{:4.2f}'}
display(df1.style.format(formatter))

plt.subplots(figsize=(20,5))
plt.subplot(1,2,1)
plt.title('REVENUE MEAN BY BROWSER',color='b',fontsize=12)
plt.xlabel('Browsers',color='b',fontsize=12)
plt.ylabel('Mean Revenue',color='b',fontsize=12)
plt.bar(range(len(df1)),df1.totals_transactionRevenue_mean,color='grey')
plt.xticks(range(len(df1)),df1.device_browser,rotation=90,fontsize=12)
plt.yticks(fontsize=12)


plt.subplot(1,2,2)
plt.title('NUMBER OF TRANSACTIONS WITH REVENUE BY BROWSER',color='b',fontsize=12)
plt.xlabel('Browsers',color='b',fontsize=12)
plt.ylabel('Count of Transactions with Revenue',color='b',fontsize=12)
plt.bar(range(len(df1)),df1.revenue_status_count,color='orange')
plt.xticks(range(len(df1)),df1.device_browser,rotation=90,fontsize=12)
plt.yticks(fontsize=12)
plt.show()

def plotmap(frame, z_var, countries_col, title, colorscale, rcolor=True):

    data = [ dict(
            type = 'choropleth',
            autocolorscale = False,
            colorscale = colorscale,
            showscale = True,
            reversescale = rcolor,
            locations = frame[countries_col],
            z = frame[z_var],
            locationmode = 'country names',
            text = frame[countries_col],
            marker = dict(line = dict(color = '#fff', width = 2))
        )           
    ]

    layout = dict(
        height=680,
        #width=1200,
        title = title,
        geo = dict(
            showframe = False,
            showcoastlines = False,
            projection = dict(type = 'mercator'),
        ),
    )
    fig = dict(data=data, layout=layout)
    iplot(fig)

colorscale = [[0, 'rgb(102,194,165)'], [0.005, 'rgb(102,194,165)'], 
              [0.01, 'rgb(171,221,164)'], [0.02, 'rgb(230,245,152)'], 
              [0.04, 'rgb(255,255,191)'], [0.05, 'rgb(254,224,139)'], 
              [0.10, 'rgb(253,174,97)'], [0.25, 'rgb(213,62,79)'], [1.0, 'rgb(158,1,66)']]
    
# Plot world map - total visits
tmp = df["geoNetwork_country"].value_counts().to_frame().reset_index()
plotmap(tmp, 'geoNetwork_country', 'index', 'Total visits by Country', colorscale, False)

colorscale = [[0,"rgb(5, 10, 172)"],[0.35,"rgb(40, 60, 190)"],[0.5,"rgb(70, 100, 245)"],\
        [0.6,"rgb(90, 120, 245)"],[0.7,"rgb(106, 137, 247)"],[1,"rgb(220, 220, 220)"]]

# Plot world map - total revenue
tmp = df.groupby("geoNetwork_country").agg({"totals_transactionRevenue" : "sum"}).reset_index()
plotmap(tmp, 'totals_transactionRevenue','geoNetwork_country', 'Total revenue by country', colorscale)

# Using squarify to plot an interesting distribution
df.loc[df["geoNetwork_city"] == "not available in demo dataset", 'geoNetwork_city'] = np.nan
number_of_colors = 20 
color = ["#"+''.join([random.choice('0123456789ABCDEF') for j in range(6)])
             for i in range(number_of_colors)]

city_tree = df["geoNetwork_city"].value_counts() #counting 

print("Description most frequent Citys: " )
print(city_tree[:15])

city_tree = round((city_tree[:30] / len(df['geoNetwork_city']) * 100),2)

plt.figure(figsize=(14,5))
g = squarify.plot(sizes=city_tree.values, label=city_tree.index, 
                  value=city_tree.values,
                  alpha=.4, color=color)
g.set_title("'TOP 30 Citys - % size of total",fontsize=20)
g.set_axis_off()
plt.show()

barplot_percentage('geoNetwork_networkDomain', num_bars= 10, 
                   color1='rgb(38, 115, 77)', color2='rgb(102, 204, 153)')

plt.figure(figsize=(14,5))

plt.subplot(1,2,1)
sns.countplot(df["device_deviceCategory"], palette="hls") 
plt.title("Device Category Count", fontsize=10)
plt.xlabel("Category", fontsize=12) 
plt.ylabel("Count", fontsize=12) 
plt.xticks(fontsize=10)

plt.subplot(1,2,2)
sns.boxenplot(x="device_deviceCategory", y = 'totals_transactionRevenue', 
              data=df[df['totals_transactionRevenue'] > 0], palette="hls")
plt.title("Device Category Revenue Distribuition", fontsize=10)
plt.xlabel("Category", fontsize=12) 
plt.ylabel("Revenue(Log)", fontsize=12) 
plt.xticks(fontsize=10) 

plt.subplots_adjust(hspace = 0.9, wspace = 0.5)

plt.show()