Using Excel to View US Pre-School Attendance Rate

Posted on 4-January-2018 by mathscinotes

Quote of the Day

We see a lot of feature-driven product design in which the cost of features is not properly accounted. Features can have a negative value to customers because they make the products more difficult to understand and use. We are finding that people like products that just work. It turns out that designs that just work are much harder to produce than designs that assemble long lists of features.

— Douglas Crockford, author of JavaScript: The Good Parts. I encounter many marketing people who view product definition as the mere listing of features. In reality, there is a balance that must be achieved.

Figure 1: Graph Being Discussed by Jeffery Sachs. (Source)

Figure 1: Graph Being Discussed by Jeffery Sachs. (Source)

Jeffrey Sachs was on CSPAN this weekend giving a talk on the competitive challenges the US faces with other nations. During his presentation, he showed a chart (Figure 1) that ranks the US as 30th among reporting OECD countries with respect to preschool participation rates for 4-year-old children. The discussion was interesting, but I found myself focusing on the technical aspects of the graphs he was using. I am always looking for good Excel examples for use in training my staff, and the y-axis in Figure 1 contains formatted text, which is something I have not shown my staff how to do.

Figure 2: My Excel Version of Figure 1.

Figure 2: My Excel Version of Figure 1.

In Figure 2, I show how my duplication of Figure 1 using Excel. For those who like to follow along, my workbook is here. To highlight the formatting of the y-axis, I used green and red colors instead of bold font.

My process was straightforward:

  • Use Power Query to grab the data from this web site.
  • Generate a bar chart.
  • Use the method of Jon Peltier to format the y-axis.

While this is not a sophisticated chart, it does provide an end-to-end example of web scraping and charting.

Posted in Excel | Comments Off on Using Excel to View US Pre-School Attendance Rate

Dealing with Furnace Condensate At Low Temperatures

Posted on 2-January-2018 by mathscinotes

Quote of the Day

If all printers were determined not to print anything till they were sure it would offend nobody, there would be very little printed.

- Benjamin Franklin

Introduction

Figure 1: A Typical Condensate Pump. (Source)

Figure 1: A Typical Condensate Pump. (Source)

While working on my retirement home and workshop in northern Minnesota, I have noticed that my furnace is generating between five and seven gallons of condensate per day. The furnace is on quite often this time of year because the outside temperature is running about -30 F (-35 C). So, we make sure to check for any issues each month and have the number of a plumbing company Airdrie that will be able to repair it ASAP if it ever breaks. But I want to do something about the amount of condensate it produces. I currently pipe the condensate over to a floor drain, which is connected to my septic system.

I mentioned the amount of condensate to my General Contractor (GC), and he said that this condensate can be an issue with a septic system in a cold climate because septic systems work best when they receive significant amounts of water flow. He said that he trickle of water can create a blockage if the flow is so low that it can freeze. Frozen pipes mean broken/blocked pipes and condensate water backing up into the house. If you are a homeowner that is gone for long periods of time during the winter – like vacationing in a warmer climate – you could return to a house with water damage. Of course, there are companies out there, like 1-800 WATER DAMAGE of Nassau County and many more, that can come to repair and restore any damage caused by water - but no homeowner wants to return to a flood.

My GC said that condensate pumps (Figure 1) resolve this issue by collecting the condensate water and releasing it in surges, which ensures that a significant amount of water is sent down the drain. These surges are very similar in size to that produced by a toilet and are very unlikely to freeze. I went online and confirmed that others use this solution to resolve their issues with a condensate pump (example).

This discussion generated a few questions that a bit of math can help me answer.

  • How much water is generated per BTU of furnace heat?
  • How much propane is consumed per BTU of furnace heat?

Background

Propane Characteristics

I obtained my information on propane from the Wikipedia:

Where is the Water Coming From?

Burning hydrocarbons generates water. We don't think about this water much because it often floats away in the form of steam. In the case of a furnace like mine, this steam is condensed so that its heat of vaporization can be captured and used to heat the building. In the case of a propane system like mine, the amount of water produced by the propane combustion can be computed by looking at the chemical formula for propane combustion (Equation 1).

