Data Visualization

in Python
Data Explorations in
Python

2
Key Learning Objectives
Apply advanced slicing techniques, including conditional filtering
and multi-axis selection, to extract meaningful subsets of data.

Utilize the groupby method to perform complex aggregations and

gain actionable insights from grouped data.

Leverage value_counts to explore categorical data distributions

and uncover patterns in key variables.

Implement advanced strategies for handling missing data, such

as statistical imputation or threshold-based removal.

Perform advanced querying to extract specific and targeted

subsets of data efficiently.

Prepare datasets for analysis by creating features, normalizing

data, and merging multiple datasets for comprehensive insights.
Dataset Context

Today, we are diving into data exploration—a powerful resource for

uncovering insights about employee behavior and retention in
competitive industries like data science.

Why Data Exploration?

• We will be applying advanced slicing techniques, including

conditional filtering, multi-axis selection, groupby, handling
missing values, and merging data to unveil hidden facts about
the dataset.

• We’re exploring the HR Analytics dataset, focused on advanced

data exploration techniques like data cleaning and preparation.

• This dataset helps uncover patterns in employee behavior,

guiding HR teams to improve retention and optimize training
investments.
About the Data

• The data is about 19,000 employees with mix of categorical and

numerical variables, making it ideal for beginner and advanced
data exploration.
• Its columns includes experience, company_size, company_type,
last_new_job, training_hours, Target, enrollee_id, city, city_
development _index, gender, relevent_experience,
enrolled_university, education_level, major_discipline
• The dataset is perfect for understanding exploratory techniques,
including handling missing data, creating
visualizations, discovering insights, exploring relationships
between variables, working with imbalanced classes for binary
outcomes.
• Now, it’s time to dive into the practical demonstration of how to
utilize this dataset for real-world data preparation and cleaning.
 Indicators & Preprocessing

Key Indicators:
• Employee Turnover: Factors influencing employee exits.
• Job Satisfaction: Drivers of satisfaction across roles.
• Training Effectiveness: Correlation with employee retention.
• Employee Engagement: Link between activities and performance.

Preprocessing Steps:
• Handling Missing Data: Ensured completeness through imputation.
• Normalization: Scaled data for consistent comparison.
• Feature Engineering: Developed new variables for deeper insights.
• Outlier Removal: Cleaned data to avoid skewed analysis.
• Data Merging: Combined datasets for holistic analysis.

Why These Methods?

 Choosing the Right Database

Why Use a Database Instead of CSV?

• Scalability & Performance: Handles large datasets efficiently.

• Data Integrity & Security: Prevents data loss and ensures accuracy.
• Multi-User Access: Supports concurrent transactions.

PostgreSQL vs SQLite
• PostgreSQL: Best for large-scale, high-traffic applications.
• SQLite: Lightweight, ideal for local or small-scale use.

Key Advantages of Databases

• Efficiency: Optimized storage and faster queries.
• Reliability: Ensures data consistency and recovery options.
• Let me know if you need further refinements! 🚀
Importing
Libraries

8
 Importing Libraries

• Pandas: Used for loading, manipulating, and analyzing

structured data.

• NumPy: Used for numerical computing, array operations,

and mathematical calculations.

• MinMaxScaler (from Scikit-Learn): Used for scaling

numerical features to a fixed range (e.g., 0 to 1) for better
machine learning performance.

• SQLite3: Used for connecting to and managing

lightweight relational databases locally.
 Importing Libraries

• SQLAlchemy: Used for database connection management,

executing SQL queries, and ORM (Object-Relational Mapping)
operations.

• sqlite3: Used for connecting to and managing SQLite databases,

ideal for lightweight, serverless applications and local data storage.

• psycopg2: Used for interacting with PostgreSQL databases, allowing

execution of SQL queries, data retrieval, and transaction
management.

• SQLAlchemy: Used for database management with support for

both SQL queries and Object-Relational Mapping (ORM), making it
easier to work with multiple database types in Python applications.
 Importing Libraries

• import pandas as pd

• import numpy as np

• from [Link] import MinMaxScaler

• import sqlite3

• import psycopg2

• from sqlalchemy import create_engine

 Importing Libraries

# Set display options to show all columns

pd.set_option('display.max_columns', None)
Reading Data

13
 Download Your Dataset Here

