Quote of the Day
If you can't describe what you're doing as a process, you don't know what you're doing.
— W. Edwards Deming, guru of statistical quality control. I took a class from Dr. Demming in the early '80s. The older I get, the smarter he seems. He was kind of a cantankerous gentleman (for example, railed constantly against management), but very wise on human behavior in the manufacturing environment.
I recently noticed that combinedfleet.com has excellent summaries of the WW2 US submarine patrol reports that are very easy to scrape for data. These patrol reports are interesting because they provide accurate data as to the rate of torpedo firings by US submarines and some indications as to the mix of torpedoes being fired. Like all WW2 records, not every patrol recorded the weapons used and it is difficult to know how accurate the hit count is.
I will scrape the patrol report pages for 238 submarines and turn the data into graphs and tables that provide insight as to the tempo of US submarine combat in WW2. My workbook is available here for those who like to follow along with the analysis. Note that I used a VBA routine to extract URLs.
WW2 Data Warning
This post is focused on using WW2 historical data to illustrate how to perform data scraping operations. I should note that the results I obtain here vary slightly from results published in other sources, which I show below. This is a common problem with WW2 sources. Often, the results are different because of OCR errors — to be clear, I do not know the source of the difference here.
Definition errors are also an issue – like what constitutes a torpedo (for example, an air-launched homing torpedo deliberately mislabeled as a Mk 21 Mine). Finally, the skippers on some of the patrol reports in some cases simply didn't say what kind of torpedo they launched. They were busy at the time and can be forgiven for simple omissions.
The book Ship Killers (Widenberg and Polmar) has two tables that contain similar information. As is typical for WW2 information, the data between the two tables do not agree in terms of weapons used and totals. However, the results are similar to what I obtained by scraping the Combined Fleet website in broad terms.
Table 1 shows the number of torpedos fired by type and their hit rates. Note that I generally do not give much credence to hit counts because of the difficulty of confirming the information, while the number of torpedoes fired is fairly easy to tally.
|Mk9, 10 11||1,722||502||29%|
Table 2 shows the number of torpedoes fired by year and theater. Note that the Pacific theater dominated US torpedo usage.
Table 3 shows the torpedoes listed in the patrol reports. Other weapons included in the report are the deck gun, burning, and ramming. I do not address those weapons in this post.
|Acoustic Seeking Torpedo||Probably a Mark 27 Cutie|
|Mark X Torpedo||Obsolete, had a depth control issue|
|Mark X Type 3 Torpedo||Obsolete, had a depth control issue|
|Mark XIV Torpedo||Problematic US WW2 standard torpedo|
|Mark XIV Type 1 Torpedo||Problematic US WW2 standard torpedo|
|Mark XIV Type 3 Torpedo||Problematic US WW2 standard torpedo|
|Mark XIV Type 3A Torpedo||Fixed US WW2 standard torpedo|
|Mark XV Torpedo||Surface ship torp, launchable from submarine aft tubes|
|Mark XVIII Electric Torpedo||US copy of German electric torpedo|
|Mark XVIII Type 1 Torpedo||US copy of German electric torpedo|
|Mark XXIII Torpedo||Single-speed Mk 14.|
The analysis approach is straightforward:
- Generate a list of URLs with submarine patrol data
- Create a Power Query function that grabs the data from each URL.
- Run the function on each submarine's URL.
- Create a consolidated data file.
- Generate required plots and tables.
Torpedoes Fired Versus Time
Figure 2 shows US torpedo firings by month during WW2. You can see that October 1944 was a very busy month. At the end of the war, the torpedo firings dropped dramatically because most Japanese ships had already been sunk.
We can generate a table of yearly data as shown in Table 4. This is very similar to Table 2 from an alternate source.
Torpedoes By Type
Table 5 shows the breakdown of the torpedoes fired by type. I assumed that "Acoustic Homing Torpedo" was a Mark 27 "Cutie" for this table.
|Torpedo||Claimed Hits||Torps Fired||Hit Percentage|
|Mark 23 (One Speed Mark 14)||986||2,595||38%|
|Mark 18 (Electric)||624||2,149||29%|
|Mark 27 Acoustic Homing||34||108||31%|
I learned a number of things during this analysis:
- October 1944 was the peak month of US torpedo firings.
This seems reasonable. The Japanese still had a significant maritime fleet and the US finally had deployed a significant number of submarines and finally had a working torpedo.
- Mark 15 torpedoes were used on submarines early in the war.
The Mark 15 is larger than the Mark 14 and could only be fired from the aft tubes (reference). I knew that torpedoes were in short supply early in the war, so it does not surprise me that they were used if available.
- There were significant numbers of Mark 18 electric torpedoes fired.
This is amazing considering the Mark 18 was copied from a German design and was copied, manufactured, and produced in significant numbers during the US's 44-month wartime involvement.
- The Mark 23 was not a popular variant.
It was a Mark 14 with the low-speed option removed. The low-speed option provided a significantly longer range. Early in the war, engagement ranges were relatively short and it made sense to remove the low-speed option. As the war progressed, ranges increased and the low-speed option became necessary.
I was curious about Power Query and how you got your results. So, I did some digging using both Power Query and MS Access. My answers at the basic level are the same as yours with the exception of the Mark 23 and Mark 18 results. According to your notes, the Mk 23 electric is a single-speed Mk 14. According to the Mk 14 wiki, it's not an electic torpedo. The Mk 18 was an electric torpedo. So, I believe your Mk 23 electric summary consisting of Mk 18 and Mk 23 numbers is in error.
Also, for the fun of it, I downloaded all columns of the submarine data from the combinedfleet.com website and took a look at the ships sunk or damaged by U.S. torpedoes. The top 5 categories of ships sunk or damaged are: 1500 cargo ships (target code AK), 409 fuel oiler tankers (target code AO), 246 target code SAM ships, 195 transport ships (target code AP), and 169 target code SMC ships. I could not find what the ship codes SAM and SMC represent. I used https://www.history.navy.mil/research/histories/ship-histories/abbreviations.html to determine a number but not all of those target codes in the downloaded data.
As always, thanks for sharing your thoughts.
I really appreciate your review. I have updated the post and the file so that I believe it is correct If not, write me a note.
SAM is short for Sampan. SMC is short for small craft. Note how SAM and SMC were destroyed using deck guns -- weren't worth a torpedo.
The definitions are called out in this book on google books.
Here is a snippet.
Note that my numbers for the top 5 categories of ships sunk exclude those with Lat/Lon errors or date errors.