Pandas Data Wrangling Challenge

In your pandas_wrangling.ipynb notebook:

1. Load all four datasets using appropriate Pandas read functions.
   • Use pd.read_csv() for CSV files with correct encoding detection
   • Use pd.read_json() for the JSON file - handle nested structure appropriately
   • Apply proper dtype specifications where known
2. Create a data profiling function that returns a dictionary containing:
   • Shape (rows, columns)
   • Column names and their data types
   • Missing value count per column
   • Duplicate row count
   • Memory usage in MB
3. Implement data validation checks that identify:
   • Columns with more than 20% missing values
   • Potential date columns stored as strings
   • Numeric columns with unexpected negative values
   • Columns with high cardinality (> 50% unique values)

Create cleaning functions in your notebook:

1. clean_employee_data(df) function that:
   • Standardizes phone numbers to format: +1-XXX-XXX-XXXX
   • Converts salary strings to numeric (remove $, commas, handle "K" suffix)
   • Fills missing performance scores using department median
   • Removes exact duplicate rows, keeping first occurrence
   • For duplicate employee_ids with different data, keep the most recent hire_date record
   • Standardizes city names to title case
   • Validates email format using regex (flag invalid emails in new column)
2. clean_expense_data(df) function that:
   • Parses all date formats into a single datetime format (YYYY-MM-DD)
   • Converts negative amounts to positive (expenses should be positive)
   • Standardizes category names (lowercase, replace variations with canonical names)
   • Strips whitespace and special characters from vendor names
   • Fills missing department_id using employee_id lookup from employees table
   • Creates a "month_year" column for aggregation
   • Flags potential anomalies: expenses > 3 standard deviations from category mean
3. clean_review_data(df) function that:
   • Clips rating values to valid range (1-5)
   • Decodes HTML entities in comment text (e.g., & → &)
   • Standardizes reviewer names (First Last format)
   • Calculates average rating per employee across all dimensions
   • Handles reviews with all-null ratings (drop or flag)
4. flatten_department_json(json_data) function that:
   • Flattens nested JSON structure into a flat DataFrame
   • Extracts location city and country into separate columns
   • Converts budget strings to numeric values
   • Creates a "project_count" column from the projects array
   • Handles missing nested fields gracefully

Create transformation functions in your notebook:

1. create_employee_master(employees_df, departments_df, reviews_df) function that:
   • Left joins employees with departments on department_id
   • Aggregates review data per employee (avg ratings, review count, latest review date)
   • Joins aggregated review data to employees
   • Calculates tenure in years from hire_date
   • Creates salary band categories: 'Entry' (<50K), 'Mid' (50-80K), 'Senior' (80-120K), 'Executive' (>120K)
   • Adds performance category based on score: 'Low' (<60), 'Average' (60-75), 'High' (75-90), 'Exceptional' (>90)
   • Output should have NO duplicate employee_ids
2. create_department_summary(employee_master_df, expenses_df) function that:
   • Groups by department to calculate: headcount, total payroll, avg salary, avg performance score
   • Calculates salary range (max - min) per department
   • Counts employees in each performance category per department
   • Calculates average tenure per department
   • Joins with department budget data to calculate budget utilization (payroll/budget)
   • Aggregates total expenses per department from expenses table
   • Sorts by total payroll descending
3. create_expense_analysis(expenses_df, departments_df) function that:
   • Creates a pivot table: rows=month_year, columns=category, values=sum of amount
   • Adds row totals and column totals
   • Calculates month-over-month percent change for each category
   • Identifies top 3 vendors by total spend per category
   • Flags months where total expenses exceed department monthly budget (budget/12)
   • Creates a rolling 3-month average expense per category
   • Joins department name for readability
4. Advanced transformations:
   • Use pd.merge() with appropriate join types and validate parameter
   • Use pd.concat() where combining DataFrames vertically
   • Implement at least one custom aggregation using agg() with named aggregations
   • Use transform() for at least one group-level calculation
   • Apply apply() with a custom function on at least one column

In your notebook, include validation cells that:

1. Verify data loading:
   • Print shape of each loaded DataFrame
   • Verify expected columns exist
   • Show sample rows from each dataset
2. Verify cleaning results:
   • Assert phone number format matches pattern
   • Assert no negative salaries exist
   • Assert no duplicate employee_ids remain
   • Assert dates are valid datetime objects
   • Assert ratings are within 1-5 range
3. Verify transformation results:
   • Print employee master shape and columns
   • Verify department summary totals
   • Display expense pivot table structure

Required Output Files:

1. cleaned_employees.csv - The final cleaned and enriched employee master file
2. department_summary.csv - Aggregated department metrics and KPIs
3. expense_analysis.csv - Pivot table with expense trends and analysis

analysis_report.txt must include:

1. Data Quality Summary:
   • Initial record counts per dataset
   • Missing value summary (before and after cleaning)
   • Duplicates found and removed
   • Data type conversions performed
2. Key Findings (answer these questions):
   • Which department has the highest average salary? Lowest?
   • What is the correlation between tenure and performance score?
   • Which expense category has grown the most over the time period?
   • Are there any departments consistently over budget?
   • What percentage of employees are in each performance category?
3. Anomalies & Recommendations:
   • List all expense anomalies flagged (transaction IDs and amounts)
   • Identify any departments with unusual patterns
   • Provide 3-5 data-driven recommendations for the client