Eq. 1 \displaystyle {{\text{C}}_{\text{3}}}{{\text{H}}_{\text{8}}}\text{+5}{{\text{O}}_{\text{2}}}\to \text{3C}{{\text{O}}_{\text{2}}}\text{+4}{{\text{H}}_{\text{2}}}\text{O}

where the chemical symbols are:

    • Propane, C3H8
    • Oxygen, O2
    • Carbon Dioxide, CO2
    • Water, H2O

Equation 1 tells us that for every mole of propane burned, we generate four moles of water.

Analysis

My Furnace Characteristics

To answer my questions, we need to discuss my furnace and how it is running during this cold snap. Here are the critical parameters:

  • Furnace heat output: H = 100,000 BTU/hour (hr)
  • Furnace efficiency: ϵ = 96.3%
  • Furnace duty cycle: dc = 30%

Calculations

Figure 2 shows my calculations. The key results are:

  • A propane-powered furnace will generate about 1 gallon of condensate per every 100,000 BTU of heat generated.
  • My furnace under these cold conditions is generating between 6 and 7 gallons of water condensate every day.
  • During the cold spell, I am burning about 34 pounds of propane each day.
Figure 2: Analysis of Condensate Generation and Propane Consumption.

Figure 2: Analysis of Condensate Generation and Propane Consumption.

Propane Heat of Combustion Propane Molar Mass

Conclusion

I now understand why I am seeing so much condensate water now. I will be installing a condensate pump this weekend.

Posted in Construction, General Science | 13 Comments

Audie Murphy's Rifle and the Power of Databases

Posted on 26-December-2017 by mathscinotes

Quote of the Day

The measure of leadership is not the quality of the head, but the tone of the body. The signs of outstanding leadership appear primarily among the followers.

— Max De Pree, businessman and writer

Figure 1: Audie Murphy, the most decorated US Soldier of WW2. (Source)

Figure 1: Audie Murphy, the most
decorated US Soldier of WW2.
(Source)

When I was a boy, I read the memoir To Hell and Back by Audie Murphy and was very impressed with his accomplishments as an infantry soldier during WW2 (Figure 1). It is a very American tale – a dirt poor teenager from a family with a dead mother and missing father accomplishes amazing feats through sheer determination and force of will. He later starred in a movie version of his book that is well worth watching. I should mention that the book tells a better tale than the movie.

I recently read that the US Army had recovered his favorite rifle, which was an M1 carbine. The M1 carbine was shorter and much lighter than the infantry's standard M1 Garand. The carbine was usually carried by troops who had limited space available (e.g. tankers) or who had to carry other things (e.g. radiomen, paratroopers). For example, my father was a radioman and he carried an M1 carbine. In Murphy's case, he carried many different weapons but appeared to prefer the M1 carbine. The story of its recovery is a testament to the power of modern database technology. The key to recovering the rifle was an interview with Murphy that provided a key piece of information – the serial number of the rifle.

Figure 2: Serial Number on Audie Murphy's M1 Carbine.

Figure 2: Murphy's M1
Carbine Serial Number.
(Source)

When Murphy had the rifle, it certainly had certainly seen better days. The explosion of a nearby mortar round had damaged it, and Murphy did a field‑expedient repair on it using a wire. He continued to use the rifle, which he referred to as his "wounded carbine". I have read that at various times Murphy had used a Thompson sub-machine gun, an M1 Garand, and the M1 carbine. He must have really liked this rifle because, during a 1967 interview, Murphy mentioned its serial number, 1108783 (Figure 2). Over six million of these rifles were produced during WW2, but that serial number provided a means for uniquely identifying that rifle.

Figure 1: Warehouse in Movie Raiders of the Lost Ark. (Source)

Figure 3: Warehouse in the
movie Raiders of the Lost Ark.
(Source)

The exact story of how the rifle left Murphy's possession is unclear. It appears that Murphy was wounded by a sniper on 25-Oct-44. Thinking that the wound may send him home, Murphy gave his rifle to a sergeant who hoped that the carbine would bring that him luck. Unfortunately, most of that sergeant's platoon was wiped out the following day. It is believed the rifle was recovered from the battlefield by the US Army, properly repaired, and put into storage. When you think of US government storage, think of a warehouse similar to that shown at the end of the movie Raiders of the Lost Ark (Figure 3). It seems like a miracle that this specific rifle could be pulled out of a warehouse decades after the war, but it really happened. A person at the Center of Military History Clearinghouse at the Anniston Army Depot did a database search for that serial number, got a hit, and the rifle was found (Source).

