Project 2: Dirty Cafe Sales

DATA 607 · Project 2 · Dataset 3 of 3

Overview

This dataset comes from a classmate’s Discussion 5A post. It’s a synthetic set of cafe transactions built for practicing data cleaning: one row per transaction, with blanks and placeholder text like “ERROR” and “UNKNOWN” scattered through every column. The questions from the post: which payment methods are used most, which item sells the most, and who spends the most on what? The post also suggested recovering missing values from the other columns, for example finding a missing price from the total and the quantity.

library(tidyverse)   # reading, cleaning, reshaping, and charting
library(knitr)       # kable() for tables
library(scales)      # dollar() and percent() formatting

# Shared look for all three Project 2 datasets
theme_p2 <- function() {
  theme_minimal(base_size = 12) +
    theme(
      plot.title       = element_text(face = "bold"),
      panel.grid.minor = element_blank(),
      legend.position  = "top"
    )
}
accent <- "#2c5f8a"

Data source

“Cafe Sales: Dirty Data for Cleaning Training” by Ahmed Mohamed on Kaggle. I downloaded dirty_cafe_sales.csv and committed it to GitHub unchanged.

Data structure before tidying

Every column is read as text, and nothing is converted to NA on import, so the blanks and placeholder words stay visible until I decide what to do with them.

csv_url <- paste0(
  "https://raw.githubusercontent.com/dillonleeper/",
  "DATA-607/main/assignments/week06/project2/cafe-sales/dirty_cafe_sales.csv"
)

read_raw <- function(path) {
  read_csv(path, col_types = cols(.default = col_character()), na = character())
}
raw_cafe <- tryCatch(read_raw(csv_url), error = function(e) read_raw("dirty_cafe_sales.csv"))

glimpse(raw_cafe)

Rows: 10,000
Columns: 8
$ `Transaction ID`   <chr> "TXN_1961373", "TXN_4977031", "TXN_4271903", "TXN_7…
$ Item               <chr> "Coffee", "Cake", "Cookie", "Salad", "Coffee", "Smo…
$ Quantity           <chr> "2", "4", "4", "2", "2", "5", "3", "4", "5", "5", "…
$ `Price Per Unit`   <chr> "2.0", "3.0", "1.0", "5.0", "2.0", "4.0", "3.0", "4…
$ `Total Spent`      <chr> "4.0", "12.0", "ERROR", "10.0", "4.0", "20.0", "9.0…
$ `Payment Method`   <chr> "Credit Card", "Cash", "Credit Card", "UNKNOWN", "D…
$ Location           <chr> "Takeaway", "In-store", "In-store", "UNKNOWN", "In-…
$ `Transaction Date` <chr> "2023-09-08", "2023-05-16", "2023-07-19", "2023-04-…

raw_cafe |> slice(1:8) |> kable(caption = "First eight rows of the raw file")

First eight rows of the raw file

Transaction ID	Item	Quantity	Price Per Unit	Total Spent	Payment Method	Location	Transaction Date
TXN_1961373	Coffee	2	2.0	4.0	Credit Card	Takeaway	2023-09-08
TXN_4977031	Cake	4	3.0	12.0	Cash	In-store	2023-05-16
TXN_4271903	Cookie	4	1.0	ERROR	Credit Card	In-store	2023-07-19
TXN_7034554	Salad	2	5.0	10.0	UNKNOWN	UNKNOWN	2023-04-27
TXN_3160411	Coffee	2	2.0	4.0	Digital Wallet	In-store	2023-06-11
TXN_2602893	Smoothie	5	4.0	20.0	Credit Card		2023-03-31
TXN_4433211	UNKNOWN	3	3.0	9.0	ERROR	Takeaway	2023-10-06
TXN_6699534	Sandwich	4	4.0	16.0	Cash	UNKNOWN	2023-10-28

The raw file has 10,000 rows and 8 columns: Transaction ID, Item, Quantity, Price Per Unit, Total Spent, Payment Method, Location, Transaction Date.

Why it’s untidy:

