aut

# importing libraries

import pandas as pd

import numpy as np

import math

import datetime

from datetime import date

# marked true if 2x data and false if 7x data

data_2x = True

# reading files

df_product_file = pd.read_excel("Product Data.xlsx", engine="openpyxl")

df_audit_log = pd.read_excel("Audit Log.xlsx", engine="openpyxl")

df_website_count_2x = pd.read_excel("Website Count.xlsx", sheet_name="All Website", engine="openpyxl")

df_website_count_7x = pd.read_excel("Website Count.xlsx", sheet_name="100 websites_7x", engine="openpyxl")

df_master_file_continue = pd.read_excel("Master File.xlsx", sheet_name="Consolidate File", engine="openpyxl")

# Removing duplicates from master file and resetting index

df_master_file_continue.drop_duplicates(subset=["PRODUCT_CODE"], inplace = True)

df_master_file_continue.reset_index(drop=True, inplace=True)

# creating a dummy dataframe for reference purpose

df_dummy = pd.DataFrame(columns = df_product_file.columns) 

# Record no.

record_no = True

# list of required sequential numbers

lst = list(range(1, df_product_file.shape[0] + 1))

# converting the list to dataframe

df_dummy["Record no."] = lst

# checking if the two columns match

if (df_dummy["Record no."] != df_product_file['Record no.']).sum():

    record_no = False

# punching the result

df_audit_log.iloc[0, 1] = record_no

# Week

week = True

# need to change for each financial year

start_day = date(2020, 3, 29)

# today's date

current_date = datetime.datetime.now()

today = date(current_date.year,current_date.month,  current_date.day)

delta = today - start_day

weeks = (delta.days) / 7

weeks = math.ceil(weeks)

if (df_product_file['Week'] != weeks).sum():

    week = False

# punching the result

df_audit_log.iloc[1, 1] = week

# Country

country = True

# checking if country is standardaized

if data_2x:

    if (~ df_product_file['Country'].isin(df_website_count_2x['Country'])).sum():

        country = False

else:

    if (~ df_product_file['Country'].isin(df_website_count_7x['Countyr'])).sum():

        country = False

# punching the result

df_audit_log.iloc[2, 1] = country

# Website

website = True

# checking if website is standardaized

if data_2x:

    if (~ df_product_file['Website'].isin(df_website_count_2x['Website'])).sum():

        website = False

else:

    if (~ df_product_file['Website'].isin(df_website_count_7x['Website Name'])).sum():

        website = False

# punching the result

df_audit_log.iloc[3, 1] = website

# Genre

genre = True

# listing the junk characters to be searched

junk_characters = '&amp|#39|<|>|<>'

if df_product_file['Genre'].str.contains(junk_characters, case=False).sum():

    genre = False

# punching the result

df_audit_log.iloc[4, 1] = genre

# Genre_NPD

genre_npd = True

# lokking up for values for genre from master file

df_dummy["Genre_NPD"] =     df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["GENRE"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Genre_NPD"] = np.where(df_dummy["Genre_NPD"].isna() & df_product_file['Website'].isin(["Stadia"]),  

                                df_product_file['Genre_NPD'], df_dummy["Genre_NPD"])

# checking if the calculated column matches with actual column

if (df_dummy["Genre_NPD"] != df_product_file['Genre_NPD']).sum():

    genre_npd = False

# cheking if there are no blanks

elif df_product_file['Genre_NPD'].isnull().sum():

    genre_npd = False    

# punching the result

df_audit_log.iloc[5, 1] = genre_npd

# Sub-Genre

sub_genre = True

# veryfing if all records are blank

if df_product_file['Sub-Genre'].notnull().sum():

    sub_genre = False

# punching the result

df_audit_log.iloc[6, 1] = sub_genre

# Sub-Genre_NPD

sub_genre_npd = True

# lokking up for values for sub genre from master file

