-
Notifications
You must be signed in to change notification settings - Fork 0
/
01_-_data_cleaning.py
136 lines (102 loc) · 4.46 KB
/
01_-_data_cleaning.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
# -*- coding: utf-8 -*-
"""
Created on Tue Jan 3 13:22:17 2023
@author: ildeniz
"""
#%%
# loading initial packages and libraries
import os
import pandas as pd
# import numpy as np
# Setting the working directory
path = os.getcwd()
real_path = os.path.realpath(__file__)
dir_path = os.path.dirname(real_path)
os.chdir(dir_path)
#%%
df_store = pd.read_csv('Data\store.csv')
""" WARNING!
`\t` part works as a/an 'tab/indent' so this generates a data loading problem.
Use '\\' or '/t' instead of '\t'
"""
df_rossmann = pd.read_csv('Data/train.csv')
# we don't need to load 'test.csv' since we won't participate into the competition
# A warning message appeared while loading 'train.csv'
# =============================================================================
# DtypeWarning: Columns (7) have mixed types. Specify dtype option on import or set low_memory=False.
# =============================================================================
# get the name of column 7
df_rossmann.columns[7]
# elements of 'StateHoliday'
df_rossmann['StateHoliday'].unique()
# 'train.csv' is consist of two types of zeros, one is in string type
# and the other one is in integer type. Since 0 stands for 'no holiday', and the
# other entries are 'str' type, it is better to change 'int' types into 'str'
# to deal with the confusion.
# #%%time
df_rossmann['StateHoliday'] = [str(int(x)) if isinstance(x, int) else x for x in df_rossmann['StateHoliday']]
df_rossmann['StateHoliday'].unique()
df_rossmann.isnull().sum()
# no missing values in 'rossmann.csv'
df_store.isnull().sum()
"""
TODO: extract the part below and turn it into a function
"""
total = df_store.isnull().sum().sort_values(ascending=False)
percent = (df_store.isnull().sum())/(df_store.isnull().count().sort_values(ascending=False))*100
missing_values=pd.concat([total,percent],keys=['Total','Percent'],axis=1)
missing_values
# Total Percent
# Promo2SinceWeek 544 48.789238
# Promo2SinceYear 544 48.789238
# PromoInterval 544 48.789238
# CompetitionOpenSinceMonth 354 31.748879
# CompetitionOpenSinceYear 354 31.748879
# CompetitionDistance 3 0.269058
df_store.info()
df_store['CompetitionDistance'].skew()
# 2.9292856455312055
import seaborn as sns
sns.kdeplot(df_store['CompetitionDistance'], shade=True)
# sns.histplot(df_store['CompetitionDistance'])
# since 'CompetitionDistance' is highly skewed, it would be better to fill
# the missing values with the median value
df_store['CompetitionDistance'].fillna(df_store['CompetitionDistance'].median(), inplace = True)
# merge store data with the main data set
df_rossmann = df_rossmann.merge(df_store,how='left',on='Store')
df_rossmann.info()
# Get list of categorical variables
cat_vars = (df_rossmann.dtypes == 'object')
object_cols = list(cat_vars[cat_vars].index)
"""
'Date' -> convert to 'datetime' while loading the data set
'StateHoliday' -> one-hot-encoding
'StoreType' -> one-hot-encoding
'Assortment' -> one-hot-encoding
'PromoInterval' -> this column has missing values, I skip this one for now
"""
# Get number of unique entries in each column with categorical data
object_nunique = list(map(lambda col: df_rossmann[col].nunique(), object_cols))
d = dict(zip(object_cols, object_nunique))
# Print number of unique entries by column, in ascending order
sorted(d.items(), key=lambda x: x[1])
# looks like it worths to apply one-hot-encoding on 'Assortment', 'StoreType', and 'StateHoliday'
# [('Assortment', 3),
# ('PromoInterval', 3),
# ('StateHoliday', 4),
# ('StoreType', 4),
# ('Date', 942)]
# one-hot encoding the categorical columns
one_hot_encoded_data = pd.get_dummies(df_rossmann[['Assortment', 'StateHoliday', 'StoreType']], columns = ['Assortment', 'StateHoliday', 'StoreType'])
# excluding catergorical columns
num_df_rossmann = df_rossmann.drop(['Assortment', 'StateHoliday', 'StoreType'], axis=1)
# merging new categorical columns with existing numerical columns
df_rossmann = pd.concat([num_df_rossmann, one_hot_encoded_data], axis=1)
# to shrink the '*.csv' file, get rid of the unused columns
df_rossmann = df_rossmann.drop(['Promo2SinceWeek',
'Promo2SinceYear',
'PromoInterval',
'CompetitionOpenSinceMonth',
'CompetitionOpenSinceYear'],axis=1)
# saving the cleaned data set as a '*.csv' file
df_rossmann.to_csv('Data/rossmann_cleaned.csv', index=False)