• Missing values come in three forms. Some cells are blank, some say “ERROR”, and some say “UNKNOWN”. They all mean the value wasn’t recorded.
• Numbers and dates are stored as text. Because those placeholder words sit in the Quantity, Price, Total, and Date columns, none of them can be read as numbers or dates.
• Column names have spaces and capitals, like Price Per Unit.
• The three amount columns depend on each other. Total Spent should always equal Quantity × Price Per Unit, but each is stored on its own, so a gap in one isn’t filled even when the other two are known.

Transformation steps

Step 1: Rename columns to snake_case

cafe_named <- raw_cafe |>
  rename_with(\(x) x |> str_to_lower() |> str_replace_all("\\s+", "_"))

names(cafe_named)

[1] "transaction_id"   "item"             "quantity"         "price_per_unit"  
[5] "total_spent"      "payment_method"   "location"         "transaction_date"

Step 2: Audit the problem values (wide to long)

To count bad values across all columns at once, I pivot every column except the ID into one long column / value pair. That turns eight separate checks into one count().

placeholders <- c("", "ERROR", "UNKNOWN")

audit_long <- cafe_named |>
  pivot_longer(-transaction_id, names_to = "column", values_to = "value") |>
  mutate(
    value = str_trim(value),
    issue = case_when(
      value == ""        ~ "Blank",
      value == "ERROR"   ~ "ERROR",
      value == "UNKNOWN" ~ "UNKNOWN",
      TRUE               ~ "OK"
    )
  )

audit_table <- audit_long |>
  count(column, issue) |>
  pivot_wider(names_from = issue, values_from = n, values_fill = 0) |>
  mutate(column = factor(column, levels = names(cafe_named)[-1])) |>
  arrange(column) |>
  relocate(column, OK)

audit_table |> kable(caption = "Problem values per column, before cleaning")

Problem values per column, before cleaning

column	OK	Blank	ERROR	UNKNOWN
item	9031	333	292	344
quantity	9521	138	170	171
price_per_unit	9467	179	190	164
total_spent	9498	173	164	165
payment_method	6822	2579	306	293
location	6039	3265	358	338
transaction_date	9540	159	142	159

Decision: all three placeholders mean the same thing, a value that wasn’t recorded, so they all become NA.

Step 3: Convert placeholders to NA and fix the column types

cafe_typed <- cafe_named |>
  mutate(
    across(everything(), str_trim),
    across(-transaction_id, \(x) if_else(x %in% placeholders, NA_character_, x)),
    quantity         = as.integer(quantity),
    price_per_unit   = as.numeric(price_per_unit),
    total_spent      = as.numeric(total_spent),
    transaction_date = ymd(transaction_date)
  )

missing_before <- cafe_typed |> summarise(across(everything(), \(x) sum(is.na(x))))

Step 4: Recover missing values from the other columns

The classmate’s post pointed out that some gaps can be filled from other columns. I use three rules, all documented here:

1. Every item has one fixed price. I learn the menu from rows where both the item and the price are recorded. A missing price is filled from the item.
2. A price that belongs to only one item identifies that item. If two items share a price, I can’t tell them apart, so those stay missing.
3. Total = Quantity × Price. When any two of the three are known, the third is calculated.

The rules depend on each other: finding a price from the total can then reveal the item. So I run them in a loop until a pass changes nothing.

menu <- cafe_typed |>
  filter(!is.na(item), !is.na(price_per_unit)) |>
  count(item, price_per_unit) |>
  group_by(item) |>
  slice_max(n, n = 1, with_ties = FALSE) |>   # the most common price per item
  ungroup() |>
  select(item, menu_price = price_per_unit)

# Prices that point to exactly one item
unique_prices <- menu |>
  add_count(menu_price, name = "items_at_price") |>
  filter(items_at_price == 1) |>
  select(menu_price, price_item = item)

menu |>
  left_join(unique_prices, by = "menu_price") |>
  transmute(Item = item, `Menu price` = dollar(menu_price),
            `Identifies the item?` = if_else(is.na(price_item), "No, shared price", "Yes")) |>
  arrange(`Menu price`) |>
  kable(caption = "Menu learned from complete rows")

Menu learned from complete rows

Item	Menu price	Identifies the item?
Cookie	$1.00	Yes
Tea	$1.50	Yes
Coffee	$2.00	Yes
Cake	$3.00	No, shared price
Juice	$3.00	No, shared price
Sandwich	$4.00	No, shared price
Smoothie	$4.00	No, shared price
Salad	$5.00	Yes

