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? Andrew Adams, Nancy Edwards, Michael Mitchell, Robert King and Laura Callahan

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: Finn Gillespie

2026-10-27

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: Finn Gillespie

2026-10-27

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`)