Data Preprocessing

Introduction

In the real world, data is not clean. Several techniques are used as follows:

• Data cleaning: fill in missing values, smooth noisy data, identify or remove outliers, and resolve inconsistencies.
• Data integration: using multiple databases, data cubes, or files.
• Data transformation: normalization and aggregation.
• Data reduction: reducing the volume but producing the same or similar analytical results.
• Data discretization: part of data reduction, replacing numerical attributes with nominal ones.

Case study

The dataset I chose is from Kaggle. It is called Melbourne Housing Market. The following link is: https://www.kaggle.com/anthonypino/melbourne-housing-market

1. Import useful packages, read datasets and get some general information

import pandas as pd
import matplotlib.pyplot as plt
import numpy as ny
import seaborn as sns

housing = pd.read_csv('housing.csv')

housing.head()

The dataset had 21 attributes and 34857 observations in all.

housing.isnull().sum()

Suburb               0
Address              0
Rooms                0
Type                 0
Price             7610
Method               0
SellerG              0
Date                 0
Distance             1
Postcode             1
Bedroom2          8217
Bathroom          8226
Car               8728
Landsize         11810
BuildingArea     21115
YearBuilt        19306
CouncilArea          3
Lattitude         7976
Longtitude        7976
Regionname           3
Propertycount        3
dtype: int64

housing.isnull().sum()/len(housing)*100

Suburb            0.000000
Address           0.000000
Rooms             0.000000
Type              0.000000
Price            21.832057
Method            0.000000
SellerG           0.000000
Date              0.000000
Distance          0.002869
Postcode          0.002869
Bedroom2         23.573457
Bathroom         23.599277
Car              25.039447
Landsize         33.881286
BuildingArea     60.576068
YearBuilt        55.386293
CouncilArea       0.008607
Lattitude        22.882061
Longtitude       22.882061
Regionname        0.008607
Propertycount     0.008607
dtype: float64

It shows missing value percentage for each column. We can also get the percentage of all the missing values.

missing = housing.isnull().sum().sum()
all = housing.isnull().count().sum()
missing/all*100

13.794455441757275

There are a lot of missing values in the dataset, especially in Landsize, BuildingArea, and YearBuilt column. 13% of the data are missing. That is not bad.

You can also visualize missing values

plt.figure(figsize=(10,3))
sns.set(font_scale=1.2)
sns.heatmap(housing.isnull(),yticklabels = False, cbar = False, cmap = 'plasma')

<matplotlib.axes._subplots.AxesSubplot at 0x170625eb978>

2. Obtain descriptive statistics of some attributes.

Firstly, we need to understand the data type. Typically, they are:

1. Categorical data
   • Nominal: variables are variables that have two or more categories, but which do not have an intrinsic order. Examples: Gender, weather, room type
   • Ordinal: variables are variables that have two or more categories just like nominal variables only the categories can also be ordered or ranked. Examples: Class, Ranking
2. Numeric data
   • Interval
   • Ratio

Measures of the central tendency: Mean, Median, Mode

• Mean: Average; Susceptible to outliers.
• Median: Better in skewed data
• Mode: the most frequent score. Best for nominal data
• Importance: right-skewed data: median > mean. left-skewed data: median < mean

The more skewed the distribution, the greater the difference between the median and mean, and the greater emphasis should be placed on using the median as opposed to the mean.

Measures of the Dispersion: Variance/Standard Deviation, Range, interquantile range

• Variance/Standatd Deviation
• Range: Max-min,
• Interquartile: Q3 - Q1. Interquartile range is where the middle 50% data locates.

housing.describe()
#'mean of housing price',housing['Price'].mean()

Here, we choose price and number of rooms as two attributes we are interested in.

Also, we may need median, mode, range, quartiles for these two attributes.

'median of housing price',housing['Price'].median()

median of housing price 870000.0





('quartiles for housing price', 870000.0)

'mode of housing price',housing['Price'].mode()[0],housing['Price'].mode()[1]

('mode of housing price', 600000.0, 1100000.0)

'mode of housing price',[x for x in housing['Price'].mode()]

('mode of housing price', [600000.0, 1100000.0])

