US Navy WW2 Aviation Statistics Cleanup Using Power Query

Quote of the Day

The difference between MacArthur and God is that God never thought that He was MacArthur.

— Tom Kratman, former Regular Army Infantry Enlisted


Introduction

Figure 1: F6F Hellcat had the highest kill ratio of any US fighter in the Pacific War.

Figure 1: F6F Hellcat had the highest kill ratio of any USN fighter in the Pacific War.

I was reading a forum post on fighter kill ratios during WW2 and decide to compute some  Imperial Japanese Navy (IJN) vs  US Navy (USN) ratios for myself. I should point out that these ratios are generally viewed as inflated because of the difficulty of confirming downed aircraft. However, the inflated numbers continue to be quoted. The published reports state that the F6F Hellcat had the best kill ratio of the USN/Marine fighter at 19-to-1, followed by the F4U Corsair at 11-to-1, and the F4F Wildcat at 7-to-1.

I decided to compute the ratios for myself using the Naval Aviation Combat Statistics (NACS) report on the air war in the Pacific. As far as data cleanup goes, there is nothing quite as ugly as scanned WW2 documents:

  • scanning errors
  • typing errors
  • hierarchical row and column headings
  • math errors

This scan had all the usual problems minus the math errors. Click on Figure 2 to see the scan that I OCRed.

Figure 2: NACS Table 1.

Figure 2: NACS Table 1.

Background

The analysis presented here is entirely based on the NASC's Table 1. Virtually all the work is in forcing the table into tidy format for easy pivot table generation. Figure 3 shows my data extraction workflow.

Figure 2: Data Processing Workflow.

Figure 3: Data Processing Workflow.

My Excel workbook is available here.

Analysis

After converting the data to tidy format, I generated a few pivot tables. Table 1 shows the kill ratios for the listed USN fighters: Hellcat, Corsair, and Wildcat. My numbers agree with other publications. I was not able to find any references to the Brewster Buffalo's kill ratio in USN use, but my low number does not surprise me – the USN wanted to get rid of the Buffalo as soon as it could. I should note the Buffalo was used very successfully by the Finns.

Table 1: USN WW2 Fighter Kill Ratios.
Figure M: USN Fighter Kill Ratios.

Table 2 shows the bomb tonnage dropped by USN aircraft. Note how the Corsair had the highest bomb tonnage total among the fighters. This attack capability explains why the Corsair remained in service through with the USN through the Korean War. Table 3 shows that the Hellcat and Corsair dominate the action sortie statistics.

Table 2: Bomb Tonnage By Aircraft. Table 3: Sorties By Aircraft.

Conclusion

Power Query was able to deal with a complex table having both hierarchical and non-hierarchical column headings. I was able to generate pivot tables that confirmed previously published kill ratio numbers. I also was able to generate numbers for action sorties and bomb tonnage.

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

2 Responses to US Navy WW2 Aviation Statistics Cleanup Using Power Query

  1. Gary Reed says:

    Regarding your comment that the Corsair was chosen for Korea because it had the highest bomb tonnage of USN fighters in WWII, I have read that the final decision was between the USN's Corsair and the USAAF's P-47. Although the P-47 was preferred as a ground attack aircraft, the Corsair was selected because of the greater availability of Corsair spare parts already in the Pacific region.

     
    • mathscinotes says:

      Hi Gary,

      You might be right. These decisions are frequently driven by logistics issues. One negative of the Corsair was its availability . I have read that the Hellcat had 90% availability compared to the Corsair's 66% (link). I have seen reports that an F4U required 50.5 hours of maintenance per hour of flight (link). The Hellcat required 11 hours of maintenance for every hour of flight (link). There are good general discussions of the decision on Quora and on Reddit.

      biegert

       

Leave a Reply

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