Load data for IMM product “Sawn: tropical hardwood”

productimm_ <- "Sawn: tropical hardwood"
productselected <- classificationimm %>% filter(productimm %in% productimm_)
# Convert to character because SQL is slower if 
# search is performed on character index, while using a numeric value
productselected <- as.character(productselected$productcode)
tableread <- "vld_comext_monthly"
periodstart <- 200000
con <- RMySQL::dbConnect(RMySQL::MySQL(), dbname = "tradeflows")
# swd <- eutradeflows::loadflows(con, 440799, periodstart = 200000)
swd <- tbl(con, tableread) %>%
    filter(productcode %in% productselected &
               period > periodstart) %>% 
    addproreppar2tbl(con,.) %>% 
    collect() 
message("Remove trailing white space in country name. Note: this should be fixed in the cleaning procedure")
## Remove trailing white space in country name. Note: this should be fixed in the cleaning procedure
# Base on https://stackoverflow.com/a/2261349
swd <- swd %>% 
    mutate(reporter = gsub("^\\s+|\\s+$","",reporter),
           partner = gsub("^\\s+|\\s+$","",partner))
# write.csv(filter(swd, reporter=="Utd. Kingdom"), "/tmp/swd.csv")

There are 249845 rows in the dataset.

Synchronised plots displaying value, quantity and weight

Uses the dygraph package. To zoom, select directly on the plot or move the sliders of the selectin window. Double click to zoom out.

The following actions are performed before plotting: Filter data to keep only imports from Indonesia into the UK. Aggregate Trade value, quantity and volume (separate data at the 8 digit level is available in the database). Transform the data frame into a time series “xts” object.

# This will be part of the datasetfiltered() reactive function
swd1 <- swd %>% 
    filter(reporter == "Utd. Kingdom" & partner== "Cameroon" & flowcode==1) %>% 
    group_by(reportercode, partnercode, flowcode, unitcode, period) %>% 
    summarise_at(c("tradevalue", "quantity","weight"), sum, na.rm = TRUE) %>% 
    ungroup() %>% 
    mutate(date = lubridate::parse_date_time(period, "ym")) %>% 
    select(date, tradevalue, quantity, weight) %>%
    data.frame()
# Convert to a time series
swd1xts <- xts::xts(swd1[,-1], order.by=swd1[,1])

dateWindow <- c("2012-01-01", "2018-01-01")
dygraph(swd1xts[,"quantity"], main = "Quantity", ylab = "M3", group="tf") %>% 
    dyRangeSelector(dateWindow=dateWindow) %>% 
    dyRoller(rollPeriod=12)
dygraph(swd1xts[,"tradevalue"], main = "Trade Value", ylab = "1000 €", group="tf") %>% 
    dyRangeSelector(dateWindow=dateWindow) %>% 
    dyRoller(rollPeriod=12)
dygraph(swd1xts[,"weight"], main = "Weight", ylab = "T", group="tf") %>% 
    dyRangeSelector(dateWindow=dateWindow) %>% 
    dyRoller(rollPeriod=12)

Products included under the IMM name “Sawn: tropical hardwood”

swd %>% 
    filter(reporter == "Utd. Kingdom" & partner== "Cameroon" & flowcode==1) %>% 
    ungroup() %>%  distinct(productcode, productdescription) %>% 
    knitr::kable()
