BAN 3083 - Week 3: Restructuring & Combining Data Files

Overview: Restructuring & Combining Data in Business Analytics

In corporate analytics (BAN 3083), data is almost never stored in a single table shaped the way your chart or statistical model requires. You must master two core operations:

Restructuring (Reshaping) Data (tidyr):
- pivot_longer() (SAS PROC TRANSPOSE Wide-to-Long): Turns column headers (like Q1, Q2, Q3, Q4) into rows so you can plot time series in ggplot2 and run regression models.
- pivot_wider() (SAS PROC TRANSPOSE Long-to-Wide): Turns rows into cross-tabulated columns for executive presentation tables.
Combining Data Files (dplyr):
- Stacking rows (bind_rows): Appending monthly or regional extracts with identical columns (SQL UNION ALL / SAS SET).
- Relational Joins (left_join, inner_join, anti_join): Merging tables side-by-side using a primary/foreign key (SQL JOIN / SAS MERGE BY).

Part 1: Restructuring Wide Spreadsheets into Tidy Long Data (`pivot_longer`)

Accounting and Finance teams love “Wide” spreadsheets where each quarter or year is a separate column. Let’s look at a quarterly revenue spreadsheet for Chinook’s 4 global divisions:

library(tidyverse)
library(DBI)
library(RSQLite)
library(gt)
library(scales)
library(patchwork)

wide_finance <- tibble(
  Division = c("North America", "Europe", "Latin America", "Asia Pacific"),
  Q1_2025  = c(125000, 98000, 64000, 110000),
  Q2_2025  = c(138000, 105000, 71000, 129000),
  Q3_2025  = c(149000, 118000, 68000, 145000),
  Q4_2025  = c(182000, 142000, 89000, 176000)
)

# Display the wide accounting table
wide_finance |>
  gt() |>
  tab_header(title = "Wide Format: Finance Department Spreadsheet") |>
  fmt_currency(columns = starts_with("Q"), currency = "USD", decimals = 0)

Division	Q1_2025	Q2_2025	Q3_2025	Q4_2025
Wide Format: Finance Department Spreadsheet
North America	$125,000	$138,000	$149,000	$182,000
Europe	$98,000	$105,000	$118,000	$142,000
Latin America	$64,000	$71,000	$68,000	$89,000
Asia Pacific	$110,000	$129,000	$145,000	$176,000

Why Wide Data Fails for Plotting

Try plotting Quarter on the x-axis and Revenue on the y-axis from wide_finance—you can’t, because there is no Quarter column or Revenue column!

Let’s use pivot_longer() to restructure the 4 quarter columns into two tidy columns: Quarter and Revenue:

long_finance <- wide_finance |>
  pivot_longer(
    cols = starts_with("Q"),
    names_to = "Quarter",
    values_to = "Revenue"
  )

long_finance

## # A tibble: 16 × 3
##    Division      Quarter Revenue
##    <chr>         <chr>     <dbl>
##  1 North America Q1_2025  125000
##  2 North America Q2_2025  138000
##  3 North America Q3_2025  149000
##  4 North America Q4_2025  182000
##  5 Europe        Q1_2025   98000
##  6 Europe        Q2_2025  105000
##  7 Europe        Q3_2025  118000
##  8 Europe        Q4_2025  142000
##  9 Latin America Q1_2025   64000
## 10 Latin America Q2_2025   71000
## 11 Latin America Q3_2025   68000
## 12 Latin America Q4_2025   89000
## 13 Asia Pacific  Q1_2025  110000
## 14 Asia Pacific  Q2_2025  129000
## 15 Asia Pacific  Q3_2025  145000
## 16 Asia Pacific  Q4_2025  176000

Plotting the Restructured Data (`ggplot2`)

Now that our data is in Tidy (Long) Format, we can plot a multi-division quarterly growth chart in seconds:

ggplot(long_finance, aes(x = Quarter, y = Revenue, color = Division, group = Division)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 3) +
  scale_y_continuous(labels = label_dollar()) +
  labs(
    title = "Quarterly Revenue Trajectory by Division (2025)",
    subtitle = "Restructured from Wide Spreadsheet to Long Tidy Format via pivot_longer()",
    x = "Fiscal Quarter",
    y = "Revenue (USD)",
    color = "Division"
  ) +
  theme_minimal(base_size = 12)

Part 2: Stacking Separate Regional Data Files (`bind_rows`)