recover_once <- function(df) {
  df |>
    left_join(menu, by = "item") |>
    left_join(unique_prices, by = c("price_per_unit" = "menu_price")) |>
    mutate(
      price_per_unit = coalesce(price_per_unit, menu_price),         # rule 1
      item           = coalesce(item, price_item),                   # rule 2
      total_spent    = coalesce(total_spent, quantity * price_per_unit),
      quantity       = coalesce(quantity, as.integer(round(total_spent / price_per_unit))),
      price_per_unit = coalesce(price_per_unit, total_spent / quantity)  # rule 3
    ) |>
    select(-menu_price, -price_item)
}

cafe_recovered <- cafe_typed
repeat {
  before <- sum(is.na(cafe_recovered))
  cafe_recovered <- recover_once(cafe_recovered)
  if (sum(is.na(cafe_recovered)) == before) break
}

missing_after <- cafe_recovered |> summarise(across(everything(), \(x) sum(is.na(x))))

Step 5: Label the categories that can’t be recovered

Payment method and location can’t be worked out from anything else in the row. Instead of guessing or dropping those transactions, I label them “Unknown” so they stay in the totals and show up as their own group.

cafe_clean <- cafe_recovered |>
  mutate(
    payment_method = replace_na(payment_method, "Unknown"),
    location       = replace_na(location, "Unknown")
  )

Step 6: Check the result (long and back to wide)

To compare how much each step fixed, I stack the before and after missing counts into one long table, then spread them back out with one column per stage.

recovery <- bind_rows(
  missing_before |> mutate(stage = "Missing before"),
  missing_after  |> mutate(stage = "Missing after recovery"),
  cafe_clean |> summarise(across(everything(), \(x) sum(is.na(x)))) |> mutate(stage = "Missing in final data")
) |>
  pivot_longer(-stage, names_to = "column", values_to = "missing") |>
  pivot_wider(names_from = stage, values_from = missing) |>
  filter(column != "transaction_id") |>
  mutate(Recovered = `Missing before` - `Missing after recovery`)

recovery |> kable(caption = "Missing values per column at each stage")

Missing values per column at each stage

column	Missing before	Missing after recovery	Missing in final data	Recovered
item	969	480	480	489
quantity	479	23	23	456
price_per_unit	533	6	6	527
total_spent	502	23	23	479
payment_method	3178	3178	0	0
location	3961	3961	0	0
transaction_date	460	460	460	0

# Every row with all three amounts must satisfy total = quantity x price
complete_amounts <- cafe_clean |> filter(!is.na(quantity), !is.na(price_per_unit), !is.na(total_spent))
mismatches <- complete_amounts |> filter(abs(total_spent - quantity * price_per_unit) > 0.005)

Of the 9,974 rows with all three amounts, 0 break the rule Total = Quantity × Price, so the recovered values are consistent with the recorded ones.

Rows still missing an item or an amount after recovery can’t be fixed without guessing. Most of the remaining missing items are rows priced at $3 or $4, where two items share the price. Missing transaction dates are left as NA because none of the analyses use the date. For each analysis I use every row that has the fields that analysis needs, and say how many rows that is.

Analytical methods

All analysis uses cafe_clean.

• Payment methods: share of transactions and share of revenue for each method. “Unknown” is shown as its own group rather than dropped.
• Top items: total units sold and total revenue per item.
• Who spends the most on what: there’s no customer ID, so “who” can only mean a group of transactions. I compare the average amount spent per transaction across payment methods and locations, overall and by item.
• No part of this analysis uses randomness, so no seed is needed.

Analysis 1: Which payment methods are used most?

payments <- cafe_clean |>
  group_by(payment_method) |>
  summarise(
    transactions = n(),
    revenue      = sum(total_spent, na.rm = TRUE),
    .groups      = "drop"
  ) |>
  mutate(
    share_txn     = transactions / sum(transactions),
    share_revenue = revenue / sum(revenue)
  ) |>
  arrange(desc(transactions))

payments |>
  transmute(
    `Payment method`     = payment_method,
    Transactions         = comma(transactions),
    `Share of transactions` = percent(share_txn, accuracy = 0.1),
    Revenue              = dollar(revenue),
    `Share of revenue`   = percent(share_revenue, accuracy = 0.1)
  ) |>
  kable(caption = "Transactions and revenue by payment method")

