<jupyter_start><jupyter_text>ANZ Synthesised Transaction Dataset The dataset contains the synthesised transaction of 100 accounts by ANZ.The dataset is designed to simulate realistic transaction behaviours that are observed in ANZ’s real transaction data, so many of the insights you can gather from the tasks below will be genuine.<jupyter_code># Importing Libraries
import numpy as np
import pandas as pd 
import plotly.express as px
import warnings
import plotly.graph_objects as go
import plotly.express as px
import plotly.figure_factory as ff
from plotly.subplots import make_subplots
import plotly.io as pio
%matplotlib inline
pio.templates.default = "plotly_dark"
warnings.filterwarnings('ignore')
df = pd.read_excel('ANZ_synthesised_transaction.xlsx')
df.head()
# Lets check the Data¶
df.shape
df.info()
df.isnull().sum() #'bpay_biller_code' & 'merchant_code' have majority of the values as NULLS so can be easily dropped
df.country.value_counts() # This can be dropped as we are only dealing with one country
df.currency.value_counts() # This can be also dropped as we are only dealing with just one currency
# Drop 'bpay_biller_code' ,Currency , country and 'merchant_code' columns.
df.drop(['bpay_biller_code','merchant_code', 'currency','country'],axis=1,inplace=True)
# Lets check if we have any duplicates records in the dataset
df.duplicated().sum() # NO Duplicates
# Create Age buckets to analyse data as per age group
df['age_group']=pd.cut(df.age,[0,20,30,40,50,60,99999],labels=['<20','20-30','30-40','40-50','50-60','>60'])
# Change datatype of extraction to datetime
df.loc[:,['extraction','date']] = df.loc[:,['extraction','date']].apply(pd.to_datetime, errors='coerce')
df.info() # Datatype of extraction & date has been updated
# Create date helper columns
df['month'] =df['date'].dt.month_name()
df['day'] = df['date'].dt.day_name()
df['hour']= df.extraction.dt.hour
df.head()
# Change datatype of card_present_flag to INT
df.card_present_flag = df.card_present_flag.astype('Int64')
df.head()
# Analyze Categorical variables¶
cols = ['card_present_flag', 'status', 'txn_description' , 'movement' , 'gender', 'merchant_state']

#Subplot initialization
fig = make_subplots(
                     rows=3, 
                     cols=2,
                     subplot_titles=('card_present_flag', 'status', 'txn_description' , 'movement','gender', 'merchant_state'),
                     horizontal_spacing=0.2,
                     vertical_spacing=0.2 
                   )
# Adding subplots
count=0
for i in range(1,4):
    for j in range(1,3):
        fig.add_trace(go.Bar(x=df[cols[count]].value_counts().index, 
                             y=df[cols[count]].value_counts(),
                             name=cols[count],
                             textposition='auto',
                             text= [str(i) + '%' for i in (df[cols[count]].value_counts(normalize=True)*100).apply(lambda x : round(x)).tolist()],
                            ),
                      row=i,col=j)
        count+=1
