Recoil Calculation Example


Quote of the Day

You have not yet begun to consider what sorts of people are these Athenians whom you may have to fight.

— Thucydides, describing a statement by an ambassador from Corinth who was speaking to the Spartan Assembly. The Spartans were bent on war. This quote is similar to Yamamoto's often cited (but unconfirmedwarning to the Japanese government about going to war with the United States.


Figure 1: A-10 Firing its GAU-8. (Source)

Figure 1: A-10 Firing its GAU-8. (Source)

I have been reading about the US Air Force's battle to retire the A-10 Warthog (Figure 1). The USAF has never cared for the A-10 and has made a number of attempts to replace it with either the F-16 or the F-35. During my reading, I saw the following statement about the recoil of it 30 mm Gatling gun, and the impact of this recoil on the A-10's speed.

The average recoil force of the GAU-8/A is 10,000 pounds-force (45 kN), which is slightly more than the output of one of the A-10's two TF34 engines (9,065 lbf / 40.3 kN each). While this recoil force is significant, in practice a cannon fire burst only slows the aircraft a few miles per hour in level flight.

In this post, I will examine these two statements mathematically to determine if I understand them.


Key Performance Parameters

Figure 2 shows the GAU-8 data as stated on the General Dynamics web page.

Figure 2: GAU-8 Key Performance Parameters. (Source)

Figure 2: GAU-8 Key Performance Parameters. (Source)

GAU-8 and Its Projectile

Figure 2: Three Types of 30 mm GAU-8 Rounds. (Source)

Figure 3: Three Types of 30 mm GAU-8 Rounds. (Source)

Figure 3 shows the GAU-8's 30 mm projectile. For this exercise, I will assume the projectile has the following characteristics:

  • Projectile velocity: vMuzzle = 3400 feet per second (fps)
  • Projectile mass: mGAU8 = 395 grams
  • Rate of fire: r = 6000 round per minute

The GAU-8 can be programmed for different rates of fire. I will assume a 6000 rounds per minute for the maximum rate of fire, which will generate the maximum recoil. I will also assume that the gun is fired in burst of 100 rounds, a number that I am guessing based on the ammunition capacity of 511 rounds. Effectively, I am assuming that the gun only has five bursts available.

To estimate the impact of firing the GAU-8 on the speed of the A-10, I will assume that the A-10 weighs 51,000 pounds, which is its listed maximum takeoff weight.


Shortcomings of this Analysis

No explosion-driven device is 100% efficient at converting chemical energy into projectile energy. In the case of gun, it is common to assume that as much as 20% of available powder energy goes into the gases that escape from the end of the barrel. I do not know the impact of this gas discharge on the overall recoil for a GAU-8, but it is significant. I will ignore this escaping gas in my analysis below, which means that my calculations provide a lower bound on the recoil of this weapon.

In reality, recoil can only be accurately estimated with detailed knowledge of the gas discharge characteristics.

Recoil Analysis

Figure 4 shows how to estimate the amount of recoil by assume that recoil is do to the change in momentum caused by the opposing momentum of the fired projectiles. Note that this estimate ignores the momentum of the expelled gases.

Figure 3: Recoil Calculation.

Figure 4: Recoil Calculation.

We see that the recoil must be greater than 9200 pounds, which means that the stated recoil force of 10,000 pounds is reasonable.

Impact on A-10 Speed

Figure 5 shows how you can estimate the reduction in the aircraft's speed caused by the firing of the GAU-8.  I calculate that the impact of the GAU-8 on the A-10's speed is ~4 miles per hours, which roughly agrees with the statement quoted in this post's introduction, i.e. a few miles per hour.

Figure 4: Impact of GAU-8 Firing on A-10 Velocity.

Figure 5: Impact of GAU-8 Firing on A-10 Velocity.


The fact that the GAU-8 has ~5 tons of recoil force is amazing. It is hard to believe a weapon like that can be mounted on an aircraft.

As I worked on this problem, I recalled a Woody Woodpecker cartoon within the movie Destination Moon that illustrated how a firing a rifle can generate thrust.