productcode productdescription
44072191 MAHOGANY “SWIETENIA SPP.”, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM, PLANED (EXCL. END-JOINTED)
44072199 MAHOGANY “SWIETENIA SPP.”, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. PLANED, SANDED OR END-JOINTED)
44072299 VIROLA, IMBUIA AND BALSA, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. PLANED, SANDED OR END-JOINTED)
44072590 DARK RED MERANTI, LIGHT RED MERANTI AND MERANTI BAKAU, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. SUCH PRODUCTS PLANED, SANDED OR END-JOINTED)
44072690 WHITE LAUAN, WHITE MERANTI, WHITE SERAYA, YELLOW MERANTI AND ALAN, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. PLANED, SANDED OR END-JOINTED)
44072791 SAPELLI, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM, PLANED (EXCL. END-JOINTED)
44072799 SAPELLI, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. PLANED, SANDED OR END-JOINTED)
44072899 IROKO, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. PLANED, SANDED OR END-JOINTED)
44072925 KERUING, RAMIN, KAPUR, TEAK, JONGKONG, MERBAU, JELUTONG, KEMPAS, OKOUMÉ, OBECHE, SIPO, ACAJOU D’AFRIQUE, MAKORÉ, TIAMA, MANSONIA, ILOMBA, DIBÉTOU, LIMBA AND AZOBÉ, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM, PLANED (EXCL. END-JOINTED)
44072960 KERUING, RAMIN, KAPUR, TEAK, JONGKONG, MERBAU, JELUTONG, KEMPAS, OKOUMÉ, OBECHE, SIPO, ACAJOU D’AFRIQUE, MAKORÉ, TIAMA, MANSONIA, ILOMBA, DIBÉTOU, LIMBA, AZOBÉ, PALISSANDRE DE RIO, PALISSANDRE DE PARA AND PALISSANDRE DE ROSE, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. SUCH PRODUCTS PLANED, SANDED OR END-JOINTED)
44072983 ABURA, AFRORMOSIA, AKO, ANDIROBA, ANINGRE, AVODIRE, BALAU, BOSSE CLAIR, BOSSE FONCE, CATIVO, CEDRO, DABEMA, DOUSSIE, FRAMIRE, FREIJO, FROMAGER, FUMA, GERONGGANG, IPE, JABOTY, JEQUITIBA, KOSIPO, KOTIBE, KOTO, LOURO, MACARANDUBA, MAHOGANY (EXCL. “SWIETENIA SPP.”), MANDIOQUEIRA, MENGKULANG, MERAWAN, MERPAUH, MERSAWA, MOABI, NIANGON, NYATOH, ONZABILI, OREY, OVENGKOL, OZIGO, PADAUK, PALDAO, PALISSANDRE DE GUATEMALA, PAU AMARELO, PAU MARFIM, PULAI, PUNAH, QUARUBA, SAQUI-SAQUI, SEPETIR, SUCUPIRA, SUREN, TAUARI, TOLA, KERUING, RAMIN, KAPUR, TEAK, JONGKONG, MERBAU, JELUTONG, KEMPAS, OKOUME, OBECHE, SIPO, ACAJOU D’AFRIQUE, MAKORE, TIAMA, MANSONIA, ILOMBA, DIBETOU, LIMBA AND AZOBE, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM, PLANED (EXCL. END-JOINTED)
44072995 ABURA, AFRORMOSIA, AKO, ANDIROBA, ANINGRE, AVODIRE, BALAU, BOSSE CLAIR, BOSSE FONCE, CATIVO, CEDRO, DABEMA, DOUSSIE, FRAMIRE, FREIJO, FROMAGER, FUMA, GERONGGANG, IPE, JABOTY, JEQUITIBA, KOSIPO, KOTIBE, KOTO, LOURO, MACARANDUBA, MAHOGANY (EXCL. “SWIETENIA SPP.”), MANDIOQUEIRA, MENGKULANG, MERAWAN, MERPAUH, MERSAWA, MOABI, NIANGON, NYATOH, ONZABILI, OREY, OVENGKOL, OZIGO, PADAUK, PALDAO, PALISSANDRE DE GUATEMALA, PAU AMARELO, PAU MARFIM, PULAI, PUNAH, QUARUBA, SAQUI-SAQUI, SEPETIR, SUCUPIRA, SUREN, TAUARI, TOLA, KERUING, RAMIN, KAPUR, TEAK, JONGKONG, MERBAU, JELUTONG, KEMPAS, OKOUME, OBECHE, SIPO, ACAJOU D’AFRIQUE, MAKORE, TIAMA, MANSONIA, ILOMBA, DIBETOU, LIMBA, AZOBE, PALISSANDRE DE RIO, PALISSANDRE DE PARA AND PALISSANDRE DE ROSE, SAWN OR CHIPPED LENGTHWISE, SLICED OR PEELED, OF A THICKNESS OF > 6 MM (EXCL. END-JOINTED, PLANED AND SANDED)