<jupyter_start><jupyter_text>Data Quality AnalysisThis notebook is created to found issues about data quality in 'Transactions','CustomerDemographic','NewCustomerList' and 'CustomerAdress' datasets given by Sprocket Central Pty Ltd Data Set<jupyter_code>#Importing libraries
#-------------------------------

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
pd.plotting.register_matplotlib_converters()
%matplotlib inline
import seaborn as sns
print("setup complete!")
#Opening the File.xlsx
#-------------------------------

file_path = "KPMG_VI_New_raw_data_update_final.xlsx"
#Function to open the sheets as dataframes
#-------------------------------

def analysing_data(file_path, sheet, col):
    data = pd.read_excel(file_path, sheet_name=sheet, index_col=0, skiprows=1, usecols=col)
    return data
#Sheet_1 - Transactions
sheet_1 = analysing_data(file_path,'Transactions','A:M')

#Sheet_2 - CustomerDemographic
sheet_2 = analysing_data(file_path,'CustomerDemographic','A:M')

#Sheet_3 - NewCustomersList
sheet_3 = analysing_data(file_path,'NewCustomerList','A:W')

#Sheet_4 - CustomerAdress
sheet_4 = analysing_data(file_path,'CustomerAddress','A:F')
#Sheet_1 - Transactions
sheet_1.info()
#Sheet_2 - CustomerDemographic
sheet_2.info()
#Sheet_3 - NewCustomersList
sheet_3.info()
#Sheet_4 - CustomerAdress
sheet_4.info()
#Function to Accuracy
#-------------------------------

def Accuracy(file):
    value = file.columns.to_series().groupby(file.dtypes).groups
    return value
#Sheet_1 - Transactions
print('---------|Sheet(1)-Transactions:')
print(Accuracy(sheet_1))
print('\n')

#Sheet_2 - CustomerDemographic
print('---------|Sheet(2)-CustomerDemographic:')
print(Accuracy(sheet_2))
print('\n')

#Sheet_3 - NewCustomersList
print('---------|Sheet(3)-NewCustomerList:')
print(Accuracy(sheet_3))
print('\n')

#Sheet_4 - CustomerAdress
print('---------|Sheet(4)-CustomerAdress:')
print(Accuracy(sheet_4))
print('\n')
#Function to Completeness
#-------------------------------
def Completeness (file):
    
    missing_values_count = file.isnull().sum()
    total_cells = np.product(file.shape)
    total_missing = missing_values_count.sum()

    #percent of data missed
    percent = (total_missing / total_cells) * 100
    complete = (total_cells - total_missing)
    print(f'Data Empty:', round(percent,2))
    print(f'Data Complete:', round(complete, 2))

    #Creating a dataframe to stackedbar
    data = pd.DataFrame({'Total_Row': file.count(axis=0), 'Missing': file.isnull().sum()})

    # create stacked bar chart for monthly temperatures
    data.plot.bar(stacked=True, figsize=(12,6))
    plt.ylabel('Number of Records')
    plt.title(f'Total Value Missed per Column')
#Sheet_1 - Transactions
print('---------|Sheet(1)-Transactions:')
print(Completeness(sheet_1))
print('\n')
 
sheet_1['online_order']
#Sheet_2 - CustomerDemographic
print('---------|Sheet(2)-CustomerDemographic:')
print(Completeness(sheet_2))
print('\n')
#Sheet_3 - NewCustomersList
print('---------|Sheet(3)-NewCustomerList:')
print(Completeness(sheet_3))
print('\n')
#Sheet_4 - CustomerAdress
print('---------|Sheet(4)-CustomerAdress:')
print(Completeness(sheet_4))
print('\n')
#Function to Consistency

def Consistency(file_list):
    
    neo = list()
    dic = dict()
    
    for x in file_list:
        for w in x.columns:
            neo.append(w)
            
    for item in neo:
        dic[item] = dic.get(item,0)+1
                
    return dic
file_list = [sheet_1, sheet_2, sheet_3, sheet_4]
Consistency(file_list)
#Comments:
#4 columns without description?
sheet_3.head()
#Function to Currency - datetime64[ns]

#-------------------------------

def Currency(file, path):
    print("Max date: {}".format(file[path].max()))
    print('------------------')
    print("Min date: {}".format(file[path].min()))
    
Currency(sheet_1, 'transaction_date')
Currency(sheet_2, 'DOB')
sheet_2.query("DOB == '1843-12-21'")
Currency(sheet_3, 'DOB')
#'product_first_sold_date' with a wrong type data - it should be date not float and cannot transform it...
sheet_1['product_first_sold_date']
pd.to_datetime(sheet_1['product_first_sold_date'][[1]], format='%m%d%y')
#Validity -> Data containing allowable values

def Validity_id(file):
    for x in file:
        if type(x) != int:
            print(x)
        else:
            pass
    print('All validated!')        


def Validity_gender(file):
    
    dic = dict()
    
    for x in file:
        if x not in dic:
            dic[x] = 1
        else:
            dic[x] = dic[x] + 1
            
    return dic

print('Sheet 1 - Transactions')
print(Validity_id(sheet_1['product_id']))
print('\n')

print('Sheet 1 - Transactions')
print(Validity_id(sheet_1['customer_id']))
print('\n')
print('Sheet 2 - Customer Demographic')
print(Validity_gender(sheet_2['gender']))
print('\n')

print('Sheet 3 - New Customer List')
print(Validity_gender(sheet_3['gender']))
print('\n')
print('Sheet 4 - Customer Address')
print(Validity_gender(sheet_4['state']))
print('\n')
#Relevancy -> Data Items with Value Meta-Data
sheet_2['default'].head()
#Uniqueness -> Values that are duplicated
def Unique(file):
    return file[file.duplicated()]
Unique(sheet_1)
sheet_3['Rank']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18448079117535185, 'fraction_numerical': 0.017497147204260176, 'mean_word_length': 3.6010498687664043, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '494033', 'license': 'MIT', 'n_tokens_mistral': 1758, 'n_tokens_neox': 1731, 'n_words': 373, 'path': 'Task_1/KPMG_VI_New_raw_data_update_final.ipynb', 'repo_name': 'Ingrid-0906/KPMG-Internship-2022', 'size': '5258'}