Figure 2: Good Video Briefing on Spreadsheet Work.
Posted in Ballistics, Military History | Leave a comment

Lake Level Variation Over Time


Quote of the Day

If physics was about proofs, von Neumann would be a pretty good physicist.

Wolfgang Pauli, after reading a rigorous mathematical proof about some aspect of quantum mechanics by John von Neumann.

Figure 1: Plot of Eagle Lake Level Relative to Sea Level.

Figure 1: Chart of Eagle Lake Level Relative to Sea Level. The Ordinary High-Water Mark (OHWM) is the level used by the State of Minnesota for determining how far away buildings must be placed from the water.

My wife and I are building a vacation home on the shores of Eagle Lake in Itasca County, Minnesota. We also are active members of the local lake association, which is a group of homeowners who work on projects to keep our lake healthy. One task I perform on a yearly basis for the lake association is to draw a graph of how our lake level is varying over time (Figure 1). The lake level is important to homeowners because it affects the amount of beach that is exposed and the length of their docks.

Figure 1 shows how our lake level has varied since 2001. The peaks correspond to recent heavy rains. The low points tend to corresponds to times when a beaver dam at the lake outflow fails, releasing water,  and causing a quick reduction in lake level. In general, the beavers perform excellent maintenance on their dams, but trappers occasionally remove the beavers, and then the beaver dams fall into disrepair until a new beaver pair arrives. No lake level measurements are taken during the winter because the lake is frozen and covered in snow. I indicate winter in Figure 1 with a snowflake symbol.

I attach my spreadsheet here for those who are interested in how this chart is obtain the lake level information. Power Query is used to grab the data from the State of Minnesota and clean the data up for graphing. I use standard Excel commands for plotting the data.


Posted in Cabin | Leave a comment

Sugar-to-Flour Mass Ratios in Cake Recipes


Quote of the Day

The Hemingway we are talking about did not choose his death.

— Orson Welles, talking about Ernest Hemingway and how he suffered near the end of his life. Hemingway was not the same man at the end of his life that Welles had known earlier.


Figure 1: Histogram of Sugar-to-Flour Mass Ratios in 62 Cake Recipes.

Figure 1: Histogram of Sugar-to-Flour Mass Ratios in 62 Cake Recipes.

I have been working at becoming a better baker. Specifically, I have been trying to understand how recipes are developed. Many baker's begin developing their recipe's based on ratios of ingredients. The classic rule ratio of thumb for cake recipes is to use equal masses of flour, sugar, butter, and eggs – with the ingredient ratios expressed as 1:1:1:1.

I recently noticed that many of the recipes that I have been using do not follow this rule of thumb. I was most curious about how the ratio of sugar-to-flour varied between recipes. I found a text database of cake recipes and decide to create a histogram that shows how the sugar-to-flour mass ratio varies.


Flour and Sugar Densities

Recipes in the US tend to use volume measures like cups rather than mass measures. To convert these volume measures to masses, I needed to determine the densities of flour and sugar in terms of cups (Figure 2).

Figure 2: Computing the Density Ratio of Sugar-to-Flour.

Figure 2: Computing the Density Ratio of Sugar-to-Flour.

Confounding Issues

Cake recipes frequently contain ingredients that affect the amount of flour or sugar required. Example of these confounding ingredients include:

  • cocoa (reduces the need for flour)
  • honey (reduces the need for granulated sugar)
  • applesauce
  • candied fruit (e.g. cherries)

Also, the recipes sometimes call out sifted flour, which has a lower density than unsifted flour. For my analysis here, I am ignoring confounding ingredients and just looking at the ingredients labeled sugar and flour (granulated or brown) and taking their mass ratios.


My analysis was performed using Excel and Power Query. I have included my analysis and data here. I did not use every recipe in the text database because they had obvious confounding sources of sugar. For example, some recipes had large amounts of sugar added in the form of juices and fruits. I flagged the recipes I used by prefixing their names and their flour and sugar ingredients with "@" symbols. This made it simple to extract the information that I needed.

Once I had extracted the ingredients, I then determined the volume ratios and converted the volume ratios to mass ratios using the densities of flour and sugar.


