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

Soul - Is it Real ?

Every religion believe in existence of soul but has their own theories about it. 

For instance according to most of the Abrahamic religions, immortal souls belong only to human beings. as the Catholic theologian Thomas Aquinas attributed "soul" to all organisms but argued that only human souls are immortal. 

Other religions (most notably Jainism and Hinduism) teach that all biological organisms have souls, and others teach that even non-biological entities (such as rivers and mountains) possess souls.

Scientific community is still at no conclusion about its existence instead of conducting many experiments.

Many scientists throughout the ages have given different theories and some believe it while other discard it as nuisance.

Now I want you to decide whether Soul exists or not ???

I will not give any scientific or religious theory (no fact is available in this regard) because it will just confuse you rather enlighten and even they don't have any proof about it.

So lets start , consider a car or any machine. 

It needs  fuel to run or operate. 

The car is in working condition and the fuel tank is also full yet the car wont start on its own. 

It needs a driver to start a car , run it, operate it or even make it fly !!

So in this case the driver is the soul of the car. 

Now consider body as a machine which needs food as fuel to be alive or keep it running.

So there is something or some energy (as per science) which controls our actions , conscience etc.

That is so called Soul.

When our body stops working , the soul remains unharmed and just shifts to another body as a driver will do when the car is not working.

Its Complex and at the same time Simple too.

At present science doesn't have any answer but someday it will. 

Until then its up to you to decide whether you believe it or not.

So forget about Religion or Science for the moment and just take your time and decide 

What You Think ???

Dark Web - Dark Side Of Internet

World Wide Web is divided into 2 types - Surface Web and Deep Web

Surface Web

It is the portion of web which is indexed by standard search engines which u can safely and legally access.

All websites you access from standard browsers i.e. Chrome, Mozilla, Opera etc are part of surface web which is roughly 4-5 % of the whole web database.

Deep Web ( Invisible Web or Hidden Web)

The remaining portion of the web which is almost 95% is known as Deep Web. 

It includes almost everything you can ever think of.

It goes without saying that it consists of legal and illegal contents.

Now What is Dark Web ?

Dark Web 

Ya you guessed it right !!! The Illegal portion of deep web is known as Dark Web but in actual its the darkest thing you will ever see.

It is a big black market where you can buy drugs, guns, more guns, weapons of mass destruction, slaves etc etc etc. 

You can even hire a Hit-man to assassinate anyone. 

It doesn't end here, you can watch child porn , cannibal videos and many more disturbing things. 

It also contains almost 50k extremist websites among which are ISIS and such other terrorist organizations. 

You can buy , trade or even sell stolen items and access Hidden Wiki.

How to Access

As you know you cant access it with your standard browsers, also for safety you will need to continuously change your IP address to visit dark web.

But there is a browser which will do that for you and that too free of cost. 

Thou Name is TOR Browser.

You can download it for free, just search the Google for it.

The currency used for transaction in deep web is Bit-Coin as it is very hard to trace its origin.

Happy Surfing !!!!

P.S. Surf Dark Web at your own risk :)

10 Random Facts !

• The mobile phone you use has more computing power than the computers used for the Apollo 11 moon landing. 

• The world's biggest family lives together in India: A Man with 39 Wives and 94 Children.

• The King of Hearts is the only king without a mustache on a standard playing card!

• A Lightning Bolt generates temperatures five times hotter than those found at the sun's surface.

• An average person produces about 25,000 quarts of saliva in a lifetime, enough to fill two swimming pools.

• Cat urine glows under a black-light!

• On Average right handed people live 9 years longer than their left handed counterparts.

• People say “bless you” when you sneeze because your heart stops for a millisecond.

• An average human loses about 200 head hairs per day.

• Animals can Dream and commit Suicide too.