• [Link]
a5fYHLpPxfevRSuWGTquxyuG?usp=drive_link
 Reading Data

Using PostgreSQL with psycopg2

import psycopg2

# Connect to the PostgreSQL database

conn = [Link](
host="localhost", # Replace with your host
database="job_change", # Replace with your database name
user="postgres", # Replace with your username
password="password" # Replace with your password
port = 5432 # Replace with your password
)

# Query the data

query = "SELECT * FROM job_change_data"
df = pd.read_sql(query, conn)
print([Link]())
[Link]()
 Reading Data

#using live database to query data

# Define connection details

• host = "your_host"

• database = "your_database"

• user = "your_username"

• password = "your_password"

• port = "your_port" # Default is 5432

 Reading Data

from sqlalchemy import create_engine

import pandas as pd

# Create the connection string

connection_string =
f"postgresql://{user}:{password}@{host}:{port}/{dat
abase}"

# Create an engine
engine = create_engine(connection_string)
 Reading Data

# Test the connection by querying a table

try:
query = "SELECT * FROM sms_categories;"
df = pd.read_sql_query(query, engine)
print(df)
except Exception as e:
print(f"An error occurred: {e}")
finally:
# Dispose of the engine
[Link]()
print("Connection closed.")
 Reading Data

Using SQLite
# Create a connection to a new SQLite database (or connect to an
existing one)

conn = [Link]('job_change_data.db')

# Create a cursor object to execute SQL commands

cursor = [Link]()

# Create a table
[Link](''' CREATE TABLE IF NOT EXISTS job_change_data ( ID
INTEGER PRIMARY KEY AUTOINCREMENT, company_size TEXT, experience
TEXT, relevent_experience TEXT ) ''')
 Reading Data

# Insert some sample data