The 1:1 mass ratio between flour and sugar appears to be violated frequently. In fact, the 1:1 volume ratio between flour and sugar is more common than the 1:1 mass ratio.






Posted in Baking | Leave a comment

Real-Life Equipment Optimization Problem


Quote of the Day

First forget inspiration. Habit is more dependable. Habit will sustain you whether you’re inspired or not.

Octavia Butler. It is very important to cultivate habits that promote excellence. Jack London used to say that "You can't wait for inspiration, you have to go after it with a club." I believe that as well. I often find that inspiration comes in the quiet times that occur after I have laid siege to a problem for a while.


Figure 1: Illustration of the Manufacturing Situation.

Figure 1: Illustration of the Manufacturing Situation.

I recently was asked to provide a recommendation on how to schedule the operating time for three different machines that were producing three different products. This is not a made up problem, but reflects a real production situation. My solution uses Excel's Solver and its linear programming-based optimization routine to find an optimal machine scheduling plan. The solution I provided has proven to be useful to the folks who asked for it, and I thought it was worth sharing my solution here.

I should mention that the model I am showing here is only the first-round of my modeling effort. I am continuing to work with the group that is responsible for this manufacturing effort to create a more detailed model of their manufacturing process. The effort has been very interesting and has provided much insight into their manufacturing process.

For privacy reasons, I have normalized all product costs and have changed all the default values from their true values.

Problem Description

Figure 1 shows a high-level illustration of the manufacturing situation. The following list defines the problem and its constraints:

  • There are three machines called 1, 2, and 3.
  • There are three products being manufactured called A, B, and C.
  • Each product has a different unit market value.
  • We will be be optimizing the total product value produced by these three machines each month.
  • Machine 1 can only produce product A.
  • Machine 2 can only produce product A.
  • Machine 3 can produce either product B or C.
  • Machine's 1 and 3 share the same electrical supply and only one of these machines can be run at a time. I should mention that these machines are enormous, in a remote location, and take a considerable amount of power. Upgrading the electrical service is prohibitively expensive.
  • Machine 3 requires time to switch from producing B to C and vice-versa. I will not be modeling this loss of production – I will include switch-over time in a later model. I needed to get an initial solution out immediately.
  • There are production contracts that mandate that a minimum number of A, B, and C products must be manufactured each month.
  • The machines have a certain amount of downtime that is related to their hours of operation. I have modeled these maintenance operations as a loss of efficiency.


I am not going to go through the details of the spreadsheet here in text – I recommend you just look at the spreadsheet itself. Here are its critical features:

  • The spreadsheet uses simple VBA macros to select canned scenarios and run the solver.
  • I have defined four scenarios that you can select using radio buttons.
  • You can change the inputs to whatever you want and click the solve button to generate a custom solution.

Here is my spreadsheet.


The model provided us a very useful discussion vehicle for understanding and evaluating the manufacturing process in terms of:

  • machine efficiency
  • switching time
  • product mix
  • electrical power

While I plan to continue to refine the model, the spreadsheet included here is proving to be quite accurate.

Posted in General Mathematics | 3 Comments

Business Trip to Portugal


Quote of the Day

Every boy in the streets of Göttingen knows more about 4-dimensional geometry than Einstein.

David Hilbert, a first-class mathematician in competition with Einstein for developing general relativity. The boys in Göttingen sound pretty impressive to me.

Figure 2: Salt Boats Being Used to Carry Tourists. (Wikipedia)

Figure 1: Salt Boats Being Used to Carry Tourists. (Wikipedia)

I just came back from a business trip to Aveiro, Portugal. What a beautiful place! It is a very vibrant university town that lies about 6 km from the ocean. It is known as the "Venice of Portugal" because of its system of canals, which is a legacy of the old days when Aveiro was a major source of sea salt for the region (Figure 1). The climate is warm, with a very substantial breeze that blows out toward the Atlantic ocean. I liked my visit so much that I plan on bringing my wife on a future trip.

Figure M: Butterly in Pavement.

Figure 2: Butterfly in Pavement. (Source)

