Mapping Data KINO

by Laksmi Setiawati

Problem

Statement

Kino has three datasets about their products. Each dataset has different columns but the product name value and product code value are the same. Kino wants to merge the datasets so that it can be used for the analysis process.

Merging Datasets

Steps doing data merge

01. Understanding Dataset

Dataset 1 sample

Dataset 1 consist 3 columns, Product Brand, Product Name and Product Code. Below description of each columns.

Product Brand : brand name of the product

Product Name : name of the product

Product Code : code of the product that consist brand code, product code, product packaging code, etc

Take a look at product ELLIPS CG MONDAY 50ML BTL -1403.

01. Understanding Dataset

Dataset 2 sample

Dataset 2 consist 2 columns, Material Description and Material. Below description of each columns.

Material Description : name of the product

Material : code of the product that consist brand code, product code, product packaging code, etc

Take a look at product ELLIPS CG MONDAY 50ML BTL - PH.

01. Understanding Dataset

Dataset 3 sample

Dataset 3 consist 2 columns, Product Code and Product Name. Below description of each columns.

Product Code : code of the product that consist brand code, product code, product packaging code, etc

Product Name : name of the product

Take a look at product ELLIPS COLOGNE GEL MONDAY 50ML.

01. Understanding Dataset

​

Value of product name of each datasets are similar. They are Ellips Cologne Gel with varian MONDAY.

Then take a look of each product code value.

Dataset 1 : FG30408.126.0050.C�Dataset 2 : FG20408.126.0502.C�Dataset 3 : FG20408.126.0502.C

They have similar value. We could use it as relational key.

02. Split Product Code

Check the similarity using python code

Dataset 1 column product code, dataset 2 column material, and dataset 3 column product, have same value and some of row have similar value. We’ll called them as ‘Product Code’. Because Product Code consist code of brand, code of packaging, code of the product, etc, we need to check the similarity of them. Below is sample of python code:

data_source_1['brand_code'] = data_source_1['product_code'].apply(lambda x: x[3:5])

data_source_2['brand_code'] = data_source_2['material'].apply(lambda x: x[3:5])

data_source_3['brand_code'] = data_source_3['product_code'].apply(lambda x: x[4:6])

03. Create Relational Key

Creating key for join using python code

Based on splitted code in previous step, we’ll create relational unique code to join each dataset. Below is sample of python code:

data_source_1['key'] = data_source_1['brand_code'] + data_source_1['size']

data_source_2['key'] = data_source_2['brand_code'] + data_source_2['size']

data_source_3['key'] = data_source_3['brand_code'] + data_source_3['size']

04. Merge & Mapping

Based on Key using python code

Below is sample of python code:

for index1, row1 in data_mapping.iterrows():

found_match = False

for index2, row2 in data_source_3.iterrows():

if row1['key'] in row2['key']:

data_mapping.at[index1, 'ds3_name'] = row2['product_name']

data_mapping.at[index1, 'ds3_code'] = row2['product_code']

found_match = True

break

04. Merge & Mapping

Based on Key using python code

Below is sample of python code:

for index, row in data_source_3.iterrows():

if row['key'] not in data_mapping['key'].values:

data_mapping.loc[data_mapping.index[-1] + 1, 'key'] = row['key']

data_mapping.loc[data_mapping.index[-1], 'ds3_name'] = row['product_name']

data_mapping.loc[data_mapping.index[-1], 'ds3_code'] = row['product_code']

data_mapping.loc[data_mapping.index[-1], 'ds1_name'] = ""

data_mapping.loc[data_mapping.index[-1], 'ds1_code'] = ""

data_mapping.loc[data_mapping.index[-1], 'ds2_name'] = ""

data_mapping.loc[data_mapping.index[-1], 'ds2_code'] = ""

05. Merged Dataset

Based on Key using python code

Below is sample of merged dataset:

Thanks

Let’s work together

laksmise@gmail.com

laksmisetiawati.github.io

linkedin.com/in/laksmi-setiawati

Please keep this slide for attribution

CREDITS: This presentation template was created by Slidesgo, and includes icons by Flaticon and infographics & images by Freepik