'range of housing price',housing['Price'].max()-housing['Price'].min()

('range of housing price', 11115000.0)

'quartiles for housing price',housing['Price'].quantile([0.25,0.75])

('quartiles for housing price', 0.25     635000.0
 0.75    1295000.0
 Name: Price, dtype: float64)

'standard deviation for housing price',housing['Price'].std()

('standard deviation for housing price', 641467.1301046001)

Visualize the distributions of numeric value

Histogram, Boxplot

plt.figure(figsize=[15,6])
housing['Price'].hist(bins=30)

<matplotlib.axes._subplots.AxesSubplot at 0x26f1caabfd0>

plt.figure(figsize=[10,8])
sns.distplot(housing['Price'].dropna(),bins=30)

<matplotlib.axes._subplots.AxesSubplot at 0x17062335128>

sns.boxplot(y='Price',data=housing)

<matplotlib.axes._subplots.AxesSubplot at 0x26f1ca8fe80>

Then apply the same techniques to Rooms column.

'median of room number',housing['Rooms'].median()

('median of room number', 3.0)

'mode of room number',housing['Rooms'].mode()[0]

('mode of room number', 3)

'range of room number',housing['Rooms'].max()-housing['Rooms'].min()

('range of room number', 15)

'quartiles for room number',housing['Rooms'].quantile([0.25,0.75])

('quartiles for room number', 0.25    2.0
 0.75    4.0
 Name: Rooms, dtype: float64)

plt.figure(figsize=[15,6])
housing['Rooms'].hist(bins=30)

<matplotlib.axes._subplots.AxesSubplot at 0x26f1d1fc550>

sns.countplot(housing['Rooms'])

<matplotlib.axes._subplots.AxesSubplot at 0x26f1d308c88>

sns.boxplot(y=housing['Rooms'])

<matplotlib.axes._subplots.AxesSubplot at 0x26f1d316e48>

3. Potential problem of data quality

1. Missing data

There are several methods to handle missing values. In summary, they are:

• Ignore the tuple: usually done when class label is missing.
• Use the attribute mean (or majority nominal value) to fill in the missing value.
• Use the attribute mean (or majority nominal value) for all samples belonging to the same class.
• Predict the missing value by using a learning algorithm: consider the attribute with the missing value as a dependent (class) variable and run a learning algorithm (usually Bayes or decision tree) to predict the missing value.

Reference: http://www.cs.ccsu.edu/~markov/ccsu_courses/datamining-3.html

To simply, we just remove all the observations which have missing values. Advanced methods will be studied in the future.

housing = housing.dropna()

housing.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 8887 entries, 2 to 34856
Data columns (total 21 columns):
Suburb           8887 non-null object
Address          8887 non-null object
Rooms            8887 non-null int64
Type             8887 non-null object
Price            8887 non-null float64
Method           8887 non-null object
SellerG          8887 non-null object
Date             8887 non-null object
Distance         8887 non-null float64
Postcode         8887 non-null float64
Bedroom2         8887 non-null float64
Bathroom         8887 non-null float64
Car              8887 non-null float64
Landsize         8887 non-null float64
BuildingArea     8887 non-null float64
YearBuilt        8887 non-null float64
CouncilArea      8887 non-null object
Lattitude        8887 non-null float64
Longtitude       8887 non-null float64
Regionname       8887 non-null object
Propertycount    8887 non-null float64
dtypes: float64(12), int64(1), object(8)
memory usage: 1.5+ MB

Now, we drop all the missing values. Dataset is clean.

2. Outlier data

There are several methods to handle missing values. In summary, they are:

• Binning
  • Sort the attribute values and partition them into bins (see “Unsupervised discretization” below);
  • Then smooth by bin means, bin median, or bin boundaries.
• Clustering: group values in clusters and then detect and remove outliers (automatic or manual)
• Regression: smooth by fitting the data into regression functions.
• Univariate method: Boxplot

housing.describe()

housing[housing['BuildingArea']==0]

housing[housing['Rooms']==0]

BuildingArea is suspicious because of zero values. How could they be zero since they all have rooms? Some mistakes might be taken in the data entry. We choose to remove these observations.

