Look, I've sat on both sides of the interview table. When an interviewer asks you about data frame operations, they aren't actually testing if you've memorized the dplyr documentation. They're testing whether you can think about data as a structured flow.

Think of a data frame like a massive, multi-page ledger in a warehouse. If I tell you to "find all the shipments from last Tuesday that were delayed and calculate the total loss," you wouldn't just stare at the book. You'd flip to the date column (filtering), look for the "Delayed" status (filtering again), find the cost of those items (selecting), and then add them up on a scrap of paper (aggregating). That's exactly what we're doing in R; we're just replacing the scrap paper with functions.

The Filtering Filter: Base R vs. Tidyverse

One of the most common "gotcha" questions is asking you to filter a data frame using both base R and dplyr. They want to see if you understand the underlying logic of indexing.

# Let's use a dataset of tech support tickets
tickets <- data.frame(
  id = 1:4,
  priority = c("High", "Low", "High", "Medium"),
  resolved = c(TRUE, FALSE, TRUE, FALSE)
)

# The Tidyverse way (clean, readable)
library(dplyr)
high_priority <- tickets %>% filter(priority == "High")

# The Base R way (what the interviewer is checking for)
high_priority_base <- tickets[tickets$priority == "High", ]

Notice that comma in the base R version? tickets[rows, columns]. If you forget that comma, R thinks you're trying to subset columns instead of rows, and the whole thing crashes. I've seen senior devs trip over that in high-pressure interviews. Don't be that person.

Adding Intelligence with Mutate

Interviewer: "How do you create a new column based on existing ones?" They're looking for mutate(). The key here is showing you can handle conditional logic within that mutation—usually via case_when() or ifelse().

# Adding a 'status' column based on resolution and priority
tickets <- tickets %>%
  mutate(urgency = case_when(
    priority == "High" & !resolved ~ "CRITICAL",
    priority == "High" & resolved ~ "Resolved High",
    TRUE ~ "Standard"
  ))

In my experience, the candidates who stand out are the ones who mention that mutate() preserves the original data frame's length, unlike summarize(), which collapses it. That distinction is crucial.

The Join Jungle

You will almost certainly be asked about joins. The trick is to explain why you're choosing a specific join. If you just say "I'll use a left join," you're giving a textbook answer. If you say, "I'll use a left join because I want to keep every single ticket even if there's no matching employee record in the staff table," you're speaking like an engineer.

staff <- data.frame(id = c(1, 2), name = c("Alice", "Bob"))

# Keep all tickets, add staff names where available
final_report <- tickets %>%
  left_join(staff, by = c("id" = "id"))

Remember: inner_join drops rows that don't match in both tables. left_join keeps everything in the first table. If you're dealing with financial data, an inner_join could accidentally delete missing records and hide a massive error in your data pipeline. Be careful.

The Aggregation Gauntlet

Finally, the "group by and summarize" pattern. This is the bread and butter of data analysis. The interviewer wants to see if you can transition from a detailed list to a high-level summary.

# Count how many tickets per priority level
summary_table <- tickets %>%
  group_by(priority) %>%
  summarize(total = n(), 
            resolved_pct = mean(resolved) * 100)

A quick tip: always mention that group_by() doesn't actually change the data frame visually—it just adds metadata that tells subsequent functions how to behave. It's a subtle point, but it shows you actually understand how the dplyr engine works under the hood.

📋 Practical Task

Exercise: Auditing the E-Commerce Order Log

You have been handed two messy data frames: orders and products. Your task is to create a "High Value Audit" report. Write a script that performs the following operations:

orders <- data.frame(
  order_id = 101:105,
  prod_id = c(1, 2, 1, 3, 2),
  quantity = c(2, 1, 5, 1, 3)
)

products <- data.frame(
  prod_id = c(1, 2, 3),
  price = c(10.00, 50.00, 100.00),
  category = c("Stationery", "Electronics", "Electronics")
)

Your requirements:

• Join the two data frames so that every order has its corresponding price and category.
• Create a new column called total_cost (quantity * price).
• Filter the data to only include orders where the total_cost is greater than $40.
• Group the result by category and calculate the total revenue and the average quantity ordered for that category.

Ensure your final output is a summarized data frame containing only the category, the total revenue, and the average quantity.