Figure 4 shows the rifle in its museum display today. I should mention that another movie,  Carbine Williams, was made that involved the M1 carbine. It is the story of a convict, Marsh Williams , who created the basic operating mechanism of the gun while serving time in a North Carolina prison. If you are curious about the four rifles he designed while in prison, see this Wikipedia paragraph.

Figure 4: Museum Display of Audie Murphy's Rifle, Gear, and Medals from WW2.

Figure 4: Audie Murphy's M1 Carbine in Museum Display. (Source)

I do have my own tale of trying to recover something from government storage, but it is much less interesting. Back in the early 1990s, I worked on the development of a very small sonar system that used low-voltage ceramic transducers. The US Navy paid $30 million for the development of this technology, which worked but the Cold War was ending and they decided not to pursue the technology any further. We sent the sonar system to the US Navy for storage. A few years ago, I got a call from a contractor who was wondering if I knew how to find the sonar system because the US Navy wanted to resurrect the project. I told him the name of the government employee that was sent the unit – I was concerned that he may have retired. The contractor called me back two weeks later and said that the government employee was still working, and he had the sonar system in his office! It never went into storage because it looked so cool that he had decided to use it as a doorstop. The $30 million doorstop was returned to the contractor, who found that it still worked, and he used it to pursue further development of the technology. I chuckle just writing that – $30 million doorstop.

Posted in Military History, software | 10 Comments

Earth's Curvature and Battleship Gunnery

Posted on 20-December-2017 by mathscinotes

Quote of the Day

Eggshells smashing each other with hammers.

— Winston Churchill, describing his feelings on battleship combat.

Introduction

Figure 1: Factors Affecting Range Ballistics. (Source)

Figure 1: Factors Affecting Range Ballistics. (Source)

I must admit that I am a bit of a battleship junkie. I have been reading some old US Navy manuals on battleship fire control, which discuss the various effects that must be corrected for to ensure accurate fire (Figure 1). In this post, I want to examine how the curvature of the Earth affected the gunnery direction. Curvature corrections are only needed for very long-range artillery.

Figure 2: Range Table for US Navy 16-inch/50 caliber. (Source)

Figure 2: Range Table Excerpt for US Navy 16-inch/50
caliber. (Source)

Gunnery direction calculations usually begin with a range table (Figure 2), which tells the gunner the angle that projectile must be fired at to hit a target at a given range on the same horizontal plane as the gun (i.e. no difference in height between the gun and target). The target height relative to the gun can be either positive or negative, which affects the range that is used to index into the range table . For example, battleships in WW2 doing shore bombardment sometimes needed to attack fortifications on mountains (e.g. Mount Suribachi on Iwo Jima). For sea-level sea battles, the targets are below the horizontal plane of the ship firing the projectile.

Figure 3: Example Where Target is Lower Than the Gun. (Source)

Figure 3: Example Where Target is Lower Than
the Gun. (Source)

Figure 3 shows that firing at a target that is at sea level also involves a difference in heights. The rangefinders on a battleship determined a Line-Of-Sight (LOS) distance, but that distance is not the same as the horizontal distance listed in the table of Figure 2. The LOS distance must be corrected to an effective horizontal distance that can be looked up in the range table. My goal in this post is to show how we can correct the LOS distance to provide the required horizontal distance, which can then be used to read the gun elevation from the  table in Figure 2.

All calculations are performed in Excel – my workbook is here.

Background

Earth Curvature Calculation

I have written about how to compute the curvature of the Earth over a given distance in another post using Equation 1, which relates the deviation from horizontal to the distance from the measurement origin.

Eq. 1 \displaystyle \delta =\sqrt{{{{R}^{2}}+{{R_{LOS}}^{2}}}}-R

where

  • δ deviation from horizontal, which is called curvature in gunnery.
  • R is the radius of the Earth (3963.2 miles)
  • RLOS is the LOS distance.

