In [52]:
import pandas as pd
In [53]:
df= pd.read_csv(r"C:\Users\ipcs nagpur\Downloads\IMP python modules (1)\IMP python module
s\employee_performance_data.csv")
In [54]:
[Link](7)
Out[54]:
Employee_ID Employee_Name Age Department Years_of_Experience Salary Performance_Score Project_Completion
0 1001 Alice Smith 28.0 HR 5.0 55000.0 8.0 yes
1 1002 Bob JOnes 35.0 IT 12.0 120000.0 NaN No
2 1003 Charlie Brown 22.0 IT 2.0 40000.0 5.0 yes
3 1004 David Wilkins -1.0 Finance 15.0 200000.0 9.0 yes
4 1005 Eve Turner 40.0 Finance 10.0 NaN 7.0 NaN
5 1006 Frank N. Walker 27.0 marketing NaN 80000.0 6.0 yes
6 1007 Grace Lee NaN HR 6.0 65000.0 8.0 yes
1. Handling Missing Values:
a) Fill missing Performance_Score , Salary , and Years_of_Experience with the median or mean
values:
To fill missing values, we can use either the median or mean of the respective columns.
In [55]:
[Link]().sum()
Out[55]:
Employee_ID 0
Employee_Name 1
Age 1
Department 1
Years_of_Experience 2
Salary 1
Performance_Score 2
Project_Completion 1
dtype: int64
In [56]:
# Fill missing 'Performance_Score' with the median
df['Performance_Score'].fillna(df['Performance_Score'].median(), inplace=True)
In [57]:
# Fill missing 'Salary' with the median
df['Salary'].fillna(df['Salary'].median(), inplace=True)
In [58]:
# Fill missing 'Years_of_Experience' with the median
df['Years_of_Experience'].fillna(df['Years_of_Experience'].median(), inplace=True)
In [59]:
[Link]().sum()
Out[59]:
Employee_ID 0
Employee_Name 1
Age 1
Department 1
Years_of_Experience 0
Salary 0
Performance_Score 0
Project_Completion 1
dtype: int64
b) Fill missing Department and Age with a default placeholder or use domain-specific logic to impute
them:
For Department , we can fill missing values with a placeholder like "Unknown". For Age , we can replace
missing values with the median age or use a domain-specific approach.
In [60]:
[Link]().sum()
Out[60]:
Employee_ID 0
Employee_Name 1
Age 1
Department 1
Years_of_Experience 0
Salary 0
Performance_Score 0
Project_Completion 1
dtype: int64
In [61]:
# Fill missing 'Years_of_Experience' with the median
df['Age'].fillna(df['Age'].median(), inplace=True)
In [62]:
# Fill missing 'Department' with 'Unknown'
df['Department'].fillna('Unknown', inplace=True)
In [63]:
[Link]().sum()
Out[63]:
Employee_ID 0
Employee_Name 1
Age 0
Department 0
Years_of_Experience 0
Salary 0
Performance_Score 0
Project_Completion 1
dtype: int64
2. Fixing Invalid and Negative Data:
a) Correct the negative Age for David Wilkins:
If there is a negative age, we can replace it with the median age to correct the value.
If there is a negative age, we can replace it with the median age to correct the value.
In [64]:
# Replace negative 'Age' values with the median value
df['Age'] = df['Age'].apply(lambda x: df['Age'].median() if x < 0 else x)
In [66]:
df['Age']
Out[66]:
0 28.0
1 35.0
2 22.0
3 29.0
4 40.0
5 27.0
6 29.0
7 30.0
8 26.0
9 40.0
10 33.0
11 27.0
12 32.0
13 27.0
14 38.0
Name: Age, dtype: float64
b) Replace the extreme outlier Salary values with reasonable adjustments:
Outliers can be identified using the Interquartile Range (IQR) method. We can replace extreme salary values with
the median salary if they are outside the range defined by IQR.
In [67]:
# Identify outliers in 'Salary' using IQR
Q1 = df['Salary'].quantile(0.25)
Q3 = df['Salary'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
# Replace outliers with the median salary
df['Salary'] = df['Salary'].apply(lambda x: df['Salary'].median() if x < lower_bound or
x > upper_bound else x)
In [69]:
df['Salary']
Out[69]:
0 55000.0
1 77500.0
2 77500.0
3 77500.0
4 77500.0
5 80000.0
6 65000.0
7 95000.0
8 50000.0
9 77500.0
10 75000.0
11 72000.0
12 80000.0
13 70000.0
14 77500.0
Name: Salary, dtype: float64
3. Data Cleaning & Standardization:
a) Standardize capitalization for Department and Project_Completion :
To standardize text data like Department and Project_Completion , we will make the department names in
title case and the Project_Completion column lowercase. Additionally, we will replace "n/a" values with
"no".
In [70]:
# Standardize department names to title case
df['Department'] = df['Department'].[Link]()
# Standardize 'Project_Completion' to lowercase and replace 'n/a' with 'no'
df['Project_Completion'] = df['Project_Completion'].[Link]()
df['Project_Completion'].replace('n/a', 'no', inplace=True)
b) Clean up the Performance_Score column:
To clean the Performance_Score column, we can replace any missing or invalid values (if any) with the
median score.
In [71]:
# Replace missing 'Performance_Score' with the median value
df['Performance_Score'].fillna(df['Performance_Score'].median(), inplace=True)
In [83]:
import numpy as np
In [84]:
df['Project_Completion'].replace([Link], 'no', inplace=True)
In [85]:
[Link]().sum()
Out[85]:
Employee_ID 0
Employee_Name 1
Age 0
Department 0
Years_of_Experience 0
Salary 0
Performance_Score 0
Project_Completion 0
dtype: int64
In [86]:
df['Project_Completion']
Out[86]:
0 yes
1 no
2 yes
3 yes
4 no
5 yes
6 yes
7 yes
8 yes
9 no
10 no
11 yes
12 yes
13 yes
14 yes
Name: Project_Completion, dtype: object
4. Data Transformation:
a) Perform any necessary data transformations:
We may need to convert Years_of_Experience to numeric type if it isn't already in the correct format. We can
also check for and handle any potential errors (like NaN values) in that column.
In [87]:
# Ensure 'Years_of_Experience' is numeric (convert if necessary)
df['Years_of_Experience'] = pd.to_numeric(df['Years_of_Experience'], errors='coerce')
# If there are any NaN values after the conversion, fill them with the median or mean
df['Years_of_Experience'].fillna(df['Years_of_Experience'].median(), inplace=True)
b) Identify and handle any duplicate employee records:
We can check for duplicate records based on Employee_ID and remove any duplicates.
In [88]:
# Drop duplicate rows based on 'Employee_ID'
df.drop_duplicates(subset='Employee_ID', inplace=True)
5. Basic Data Analysis:
a) Calculate the average Salary by Department :
To calculate the average salary for each department, we can group by the Department column.
In [89]:
# Group by 'Department' and calculate average salary
avg_salary_by_department = [Link]('Department')['Salary'].mean()
print(avg_salary_by_department)
Department
Engineering 76000.000000
Finance 77500.000000
Hr 68125.000000
It 83333.333333
Marketing 65000.000000
Unknown 70000.000000
Name: Salary, dtype: float64
b) Calculate the average Performance_Score per department:
We can similarly calculate the average performance score for each department.
In [90]:
# Group by 'Department' and calculate average performance score
avg_performance_by_department = [Link]('Department')['Performance_Score'].mean()
print(avg_performance_by_department)
Department
Engineering 5.500000
Finance 8.666667
Hr 7.000000
Hr 7.000000
It 7.333333
Marketing 6.000000
Unknown 6.000000
Name: Performance_Score, dtype: float64
c) Identify employees with high performance but low salaries and vice versa:
Let's filter employees who have a high performance score (e.g., > 8) but a low salary (e.g., < $60,000), and vice
versa.
In [92]:
# Employees with high performance but low salary (Performance Score > 7 and Salary < 80,0
00)
high_perf_low_salary = df[(df['Performance_Score'] > 7) & (df['Salary'] < 80000)]
print("Employees with high performance but low salary:\n ", high_perf_low_salary)
# Employees with low performance but high salary (Performance Score < 6 and Salary > 100,
000)
low_perf_high_salary = df[(df['Performance_Score'] < 6) & (df['Salary'] > 190000)]
print("Employees with low performance but high salary:\n ", low_perf_high_salary)
Employees with high performance but low salary:
Employee_ID Employee_Name Age Department Years_of_Experience Salary \
0 1001 Alice Smith 28.0 Hr 5.0 55000.0
3 1004 David Wilkins 29.0 Finance 15.0 77500.0
6 1007 Grace Lee 29.0 Hr 6.0 65000.0
14 1015 Oliver Black 38.0 Finance 20.0 77500.0
Performance_Score Project_Completion
0 8.0 yes
3 9.0 yes
6 8.0 yes
14 10.0 yes
Employees with low performance but high salary:
Empty DataFrame
Columns: [Employee_ID, Employee_Name, Age, Department, Years_of_Experience, Salary, Perfo
rmance_Score, Project_Completion]
Index: []
BY Pranjal Gajbhiye(AIE)
Happy learning..
In [ ]: