Cleaning Data in Python
Adel Nehme
Content Developer @DataCamp
I) Value inconsistency
'married'
, 'Maried'
, 'UNMARRIED'
, 'not married'
..'married '
, ' married '
..II) Collapsing too many categories to few
0-20K
, 20-40K
categories ... from continuous household income data'rich'
, 'poor'
III) Making sure data is of type category
(seen in Chapter 1)
Capitalization: 'married'
, 'Married'
, 'UNMARRIED'
, 'unmarried'
..
# Get marriage status column
marriage_status = demographics['marriage_status']
marriage_status.value_counts()
unmarried 352
married 268
MARRIED 204
UNMARRIED 176
dtype: int64
# Get value counts on DataFrame
marriage_status.groupby('marriage_status').count()
household_income gender
marriage_status
MARRIED 204 204
UNMARRIED 176 176
married 268 268
unmarried 352 352
# Capitalize
marriage_status['marriage_status'] = marriage_status['marriage_status'].str.upper() marriage_status['marriage_status'].value_counts()
UNMARRIED 528
MARRIED 472
# Lowercase
marriage_status['marriage_status'] = marriage_status['marriage_status'].str.lower() marriage_status['marriage_status'].value_counts()
unmarried 528
married 472
Trailing spaces: 'married '
, 'married'
, 'unmarried'
, ' unmarried'
..
# Get marriage status column
marriage_status = demographics['marriage_status']
marriage_status.value_counts()
unmarried 352
unmarried 268
married 204
married 176
dtype: int64
# Strip all spaces
demographics = demographics['marriage_status'].str.strip()
demographics['marriage_status'].value_counts()
unmarried 528
married 472
Create categories out of data: income_group
column from income
column.
# Using qcut()
import pandas as pd
group_names = ['0-200K', '200K-500K', '500K+']
demographics['income_group'] = pd.qcut(demographics['household_income'], q = 3,
labels = group_names)
# Print income_group column
demographics[['income_group', 'household_income']]
category household_income
0 200K-500K 189243
1 500K+ 778533
..
Create categories out of data: income_group
column from income
column.
# Using cut() - create category ranges and names
ranges = [0,200000,500000,np.inf]
group_names = ['0-200K', '200K-500K', '500K+']
# Create income group column
demographics['income_group'] = pd.cut(demographics['household_income'], bins=ranges,
labels=group_names)
demographics[['income_group', 'household_income']]
category Income
0 0-200K 189243
1 500K+ 778533
Map categories to fewer ones: reducing categories in categorical column.
operating_system
column is: 'Microsoft', 'MacOS', 'IOS', 'Android', 'Linux'
operating_system
column should become: 'DesktopOS', 'MobileOS'
# Create mapping dictionary and replace
mapping = {'Microsoft':'DesktopOS', 'MacOS':'DesktopOS', 'Linux':'DesktopOS',
'IOS':'MobileOS', 'Android':'MobileOS'}
devices['operating_system'] = devices['operating_system'].replace(mapping)
devices['operating_system'].unique()
array(['DesktopOS', 'MobileOS'], dtype=object)
Cleaning Data in Python