US WW2 Submarine Tonnage Sunk Database

Quote of the Day

Almost every successful person begins with two beliefs: The future can be better than the present. And I have the power to make it so.

— David Brooks. This quote really speaks to me. The bedrock of success is the belief that you can make things better. At the core of this belief is a strong feeling of hope.


Figure 1: USS Gato, the lead boat of the most common type of US submarine during WW2.

Figure 1: USS Gato, the lead boat of the most common class of US submarines during WW2.

While answering a recent question about the tonnage sank by the top US submarine skippers during WW2, I realized that I had not made available my conversion to Excel of the Joint Army–Navy Assessment Committee (JANAC) data for vessels sunk by US submarines. The JANAC records are considered the official records because they were cross-checked with information from Japanese records.

The spreadsheet itself is from a course I taught last year on using Get and Transform (also known as Power Query). The raw data is from the Hyperwar web site – I often use the old WW2 records as an example of horribly formatted data that can be converted to a useful computer format using Python or Excel. The Hyperwar data appears to be from human-generated JANAC reports that were OCRed and converted to HTML. The Hyperwar site is a great resource, but the data does contain numerous conversion issues (e.g. commas turned into periods, extra Øs added to numbers). I cleaned up the obvious problems and cross-checked my results with data from the now-defunct Valor at Sea web site. The agreement was excellent.

This post makes this data available to those who are interested. With the Valor at Sea website offline, I could not find data summaries available anywhere else. Having it in spreadsheet form provides you the ability to generate custom reports. The data includes the specific ships sunk by each submarine. It does not include data for ships sunk because of the action of multiple submarines.

The Excel workbook is available here. There are no macros, but there are hyperlinks to various data sources.

 
This entry was posted in History Through Spreadsheets, Military History. Bookmark the permalink.

2 Responses to US WW2 Submarine Tonnage Sunk Database

  1. Ken St.Andre says:

    Mark, I have just discovered Silent Hunter and I am also intrigued by the math of it all. I have been working through understanding how the Is-Was actually worked, the principles not the hardware. I have seen a number of references to the concept of bearing rate change being used to determine target course and speed but I can't find anything that describes it in understandable terms. This might be an area that would interest you for a future blog.
    I enjoyed your table on the tonnage data. It has been a curious thing to me that the number of sinkings per ship and/or Captain is not very large when you consider the number of years that they were hunting. I have seen elsewhere that the success rate for hits vs # or torpedoes shot was about 45% even among the best Captains. If you couple that with how hard it would be to find a target on the big ocean, the low total numbers make sense.
    Of course the other amazing thing is that the average age of these Captains was about 32 years old. Most of the rest of the crew was obviously younger.
    Great work on your blog postings. I look forward to seeing more.

     
    • mathscinotes says:

      I am a huge fan of Silent Hunter. As far as blogging goes, I maintain a list of future blog topics and I will add estimating target course and bearing to my list. I probably should also show how the Is-Was works. I actually have some information on skipper success rate, and it will be a topic for a future post. The success rate early in the war was very low because the torpedoes simply did not work: (1) ran too deep, (2) the magnetic influence exploder could not adapt to the variability in the Earth's magnetic field, and (2) the contact exploder could not handle the deceleration of a straight-on hit.

      Thanks for leaving a comment.

      mark

       

Leave a Reply

Your email address will not be published. Required fields are marked *