data = [ ('<10', '5', 'Has relevent experience'), ('100-500', '>20',
'No relevent experience'), ('5000-9999', '<1', 'Has relevent
experience'), ]

[Link]('INSERT INTO job_change_data

(company_size, experience, relevent_experience) VALUES (?,
?, ?)', data)

# Commit and close the connection

[Link]()

print("Data inserted successfully.")

 Reading Data

# Query the data and load it into a pandas DataFrame

# Reopen the connection to the database (if it's closed)

conn = [Link]('job_change_data.db')
query = "SELECT * FROM job_change_data"
df = pd.read_sql_query(query, conn)

# Display the DataFrame

print("Data from the database:")
print(df)

# Close the connection

[Link]()
 Reading Data

# Load the dataset (job_change_data.xlsx)

df_excel = pd.read_excel('job_change_data.xlsx’)

# Load the dataset (job_change_data.csv)

df = pd.read_csv('job_change_data.csv')
Data
Understanding

23
 Data Understanding

#Display column names and their types

print([Link]())

# Check the first 10 rows

print([Link](10))

#numerical values
[Link]()

#categorical
[Link](include='object')
 Data Understanding

#Dropping Duplicates
df.drop_duplicates(inplace=True)

• DataFrame Shape
# Show the number of rows and columns in the
dataframe
print(f"Rows: {[Link][0]}, Columns: {[Link][1]}")

• Data Types
# Display data types of all columns
print([Link])
 Data Understanding

Slicing (Advanced)
# Display only rows where city_development_index > 0.8
print("Rows where city_development_index > 0.8:")
print(df[df['city_development_index'] > 0.8])

# Select specific columns (e.g., city, gender, experience)

print("\nSpecific columns (city, gender, experience):")
print(df[['city', 'gender', 'experience']].head())

# Slicing by row and column indices

print("\nFirst 5 rows and specific columns by index:")
print([Link][0:5, [1, 2, 9]]) # First 5 rows and specific columns by index
 Data Understanding

# Display rows where training_hours > 50 and relevant experience is

'Yes'
print("\nRows where training_hours > 50 and relevant experience is
'Yes':")
print(df[(df['training_hours'] > 50) & (df['relevent_experience'] == 1)])

# Display rows where experience is between 5 and 10 years

print("\nRows where experience is between 5 and 10 years:")
print(df[df['experience'].[Link]('(\d+)',
expand=False).astype(float).between(5, 10, inclusive="both")])

# Select last 5 rows of the DataFrame

print("\nLast 5 rows of the DataFrame:")
print([Link][-5:])
 Data Understanding

# Select all rows but only city and training_hours columns

print("\nAll rows with only city and training_hours columns:")
print([Link][:, ['city', 'training_hours']])

# Display rows where city_development_index is in a specific range

(e.g., 0.6 to 0.9)
print("\nRows where city_development_index is between 0.6 and 0.9:")
print(df[df['city_development_index'].between(0.6, 0.9)])

# Conditional slicing for specific cities (e.g., city_1, city_2)

specific_cities = ['city_1', 'city_2']
print("\nRows for specific cities (city_1 and city_2):")
print(df[df['city'].isin(specific_cities)])

# Slicing by skipping rows (e.g., every 2nd row)

print("\nEvery 2nd row of the DataFrame:")
print([Link][::2])
Data Preparation

29
 Data Preparation

Removal of
Unwanted
Observations

Fixing
Handling Structural
Missing Data Errors

Managing
Unwanted
Outliers
 Data Preparation

# Check for missing values in numerical columns

print("Missing values in numerical columns:")
print(df[['city_development_index',
'training_hours']].isnull().sum())

# Get summary statistics for numerical columns

print("\nSummary statistics for numerical columns:")
print(df[['city_development_index',
'training_hours']].describe())
 Data Preparation

# Calculate the correlation between

city_development_index and training_hours
print("\nCorrelation between 'city_development_index' and
'training_hours':")
print(df[['city_development_index', 'training_hours']].corr())

# Categorical Values Check

# Check unique values in 'gender' and 'enrolled_university'

print("\nUnique values in 'gender':")
print(df['gender'].unique())
print("\nUnique values in 'enrolled_university':")
print(df['enrolled_university'].unique())
 Data Preparation

# Count occurrences of each category in

'education_level'
print("\nValue counts for 'education_level':")
print(df['education_level'].value_counts())

# Check frequency distribution of

'major_discipline'
print("\nFrequency distribution of
'major_discipline':")
print(df['major_discipline'].value_counts(normalize=
True))
 Data Preparation

Groupby
# Group by 'major_discipline' and calculate average
training_hours
average_training_by_discipline =
[Link]('major_discipline')['training_hours'].mean()
print("\nAverage training hours by major discipline:")
print(average_training_by_discipline)

# Group by 'company_type' and get count of employees

employees_by_company_type =
[Link]('company_type')['enrollee_id'].count()
print("\nCount of employees by company type:")
print(employees_by_company_type)
 Data Preparation

# Group by 'education_level' and calculate median

city_development_index
median_by_education =
[Link]('education_level')['city_development_index'].media
n()
print("\nMedian city_development_index by education level:")
print(median_by_education)

# Group by 'company_size' and calculate max

training_hours
max_training_by_company_size =
[Link]('company_size')['training_hours'].max()
print("\nMax training hours by company size:")
print(max_training_by_company_size)
 Data Preparation

# Group by multiple columns (e.g.,

'major_discipline' and 'education_level') and get
average training_hours
avg_train_by_disc_educ =

[Link](['major_discipline',
'education_level'])['training_hours'].mean()

print("\nAverage training hours by major discipline

and education level:")

print(avg_train_by_disc_educ)
 Data Preparation

Merge
# Ensure enrollee_id is part of training_hours_per
training_hours_per = [Link]({
'enrollee_id': df['enrollee_id'], # Include enrollee_id
'training_hours_per': (df['training_hours'] /
df['training_hours'].sum()) * 100
})
print(training_hours_per)

# Perform a left merge

merged_df = [Link](df, training_hours_per, on='enrollee_id',
how='left')

# Display the merged DataFrame

print("\nMerged DataFrame (showing first 5 rows):")
print(merged_df.head())
 Data Preparation

Queries
# Query to find enrollees with relevant experience and education
level of 'Masters'
masters_with_experience = [Link]("relevent_experience == 'Has
relevent experience' and education_level == 'Masters'")
print("\nEnrollees with relevant experience and a Master's degree:")
print(masters_with_experience.head())

# Query to find enrollees with city_development_index above 0.9

and more than 5 years of experience
high_cdi_experience = [Link]("city_development_index > 0.9 and
[Link]('5', na=False)", engine='python')
print("\nEnrollees with city_development_index > 0.9 and more than 5
years of experience:")
print(high_cdi_experience.head())
 Data Preparation

# Dropping Irrelevant Columns and Rows

# Identifying Duplicated Columns

# Check for duplicate column names

duplicate_columns =
[Link][[Link]()]

# Display duplicate columns if any

if len(duplicate_columns) > 0:
print("\nDuplicate columns found:")
print(duplicate_columns)
else:
print("\nNo duplicate columns found.")
 Data Preparation

Dealing with Missing Values

#Checking Null Values
[Link]().sum()
[Link]().mean().sort_values(ascending=False)*100

#Filling Null Values accordingly

df['enrolled_university'] =
df['enrolled_university'].fillna(df['enrolled_university'].mode()[0])
df['education_level'] =
df['education_level'].fillna(df['education_level'].mode()[0])
df['major_discipline'] = df['major_discipline'].fillna('Other')
df['gender'] = df['gender'].fillna(df['gender'].mode()[0])
df['experience'].unique()
 Data Preparation

#Doing the Data Correction

df['experience'] = df['experience'].replace({'>20': 20, '<1': 0,'
nan':0,'inf':0}).astype(float)
df['experience'] = df['experience'].fillna(df['experience'].median())

#Changing Data Type

df['experience'] = df['experience'].astype(int)
df['company_type'] = df['company_type'].fillna(0)
df['experience'].unique()
df['company_size'] =
df['company_size'].fillna(df['company_size'].mode()[0])
df['company_type'] = df['company_type'].fillna('Unknown')
df['last_new_job'] =
df['last_new_job'].fillna(df['last_new_job'].mode()[0])
df['company_type'] = df['company_type'].replace(0, 'Unknown')
 Data Preparation

df1 = [Link](['enrollee_id','last_new_job'],
axis=1)

Print([Link]())
 Data Preparation

# Select only the numeric columns

df_numeric = df1.select_dtypes(include=['float64',
'int64’])

Print(df_numeric)

[Link]

[Link](include='object')

[Link]('education_level').agg(['count','mean'])

df['company_size'].unique()
 Data Preparation

Renaming Columns

# Rename columns for better readability

[Link](columns={
'city_development_index':
'City_Development_Index',
'relevent_experience': 'Relevant_Experience',
})
 Data Preparation

Renaming Columns
Identify columns in the dataset that need better readability
or clarity.

# Rename columns for better readability

[Link](columns={
'city_development_index': 'City_Development_Index',
'relevent_experience': 'Relevant_Experience',
'enrolled_university': 'Enrolled_University',
'major_discipline': 'Major_Discipline',
'company_size': 'Company_Size',
'company_type': 'Company_Type',
'last_new_job': 'Last_New_Job'
}, inplace=True)
Assignment

46
Assignment

• Write Python code to filter rows where

City_Development_Index > 0.8 and
Company_Size is greater than 3.

• Use iloc to select the first 10 rows and specific

columns like Experience and Education_Level.

• Group data by Relevant_Experience and

calculate the average
City_Development_Index for each group.
Assignment

• Group by Company_Size and count the

number of unique entries in Last_New_Job.

• Analyze the frequency distribution of

Company_Type using value_counts().

• Identify numerical columns with missing

values and fill them using the mean
Assignment

• Query the dataset to extract rows where Experience >

10 and Company_Size == 7.

• Create a new feature Experience_Gap by subtracting

Last_New_Job from Experience.

• Normalize City_Development_Index to a 0–1 scale

and explain the benefits of normalization.

• Create new column cdi_per and merge it to the

original dataframe using [Link] and analyze the
Conclusion

50
 Conclusion

•We have explored essential advanced data

manipulation techniques that are crucial for any
data analyst or scientist. You learned how to apply
advanced slicing and querying to extract
meaningful insights and harnessed the power of
groupby and aggregations to uncover patterns
within grouped data. We also delved into analyzing
distributions using value_counts and tackled
missing data with robust strategies like imputation
 Conclusion

•To wrap it up, we emphasized the importance of

preparing datasets through feature creation,
normalization, and merging, ensuring your data is
ready for in-depth analysis or machine learning
models.

•By mastering these techniques, you're well on your

way to transforming raw data into actionable
insights. Remember, the key to great data analysis