Alright, let's put you in the hot seat. Imagine we're in a technical interview. The interviewer hands you a CSV and says, "You have 20 minutes to get this data into a state where we can actually run a linear regression on it." No prep time, no clean API—just a raw, messy file. This is the "whiteboard" pressure part of the job.

I've got a dataset here called sales_dump.csv. It's supposed to track regional sales, but as usual, the data entry was a disaster. Let's pull it in and see what we're dealing with.

# Let's peek at the first few rows
raw_data <- read.csv("sales_dump.csv", stringsAsFactors = FALSE)
head(raw_data)

#   OrderID  Date       Amount   Region  Customer_Rating
# 1 101      2023-01-01 $1,200   North   4.5
# 2 102      01/02/23   $850     South   missing
# 3 103      2023-01-03 500      North   3.2
# 4 104      2023-01-04 $1,100   East    N/A
# 5 105      2023-01-05 $900     West    2.1

The "Quick Win" that Fails

My first instinct is to just get the Amount column into a numeric format so I can sum it up. I'll try a quick as.numeric() and see what happens.

raw_data$Amount <- as.numeric(raw_data$Amount)
# Warning message: NAs introduced by coercion 

And there it is. The classic R warning. If I check the data now, half my amounts are NA. Why? Because R doesn't know what to do with the dollar signs and commas. I was too optimistic. In a timed environment, don't assume the data is "almost" numeric. If there's a symbol, it's a string.

I need to strip those characters out. I'll use gsub to wipe the symbols and then cast it. I'm doing this in one pipe because it's faster to write and read during a live session.

library(dplyr)
library(stringr)

clean_data <- raw_data %>%
  mutate(Amount = as.numeric(str_replace_all(Amount, "[$,]", "")))

# Now Amount is numeric. Progress.

Hunting the Ghost NAs

Now look at Customer_Rating. I see "missing" and "N/A". If I run sum(is.na(clean_data$Customer_Rating)), it'll probably return 0, because those are literally the strings "missing" and "N/A", not actual R NA values. This is a trap that kills your analysis later when mean() returns a string error.

I could write a bunch of if_else statements, but that's too slow for a whiteboard test. I'll just create a vector of "garbage" strings and replace them all at once.

garbage_vals <- c("missing", "N/A", "unknown", "")

clean_data <- clean_data %>%
  mutate(Customer_Rating = na_if(Customer_Rating, "missing"),
         Customer_Rating = na_if(Customer_Rating, "N/A"))

# Wait, that's tedious if I have 10 different types of garbage strings. 
# Let's try a different approach using %in%.

Actually, a better way to do this under pressure is to just force everything that isn't a digit or a decimal into an NA. Let's try that.

clean_data$Customer_Rating <- as.numeric(clean_data$Customer_Rating)
# This actually works here because as.numeric() converts "missing" to NA automatically.
# I overthought it. Lesson: sometimes the simplest "brute force" coercion is the fastest way to clean.

The Date Format Nightmare

Here is the real headache: the Date column. We have 2023-01-01 (ISO) and 01/02/23 (US/Short). If I try as.Date(), it's going to fail on one of those formats.

I'm not going to write a regex to detect which row is which; that's a waste of time. I'll use lubridate's parse_date_time. It allows me to provide a vector of possible formats, and it will try them in order until one sticks.

library(lubridate)

clean_data <- clean_data %>%
  mutate(Date = parse_date_time(Date, orders = c("ymd", "mdy")))

# Let's check if we have any NAs now
sum(is.na(clean_data$Date)) 
# 0. We're in business.

Tying it together for the interviewer

When you're doing this live, the interviewer isn't just looking for the correct answer—they're looking at your workflow. I didn't just blindly run code; I observed a failure (the Amount coercion), reasoned through the cause (special characters), and applied a fix. I did the same for the dates.

The final "clean" pipeline looks like this. If I had to write this from scratch now that I've explored the data, I'd bundle it into one clean block:

final_set <- raw_data %>%
  mutate(
    Amount = as.numeric(str_replace_all(Amount, "[$,]", "")),
    Customer_Rating = as.numeric(Customer_Rating),
    Date = parse_date_time(Date, orders = c("ymd", "mdy"))
  )

📋 Practical Task

Exercise: Cleaning the Chaotic Logistics Manifest

You have been handed a "Logistics Manifest" dataframe that is a total wreck. Your goal is to clean it so that it can be used for calculations.

The Dataset:

manifest <- data.frame(
  Shipment_ID = c("S1", "S2", "S3", "S4"),
  Weight_kg = c("15.5kg", "20.1kg", "missing", "12.0kg"),
  Delivery_Date = c("2023-10-01", "10/02/2023", "2023-10-03", "10-04-23"),
  Cost = c("$100.00", "$150.50", "$120.00", "Unknown"),
  stringsAsFactors = FALSE
)

Your Requirements:

• Weight_kg: Remove the "kg" suffix and convert the column to numeric. The "missing" value should become a proper NA.
• Delivery_Date: Standardize all dates into a single Date format (use lubridate::parse_date_time to handle the mixed formats).
• Cost: Remove the "$" symbol and convert to numeric. The "Unknown" value should become a proper NA.

Write a dplyr pipeline that transforms manifest into clean_manifest meeting all these criteria.