These parameters are illustrated in Figure 5.

We can use Equation 1 to compute a curvature versus range table (Figure 4). This table duplicates the results shown in this reference.

To illustrate how to read this table, consider the range of 19,800 yards. We go to the row that corresponds to 19,000 yards and find the column that corresponds to 800 yards. At the intersection of the row and column, we find a curvature of 84 ft.

Figure 4: Table of Curvatures for Different Horizontal Ranges.

Figure 4: Table of Curvatures for Different Horizontal Ranges. This figure shows how to find the curvature for a range of 19,800 yards, which is 84 feet.

Rate of Height Change

The US Navy manuals refer to "Column 19" and the "Change in height of impact for variation of 100 yards in sight bar."  While this sounds like a complex parameter, it is simply the tangent of the projectiles impact angle with respect to horizontal, which is called the angle of fall and is listed in the range table shown in Figure 2. The tangent of the angle of fall tells you how many feet the projectile loses in height for every foot of horizontal distance. We will use this parameter to relate the height difference to the range correction.

Analysis

Earth Curvature Correction Calculation

Figure 5 defines some variables using the illustration of Figure 3. You can see in Figure 5 hitting target on a requires reducing the range setting of the gun (RH) from the distance measured along the line of the sight (RLOS) by Δ, i.e. {{R}_{H}}={{R}_{{LOS}}}-\Delta.

Figure 5: Illustration of the Range Correction.

Figure 5: Illustration of the Range Correction.

For modeling purposes in Figure 6, we can treat the trajectory of the shell near the target as a straight line. This allows us to use a simple trigonometric function to compute Δ, i.e. \text{tan}\left( {{{\theta }_{{Fall}}}} \right)=\frac{\delta}{\Delta }\Rightarrow \Delta =\frac{\delta}{{\text{tan}\left( {{{\theta }_{{Fall}}}} \right)}}.

Figure 6: Details on the Correction Term Δ.


Figure 6: Details on the Correction Term Δ.

Example

I copied a section of the range table from the US Navy manual and used it to compute: (1) curvature; (2) change in height of impact for variation of 100 yards in sight bar (i.e. LOS range); (3) danger space (discussed in this blog post). I can verify that (1) and (2) agree with the manual. Item (3) is discussed but not listed in the manual tables.

Figure 7: My Duplication of Curvature Correction Table.

Figure 7: My Duplication of Curvature Correction Table.

Conclusion

I am interested in understanding the gunnery corrections for the Earth's curvature and the Coriolis effect. I believe this post thoroughly covers the curvature correction.  I will put out a post shortly on the correction for the Coriolis effect.

Posted in Ballistics, Naval History | 102 Comments

Super Bowl Winners and Losers Using Power Query

Posted on 13-December-2017 by mathscinotes

Quote of the Day

In Data Science, 80% of time spent prepare data, 20% of time spent complain about need for prepare data.

-Tweet from Big Data Borat. I must admit, I am amazed at the poor format of data on the web. I work with a lot of old WW2 data that was processed by human typists, so I understand the quality issues there. By there is no excuse for the poor formatting of machine-processed data today.

Figure 1: Teams with Most Super Bowl Wins.

Figure 1: Teams with Most Super Bowl Wins.

I was reading a post on Statista showing the NFL teams with the most Super Bowl wins. Since my staff includes a number of football fans - mainly Viking and Packer supporters - I decided it would be a good training exercise to show them how to gather the football statistics and present them in the same manner as shown on Statista. I should mention that I do not follow football at all; this is purely a data analysis exercise for me. So those who want to place bets using Sports Betting America after looking at the data can do so if they want, but for me, I just love the statistical analysis of it.

I used web resources and Power Query to generate an Excel workbook that I could then use to generate the charts I wanted. I duplicated the Statista graph in Figure 1. In addition to gathering the data, I also used a "bulk" substitution routine put together by Miguel Angel Escobar, also known by his Youtube handle as The Power User. My spreadsheet is available here.

Figure 1: Teams with Most Super Bowl Wins.

Figure 2: Teams with Most Super Bowl Losses.

