Data Wrangling

The process of turning raw data into more appropriate format. in the end, the result should have acceptable Data Quality.

Contents included in this notebook:

• Data Exploration
• Data Cleaning
• Merging Datasets

Also this notebook will introduce you to Pandas which faciliate the manipulation task.

Datasets:

• Samsung after-sales service in Iran
• Iran’s Earthquakes through history

from os.path import join
import numpy as np
import pandas as pd
%pdb 1

Automatic pdb calling has been turned ON

Samsung after-sales service in Iran

Data Exploration

The purpose of this part is to gain a better understanding of the data. what’s data is about? what’s the structure? what’s the values? …

Let’s read Iran’s Earthquakes dataset into a DataFrame.

service_df = pd.read_csv(join('Datasets', 'service', 'samdata.csv'))
service_df

shape attribute indicate how large the resulting DataFrame is.

service_df.shape

(3136, 22)

print("pandas only displays " + str(pd.options.display.max_rows) + " rows" ) 
print("and " + str(pd.options.display.max_columns) + " columns at max by default" ) 

pandas only displays 60 rows
and None columns at max by default

The size of the dataset is not that big and beside that, it only have 2 more columns than pandas maximum displayed columns. so let’s tell pandas to show all columns for convenience.

 pd.set_option('display.max_columns', None)

use loc() and iloc() for selecting rows in specific range.

service_df.iloc[26:33:3]

sample() returns random rows to look at.

service_df.sample(13)

Dataset itself have a column for index values, let’s be sure that it’s really unique for each row, then we can condiser No column as index.

service_df['No'].is_unique

True

service_df.set_index("No", inplace=True)

head(n) returns first n rows. tail(n) returns last n rows. Default value is considered to be 5.

service_df.head()

Columns attribute returns all column names.

service_df.columns

Index(['Service_type', 'Cost_Type', 'Product_Group', 'Serial_No', 'City',
       'Product_Date', 'Receipt_Date', 'Appoint_Date', 'Complete_Date',
       'TAT01', 'TAT02', 'Job_Satus', 'Defect_Des', 'Symptom_Desc',
       'Repair_Action_Desc', 'Labor_Charge_Desc', 'Engineer',
       'Labor_Charge_Amount', 'Parts_Amount', 'Discount_Amount',
       'Total_Invoice_Amount'],
      dtype='object')

dtypes shows each column data type.

service_df.dtypes

Service_type              int64
Cost_Type                object
Product_Group            object
Serial_No                object
City                     object
Product_Date            float64
Receipt_Date             object
Appoint_Date             object
Complete_Date            object
TAT01                     int64
TAT02                     int64
Job_Satus                 int64
Defect_Des               object
Symptom_Desc             object
Repair_Action_Desc       object
Labor_Charge_Desc        object
Engineer                 object
Labor_Charge_Amount       int64
Parts_Amount            float64
Discount_Amount         float64
Total_Invoice_Amount    float64
dtype: object

info() function summarise the dadaset.