df_dummy["Sub-Genre_NPD"] =     df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["SUB_GENRE"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Sub-Genre_NPD"] = np.where(df_dummy["Sub-Genre_NPD"].isna() & df_product_file['Website'].isin(["Stadia"]),  

                                df_product_file['Sub-Genre_NPD'], df_dummy["Sub-Genre_NPD"])

# replacing zeroes with blanks if any

df_dummy["Sub-Genre_NPD"] = np.where(df_dummy["Sub-Genre_NPD"] == 0, np.nan, df_dummy["Sub-Genre_NPD"])

# replacing NaNs with blanks

df_dummy["Sub-Genre_NPD"].fillna('', inplace=True)

df_product_file['Sub-Genre_NPD'].fillna('', inplace=True)

# checking if the calculated column matches with actual column

if (df_dummy["Sub-Genre_NPD"] != df_product_file['Sub-Genre_NPD']).sum():

    sub_genre_npd = False 

# punching the result

df_audit_log.iloc[7, 1] = sub_genre_npd

# Product Name (as on website)

product_name = True

# listing the junk characters to be searched

junk_characters = 'true|false|#N/A'

# cheking if there are no blanks

if df_product_file['Product Name (as on website)'].isnull().sum():

    product_name = False

# checking if product name contains any junk words

elif df_product_file['Product Name (as on website)'].str.contains(junk_characters, case=False).sum():

    product_name = False    

    

# punching the result

df_audit_log.iloc[8, 1] = product_name

# Product_Code

product_code = True

# creating a key in product file

df_product_file["Key"] = df_product_file['Title Name (Standard)'].str.lower() + df_product_file['Platform'] + df_product_file['Title_Type']

# creating a key in master file

df_master_file_continue["Key"] = df_master_file_continue["Title"].str.lower() + df_master_file_continue["PLATFORM"] + df_master_file_continue["Product_Type"]

# lokking up for values for product code from master file

df_dummy["Product_Code"] = df_product_file.merge(df_master_file_continue, on="Key", how="left")["PRODUCT_CODE"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Product_Code"] = np.where(df_dummy["Product_Code"].isna() & df_product_file['Website'].isin(["Stadia"]),  

                                df_product_file['Product_Code'], df_dummy["Product_Code"])

# checking if the calculated column matches with actual column

if (df_dummy["Product_Code"] != df_product_file['Product_Code']).sum():

    product_code = False

# cheking if there are no blanks

elif df_product_file['Product_Code'].isnull().sum():

    product_code = False    

# punching the result

df_audit_log.iloc[9, 1] = product_code

# Title Name (Standard)

title_name = True

# lokking up for values for title name from master file

df_dummy["Title Name (Standard)"] =     df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["Title"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Title Name (Standard)"] =             np.where(df_dummy["Title Name (Standard)"].isna() & df_product_file['Website'].isin(["Stadia"]),  

            df_product_file['Title Name (Standard)'], df_dummy["Title Name (Standard)"])

# checking if the calculated column matches with actual column

if (df_dummy["Title Name (Standard)"] != df_product_file['Title Name (Standard)']).sum():

    title_name = False

# cheking if there are no blanks

elif df_product_file['Title Name (Standard)'].isnull().sum():

    title_name = False    

# punching the result

df_audit_log.iloc[10, 1] = title_name

# Publisher

publisher = True

# lokking up for values for publisher from master file

df_dummy["Publisher"] = df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["PRIMARY_PUBLISHER"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Publisher"] =             np.where(df_dummy["Publisher"].isna() & df_product_file['Website'].isin(["Stadia"]),  

            df_product_file['Publisher'], df_dummy["Publisher"])

# checking if the calculated column matches with actual column

if (df_dummy["Publisher"] != df_product_file['Publisher']).sum():

    publisher = False

# cheking if there are no blanks

elif df_product_file['Publisher'].isnull().sum():

    publisher = False

# punching the result

df_audit_log.iloc[11, 1] = publisher