In addition to duplicating the Statista chart, I also want to look at the number of Super Bowl losses. I live in Minnesota and our Vikings team has never won the Super Bowl, though it has made four appearances. Figure 2 shows a list of NFL teams ranked by their number of Super Bowl losses.

The Denver Broncos have the most losses with 5, but they also have had three wins. The Vikings, Patriots, and Bills have all have had four losses. While the Patriots have had five wins, neither the Vikings nor Bills have had even a single Super Bowl win.

Figure 3: Number of Super Bowl Appearances By Teams with No Super Bowl Wins.

Figure 3: Number of Super Bowl Appearances By
Teams with No Super Bowl Wins.

In Figure 3 shows the number of Super Bowl appearances by teams with no wins. The Vikings and Bills lead this list. Things could be worse. Notice how the Jaguars, Texans, Lions, and Browns have never been to the Super Bowl. I especially feel sorry for the Lions and Browns, who have long established franchises.

I did put the data into table form, which I show in Table 1.

Table 1: NFL Super Bowl Statistics

Posted in Excel, Statistics | Comments Off on Super Bowl Winners and Losers Using Power Query

My Star Trek TOS Database

Posted on 8-December-2017 by mathscinotes

Quote of the Day

Well, if there is a bright center to the universe, you're on the planet farthest from.

—Luke Skywalker, expressing the lament of a young person growing up in a small town. As a person who grew up in a small, rural Minnesota town, I understand his feelings perfectly.

Figure 1: Spock with Whiz Wheel.

Figure 1: Spock with Whiz Wheel. I love the
anachronism here. (Source)

The people on my team frequently chide me for using management analogies drawn from the original Star Trek television series, which fans refer to as Star Trek TOS. Because of these analogies, I need to provide exact references for the younger folks on our team who have not memorized every episode. I decided that it was time for me to put together an online list that I could search and sort as the need arises. I grabbed the data from the Internet Movie Database (IMBD), including their review score for each episode.

The IMDB ratings did provide me with one surprise. While I expected City on the Edge of Forever to be the most popular episode, I was a bit surprised to see Mirror, Mirror as the second most popular.

Table 1: Star Trek TOS Episodes and Ranking.

Posted in Science Fiction | 5 Comments

Cannon Bore Measured in Pounds

Posted on 7-December-2017 by mathscinotes

Quote of the Day

Gentlemen, we will chase perfection, and we will chase it relentlessly, knowing all the while we can never attain it. But along the way, we shall catch excellence.

— Vince Lombardi Jr., the son of legendary Green Bay Packer Coach Vince Lombardi.

Figure 1: 17 Pdr Anti-Tank Gun on Sherman Firefly .

Figure 1: 17-Pounder Anti-Tank
Gun on Sherman Firefly . (Source)

I have been pulling together some WW2 data for an article that I want to publish in the next year or two. During my research, I have noticed that the British specified the caliber of their artillery by the nominal mass of the projectile (lbm or pound mass) and not by the bore diameter (Figure 1). I was curious as to how the British came to this particular system and decided to investigate further. As with many military standards, it traces its history back hundreds of years.

Back in the mid-17th century, cannons fired solid iron, spherical shot. This means that the application of a bit of geometry allows one to relate the diameter of the shot to the mass of the shot. The use of mass to determine the size of something was very common in the old days when weight measurements were far easier than dimensional measurements. We still see thread diameter measured using pounds – as long as the thread is made consistently (standard length, consistent thickness and material), weight can be used to specify thread diameter.

Equation 1 shows the formula that I will use to relate the diameter of spherical shot to the its mass. I will use Equation 1 to calculate a table that I found in a document from 1768 (Muller, pg. 6) that lists both the diameter of the shot and the gun to the mass of the projectile. Note that breach-loading artillery must have a bore diameter (referred to as the caliber) that is slightly larger than the diameter of the shot so that the shot can be forced down the barrel when covered in wadding. In 1768, the standard was that the caliber was 5% larger than the shot diameter.

Eq. 1 \displaystyle M=\rho \cdot \frac{4}{3}\cdot \pi \cdot {{\left( {\frac{D}{2}} \right)}^{3}}\Rightarrow D=\sqrt[3]{{\frac{{6\cdot M}}{{\rho \cdot \pi }}}}

