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 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.

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 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.

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.