Consulting - Quality
Difference makes the DIFFERENCE
HANDLING MISSING DATA
CW - Overall Understanding
Dealing with missing data with Numpy¶
In [ ]:
import numpy as np
import pandas as pd
import seaborn as sns
In [ ]:
x = np.array([1,2,3,4,5,6,7,8,9,10])
In [ ]:
x.sum()
Out[ ]:
In [ ]:
print(x.dtype)
In [ ]:
x = np.array([1,2,3,4,5,6.2,7,8,9,10])
In [ ]:
x.dtype
Out[ ]:
In [ ]:
x.sum()
Out[ ]:
In [ ]:
x = np.array([1,2,3,4,5,'--',7,8,9,10])
cannot perform reduce with flexible type¶
- reduce operation means that the function sum() is going to reduce the given numbers to a single number
- fexlible type means that the given dataset contains different datatypes and not single type or similar type
- and is not able to perform such function because of different data types present in the dataset
In [ ]:
x.sum()
In [ ]:
x.dtype
Out[ ]:
In [ ]:
# Using NONE seems to be good enough unlike the previous dataset,
# but still the same error occurs when we perform any such operation
x = np.array([1,2,3,4,5,None,7,8,9,10])
In [ ]:
x.sum()
In [ ]:
# even now, we get the same error and cannot perform any operations
x = np.array([1,2,3, np.nan ,7])
In [ ]:
x.sum()
Out[ ]:
In [ ]:
25 * np.nan
Out[ ]:
- take a boolean array and map it on to the original array, so that it returns only True values and not False values
- the number of True and False elements should be identical to the position of valid and missing values in the dataset.
- the above method can be applied to an array of any dimension
In [ ]:
x = np.array([1,2,3, np.nan ,7])
In [ ]:
# x = np.array([1, 2, 3, np.nan, 7])
x_b = np.array([True, True, True, False, True])
In [ ]:
x[x_b]
Out[ ]:
In [ ]:
x[x_b].sum()
Out[ ]:
In [ ]:
# the following boolean array doesnot work as the mapping is not exact and returns nan
# x = np.array([1, 2, 3, np.nan, 7])
x_b = np.array([True, False, True, True, True])
In [ ]:
x[x_b].sum()
Out[ ]:
In [ ]:
x = np.array([1,2,3, np.nan ,7])
In [ ]:
# x = np.array([1, 2, 3, np.nan, 7])
x_b = np.array([True, True, True, False, True])
In [ ]:
x[x_b].mean()
Out[ ]:
Find the missing elements in a sequence¶
In [ ]:
arr = [1,2,4,5,6,7,9,10, 12, 25, 30] # serial numbers or numbers in sequence
missing = [] # empty array to gather missing elements
for i in range(arr[0], arr[-1]+1): # run a loop with a range of numbers in arr
if i not in arr: # if number in range is not in given array
missing.append(i) # append that value to empty array
print(missing) # print the elements that are missing
alternative method - to find missing elements in a sequence¶
In [ ]:
arr = [1,4,5,7,9,10]
missing_values = [i for i in range(arr[0], arr[-1]+1) if i not in arr]
print(missing_values)
In [ ]:
arr = np.array([1,2,3, np.nan ,7])
In [ ]:
len(arr)
Out[ ]:
using isnan() from Numpy¶
In [ ]:
arr = np.array([1, 2, 3, np.nan, 7, 10, np.nan, 15])
bl_array = []
for i in arr:
if np.isnan(i):
bl_array.append(False)
else:
bl_array.append(True)
print(bl_array)
using isnumeric() from Numpy¶
In [ ]:
arr = np.array([1, np.nan, None, 'missing', 3, '--' ,7])
In [ ]:
import numbers
take close look, np.nan is not counted as non-number¶
In [ ]:
bl_array = []
for i in arr:
# if np.char.isnumeric(i):
# if type(i) == int or type(i) == float:
if isinstance(i, numbers.Number):
bl_array.append(True)
# elsif np.isnan(i):
# bl_array.append(False)
else:
bl_array.append(False)
print(bl_array)
take close look, np.nan is not counted as non-number¶
In [ ]:
arr = np.array([1, np.nan, None, 'missing', 3, '--' ,7])
bl_array = []
for i in arr:
if type(i) == int or type(i) == float:
bl_array.append(True)
else:
bl_array.append(False)
print(bl_array)
to work with mask functionality¶
In [ ]:
arr = np.array([1, 2, None, 'missing', 3, '--' ,7])
bl_array = []
for i in arr:
if type(i) == int or type(i) == float:
bl_array.append(0)
else:
bl_array.append(1)
print(bl_array)
In [ ]:
m_x = np.ma.masked_array(arr, mask = bl_array)
In [ ]:
m_x
Out[ ]:
In [ ]:
print(m_x)
In [ ]:
m_x.sum()
Out[ ]:
In [ ]:
m_x.mean()
Out[ ]:
# Dealing with missing data with Pandas¶
creating sample dataset with of the type of dictionary¶
In [ ]:
room = {"room number": [101, 102, 103, 104, 105, '' , 107, 108, 109, 110],
"num_students": [1, 2, "", 4, 'empty', 2, 3, 4, 'na', 3],
"Department": ["Civil", "Civil", "", "Electrical", "n/a", "CS", "Metallurgy", "na", "Chemical", "Civil"],
"Occupied": ['y', 'n', 'y', ' ', 'y', '--', 'n', 'na', '--', 'y']}
df = pd.DataFrame(room)
In [ ]:
df
Out[ ]:
In [ ]:
df.dtypes
Out[ ]:
In [ ]:
df['room number'] = pd.to_numeric(df['room number'])
In [ ]:
df.dtypes
Out[ ]:
operations are much faster when the datatype is of int or float rather than of object type - follow the below example¶
In [ ]:
%timeit np.arange(1000000, dtype="int").sum()
In [ ]:
%timeit np.arange(1000000, dtype="object").sum()
In [ ]:
df['room number'].isnull()
Out[ ]:
adds all the true values to give the count of null values¶
In [ ]:
df['room number'].isnull().sum()
Out[ ]:
In [ ]:
df.isnull()
Out[ ]:
In [ ]:
df.isnull().sum()
Out[ ]:
In [ ]:
rooms = pd.read_csv('/content/rooms.csv')
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms.dtypes
Out[ ]:
In [ ]:
rooms.isnull().sum()
Out[ ]:
In [ ]:
missing_values = ['na', 'n/a', 'empty', 'NA', '--']
In [ ]:
rooms = pd.read_csv('/content/rooms.csv', na_values = missing_values)
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms.isnull()
Out[ ]:
In [ ]:
rooms.department.unique()
Out[ ]:
In [ ]:
rooms['room_number'].count()
Out[ ]:
Filling the missing values¶
to fill all the missing values or null values with string "N"¶
In [ ]:
rooms['occupied'].fillna("n", inplace = True)
In [ ]:
rooms
Out[ ]:
In [ ]:
def Convert_Y_to_True(v):
if v == "y":
return True
else:
return False
In [ ]:
rooms['occupied'] = rooms['occupied'].apply(Convert_Y_to_True)
In [ ]:
rooms
Out[ ]:
In [ ]:
def Convert_Y_to_True(v):
if v == True:
return "y"
else:
return "n"
In [ ]:
rooms['occupied'] = rooms['occupied'].apply(Convert_Y_to_True)
In [ ]:
rooms
Out[ ]:
In [ ]:
# lambda x: 'found' if x == "Tie" else "not found"
rooms['occupied'] = rooms['occupied'].apply(lambda x: True if x == "y" else "False")
In [ ]:
rooms
Out[ ]:
to fill the missing value with the value above the null value - with forward fill - ffill, also called 'pad'¶
In [ ]:
rooms['Dept2'] = rooms['department']
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['Dept2'].fillna(method='ffill', inplace = True)
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['dept'] = rooms['department']
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['dept'] = rooms['dept'].fillna(method = 'bfill', inplace = True)
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms.drop(['dept'], axis=1)
Out[ ]:
In [ ]:
rooms['dept'] = rooms['department']
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['dept'].fillna(method = 'bfill', inplace = True)
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['num_students'].fillna(rooms['num_students'].median(), inplace = True)
In [ ]:
rooms
Out[ ]:
to delete a particular cell in a column¶
In [ ]:
rooms.at[6, 'room_number'] = np.nan
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms.drop(106, inplace=True)
In [ ]:
rooms
Out[ ]:
In [ ]:
rooms['room_number'].interpolate(inplace = True)
In [ ]:
rooms
Out[ ]:
Experiments on AMEO_15¶
In [ ]:
df = pd.read_excel('/content/Ameo15.xlsx')
In [ ]:
df.head()
Out[ ]:
In [ ]:
df['10board'].unique()
Out[ ]:
In [ ]:
df.tail()
Out[ ]:
In [ ]:
df.isna().any().count()
Out[ ]:
In [ ]:
df.isna().any()
Out[ ]:
In [ ]:
df.isnull()
Out[ ]:
In [ ]:
df.isnull().any()
Out[ ]:
In [ ]:
df.dtypes
Out[ ]:
In [ ]:
df.Gender.unique()
Out[ ]:
In [ ]:
sns.violinplot(x = 'gender', y='salary', data = df)
Out[ ]:
In [ ]:
df[['10percentage', '12percentage', 'collegegpa', 'gender']].groupby('gender').mean()
Out[ ]:
In [ ]:
df[['10percentage', '12percentage', 'collegegpa', 'gender']].groupby('gender').median()
Out[ ]:
In [ ]:
df[['conscientious', 'agreeableness',
'extraversion', 'nueroticism', 'openess_to_experience',
'gender']].groupby('gender').mean()
Out[ ]:
In [ ]:
df[['conscientious', 'agreeableness',
'extraversion', 'nueroticism', 'openess_to_experience',
'gender']].groupby('gender').median()
Out[ ]:
In [ ]:
df[['salary', 'gender']].groupby('gender').mean()
Out[ ]:
define a threshold for salary¶
In [ ]:
df['salary'].mean()
Out[ ]:
In [ ]:
df['salary'].mean() + df['salary'].std()
Out[ ]:
In [ ]:
th = df['salary'].mean() + df['salary'].std()
In [ ]:
df['HighIncome'] = (df.salary > th)
In [ ]:
df.sample(10)
Out[ ]:
In [ ]:
df[['salary', 'HighIncome', 'gender']].groupby(['HighIncome', 'gender']).mean()
Out[ ]:
In [ ]:
df[['salary', 'HighIncome', 'gender']].groupby(['HighIncome', 'gender']).count()
Out[ ]:
In [ ]:
print("Low income female percentage ", 1264/3825*100)
In [ ]:
print("High income female percentage ", 76/333*100)
In [ ]:
print("Low income female percentage ", 1264/(1264+3825)*100)
In [ ]:
print("High income female percentage ", 76/(76+333)*100)
In [ ]:
df_ = df
In [ ]:
df_ = df_[['electrical_engg', 'telecom_engg', 'civil_engg' ]].sample(30)
In [ ]:
df_
Out[ ]:
In [ ]:
df.info()
In [ ]:
def trade_categoriese(row):
if row['electrical_engg'] > 0:
return 'ee'
elif row['telecom_engg'] > 0:
return 'te'
elif row['civil_engg'] > 0:
return 'ce'
elif row['computer_programming'] > 0:
return 'cp'
elif row['electronics_semicon'] > 0:
return 'es'
elif row['computer_science'] > 0:
return 'cs'
elif row['mechanical_engg'] > 0:
return 'me'
In [ ]:
df['EnggTrade'] = df.apply(lambda row: trade_categoriese(row), axis=1)
In [ ]:
df.head()
Out[ ]:
In [ ]:
dx = df[['gender', "EnggTrade", 'salary', ]].groupby(['EnggTrade', 'gender']).mean()
In [ ]:
dx
Out[ ]:
In [ ]:
dx.style.highlight_max(color='green').highlight_min(color='red')
Out[ ]:
In [ ]:
!pip install nbconvert
In [ ]:
%shell jupyter nbconvert --to html /content/testfile.ipynb