Suppose two regional offices send separate monthly invoice extracts. When two tables have the exact same columns, we stack them vertically using bind_rows() (with .id = "Source_Batch" to track which file each row came from):

north_extract <- tibble(
  Invoice_ID = c(9001, 9002),
  Customer   = c("Acme Corp", "Beta LLC"),
  Amount     = c(450.00, 820.50)
)

south_extract <- tibble(
  Invoice_ID = c(9003, 9004),
  Customer   = c("Delta Inc", "Omega Co"),
  Amount     = c(310.00, 1190.00)
)

combined_extracts <- bind_rows(
  "North_Office" = north_extract,
  "South_Office" = south_extract,
  .id = "Regional_File"
)

combined_extracts

## # A tibble: 4 × 4
##   Regional_File Invoice_ID Customer  Amount
##   <chr>              <dbl> <chr>      <dbl>
## 1 North_Office        9001 Acme Corp   450 
## 2 North_Office        9002 Beta LLC    820.
## 3 South_Office        9003 Delta Inc   310 
## 4 South_Office        9004 Omega Co   1190

Part 3: Combining Relational Tables & Restructuring to Wide (`left_join` + `pivot_wider`)

Now let’s connect to the Chinook database and pull three separate tables into R (Customer, Invoice, and Employee) so we can combine them using dplyr relational joins!

con <- dbConnect(RSQLite::SQLite(), "data/chinook.sqlite")

customers <- dbReadTable(con, "Customer") |> as_tibble()
invoices  <- dbReadTable(con, "Invoice")  |> as_tibble()
employees <- dbReadTable(con, "Employee") |> as_tibble()

dbDisconnect(con)

1. Combining `Customer`, `Invoice`, and `Employee` (`inner_join` & `left_join`)

Each customer is assigned to a SupportRepId (which matches EmployeeId in the Employee table), and each invoice belongs to a CustomerId. Let’s combine all three tables to see how much revenue each Sales Support Representative generated by customer region:

rep_performance <- invoices |>
  inner_join(customers, by = "CustomerId") |>
  left_join(employees, by = c("SupportRepId" = "EmployeeId")) |>
  mutate(
    Rep_Name = paste(FirstName.y, LastName.y),
    Market = case_when(
      BillingCountry %in% c("USA", "Canada") ~ "North America",
      BillingCountry %in% c("Brazil", "Argentina", "Chile") ~ "Latin America",
      TRUE ~ "Europe & Rest of World"
    )
  ) |>
  group_by(Rep_Name, Market) |>
  summarize(Total_Sales = sum(Total, na.rm = TRUE), .groups = "drop")

rep_performance

## # A tibble: 9 × 3
##   Rep_Name      Market                 Total_Sales
##   <chr>         <chr>                        <dbl>
## 1 Jane Peacock  Europe & Rest of World       445. 
## 2 Jane Peacock  Latin America                 77.2
## 3 Jane Peacock  North America                311. 
## 4 Margaret Park Europe & Rest of World       385. 
## 5 Margaret Park Latin America                113. 
## 6 Margaret Park North America                277. 
## 7 Steve Johnson Europe & Rest of World       397. 
## 8 Steve Johnson Latin America                 84.2
## 9 Steve Johnson North America                239.

2. Restructuring Long Summary Back to a Wide Executive Cross-Tab (`pivot_wider`)

While ggplot2 loves long data, human executives love reading wide cross-tabulations! Let’s use pivot_wider() to give each Market its own column and display it with gt():

rep_performance |>
  pivot_wider(
    names_from = Market,
    values_from = Total_Sales,
    values_fill = 0
  ) |>
  mutate(Grand_Total = `North America` + `Latin America` + `Europe & Rest of World`) |>
  arrange(desc(Grand_Total)) |>
  gt() |>
  tab_header(
    title = "Sales Support Rep Revenue by Global Market",
    subtitle = "Combined via inner_join() + left_join(), Restructured via pivot_wider()"
  ) |>
  fmt_currency(columns = where(is.numeric), currency = "USD")

Rep_Name	Europe & Rest of World	Latin America	North America	Grand_Total
Sales Support Rep Revenue by Global Market
Combined via inner_join() + left_join(), Restructured via pivot_wider()
Jane Peacock	$444.84	$77.24	$310.96	$833.04
Margaret Park	$385.20	$112.86	$277.34	$775.40
Steve Johnson	$397.20	$84.24	$238.72	$720.16