fig.update_layout(
                    title=dict(text = "Analyze Categorical variables (Frequency / Percentage)",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 920,
                    margin=dict(l=80, r=80, t=150, b=80)
                  )
fig.show()
df0_grp=df.groupby(by='txn_description').sum()[['amount']].reset_index()
df0_grp.amount=df0_grp.amount.apply(lambda x : round(x))
fig=px.treemap(df0_grp,
           path=['txn_description'],
           values='amount',
           color = 'amount',
          )

fig.update_layout(
                    title=dict(text = "Total Amount  by Transaction Desciption",x=0.5,y=0.95),
                    margin=dict(l=10, r=10, t=70, b=10),
                  )
fig.data[0].textinfo = 'label+value'
fig.update_traces(marker_coloraxis=None)
fig.show()
df_grp0=df.groupby(by='merchant_suburb').sum()[['amount']].reset_index()
fig=px.treemap(df_grp0,
           path=['merchant_suburb'],
           values='amount',
           color = 'amount',
          )
fig.update_layout(
                    title=dict(text = "Total Txn Amount by Suburb",x=0.5,y=0.95),
                    margin=dict(l=10, r=10, t=50, b=10),
                    showlegend=False,
                  )
fig.data[0].textinfo = 'label+value'
fig.update_traces(marker_coloraxis=None)
fig.show()
df1 = df[df.movement=='debit'] # Debit Transactions
df1.shape # 11160 debit transactions
cols = ['card_present_flag', 'status', 'txn_description' , 'movement' , 'gender', 'merchant_state']
#Subplot initialization
fig = make_subplots(
                     rows=3, 
                     cols=2,
                     subplot_titles=('card_present_flag', 'status', 'txn_description' , 'movement','gender', 'merchant_state'),
                     horizontal_spacing=0.2,
                     vertical_spacing=0.2 
                   )
# Adding subplots
count=0
for i in range(1,4): 
    for j in range(1,3): 
        fig.add_trace(go.Bar(x=df1.groupby(by=cols[count]).sum()['amount'].index,
                             y=df1.groupby(by=cols[count]).sum()['amount'].values.round(2),
                             name=cols[count],
                             textposition='auto',
                             text=[str(round((i/sum(df1.groupby(by=cols[count]).sum()['amount'].values))*100))+'%' 
                                   for i in df1.groupby(by=cols[count]).sum()['amount'].values]
                            ),
                      row=i,col=j)
        count+=1
fig.update_layout(
                    title=dict(text = "Analyze Categorical variables (Total Txn Amount/Percentage)",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 920,
                    margin=dict(l=80, r=80, t=150, b=80)
                  )
fig.show()
df_grp=df1.groupby(by=['merchant_state','gender']).sum()[['amount']].reset_index()
order = df1.groupby(by=['merchant_state']).sum()[['amount']].sort_values(by='amount',ascending=False).index
df_grp['merchant_state']=pd.Categorical(df_grp['merchant_state'],order)
df_grp= df_grp.groupby(by=['merchant_state','gender']).sum().reset_index()
fig=px.bar(data_frame=df_grp,
       x='merchant_state',
       y='amount',color='gender',
       barmode='group',
       text=df_grp.amount.apply(lambda x : str(round(x/1000,2))+'k')
      )
fig.update_traces(textposition='outside')
fig.update_xaxes(title='Merchant State') 
fig.update_yaxes(title='Transaction Amount')
fig.update_layout(
                    title=dict(text = "Transaction Amount in Merchant State by Gender",x=0.5,y=0.95),
                    title_font_size=20,
                  )
fig.show()
fig= px.bar(data_frame=df,
       x=df1['day'].value_counts().index.tolist(), 
       y=df1['day'].value_counts().tolist(),
       color=df1['day'].value_counts().tolist(), 
       text=df1['day'].value_counts().tolist() 
      )
fig.update_traces(textposition='outside',marker_coloraxis=None)
fig.update_xaxes(title='Day') 
fig.update_yaxes(title='Transaction count')
fig.update_layout(
                    title=dict(text = "Transaction flow by each day",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 450,
                  )
fig.show()


fig1= px.bar(data_frame=df1.groupby(by='day').sum()[['amount']].sort_values('amount',ascending=False),
            text=df1.groupby(by='day').sum()[['amount']].sort_values('amount',ascending=False)['amount'].apply(lambda x : str(round(x/1000,2))+'k')
      )
fig1.update_traces(textposition='outside')
fig1.update_xaxes(title='Day') 
fig1.update_yaxes(title='Transaction Amount')
fig1.update_layout(
                    title=dict(text = "Transaction amount by each day",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 450,
                  )
fig1.show()
df1_grp=df1.groupby(by=['day','gender']).sum()[['amount']].reset_index()
order = ['Monday','Tuesday', 'Wednesday','Thursday','Friday','Saturday','Sunday']
df1_grp['day']=pd.Categorical(df1_grp['day'],order) 
df1_grp= df1_grp.groupby(by=['day','gender']).sum().reset_index()
fig=px.bar(data_frame=df1_grp,
       x='day',
       y='amount',color='gender',
       barmode='group',
       text=df1_grp.amount.apply(lambda x : str(round(x/1000,2))+'k')
      )
fig.update_traces(textposition='outside')
fig.update_xaxes(title='Day') 
fig.update_yaxes(title='Transaction Amount')
fig.update_layout(
                    title=dict(text = "Transaction Amount per day by Gender",x=0.5,y=0.95),
                    title_font_size=20,
                  )
fig.show()
fig= px.bar(data_frame=df,
       x=df['month'].value_counts().index.tolist(),
       y=df['month'].value_counts().tolist(),
       color=df['month'].value_counts().tolist(),
       text=df['month'].value_counts().tolist()
      )
fig.update_traces(textposition='outside')
fig.update_xaxes(title='Month') 
fig.update_yaxes(title='Transaction Count')
fig.update_layout(
                    title=dict(text = "Transaction flow by each month",x=0.5,y=0.95),
                    title_font_size=20,
                    width = 700,
                    height = 450,
                  )
fig.show()
fig=px.bar(df.groupby(by='customer_id').sum()['amount'].sort_values(ascending=False).head(10),
       color=df.groupby(by='customer_id').sum()['amount'].sort_values(ascending=False).head(10),
       text=df.groupby(by='customer_id').sum()['amount'].sort_values(ascending=False).head(10).round(),
      )
fig.update_traces(textposition='outside',marker_coloraxis=None)
fig.update_xaxes(title='Customer ID') 
fig.update_yaxes(title='Transaction Amount')
fig.update_layout(
                    title=dict(text = "Top 10 customers by Transaction Amount",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 500,
                  )
fig.show()
fig=px.bar(df1.age_group.value_counts(),
       color=df1.age_group.value_counts(),
       text=df1.age_group.value_counts().tolist(),
      )
fig.update_traces(textposition='outside',marker_coloraxis=None)
fig.update_xaxes(title='Age Group') 
fig.update_yaxes(title='Transaction Count')
fig.update_layout(
                    title=dict(text = "Transactions by Age Group",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 450,
                  )
fig.show()
df2_grp=df1.groupby(by=['age_group','gender']).sum()['amount'].reset_index()
fig=px.bar(data_frame=df2_grp,
       x = 'age_group',
       y = 'amount',
       color='gender',
       barmode='group',
       text=df2_grp.amount.apply(lambda x : str(round(x/1000,2))+'k')
      )
fig.update_traces(textposition='outside')
fig.update_layout(
                    title=dict(text = "Transaction Amount by Age Group & Gender",x=0.5,y=0.95),
                    title_font_size=20,
                  )
fig.show()
df3_grp=df1.groupby(by='date').mean()[['amount']].merge(df1.groupby(by='date').count()[['transaction_id']],on='date')
df3_grp.columns= ['Amount','Transaction Count']
fig=px.line(df3_grp)
fig.update_xaxes(title='Date') 
fig.update_layout(
                    title=dict(text = "Average Amount VS Txn Count over time",x=0.5,y=0.95),
                    title_font_size=20
                  )
fig.show()
fig=px.line(df1.groupby(by='date').sum()[['amount']])
fig.update_traces(line=dict(color="#8cba51", width=3.5))
fig.update_xaxes(title='Date') 
fig.update_yaxes(title='Transaction Amount')
fig.update_layout(
                    title=dict(text = "Total Txn Amount over time",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                  )
fig.show()
fig=px.line(df1.groupby(by='hour').sum()[['amount']],
            text=df1.groupby(by='hour').sum()['amount'].apply(lambda x : str(round(x/1000))+'k').values
            )
fig.update_traces(line=dict(color="#f58634", width=5))
fig.update_xaxes(title='Hour') 
fig.update_yaxes(title='Transaction Amount')
fig.update_layout(
                    title=dict(text = "Total Txn Amount hourly",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                  )
fig.update_traces(textposition='middle right',fillcolor='red')
fig.show()
df4_grp= df1.groupby(by=['hour','month','gender']).agg(['count','sum'])[['amount']].reset_index()
df4_grp.columns = ['hour', 'month' ,'gender','Transaction Count', 'Total Txn Amount']
fig1=px.line(data_frame=df4_grp,
            x=df4_grp.hour,
            y=df4_grp['Transaction Count'],
            color=df4_grp.gender,
            facet_col= df4_grp.month
           )
fig1.update_xaxes(title='Hour') 
fig1.update_layout(
                    title=dict(text = "Hourly Transaction count by Month ",x=0.5,y=0.95),
                    title_font_size=20,
                    margin=dict(l=80, r=80, t=100, b=80)
                  )
fig1.show()


fig2=px.line(data_frame=df4_grp,
            x=df4_grp.hour,
            y=df4_grp['Total Txn Amount'],
            color=df4_grp.gender,
            facet_col= df4_grp.month
           )
fig2.update_xaxes(title='Hour') 
fig2.update_layout(
                    title=dict(text = "Hourly Transaction Amount by Month ",x=0.5,y=0.95),
                    title_font_size=20,
                    margin=dict(l=80, r=80, t=100, b=80)
                  )
fig2.show()
df4_grp= df1.groupby(by=['hour','day','gender']).agg(['count','sum'])[['amount']].reset_index()
df4_grp.columns = ['hour', 'day' ,'gender','Transaction Count', 'Total Txn Amount']
fig1=px.line(data_frame=df4_grp,
            x=df4_grp.hour,
            y=df4_grp['Transaction Count'],
            color=df4_grp.gender,
            facet_col= df4_grp.day
           )
fig1.update_xaxes(title='Hour') 
fig1.update_layout(
                    title=dict(text = "Hourly Transaction count by Day ",x=0.5,y=0.95),
                    title_font_size=20,
                    margin=dict(l=80, r=80, t=100, b=80)
                  )
fig1.show()


fig2=px.line(data_frame=df4_grp,
            x=df4_grp.hour,
            y=df4_grp['Total Txn Amount'],
            color=df4_grp.gender,
            facet_col= df4_grp.day
           )
fig2.update_xaxes(title='Hour') 
fig2.update_layout(
                    title=dict(text = "Hourly Transaction Amount by Day ",x=0.5,y=0.95),
                    title_font_size=20,
                    margin=dict(l=80, r=80, t=100, b=80)
                  )
fig2.show()
# Analysing Credit Transactions

df2 = df[df.movement=='credit']
df2.shape # 883 Credit transactions
fig=px.bar(
            df2.groupby(by='customer_id').mean()['balance'].sort_values(ascending=False).head(10),
            text = df2.groupby(by='customer_id').mean()['balance'].sort_values(ascending=False).head(10).apply(
                lambda x : str(round(x/1000,2))+'k' ),
            color = df2.groupby(by='customer_id').mean()['balance'].sort_values(ascending=False).head(10)
          )
fig.update_traces(textposition='outside')
fig.update_layout(
                    title=dict(text = "Top Valuable Customers by AVG Balance",x=0.5,y=0.95),
                    title_font_size=20,
                    showlegend=False,
                    height = 500
                    
                  )
fig.update_traces(marker_coloraxis=None)
fig.show()
order = ['August','September','October']
df['month']=pd.Categorical(df['month'],order)
g1 = df.groupby(by='month').agg(['mean','sum'])['amount']
g1.columns=['Avg Amount', 'Total Amount']
g1[['Avg Amount','Total Amount']]=g1[['Avg Amount','Total Amount']].round().astype(int)
g1.reset_index(inplace=True)

g2=df.groupby(by='month').agg(['mean','sum'])['balance']
g2.columns=['Avg Balance', 'Total Balance']
g2[['Avg Balance','Total Balance']]=g2[['Avg Balance','Total Balance']].round().astype(int)
g2.reset_index(inplace=True)

month = g1.merge(g2,on='month')
month
pio.templates.default = "plotly_white"
fig = ff.create_table(month) 
for i in range(len(fig.layout.annotations)):
    fig.layout.annotations[i].font.size = 13
fig.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15781077432813842, 'fraction_numerical': 0.029451466437599706, 'mean_word_length': 2.7460353941622615, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '454563', 'license': 'MIT', 'n_tokens_mistral': 5724, 'n_tokens_neox': 5401, 'n_words': 808, 'path': 'Generating Insights from synthesised data.ipynb', 'repo_name': 'henrygero/Generating-Insights-from-synthesised-data', 'size': '16298'}