1. Given a pandas DataFrame df with columns ‘Date’, ‘Sales’, and ‘Customer_Rating’, write a Python code snippet to clean this DataFrame.
The full question
Given a pandas DataFrame df with columns ‘Date’, ‘Sales’, and ‘Customer_Rating’, write a Python code snippet to clean this DataFrame. Assume there are missing values in ‘Customer_Rating’ and duplicate rows across all columns. Remove duplicates and replace missing values in ‘Customer_Rating’ with the average rating.
Model answer
The flow
- Clarify inputs & output shape: Understand the DataFrame structure and the requirements for cleaning.
- Brute force first: Implement straightforward solutions for removing duplicates and handling missing values.
- Optimize: Utilize pandas built-in functions to efficiently perform the operations.
- State complexity: Consider the time complexity of operations, especially with large datasets.
- Test the edges: Ensure the solution handles edge cases like all values missing or no duplicates.
The answer
1. Clarify inputs & output shape
- We have a DataFrame
dfwith columnsDate,Sales, andCustomer_Rating. - The task is to remove duplicate rows and fill missing values in
Customer_Ratingwith the average rating.
2. Brute force first
- Start by identifying duplicate rows and removing them.
- Calculate the average of
Customer_Ratingand use it to fill missing values.
3. Optimize
- Use pandas functions like
drop_duplicates()andfillna()to efficiently clean the DataFrame.
import pandas as pd
# Sample DataFrame
# df = pd.DataFrame({
# 'Date': [...],
# 'Sales': [...],
# 'Customer_Rating': [...]
# })
# Remove duplicate rows
cleaned_df = df.drop_duplicates()
# Calculate the mean of Customer_Rating, ignoring NaN values
average_rating = cleaned_df['Customer_Rating'].mean()
# Fill missing values in Customer_Rating with the average rating
cleaned_df['Customer_Rating'].fillna(average_rating, inplace=True)
- Approach:
drop_duplicates()removes all duplicate rows based on all columns.mean()calculates the average ofCustomer_Rating, ignoring NaN values.fillna()replaces NaN values with the calculated average.- Complexity: The time complexity is approximately $O(n)$ for both removing duplicates and filling NaN values, where $n$ is the number of rows in the DataFrame.
4. Test the edges
- Ensure the solution works when all
Customer_Ratingvalues are missing, or when there are no duplicates.
Why this works
- Testing understanding: The interviewer is assessing your ability to use pandas for data cleaning tasks.
- Efficiency: Using pandas built-in functions ensures operations are performed efficiently on potentially large datasets.
- Edge cases: A strong answer considers edge cases, such as all values missing or no duplicates, ensuring robustness.
- Weak answers: Failing to handle missing values correctly or not removing duplicates would indicate a lack of attention to detail or understanding of pandas capabilities.