import pandas as pd
import numpy as np
print(pd.__version__)

departments = pd.DataFrame({
    'department_id': [1, 2, 3, 4, 5],
    'department_name': ['Engineering', 'Sales', 'Marketing', 'Support', 'Finance'],
    'location': ['Adelaide', 'Sydney', 'Sydney', 'Adelaide', 'Melbourne']
})
departments

rng = np.random.default_rng(seed=55)
n_employees = 120
first_names = ['Amir', 'Bianca', 'Carlos', 'Deepa', 'Elin', 'Farid', 'Grace', 'Hiro', 'Ines', 'Jonas', 'Kalani', 'Leila']
last_names = ['Nguyen', 'Okafor', 'Petrov', 'Quinn', 'Rossi', 'Suarez', 'Tanaka', 'Ueda', 'Vance', 'Wren']
print(n_employees)

rows = []
hire_start = pd.Timestamp('2019-01-01')
hire_end = pd.Timestamp('2025-12-31')
hire_range_days = (hire_end - hire_start).days

for emp_id in range(1, n_employees + 1):
    name = f'{rng.choice(first_names)} {rng.choice(last_names)}'
    dept_id = int(rng.integers(1, 6))
    hire_date = hire_start + pd.Timedelta(days=int(rng.integers(0, hire_range_days)))
    salary = round(float(rng.uniform(55000, 125000)), 2)
    performance_score = round(float(rng.normal(3.5, 0.6)), 1)
    has_left = rng.random() < 0.18
    if has_left:
        left_date = hire_date + pd.Timedelta(days=int(rng.integers(90, 1500)))
    else:
        left_date = pd.NaT
    rows.append([emp_id, name, dept_id, hire_date, salary, performance_score, left_date])

employees = pd.DataFrame(rows, columns=['employee_id', 'name', 'department_id', 'hire_date', 'salary', 'performance_score', 'left_date'])
print(employees.shape)

employees.to_csv('employees.csv', index=False)
departments.to_csv('departments.csv', index=False)
employees = pd.read_csv('employees.csv', parse_dates=['hire_date', 'left_date'])
departments = pd.read_csv('departments.csv')
employees.head()

print(employees.shape)
employees.info()

employees['department_id'].value_counts().sort_index()

staff = employees.merge(departments, on='department_id', how='left')
staff[['name', 'department_name', 'location', 'salary']].head()

print(staff.shape)
staff['department_name'].value_counts()

bins = [0, 65000, 85000, 105000, float('inf')]
labels = ['Entry (<65k)', 'Mid (65k-85k)', 'Senior (85k-105k)', 'Lead (105k+)']
staff['salary_band'] = pd.cut(staff['salary'], bins=bins, labels=labels)
staff['salary_band'].value_counts().reindex(labels)

staff['has_left'] = staff['left_date'].notna()
print(staff['has_left'].sum())
print(f"Overall attrition rate: {staff['has_left'].mean() * 100:.1f}%")

staff['tenure_days'] = (staff['left_date'].fillna(pd.Timestamp('2026-08-15')) - staff['hire_date']).dt.days
staff[['name', 'hire_date', 'left_date', 'tenure_days']].head()

attrition_by_dept = staff.groupby('department_name').agg(
    headcount=('employee_id', 'count'),
    attrition_rate_pct=('has_left', lambda s: round(s.mean() * 100, 1)),
    avg_tenure_days=('tenure_days', 'mean')
).round(1).sort_values('attrition_rate_pct', ascending=False)
attrition_by_dept

salary_by_dept = staff.groupby('department_name')['salary'].agg(['mean', 'median', 'min', 'max']).round(0)
salary_by_dept.sort_values('mean', ascending=False)

pd.crosstab(staff['department_name'], staff['salary_band'])

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(8, 5))
salary_by_dept['mean'].sort_values(ascending=False).plot(kind='bar', ax=ax, color='steelblue')
ax.set_title('Average Salary by Department')
ax.set_ylabel('Average Salary ($)')
plt.tight_layout()
plt.savefig('avg_salary_by_dept.png', dpi=150)
plt.close(fig)
print('Saved avg_salary_by_dept.png')

fig, ax = plt.subplots(figsize=(8, 5))
attrition_by_dept['attrition_rate_pct'].plot(kind='bar', ax=ax, color='indianred')
ax.set_title('Attrition Rate by Department')
ax.set_ylabel('Attrition Rate (%)')
plt.tight_layout()
plt.savefig('attrition_by_dept.png', dpi=150)
plt.close(fig)
print('Saved attrition_by_dept.png')