# Edition

edition = True

# lokking up for values for edition from master file

df_dummy["Edition"] =     df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["Edition_y"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Edition"] =             np.where(df_dummy["Edition"].isna() & df_product_file['Website'].isin(["Stadia"]),  

            df_product_file['Edition'], df_dummy["Edition"])

# checking if the calculated column matches with actual column

if (df_dummy["Edition"] != df_product_file['Edition']).sum():

    edition = False

# cheking if there are no blanks

elif df_product_file['Edition'].isnull().sum():

    edition = False

# punching the result

df_audit_log.iloc[12, 1] = edition

# Platform

platform = True

# lokking up for values for platform from master file

df_dummy["Platform"] =     df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["PLATFORM"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Platform"] =             np.where(df_dummy["Platform"].isna() & df_product_file['Website'].isin(["Stadia"]),  

            df_product_file['Platform'], df_dummy["Platform"])

# checking if platform contains standardized values

if (~df_product_file['Platform'].isin(["PC", "NS", "GSD", "PS4", "PS5", "XB1", "XBX"])).sum():

    platform = False

# checking if the calculated column matches with actual column

elif (df_dummy["Platform"] != df_product_file['Platform']).sum():

    platform = False

# cheking if there are no blanks

elif df_product_file['Platform'].isnull().sum():

    platform = False

# punching the result

df_audit_log.iloc[13, 1] = platform

# Title_Type

title_type = True

# lokking up for values for platform from master file

df_dummy["Title_Type"] = df_product_file.merge(df_master_file_continue, left_on="Product_Code", right_on="PRODUCT_CODE", how="left")["Product_Type"]

# adjusting the values for stadia for which there is no product code in master file

df_dummy["Title_Type"] =             np.where(df_dummy["Title_Type"].isna() & df_product_file['Website'].isin(["Stadia"]),  

            df_product_file['Title_Type'], df_dummy["Title_Type"])

# checking if platform contains standardized values

if (~df_product_file['Title_Type'].isin(["Digital", "Physical"])).sum():

    title_type = False

# checking if the calculated column matches with actual column

elif (df_dummy["Title_Type"] != df_product_file['Title_Type']).sum():

    title_type = False

# cheking if there are no blanks

elif df_product_file['Title_Type'].isnull().sum():

    title_type = False

# punching the result

df_audit_log.iloc[14, 1] = title_type

# 1-1 Product Code Mapping

map_genre_npd = True

map_sub_genre_npd = True

map_title_name = True

map_publisher = True

map_edition = True

map_platform = True

map_title_type = True

# checking 1-1 mapping of product code and genre

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Genre_NPD'] != 1).sum():

    map_genre_npd = False

    

# checking 1-1 mapping of product code and sub genre

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Sub-Genre_NPD'] != 1).sum():

    map_sub_genre_npd = False

# checking 1-1 mapping of product code and title name

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Title Name (Standard)'] != 1).sum():

    map_title_name = False

    

# checking 1-1 mapping of product code and publisher

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Publisher'] != 1).sum():

    map_publisher = False

    

# checking 1-1 mapping of product code and edition

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Edition'] != 1).sum():

    map_edition = False

    

# checking 1-1 mapping of product code and platform

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Platform'] != 1).sum():

    map_platform = False

# checking 1-1 mapping of product code and title type

if (df_product_file.groupby(['Product_Code']).nunique(dropna = False)['Title_Type'] != 1).sum():

    map_title_type = False    

# punching the result

df_audit_log.iloc[15, 1] = map_genre_npd

df_audit_log.iloc[16, 1] = map_sub_genre_npd

df_audit_log.iloc[17, 1] = map_title_name

df_audit_log.iloc[18, 1] = map_publisher

df_audit_log.iloc[19, 1] = map_edition

df_audit_log.iloc[20, 1] = map_platform

df_audit_log.iloc[21, 1] = map_title_type