Your data

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

Data vetting

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:

Findings and visualizations

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

Slope chart ranking 12 U.S. ports by 2025 trade value on the left and shipping weight on the right. Los Angeles falls from 1st to 4th, Houston rises from 2nd to 1st, Corpus Christi rises from 7th to 2nd, and Oakland falls from 10th to 12th.

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.

Finding 1: Oakland exports food

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%
oak_dest %>% transmute(destination = country_name, share = pct(share))
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())

Horizontal bar chart of Oakland's top six export goods by share of 2025 export value. Fruit and nuts 35.1 percent, up from 29.6 percent in 2024; meat 21.3 percent, down from 24.7 percent; then dairy, eggs and honey 5.0 percent, electrical equipment 4.2 percent, beverages 3.1 percent and machinery 2.7 percent.

  • What the data show: fruit and nuts (35.1%) and meat (21.3%) make up 56.4% of Oakland’s $20.3 billion in 2025 exports. Japan and South Korea take 31.6%. Fruit and nuts grew from 29.6% of the total in 2024.
  • Why it matters: Oakland is the smallest of the 12 ports by weight, but it is California farming’s export door.
  • How I did it: filtered the Census file to Oakland (port 2811) exports, summed value by HS2 goods chapter and by country, and divided by the port total. 2024 uses the same filters.

Finding 2: In Los Angeles, most boxes leave empty

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

Stacked horizontal bars for the Port of Los Angeles in 2025. Inbound containers total 5.32 million TEU and are almost all loaded. Outbound containers total 4.92 million TEU; 1.43 million are loaded and 3.49 million, or 70.9 percent, are empty.

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

Line chart of the empty share of outbound TEUs at the Port of Los Angeles by month, 2024 and 2025. Every month in both years is between about 60 and 76 percent; in 2025 the range is 66.6 to 74.5 percent.

  • What the data show: 70.9% of outbound TEUs in 2025 were empty (69.7% in 2024). Loaded imports outnumber loaded exports 3.7 to 1. No month in 2025 fell below 66.6%.
  • Why it matters: empty boxes still need cranes, trucks and yard space. A trade-value ranking misses that work completely.
  • How I did it: summed the port’s four primary monthly components and divided empty outbound by all outbound TEUs. These are separate counts; they do not follow one container through a round trip.

Finding 3: Baltimore’s ship calls fell 76%, then came back

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

Line chart of Baltimore port calls per day by month, 2024 compared with 2023. Both years run at about five calls a day, except 2024 falls to 1.30 calls a day in April, 76.2 percent below April 2023, then recovers to 2023 levels by August. A shaded band marks 26 March to 10 June 2024, from the Key Bridge collapse to the full reopening of the channel.

  • What the data show: in April 2024, after the Francis Scott Key Bridge collapsed on 26 March, Baltimore averaged 1.30 calls a day, 76.2% below April 2023. Container ship calls fell from 49 to 1; vehicle carriers (Ro/Ro) from 43 to 23. By August calls were back at 2023 levels (5.16 against 5.13).
  • Why it matters: the drop and the recovery both show in public data, without any port-reported figure.
  • How I did it: divided monthly calls by the number of observed days, then compared each 2024 month with the same month in 2023. This is a before-and-after description, not a causal estimate; some ships kept calling throughout.

The interactive visualization

The full dashboard, Port Atlas, lets readers test these findings for any of the 12 ports:

  • a U.S. map and a world map of trade partners;
  • value against weight;
  • loaded and empty containers;
  • vessel calls and time at berth;
  • ocean corridors.

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.

Replication

Everything needed to rerun this analysis is in this folder.

  1. The analysis (this memo). Open 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/.
  2. The data. data/ holds the cleaned extracts and the lookup tables (ports, HS2 goods names, Census country codes).
  3. Rebuilding the data from the raw downloads. 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.
sessionInfo()$R.version$version.string
## [1] "R version 4.5.0 (2025-04-11)"

AI use

Course policy asks for the tool, the prompt, the reason and my evaluation.

Feedback from the lightning talk

To be added after the 3 December presentation: what guests said, and what I changed in response.