Develop a Python script to clean data by removing duplicates and handling missing values.

To clean data in Python, primarily use the Pandas library. The script will involve identifying and removing duplicate rows using df.drop_duplicates() and handling missing values through df.dropna() or df.fillna() with appropriate imputation strategies.

Core Cleaning Mechanics

Removing Duplicates:
Use df.drop_duplicates(subset=['col1', 'col2'], keep='first'). The subset parameter specifies columns to consider for uniqueness, and keep determines which duplicate (first, last, or False to drop all) to retain. This ensures data integrity and prevents skewed aggregations or analyses in downstream processes.

Handling Missing Values:
* df.dropna(subset=['col'], how='any') removes rows or columns containing NaN values. While simple, it can lead to significant data loss, especially with sparse datasets.
* df.fillna(value) replaces NaN with a specified constant.
* df.fillna(method='ffill') or df.fillna(method='bfill') propagates the last/next valid observation forward/backward.
* df.fillna({'col_a': df['col_a'].median(), 'col_b': df['col_b'].mode()[0]}) allows for column-specific imputation, often using statistical measures like median for numerical data or mode for categorical data.
The choice depends on the data distribution and domain knowledge, aiming to preserve sample size and avoid introducing bias.

Outlier Detection (Optional but Recommended):
While not explicitly asked, identifying outliers is crucial for robust data. A common statistical approach is df[(df['col'] - df['col'].mean()).abs() < 3 * df['col'].std()], which filters data points within three standard deviations of the mean. Outliers might be removed, capped, or transformed depending on their nature and impact.

Here's a concise example:

import pandas as pd
import numpy as np

data = {'id': [1, 2, 2, 3], 'value': [10, np.nan, 20, 30], 'category': ['A', 'B', 'B', 'C']}
df = pd.DataFrame(data)
# Remove duplicates based on 'id' and 'category'
df_cleaned = df.drop_duplicates(subset=['id', 'category'], keep='first')
# Impute missing 'value' with the median
median_value = df_cleaned['value'].median()
df_cleaned['value'] = df_cleaned['value'].fillna(median_value)
# print(df_cleaned)

Choosing between dropna() and fillna() involves a trade-off between data loss and potential imputation bias. Simple imputation methods (mean/median) are quick but might not capture complex relationships, whereas advanced methods (e.g., regression imputation) are more accurate but computationally intensive. For very large datasets, Pandas might be insufficient, necessitating distributed frameworks like PySpark, where drop_duplicates can trigger expensive data shuffles across partitions.

In the interview, also mention documenting all cleaning decisions for reproducibility and auditability. In a production environment, integrate schema validation (e.g., using Great Expectations or Pydantic) to ensure data quality pre- and post-cleaning, and log statistics on removed duplicates or imputed values. This process often forms a critical step within a dbt model or a Spark transformation job.