3. Visualizing the Combined Data (`ggplot2`)

Let’s plot our rep_performance dataset as a grouped horizontal bar chart:

ggplot(rep_performance, aes(x = reorder(Rep_Name, Total_Sales, sum), y = Total_Sales, fill = Market)) +
  geom_col(position = "stack", width = 0.7) +
  coord_flip() +
  scale_y_continuous(labels = label_dollar()) +
  scale_fill_brewer(palette = "Dark2") +
  labs(
    title = "Total Chinook Revenue by Support Representative & Market",
    x = "Support Representative",
    y = "Cumulative Invoice Sales (USD)",
    fill = "Customer Market"
  ) +
  theme_minimal(base_size = 12)

Part 4: Data Auditing with Filtering Joins (`anti_join`)

One of the most useful tools in BAN 3083 is anti_join(x, y): it returns all rows in table x that have NO match in table y.

Which Chinook employees are not assigned as Support Reps for any customer?

employees |>
  anti_join(customers, by = c("EmployeeId" = "SupportRepId")) |>
  select(EmployeeId, FirstName, LastName, Title, Department = Title) |>
  gt() |>
  tab_header(
    title = "Employees With Zero Assigned Customers",
    subtitle = "Identified via anti_join(employees, customers)"
  )

EmployeeId	FirstName	LastName	Department
Employees With Zero Assigned Customers
Identified via anti_join(employees, customers)
1	Andrew	Adams	General Manager
2	Nancy	Edwards	Sales Manager
6	Michael	Mitchell	IT Manager
7	Robert	King	IT Staff
8	Laura	Callahan	IT Staff

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

At the top of this file (Line 3), replace [Type Your Name Here] with your name.
Click 🧶 Knit, then click the blue Publish button $\rightarrow$ RPubs.
Submit your https://rpubs.com/... URL on Canvas!

rsconnect::rpubsUpload(
  title = "BAN 3083: Week 3 Restructuring & Combining Data",
  contentFile = "Week03_Restructuring_and_Combining.html",
  originalDoc = "Week03_Restructuring_and_Combining.Rmd"
)$continueUrl

BAN 3083 - Week 3: Restructuring & Combining Data Files

Student Name: Max Schulze

2026-10-20

Overview: Restructuring & Combining Data in Business Analytics

Part 1: Restructuring Wide Spreadsheets into Tidy Long Data (`pivot_longer`)

Why Wide Data Fails for Plotting

Plotting the Restructured Data (`ggplot2`)

Part 2: Stacking Separate Regional Data Files (`bind_rows`)

Part 3: Combining Relational Tables & Restructuring to Wide (`left_join` + `pivot_wider`)

1. Combining `Customer`, `Invoice`, and `Employee` (`inner_join` & `left_join`)

2. Restructuring Long Summary Back to a Wide Executive Cross-Tab (`pivot_wider`)

3. Visualizing the Combined Data (`ggplot2`)

Part 4: Data Auditing with Filtering Joins (`anti_join`)

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

BAN 3083 - Week 3: Restructuring & Combining Data Files

Student Name: Max Schulze

2026-10-20

Overview: Restructuring & Combining Data in Business Analytics

Part 1: Restructuring Wide Spreadsheets into Tidy Long Data (pivot_longer)

Why Wide Data Fails for Plotting

Plotting the Restructured Data (ggplot2)

Part 2: Stacking Separate Regional Data Files (bind_rows)

Part 3: Combining Relational Tables & Restructuring to Wide (left_join + pivot_wider)

1. Combining Customer, Invoice, and Employee (inner_join & left_join)

2. Restructuring Long Summary Back to a Wide Executive Cross-Tab (pivot_wider)

3. Visualizing the Combined Data (ggplot2)

Part 4: Data Auditing with Filtering Joins (anti_join)

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

Part 1: Restructuring Wide Spreadsheets into Tidy Long Data (`pivot_longer`)

Plotting the Restructured Data (`ggplot2`)

Part 2: Stacking Separate Regional Data Files (`bind_rows`)

Part 3: Combining Relational Tables & Restructuring to Wide (`left_join` + `pivot_wider`)

1. Combining `Customer`, `Invoice`, and `Employee` (`inner_join` & `left_join`)

2. Restructuring Long Summary Back to a Wide Executive Cross-Tab (`pivot_wider`)

3. Visualizing the Combined Data (`ggplot2`)

Part 4: Data Auditing with Filtering Joins (`anti_join`)