Processing NZ Trade data

NZtrade.R

library(readr)
library(dplyr)
library(tidyr)
library(ggplot2)
library(lubridate)
library(RcppRoll)
source("~/theme.R")

datafiles <- list.files(pattern="csv$")
# remove the older provisional files that I have the directory
# to just work with the current one
listoffiles <- lapply(datafiles[-1*c(15,17,20)], read_csv, col_types=cols(.default=col_character()))

dfiles <- bind_rows(listoffiles)
# inconsistent column names over time

cleaned <- dfiles |> 
  mutate(YearMonth = ifelse(is.na(month),Month, month),
         HScode = as.numeric(ifelse(is.na(hs), `Harmonised System Code`, hs)),
         HSdesc = ifelse(is.na(hs_desc), `Harmonised System Description`, hs_desc),
         countryName = ifelse(is.na(country), Country, country),
         FOBexport = ifelse(is.na(Export_FOB), `Exports ($NZD fob)`, Export_FOB),
         QTYexport = ifelse(is.na(Export_Qty), `Exports Qty`, Export_Qty),
         FOBreexport = ifelse(is.na(Re_export_FOB), `Re-exports ($NZD fob)`, Re_export_FOB),
         QTYreexport = ifelse(is.na(Re_export_Qty), `Re-exports Qty`, Re_export_Qty),
         FOBtotal = ifelse(is.na(total_export_FOB), `Total Exports ($NZD fob)`, total_export_FOB),
         QTYtotal = ifelse(is.na(total_export_qty), `Total Exports Qty`, total_export_qty)
         ) |> 
  select(YearMonth:QTYtotal) |> 
  mutate(datey = substr(YearMonth,1,4),
         datemnt= substr(YearMonth,5,6),
    dateymd = ymd(paste0(datey,"-",datemnt,"-","15")),
         priceNZD= as.numeric(gsub(",","",FOBexport)),
         qty= as.numeric(gsub(",","",QTYexport)))

bigspendsUS <- cleaned |> 
  filter(countryName == "United States of America") |> 
  summarise(.by=c(HScode, HSdesc),
            alltimeFOB = sum(priceNZD)) |> 
  arrange(desc(alltimeFOB)) |> 
  slice(1:4)

bigquantUS <- cleaned |> 
  filter(countryName == "United States of America") |> 
  summarise(.by=c(HScode, HSdesc),
            alltimeQTY = sum(qty)) |> 
  arrange(desc(alltimeQTY)) |> 
  slice(1:4)

top4 <- cleaned |> filter(HScode %in% bigspendsUS$HScode) |> 
  mutate(isUS = ifelse(countryName == "United States of America", "US", "all others")) |> 
  summarise(.by=c(dateymd, isUS, HScode),
            value = sum(priceNZD),
            quantity = sum(qty)) |> 
  arrange(isUS, HScode, dateymd) |> 
  mutate(unitValue = value/quantity) |> 
  group_by(isUS, HScode) |> 
  mutate(rolling12value = roll_meanr(value,12),
         rolling12quantity = roll_meanr(quantity,12),
         rolling12unitvalue = roll_meanr(unitValue,12),
         Value = 100*rolling12value/rolling12value[12],
         Quantity = 100*rolling12quantity/rolling12quantity[12],
         UnitValue = 100*rolling12unitvalue/rolling12unitvalue[12]) |> 
  ungroup() |> 
  filter(!is.na(UnitValue)) |> 
  select(isUS, HScode, dateymd, Value, Quantity, UnitValue) |> 
  pivot_longer(names_to = "Measure", values_to = "PercentDec15", Value:UnitValue) |> 
  mutate(Measure = factor(Measure, levels=c("Quantity", "Value", "UnitValue")))

ggplot(top4,aes(x=dateymd, y=PercentDec15, colour=isUS)) +
  geom_hline(yintercept = c(100,200), colour=borders) + 
  geom_point(size=0.3)+
  labs(title="Rolling 12 Month NZ exports, as percentage of Dec 2015, Top aggregate value subcategories",
       subtitle="202300001 : Meat, bovine, cow, steer and heifer, boneless, frozen,
202300011 : Meat, bovine, bull, boneless, frozen
404901900 : Dairy produce; natural milk constituents (excluding whey), concentrated or sweetened
2204211801 : Wine; still, (< 14% alcohol), white, in containers of a capacity not exceeding 750ml",
       y="Percentage of Dec 2015", x="",
       caption="source: StatsNZ Overseas merchandise trade datasets") +
  theme_dh() + facet_grid(HScode ~ Measure) +
  coord_cartesian(y=c(0,NA))+
  scale_colour_colorblind(name="Importer",
                          guide = guide_legend(override.aes = list(size = 3))) +
  scale_x_date(breaks = ymd(c("2016-01-01", "2020-01-01", "2024-01-01")),
               date_labels = "%Y") +
  scale_y_continuous(breaks=c(100,200))+
  theme(axis.title.x = element_blank(),
        axis.ticks.x.bottom = element_line(linewidth=0.1, colour="black"),
        axis.ticks.length.x.bottom = unit(2, "pt"),
        strip.text = element_text(size=5.9))
ggsave(filename="~/Desktop/trade.jpg",
       height=4.5, width = 8, dpi=150, units = "in", bg = "white")