Data Wrangling Notebook Summary
1. Introduction
# Data Wrangling
This notebook presents different data wrangling techniques used commonly
2. Library Imports
# import required libraries
import random
import datetime
import numpy as np
import pandas as pd
from random import randrange
from sklearn import preprocessing
from [Link] import display
[Link].chained_assignment = None
3. Utilities
## Utilities
def _random_date(start,date_count):
"""This function generates a random date based on params
Args:
start (date object): the base date
date_count (int): number of dates to be generated
Returns:
list of random dates
"""
current = start
while date_count > 0:
curr = current + [Link](days=randrange(42))
yield curr
date_count-=1
def generate_sample_data(row_count=100):
"""This function generates a random transaction dataset
Args:
row_count (int): number of rows for the dataframe
Returns:
a pandas dataframe
"""
# sentinels
startDate = [Link](2016, 1, 1,13)
serial_number_sentinel = 1000
user_id_sentinel = 5001
product_id_sentinel = 101
price_sentinel = 2000
# base list of attributes
data_dict = {
'Serial No': [Link](row_count)+serial_number_sentinel,
'Date': [Link](pd.to_datetime([[Link]("%d-%m-%Y")
for x in _random_date(startDate,
row_count)]).date
),
'User ID': [Link]([Link](0,
row_count,
size=int(row_count/10)) + user_id_sentinel).tolist()*10,
'Product ID': [Link]([Link](0,
row_count,
size=int(row_count/10))+ product_id_sentinel).tolist()*10 ,
'Quantity Purchased': [Link]([Link](1,
42,
size=row_count)),
'Price': [Link]([Link]([Link](row_count)+1)*price_sentinel,
decimals=2),
'User Type':[Link]([chr([Link](97, 97 + 3 + 1))
for i in range(row_count)])
}
# introduce missing values
for index in range(int([Link](row_count))):
data_dict['Price'][[Link](data_dict['Price'] == [Link](data_dict['Price']))] =
[Link]
data_dict['User Type'][[Link](data_dict['User Type'] ==
[Link](data_dict['User Type']))] = [Link]
data_dict['Date'][[Link](data_dict['Date'] == [Link](data_dict['Date']))] =
[Link]
data_dict['Product ID'][[Link](data_dict['Product ID'] ==
[Link](data_dict['Product ID']))] = 0
data_dict['Serial No'][[Link](data_dict['Serial No'] ==
[Link](data_dict['Serial No']))] = -1
data_dict['User ID'][[Link](data_dict['User ID'] == [Link](data_dict['User
ID']))] = -101
# create data frame
df = [Link](data_dict)
return df
def describe_dataframe(df=[Link]()):
"""This function generates descriptive stats of a dataframe
Args:
df (dataframe): the dataframe to be analyzed
Returns:
None
"""
print("\n\n")
print("*"*30)
print("About the Data")
print("*"*30)
print("Number of rows::",[Link][0])
print("Number of columns::",[Link][1])
print("\n")
print("Column Names::",[Link]())
print("\n")
print("Column Data Types::\n",[Link])
print("\n")
print("Columns with Missing Values::",[Link][[Link]().any()].tolist())
print("\n")
print("Number of rows with Missing Values::",len([Link](df).any(1).nonzero()
[0].tolist()))
print("\n")
print("Sample Indices with missing data::",[Link](df).any(1).nonzero()[0].tolist()[0:5])
print("\n")
print("General Stats::")
print([Link]())
print("\n")
print("Summary Stats::")
print([Link]())
print("\n")
print("Dataframe Sample Rows::")
display([Link](5))
def cleanup_column_names(df,rename_dict={},do_inplace=True):
"""This function renames columns of a pandas dataframe
It converts column names to snake case if rename_dict is not passed.
Args:
rename_dict (dict): keys represent old column names and values point to
newer ones
do_inplace (bool): flag to update existing dataframe or return a new one
Returns:
pandas dataframe if do_inplace is set to False, None otherwise
"""
if not rename_dict:
return [Link](columns={col: [Link]().replace(' ','_')
for col in [Link]()},
inplace=do_inplace)
else:
return [Link](columns=rename_dict,inplace=do_inplace)
def expand_user_type(u_type):
"""This function maps user types to user classes
Args:
u_type (str): user type value
Returns:
(str) user_class value
"""
if u_type in ['a','b']:
return 'new'
elif u_type == 'c':
return 'existing'
elif u_type == 'd':
return 'loyal_existing'
else:
return 'error'
4. Sample Dataset Generation
## Generate a Sample Dataset
df = generate_sample_data(row_count=1000)
5. Additional Explanations
### Describe the Dataset
### Rename Columns
### Sort Rows on defined attributes
### Rearrange Columns in a Dataframe
### Filtering Columns
Using Column Index
Using Column Name
Using Column Datatype
### Filtering Rows
Select specific rows
Exclude Specific Row indices
Conditional Filtering
Offset from top of the dataframe
Offset from bottom of the dataframe
### TypeCasting/Data Type Conversion
### Apply/Map Usage
Map : Create a derived attribute using map
Apply: Using apply to get attribute ranges
Applymap: Extract week from date
### Missing Values
Drop Rows with missing dates
Fill Missing Price values with mean price
Fill Missing user_type values with value from previous row (forward fill)
Fill Missing user_type values with value from next row (backward fill)
### Duplicates
Drop Duplicate serial_no rows
Remove rows which have less than 3 attributes with non-missing data
### Encode Categoricals
One Hot Encoding using get_dummies()
Label Mapping
### Random Sampling data from DataFrame
### Normalizing Numeric Values
Normalize price values using **Min-Max Scaler**
Normalize quantity purchased values using **Robust Scaler**
### Data Summarization
Condition based aggregation
Condtion based counts
### Group By
Group By certain attributes
Group By with different aggregate functions
Group by specific aggregate functions for each attribute
Group by with multiple agg for each attribute
### Pivot Tables
### Stack a Dataframe
6. Data Wrangling Code
describe_dataframe(df)
print("Dataframe columns:\n{}".format([Link]()))
cleanup_column_names(df)
print("Dataframe columns:\n{}".format([Link]()))
display(df.sort_values(['serial_no', 'price'],
ascending=[True, False]).head())
display(df[['serial_no','date','user_id','user_type',
'product_id','quantity_purchased','price']].head())
# print 10 values from column at index 3
print([Link][:,3].values[0:10])
# print 10 values of quantity purchased
print(df.quantity_purchased.values[0:10])
# print 10 values of columns with data type float
print(df.select_dtypes(include=['float64']).values[:10,0])
display([Link][[10,501,20]])
display([Link]([0,24,51], axis=0).head())
display(df[df.quantity_purchased>25].head())
display(df[100:].head())
display(df[-10:].head())
df['date'] = pd.to_datetime([Link])
# compare dtypes of the original df with this one
print([Link])
df['user_class'] = df['user_type'].map(expand_user_type)
display([Link]())
display(df.select_dtypes(include=[[Link]]).apply(lambda x:
[Link]()-
[Link]()))
df['purchase_week'] = df[['date']].applymap(lambda dt:[Link]
if not
[Link]([Link])
else 0)
display([Link]())
df_dropped = [Link](subset=['date'])
display(df_dropped.head())
df_dropped['price'].fillna(value=[Link]([Link](),decimals=2),
inplace=True)
df_dropped['user_type'].fillna(method='ffill',inplace=True)
df_dropped['user_type'].fillna(method='bfill',inplace=True)
# sample duplicates
display(df_dropped[df_dropped.duplicated(subset=['serial_no'])].head())
print("Shape of df={}".format(df_dropped.shape))
df_dropped.drop_duplicates(subset=['serial_no'],inplace=True)
# updated dataframe
display(df_dropped.head())
print("Shape of df={}".format(df_dropped.shape))
display([Link](thresh=3).head())
print("Shape of df={}".format([Link](thresh=3).shape))
display(pd.get_dummies(df,columns=['user_type']).head())
type_map={'a':0,'b':1,'c':2,'d':3,[Link]:-1}
df['encoded_user_type'] = df.user_type.map(type_map)
display(([Link]()))
display([Link](frac=0.2, replace=True, random_state=42).head())
df_normalized = [Link]().copy()
min_max_scaler = [Link]()
np_scaled =
min_max_scaler.fit_transform(df_normalized['price'].[Link](-
1,1))
df_normalized['price'] = np_scaled.reshape(-1,1)
display(df_normalized.head())
df_normalized = [Link]().copy()
robust_scaler = [Link]()
rs_scaled =
robust_scaler.fit_transform(df_normalized['quantity_purchased'].values.r
eshape(-1,1))
df_normalized['quantity_purchased'] = rs_scaled.reshape(-1,1)
display(df_normalized.head())
print("Mean price of items purchased by user_type=a ::
{}".format(df['price'][df['user_type']=='a'].mean()))
print(df['purchase_week'].value_counts())
print([Link](['user_class'])['quantity_purchased'].sum())
display([Link](['user_class'])['quantity_purchased'].agg([[Link],
[Link],
np.count_nonzero]))
display([Link](['user_class','user_type']).agg({'price':[Link],
'quantity_purchased':[Link]}))
display([Link](['user_class','user_type']).agg({'price':{
'total_price':[Link],
'mean_price':[Link],
'variance_price':[Link],
'count':np.count_nonzero},
'quantity_purchased':[Link]}))
display(df.pivot_table(index='date', columns='user_type',
values='price',aggfunc=[Link]))
print([Link]())