Category Archives: History Through Spreadsheets

Using Excel to Plot Year of Dedication and Locations of Confederate Monuments

I heard some discussion on television about all the Confederate monuments around the country and when they were erected. I decided to look for the data and plot it for myself. I very quickly found a document from the Southern Poverty Law Center that looked interesting and provided me some interesting data tidying and charting challenges. My focus here is on duplicating their chart of monuments dedications dates. This chart type is not a standard Excel type and I wanted to see how I could duplicate it. This workbook will be used in a charting seminar that I plan to present in a month or so. Continue reading

 
Posted in History Through Spreadsheets, News Fact Checking | Leave a comment

US WW2 Submarine Tonnage Sunk Database

While answering a recent question about the tonnage sank by the top US submarine skippers during WW2, I realized that I had not made available my conversion of the JANAC data for vessels sunk by US submarines. The JANAC records are considered the official records because they were cross-checked with information from Japanese records. Continue reading

 
Posted in History Through Spreadsheets, Military History | 2 Comments

Visualizing US vs IJN Aircraft Carrier Numbers During WW2

I watched an interesting lecture on American History TV this weekend called Japanese Perspective on the Battle of Midway by Anthony Tully. The most interesting part of the discussion occurred when Tully began showing how the US production of aircraft carriers eventually overwhelmed the Japanese ability to build carriers. He used some simple graphs to show the relative carrier strength of the US Navy versus the Imperial Japanese Navy (IJN) over time. In this post, I will come up with my own graphics to visualize this information. Continue reading

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

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