Data Cleaning test for job interviews: common mistakes to avoid

Commonly overlooked yet paramount in data analysis, data cleaning can be the difference between clear insights and a muddled report. In interviews, candidates often falter when they're presented with datasets that feature inconsistencies in formatting or data types. Let’s focus on practical approaches to cleaning data, using examples from common business scenarios that you may face during interviews or on the job.

Imagine your employer has handed you a sales report laden with discrepancies: inconsistent phone number formats, varied date representations, and mixed data types for sales figures. Cleaning this data isn't just a task; it’s a critical skill that showcases your attention to detail.

Cleaning Phone Numbers

Consider a simple dataset with a column for phone numbers:

Name	Phone Number
Alice	(123) 456-7890
Bob	123-456-7890
Carol	1234567890
Dave	(123)4567890

Task: Make all phone numbers follow the format "123-456-7890".

Step-by-Step Solution:

1. Identify the Issue: The phone numbers are formatted inconsistently.
2. Formula/Implementation:
   • In Excel, you can use the following formulas to clean the data:

   =TEXTJOIN("-", TRUE, MID(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), LEN(A2)-8, 10))
   

   • In Python's pandas, you might use:

   import pandas as pd
   df['Phone Number'] = df['Phone Number'].replace(r'[
   () -]', '', regex=True)
   df['Phone Number'] = df['Phone Number'].str.replace(r'([0-9]{3})([0-9]{3})([0-9]{4})', r'    ext{1}-    ext{2}-    ext{3}')
   

3. Result: The cleaned phone numbers are now:

Name	Phone Number
Alice	123-456-7890
Bob	123-456-7890
Carol	123-456-7890
Dave	123-456-7890

Common Mistake:

Candidates often fail to notice the various ways phone numbers can be formatted and assume there’s only one approach. Failing to normalize several versions of the phone number, or applying a single formula without accounting for the nuances often leads to incomplete cleaning processes.

Handling Inconsistent Date Formats

Next, let’s address another obstacle:

Order ID	Order Date
1	01/25/2021
2	25-01-2021
3	2021/01/29
4	01/31/21

Task: Convert all order dates to the format "MM/DD/YYYY".

Step-by-Step Solution:

1. Identify the Issue: Dates are inconsistently formatted.
2. Formula/Implementation:
   • In Excel, use the following formula:

   =IFERROR(DATEVALUE(A2), DATEVALUE(TEXT(A2,"MM/DD/YYYY")))
   

   • In pandas:

   df['Order Date'] = pd.to_datetime(df['Order Date'], errors='coerce')
   df['Order Date'] = df['Order Date'].dt.strftime('%m/%d/%Y')
   

3. Result: All dates are standardized:

Order ID	Order Date
1	01/25/2021
2	01/25/2021
3	01/29/2021
4	01/31/2021

Common Mistake:

Candidates frequently overlook the importance of specifying errors='coerce' in pandas, leading to invalid entries being treated as errors rather than being reformatted as NaT. This means data integrity is compromised in their analyses.

Cleaning Sales Data Formats

Finally, let’s manage inconsistent data types in sales figures:

Product	Sales
A	$100
B	200
C	$150.50
D	'$180'

Task: Ensure all sales figures are of type float for accurate calculations.

Step-by-Step Solution:

1. Identify the Issue: The sales figures are a mix of strings and numeric formats.
2. Formula/Implementation:
   • In Excel:

   =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$", ""),"'", ""))
   

   • In pandas:

   df['Sales'] = df['Sales'].replace({'$': '', '"': ''}, regex=True).astype(float)
   

3. Result: All sales figures are now floats:

Product	Sales
A	100.00
B	200.00
C	150.50
D	180.00

Common Mistake:

Candidates often assume they can cast the column directly to float without first sanitizing the strings, leading to conversion errors and the potential loss of critical data.

Interview Traps

• Ignoring Edge Cases: Failing to account for all possible formats can lead to incomplete data cleaning, causing errors in analysis.
• Using One-Size-Fits-All Solutions: Relying on a single formula or function without validating it against the entire dataset can lead to discrepancies.
• Neglecting Data Validation: Missing the opportunity to check for non-numeric entries in a supposed numeric column will cause miscalculations.

On the Job

In reality, data cleaning is an iterative process where unexpected complications arise daily. Whether you’re preparing datasets for reports in Excel or performing ETL tasks in Python, a robust data-cleaning methodology will save hours in troubleshooting and error correction down the line. Sound practices include version control of datasets, documenting cleaning procedures, and considering automation where possible.

Being meticulous during data cleaning not only ensures quality analysis but also positions you as a reliable candidate for any data-oriented role. Candidates who can demonstrate their knowledge of these procedures in interviews will stand out in the selection process.

References

• Excel Functions
• Pandas Documentation
• Data Cleaning Techniques