Category Archives: History Through Spreadsheets

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 | Leave a comment

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

Fighter Plane Statistics

So I prowled around the web and found a site that seemed to have some good information on the combat history of active jet fighters, including the F-16. I thought I would use this question as a vehicle for sharpening my Power Query and Excel web scraping skills by making comparison table between the active duty fighter jets. Continue reading

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

Probability That An Old President Does Not Finish Their Term

Ronald Reagan (Figure 1) was our oldest president at the time of inauguration – 69 years 349 days old. The 2016 US presidential election is giving us a choice of two candidates that will be relatively old at inauguration: Donald Trump (70 years, 220 days), and Hilary Clinton (69 years, 86 days). Since US presidents often serve 2 terms, it is conceivable they we may have a 77- to 78-year old president in 2024. This fact makes me curious as to what is the likelihood that a 70 year-old's natural life will be long enough for them to serve one or two terms. Continue reading

 
Posted in General Mathematics, History Through Spreadsheets | Leave a comment