Category Archives: History Through Spreadsheets

Royal Navy Losses in WW2

I have been doing quite a bit of reading lately on WW2 naval actions, and I have been putting together tables that show me ship losses by year. This information gives me a feel for the tempo of battle during the war. I first looked at US naval losses (link) and am now looking at the Royal Navy losses (Figure 1). Continue reading

Posted in History Through Spreadsheets, Military History | Leave a comment

US Naval Losses in WW2

I have been putting together some information on US naval actions during WW2. Specifically, I wanted to look at US Naval losses by year during WW2 in order to get a feel for the change in battle tempo over time. The Wikipedia has an excellent page on all the US naval losses during WW2, so I simply downloaded this page, cleaned it, up and generated an Excel pivot table (Figure 1). The breakdown by combatant type is my own, everything else is from the Wikipedia. Continue reading

Posted in History Through Spreadsheets | 7 Comments

An Example of Cleaning Untidy Data with Tidyr

I recently decided to take some classes in data analysis at Datacamp, an online training site. My first classes were in dplyr and tidyr – two excellent R-based tools for manipulating files that not amenable to analysis because of inconsistencies and structure: tidyr provides many tools for cleaning up messy data, dplyr provides many tools for restructuring data. After completing the two classes, I decided that I needed to firm up my knowledge of these tools by applying them to a concrete example. Continue reading

Posted in Excel, History Through Spreadsheets | 2 Comments

History Through Spreadsheets: Executive Orders

During my recent seminar on Excel's Power Query feature, I showed my team how to grab data executive order data from the web and generate a simple plot (Figure 1). After generating the plot, I asked the audience what we could learn from this graph. I was expecting to hear that the early 1900s – the time between Teddy Roosevelt and Franklin Roosevelt – was a time of massive use of executive orders. Continue reading

Posted in Civics Through Spreadsheets, Excel, History Through Spreadsheets | 3 Comments

History Through Spreadsheets: Supreme Court Confirmations

I recently gave a seminar to my staff on using Excel with Power Query. As part of the seminar, I presented a number of web scraping examples that were well received, and I decided that some of you may appreciate them also. Continue reading

Posted in History Through Spreadsheets | Leave a comment

US Manufacturing Employment Versus Time

The only television news program that I watch is the PBS Newshour. I particularly like the discussions between Mark Shields, a reasonable liberal, and David Brooks, a reasonable conservative. On inauguration day (20-Jan-2017), they had an interesting discussion about the challenges the US faces and what can be done about them. Continue reading

Posted in History Through Spreadsheets, Statistics | 4 Comments

Some PT Boat Statistics

I was doing some reading about President John F. Kennedy (JFK) and was surprised to learn that he actually commanded three PT boats: PT-101, PT-109, and PT-59. His service on PT-101 was very short. His next command, PT-109, became famous because of its ramming and sinking by the Japanese destroyer Amagiri. Continue reading

Posted in History Through Spreadsheets, Military History | Leave a comment

Calorie Per Acre Improvements in Staple Crops Over Time

My family has strong agricultural roots – mainly in dairy and potato farming – and our holiday conversations frequently turn to discussions of crop yields (bushels per acre or lbs per acre). As I listened to the discussion between my brothers on this year's crop yields, I realized that the yield numbers they were quoting were much higher today than we saw as children. This made me curious, and I decide to go out to the US Department of Agriculture's National Agricultural Statistics Service crop database and download CSV files on the yield of some key staple crops for processing by Power Query (i.e. recently renamed Get and Transform). I will be using this file to train my staff on defining Power Query functions. No macros were used in this analysis. Continue reading

Posted in Excel, General Science, History of Science and Technology, History Through Spreadsheets | 1 Comment

Demise of the Pearl Harbor Strike Force

I just watched a wonderful BookTV presentation by three authors on the WW2 attack on Pearl Harbor, which occurred 75 years ago today. While I am generally familiar with what happened during that attack, I had not looked at the details of the attack. In particular, this show motivated me to look at the Imperial Japanese Navy (IJN) attack force composition and what happened to these ships over the course of the war. The fate of these ships reflects what happened to the rest of the IJN during the Pacific War. Continue reading

Posted in History Through Spreadsheets, Military History | 4 Comments

WW2 Casualty Rates By Country

I recently was watching a documentary on WW2 that mentioned that Greece and Yugoslavia suffered some of highest casualty rates during WW2. While I have read much about WW2, I had not looked at the casualty rates as a percentage of each country's population. I did some quick web searching and found that the Wikipedia has an excellent table summarizing WW2 casualties, which I imported into Excel and sorted by casualty rates. These percentages are mind numbing. While Greece and Yugoslavia suffered terribly, other countries suffered even more. Continue reading

Posted in History Through Spreadsheets, Military History | Leave a comment