US WW2 Torpedo Production Chart Using Power Query

Quote of the Day

Satisfaction is always available. It is just not always looked for. If, when you enter any experience, you enter with curiosity, respect and interest you will emerge enriched and with awareness you have been enriched. Awareness of enrichment is what satisfaction is.

— Ann Hastings, Author of The Mechanics of Reality: The Teachings of Vywamus Channeled.


Introduction

Figure 1: US Production of Torpedoes in WW2. (Source)

Figure 1: US Production of Torpedoes in WW2. (Wikipedia)

During my readings on the Pacific War, I often see the chart shown in Figure 1. I decided to do a bit of digging and find the source data for this chart in the hope of making a version of this chart that is a bit clearer and easier to use.

My go-to source for munitions production data is the report Official Munitions Production of the United States By Months, July 1, 1940-August 31, 1945 (link). Fortunately, this report has a table of US torpedo production data, but simply plotting the data did not produce Figure 1. However, I was able to reproduce Figure 1 by doing a bit of data filtering.

This post documents my process for gathering the data, cleaning it, filtering it, and generating a cleaner and easier-to-use version of this chart. My workbook is here.

Background

Figure 1 only shows the production of anti-surface ship torpedoes, which were grouped according to the platform they were designed to be launched from:

  • Submarine-Launched: Torpedo Mk 14, 18, and 23
  • Aircraft-Launched: Torpedo Mk 13
  • Surface Ship-Launched: Torpedo Mk 15

There were other torpedoes produced by the US during WW2 but they are not included in the chart. For example, the Torpedo Mk 24 (“FIDO”) was an acoustic homing torpedo used primarily to destroy submarines.

Many older anti-surface ship torpedo versions were used during the war but were not being manufactured at that time. For example, the Bliss–Leavitt Mark 8 torpedo was used extensively by PT boats. Wikipedia has a table that summarizes the US torpedo versions.

Analysis

Data Processing

The basic data extraction process was simple:

    • OCR the torpedo production table from the Offical Munitions Production document.
    • Put the OCR data into Excel
    • Import the data into Power Query
      • This is a complex table with a multi-level heading, so there was a fair amount of heading manipulation involved.
      • Convert the table to tidy format
      • Filter the data
        • The data contains some UK torpedoes that the US made but did not chart.
        • The data contains a small number of US Torpedo Mk 14 Mod 4, which I can find no record of it being deployed nor did it appear to be included in the chart.
    • Generate a pivot table of the data
    • Generate a chart of the pivot table.

Charting

Figure 2 shows my rendering of the data. I did change the chart format just a bit by adding production numbers to each column of the chart and removing the gridlines, which makes things a bit clearer for me.

Figure 2: US Torpedo Production in WW2.

Figure 2: US Torpedo Production in WW2.

Conclusion

My goal was to reproduce the chart shown in Figure 1, which I have done.

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

Leave a Reply

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