I was in Portugal visiting the University of Aveiro, which is an exciting place with much engineering activity. Portugal is working hard to develop a high-tech industrial base. They have some excellent electronics and optics design capability at the university and are performing some very high tech manufacturing at local commercial firms. These manufacturing firms also have a significant capability for servicing electronic products that are returned by customers. As you might expect, these manufacturers are located near the university – you see the same symbiotic relationship between schools and industry throughout the US. I was quite impressed.

Figure 1: Transistor Symbol Inlayed into the Pavement in Aveiro, Portugal. (Wikipedia)

Figure 3: Transistor Symbol. (Wikipedia)

As an amateur builder, I always look closely at how construction practices differ around the world. One novel aspect of Portuguese construction is Portuguese pavement, a type of stone pavement into which decorative patterns or symbols are inlaid (Figure 2). Most of patterns I saw were very artistic, e.g. wave patterns or stain glass-like figures, but one inlay in Aveiro of an NPN transistor symbol shows that not all patterns are artistic (Figure 3). I spent a few minutes watching some workers repair a damaged inlay – the work required a tremendous amount of labor and artistic talent. We do not have that kind of patience in the US.

Figure 3: Movie Poster for "The Miracle of Our Lady of Fatima." (Wikipedia)

Figure 4: Movie Poster for The Miracle of Our Lady of Fatima. (Wikipedia)

While driving to Aveiro from Lisbon, I passed the city of Fatima. As a lapsed Catholic, this name is very familiar to me. I vividly recall from my youth watching the movie The Miracle of Our Lady of Fatima (Figure 4). I found the story very compelling as a child, and I was not going to pass up an opportunity to visit the site.

I must admit that I was stunned when I arrived at the Sanctuary of Fatima – it was huge. The place was a beehive of activity because Pope Francis was going to visit the site for the 100th anniversary of the miracle (13-May-2017). This was making visiting the shrine expensive for the pilgrims coming to the site. I was told that during the Pope's visit, cots were renting for 1000 € per night – that is a huge sum for anyone and especially for Portugal. I really enjoyed my visit there, and I highly recommend it for anyone who happens to be in the area.

Figure 5 shows a panoramic view of the Sanctuary of Fatima. The scale of the site was incredible.

Figure 4: Fatima Basilica. (Wikipedia)

Figure 5: Fatima Basilica. (Wikipedia)



Posted in Personal | Leave a comment

Yet Another FPGA Differential Termination Example


Quote of the Day

This is not a peace. It is an armistice for twenty years.

— Ferdinand Foch on WW1's Treaty of Versailles in 1919. He proved to be prophetic, with WW2 starting almost twenty years to the day after he made this statement.


Figure 1: My Proposed Differential Termination Network.

Figure 1: My Proposed Differential Termination Network. It is drawn in LTSpice. In a later post, I will discuss how to use LTSpice to solve this problem.

An engineer asked me for assistance on determining the termination circuit for a Xilinx uG476 series 7 FPGA. The circuit works is slightly different manner than those termination circuits I have developed before (here and here) because there is not termination voltage, so I thought I should document my work here in detail. I will be using Mathcad 15 to determining the optimal resistor values for  (1) terminating the circuit in printed circuit board's characteristic impedance (Z0), and (2) ensuring that I preserve as much of the transmit signal level as possible without exceeding the input circuit's maximum voltage level.

This is a classic optimization problem that engineers must solve on a regular basis. I have been working on many of these problems lately, including a factory optimization problem that I will be documenting in a future post.

For those who are interested, my Mathcad source is here.



