BAN 3083 - Week 2: Reading & Auditing Raw Business Data

Overview: Why Reading Raw Data Matters in Business Analytics

In BAN 3083, data rarely arrives in a clean SQL table. Often, corporate ERP systems (like SAP, Oracle, or SAS exports) dump raw text files with: - Banner headers at the top of the spreadsheet (“Report Generated on…”) - Spaces and symbols in column names (Gross Revenue ($), Store ID #) - Numbers stored as text because of dollar signs ($142,500.50) or percent signs (2.1%) - Inconsistent missing value codes (N/A, MISSING, -999)

In this module, we will read these raw files into R, clean them on import, and immediately display our results using summary tables (skimr, gt) and charts (ggplot2).

Part 1: What Happens When You Import a Messy CSV Naively?

Let’s load our packages and look at the raw file data/messy_erp_sales.csv.

👉 Click the green ▶ button below to see what the raw text file looks like:

library(tidyverse)
library(janitor)
library(skimr)
library(gt)
library(scales)

# Read the first 6 raw lines of text to inspect the file before importing
read_lines("data/messy_erp_sales.csv", n_max = 6)

## [1] "REPORT: CHINOOK REGIONAL STORE AUDIT - CONFIDENTIAL"                                                   
## [2] "GENERATED BY: LEGACY ERP SYSTEM v4.2 | DO NOT EDIT MANUALLY"                                           
## [3] "Store ID #,Store Region,Manager Name,Gross Revenue ($),Total Transactions,Return Rate (%),Audit Status"
## [4] "STR-101,North America,Alice Johnson,\"$142,500.50\",1840,2.1%,PASS"                                    
## [5] "STR-102,North America,Bob Martinez,\"$98,200.00\",1210,3.4%,PASS"                                      
## [6] "STR-103,Europe,Clara Oswald,\"$164,890.75\",2150,1.8%,PASS"

Notice two major problems: 1. Lines 1 and 2 are report banners, not column headers! 2. Missing values are coded as "N/A", "MISSING", and "-999", and revenue has "$" and commas!

If we run a naive read_csv("data/messy_erp_sales.csv"), the entire table is ruined:

naive_df <- read_csv("data/messy_erp_sales.csv")
naive_df

## # A tibble: 12 × 1
##    `REPORT: CHINOOK REGIONAL STORE AUDIT - CONFIDENTIAL`                        
##    <chr>                                                                        
##  1 "GENERATED BY: LEGACY ERP SYSTEM v4.2 | DO NOT EDIT MANUALLY"                
##  2 "Store ID #,Store Region,Manager Name,Gross Revenue ($),Total Transactions,R…
##  3 "STR-101,North America,Alice Johnson,\"$142,500.50\",1840,2.1%,PASS"         
##  4 "STR-102,North America,Bob Martinez,\"$98,200.00\",1210,3.4%,PASS"           
##  5 "STR-103,Europe,Clara Oswald,\"$164,890.75\",2150,1.8%,PASS"                 
##  6 "STR-104,Europe,David Kim,N/A,890,4.9%,REVIEW"                               
##  7 "STR-105,Latin America,Elena Rostova,\"$76,400.25\",-999,2.7%,PASS"          
##  8 "STR-106,Asia Pacific,Frank Wright,\"$189,300.00\",2490,1.2%,PASS"           
##  9 "STR-107,North America,Grace Hopper,\"$115,600.80\",1520,MISSING,REVIEW"     
## 10 "STR-108,Europe,Henry Ford,\"$134,100.00\",1760,2.3%,PASS"                   
## 11 "STR-109,Latin America,Isabel Allende,\"$88,950.40\",1180,3.1%,PASS"         
## 12 "STR-110,Asia Pacific,Jack Ma,\"$210,450.00\",2810,1.5%,PASS"

Part 2: Reading & Cleaning Raw Data the Right Way (`read_csv` + `janitor`)

We can fix almost everything right inside read_csv() using two arguments: - skip = 2: Skips the 2 banner lines at the top (just like FIRSTOBS=3 in SAS!). - na = c("", "NA", "N/A", "MISSING", "-999"): Automatically converts every custom missing code into a true R NA.

Then we pipe (|>) directly into: - janitor::clean_names(): Turns "Gross Revenue ($)" into clean snake_case gross_revenue! - readr::parse_number(): Strips "$", ",", and "%" so R treats revenue and return rates as real numbers.

clean_sales <- read_csv(
  file = "data/messy_erp_sales.csv",
  skip = 2,
  na = c("", "NA", "N/A", "MISSING", "-999")
) |>
  clean_names() |>
  mutate(
    gross_revenue = parse_number(gross_revenue),
    return_rate_pct = parse_number(return_rate_percent)
  ) |>
  select(-return_rate_percent)

clean_sales

## # A tibble: 10 × 7
##    store_id_number store_region  manager_name   gross_revenue total_transactions
##    <chr>           <chr>         <chr>                  <dbl>              <dbl>
##  1 STR-101         North America Alice Johnson        142500.               1840
##  2 STR-102         North America Bob Martinez          98200                1210
##  3 STR-103         Europe        Clara Oswald         164891.               2150
##  4 STR-104         Europe        David Kim                NA                 890
##  5 STR-105         Latin America Elena Rostova         76400.                 NA
##  6 STR-106         Asia Pacific  Frank Wright         189300                2490
##  7 STR-107         North America Grace Hopper         115601.               1520
##  8 STR-108         Europe        Henry Ford           134100                1760
##  9 STR-109         Latin America Isabel Allende        88950.               1180
## 10 STR-110         Asia Pacific  Jack Ma              210450                2810
## # ℹ 2 more variables: audit_status <chr>, return_rate_pct <dbl>

Part 3: Displaying Data Quality with Diagnostic Tables (`skimr` & `janitor`)

Before running statistical analyses, an analyst always displays a Data Quality Audit Table to verify data types and missing values (n_missing).

skim(clean_sales)

Data summary
Name	clean_sales
Number of rows	10
Number of columns	7
_______________________
Column type frequency:
character	4
numeric	3
________________________
Group variables	None

Variable type: character

skim_variable	complete_rate	min	max	n_unique
store_id_number	1	7	7	10
store_region	1	6	13	4
manager_name	1	7	14	10
audit_status	1	4	6	2

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
gross_revenue	1	0.9	135599.19	45925.96	76400.25	98200.0	134100.0	164890.8	210450.0	▇▂▅▂▅
total_transactions	1	0.9	1761.11	637.11	890.00	1210.0	1760.0	2150.0	2810.0	▇▂▅▂▅
return_rate_pct	1	0.9	2.56	1.14	1.20	1.8	2.3	3.1	4.9	▇▅▇▁▂

We can also display a formatted two-way Frequency Contingency Table of store_region vs. audit_status using janitor::tabyl():

clean_sales |>
  tabyl(store_region, audit_status) |>
  adorn_totals(c("row")) |>
  adorn_percentages("row") |>
  adorn_pct_formatting(digits = 1) |>
  adorn_ns()

##   store_region       PASS    REVIEW
##   Asia Pacific 100.0% (2)  0.0% (0)
##         Europe  66.7% (2) 33.3% (1)
##  Latin America 100.0% (2)  0.0% (0)
##  North America  66.7% (2) 33.3% (1)
##          Total  80.0% (8) 20.0% (2)

Part 4: Reading Custom Delimited Files (`read_delim`)

When logistics or mainframe systems export data separated by pipes (|) or tabs (\t) instead of commas (similar to DLM='|' in SAS INFILE), we use read_delim():

shipments <- read_delim(
  file = "data/vendor_shipments_pipe.txt",
  delim = "|"
) |>
  clean_names()

shipments

## # A tibble: 6 × 6
##   shipment_id warehouse_hub carrier boxes_shipped freight_cost_usd delivery_days
##   <chr>       <chr>         <chr>           <dbl>            <dbl>         <dbl>
## 1 SHP-5001    Memphis       FedEx             120            1450              2
## 2 SHP-5002    Louisville    UPS                85             980.             3
## 3 SHP-5003    Frankfurt     DHL               210            3121.             5
## 4 SHP-5004    Memphis       FedEx              64             790              2
## 5 SHP-5005    Tokyo         DHL               150            2650              6
## 6 SHP-5006    Louisville    UPS                95            1120.             3

Part 5: Displaying Imported Data with Executive Tables (`gt`) & Plots (`ggplot2`)

1. Executive Summary Table (`gt`)

Let’s summarize our imported regional store performance and display it as a formatted Business Analytics Table using the gt package:

clean_sales |>
  group_by(store_region) |>
  summarize(
    stores = n(),
    avg_revenue = mean(gross_revenue, na.rm = TRUE),
    total_transactions = sum(total_transactions, na.rm = TRUE),
    avg_return_rate = mean(return_rate_pct, na.rm = TRUE) / 100
  ) |>
  arrange(desc(avg_revenue)) |>
  gt() |>
  tab_header(
    title = "Regional Store Performance Summary",
    subtitle = "Cleaned ERP Export (BAN 3083 Data Audit)"
  ) |>
  fmt_currency(columns = avg_revenue, currency = "USD") |>
  fmt_number(columns = total_transactions, decimals = 0) |>
  fmt_percent(columns = avg_return_rate, decimals = 1) |>
  cols_label(
    store_region = "Region",
    stores = "# Stores",
    avg_revenue = "Mean Gross Revenue",
    total_transactions = "Total Transactions",
    avg_return_rate = "Mean Return Rate"
  )

Region	# Stores	Mean Gross Revenue	Total Transactions	Mean Return Rate
Regional Store Performance Summary
Cleaned ERP Export (BAN 3083 Data Audit)
Asia Pacific	2	$199,875.00	5,300	1.4%
Europe	3	$149,495.38	4,800	3.0%
North America	3	$118,767.10	4,570	2.8%
Latin America	2	$82,675.32	1,180	2.9%

2. Visualizing Store Revenue & Audit Status (`ggplot2`)

Now let’s plot our cleaned numerical variables to compare Gross Revenue by Store and Region:

clean_sales |>
  drop_na(gross_revenue) |>
  ggplot(aes(x = reorder(store_id_number, gross_revenue), y = gross_revenue, fill = store_region)) +
  geom_col(width = 0.75) +
  coord_flip() +
  scale_y_continuous(labels = label_dollar()) +
  scale_fill_brewer(palette = "Set2") +
  labs(
    title = "Gross Revenue by Store After Parsing Currency Strings",
    subtitle = "Note: Store STR-104 was automatically flagged as NA during import",
    x = "Store ID",
    y = "Gross Revenue (USD)",
    fill = "Region"
  ) +
  theme_minimal(base_size = 12)

Part 6: Your Turn! (Practice Exercises)

Exercise 1: Summarize & Format the Pipe-Delimited Logistics Data

Using the shipments dataset we imported with read_delim() in Part 4, run the chunk below to create a gt table showing total boxes shipped and average freight cost by carrier:

shipments |>
  group_by(carrier) |>
  summarize(
    num_shipments = n(),
    total_boxes = sum(boxes_shipped),
    avg_freight_cost = mean(freight_cost_usd)
  ) |>
  gt() |>
  tab_header(title = "Logistics Carrier Summary") |>
  fmt_currency(columns = avg_freight_cost, currency = "USD")

carrier	num_shipments	total_boxes	avg_freight_cost
Logistics Carrier Summary
DHL	2	360	$2,885.38
FedEx	2	184	$1,120.00
UPS	2	180	$1,050.38

Exercise 2: Plot Freight Cost vs. Boxes Shipped

Run and customize the scatterplot below showing boxes_shipped on the x-axis and freight_cost_usd on the y-axis:

ggplot(shipments, aes(x = boxes_shipped, y = freight_cost_usd, color = carrier, size = delivery_days)) +
  geom_point(alpha = 0.85) +
  scale_y_continuous(labels = label_dollar()) +
  labs(
    title = "Freight Cost vs. Shipment Volume by Carrier",
    x = "Boxes Shipped",
    y = "Freight Cost (USD)",
    color = "Carrier",
    size = "Delivery Days"
  ) +
  theme_minimal()

🌐 How to Publish to RPubs & Submit Your URL on Canvas

Follow these steps to publish your finished lab report to RPubs.com and get your submission link for Canvas:

Add Your Name: At the very top of this file (Line 3), replace [Type Your Name Here] with your actual name and save (Ctrl+S / Cmd+S).
Click the Green ▶ Play Button on the Code Chunk Below:
- It will automatically knit your report to HTML, upload it to RPubs.com, and print a http://rpubs.com/publish/claim/... link right below the box!
Click That Link:
- It will open RPubs.com in a new tab (sign in or create a free account if this is your first lab, then click Continue).
- Copy your final https://rpubs.com/your_username/... URL from your browser’s address bar and paste it into Canvas!

# 👉 CLICK THE GREEN '▶' PLAY BUTTON ON THE RIGHT OF THIS BOX TO PUBLISH!

# Step 1: Automatically knit your latest work into an HTML report
rmarkdown::render("Week02_Reading_Raw_Data.Rmd", quiet = TRUE)

# Step 2: Upload the HTML report directly to RPubs.com
upload_result <- rsconnect::rpubsUpload(
  title = "BAN 3083: Week 2 Reading Raw Data",
  contentFile = "Week02_Reading_Raw_Data.html",
  originalDoc = "Week02_Reading_Raw_Data.Rmd"
)

cat("\n============================================================\n")
cat("🚀 SUCCESS! CLICK THE LINK BELOW TO CLAIM YOUR RPUBS PAGE:\n")
cat(upload_result$continueUrl, "\n")
cat("============================================================\n")

BAN 3083 - Week 2: Reading & Auditing Raw Business Data

Student Name: [Type Your Name Here]

2026-10-05

Overview: Why Reading Raw Data Matters in Business Analytics

Part 1: What Happens When You Import a Messy CSV Naively?

Part 2: Reading & Cleaning Raw Data the Right Way (`read_csv` + `janitor`)

Part 3: Displaying Data Quality with Diagnostic Tables (`skimr` & `janitor`)

Part 4: Reading Custom Delimited Files (`read_delim`)

Part 5: Displaying Imported Data with Executive Tables (`gt`) & Plots (`ggplot2`)

1. Executive Summary Table (`gt`)

2. Visualizing Store Revenue & Audit Status (`ggplot2`)

Part 6: Your Turn! (Practice Exercises)

Exercise 1: Summarize & Format the Pipe-Delimited Logistics Data

Exercise 2: Plot Freight Cost vs. Boxes Shipped

🌐 How to Publish to RPubs & Submit Your URL on Canvas

BAN 3083 - Week 2: Reading & Auditing Raw Business Data

Student Name: [Type Your Name Here]

2026-10-05

Overview: Why Reading Raw Data Matters in Business Analytics

Part 1: What Happens When You Import a Messy CSV Naively?

Part 2: Reading & Cleaning Raw Data the Right Way (read_csv + janitor)

Part 3: Displaying Data Quality with Diagnostic Tables (skimr & janitor)

Part 4: Reading Custom Delimited Files (read_delim)

Part 5: Displaying Imported Data with Executive Tables (gt) & Plots (ggplot2)

1. Executive Summary Table (gt)

2. Visualizing Store Revenue & Audit Status (ggplot2)

Part 6: Your Turn! (Practice Exercises)

Exercise 1: Summarize & Format the Pipe-Delimited Logistics Data

Exercise 2: Plot Freight Cost vs. Boxes Shipped

🌐 How to Publish to RPubs & Submit Your URL on Canvas

Part 2: Reading & Cleaning Raw Data the Right Way (`read_csv` + `janitor`)

Part 3: Displaying Data Quality with Diagnostic Tables (`skimr` & `janitor`)

Part 4: Reading Custom Delimited Files (`read_delim`)

Part 5: Displaying Imported Data with Executive Tables (`gt`) & Plots (`ggplot2`)

1. Executive Summary Table (`gt`)

2. Visualizing Store Revenue & Audit Status (`ggplot2`)