Transactions and revenue by payment method

Payment method	Transactions	Share of transactions	Revenue	Share of revenue
Unknown	3,178	31.8%	$27,813.50	31.2%
Digital Wallet	2,291	22.9%	$20,414.00	22.9%
Credit Card	2,273	22.7%	$20,466.00	23.0%
Cash	2,258	22.6%	$20,402.50	22.9%

known_pay <- payments |> filter(payment_method != "Unknown")

payments |>
  mutate(payment_method = fct_reorder(payment_method, transactions),
         known = payment_method != "Unknown") |>
  ggplot(aes(x = transactions, y = payment_method, fill = known)) +
  geom_col(width = 0.65) +
  geom_text(aes(label = percent(share_txn, accuracy = 0.1)), hjust = -0.15, size = 3.8) +
  scale_fill_manual(values = c(`TRUE` = accent, `FALSE` = "#b8c2cc"), guide = "none") +
  scale_x_continuous(labels = comma, expand = expansion(mult = c(0, 0.12))) +
  labs(title = "Transactions by payment method", x = "Transactions", y = NULL) +
  theme_p2()

Among transactions with a recorded method, Digital Wallet is used most often (2,291 transactions), followed by Credit Card (2,273) and Cash (2,258). The three are close: the gap between the most and least used is 0.5% of known transactions. The bigger story is the Unknown group, 31.8% of all transactions, which is large enough that the ranking of the three known methods could change if those were recorded.

Analysis 2: Which item sells the most?

items <- cafe_clean |>
  filter(!is.na(item)) |>
  group_by(item) |>
  summarise(
    transactions = n(),
    units        = sum(quantity, na.rm = TRUE),
    revenue      = sum(total_spent, na.rm = TRUE),
    .groups      = "drop"
  ) |>
  left_join(menu, by = "item") |>
  arrange(desc(units))

items |>
  transmute(
    Item         = item,
    `Menu price` = dollar(menu_price),
    `Units sold` = comma(units),
    Revenue      = dollar(revenue),
    `Share of revenue` = percent(revenue / sum(revenue), accuracy = 0.1)
  ) |>
  kable(caption = "Units and revenue by item (rows with a known item)")

Units and revenue by item (rows with a known item)

Item	Menu price	Units sold	Revenue	Share of revenue
Coffee	$2.00	3,904	$7,808	9.3%
Salad	$5.00	3,819	$19,095	22.7%
Tea	$1.50	3,650	$5,475	6.5%
Cookie	$1.00	3,598	$3,598	4.3%
Juice	$3.00	3,505	$10,515	12.5%
Cake	$3.00	3,468	$10,404	12.4%
Sandwich	$4.00	3,429	$13,716	16.3%
Smoothie	$4.00	3,336	$13,344	15.9%

items_missing <- sum(is.na(cafe_clean$item))
top_units   <- items |> slice_max(units, n = 1)
top_revenue <- items |> slice_max(revenue, n = 1)

items |>
  select(item, `Units sold` = units, `Revenue ($)` = revenue) |>
  pivot_longer(-item, names_to = "measure", values_to = "value") |>
  ggplot(aes(x = value, y = reorder(paste(item, measure, sep = "___"), value))) +
  geom_col(fill = accent, width = 0.65) +
  facet_wrap(~ measure, scales = "free") +
  scale_y_discrete(labels = \(x) str_remove(x, "___.*$")) +
  scale_x_continuous(labels = comma) +
  labs(title = "Units sold and revenue by item", x = NULL, y = NULL) +
  theme_p2()

lowest_units <- items |> slice_min(units, n = 1)

By units, the most bought item is Coffee (3,904 units). By revenue it’s Salad ($19,095). Those can differ because prices range from $1 to $5: a cheap item can sell the most units while a pricier one brings in the most money. These totals leave out the 480 transactions whose item couldn’t be recovered.

Analysis 3: Who spends the most on what?