housing = housing[housing['BuildingArea']!=0]

housing.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 8842 entries, 2 to 34856
Data columns (total 21 columns):
Suburb           8842 non-null object
Address          8842 non-null object
Rooms            8842 non-null int64
Type             8842 non-null object
Price            8842 non-null float64
Method           8842 non-null object
SellerG          8842 non-null object
Date             8842 non-null object
Distance         8842 non-null float64
Postcode         8842 non-null float64
Bedroom2         8842 non-null float64
Bathroom         8842 non-null float64
Car              8842 non-null float64
Landsize         8842 non-null float64
BuildingArea     8842 non-null float64
YearBuilt        8842 non-null float64
CouncilArea      8842 non-null object
Lattitude        8842 non-null float64
Longtitude       8842 non-null float64
Regionname       8842 non-null object
Propertycount    8842 non-null float64
dtypes: float64(12), int64(1), object(8)
memory usage: 1.5+ MB

3. Data Reduction

Data reduction is necessary when there are too much redundant information in the dataset. In summary, common methods are:

• Reducing the number of attributes
  • Data cube aggregation: applying roll-up, slice or dice operations.
  • Removing irrelevant attributes: attribute selection (filtering and wrapper methods), searching the attribute space (see Lecture 5: Attribute-oriented analysis).
  • Principle component analysis (numeric attributes only): searching for a lower dimensional space that can best represent the data
• Reducing the number of attribute values
  • Binning (histograms): reducing the number of attributes by grouping them into intervals (bins).
  • Clustering: grouping values in clusters.
  • Aggregation or generalization
• Reducing the number of tuples
  • Sampling

Reference: http://www.cs.ccsu.edu/~markov/ccsu_courses/datamining-3.html

In this case, Address and sellerG (real estate agent) seems to be unnecessary currently. In the future, natural language processing might help include in the model. Also, bedroom2 is scraped from different sources. We chose to ignore this column.

housing = housing.drop(['Address','SellerG'],axis = 1)    #axis=1 indicate the column name

4. Feature Engineering

Feature engineering is important to dig more inforamtion from row data. We just translate some attributes into new ones that make more sense in the reality. For example, in the case, we would translate YearBuilt into Age of property. Date sold can also be split into three attributes: Year, Month and Weekday.

Let’s do it!

housing['Age'] = 2018-housing['YearBuilt']

housing[housing['Age']<0]

** Notice Here: There is one property to be built in 2019 **

We are using groupby function to see the average price VS age of property

plt.figure(figsize=(10,6))
housing.groupby('Age')['Price'].mean().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x17065f3c128>

Here, we found a age of 800. It could be true, but it seems an outlier. We choose to take it out.

housing[housing['Age']>400]

housing = housing[housing['Age']<500]

Then we visualize again. It should be good now.

plt.figure(figsize=(10,6))
ax=housing.groupby('Age')['Price'].mean().plot()
ax.set_ylabel('Price')

Text(0,0.5,'Price')

Interesting Finding!

Then we transform Date column!

housing['Date']=pd.to_datetime(housing['Date'],infer_datetime_format=True)  ## transform Date column to datetime object

housing['Year']=pd.DatetimeIndex(housing['Date']).year
housing['Month']=pd.DatetimeIndex(housing['Date']).month
housing['Weekday']=pd.DatetimeIndex(housing['Date']).weekday

housing.head()

sns.factorplot(y='Price',x='Month',kind='bar',data=housing)

<seaborn.axisgrid.FacetGrid at 0x170620c41d0>

sns.factorplot(y='Price',x='Weekday',kind='bar',data=housing) 

<seaborn.axisgrid.FacetGrid at 0x17066119d30>

sns.factorplot(y='Price',x='Year',kind='bar',data=housing)

<seaborn.axisgrid.FacetGrid at 0x170627f4358>

Conclusion

1. Data preprocessing is very important.
2. More advanced method in outliers detection and missing values handling is to be studied in the future
   • I am really not sure what outliers should be taken into account. Price has many extreme values from boxplot. Should we take all of them off?
   • Missing values had better be taken care of carefully. Here, I just drop them.
3. Visualization will be introduced next time.

Thank you!