Live dashboard: https://j221-isaac.night-zone.com · Lightning
talk slides: https://j221-isaac.night-zone.com/talk/ ·
Data: the data/ folder next to this file ·
Code: every chunk below, plus scripts/ for
rebuilding the dashboard from the raw downloads.
The idea came from my work at Mitsui O.S.K. Lines, where I helped build AI tools for route, bunkering and port-call decisions. Inside a shipping company, a port is a schedule and a cost. In the news, a port is usually one number: total containers, or total trade value. I wanted to see what the public data says when you put several measures for the same ports side by side.
Port Atlas uses five public sources:
| Measure | Source and link | Primary or secondary | Coverage used |
|---|---|---|---|
| Goods, partners, value, weight | U.S. Census Bureau, Port HS6 trade data (imports) and exports | Primary: official customs records | 12 ports, full years 2024 and 2025 (December cumulative files) |
| Loaded and empty containers | Port of Los Angeles, historical TEU statistics | Primary: the port authority’s own counts | 24 months, 2024–2025 |
| Ship visits | IMF PortWatch | Secondary: IMF and University of Oxford estimates built from AIS ship-tracking signals | 4 port areas, Jan 2023 to 16 Oct 2026 |
| Time at berth | Bureau of Transportation Statistics, dataset nfsh-p62e | Primary (published aggregate) | Average of 25 container ports, Jan 2019 to Jun 2023 |
| Ocean corridors | UNCTAD, Review of Maritime Transport 2025 | Secondary: published tables sourced from MDS Transmodal and Clarksons | Regional corridors, 2018–2024 |
What a reader should know before trusting the numbers:
ct <- cols(.default = col_double(), direction = col_character(), port = col_character(),
country = col_character(), hs2 = col_character())
trade25 <- read_csv("data/census_port_trade_2025.csv", col_types = ct)
trade24 <- read_csv("data/census_port_trade_2024.csv", col_types = ct)
ports <- read_csv("data/lookup_ports.csv", col_types = cols(port = col_character()))
hs2 <- read_csv("data/lookup_hs2.csv", col_types = cols(.default = col_character()))
cty <- read_csv("data/lookup_countries.csv", col_types = cols(.default = col_character()))
la <- read_csv("data/la_monthly_teu_2024_2025.csv", show_col_types = FALSE)
calls <- read_csv("data/portwatch_monthly_2023_2026.csv", show_col_types = FALSE)
berth <- read_csv("data/bts_container_berth_2019_2023.csv", show_col_types = FALSE)
ocean <- read_csv("data/unctad_ocean_trade.csv", show_col_types = FALSE)
tibble(file = c("Census 2025", "Census 2024", "Los Angeles TEU", "PortWatch", "BTS berth", "UNCTAD"),
rows = c(nrow(trade25), nrow(trade24), nrow(la), nrow(calls), nrow(berth), nrow(ocean)))| file | rows |
|---|---|
| Census 2025 | 71926 |
| Census 2024 | 72522 |
| Los Angeles TEU | 24 |
| PortWatch | 184 |
| BTS berth | 54 |
| UNCTAD | 44 |
The two Census files are already aggregated from the raw downloads to
one row per direction × port × country × HS2 goods chapter. The raw ZIP
files had 2,446,906 records (2025) and 2,436,758 records (2024);
scripts/build_data.py and
scripts/build_operator_data.py do that aggregation and can
be rerun from the original files (see Replication).
What is different about Port Atlas:
I followed the questions in the Assignment 1 bulletproofing guide. The table summarises them; the code that produced each check follows.
| Question | Census trade | Los Angeles TEU | PortWatch | BTS berth |
|---|---|---|---|---|
| Source and documentation | Census Bureau, Foreign Trade Division; file layout | Port of Los Angeles statistics page | IMF/Oxford; PortWatch docs | BTS; methodology |
| Time period | Full year 2024, full year 2025 | Jan 2024 – Dec 2025 | Jan 2023 – 16 Oct 2026 | Jan 2019 – Jun 2023 |
| Records | 4.9 million raw rows; 144,448 aggregated rows | 24 months | 184 area-months | 54 months |
| Duplicates | None on the source key (checked in raw files and below) | None | None | None |
| Consistency | Codes, not free text, so no spelling problems. Every port, country and goods code matches the official dictionaries | Two published subtotals (March and October 2025) don’t match their parts; I sum the parts | Vessel-type counts add up to total calls | n/a |
| Valid ranges | No negative values; all rows in the stated year | No negatives | Observed days match the month length, except the partial October 2026 | Positive hours; consecutive months |
| Missing data | No missing values in amount fields | None | None | None |
key_dupes <- function(df) df %>% count(direction, port, country, hs2) %>% filter(n > 1) %>% nrow()
amounts <- c("vessel_value_usd", "vessel_weight_kg", "container_value_usd", "container_weight_kg")
vet <- function(df, label) tibble(
file = label,
rows = nrow(df),
duplicate_keys = key_dupes(df),
missing_amounts = sum(is.na(df[amounts])),
negative_amounts = sum(df[amounts] < 0, na.rm = TRUE),
ports_not_in_lookup = sum(!unique(df$port) %in% ports$port),
goods_not_in_lookup = sum(!unique(df$hs2) %in% hs2$hs2),
countries_not_in_lookup = sum(!unique(df$country) %in% cty$country),
container_over_vessel = sum(df$container_value_usd > df$vessel_value_usd)
)
bind_rows(vet(trade25, "Census 2025"), vet(trade24, "Census 2024"))| file | rows | duplicate_keys | missing_amounts | negative_amounts | ports_not_in_lookup | goods_not_in_lookup | countries_not_in_lookup | container_over_vessel |
|---|---|---|---|---|---|---|---|---|
| Census 2025 | 71926 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Census 2024 | 72522 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
container_over_vessel checks that containerised cargo, a
subset of vessel cargo, never exceeds it. Countries missing from the
lookup are a handful of Census-specific codes (for example, special
territories); the dashboard keeps them and labels them by code.
la %>%
mutate(import_parts_ok = abs(loaded_import_teu + empty_import_teu - total_import_teu) < 1,
export_parts_ok = abs(loaded_export_teu + empty_export_teu - total_export_teu) < 1) %>%
summarise(months = n(), duplicate_months = sum(duplicated(paste(year, month))),
missing = sum(is.na(across(everything()))), negative = sum(across(where(is.numeric), ~ sum(.x < 0))),
import_subtotal_mismatches = sum(!import_parts_ok), export_subtotal_mismatches = sum(!export_parts_ok))| months | duplicate_months | missing | negative | import_subtotal_mismatches | export_subtotal_mismatches |
|---|---|---|---|---|---|
| 24 | 0 | 0 | 0 | 1 | 2 |
The subtotal mismatches are in the port’s published table, not in my copy. I use the four primary components (loaded and empty, in and out) throughout.
calls %>%
mutate(days_in_month = lubridate::days_in_month(as.Date(date)),
mix = portcalls_container + portcalls_dry_bulk + portcalls_general_cargo + portcalls_roro + portcalls_tanker) %>%
summarise(area_months = n(), areas = n_distinct(portname),
duplicate_keys = sum(duplicated(paste(portid, year, month))),
vessel_types_add_up = all(mix == portcalls),
full_months_complete = all(observed_days[complete_month] == days_in_month[complete_month]),
partial_months = paste(date[!complete_month], collapse = ", "))| area_months | areas | duplicate_keys | vessel_types_add_up | full_months_complete | partial_months |
|---|---|---|---|---|---|
| 184 | 4 | 0 | TRUE | TRUE | 2026-10-01, 2026-10-01, 2026-10-01, 2026-10-01 |
berth %>% summarise(months = n(), first = min(date), last = max(date), duplicates = sum(duplicated(date)), min_hours = min(hours), max_hours = max(hours))| months | first | last | duplicates | min_hours | max_hours |
|---|---|---|---|---|---|
| 54 | 2019-01-01 | 2023-06-01 | 0 | 26 | 37 |
Questions I would still ask someone:
One chart first, because it frames the three findings: which port is “busiest” depends on what you count.
port_totals <- trade25 %>%
group_by(port) %>%
summarise(value = sum(vessel_value_usd), tonnes = sum(vessel_weight_kg) / 1000) %>%
left_join(ports, by = "port") %>%
mutate(rank_value = rank(-value), rank_weight = rank(-tonnes),
name = sub("–Newport News", "", port_name),
focus = name %in% c("Los Angeles", "Houston", "Corpus Christi", "Oakland"))
port_totals %>% arrange(rank_value) %>%
transmute(port = port_name, value_billion_usd = round(value / 1e9, 1), million_tonnes = round(tonnes / 1e6, 1), rank_value, rank_weight)| port | value_billion_usd | million_tonnes | rank_value | rank_weight |
|---|---|---|---|---|
| Los Angeles | 300.7 | 73.1 | 1 | 4 |
| Houston | 204.8 | 212.2 | 2 | 1 |
| Savannah | 121.2 | 42.9 | 3 | 8 |
| Long Beach | 106.1 | 45.6 | 4 | 6 |
| Norfolk–Newport News | 99.0 | 57.4 | 5 | 5 |
| Charleston | 84.2 | 22.5 | 6 | 9 |
| Corpus Christi | 80.8 | 153.3 | 7 | 2 |
| Baltimore | 65.6 | 45.4 | 8 | 7 |
| New Orleans | 65.1 | 117.2 | 9 | 3 |
| Oakland | 48.1 | 14.3 | 10 | 12 |
| Tacoma | 43.2 | 14.9 | 11 | 11 |
| Seattle | 24.6 | 15.0 | 12 | 10 |
slope <- port_totals %>% select(name, focus, rank_value, rank_weight) %>%
pivot_longer(c(rank_value, rank_weight), names_to = "measure", values_to = "rank") %>%
mutate(x = ifelse(measure == "rank_value", 1, 2))
ggplot(slope, aes(x, rank, group = name)) +
geom_line(aes(colour = focus, linewidth = focus)) +
geom_point(aes(colour = focus, shape = measure), size = 2.6) +
geom_text(data = filter(slope, x == 1), aes(label = paste(rank, name), fontface = ifelse(focus, "bold", "plain")), hjust = 1, nudge_x = -.05, size = 3.6, colour = ink) +
geom_text(data = filter(slope, x == 2), aes(label = paste(rank, name), fontface = ifelse(focus, "bold", "plain")), hjust = 0, nudge_x = .05, size = 3.6, colour = ink) +
scale_y_reverse() + scale_x_continuous(limits = c(.45, 2.55), breaks = c(1, 2), labels = c("Rank by trade value", "Rank by shipping weight")) +
scale_colour_manual(values = c(`TRUE` = blue, `FALSE` = quiet), guide = "none") +
scale_linewidth_manual(values = c(`TRUE` = 1.4, `FALSE` = .6), guide = "none") +
scale_shape_manual(values = c(rank_value = 16, rank_weight = 15), guide = "none") +
labs(title = "“Busiest” depends on what you count", subtitle = "12 selected U.S. ports, 2025, imports and exports, all vessel cargo",
x = NULL, y = NULL, caption = "Source: U.S. Census Bureau, Port HS6 trade data. Dollars are merchandise value, not port revenue.") +
theme_atlas() + theme(axis.text.y = element_blank(), panel.grid = element_blank(), axis.text.x = element_text(face = "bold", colour = ink))Los Angeles moves the most value ($300.7 billion) but ranks 4th by weight. Houston and Corpus Christi move fuel and bulk goods, which are heavy and cheap per kilogram. That is why I give each finding its own measure.
oak <- function(df) df %>% filter(port == "2811", direction == "exports") %>%
group_by(hs2) %>% summarise(value = sum(vessel_value_usd), .groups = "drop") %>%
mutate(share = value / sum(value))
oak25 <- oak(trade25); oak24 <- oak(trade24)
oak_goods <- oak25 %>% left_join(select(oak24, hs2, share24 = share), by = "hs2") %>%
left_join(hs2, by = "hs2") %>% arrange(desc(value)) %>% slice_head(n = 6)
oak_dest <- trade25 %>% filter(port == "2811", direction == "exports") %>%
group_by(country) %>% summarise(value = sum(vessel_value_usd)) %>% mutate(share = value / sum(value)) %>%
arrange(desc(value)) %>% slice_head(n = 5) %>% left_join(cty, by = "country")
oak_goods %>% transmute(goods, value_million_usd = round(value / 1e6), share_2025 = pct(share), share_2024 = pct(share24))| goods | value_million_usd | share_2025 | share_2024 |
|---|---|---|---|
| Fruit & nuts | 7115 | 35.1% | 29.6% |
| Meat | 4332 | 21.3% | 24.7% |
| Dairy, eggs & honey | 1009 | 5.0% | 4.2% |
| Electrical equipment | 860 | 4.2% | 4.8% |
| Beverages | 624 | 3.1% | 3.6% |
| Machinery & computers | 554 | 2.7% | 3.5% |
| destination | share |
|---|---|
| Japan | 18.4% |
| South Korea (Republic of Korea) | 13.2% |
| India | 6.9% |
| China | 6.5% |
| Taiwan | 4.9% |
ggplot(oak_goods, aes(y = fct_reorder(goods, share))) +
geom_col(aes(x = share, fill = rank(-share) <= 2), width = .58) +
geom_point(aes(x = share24), shape = 124, size = 9, colour = brown) +
geom_text(aes(x = pmax(share, share24) + .012, label = pct(share)), hjust = 0, size = 3.8, colour = ink) +
scale_fill_manual(values = c(`TRUE` = blue, `FALSE` = quiet), guide = "none") +
scale_x_continuous(labels = percent_format(accuracy = 1), limits = c(0, .42), expand = c(0, 0)) +
labs(title = "Oakland exports food", subtitle = "Share of Oakland's 2025 export value; brown marks show the 2024 share", x = NULL, y = NULL,
caption = "Source: U.S. Census Bureau, Port HS6 trade data, all vessel cargo.") +
theme_atlas() + theme(panel.grid.major.y = element_blank())la_year <- la %>% group_by(year) %>%
summarise(loaded_in = sum(loaded_import_teu), empty_in = sum(empty_import_teu),
loaded_out = sum(loaded_export_teu), empty_out = sum(empty_export_teu)) %>%
mutate(empty_share_out = empty_out / (loaded_out + empty_out), loaded_in_to_out = loaded_in / loaded_out)
la_year %>% mutate(empty_share_out = pct(empty_share_out), loaded_in_to_out = round(loaded_in_to_out, 2))| year | loaded_in | empty_in | loaded_out | empty_out | empty_share_out | loaded_in_to_out |
|---|---|---|---|---|---|---|
| 2024 | 5356680 | 7529.30 | 1494201 | 3438942 | 69.7% | 3.58 |
| 2025 | 5316713 | 3793.15 | 1429260 | 3489552 | 70.9% | 3.72 |
la_month <- la %>% mutate(empty_share = empty_export_teu / (empty_export_teu + loaded_export_teu))
range(la_month$empty_share[la_month$year == 2025])## [1] 0.6663583 0.7445220
la_bars <- la_year %>% filter(year == 2025) %>%
transmute(Inbound_Loaded = loaded_in, Inbound_Empty = empty_in, Outbound_Loaded = loaded_out, Outbound_Empty = empty_out) %>%
pivot_longer(everything(), names_to = c("direction", "status"), names_sep = "_", values_to = "teu") %>%
mutate(status = factor(status, c("Empty", "Loaded")), direction = factor(direction, c("Outbound", "Inbound")))
ggplot(la_bars, aes(teu / 1e6, direction, fill = status)) +
geom_col(width = .55, colour = "white") +
annotate("text", x = (la_year$loaded_out[2] + la_year$loaded_out[2] + la_year$empty_out[2]) / 2 / 1e6, y = 1, label = paste(pct(la_year$empty_share_out[2]), "empty"), colour = "white", fontface = "bold", size = 4.4) +
scale_fill_manual(values = c(Loaded = blue, Empty = brown), breaks = c("Loaded", "Empty")) +
scale_x_continuous(labels = function(x) paste0(x, "M"), expand = c(0, 0)) +
labs(title = "Most boxes leaving Los Angeles are empty", subtitle = "Port of Los Angeles, 2025, million TEU (20-foot equivalent units)", x = NULL, y = NULL,
caption = "Source: Port of Los Angeles historical TEU statistics.") +
theme_atlas() + theme(panel.grid.major.y = element_blank(), axis.text.y = element_text(face = "bold", colour = ink))ggplot(la_month, aes(month, empty_share, colour = factor(year), linetype = factor(year), shape = factor(year))) +
geom_line(linewidth = 1) + geom_point(size = 2.4) +
scale_colour_manual(values = c(`2024` = brown, `2025` = blue)) + scale_linetype_manual(values = c(`2024` = "dashed", `2025` = "solid")) +
scale_shape_manual(values = c(`2024` = 15, `2025` = 16)) +
scale_x_continuous(breaks = 1:12, labels = month.abb) + scale_y_continuous(labels = percent_format(accuracy = 1), limits = c(.55, .8)) +
labs(title = "Every month, two-thirds or more leave empty", subtitle = "Empty share of outbound TEUs, Port of Los Angeles", x = NULL, y = NULL,
caption = "Source: Port of Los Angeles historical TEU statistics.") +
theme_atlas()balt <- calls %>% filter(portname == "Baltimore", year %in% c(2023, 2024)) %>%
mutate(per_day = portcalls / observed_days)
balt_cmp <- balt %>% select(year, month, per_day) %>%
pivot_wider(names_from = year, values_from = per_day, names_prefix = "y") %>%
mutate(change = y2024 / y2023 - 1)
balt_cmp %>% mutate(across(c(y2023, y2024), ~ round(.x, 2)), change = pct(change))| month | y2023 | y2024 | change |
|---|---|---|---|
| 1 | 5.32 | 5.16 | -3.0% |
| 2 | 5.43 | 5.21 | -4.1% |
| 3 | 5.10 | 4.61 | -9.5% |
| 4 | 5.47 | 1.30 | -76.2% |
| 5 | 5.32 | 2.71 | -49.1% |
| 6 | 5.73 | 4.37 | -23.8% |
| 7 | 5.26 | 4.77 | -9.2% |
| 8 | 5.13 | 5.16 | 0.6% |
| 9 | 5.47 | 5.07 | -7.3% |
| 10 | 5.16 | 4.74 | -8.1% |
| 11 | 4.97 | 5.73 | 15.4% |
| 12 | 4.58 | 5.35 | 16.9% |
balt %>% filter(month == 4) %>% select(year, portcalls, observed_days, portcalls_container, portcalls_roro, portcalls_dry_bulk)| year | portcalls | observed_days | portcalls_container | portcalls_roro | portcalls_dry_bulk |
|---|---|---|---|---|---|
| 2023 | 164 | 30 | 49 | 43 | 43 |
| 2024 | 39 | 30 | 1 | 23 | 9 |
ggplot(balt, aes(month, per_day, colour = factor(year), linetype = factor(year), shape = factor(year))) +
annotate("rect", xmin = 3 + 25 / 31, xmax = 6 + 9 / 30, ymin = -Inf, ymax = Inf, fill = "#eef4f8") +
annotate("text", x = 3 + 25 / 31 + .08, y = 6.6, label = "26 Mar: bridge collapse", hjust = 0, size = 3.4, colour = muted) +
annotate("text", x = 6 + 9 / 30 + .08, y = 6.1, label = "10 Jun: channel reopened", hjust = 0, size = 3.4, colour = muted) +
geom_line(linewidth = 1.05) + geom_point(size = 2.5) +
annotate("text", x = 4.2, y = balt_cmp$y2024[4], label = paste0(sprintf("%.2f", balt_cmp$y2024[4]), " a day (", pct(balt_cmp$change[4]), ")"), hjust = 0, fontface = "bold", colour = blue, size = 4) +
scale_colour_manual(values = c(`2023` = brown, `2024` = blue)) + scale_linetype_manual(values = c(`2023` = "dashed", `2024` = "solid")) +
scale_shape_manual(values = c(`2023` = 15, `2024` = 16)) +
scale_x_continuous(breaks = 1:12, labels = month.abb) + scale_y_continuous(limits = c(0, 7)) +
labs(title = "Baltimore's ship calls fell 76%, then came back", subtitle = "Port calls per observed day, all vessel types", x = NULL, y = NULL,
caption = "Source: IMF PortWatch (AIS-derived). Event dates: U.S. Army Corps of Engineers.") +
theme_atlas()The full dashboard, Port Atlas, lets readers test these findings for any of the 12 ports:
Every chart has a written description (alt text) under it. A colour-vision mode adds hatching, point shapes and line styles so nothing depends on colour alone.
Everything needed to rerun this analysis is in this folder.
J221_Final_Project_Isaac_Xie.Rmd in RStudio and knit, or
run rmarkdown::render("J221_Final_Project_Isaac_Xie.Rmd").
It needs dplyr, readr, tidyr, ggplot2, scales, forcats, knitr and
lubridate. It reads only the CSVs in data/.data/ holds the cleaned
extracts and the lookup tables (ports, HS2 goods names, Census country
codes).scripts/ contains the Python scripts (standard library
only) that built those extracts and the dashboard:
build_data.py and build_operator_data.py
read the original Census ZIP files (PORTHS6MM2512.ZIP,
PORTHS6XM2512.ZIP, PORTHS6MM2412.ZIP,
PORTHS6XM2412.ZIP, linked in DATA_SOURCES.md).
They check every raw record and aggregate to HS2.build_app.py bundles the dashboard.
build_talk.py builds the talk slides.## [1] "R version 4.5.0 (2025-04-11)"
Course policy asks for the tool, the prompt, the reason and my evaluation.
To be added after the 3 December presentation: what guests said, and what I changed in response.