spend_by <- function(df, group) {
  df |>
    filter(!is.na(total_spent)) |>
    group_by({{ group }}) |>
    summarise(
      transactions  = n(),
      avg_spend     = mean(total_spent),
      total_revenue = sum(total_spent),
      .groups       = "drop"
    ) |>
    arrange(desc(avg_spend))
}

spend_pay <- spend_by(cafe_clean, payment_method)
spend_loc <- spend_by(cafe_clean, location)

bind_rows(
  spend_pay |> rename(group = payment_method) |> mutate(by = "Payment method"),
  spend_loc |> rename(group = location) |> mutate(by = "Location")
) |>
  transmute(
    `Grouped by`          = by,
    Group                 = group,
    Transactions          = comma(transactions),
    `Avg spend per transaction` = dollar(avg_spend, accuracy = 0.01),
    Revenue               = dollar(total_revenue)
  ) |>
  kable(caption = "Average spend per transaction")

Average spend per transaction

Grouped by	Group	Transactions	Avg spend per transaction	Revenue
Payment method	Cash	2,254	$9.05	$20,402.50
Payment method	Credit Card	2,269	$9.02	$20,466.00
Payment method	Digital Wallet	2,285	$8.93	$20,414.00
Payment method	Unknown	3,169	$8.78	$27,813.50
Location	In-store	3,008	$9.03	$27,174.00
Location	Unknown	3,953	$8.95	$35,369.50
Location	Takeaway	3,016	$8.80	$26,552.50

item_pay <- cafe_clean |>
  filter(!is.na(item), !is.na(total_spent), payment_method != "Unknown") |>
  group_by(item, payment_method) |>
  summarise(avg_spend = mean(total_spent), transactions = n(), .groups = "drop")

item_pay |>
  mutate(item = fct_reorder(item, avg_spend)) |>
  ggplot(aes(x = payment_method, y = item, fill = avg_spend)) +
  geom_tile(color = "white", linewidth = 1) +
  geom_text(aes(label = dollar(avg_spend, accuracy = 0.01)), size = 3.6) +
  scale_fill_gradient(low = "#eaf2f8", high = "#5b8db8", labels = dollar) +
  labs(
    title = "Average spend per transaction, by item and payment method",
    x     = NULL,
    y     = NULL,
    fill  = "Avg spend"
  ) +
  theme_p2() +
  theme(panel.grid = element_blank())

pay_known <- spend_pay |> filter(payment_method != "Unknown")
loc_known <- spend_loc |> filter(location != "Unknown")
pay_gap   <- max(pay_known$avg_spend) - min(pay_known$avg_spend)
loc_gap   <- max(loc_known$avg_spend) - min(loc_known$avg_spend)
item_spread <- item_pay |>
  group_by(item) |>
  summarise(gap = max(avg_spend) - min(avg_spend), .groups = "drop")

Across payment methods, average spend per transaction runs from $8.93 to $9.05, a gap of only $0.12. In-store and takeaway differ by $0.23. Location is missing even more often than payment method: 3,953 transactions with a recorded total have no location. The heatmap shows the same thing item by item: what someone spends depends almost entirely on what they buy, not how they pay. Within any one item, the averages across payment methods differ by at most $0.55.

Conclusions

• Payment methods are close to evenly split. Digital Wallet leads among recorded methods with 2,291 transactions, but 31.8% of transactions have no method recorded, which is the biggest problem for this question.
• Coffee sells the most units; Salad brings in the most revenue. Price matters as much as volume when ranking items.
• Spending is driven by the item, not the customer group. Average spend barely moves across payment methods ($0.12 range) or locations ($0.23).
• Cleaning recovered a large share of the gaps. The arithmetic and menu rules filled 1,951 missing values without guessing, and the recovered rows all satisfy Total = Quantity × Price.

Limitations. The data is synthetic, so these patterns describe how the dataset was generated more than real cafe behavior. There’s no customer ID, so “who spends the most” can only be answered by payment method and location. Items that share a price can’t be recovered from the price alone, and payment method and location can’t be recovered at all.

AI Disclosure and Citation

Claude (Opus 5.5, accessed through claude.ai) helped with this dataset, including writing the cleaning, recovery, and analysis code, drafting the write-up, and rendering the document to check the results.

Anthropic. (2026). Claude (Opus 5.5) [Large language model]. https://claude.ai/. Accessed October 11, 2026.