where

  • M is the mass the round shot.
  • ρ is the density of the material making up the solid shot.
  • D is the diameter of the round shot.

The calculation of the cannon caliber (i.e. bore diameter) is simply 1.05·D, where D is the output of  Equation 1. I used the Excel spreadsheet here to generate my version of the original table (Figure 2).

Figure M: My Version of the Muller Table.

Figure 2: My Version of the Muller Table.

The values I compute are close to those shown in the original table (Figure 4).  I assume the errors are due to routine calculation errors on the part of the human calculators,  which can easily occur when using logarithm tables. I shudder just thinking about performing these calculations by hand using logarithms. I spent weeks in Osseo Junior High School  mastering interpolating log tables for just such hand calculations. Very little had changed between 1768 and 1971.

I should mention that Muller computes the density of the shot material based on a 9 pound projectile with 4 inch diameter – this projectile constituted both a diameter and weight calibration reference similar in spirit to the international prototype kilogram and meter.  I compute the density of the shot reference as  7.43 gm/cm3, which is very close to the density of pure iron (7.874 gm/cm3).

Using the term pounder to express the diameter of an artillery projectile today is not particularly useful because the mass of a projectile today does not uniquely determine its diameter. In fact, the vast majority of modern projectiles are composed of multiple types of materials (e.g., steel, copper, tungsten, explosive, tantalum, etc) in multiple types of geometries (e.g., shells, arrows, winged, rocket-boosted, etc)  based on their function. Thus, diameter and mass are not as strongly correlated today as 300 hundred years ago. Figure 3 shows how the pounder unit relates to the projectile diameter for some modern UK artillery. Notice how projectile mass does not uniquely determine the diameter of a modern projectile. This approach has become unworkable and the UK now specifies projectile diameter.

Figure 3: List of Modern Artillery With Their Bore Diameters and Pounder Designation.

Figure 3: List of Modern Artillery With Their Bore Diameters and Pounder Designation. (Source)

Appendix A: Original Table from Muller Document

Figure 4 shows the original table from the Muller document. Note that I actually corrected one obvious error in this table – duplicate values of 3.668 in the top caliber row (marked in red). Errors like this are very common. I have spent hours trying to find errors in old WW2 records. I do have sympathy for the poor yeoman who had to type in all this data.

Figure 3: Table of Shot Masses, Diameters, and Calibers.


Figure 4: Table of Shot Masses, Diameters, and Calibers.

Posted in Military History | Comments Off on Cannon Bore Measured in Pounds

The Pacific War and Manufacturing Capacity

Posted on 4-December-2017 by mathscinotes

Quote of the Day

Our job is to teach the students we have. Not the ones we would like to have. Not the ones we used to have. Those we have right now. All of them.

— Dr. Kevin Maxwell, teacher. I saw this quote on Dr. Nic's blog, which is one of my favorites.

Introduction

Figure 1: US Political Cartoon from 1942.

Figure 1: US Political Cartoon from 1942.

I was watched a particularly interesting lecture by Victor Davis Hanson on his new book The Second World Wars. While Hanson is generally thought of as an ancient Greek scholar, he does an excellent job of analyzing WW2 from a novel set of viewpoints: ideas, air, water, earth, fire, and people. One particular emphasis of Hanson is the role of the dominating manufacturing capacity of the Allied powers versus the Axis powers. I have listened to a number of WW2 history lectures recently, and all of them emphasized that  the US WW2 strategy from the beginning was to build massive numbers of medium quality weapons that would overwhelm Japan and Germany by sheer numbers. This approach was based on the belief that quantity can cover up all sorts of shortcomings with quality, personnel, and training.

My focus here will be on the Pacific War because a number of the lectures also focused there. I have never before looked at the relative production levels of the Allies versus Japan. I decided to grab some military production data for the UK, US, and Japan from this web site using Power Query and Excel. I did not look at military production for the Soviet Union because their contributions to the Pacific War were minimal. For those interested in my analysis, the spreadsheet is here. This was a good exercise in basic web scraping and table generation using Excel. I looked at three areas associated with wartime manufacturing: merchant shipping production (tonnage), aircraft production, and warship production.

Analysis

Merchant Shipping

