employee_performance_analysis.py

import pandas as pd
df = pd.read_csv(r"C:\Users\emman\OneDrive\Sales.csv")
pd.set_option("display.max_columns", None)
print(df)
print(df.dtypes)
df["Salary"] = pd.to_numeric(df["Salary"], errors = "coerce")
df["Joining_Date"] = pd.to_datetime(df["Joining_Date"],errors = "coerce",dayfirst = True)
print(df.dtypes)
a = df.isnull().sum()
print(a)
print("total duplicates")
print(df[df.duplicated()])
temp =df.copy()
temp = temp.drop_duplicates()
print(temp[temp.duplicated()])
print("no of duplicates")
print(temp.duplicated().sum())
print("task 1")
print(temp.head(5))
print(temp.tail(5))
print(temp.shape)
print(temp.dtypes)
print(temp.describe())
print(temp.isnull().sum())
print(temp.duplicated().sum())
print("task 2")
print(temp.duplicated(subset = ["Employee_ID"]).sum())
temp = temp.drop_duplicates(subset = ["Employee_ID"])
temp["Employee_Name"] = temp["Employee_Name"].str.strip().str.title()
temp["Department"] = temp["Department"].str.strip().str.title()
temp["City"] = temp["City"].str.strip().str.title()
temp["Manager"] = temp["Manager"].str.strip().str.title()
temp["Department"] = temp["Department"].replace({"Saless" : "Sales","It" : "IT"})
temp["Salary"] = temp["Salary"] .fillna(temp["Salary"].mean())
temp["Bonus"] = temp["Bonus"] .fillna(temp["Bonus"].median())
temp = temp[(temp["Experience"] >= 0)&(temp["Salary"] >= 0) & (temp["Bonus"] >= 0)]
temp = temp.reset_index(drop=True)
temp["Year"] = temp["Joining_Date"].dt.year
temp["Month"] = temp["Joining_Date"].dt.month
temp["Day"] = temp["Joining_Date"].dt.day
filtered = temp.loc[(temp["Year"] == 2024)
                & (temp["Department"].isin(["IT","HR"]))
                & (temp["City"].isin(["Kochi","Trivandrum"]))
                & (temp["Salary"].between(40000,70000))
                &(temp["Bonus"] > 4000),
                ["Manager","Employee_ID","Experience","Salary","Bonus","Department","City","Month"]]
report = (filtered.groupby("Manager").agg({
    "Employee_ID" : "count",
    "Experience"  : ["sum","mean"],
    "Salary" : ["sum","mean","max","min"],
    "Bonus" : ["sum","mean"] }).sort_values(by = ("Salary","sum"),ascending = False).head(5).reset_index())
print("manager handles the highest number of employees")
highest_number_of_employees = filtered.groupby("Manager")["Employee_ID"].count().sort_values(ascending = False).reset_index()
print(highest_number_of_employees)
print("department has the highest average salary")
highest_avg_salary = filtered.groupby("Department")["Salary"].mean().sort_values(ascending = False).reset_index()
print(highest_avg_salary)
print("city has the highest total salary")
highest_total_salary = filtered.groupby("City")["Salary"].sum().sort_values(ascending = False).reset_index()
print(highest_total_salary)
print("manager gives the highest average bonus")
highest_avg_bonus = filtered.groupby("Manager")["Bonus"].mean().sort_values(ascending = False).reset_index()
print(highest_avg_bonus)
print("month has the highest number of employee joinings")
emp_joining = filtered.groupby("Month")["Employee_ID"].count().sort_values(ascending = False).reset_index()
print(emp_joining)
print("department has the highest total experience")
highest_total_exp = filtered.groupby("Department")["Experience"].sum().sort_values(ascending = False).reset_index()
print(highest_total_exp)
print("employee has the highest salary")
high_salary = filtered.groupby("Employee_ID")["Salary"].sum().sort_values(ascending = False).reset_index().head(1)
print(high_salary)
print("city has the lowest total bonus")
lowest_total_bonus = filtered.groupby("City")["Bonus"].sum().sort_values(ascending = True).reset_index().head(1)
print(lowest_total_bonus)
print(" employees work in each department")
employee_count = filtered.groupby("Department")["Employee_ID"].count().sort_values(ascending = False).reset_index()
print(employee_count)
report.to_csv(r"C:\Users\emman\OneDrive\manager_performance_report.csv", index=False)