Common-Mode Voltage (VA and VB)
This is the DC level of the differential signal. In the circuit of Figure 1, there are two common-mode voltage requirements that must be met: (1) the open-circuit output voltage for the transmitter (VA), and (2) the open-circuit voltage for the receiver (VB).
Characteristic Impedance (Z0)
For the low-loss case we have here, the characteristic impedance is the resistive value that represents the effective resistance of an infinitely long transmission line.
Gain (G)
Really, this is an attenuation factor between the differential signal (represented by V3 and V4 in Figure 1, and the signal across the termination resistors, R5 and R6. Xilinx refers to this parameter as "Gain," and I will use their nomenclature.


The requirements are simple:

  • VA = 1.3 V (specified by Xilinx)
  • VB = 0.8 V (specified by Xilinx)
  • R5, R6 = 50 Ω (inside the FPGA and specified by Xilinx)
  • 0.267 ≤ G ≤ 0.813 (see Appendix A for details)


Circuit Formula Derivation

Figure 2 shows how I derived the formulas for the resistor values required to meet the Z0 and common-mode voltage values, which are met exactly by these formulas.

Figure M: Derivation of Resistance Formulas.

Figure 2: Derivation of Resistance Formulas.

Solution for Resistance Values

Figure 3 shows how I used Mathcad's maximize routine to determine the optimal resistor values. I chose the resistor values to find the largest gain consistent with the gain range limits.

Figure M: Determine the Optimum Solution.

Figure 3: Determine the Optimum Solution.

This solution shows that R2 is large relative to the other resistors and can be replaced by an open circuit.

Solution for Resistance Values

Figure 4 shows how I converted the computed resistor values to standard resistor values. I also verified that my solution is still valid using standard resistor values (see Appendix B).

Figure M: Compute Standard Resistance Values and Double Check the Solution Still Works.

Figure 4: Compute Standard Resistance Values and Double Check the Solution Still Works.

As mentioned above, R2 is so large relative to the other resistances that it can be replaced by an open circuit. All requirements are met by the computed resistance values.


I was able to derive the required resistor values to meet the requirements that Xilinx imposed on the termination. This circuit will be used for terminating electronics within a high-speed fiber optic product.


I have been working in Aveiro, Portugal for the last week. The circuit was tested in my absence and worked.

Appendix A: Gain Requirements.

Figure 5 shows how Xilinx derived the gain requirements here.

Figure M: Derivation of Gain Margin Min and Max.

Figure 5: Derivation of Gain Margin Min and Max

Appendix B: Determining Closest Standard Resistor Value.

Figure 6 shows my routine for determining the closest standard resistor value to the computed resistor value.

Figure M: Determining Closest Standard Resistor Value.

Figure 6: Determining Closest Standard Resistor Value.











Posted in Electronics | 7 Comments

Naked and Afraid – Who Taps Out More? Men or Women


Quote of the Day

The best of ideas is hurt by uncritical acceptance and thrives on critical examination.

— George Polya, mathematician

Figure 1: Locations of 2-Person Naked and Afraid Episodes.

Figure 1: Locations of 2-Person Naked and Afraid Episodes.

I have been receiving quite a few questions about my database of Naked and Afraid information. I only track the shows with 2-person teams – I am not a big fan of the XL episodes. There recently has been a number of shows where women have been particularly strong leaders and finishers, and some big, tough-looking men struggled right from the start and failed to complete the challenge.   In response to some of the recent shows, a number of people have asked "Who taps out more – men or women?" So I have updated my Excel Workbook with the data from recent episodes and went to work. I have also updated Figure 1, which shows where the episodes were filmed.

I filtered my database for episodes where a woman finished and a man did not, or vice versa. This process generated a list of solo finishers and the number of days they were solo (Figure 2). Seventeen people finished the challenge alone, 9 men and 8 women.

igure 2: List of Solo Finishers and the Number of Days Solo.

Figure 2: List of Solo Finishers and the Number of Days Solo.

Figure 3 shows the summary statistics for the solo finishers. While there have been some strong female solo finishers lately, overall men and women have had similar solo performances, at least with respect to the average days solo and the number of solo finishers.

Figure 3: Summary Statistics of Solo Finishers.

Figure 3: Summary Statistics of Solo Finishers.

I am seeing a pattern in how men tap out that I do want to explore, but I am not certain how to obtain the data to prove it. It seems that the big, muscular men do not do as well as the smaller, wiry men. I do not have data on the size of the men, so I cannot perform this analysis right now – I am thinking about how to gather that data.









Posted in Naked and Afraid | 1 Comment

Royal Navy Losses in WW2


Quote of the Day

Figure out what you are not good at, and assume it will not change, and then hire someone to compensate.

John Sexton, quoted by Anne Marie Slaughter. This is critical advice for any manager. You personally do not have to be good at everything, but you must be able to attract and retain a group of people that can do everything.

Figure 1: Pivot Table of Royal Navy Losses During WW2.

Figure 1: Pivot Table of Royal Navy Losses During WW2. (Data Source)

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

A key difference  between US and UK naval losses is when the loss peak occurred. The peak losses would occur when (1) there were significant operations in progress, and (2) both sides had significant numbers of combatants. The US rate of loss peaked during 1944, while the Royal Navy's losses peaked during 1942.

In 1942, the Royal Navy was quite focused on Atlantic convoy protection, and they would have had their hands full during the Second  Happy Time of the German U-boat fleet. After 1942, British anti-submarine warfare operations became so efficient that serving on U-boat was almost a death sentence. In 1944, US naval losses would have peaked while the US Navy was focused on island hopping and the Japanese still had a significant navy. By 1945, the Imperial Japanese Navy (IJN) was a hollow force, and there were few IJN ships to engage US forces.

For those who are interested, my spreadsheet is here – along with the data, which I downloaded from a web page. The analysis was performed using Power Query, which is my primary tool for small data sets. To get a quick feel for the data, focus first on the sparklines at the right-hand side of the chart.


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

A Little Beer Data


Quote of the Day

Ladies and gentlemen, this is your captain speaking. We have a small problem. All four engines have stopped. We are doing our damnedest to get them going again. I trust you are not in too much distress.

— Pilot message on British Airways flight 9 from Singapore to Australia as it flew next to an erupting volcano near Indonesia, and ash shut down all four of the 747’s engines.


Figure 1: Label of the Most Bitter Beer.

Figure 1: Label of the Most Bitter Beer in the Dataset. (Link)

I gave a seminar last week on the use of Python with Pandas using Jupyter notebooks. When I give a seminar, I always have a worked example to illustrate the points that I am trying to make. This attendance at this seminar had a number of young men in it, so I decided to focus on a topic near and dear to their hearts – beer. I was amazed at the amount of beer knowledge showed by some individuals in the audience. Clearly, beer is a major part of their lives. I myself know NOTHING about beer, so I had to educate myself. The data provide interesting to analyze, and I will probably be augmenting my seminar with additional data over time.

My objectives during this seminar were on how to use Python with Pandas to:

  • illustrate how to import beer and brewery data files into a DataFrames
  • cleanup the imported data
  • illustrate how to merge (i.e. join) tables
  • graph the data
  • analyze the data using pivot tables

The presentation was well received, and I felt the information would be worth posting here.

I should mention that I have reduced my blogging rate because I have been busy preparing to build a retirement home in Northern Minnesota. I have never contracted a custom home before and the effort has been enormous and all-consuming. I will discuss my construction project in series of posts once I get everything kicked off – no heavy equipment moves in Northern Minnesota until the ground thaws.

For those of you who like to work along, I include my Jupyter notebook, data files, and an Excel version of this analysis here.



A brewery that produces small amounts of beer, typically much smaller than large-scale corporate breweries, and is independently owned. Such breweries are generally characterized by their emphasis on quality, flavor and brewing technique. The definition of a small amount of beer varies by state. For example, Missouri limits a microbrewer to no more than 10,000 barrels a year. (Link)
Craft Brewer
A term for the developments succeeding the microbrewing movement of the late 20th century. The definition is not entirely consistent but typically applies to relatively small, independently-owned commercial breweries that employ traditional brewing methods and emphasize flavor and quality. The term is usually reserved for breweries established since the 1970s but may be used for older breweries with a similar focus. For tax purposes, craft breweries produce less than two million barrels of beer a year. (Link)
International Bittering Units (IBU)
A metric used to approximately quantify the bitterness of beer. An IBU is measured in parts-per-million (ppm) of isohumolone, the main chemical compound derived from hops that makes beer taste bitter. Isohumulone is created when the alpha acids in hops isomerize, or breakdown, in the boil. (Link)
Alcohol By Volume (ABV)
ABV is defined as the number of millilitres of pure ethanol present in 100 millilitres (3.4 US fl oz) of solution at 20 °C (68 °F). The number of millilitres of pure ethanol is the mass of the ethanol divided by its density at 20 °C, which is 0.78924 g/ml. The ABV standard is used worldwide. (Link)
Proof is a measure of the content of ethanol (alcohol) in an alcoholic beverage. The term was originally used in the United Kingdom and was equal to about 1.75 times the alcohol by volume (ABV). The UK now uses the ABV standard instead of alcohol proof. In the United States, alcohol proof is defined as twice the percentage of ABV. (Link)
An alpha acid generated when hops breaks down during boiling. (Link)


Data Source

All the data for this analysis comes from this web site. I must applaud the author of this web site for the excellent illustration he provided on the work involved in scraping data from a complex web page. I do much web scraping myself, and his description of how to gather the data is the best I have seen.

Breweries with the Most Labels

As I looked at the data, I noticed that some breweries had an enormous number of beer names in their portfolio. So I generated Figure 1, which is a table of top ten breweries by number of beer names (also known as labels).

Figure 1: Craft Breweries with the Most Labels.

Figure 2: Craft Breweries with the Most Labels.

Beers By Alcohol Content (ABV)

The alcohol content of beer comes up occasionally and this data set allowed me to look at the distribution of alcohol content by volume (i.e. ABV). First, I looked at the top ten beers by ABV (Figure 3). Note that many beers had ABV values of 9.9%, so the beers you see in the table simply sorted higher than the others.

Figure M: Top 10 Craft Beers By ABV.

Figure 3: Top 10 Craft Beers By ABV.

The beers in Figure 3 are actually outliers. The bulk of craft beer ABV values are in the 5% range (Figure 4).

Figure M: ABV Histogram.

Figure 4: ABV Histogram.

My preferred way to view empirical distributions is with a violin plot, which I show for ABV values in Figure 5. The violin plot also allows you to show quartiles, which are shown as dashed lines.

Figure M: ABV Violin Plot.

Figure 5: ABV Violin Plot.

Cities With The Most Craft Breweries

Figure 6 shows the top ten cities by craft brewery number. I was not surprised at all to see Portland OR and Bend OR on the list.

Figure 6: Cities with Most Craft Breweries.

Figure 6: Cities with Most Craft Breweries.

Top 10 Beers By Bitterness (IBUs)

Figure 7 shows the top ten beers by bitterness. After I presented this table, staff members started talking about how they could obtain a small stock of these beers. I am floored that there are people who seek out bitter beer.

Figure 7: Top Ten Beers By IBU.

Figure 7: Top Ten Beers By IBU.


The reviews of the seminar were good! My team uses Python for its automation language, but has not been using Pandas or Jupyter notebooks – they still use Excel to present their test data. I am hoping that my presentation motivates them to consider Jupyter notebooks and Panda so that all their work can be done without switching software.

I added Figure 8 because I found it interesting that some smaller brews are becoming commonly available in many states. For example, Summit products are available everywhere in Minnesota.

Figure M: Beers Most Likely on a Menu By State.

Figure 8: Beers Most Likely on a Menu By State. (Link)

Posted in Python, software | Leave a comment

US Naval Losses in WW2


Quote of the Day

Every time you find your attention captured by an advertisement, your awareness, and perhaps something more, has, if only for a moment, been appropriated without your consent.

— Tim Wu

Figure 1: US Naval Losses in WW2. (Source)

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.

To help illustrate the loss rate by year, I added sparklines on the right-hand side of the table. The sparklines do make it clear that the pace of battle was high during 1942 and 1944. It is difficult to assess the tempo of naval losses in 1945 from this chart because (1) 1945 was a partial year of WW2, and (2) the Imperial Japanese Navy was a shell of its former self during most of 1945.

There is nothing particularly special about this data or my analysis, but I thought I would make the spreadsheet available to others in case they were interested in looking at WW2 data – there are a fair number of discussion groups on this topic. Having the data in a spreadsheet makes it easy to work with.

Posted in History Through Spreadsheets | 7 Comments