Merchant shipping is the lifeblood of island nations like the UK and Japan. Figures 2 and 3 show merchant ship production data for the US, UK, and Japan during WW2. Figure 2 shows that the US and UK produced nearly ten times the merchant ship tonnage as did Japan. An order of magnitude advantage in shipping capacity when you are fighting an island war is overwhelming.

Figure 2: Merchant Ship Production Tonnage By Country.

Figure 2: Merchant Ship Production Tonnage By Country.

Figure 3 shows the data of Figure 2 in graphical form.

Figure 2: Merchant Ship Tonnage Versus Time For the US, UK, and Japan.

Figure 3: Merchant Ship Tonnage Versus Time For the US, UK, and Japan.

Aircraft Production

Because the US strategy was focused on island hopping, the production of aircraft was also critical because a single island airbase could secure a region of many tens of thousands of square miles – these island airbases were sometimes referred to as unsinkable aircraft carriers. As is shown in Figure 4, the US and UK dominated in aircraft production as well, though not by as much as seen with merchant ship production.

Figure 4: WW2 Aircraft Production By Type and Country.

Figure 4: WW2 Aircraft Production By Type and Country.

Warship Production

Ultimately, warships were needed because there was a war to win. I looked at the production of six classes of warships: aircraft carriers, battleships, cruisers, destroyers, escorts, and submarines. I started my counting in 1942, which is when the US really began warship production in earnest. Figure 5 shows a table of the data and Figure 6 shows a graphical view. The differences in the warship counts again reflect an order of magnitude difference – for example, the US and UK produced 151 aircraft carriers (all types) from 1942 to 1945 while Japan produced 15.

One stark difference between Japan and the UK and US alliance was in the area of escort vessels, which were warships designed to protect merchant convoys from air and submarine attacks. The data source I used for this post claimed that the Japanese had no escort vessels. Some people may argue that one class of Japanese destroyer (Matsu) could be viewed as an escort, but only seventeen were built and these had minimal impact because they were deployed at the very end of the war. In any event, the UK and US built hundreds of escort vessels to protect their supply lines. Japan did not give priority to protecting their supply lines, which meant starvation was a real threat once they were isolated.

Figure M: Warship Production By Nation and Year.

Figure 5: Warship Production By Nation and Year.

Figure 6 provides a visual representation of the warships produced between 1942 and 1945. I used the color green for Japan. You see very little green in Figure 6.

Figure M: Graphical View of Figure M.

Figure 6: Graphical View of Figure 5.

Conclusion

The US gave the European Theater higher priority for resources than the Pacific Theater; historians usually talk in terms of 30% to the Pacific and 70% to Europe. This approach was referred to as Europe First. While Japan was only given second priority, the data in this post shows that the US and UK still had more than enough resources to dominate over Japan. Once Germany was defeated, Japan was facing opponents with truly awesome capacity for destruction. I am amazed that they even considered starting a war with the US, let alone the Allies (US, UK, Australia, New Zealand, and the Soviet Union). I hope modern leaders learn just how easy it is to miscalculate when it comes to military action.

Posted in History Through Spreadsheets, Military History | Comments Off on The Pacific War and Manufacturing Capacity

Statistics of US Senate Disciplining Its Members

Posted on 21-November-2017 by mathscinotes

Quote of the Day

The Oversimplification, The Cherry Pick, The Butter-up Undercut, The Demonizer, The Blame the Blogger, The Ridicule and Dismiss, The Literal Nitpick, The Credit Snatch, The Certain Uncertainty, The Blind Eye to Follow-Up, The Lost in Translation, The Straight-Up Fabrication.

— David Levitan (author of the book Not a Scientist), his categorization of the methods politicians use to obfuscate science. I have seen our politicians use every method he described. Politicians generally begin their attack on science with the phrase "I am not a scientist but …"

Figure 1: Drawing of Senators Fist Fighting in the 1800s.

Figure 1: Senators Fist Fighting. (Source)

The controversial senate candidacy of Roy Moore has resulted in some discussion of how the US Senate would respond if he won the election. Senate Majority Leader Mitch McConnell has mentioned the possibility of expulsion. I could not recall hearing of anyone being expelled from the Senate recently, so I decided to grab some data from the Senate web site and summarize it here.

