MY-DATA607-Week06-Project2-Convoys
Michael Y.
October 7, 2018
Week 6 - Project 2 - Dataset 1: Military Convoys in WWII
##setwd("c:/users/Michael/DROPBOX/priv/CUNY/MSDS/201809-Fall/DATA607_Andy_Sabrina/Week06-Project 2")The assignment is as follows:
Choose any three of the “wide” datasets identified in the Week 6 [sic] Discussion items.
For each of the three chosen datasets:
(1) Create a .CSV file (or optionally, a MySQL database!) that includes all of the information above. You’re encouraged to use a “wide” structure similar to how the information appears above, so that you can practice tidying and transformations as described below.
Note: For reasons explained below, I am loading multiple (eight) tables from the web. It is easier to do this by putting each table into an individual Excel sheet, where all the sheets are joined together in an Excel Workbook, rather than in 8 separate .csv files .
(2) Read the information from your .CSV file into R, and use tidyr and dplyr as needed to tidy and transform your data.
(3) Perform analysis
(4) Your code should be in an R Markdown file, posted to rpubs.com, and should include narrative descriptions of your data cleanup work, analysis, and conclusions.
library(readr)
library(stringr)
library(tidyr)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(tibble)
library(ggplot2)
library(utils)
library(scales)##
## Attaching package: 'scales'## The following object is masked from 'package:readr':
##
## col_factorData Loading
Load the raw datafile :
The data comes from the following webpage, as suggested by Romerl:
https://en.wikipedia.org/wiki/List_of_Allied_convoys_during_World_War_II_by_region
This webpage contains eight tables, each corresponding to a different part of the globe.
Because we have not yet covered web scraping, I don’t know how to read and parse the 8 tables directly from the Wikipedia page. So, instead I used functionality available within Excel to extract each of the 8 tables into a separate sheet within an Excel workbook. I have loaded this excel workbook into my github, from which it can be extracted as below.
I then loaded these 8 tables into R using library XLConnect, where they form a list of 8 dataframes.
I then proceed to perform the data cleanup, merging, etc., from this point.
While it would have been possible to write each of the eight tables back into an individual .csv file and then read each back in to R, that would create excess messiness. As given, the tables can’t be put into an individual .csv because the headers are not identical (specifically, the second column, which details the convoy routes, is region-specific.)
Military convoy tables (suggested by Romerl)
#setwd("C:/Users/Michael/Dropbox/priv/CUNY/MSDS/201809-Fall/DATA607_Andy_Sabrina/Week06-Project 2/Convoys")
### The excel workbook "EightConvoys.xlsx" contains 8 sheets, each of which is a table from the URL listing military convoys
### https://en.wikipedia.org/wiki/List_of_Allied_convoys_during_World_War_II_by_regionThere are 8 tables in the Wikipedia page, which are named as follows:
ConvoyTableNames <- c(
'01-European Coastal Atlantic Convoys',
'02-North Atlantic Convoys',
'03-North American Coastal and Caribbean Convoys',
'04-Mediterranean and North African Coastal Convoys',
'05-South Atlantic Convoys',
'06-Indian Ocean Convoys',
'07-Pacific Convoys',
'08-Normandy Invasion Convoys'
)Use the library “XLConnect” to load up the Excel workbook into R, creating a separate dataframe for each of the 8 tables
### The package "XLConnect" easily loads up an Excel Workbook with multiple sheets, putting each sheet into a dataframe, and returning a list contains all such dfs
### If the package "XLConnect" is not already installed on this computer, then install it
if (is.na(installed.packages()[,"Package"]["XLConnect"])) {
install.packages("XLConnect")
}
### load the library XLConnect
require(XLConnect) ## Loading required package: XLConnect## Loading required package: XLConnectJars## XLConnect 0.2-15 by Mirai Solutions GmbH [aut],
## Martin Studer [cre],
## The Apache Software Foundation [ctb, cph] (Apache POI),
## Graph Builder [ctb, cph] (Curvesapi Java library)## http://www.mirai-solutions.com
## https://github.com/miraisolutions/xlconnectRead the excel workbook into R using loadWorkbook from XLConnect .
Note that the Excel sheets are initially named Sheet1, Sheet2, … Sheet8 .
### if loading the file from my local machine:
### wb <- loadWorkbook("Convoys/EightConvoys.xlsx")
### If retrieving the files from my Github:
sourcefile = "https://raw.githubusercontent.com/myampol/MY607/master/EightConvoys.xlsx"
destfile = "c:/temp/EightConvoys.xlsx"
download.file(sourcefile,destfile,mode="wb")
### load the entire Excel workbook into R
wb <- loadWorkbook(destfile)Use readWorksheet from XLConnect to create a list which captures each of the 8 sheets into an individual dataframe (again, the sheets are still named Sheet1, Sheet2,…,Sheet8)
lst = readWorksheet(wb, sheet = getSheets(wb))Have a look at the structure of the list
str(lst)## List of 8
## $ Sheet1:'data.frame': 39 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:39] "BB" "BC" "BD" "BK" ...
## ..$ Coastal.Convoy.Routes: chr [1:39] "Belfast or River Clyde to Bristol Channel" "Bristol Channel to Bay of Biscay" "White Sea to Dikson Island" "White Sea to Kola Inlet" ...
## ..$ First.Sailing : chr [1:39] "1940 from Belfast - 1945 from Clyde" NA "September 1943" "Summer 1941" ...
## ..$ Last.Sailing : chr [1:39] "1943 from Belfast - 1945 from Clyde" NA NA NA ...
## ..$ Number.of.Convoys : num [1:39] NA NA NA NA NA NA NA NA NA NA ...
## ..$ Notes : chr [1:39] NA "outward and return convoys used same number" NA NA ...
## $ Sheet2:'data.frame': 36 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:36] "AT" "BHX" "CK" "CT" ...
## ..$ North.Atlantic.Routes: chr [1:36] "United States to British Isles" "Bermuda to Liverpool" "Charleston, South Carolina to British Isles" "British Isles to Canada" ...
## ..$ First.Sailing : chr [1:36] "March 1942" "May 1940" "1944" "1941" ...
## ..$ Last.Sailing : chr [1:36] "1945" "March 1941" "1944" "1941" ...
## ..$ Number.of.Convoys : chr [1:36] NA "97 (# 41-137)" NA NA ...
## ..$ Notes : chr [1:36] "troopships" "sailed from Bermuda and merged with same number HX convoy at sea" "rarely used" "troopships" ...
## $ Sheet3:'data.frame': 65 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:65] "AH" "ARG" "AW" "BS" ...
## ..$ North.American.Coastal.and.Caribbean.Routes: chr [1:65] "Aruba to Halifax Harbour" "Boston to Argentia, Newfoundland" "Aruba to Curaçao" "Corner Brook, Newfoundland to Sydney, Nova Scotia" ...
## ..$ First.Sailing : chr [1:65] "July 1942" NA NA NA ...
## ..$ Last.Sailing : chr [1:65] "September 1942" NA NA NA ...
## ..$ Number.of.Convoys : logi [1:65] NA NA NA NA NA NA ...
## ..$ Notes : chr [1:65] "a brief tanker series" "USN convoys" "local tanker traffic" "a brief series" ...
## $ Sheet4:'data.frame': 73 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:73] "AC" "AG" "AH" "AN" ...
## ..$ Mediterranean.and.North.African.Coastal.Routes: chr [1:73] "Alexandria to Tobruk" "Alexandria to Greece" "Augusta, Sicily to heel of Italy" "Alexandria to Piraeus" ...
## ..$ First.Sailing : chr [1:73] "1941" "1941" "September 1943" "1940" ...
## ..$ Last.Sailing : chr [1:73] "1941" "1941" NA "1941" ...
## ..$ Number.of.Convoys : logi [1:73] NA NA NA NA NA NA ...
## ..$ Notes : chr [1:73] NA NA NA NA ...
## $ Sheet5:'data.frame': 53 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:53] "AS" "BF" "BRN" "BT" ...
## ..$ South.Atlantic.Routes: chr [1:53] "United States to Suez Canal" "Bahia to Freetown" "Bahia to Recife northward" "Bahia to Trinidad" ...
## ..$ First.Sailing : chr [1:53] "1942" "1943" "late 1942" "November 1942" ...
## ..$ Last.Sailing : chr [1:53] "1942" NA NA "July 1943" ...
## ..$ Number.of.Convoys : logi [1:53] NA NA NA NA NA NA ...
## ..$ Notes : chr [1:53] "via Freetown" "USN escorts" NA NA ...
## $ Sheet6:'data.frame': 65 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:65] "AB" "ABF" "AJ" "AK" ...
## ..$ Indian.Ocean.Routes: chr [1:65] "Aden to Mumbai" "Aden to Mumbai" "Aden to Colombo" "Aden to Kilindini Harbour" ...
## ..$ First.Sailing : chr [1:65] "November 1942" NA NA NA ...
## ..$ Last.Sailing : chr [1:65] NA NA NA NA ...
## ..$ Number.of.Convoys : num [1:65] NA NA NA NA NA NA NA NA NA NA ...
## ..$ Notes : chr [1:65] NA "fast troopship convoys" NA NA ...
## $ Sheet7:'data.frame': 47 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:47] "AN" "BG" "BN" "BT" ...
## ..$ Pacific.Routes : chr [1:47] "Admiralty Islands to New Guinea" "Milne Bay to New Guinea (OR) Brisbane to Gladstone, Queensland" "New Britain to New Guinea" "Sydney to United States (OR) Brisbane to Townsville, Queensland" ...
## ..$ First.Sailing : chr [1:47] NA NA NA "1943 (Sydney to US)" ...
## ..$ Last.Sailing : chr [1:47] NA NA NA "1944 (Sydney to US)" ...
## ..$ Number.of.Convoys: logi [1:47] NA NA NA NA NA NA ...
## ..$ Notes : chr [1:47] NA NA NA "Sydney to US were troop convoys" ...
## $ Sheet8:'data.frame': 53 obs. of 6 variables:
## ..$ Code.Prefix : chr [1:53] "ATM" "BEC" "COC" "EBC" ...
## ..$ Normandy.Invasion.Convoys: chr [1:53] "Antwerp to River Thames" "Bristol Channel to France" "Plymouth to Brittany" "Bristol Channel to France" ...
## ..$ First.Sailing : chr [1:53] "late 1944" "June 1944" "late 1944" "June 1944" ...
## ..$ Last.Sailing : chr [1:53] "1945" "October 1944" "early 1945" "October 1944" ...
## ..$ Number.of.Convoys : num [1:53] NA NA NA NA 1 NA NA 2 2 NA ...
## ..$ Notes : chr [1:53] NA NA NA NA ...Tidy and transform the data
The header of the second column (*.Routes , except for the final table) in each table differs; each should correspond to the the name of the respective Convoy for that part of the world.
Check the listing to ensure that we haven’t gotten the above ConvoyTableNames out-of-sequence:
for (i in 1:8) print(paste(ConvoyTableNames[i], "|", names(lst[[i]][2])))## [1] "01-European Coastal Atlantic Convoys | Coastal.Convoy.Routes"
## [1] "02-North Atlantic Convoys | North.Atlantic.Routes"
## [1] "03-North American Coastal and Caribbean Convoys | North.American.Coastal.and.Caribbean.Routes"
## [1] "04-Mediterranean and North African Coastal Convoys | Mediterranean.and.North.African.Coastal.Routes"
## [1] "05-South Atlantic Convoys | South.Atlantic.Routes"
## [1] "06-Indian Ocean Convoys | Indian.Ocean.Routes"
## [1] "07-Pacific Convoys | Pacific.Routes"
## [1] "08-Normandy Invasion Convoys | Normandy.Invasion.Convoys"The correspondence between the above pairs of names indicate that we are indeed loading up the sheets (Sheet1, Sheet2, etc.) in the correct sequence.
Rename the 8 dataframes to reflect which Convoy each dataframe represents:
names(lst) <- ConvoyTableNamesHave a look at the head of each table:
for (i in names(lst)) {
print(i)
print(head(lst[[i]]))
#####print(knitr::kable(head(lst[[i]]),format="markdown"))
print("_____________________________________________________________________")
}## [1] "01-European Coastal Atlantic Convoys"
## Code.Prefix Coastal.Convoy.Routes
## 1 BB Belfast or River Clyde to Bristol Channel
## 2 BC Bristol Channel to Bay of Biscay
## 3 BD White Sea to Dikson Island
## 4 BK White Sea to Kola Inlet
## 5 BTC Bristol Channel to River Thames
## 6 CE St. Helens Roads to Southend-on-Sea
## First.Sailing Last.Sailing
## 1 1940 from Belfast - 1945 from Clyde 1943 from Belfast - 1945 from Clyde
## 2 <NA> <NA>
## 3 September 1943 <NA>
## 4 Summer 1941 <NA>
## 5 1944 1945
## 6 <NA> <NA>
## Number.of.Convoys Notes
## 1 NA <NA>
## 2 NA outward and return convoys used same number
## 3 NA <NA>
## 4 NA <NA>
## 5 NA <NA>
## 6 NA <NA>
## [1] "_____________________________________________________________________"
## [1] "02-North Atlantic Convoys"
## Code.Prefix North.Atlantic.Routes
## 1 AT United States to British Isles
## 2 BHX Bermuda to Liverpool
## 3 CK Charleston, South Carolina to British Isles
## 4 CT British Isles to Canada
## 5 CU Caribbean (later New York City) to Liverpool
## 6 GUF Mediterranean to Chesapeake Bay
## First.Sailing Last.Sailing Number.of.Convoys
## 1 March 1942 1945 <NA>
## 2 May 1940 March 1941 97 (# 41-137)
## 3 1944 1944 <NA>
## 4 1941 1941 <NA>
## 5 20 March 1943 30 May 1945 73
## 6 29 November 1942 16 April 1945 22
## Notes
## 1 troopships
## 2 sailed from Bermuda and merged with same number HX convoy at sea
## 3 rarely used
## 4 troopships
## 5 14-knot convoys of tankers with some fast cargo ships
## 6 faster ships
## [1] "_____________________________________________________________________"
## [1] "03-North American Coastal and Caribbean Convoys"
## Code.Prefix North.American.Coastal.and.Caribbean.Routes
## 1 AH Aruba to Halifax Harbour
## 2 ARG Boston to Argentia, Newfoundland
## 3 AW Aruba to Curaçao
## 4 BS Corner Brook, Newfoundland to Sydney, Nova Scotia
## 5 BW Sydney, Nova Scotia to St. John's, Newfoundland
## 6 BX Boston to Halifax Harbour
## First.Sailing Last.Sailing Number.of.Convoys Notes
## 1 July 1942 September 1942 NA a brief tanker series
## 2 <NA> <NA> NA USN convoys
## 3 <NA> <NA> NA local tanker traffic
## 4 <NA> <NA> NA a brief series
## 5 1942 <NA> NA <NA>
## 6 21 March 1942 22 May 1945 NA <NA>
## [1] "_____________________________________________________________________"
## [1] "04-Mediterranean and North African Coastal Convoys"
## Code.Prefix Mediterranean.and.North.African.Coastal.Routes
## 1 AC Alexandria to Tobruk
## 2 AG Alexandria to Greece
## 3 AH Augusta, Sicily to heel of Italy
## 4 AN Alexandria to Piraeus
## 5 AP British Isles to Egypt
## 6 ARM local Mediterranean
## First.Sailing Last.Sailing Number.of.Convoys Notes
## 1 1941 1941 NA <NA>
## 2 1941 1941 NA <NA>
## 3 September 1943 <NA> NA <NA>
## 4 1940 1941 NA <NA>
## 5 summer 1940 summer 1940 NA troopships
## 6 <NA> <NA> NA little used
## [1] "_____________________________________________________________________"
## [1] "05-South Atlantic Convoys"
## Code.Prefix South.Atlantic.Routes First.Sailing
## 1 AS United States to Suez Canal 1942
## 2 BF Bahia to Freetown 1943
## 3 BRN Bahia to Recife northward late 1942
## 4 BT Bahia to Trinidad November 1942
## 5 CF Cape Town to British Isles via west Africa <NA>
## 6 CN Cape Town northward to dispersal <NA>
## Last.Sailing Number.of.Convoys Notes
## 1 1942 NA via Freetown
## 2 <NA> NA USN escorts
## 3 <NA> NA <NA>
## 4 July 1943 NA <NA>
## 5 <NA> NA little used
## 6 <NA> NA <NA>
## [1] "_____________________________________________________________________"
## [1] "06-Indian Ocean Convoys"
## Code.Prefix Indian.Ocean.Routes First.Sailing
## 1 AB Aden to Mumbai November 1942
## 2 ABF Aden to Mumbai <NA>
## 3 AJ Aden to Colombo <NA>
## 4 AK Aden to Kilindini Harbour <NA>
## 5 AKD Aden to Durban via Kilindini Harbour September 1943
## 6 AM Chittagong to Chennai <NA>
## Last.Sailing Number.of.Convoys Notes
## 1 <NA> NA <NA>
## 2 <NA> NA fast troopship convoys
## 3 <NA> NA <NA>
## 4 <NA> NA <NA>
## 5 <NA> NA <NA>
## 6 <NA> NA <NA>
## [1] "_____________________________________________________________________"
## [1] "07-Pacific Convoys"
## Code.Prefix
## 1 AN
## 2 BG
## 3 BN
## 4 BT
## 5 BV
## 6 CO
## Pacific.Routes
## 1 Admiralty Islands to New Guinea
## 2 Milne Bay to New Guinea (OR) Brisbane to Gladstone, Queensland
## 3 New Britain to New Guinea
## 4 Sydney to United States (OR) Brisbane to Townsville, Queensland
## 5 Brisbane to Townsville, Queensland
## 6 Newcastle, New South Wales to Melbourne and Adelaide
## First.Sailing Last.Sailing Number.of.Convoys
## 1 <NA> <NA> NA
## 2 <NA> <NA> NA
## 3 <NA> <NA> NA
## 4 1943 (Sydney to US) 1944 (Sydney to US) NA
## 5 <NA> <NA> NA
## 6 1942 1942 NA
## Notes
## 1 <NA>
## 2 <NA>
## 3 <NA>
## 4 Sydney to US were troop convoys
## 5 <NA>
## 6 <NA>
## [1] "_____________________________________________________________________"
## [1] "08-Normandy Invasion Convoys"
## Code.Prefix Normandy.Invasion.Convoys First.Sailing Last.Sailing
## 1 ATM Antwerp to River Thames late 1944 1945
## 2 BEC Bristol Channel to France June 1944 October 1944
## 3 COC Plymouth to Brittany late 1944 early 1945
## 4 EBC Bristol Channel to France June 1944 October 1944
## 5 EBM Bristol Channel to France June 1944 June 1944
## 6 ECM Falmouth, Cornwall to France June 1944 early July 1944
## Number.of.Convoys Notes
## 1 NA <NA>
## 2 NA <NA>
## 3 NA <NA>
## 4 NA <NA>
## 5 1 motor transport convoy
## 6 NA <NA>
## [1] "_____________________________________________________________________"We need to perform various operations on the tables before we can concatentate them together.
When XLConnect read in each table, it made nearly everything type character, with the exception of the column “Number.of.Convoys”.
For some tables, everything in this column was blank, so it was assigned type logical.
For some other tables, there were numerical values for a few entries, so the column was assigned type numeric.
And, one table (#02) includes entries containing parentheses, hashmarks, and hyphens, so it was loaded as type character:
for (i in names(lst)) {
cat(i,"\n")
cat(str(lst[[i]]$Number.of.Convoys))
cat(class(lst[[i]]$Number.of.Convoys),"\n")
cat("____________________________________________________________________\n")
}## 01-European Coastal Atlantic Convoys
## num [1:39] NA NA NA NA NA NA NA NA NA NA ...
## numeric
## ____________________________________________________________________
## 02-North Atlantic Convoys
## chr [1:36] NA "97 (# 41-137)" NA NA "73" "22" "92" "89" "377" "17" ...
## character
## ____________________________________________________________________
## 03-North American Coastal and Caribbean Convoys
## logi [1:65] NA NA NA NA NA NA ...
## logical
## ____________________________________________________________________
## 04-Mediterranean and North African Coastal Convoys
## logi [1:73] NA NA NA NA NA NA ...
## logical
## ____________________________________________________________________
## 05-South Atlantic Convoys
## logi [1:53] NA NA NA NA NA NA ...
## logical
## ____________________________________________________________________
## 06-Indian Ocean Convoys
## num [1:65] NA NA NA NA NA NA NA NA NA NA ...
## numeric
## ____________________________________________________________________
## 07-Pacific Convoys
## logi [1:47] NA NA NA NA NA NA ...
## logical
## ____________________________________________________________________
## 08-Normandy Invasion Convoys
## num [1:53] NA NA NA NA 1 NA NA 2 2 NA ...
## numeric
## ____________________________________________________________________(1) Before we can concatenate all 8 tables together, we have to cast the above column to same data type (character) because we may get an error if we attempt to combine the tables without having done so because relying on R to perform the casting will only work if we start with the table containing character data and append the other tables to it – automatic casting of numeric or logical to character will succeed, but the process will fail if trying to cast character to numeric given the specific data.
(2) We want to add a column which identifies which of the 8 regions each row corresponds to, which we will do using add_column from tibble.
(3) Additionally, in order to combine all the various “*.Routes" columns (and, in the final table, the equivalent column named “Normandy.Invasion.Convoy”) into a single column, we need to make every table have the same header or else we will have lots of separate columns for each type of routes, which is not what we want.
Once the above changes are made, we can use bind_rows from dplyr to append the dataframes together. Here we do this by creating an empty dataframe called AllConvoys and then appending each table as we loop through:
AllConvoys=NULL
for (df_name in names(lst)) {
### When each sheet was initially read in, the type assigned to Number.of.Convoys varied.
### We cannot append all the tables together if the type of this column is not uniform
### therefore, forcibly cast all such items to CHARACTER
lst[[df_name]]$Number.of.Convoys <- as.character(lst[[df_name]]$Number.of.Convoys)
### expand each dataframe by inserting a "Region" column which contains the region name
lst[[df_name]] <- add_column(lst[[df_name]],Region=df_name,.before=1)
### Because we put the region name at the beginning (i.e., leftmost-column),
### the column containing the various Routes has shifted from column 2 to column 3.
### In order to aggregate all such columns together across the eight dataframes,
### rename them all "Routes", as the new "Region" column contains the necessary information to identify the area
names(lst[[df_name]])[3] <- "Routes"
### concatentate all eight tables together using bind_rows
### here we're doing it one-at-a-time by appending each table onto the previous ones
### note that this would fail without the type adjustment above on Number.of.Convoys
AllConvoys <- bind_rows(AllConvoys,lst[[df_name]])
###cat("\nEnd of processing ",df_name)
}We now have a table which contains 431 rows. Here are views of selections from the table:
dim(AllConvoys)## [1] 431 7head(AllConvoys)## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys BB
## 2 01-European Coastal Atlantic Convoys BC
## 3 01-European Coastal Atlantic Convoys BD
## 4 01-European Coastal Atlantic Convoys BK
## 5 01-European Coastal Atlantic Convoys BTC
## 6 01-European Coastal Atlantic Convoys CE
## Routes
## 1 Belfast or River Clyde to Bristol Channel
## 2 Bristol Channel to Bay of Biscay
## 3 White Sea to Dikson Island
## 4 White Sea to Kola Inlet
## 5 Bristol Channel to River Thames
## 6 St. Helens Roads to Southend-on-Sea
## First.Sailing Last.Sailing
## 1 1940 from Belfast - 1945 from Clyde 1943 from Belfast - 1945 from Clyde
## 2 <NA> <NA>
## 3 September 1943 <NA>
## 4 Summer 1941 <NA>
## 5 1944 1945
## 6 <NA> <NA>
## Number.of.Convoys Notes
## 1 <NA> <NA>
## 2 <NA> outward and return convoys used same number
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 <NA> <NA>
## 6 <NA> <NA>tail(AllConvoys)## Region Code.Prefix
## 426 08-Normandy Invasion Convoys WEP
## 427 08-Normandy Invasion Convoys WFM
## 428 08-Normandy Invasion Convoys WMP
## 429 08-Normandy Invasion Convoys WNC
## 430 08-Normandy Invasion Convoys WNL
## 431 08-Normandy Invasion Convoys WVP
## Routes First.Sailing Last.Sailing
## 426 Isle of Wight to Cherbourg-Octeville December 1944 December 1944
## 427 <NA> October 1944 November 1944
## 428 Isle of Wight to Arromanches-les-Bains November 1944 December 1944
## 429 Isle of Wight to Le Havre December 1944 May 1945
## 430 Isle of Wight to France April 1945 May 1945
## 431 Isle of Wight to France December 1944 May 1945
## Number.of.Convoys Notes
## 426 <NA> <NA>
## 427 <NA> invasion convoys
## 428 <NA> <NA>
## 429 <NA> <NA>
## 430 <NA> <NA>
## 431 <NA> <NA>### Note that the below two subsetting/filtering methods select the same rows, but differ on the index numbering:
AllConvoys[seq(1,431,25),]## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys BB
## 26 01-European Coastal Atlantic Convoys NP
## 51 02-North Atlantic Convoys KJ
## 76 03-North American Coastal and Caribbean Convoys AH
## 101 03-North American Coastal and Caribbean Convoys HK
## 126 03-North American Coastal and Caribbean Convoys SH
## 151 04-Mediterranean and North African Coastal Convoys CG
## 176 04-Mediterranean and North African Coastal Convoys MW
## 201 04-Mediterranean and North African Coastal Convoys TSS
## 226 05-South Atlantic Convoys FB
## 251 05-South Atlantic Convoys STL
## 276 06-Indian Ocean Convoys BAF
## 301 06-Indian Ocean Convoys JC
## 326 06-Indian Ocean Convoys SR
## 351 07-Pacific Convoys MS
## 376 07-Pacific Convoys VK
## 401 08-Normandy Invasion Convoys FPP
## 426 08-Normandy Invasion Convoys WEP
## Routes
## 1 Belfast or River Clyde to Bristol Channel
## 26 British Isles to Norway
## 51 Kingston, Jamaica to United Kingdom
## 76 Aruba to Halifax Harbour
## 101 Houston, Texas to Key West, Florida
## 126 Sydney, Nova Scotia to Halifax Harbour
## 151 Casablanca to Gibraltar
## 176 Alexandria to Malta
## 201 assault convoys for Naples landings
## 226 Freetown to Bahia
## 251 Freetown to Lagos via Sekondi-Takoradi
## 276 Mumbai to Aden
## 301 Colombo to Kolkata
## 326 Kolkata to Yangon
## 351 Melbourne to Singapore
## 376 Sydney to Wellington
## 401 France to Portland Harbour
## 426 Isle of Wight to Cherbourg-Octeville
## First.Sailing
## 1 1940 from Belfast - 1945 from Clyde
## 26 April 1940
## 51 <NA>
## 76 July 1942
## 101 <NA>
## 126 <NA>
## 151 <NA>
## 176 <NA>
## 201 <NA>
## 226 <NA>
## 251 <NA>
## 276 <NA>
## 301 <NA>
## 326 1941
## 351 November 1941
## 376 <NA>
## 401 July 1944
## 426 December 1944
## Last.Sailing Number.of.Convoys
## 1 1943 from Belfast - 1945 from Clyde <NA>
## 26 May 1940 <NA>
## 51 <NA> <NA>
## 76 September 1942 <NA>
## 101 <NA> <NA>
## 126 <NA> <NA>
## 151 <NA> <NA>
## 176 <NA> <NA>
## 201 <NA> <NA>
## 226 <NA> <NA>
## 251 <NA> <NA>
## 276 <NA> <NA>
## 301 <NA> <NA>
## 326 1942 <NA>
## 351 February 1942 <NA>
## 376 <NA> <NA>
## 401 August 1944 <NA>
## 426 December 1944 <NA>
## Notes
## 1 <NA>
## 26 troop convoys
## 51 <NA>
## 76 a brief tanker series
## 101 <NA>
## 126 <NA>
## 151 <NA>
## 176 <NA>
## 201 <NA>
## 226 <NA>
## 251 <NA>
## 276 fast troopships
## 301 <NA>
## 326 <NA>
## 351 <NA>
## 376 <NA>
## 401 personnel convoys
## 426 <NA>filter(AllConvoys,row_number() %% 25==1)## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys BB
## 2 01-European Coastal Atlantic Convoys NP
## 3 02-North Atlantic Convoys KJ
## 4 03-North American Coastal and Caribbean Convoys AH
## 5 03-North American Coastal and Caribbean Convoys HK
## 6 03-North American Coastal and Caribbean Convoys SH
## 7 04-Mediterranean and North African Coastal Convoys CG
## 8 04-Mediterranean and North African Coastal Convoys MW
## 9 04-Mediterranean and North African Coastal Convoys TSS
## 10 05-South Atlantic Convoys FB
## 11 05-South Atlantic Convoys STL
## 12 06-Indian Ocean Convoys BAF
## 13 06-Indian Ocean Convoys JC
## 14 06-Indian Ocean Convoys SR
## 15 07-Pacific Convoys MS
## 16 07-Pacific Convoys VK
## 17 08-Normandy Invasion Convoys FPP
## 18 08-Normandy Invasion Convoys WEP
## Routes
## 1 Belfast or River Clyde to Bristol Channel
## 2 British Isles to Norway
## 3 Kingston, Jamaica to United Kingdom
## 4 Aruba to Halifax Harbour
## 5 Houston, Texas to Key West, Florida
## 6 Sydney, Nova Scotia to Halifax Harbour
## 7 Casablanca to Gibraltar
## 8 Alexandria to Malta
## 9 assault convoys for Naples landings
## 10 Freetown to Bahia
## 11 Freetown to Lagos via Sekondi-Takoradi
## 12 Mumbai to Aden
## 13 Colombo to Kolkata
## 14 Kolkata to Yangon
## 15 Melbourne to Singapore
## 16 Sydney to Wellington
## 17 France to Portland Harbour
## 18 Isle of Wight to Cherbourg-Octeville
## First.Sailing Last.Sailing
## 1 1940 from Belfast - 1945 from Clyde 1943 from Belfast - 1945 from Clyde
## 2 April 1940 May 1940
## 3 <NA> <NA>
## 4 July 1942 September 1942
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## 11 <NA> <NA>
## 12 <NA> <NA>
## 13 <NA> <NA>
## 14 1941 1942
## 15 November 1941 February 1942
## 16 <NA> <NA>
## 17 July 1944 August 1944
## 18 December 1944 December 1944
## Number.of.Convoys Notes
## 1 <NA> <NA>
## 2 <NA> troop convoys
## 3 <NA> <NA>
## 4 <NA> a brief tanker series
## 5 <NA> <NA>
## 6 <NA> <NA>
## 7 <NA> <NA>
## 8 <NA> <NA>
## 9 <NA> <NA>
## 10 <NA> <NA>
## 11 <NA> <NA>
## 12 <NA> fast troopships
## 13 <NA> <NA>
## 14 <NA> <NA>
## 15 <NA> <NA>
## 16 <NA> <NA>
## 17 <NA> personnel convoys
## 18 <NA> <NA>Most of the Routes are of the form “Origin” to “Destination”.
Extract the Origin and Destination into individual columns, but retain the original route so we can examine those for which such separation fails
AllConvoys <- extract(AllConvoys,
Routes,
into = c('Origin', 'Destination'),
'(.*)\\sto\\s(.*)',
remove=FALSE)
head(AllConvoys)## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys BB
## 2 01-European Coastal Atlantic Convoys BC
## 3 01-European Coastal Atlantic Convoys BD
## 4 01-European Coastal Atlantic Convoys BK
## 5 01-European Coastal Atlantic Convoys BTC
## 6 01-European Coastal Atlantic Convoys CE
## Routes Origin
## 1 Belfast or River Clyde to Bristol Channel Belfast or River Clyde
## 2 Bristol Channel to Bay of Biscay Bristol Channel
## 3 White Sea to Dikson Island White Sea
## 4 White Sea to Kola Inlet White Sea
## 5 Bristol Channel to River Thames Bristol Channel
## 6 St. Helens Roads to Southend-on-Sea St. Helens Roads
## Destination First.Sailing
## 1 Bristol Channel 1940 from Belfast - 1945 from Clyde
## 2 Bay of Biscay <NA>
## 3 Dikson Island September 1943
## 4 Kola Inlet Summer 1941
## 5 River Thames 1944
## 6 Southend-on-Sea <NA>
## Last.Sailing Number.of.Convoys
## 1 1943 from Belfast - 1945 from Clyde <NA>
## 2 <NA> <NA>
## 3 <NA> <NA>
## 4 <NA> <NA>
## 5 1945 <NA>
## 6 <NA> <NA>
## Notes
## 1 <NA>
## 2 outward and return convoys used same number
## 3 <NA>
## 4 <NA>
## 5 <NA>
## 6 <NA>tail(AllConvoys)## Region Code.Prefix
## 426 08-Normandy Invasion Convoys WEP
## 427 08-Normandy Invasion Convoys WFM
## 428 08-Normandy Invasion Convoys WMP
## 429 08-Normandy Invasion Convoys WNC
## 430 08-Normandy Invasion Convoys WNL
## 431 08-Normandy Invasion Convoys WVP
## Routes Origin
## 426 Isle of Wight to Cherbourg-Octeville Isle of Wight
## 427 <NA> <NA>
## 428 Isle of Wight to Arromanches-les-Bains Isle of Wight
## 429 Isle of Wight to Le Havre Isle of Wight
## 430 Isle of Wight to France Isle of Wight
## 431 Isle of Wight to France Isle of Wight
## Destination First.Sailing Last.Sailing Number.of.Convoys
## 426 Cherbourg-Octeville December 1944 December 1944 <NA>
## 427 <NA> October 1944 November 1944 <NA>
## 428 Arromanches-les-Bains November 1944 December 1944 <NA>
## 429 Le Havre December 1944 May 1945 <NA>
## 430 France April 1945 May 1945 <NA>
## 431 France December 1944 May 1945 <NA>
## Notes
## 426 <NA>
## 427 invasion convoys
## 428 <NA>
## 429 <NA>
## 430 <NA>
## 431 <NA>Let’s examine those items which didn’t have " to " in the Route
Failed_To_Extract <- filter(.data = AllConvoys, is.na(Origin))
Failed_To_Extract## Region Code.Prefix
## 1 04-Mediterranean and North African Coastal Convoys ARM
## 2 04-Mediterranean and North African Coastal Convoys CNF
## 3 04-Mediterranean and North African Coastal Convoys NCF
## 4 04-Mediterranean and North African Coastal Convoys NCS
## 5 04-Mediterranean and North African Coastal Convoys SBF
## 6 04-Mediterranean and North African Coastal Convoys SBM
## 7 04-Mediterranean and North African Coastal Convoys TJF
## 8 04-Mediterranean and North African Coastal Convoys TJM
## 9 04-Mediterranean and North African Coastal Convoys TJS
## 10 04-Mediterranean and North African Coastal Convoys TSF
## 11 04-Mediterranean and North African Coastal Convoys TSM
## 12 04-Mediterranean and North African Coastal Convoys TSS
## 13 05-South Atlantic Convoys LGE
## 14 05-South Atlantic Convoys LGW
## 15 05-South Atlantic Convoys OSS
## 16 05-South Atlantic Convoys PGE
## 17 07-Pacific Convoys NLY
## 18 08-Normandy Invasion Convoys EXP
## 19 08-Normandy Invasion Convoys WAP
## 20 08-Normandy Invasion Convoys WDC
## 21 08-Normandy Invasion Convoys WFM
## Routes Origin Destination
## 1 local Mediterranean <NA> <NA>
## 2 convoys for Allied invasion of Sicily <NA> <NA>
## 3 fast convoys for Allied invasion of Sicily <NA> <NA>
## 4 slow convoys for Allied invasion of Sicily <NA> <NA>
## 5 assault convoys for Allied invasion of Sicily <NA> <NA>
## 6 assault convoys for Allied invasion of Sicily <NA> <NA>
## 7 assault convoys for Allied invasion of Sicily <NA> <NA>
## 8 assault convoys for Allied invasion of Sicily <NA> <NA>
## 9 assault convoys for Allied invasion of Sicily <NA> <NA>
## 10 assault convoys for Naples landings <NA> <NA>
## 11 assault convoys for Naples landings <NA> <NA>
## 12 assault convoys for Naples landings <NA> <NA>
## 13 Lagos eastbound <NA> <NA>
## 14 Lagos westbound <NA> <NA>
## 15 Freetown southward <NA> <NA>
## 16 Pointe-Noire southward <NA> <NA>
## 17 Lingayen Gulf - Jayapura - Leyte <NA> <NA>
## 18 <NA> <NA> <NA>
## 19 <NA> <NA> <NA>
## 20 <NA> <NA> <NA>
## 21 <NA> <NA> <NA>
## First.Sailing Last.Sailing Number.of.Convoys
## 1 <NA> <NA> <NA>
## 2 <NA> <NA> <NA>
## 3 1943 1943 <NA>
## 4 1943 1943 <NA>
## 5 1943 1943 <NA>
## 6 1943 1943 <NA>
## 7 1943 1943 <NA>
## 8 1943 1943 <NA>
## 9 1943 1943 <NA>
## 10 <NA> <NA> <NA>
## 11 <NA> <NA> <NA>
## 12 <NA> <NA> <NA>
## 13 <NA> <NA> <NA>
## 14 <NA> <NA> <NA>
## 15 <NA> <NA> <NA>
## 16 <NA> <NA> <NA>
## 17 <NA> <NA> <NA>
## 18 June 1944 October 1944 <NA>
## 19 June 1944 October 1944 <NA>
## 20 September 1944 December 1944 <NA>
## 21 October 1944 November 1944 <NA>
## Notes
## 1 little used
## 2 special designation
## 3 <NA>
## 4 <NA>
## 5 <NA>
## 6 <NA>
## 7 <NA>
## 8 <NA>
## 9 <NA>
## 10 <NA>
## 11 <NA>
## 12 <NA>
## 13 local west African traffic
## 14 local west African traffic
## 15 southward extension of the corresponding OS convoy
## 16 local traffic
## 17 <NA>
## 18 invasion convoys
## 19 invasion convoys
## 20 invasion convoys
## 21 invasion convoysThere are 21 such rows where the Route is not of the form (Origin) to (Destination).
There is a column “Number.of.Convoys” which lists the number of times a particular route was used. Unfortunately it is not populated for most rows. There are a few rows where the entry includes a count as well as additional information (apparently a range of sequence numbers, e.g., “97 (# 41-137)”).
AllConvoys %>% filter(str_detect(string = Number.of.Convoys, pattern = "\\("))## Region Code.Prefix Routes Origin
## 1 02-North Atlantic Convoys BHX Bermuda to Liverpool Bermuda
## 2 02-North Atlantic Convoys JW Iceland to White Sea Iceland
## 3 02-North Atlantic Convoys RA White Sea to Scotland White Sea
## Destination First.Sailing Last.Sailing Number.of.Convoys
## 1 Liverpool May 1940 March 1941 97 (# 41-137)
## 2 White Sea 25 December 1942 12 May 1945 17 (# 51-67)
## 3 Scotland 30 December 1942 23 May 1945 17 (# 51-67)
## Notes
## 1 sailed from Bermuda and merged with same number HX convoy at sea
## 2 replaced PQ convoys
## 3 replaced QP convoysWe need to remove this extraneous detail from these items in order to be able to make any use of the counts. Additionally, we need to cast the values from character back to numeric.
AllConvoys <- AllConvoys %>%
mutate(Number.of.Convoys,
Number.of.Convoys=as.numeric(
gsub(pattern = " .*$",replacement = "",x=Number.of.Convoys)))Analysis
Plot the number of convoy routes per region:
AllConvoys %>%
select(Region) %>%
mutate(RegionNum=substr(Region,1,2)) %>%
ggplot(aes(x=factor(RegionNum), y=table(RegionNum)[RegionNum], fill=Region)) +
geom_bar(stat="identity", position="dodge")+
theme_minimal()+
geom_text(aes(label=as.integer(table(RegionNum)[RegionNum])),
vjust=1.6, color="black",
position = position_dodge(0.8), size=4) +
scale_x_discrete( expand = waiver(), position = "bottom")+
scale_y_continuous(name = waiver(), breaks = waiver(),
minor_breaks = waiver(), labels = waiver(), limits = NULL,
expand = waiver(), oob = censor, na.value = NA_real_,
trans = "identity", position = "left", sec.axis = waiver()) +
labs(x="",y="Number of Convoy Routes")+
ggtitle("World War II: Number of Allied Convoy Routes, by region")
Although there is field “Number.of.Convoys” which apparently counts the number of times a particular route was used, it is not populated for most rows. In particular, it is only populated for 33 rows out of 431.
CountOfConvoysDefined <- AllConvoys %>% filter(!is.na(Number.of.Convoys)) %>% count(x = .)
Total.Number.of.Convoys <- sum(AllConvoys$Number.of.Convoys,na.rm=T)Once the data is cleaned, the number of rows for which the Number.of.Convoys is not NA is 33 .
The totalsum of the number of convoys on such rows is 2842 .
These are such rows:
AllConvoys %>% filter(!is.na(Number.of.Convoys))## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys OA
## 2 01-European Coastal Atlantic Convoys OB
## 3 02-North Atlantic Convoys BHX
## 4 02-North Atlantic Convoys CU
## 5 02-North Atlantic Convoys GUF
## 6 02-North Atlantic Convoys GUS
## 7 02-North Atlantic Convoys HG
## 8 02-North Atlantic Convoys HX
## 9 02-North Atlantic Convoys HXF
## 10 02-North Atlantic Convoys JW
## 11 02-North Atlantic Convoys KMF
## 12 02-North Atlantic Convoys KMS
## 13 02-North Atlantic Convoys MKF
## 14 02-North Atlantic Convoys MKS
## 15 02-North Atlantic Convoys OG
## 16 02-North Atlantic Convoys ON
## 17 02-North Atlantic Convoys ONS
## 18 02-North Atlantic Convoys OS
## 19 02-North Atlantic Convoys PQ
## 20 02-North Atlantic Convoys QP
## 21 02-North Atlantic Convoys RA
## 22 02-North Atlantic Convoys RB
## 23 02-North Atlantic Convoys SC
## 24 02-North Atlantic Convoys SL
## 25 02-North Atlantic Convoys UC
## 26 02-North Atlantic Convoys UGF
## 27 02-North Atlantic Convoys UGS
## 28 06-Indian Ocean Convoys DM
## 29 06-Indian Ocean Convoys JMG
## 30 06-Indian Ocean Convoys MD
## 31 08-Normandy Invasion Convoys EBM
## 32 08-Normandy Invasion Convoys EMM
## 33 08-Normandy Invasion Convoys EMP
## Routes
## 1 River Thames (or Methil, Fife after July 1940) to Liverpool
## 2 Liverpool to the Atlantic Ocean
## 3 Bermuda to Liverpool
## 4 Caribbean (later New York City) to Liverpool
## 5 Mediterranean to Chesapeake Bay
## 6 Mediterranean to Chesapeake Bay
## 7 Gibraltar to Liverpool
## 8 Halifax Harbour (later New York City) to Liverpool
## 9 Halifax Harbour to Liverpool
## 10 Iceland to White Sea
## 11 Firth of Clyde to Mediterranean
## 12 Liverpool to Mediterranean
## 13 Mediterranean to Firth of Clyde or Liverpool
## 14 Mediterranean to Liverpool
## 15 Liverpool to Gibraltar
## 16 Liverpool to Halifax Harbour
## 17 Liverpool to Halifax Harbour
## 18 Liverpool to Sierra Leone
## 19 Iceland to White Sea
## 20 White Sea to Iceland
## 21 White Sea to Scotland
## 22 United States to British Isles
## 23 Sydney, Nova Scotia (or Halifax Harbour or New York City) to Liverpool
## 24 Sierra Leone to Liverpool
## 25 Liverpool to Caribbean (later New York City)
## 26 Chesapeake Bay to Mediterranean
## 27 Chesapeake Bay to Mediterranean
## 28 Durban to Malaya
## 29 assault convoy to Malaya
## 30 Madagascar to Durban
## 31 Bristol Channel to France
## 32 Belfast to France
## 33 Belfast to France
## Origin
## 1 River Thames (or Methil, Fife after July 1940)
## 2 Liverpool
## 3 Bermuda
## 4 Caribbean (later New York City)
## 5 Mediterranean
## 6 Mediterranean
## 7 Gibraltar
## 8 Halifax Harbour (later New York City)
## 9 Halifax Harbour
## 10 Iceland
## 11 Firth of Clyde
## 12 Liverpool
## 13 Mediterranean
## 14 Mediterranean
## 15 Liverpool
## 16 Liverpool
## 17 Liverpool
## 18 Liverpool
## 19 Iceland
## 20 White Sea
## 21 White Sea
## 22 United States
## 23 Sydney, Nova Scotia (or Halifax Harbour or New York City)
## 24 Sierra Leone
## 25 Liverpool
## 26 Chesapeake Bay
## 27 Chesapeake Bay
## 28 Durban
## 29 assault convoy
## 30 Madagascar
## 31 Bristol Channel
## 32 Belfast
## 33 Belfast
## Destination First.Sailing Last.Sailing
## 1 Liverpool 7 September 1939 24 October 1940
## 2 the Atlantic Ocean 7 September 1939 21 July 1941
## 3 Liverpool May 1940 March 1941
## 4 Liverpool 20 March 1943 30 May 1945
## 5 Chesapeake Bay 29 November 1942 16 April 1945
## 6 Chesapeake Bay 21 December 1942 27 May 1945
## 7 Liverpool 26 September 1939 19 September 1942
## 8 Liverpool 16 Sept 1939 23 May 1945
## 9 Liverpool 29 Sept 1939 12 February 1940
## 10 White Sea 25 December 1942 12 May 1945
## 11 Mediterranean 26 October 1942 23 May 1945
## 12 Mediterranean 22 October 1942 27 April 1945
## 13 Firth of Clyde or Liverpool 12 November 1942 4 June 1945
## 14 Liverpool 12 November 1942 25 May 1945
## 15 Gibraltar 2 October 1939 17 October 1943
## 16 Halifax Harbour 26 July 1941 27 May 1945
## 17 Halifax Harbour 15 March 1943 21 May 1945
## 18 Sierra Leone 24 July 1941 27 May 1945
## 19 White Sea 29 September 1941 2 September 1942
## 20 Iceland 28 September 1941 17 November 1942
## 21 Scotland 30 December 1942 23 May 1945
## 22 British Isles September 1942 September 1942
## 23 Liverpool 15 August 1940 26 May 1945
## 24 Liverpool 14 September 1939 25 November 1944
## 25 Caribbean (later New York City) 15 February 1943 3 June 1945
## 26 Mediterranean 24 October 1942 8 April 1945
## 27 Mediterranean 13 November 1942 28 May 1945
## 28 Malaya 1941 1942
## 29 Malaya September 1945 <NA>
## 30 Durban <NA> <NA>
## 31 France June 1944 June 1944
## 32 France June 1944 July 1944
## 33 France July 1944 July 1944
## Number.of.Convoys
## 1 234
## 2 345
## 3 97
## 4 73
## 5 22
## 6 92
## 7 89
## 8 377
## 9 17
## 10 17
## 11 45
## 12 98
## 13 45
## 14 103
## 15 95
## 16 307
## 17 51
## 18 131
## 19 18
## 20 15
## 21 17
## 22 1
## 23 177
## 24 178
## 25 71
## 26 22
## 27 95
## 28 3
## 29 1
## 30 1
## 31 1
## 32 2
## 33 2
## Notes
## 1 merged with OB convoy in the southwest approaches
## 2 merged with OA convoy in the southwest approaches - ON and OS convoys replaced OB convoys
## 3 sailed from Bermuda and merged with same number HX convoy at sea
## 4 14-knot convoys of tankers with some fast cargo ships
## 5 faster ships
## 6 slower ships
## 7 replaced by MKS convoys after Operation Torch
## 8 9-knot convoys for ships of sustained speeds less than 15 knots
## 9 fast sections of HX convoys
## 10 replaced PQ convoys
## 11 faster ships to the Mediterranean
## 12 slower ships to the Mediterranean - 1st 12 sailed independently - remainder sailed with OS convoys and detached west of Gibraltar
## 13 faster ships from the Mediterranean
## 14 slower ships from the Mediterranean- 1st 11 sailed independently - remainder merged with SL convoys west of Gibraltar
## 15 early sailings every 5th merged OA/OB convoy became an OG convoy at sea - later OG convoys sailed from Liverpool
## 16 replaced OB convoys for North American destinations - alternate convoys included slower ships until the ONS convoys started
## 17 slower ships westbound on the ON convoy route
## 18 replaced OB convoys for non-North American destinations - included KMS convoys detached west of Gibraltar
## 19 replaced by JW convoys
## 20 replaced by RA convoys
## 21 replaced QP convoys
## 22 small passenger steamers
## 23 7-knot convoys of eastbound ships too slow for the 9-knot HX convoys
## 24 merged with MKS convoys west of Gibraltar
## 25 14-knot convoys of tankers with some fast cargo ships
## 26 faster ships - (UGF-1) was the invasion force for Operation Torch
## 27 slower ships
## 28 <NA>
## 29 <NA>
## 30 <NA>
## 31 motor transport convoy
## 32 <NA>
## 33 <NA>This sums up the Number.of.Convoys by region, where such number is not NA:
TotalConvoysByRegion <- AllConvoys %>%
group_by(Region) %>%
summarise(total_convoys = sum(Number.of.Convoys, na.rm = T)) %>%
ungroup()
TotalConvoysByRegion ## # A tibble: 8 x 2
## Region total_convoys
## <chr> <dbl>
## 1 01-European Coastal Atlantic Convoys 579
## 2 02-North Atlantic Convoys 2253
## 3 03-North American Coastal and Caribbean Convoys 0
## 4 04-Mediterranean and North African Coastal Convoys 0
## 5 05-South Atlantic Convoys 0
## 6 06-Indian Ocean Convoys 5
## 7 07-Pacific Convoys 0
## 8 08-Normandy Invasion Convoys 5TotalConvoysByRegion %>%
mutate(RegionNum=substr(Region,1,2)) %>%
ggplot(aes(x=factor(RegionNum), y=total_convoys, fill=Region)) +
geom_bar(stat="identity", position="dodge")+
theme_minimal()+
geom_text(aes(label=total_convoys),
vjust=-0.2, color="black",
position = position_dodge(0.8), size=4) +
scale_x_discrete( expand = waiver(), position = "bottom")+
scale_y_continuous(name = waiver(), breaks = waiver(),
minor_breaks = waiver(), labels = waiver(), limits = NULL,
expand = waiver(), oob = censor, na.value = NA_real_,
trans = "identity", position = "left", sec.axis = waiver()) +
labs(x="",y="Total Number of Convoys (where known)")+
ggtitle("World War II: Total Number of Allied Convoys (where known), by region")
Convoy routes by Origin (top 10)
ConvoysByOrigin <- AllConvoys %>%
group_by(Origin) %>%
summarise(ConvoyRoutesByOrigin = n()) %>%
arrange(.data = ., desc(ConvoyRoutesByOrigin)) %>%
ungroup()
top_n(x = ConvoysByOrigin, wt=ConvoyRoutesByOrigin, n = 10) ## # A tibble: 11 x 2
## Origin ConvoyRoutesByOrigin
## <chr> <int>
## 1 <NA> 21
## 2 Trinidad 13
## 3 France 11
## 4 Isle of Wight 10
## 5 River Thames 10
## 6 Freetown 9
## 7 Halifax Harbour 9
## 8 Alexandria 8
## 9 British Isles 8
## 10 Dakar 8
## 11 Liverpool 8The above reflects the fact that there are 21 routes for which it was not possible to extract origin/destination details.
Convoy routes by Destination (top 10)
ConvoysByDestination <- AllConvoys %>%
filter(!is.na(Destination)) %>%
group_by(Destination) %>%
summarise(ConvoyRoutesByDestination = n()) %>%
arrange(.data = ., desc(ConvoyRoutesByDestination)) %>%
ungroup()
top_n(x = ConvoysByDestination, wt=ConvoyRoutesByDestination, n = 10) ## # A tibble: 11 x 2
## Destination ConvoyRoutesByDestination
## <chr> <int>
## 1 France 19
## 2 Liverpool 10
## 3 Townsville, Queensland 10
## 4 Aden 8
## 5 British Isles 8
## 6 Halifax Harbour 8
## 7 Guantanamo Bay Naval Base 7
## 8 Key West, Florida 7
## 9 dispersal 6
## 10 Gibraltar 6
## 11 Trinidad 6This example would have also shown the 21 rows for which Destination is “NA”, except I have filtered them out.
From above we can observe that there are a large number of Convoy routes for which the “Origin” or “Destination” is simply “France”, without further designation of a specific city/port. As it turns out, all such items are associated with the Normandy Invasion:
AllConvoys %>%
filter(Origin=="France"|Destination=="France")## Region Code.Prefix Routes
## 1 08-Normandy Invasion Convoys BEC Bristol Channel to France
## 2 08-Normandy Invasion Convoys EBC Bristol Channel to France
## 3 08-Normandy Invasion Convoys EBM Bristol Channel to France
## 4 08-Normandy Invasion Convoys ECM Falmouth, Cornwall to France
## 5 08-Normandy Invasion Convoys EMM Belfast to France
## 6 08-Normandy Invasion Convoys EMP Belfast to France
## 7 08-Normandy Invasion Convoys ETC River Thames to France
## 8 08-Normandy Invasion Convoys ETM River Thames to France
## 9 08-Normandy Invasion Convoys EWL Isle of Wight to France
## 10 08-Normandy Invasion Convoys EWM Isle of Wight to France
## 11 08-Normandy Invasion Convoys EWP Isle of Wight to France
## 12 08-Normandy Invasion Convoys FC France to western England
## 13 08-Normandy Invasion Convoys FCP France to western England
## 14 08-Normandy Invasion Convoys FPM France to Portland Harbour
## 15 08-Normandy Invasion Convoys FPP France to Portland Harbour
## 16 08-Normandy Invasion Convoys FTC France to River Thames
## 17 08-Normandy Invasion Convoys FTM France to River Thames
## 18 08-Normandy Invasion Convoys FWC France to Isle of Wight
## 19 08-Normandy Invasion Convoys FWL France to Isle of Wight
## 20 08-Normandy Invasion Convoys FWM France to Isle of Wight
## 21 08-Normandy Invasion Convoys FWP France to Isle of Wight
## 22 08-Normandy Invasion Convoys FXP France to British Isles
## 23 08-Normandy Invasion Convoys NAP Dover to France
## 24 08-Normandy Invasion Convoys TAP River Thames to France
## 25 08-Normandy Invasion Convoys TMC River Thames to France
## 26 08-Normandy Invasion Convoys TMM River Thames to France
## 27 08-Normandy Invasion Convoys WEC Isle of Wight to France
## 28 08-Normandy Invasion Convoys WEL Isle of Wight to France
## 29 08-Normandy Invasion Convoys WNL Isle of Wight to France
## 30 08-Normandy Invasion Convoys WVP Isle of Wight to France
## Origin Destination First.Sailing Last.Sailing
## 1 Bristol Channel France June 1944 October 1944
## 2 Bristol Channel France June 1944 October 1944
## 3 Bristol Channel France June 1944 June 1944
## 4 Falmouth, Cornwall France June 1944 early July 1944
## 5 Belfast France June 1944 July 1944
## 6 Belfast France July 1944 July 1944
## 7 River Thames France June 1944 October 1944
## 8 River Thames France June 1944 October 1944
## 9 Isle of Wight France June 1944 June 1944
## 10 Isle of Wight France September 1944 October 1944
## 11 Isle of Wight France <NA> <NA>
## 12 France western England June 1944 July 1944
## 13 France western England June 1944 July 1944
## 14 France Portland Harbour <NA> <NA>
## 15 France Portland Harbour July 1944 August 1944
## 16 France River Thames 1944 1944
## 17 France River Thames 1944 1944
## 18 France Isle of Wight June 1944 <NA>
## 19 France Isle of Wight 1944 1944
## 20 France Isle of Wight June 1944 July 1944
## 21 France Isle of Wight June 1944 September 1944
## 22 France British Isles June 1944 October 1944
## 23 Dover France December 1944 December 1944
## 24 River Thames France 1945 1945
## 25 River Thames France June 1944 October 1944
## 26 River Thames France June 1944 October 1944
## 27 Isle of Wight France December 1944 May 1945
## 28 Isle of Wight France 1944 1945
## 29 Isle of Wight France April 1945 May 1945
## 30 Isle of Wight France December 1944 May 1945
## Number.of.Convoys Notes
## 1 NA <NA>
## 2 NA <NA>
## 3 1 motor transport convoy
## 4 NA <NA>
## 5 2 <NA>
## 6 2 <NA>
## 7 NA <NA>
## 8 NA motor transport convoys
## 9 NA mainly landings ships and landing craft
## 10 NA motor transport convoys
## 11 NA personnel convoys
## 12 NA <NA>
## 13 NA personnel convoys
## 14 NA motor transport convoys
## 15 NA personnel convoys
## 16 NA <NA>
## 17 NA motor transport convoys
## 18 NA <NA>
## 19 NA landing craft convoys
## 20 NA motor transport convoys
## 21 NA personnel convoys
## 22 NA <NA>
## 23 NA <NA>
## 24 NA <NA>
## 25 NA <NA>
## 26 NA motor transport convoys
## 27 NA <NA>
## 28 NA landing craft
## 29 NA <NA>
## 30 NA <NA>ConvoysByOriginExInvasion <- AllConvoys %>% filter(.data = ., Region != ConvoyTableNames[8] & !is.na(Origin)) %>%
group_by(Origin) %>%
summarise(ConvoyRoutesByOrigin = n()) %>%
arrange(.data = ., desc(ConvoyRoutesByOrigin)) %>%
ungroup()
top_n(x = ConvoysByOriginExInvasion, wt=ConvoyRoutesByOrigin, n = 10) ## # A tibble: 10 x 2
## Origin ConvoyRoutesByOrigin
## <chr> <int>
## 1 Trinidad 13
## 2 Freetown 9
## 3 Halifax Harbour 9
## 4 Alexandria 8
## 5 British Isles 8
## 6 Dakar 8
## 7 Liverpool 8
## 8 Aden 7
## 9 Gibraltar 7
## 10 Guantanamo Bay Naval Base 7ConvoysByDestinationExInvasion <- AllConvoys %>% filter(.data = ., Region != ConvoyTableNames[8] & !is.na(Destination)) %>%
group_by(Destination) %>%
summarise(ConvoyRoutesByDestination = n()) %>%
arrange(.data = ., desc(ConvoyRoutesByDestination)) %>%
ungroup()
top_n(x = ConvoysByDestinationExInvasion, wt=ConvoyRoutesByDestination, n = 10) ## # A tibble: 10 x 2
## Destination ConvoyRoutesByDestination
## <chr> <int>
## 1 Liverpool 10
## 2 Townsville, Queensland 10
## 3 Aden 8
## 4 Halifax Harbour 8
## 5 British Isles 7
## 6 Guantanamo Bay Naval Base 7
## 7 Key West, Florida 7
## 8 dispersal 6
## 9 Gibraltar 6
## 10 Trinidad 6We see that a sizable number of convoys are to or from Liverpool, a major U.K. port:
AllConvoys %>%
filter(Origin=="Liverpool"|Destination=="Liverpool")## Region Code.Prefix
## 1 01-European Coastal Atlantic Convoys OA
## 2 01-European Coastal Atlantic Convoys OB
## 3 02-North Atlantic Convoys BHX
## 4 02-North Atlantic Convoys CU
## 5 02-North Atlantic Convoys HG
## 6 02-North Atlantic Convoys HX
## 7 02-North Atlantic Convoys HXF
## 8 02-North Atlantic Convoys KMS
## 9 02-North Atlantic Convoys MKS
## 10 02-North Atlantic Convoys OG
## 11 02-North Atlantic Convoys ON
## 12 02-North Atlantic Convoys ONS
## 13 02-North Atlantic Convoys OS
## 14 02-North Atlantic Convoys SC
## 15 02-North Atlantic Convoys SL
## 16 02-North Atlantic Convoys TCU
## 17 02-North Atlantic Convoys TUC
## 18 02-North Atlantic Convoys UC
## Routes
## 1 River Thames (or Methil, Fife after July 1940) to Liverpool
## 2 Liverpool to the Atlantic Ocean
## 3 Bermuda to Liverpool
## 4 Caribbean (later New York City) to Liverpool
## 5 Gibraltar to Liverpool
## 6 Halifax Harbour (later New York City) to Liverpool
## 7 Halifax Harbour to Liverpool
## 8 Liverpool to Mediterranean
## 9 Mediterranean to Liverpool
## 10 Liverpool to Gibraltar
## 11 Liverpool to Halifax Harbour
## 12 Liverpool to Halifax Harbour
## 13 Liverpool to Sierra Leone
## 14 Sydney, Nova Scotia (or Halifax Harbour or New York City) to Liverpool
## 15 Sierra Leone to Liverpool
## 16 Caribbean (later New York City) to Liverpool
## 17 Liverpool to Caribbean (later New York City)
## 18 Liverpool to Caribbean (later New York City)
## Origin
## 1 River Thames (or Methil, Fife after July 1940)
## 2 Liverpool
## 3 Bermuda
## 4 Caribbean (later New York City)
## 5 Gibraltar
## 6 Halifax Harbour (later New York City)
## 7 Halifax Harbour
## 8 Liverpool
## 9 Mediterranean
## 10 Liverpool
## 11 Liverpool
## 12 Liverpool
## 13 Liverpool
## 14 Sydney, Nova Scotia (or Halifax Harbour or New York City)
## 15 Sierra Leone
## 16 Caribbean (later New York City)
## 17 Liverpool
## 18 Liverpool
## Destination First.Sailing Last.Sailing
## 1 Liverpool 7 September 1939 24 October 1940
## 2 the Atlantic Ocean 7 September 1939 21 July 1941
## 3 Liverpool May 1940 March 1941
## 4 Liverpool 20 March 1943 30 May 1945
## 5 Liverpool 26 September 1939 19 September 1942
## 6 Liverpool 16 Sept 1939 23 May 1945
## 7 Liverpool 29 Sept 1939 12 February 1940
## 8 Mediterranean 22 October 1942 27 April 1945
## 9 Liverpool 12 November 1942 25 May 1945
## 10 Gibraltar 2 October 1939 17 October 1943
## 11 Halifax Harbour 26 July 1941 27 May 1945
## 12 Halifax Harbour 15 March 1943 21 May 1945
## 13 Sierra Leone 24 July 1941 27 May 1945
## 14 Liverpool 15 August 1940 26 May 1945
## 15 Liverpool 14 September 1939 25 November 1944
## 16 Liverpool <NA> <NA>
## 17 Caribbean (later New York City) <NA> <NA>
## 18 Caribbean (later New York City) 15 February 1943 3 June 1945
## Number.of.Convoys
## 1 234
## 2 345
## 3 97
## 4 73
## 5 89
## 6 377
## 7 17
## 8 98
## 9 103
## 10 95
## 11 307
## 12 51
## 13 131
## 14 177
## 15 178
## 16 NA
## 17 NA
## 18 71
## Notes
## 1 merged with OB convoy in the southwest approaches
## 2 merged with OA convoy in the southwest approaches - ON and OS convoys replaced OB convoys
## 3 sailed from Bermuda and merged with same number HX convoy at sea
## 4 14-knot convoys of tankers with some fast cargo ships
## 5 replaced by MKS convoys after Operation Torch
## 6 9-knot convoys for ships of sustained speeds less than 15 knots
## 7 fast sections of HX convoys
## 8 slower ships to the Mediterranean - 1st 12 sailed independently - remainder sailed with OS convoys and detached west of Gibraltar
## 9 slower ships from the Mediterranean- 1st 11 sailed independently - remainder merged with SL convoys west of Gibraltar
## 10 early sailings every 5th merged OA/OB convoy became an OG convoy at sea - later OG convoys sailed from Liverpool
## 11 replaced OB convoys for North American destinations - alternate convoys included slower ships until the ONS convoys started
## 12 slower ships westbound on the ON convoy route
## 13 replaced OB convoys for non-North American destinations - included KMS convoys detached west of Gibraltar
## 14 7-knot convoys of eastbound ships too slow for the 9-knot HX convoys
## 15 merged with MKS convoys west of Gibraltar
## 16 14-knot CU convoys of tankers and fast cargo ships with troopships
## 17 14-knot UC convoys of tankers and fast cargo ships with some troopships
## 18 14-knot convoys of tankers with some fast cargo shipsAnd, for those routes on which we have frequency information, we see that Liverpool is well-represented at the top of the list:
FrequentlyUsedConvoyRoutes <- AllConvoys %>% filter(.data = ., !is.na(Number.of.Convoys)) %>%
#group_by(Origin) %>%
#summarise(ConvoyRoutesByOrigin = n()) %>%
arrange(.data = ., desc(Number.of.Convoys)) %>%
ungroup()
top_n(x = FrequentlyUsedConvoyRoutes, wt=Number.of.Convoys, n = 10)## Region Code.Prefix
## 1 02-North Atlantic Convoys HX
## 2 01-European Coastal Atlantic Convoys OB
## 3 02-North Atlantic Convoys ON
## 4 01-European Coastal Atlantic Convoys OA
## 5 02-North Atlantic Convoys SL
## 6 02-North Atlantic Convoys SC
## 7 02-North Atlantic Convoys OS
## 8 02-North Atlantic Convoys MKS
## 9 02-North Atlantic Convoys KMS
## 10 02-North Atlantic Convoys BHX
## Routes
## 1 Halifax Harbour (later New York City) to Liverpool
## 2 Liverpool to the Atlantic Ocean
## 3 Liverpool to Halifax Harbour
## 4 River Thames (or Methil, Fife after July 1940) to Liverpool
## 5 Sierra Leone to Liverpool
## 6 Sydney, Nova Scotia (or Halifax Harbour or New York City) to Liverpool
## 7 Liverpool to Sierra Leone
## 8 Mediterranean to Liverpool
## 9 Liverpool to Mediterranean
## 10 Bermuda to Liverpool
## Origin
## 1 Halifax Harbour (later New York City)
## 2 Liverpool
## 3 Liverpool
## 4 River Thames (or Methil, Fife after July 1940)
## 5 Sierra Leone
## 6 Sydney, Nova Scotia (or Halifax Harbour or New York City)
## 7 Liverpool
## 8 Mediterranean
## 9 Liverpool
## 10 Bermuda
## Destination First.Sailing Last.Sailing Number.of.Convoys
## 1 Liverpool 16 Sept 1939 23 May 1945 377
## 2 the Atlantic Ocean 7 September 1939 21 July 1941 345
## 3 Halifax Harbour 26 July 1941 27 May 1945 307
## 4 Liverpool 7 September 1939 24 October 1940 234
## 5 Liverpool 14 September 1939 25 November 1944 178
## 6 Liverpool 15 August 1940 26 May 1945 177
## 7 Sierra Leone 24 July 1941 27 May 1945 131
## 8 Liverpool 12 November 1942 25 May 1945 103
## 9 Mediterranean 22 October 1942 27 April 1945 98
## 10 Liverpool May 1940 March 1941 97
## Notes
## 1 9-knot convoys for ships of sustained speeds less than 15 knots
## 2 merged with OA convoy in the southwest approaches - ON and OS convoys replaced OB convoys
## 3 replaced OB convoys for North American destinations - alternate convoys included slower ships until the ONS convoys started
## 4 merged with OB convoy in the southwest approaches
## 5 merged with MKS convoys west of Gibraltar
## 6 7-knot convoys of eastbound ships too slow for the 9-knot HX convoys
## 7 replaced OB convoys for non-North American destinations - included KMS convoys detached west of Gibraltar
## 8 slower ships from the Mediterranean- 1st 11 sailed independently - remainder merged with SL convoys west of Gibraltar
## 9 slower ships to the Mediterranean - 1st 12 sailed independently - remainder sailed with OS convoys and detached west of Gibraltar
## 10 sailed from Bermuda and merged with same number HX convoy at seaAggregating, Liverpool is by far the most frequent origin:
FrequentlyUsedOrigins <- AllConvoys %>% filter(.data = ., !is.na(Number.of.Convoys)) %>%
group_by(Origin) %>%
summarise(total_convoys = sum(Number.of.Convoys, na.rm = T)) %>%
arrange(.data = ., desc(total_convoys)) %>%
ungroup()
top_n(x = FrequentlyUsedOrigins, wt=total_convoys, n = 10)## # A tibble: 10 x 2
## Origin total_convoys
## <chr> <dbl>
## 1 Liverpool 1098
## 2 Halifax Harbour (later New York City) 377
## 3 Mediterranean 262
## 4 River Thames (or Methil, Fife after July 1940) 234
## 5 Sierra Leone 178
## 6 Sydney, Nova Scotia (or Halifax Harbour or New York City) 177
## 7 Chesapeake Bay 117
## 8 Bermuda 97
## 9 Gibraltar 89
## 10 Caribbean (later New York City) 73Here’s a plot of the top 10 Origins:
FrequentlyUsedOrigins %>%
top_n(x = ., wt=total_convoys, n = 10) %>%
ggplot(aes(x=Origin, y=total_convoys, fill=Origin)) +
geom_bar(stat="identity", position="dodge")+
theme_minimal()+
theme(axis.text.x=element_blank())+
geom_text(aes(label=total_convoys),
vjust=-0.2, color="black",
position = position_dodge(0.8), size=4) +
scale_x_discrete( expand = waiver(), position = "bottom")+
scale_y_continuous(name = waiver(), breaks = waiver(),
minor_breaks = waiver(), labels = waiver(), limits = NULL,
expand = waiver(), oob = censor, na.value = NA_real_,
trans = "identity", position = "left", sec.axis = waiver()) +
labs(x="",y="Total Number of Convoys (where known)")+
ggtitle("World War II: Total Number of Allied Convoys (where known), by Origin")
Similarly, Liverpool is also the most frequent destination:
FrequentlyUsedDestinations <- AllConvoys %>% filter(.data = ., !is.na(Number.of.Convoys)) %>%
group_by(Destination) %>%
summarise(total_convoys = sum(Number.of.Convoys, na.rm = T)) %>%
arrange(.data = ., desc(total_convoys)) %>%
ungroup()
top_n(x = FrequentlyUsedDestinations, wt=total_convoys, n = 10)## # A tibble: 10 x 2
## Destination total_convoys
## <chr> <dbl>
## 1 Liverpool 1345
## 2 Halifax Harbour 358
## 3 the Atlantic Ocean 345
## 4 Mediterranean 260
## 5 Sierra Leone 131
## 6 Chesapeake Bay 114
## 7 Gibraltar 95
## 8 Caribbean (later New York City) 71
## 9 Firth of Clyde or Liverpool 45
## 10 White Sea 35Here’s a plot of the top 10 Destinations:
FrequentlyUsedDestinations %>%
top_n(x = ., wt=total_convoys, n = 10) %>%
ggplot(aes(x=Destination, y=total_convoys, fill=Destination)) +
geom_bar(stat="identity", position="dodge")+
theme_minimal()+
theme(axis.text.x=element_blank())+
geom_text(aes(label=total_convoys),
vjust=-0.2, color="black",
position = position_dodge(0.8), size=4) +
scale_x_discrete( expand = waiver(), position = "bottom")+
scale_y_continuous(name = waiver(), breaks = waiver(),
minor_breaks = waiver(), labels = waiver(), limits = NULL,
expand = waiver(), oob = censor, na.value = NA_real_,
trans = "identity", position = "left", sec.axis = waiver()) +
labs(x="",y="Total Number of Convoys (where known)")+
ggtitle("World War II: Total Number of Allied Convoys (where known), by Destination")