service_df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 3136 entries, 1 to 3136
Data columns (total 21 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   Service_type          3136 non-null   int64  
 1   Cost_Type             3136 non-null   object 
 2   Product_Group         3126 non-null   object 
 3   Serial_No             3127 non-null   object 
 4   City                  3136 non-null   object 
 5   Product_Date          724 non-null    float64
 6   Receipt_Date          3136 non-null   object 
 7   Appoint_Date          3136 non-null   object 
 8   Complete_Date         3136 non-null   object 
 9   TAT01                 3136 non-null   int64  
 10  TAT02                 3136 non-null   int64  
 11  Job_Satus             3136 non-null   int64  
 12  Defect_Des            3136 non-null   object 
 13  Symptom_Desc          3136 non-null   object 
 14  Repair_Action_Desc    3136 non-null   object 
 15  Labor_Charge_Desc     3136 non-null   object 
 16  Engineer              3136 non-null   object 
 17  Labor_Charge_Amount   3136 non-null   int64  
 18  Parts_Amount          3136 non-null   float64
 19  Discount_Amount       3136 non-null   float64
 20  Total_Invoice_Amount  3136 non-null   float64
dtypes: float64(4), int64(5), object(12)
memory usage: 539.0+ KB

For numerical values describe() function shows statical features of each column.

service_df.describe()

Columns can be accessed as a attribute to the dataframe. Each column in fact is a Series. we can count each repetative value by using value_count() function.

service_df.Product_Group.value_counts()

LED40                 1008
LCD-40                 529
DIGITAL CAMERA         282
LED46                  250
PRINTER - MONO LAS     151
DVD PLAYER             119
AUDIO - MP3 Player     109
HARD DISK DRIVE        102
AUDIO - HOME THEAT     102
REFRIGERATOR-SBS        68
DVD PLAYER/ RECORD      65
PLASMA-42               62
CAMCORDER               57
PLASMA-43               49
PRINTER - COLOR LA      35
MONITOR - TFT LCD       24
VACUUM CLEANER          24
TFT18                   18
WASHING MACHINE         16
PLASMA-51               15
LED55                   15
OVEN - MICROWAVE        11
LCD- 46                  8
PLASMA-50                4
AIR CONDITIONER          3
Name: Product_Group, dtype: int64

Also we can group rows by columns. and get each group size.

service_df.groupby(by =["Product_Group", "Cost_Type"]).size()

Product_Group       Cost_Type        
AIR CONDITIONER     Normal Warranty         2
                    Out of Warranty         1
AUDIO - HOME THEAT  Normal Warranty         3
                    Out of Warranty        99
AUDIO - MP3 Player  Normal Warranty        28
                    Out of Warranty        81
CAMCORDER           Normal Warranty         5
                    Out of Warranty        52
DIGITAL CAMERA      Normal Warranty       149
                    Out of Warranty       133
DVD PLAYER          Normal Warranty        53
                    Out of Warranty        66
DVD PLAYER/ RECORD  Extended Warranty      11
                    Normal Warranty        18
                    Out of Warranty        36
HARD DISK DRIVE     Extended Warranty      63
                    Normal Warranty        14
                    Out of Warranty        25
LCD- 46             Normal Warranty         5
                    Out of Warranty         3
LCD-40              Normal Warranty       522
                    Out of Warranty         7
LED40               Normal Warranty      1003
                    Out of Warranty         5
LED46               Normal Warranty       247
                    Out of Warranty         3
LED55               Normal Warranty        14
                    Out of Warranty         1
MONITOR - TFT LCD   Normal Warranty         5
                    Out of Warranty        19
OVEN - MICROWAVE    Normal Warranty         3
                    Out of Warranty         8
PLASMA-42           Normal Warranty        55
                    Out of Warranty         7
PLASMA-43           Normal Warranty        45
                    Out of Warranty         4
PLASMA-50           Normal Warranty         3
                    Out of Warranty         1
PLASMA-51           Normal Warranty        12
                    Out of Warranty         3
PRINTER - COLOR LA  Normal Warranty        14
                    Out of Warranty        21
PRINTER - MONO LAS  Normal Warranty        57
                    Out of Warranty        94
REFRIGERATOR-SBS    Normal Warranty        32
                    Out of Warranty        36
TFT18               Extended Warranty       2
                    Normal Warranty        15
                    Out of Warranty         1
VACUUM CLEANER      Normal Warranty         1
                    Out of Warranty        23
WASHING MACHINE     Out of Warranty        16
dtype: int64

In fact the result of above command is a Series which have multiple indexes. by using unstack(), we reshape the series into a Dataframe

service_df.groupby(by =["Product_Group", "Cost_Type"]).size().unstack()

let’s count NaN values in each columns.

service_df.isna().sum()

Service_type               0
Cost_Type                  0
Product_Group             10
Serial_No                  9
City                       0
Product_Date            2412
Receipt_Date               0
Appoint_Date               0
Complete_Date              0
TAT01                      0
TAT02                      0
Job_Satus                  0
Defect_Des                 0
Symptom_Desc               0
Repair_Action_Desc         0
Labor_Charge_Desc          0
Engineer                   0
Labor_Charge_Amount        0
Parts_Amount               0
Discount_Amount            0
Total_Invoice_Amount       0
dtype: int64

let’s see rows with NaN values

service_df[service_df[['Product_Group','Serial_No']].isna().any(1)]

Data Cleaning

In this part, based on what we want to do with the data, we restructure, reformat and validate the data.

for start, let’s remove columns that’s not useful for our hypothetical purpose.

• Does Serial_No really help the model to decide how much time it takes to be repaired?
• or is Product_Date values meaningful?

if the answers to these questions are No, we can drop those columns.

service_df = service_df.drop(columns=['Product_Date', 'Serial_No'])
service_df

Dataset at hand have some NaN values. what does it mean? is it that the value exist but not provided or there isn’t anything to fill that field?

possible solutions

• drop data
  • drop the whole row
  • drop the whole column
• Imputation
  • replace it by mean
  • replace it by frequency
  • adding unkown category
  • …

it’s important to have a logical reason. it’s not reasonable to fill NaN values with mean when NaN occupies half of that column.

Where’s the NaN values ?

service_df.isna().sum()

Service_type             0
Cost_Type                0
Product_Group           10
City                     0
Receipt_Date             0
Appoint_Date             0
Complete_Date            0
TAT01                    0
TAT02                    0
Job_Satus                0
Defect_Des               0
Symptom_Desc             0
Repair_Action_Desc       0
Labor_Charge_Desc        0
Engineer                 0
Labor_Charge_Amount      0
Parts_Amount             0
Discount_Amount          0
Total_Invoice_Amount     0
dtype: int64

Let’s see rows with NaN values.

service_df[pd.isna(service_df.Product_Group)]

based on the task you about to do with data after the data is prepared, decide how much droping rows is logical. if 10 rows is not a lot, we can drop them.

service_df.dropna(inplace=True)

what cities are in the dataset?

print(sorted(service_df.City.unique()))

['ABADAN', 'ABHAR', 'AHVAZ', 'AMOL', 'ARAK', 'ARDEBI', 'ARDEBIL', 'BABOL', 'BABOLSAR', 'BANDAR ABBAS', 'BANDAR ANZALI', 'BANDARABBAS', 'BARDSIR', 'BEHBAHAN', 'BIJAR', 'BIRJAND', 'BOJNORD', 'BOKAN', 'BOMEHEN', 'BOROJERD', 'CHABAHAR', 'CHALOOS', 'CHALOUS', 'CHENARAN', 'DEZFOL', 'DEZFOUL', 'DOROD', 'ESFAHAN', 'ESFAHN', 'ESFERAYN', 'ESLAM ABAD', 'ESLAM SHAHR', 'ESLAMSHAHR', 'FARDIS', 'FARHANG', 'GARMSAR', 'GHAEM SHAHR', 'GHAEMSHAHR', 'GHARCHAK', 'GOLPAYGAN', 'GONBAD', 'GONBAD KAVOS', 'GORGAN', 'HAMEDAN', 'HASHTGERD', 'HSMEDAN', 'ILAM', 'IZEH', 'JAHROM', 'JEYROFT', 'KARAJ', 'KASHAN', 'KASHMAR', 'KERMAN', 'KERMANSHAH', 'KHOMAM', 'KHOMEIN', 'KHORAM ABAD', 'KHOY', 'KhoramAbad', 'LAHIJAN', 'LANGEROD', 'LANGEROOD', 'MAHMOUD ABAD', 'MAHSHAHR', 'MALAYER', 'MANJIL', 'MASHHAD', 'MEHRSHAHR', 'MIYANEH', 'NAHAVAND', 'NIYSHABOUR', 'NOR', 'NOSHAHR', 'NOUR', 'NOUSHAHR', 'OROMIYEH', 'PARS ABAD', 'QAZVIN', 'QOM', 'RAFSANJAN', 'RASHT', 'REZVANSHAHR', 'ROBAT KARIM', 'ROBATKARIM', 'RODSAR', 'SABZEVAR', 'SALMAS', 'SARAF ZADEH', 'SARBANDAR', 'SARI', 'SAVAD KOOH', 'SAVEH', 'SAVOJBOLAGH', 'SEMNAN', 'SHAHR GHODS', 'SHAHRE GHODS', 'SHAHRE KORD', 'SHAHRGHODS', 'SHAHROD', 'SHAHROOD', 'SHAHRYAR', 'SHAZAND', 'SHIRAZ', 'SIRJAN', 'TABAS', 'TABRIZ', 'TALESH', 'TEHRAN', 'TONEKABON', 'Tehran', 'VARAMIN', 'YAZD', 'ZAHEDAN', 'ZANJAN']

Look at the values above, what you see? we have multiple ways of writing same city name.

• ‘ARDEBI’, ‘ARDEBIL’
• ‘BANDAR ABBAS’, ‘BANDARABBAS’
• ‘CHALOOS’, ‘CHALOUS’
• ‘DEZFOL’, ‘DEZFOUL’
• ‘ESFAHAN’, ‘ESFAHN’
• ‘ESLAMSHAHR’, ‘ESLAM SHAHR’
• ‘GHAEM SHAHR’, ‘GHAEMSHAHR’
• ‘HAMEDAN’, ‘HSMEDAN’
• ‘KHORAM ABAD’, ‘KhoramAbad’
• ‘LANGEROD’, ‘LANGEROOD’
• ‘NOR’, ‘NOUR’
• ‘NOSHAHR’, ‘NOUSHAHR’
• ‘ROBAT KARIM’, ‘ROBATKARIM’
• ‘SHAHR GHODS’, ‘SHAHRE GHODS’, ‘SHAHRGHODS’
• ‘SHAHROD’, ‘SHAHROOD’
• ‘TEHRAN’, ‘Tehran’

so we should transform them into a common format. it’s a Data standardization.

city_dictionary = {'ARDEBIL': 'ARDEBI', 'BANDARABBAS': 'BANDAR ABBAS', 'CHALOUS': 'CHALOOS', 'DEZFOUL': 'DEZFOL', 'ESFAHN':  'ESFAHAN', 'ESLAM SHAHR': 'ESLAMSHAHR', 'GHAEMSHAHR': 'GHAEM SHAHR', 'HAMEDAN': 'HSMEDAN', 'KhoramAbad':  'KHORAM ABAD', 'LANGEROOD': 'LANGEROD', 'NOUR': 'NOR', 'NOUSHAHR': 'NOSHAHR', 'ROBATKARIM': 'ROBAT KARIM','SHAHRE GHODS': 'SHAHR GHODS', 'SHAHRGHODS': 'SHAHR GHODS', 'SHAHROOD': 'SHAHROD', 'Tehran': 'TEHRAN'}
service_df = service_df.replace({'City': city_dictionary})

Now let’s check if dataframe have duplicate rows or not.

service_df.duplicated().any()

True

unfortunately, it does. since we’re looking at the dataframe as Analytical base table(ABT), it’s better to drop them.

service_df.drop_duplicates(inplace=True)
service_df.shape

(1971, 19)

let’s look again at dtypes. we can convert them into more sufficent types.

service_df.dtypes

Service_type              int64
Cost_Type                object
Product_Group            object
City                     object
Receipt_Date             object
Appoint_Date             object
Complete_Date            object
TAT01                     int64
TAT02                     int64
Job_Satus                 int64
Defect_Des               object
Symptom_Desc             object
Repair_Action_Desc       object
Labor_Charge_Desc        object
Engineer                 object
Labor_Charge_Amount       int64
Parts_Amount            float64
Discount_Amount         float64
Total_Invoice_Amount    float64
dtype: object

Receipt_Date, Appoint_Date and Complete_Date elements are object, let’s convert them to datetime type. Remember that we have explored the data. so we know data is using this (%m/%d/%Y) time format.

service_df[["Receipt_Date", "Appoint_Date", "Complete_Date"]] = service_df[["Receipt_Date", "Appoint_Date", "Complete_Date"]].apply(lambda _: pd.to_datetime(_, format='%m/%d/%Y'))

Now let’s see all values for each column with object dtype

for column in service_df.select_dtypes(include=[object]).columns:
    print(column, sorted(service_df[column].unique()), sep="\n\t")
    print("-------------------------")

Cost_Type
	['Extended Warranty', 'Normal Warranty', 'Out of Warranty']
-------------------------
Product_Group
	['AIR CONDITIONER ', 'AUDIO - HOME THEAT', 'AUDIO - MP3 Player', 'CAMCORDER', 'DIGITAL CAMERA', 'DVD PLAYER', 'DVD PLAYER/ RECORD', 'HARD DISK DRIVE', 'LCD- 46', 'LCD-40', 'LED40', 'LED46', 'LED55', 'MONITOR - TFT LCD ', 'OVEN - MICROWAVE', 'PLASMA-42', 'PLASMA-43', 'PLASMA-50', 'PLASMA-51', 'PRINTER - COLOR LA', 'PRINTER - MONO LAS', 'REFRIGERATOR-SBS', 'TFT18', 'VACUUM CLEANER', 'WASHING MACHINE']
-------------------------
City
	['ABADAN', 'ABHAR', 'AHVAZ', 'AMOL', 'ARAK', 'ARDEBI', 'BABOL', 'BABOLSAR', 'BANDAR ABBAS', 'BANDAR ANZALI', 'BARDSIR', 'BEHBAHAN', 'BIJAR', 'BIRJAND', 'BOJNORD', 'BOKAN', 'BOMEHEN', 'BOROJERD', 'CHABAHAR', 'CHALOOS', 'CHENARAN', 'DEZFOL', 'DOROD', 'ESFAHAN', 'ESFERAYN', 'ESLAM ABAD', 'ESLAMSHAHR', 'FARDIS', 'FARHANG', 'GARMSAR', 'GHAEM SHAHR', 'GHARCHAK', 'GOLPAYGAN', 'GONBAD', 'GONBAD KAVOS', 'GORGAN', 'HASHTGERD', 'HSMEDAN', 'ILAM', 'IZEH', 'JAHROM', 'JEYROFT', 'KARAJ', 'KASHAN', 'KASHMAR', 'KERMAN', 'KERMANSHAH', 'KHOMAM', 'KHOMEIN', 'KHORAM ABAD', 'KHOY', 'LAHIJAN', 'LANGEROD', 'MAHMOUD ABAD', 'MAHSHAHR', 'MALAYER', 'MANJIL', 'MASHHAD', 'MEHRSHAHR', 'MIYANEH', 'NAHAVAND', 'NIYSHABOUR', 'NOR', 'NOSHAHR', 'OROMIYEH', 'PARS ABAD', 'QAZVIN', 'QOM', 'RAFSANJAN', 'RASHT', 'REZVANSHAHR', 'ROBAT KARIM', 'RODSAR', 'SABZEVAR', 'SALMAS', 'SARAF ZADEH', 'SARBANDAR', 'SARI', 'SAVAD KOOH', 'SAVEH', 'SAVOJBOLAGH', 'SEMNAN', 'SHAHR GHODS', 'SHAHRE KORD', 'SHAHROD', 'SHAHRYAR', 'SHAZAND', 'SHIRAZ', 'SIRJAN', 'TABAS', 'TABRIZ', 'TALESH', 'TEHRAN', 'TONEKABON', 'VARAMIN', 'YAZD', 'ZAHEDAN', 'ZANJAN']
-------------------------
Defect_Des
	['COLD SOLDER', 'DEFORMED', 'DIRTY', 'DRY', 'LEAKING', 'OPEN', 'SHORT', 'Software Bug', 'WORN-OUT', 'WRONG PARTS']
-------------------------
Symptom_Desc
	['BEATING ON PICTURE', 'BUTTON/DIAL NOT WORKING', 'Broken External Flash', 'Cosmetic replacement(customer request)', 'DISPLAY DIM', 'Defective Date Mode', 'Defective Eye Cup', 'Defective LCD Touch Screen', 'Defective Usb Cable', 'EXCESSIVE COLOR', 'Exterior Problem/damage', 'FAULTY COUNTER/TIMER', 'FAULTY OSD', 'FIRMWARE UPAGRADE REQUESTED', 'HORIZONTAL LINE PRINTED', 'MEMORY ERASED/MISSED', 'NO AUDIO', 'NO LOADING', 'NO PICTURE/NO RASTER', 'NO POWER', 'No Power', 'OTHER FAULTY INDICATION', 'OTHER PROBLEMS', 'OUTCASE CRACK', 'PICTURE SLIDING', 'PICTURE TOO BRIGHT', 'PICTURE TOO DARK', 'PIXEL MISSING', 'POOR FOCUS', 'POWER OFF NOT WORKING', 'POWER ON, BUT NO OPERATION', 'Power On, But No Operation', 'READ ERROR', 'RESET NOT WORKING', 'SHORT BATTERY CHARGING', 'SHORT BATTERY LIFE', 'SNOWY PICTURE', 'SOUND CLIPPING', 'Test OK', 'UNIT AUTO-OFF', 'VERTICAL LINE PRINTED']
-------------------------
Repair_Action_Desc
	['ADJUSTMENT', 'CHANGE LCD', 'CHANGE OUTCASE', 'CLEANING', 'Cosmetic replacement(customer request)', "DECK ASS'Y REPAIR/REPLACEMENT", 'DECK CLEANING', 'ELECTRICAL PARTS REPLACEMENT', 'FIRMWARE UPGRADE', 'HEAD CLEANING', 'MECHANICAL PARTS REPLACEMENT', 'MECHANICAL RE-ASSEMBLING', 'No Action', 'OTHER CLEANING', 'OTHER ELECTRICAL ADJUSTMENT', 'OTHER MECHANICAL ADJUSTMENT', 'OTHER REPAIR', 'PARTS REMOVAL', 'PCB REPLACEMENT', 'PICKUP CLEANING', 'REPAIR REFUSAL', 'SCRAP', 'SET EXCHANGE', 'SOFTWARE UPGRADE', 'SOLDERING', 'TELECOM CARRIER PROBLEM']
-------------------------
Labor_Charge_Desc
	['Free Of Charge', 'Major', 'Maximum', 'Medium', 'Minimum', 'Minor']
-------------------------
Engineer
	['Tech01', 'Tech02', 'Tech03', 'Tech04', 'Tech05', 'Tech06', 'Tech07', 'Tech08', 'Tech10', 'Tech11', 'Tech12', 'Tech13', 'Tech14', 'Tech15', 'Tech16', 'Tech17', 'Tech18', 'Tech20', 'Tech21', 'Tech22', 'Tech23', 'Tech24']
-------------------------

it seems all columns with object dtype can be seen as category type.

for column in service_df.select_dtypes(include=[object]).columns:
    service_df[column] = service_df[column].astype('category')

Now let’s see all values for each column with integer dtype

for column in service_df.select_dtypes(include=['int64']).columns:
    print(column, sorted(service_df[column].unique()), sep="\n\t")
    print("-------------------------")

Service_type
	[0, 1]
-------------------------
TAT01
	[0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 26, 27, 28, 30, 31, 32, 33, 34, 36, 37, 38, 40, 42, 44, 50, 54, 55, 58, 64, 65, 67, 68, 81]
-------------------------
TAT02
	[0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 28, 30, 31, 32, 33, 34, 35, 36, 37, 38, 40, 41, 42, 43, 44, 45, 46, 47, 48, 50, 51, 52, 54, 56, 58, 60, 65, 68, 73, 75, 77, 78, 80, 81, 82, 84, 87, 88, 105, 122, 135, 153, 158, 208, 351]
-------------------------
Job_Satus
	[0, 1]
-------------------------
Labor_Charge_Amount
	[0, 50000, 70000, 85000, 120000, 145000, 150000, 170000, 180000, 200000, 215000, 240000, 250000, 300000, 350000, 360000, 400000, 420000, 540000]
-------------------------

also Service_type and Job_Satus can be seen as categorical data type.

service_df['Service_type'] = service_df['Service_type'].astype('category')
service_df['Job_Satus'] = service_df['Job_Satus'].astype('category')

Scaling and Normalization

Labor_Charge_Amount, Parts_Amount, Discount_Amount and Total_Invoice_Amount values shows how big their are. we can scale them down

service_df['Labor_Charge_Amount'] = service_df['Labor_Charge_Amount'].div(1000)
service_df['Parts_Amount'] = service_df['Parts_Amount'].div(1000)
service_df['Discount_Amount'] = service_df['Discount_Amount'].div(1000)
service_df['Total_Invoice_Amount'] = service_df['Total_Invoice_Amount'].div(1000)

service_df.head()

Dummy variables

how about Regression model? but all variables should be numerical whereas dataframe have some columns with categorical type.

to address, An indicator variable rendered as a numerical variable can be used to label categorical variables.

dummy_variable_cost_type = pd.get_dummies(service_df["Cost_Type"])
dummy_variable_cost_type

as you can see, there’s only one cell in each row with value one.

Merging Datasets

so we have the service_df Dataframe and dummy_variable_cost_type Dataframe. we want to merge them into one Dataframe. in this case we append columns from dummy_variable_cost_type into service_df.

this link is a comprehensive description to merging

service_df = pd.concat([service_df, dummy_variable_cost_type], axis=1)

Droping original “Cost_Type” column since it’s redundant.

service_df.drop('Cost_Type', axis = 1, inplace=True)
service_df

now store the prepared dataframe. and let’s say you are just interested in “HARD DISK DRIVE”s. to_csv() and to_pickle will do this.

Harddrive_df = service_df.loc[service_df['Product_Group'] == 'HARD DISK DRIVE']
Harddrive_df.to_csv('Harddrive.csv') 

Iran’s Earthquakes through history

Opening Excel Files

as another example. let’s look at this new database

earthquake_dfs = pd.read_excel(join('Datasets', 'earthquake', 'data.xls'), sheet_name=None)
earthquake_dfs.keys()

dict_keys(['Historical', '1900-1963', '1964-2000'])

data is divided between three sheet. let’s look at two of them.

earthquake_df1 = earthquake_dfs['1900-1963']
earthquake_df1.head()

earthquake_df2 = earthquake_dfs['1964-2000']
earthquake_df2.head()

second row is header too. we could have use header=[0, 1] as argument for csv_read to fix this. but now let’s do it manually.

earthquake_df1.columns = ['No', 'Year', 'Month', 'Day', 'Time', 'Lat', 'Long', 'Dept', 'mb', 'Ms', 'ref']
earthquake_df1 = earthquake_df1.iloc[1:].reset_index(drop=True)
earthquake_df2.columns = ['No', 'Year', 'Month', 'Day', 'Time', 'Lat', 'Long', 'Dept', 'mb', 'Ms', 'Mw', 'ref']
earthquake_df2 = earthquake_df2.iloc[1:].reset_index(drop=True)
earthquake_df1.head()

Concatenation

we concat these two dataframe into one. notice that how new columns are the union of two dataframes’ columns.

earthquake_df_all = pd.concat([earthquake_df1, earthquake_df2])
earthquake_df_all.head()

Imputation

Also let’s replace NaNs in mb column with mean of that column.

earthquake_df_all['mb'].fillna((earthquake_df_all['mb'].mean()), inplace=True)
earthquake_df_all.head()

Further work can be done on this dataframe.

Json files (Bonus)

In this part, we will use request to gather search results.

import requests
import json

query = {'q': 'playstation 5', 'sort': 'popularity', 'size' : 50}
response = requests.get("https://api.torob.com/v4/base-product/search/", params=query)

response_json =  response.json()
response_json.keys()

dict_keys(['results', 'count', 'max_price', 'min_price', 'next', 'previous', 'spellcheck', 'available_filters', 'filter_by_category_title', 'categories', 'parent_categories'])

response_df = pd.json_normalize(response_json['results'])
response_df.head()