I used Power Query to download and process the data from the Senate website. Figure 2 shows my summary of all expelled Senators. Only 15 senators have been expelled, with 14 being expelled for their participation in the Confederacy. The last senator to be expelled was in 1862.

Figure 2: List of All Expelled US Senators.


Figure 2: List of All Expelled US Senators.

There are other ways the US Senate can discipline its members – actions like censure, condemnation, and denunciation. Near as I can tell, people distinguish condemnation and denunciation from censure, but there really is no difference (e.g. discussion of McCarthy). Figure 3 show the list of senators subjected to forms of discipline other than expulsion. I also included those senators who resigned or whose terms ended before their disciplinary reviews were completed. The last senator to resign while undergoing disciplinary review was Robert Packwood back in 1995.

Figure 3: Senators Accused of Bad Behavior But Not Expelled.

Figure 3: Senators Going Through Disciplinary Actions Other Than Expulsion.

For those who are interested, my workbook is here.

Posted in Civics Through Spreadsheets | 5 Comments

An Interstellar Visitor?

Posted on 20-November-2017 by mathscinotes

Quote of the Day

In the days of my youth, the trickle-down theory was called the horse and sparrow idea of economics: If you feed a horse enough oats, some of it will go through the horse and fall on the road for the sparrow.

— John Kenneth Galbraith (Post-Keynesian economist). I guess the common people are the sparrows? We get to pick through the manure?

Figure 1

Figure 1: Asteroid A/2017 U1. (Source)

Astronomers are now working on determining if a recently discovered asteroid is an interstellar visitor. The first observations of this asteroid were made by a team of researchers at the University of Hawaii's Pan-STARRS 1 telescope on Haleakala in Hawaii (Figure 1).

The astronomers were excited when they determined the orbital parameters of the asteroid and saw that its orbit has an eccentricity of 1.2, which means that it is on a hyperbolic trajectory through the solar system. Its estimated interstellar speed is 26.3 km/s, which is also known as the hyperbolic excess velocity. Figure 2 shows how to compute this value. The links (brown color) in Figure 2 are live and lead you to explanatory web pages.

Figure 2: Calculation of U1's Interstellar Velocity.

Figure 2: Calculation of U1's Interstellar Velocity.

Good Stack Exchange Discussion Good general discussion by principal Chat Group Discussion Mass of Sun Universal Gravitational Constant Standard Grav. Parameter Wikipedia Page on U1 Interstellar Velocity

The observations showed that it was likely a reddish, low albedo object probably similar to  D-type or P-type asteroids. The Wikipedia lists the absolute magnitude of asteroid U1 as 22.08. If we assume the albedo is low (~10%) like a D or P-type asteroid, we can estimate U1's equivalent diameter using the formula from this post to be 160 meters (Figure 3). The calculation in Figure 2 agrees with the value given in the Wikipedia.

Figure 2: Estimating the Size of Asteroid U1.

Figure 3: Estimating the Size of Asteroid U1.

The computed trajectories of asteroid A/2017 U1 show that it will pass in and out of our solar system (Figure 4).

Figure 3: Trajectory of Asteroid A/2017/ U1 Through Our Solar System.

Figure 4: Trajectory of Asteroid A/2017/ U1 Through Our Solar System. (Source)

Scientists have long speculated on the number of rogue bodies circulating between the stars – here is a list of candidate rogue planets.

One of my favorite science fiction books, When Worlds Collide, is a story written in the 1930s about a rogue planet being captured by our Sun. While the language used in the book is a bit dated, it is still popular enough that it remains in print. Hollywood made a B-movie that is loosely based on the book (Figure 5).

Figure 3: When Worlds Collide Movie Poster.

Figure 5: When Worlds Collide Movie Poster.

As an aside, my favorite science fiction book is After Worlds Collide, which is the sequel to When Worlds Collide.

Postscript

Here is an artist's conception of the asteroid's shape given recent data (Figure 6).

Figure M: Artist's Conception of Asteroid's Shape Based on European Space Agency Data. (Source)

Figure 6: Artist's Conception of Asteroid's Shape Based on European Space Agency Data. (Source)

Posted in Astronomy | Comments Off on An Interstellar Visitor?