## Super Bowl Winners and Losers Using Power Query

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.

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.

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

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

RankTeamGamesWinsLosses
1Pittsburgh Steelers862
2San Francisco 49ers651
3Dallas Cowboys853
4New England Patriots954
5Green Bay Packers541
6New York Giants541
7Los Angeles/Oakland Raiders532
8Washington Redskins532
9Denver Broncos835
10Baltimore Ravens220
11Baltimore/Indianapolis Colts422
12Miami Dolphins523
13New Orleans Saints110
14New York Jets110
15Tampa Bay Buccaneers110
16Chicago Bears211
17Kansas City Chiefs211
18St. Louis/Los Angeles Rams312
19Seattle Seahawks312
20Buffalo Bills404
21Minnesota Vikings404
22Atlanta Falcons202
23Carolina Panthers202
24Cincinnati Bengals202
26Arizona Cardinals101
27San Diego/Los Angeles Chargers101
28Tennessee Titans101
29Cleveland Browns000
30Houston Texans000
31Detroit Lions000
32Jacksonville Jaguars000

## My Star Trek TOS Database

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

EpisodeTitleRatingPlot Summary
1-01The Man Trap7.3Dr. McCoy discovers his old flame is not what she seems after crew members begin dying from a sudden lack of salt in their bodies.
1-02Charlie X7.2Captain Kirk must learn the limits to the power of a 17-year-old boy with the psychic ability to create anything and destroy anyone.
1-03Where No Man Has Gone Before7.8The flight recorder of the 200-year-old U.S.S. Valiant relays a tale of terror--a magnetic storm at the edge of the galaxy!
1-04The Naked Time8.0The crew is infected with a mysterious disease that removes people's emotional inhibitions to a dangerous degree.
1-05The Enemy Within7.8A transporter malfunction splits Captain Kirk into two halves: one meek and indecisive, the other violent and ill tempered. The remaining crew members stranded on the planet cannot be beamed up to the ship until a problem is fixed.
1-06Mudd's Women6.9The Enterprise picks up untrustworthy entrepreneur Harry Mudd accompanied by three beautiful women who immediately put a spell on all the male crew members.
1-07What Are Little Girls Made Of?7.6Nurse Chapel is reunited with her fiance; but his new obsession leads him to make an android duplicate of Captain Kirk.
1-08Miri7.1The Enterprise discovers a planet exactly like Earth, but the only inhabitants are children who contract a fatal disease upon entering puberty.
1-09Dagger of the Mind7.5Kirk and psychiatrist Helen Noel are trapped on a maximum security penal colony that experiments with mind control and Spock must use the Vulcan mind-meld to find a way to save them.
1-10The Corbomite Maneuver8.2After the Enterprise is forced to destroy a dangerous marker buoy, a gigantic alien ship arrives to capture and condemn the crew as trespassers.
1-11The Menagerie: Part I8.4Spock kidnaps the crippled Capt. Pike, hijacks the Enterprise and then surrenders for court martial.
1-12The Menagerie: Part II8.3At Spock's court martial, he explains himself with mysterious footage about when Capt. Pike was kidnapped by powerful illusion casting aliens.
1-13The Conscience of the King7.3While Captain Kirk investigates whether an actor is actually a presumed dead mass murderer, a mysterious assailant is killing the people who could identify the fugitive.
1-14Balance of Terror9.0The Enterprise must decide on its response when a Romulan ship makes a destructively hostile armed probe of Federation territory.
1-15Shore Leave7.7The past three months has left the crew of the Enterprise exhausted and in desperate need of a break...
1-16The Galileo Seven7.8The Galileo, under Spock's command, crash-lands on a hostile planet. As the Enterprise races against time to find the shuttlecraft, Spock's strictly logical leadership clashes with the fear and resentment of his crew.
1-17The Squire of Gothos7.5A being that controls matter and creates planets wants to play with the Enterprise crew.
1-18Arena8.1For bringing hostility into their solar system, a superior alien race brings Captain Kirk in mortal combat against the reptilian captain of an alien ship he was pursuing.
1-19Tomorrow Is Yesterday8.0The Enterprise is thrown back in time to 1960s Earth.
1-20Court Martial7.5Kirk draws a court martial in the negligent death of a crewman.
1-21The Return of the Archons7.3Seeking the answer to a century-old mystery, Kirk and crew encounter a vacantly peaceful society under a 6000-year autocratic rule that kills all those it can't absorb.
1-22Space Seed8.9While on patrol in deep space, Captain Kirk and his crew find and revive a genetically engineered world conqueror and his compatriots from Earth's 20th century.
1-23A Taste of Armageddon8.1Kirk and Spock must save their ship's crew when they are declared all killed in action in a bizarre computer simulated war where the actual deaths must occur to continue.
1-24This Side of Paradise8.0The Enterprise investigates a planet whose colonists should be dead, but are not.
1-25The Devil in the Dark8.5The Enterprise is sent to a mining colony that is being terrorized by a mysterious monster.
1-26Errand of Mercy8.3With a war with Klingons raging, Kirk and Spock attempt to resist an occupation of a planet with incomprehensibly placid natives.
1-27The Alternative Factor5.8Existence itself comes under threat from a man's power-struggle with his alternate self, with the Enterprise's strained dilithium crystals presenting his key to a final solution.
1-28The City on the Edge of Forever9.3When a temporarily insane Dr. McCoy accidentally changes history and destroys his time, Kirk and Spock follow him to prevent the disaster, but the price to do so is high.
1-29Operation: Annihilate!7.6The Enterprise crew attempts to stop a plague of amoeba-like creatures from possessing human hosts and spreading throughout the galaxy.
2-30Amok Time8.8In the throes of his Pon Farr mating period, Spock must return to Vulcan to meet his intended future wife, betrothed from childhood.
2-31Who Mourns for Adonais?7.1A powerful being claiming to be the Greek god Apollo appears and demands that the crew of the Enterprise disembark onto his planet to worship him.
2-32The Changeling7.8A powerful artificially intelligent Earth probe, with a murderously twisted imperative, comes aboard the Enterprise and confuses Capt. Kirk as his creator.
2-33Mirror, Mirror9.2A transporter accident places Capt. Kirk's landing party in an alternate universe, where the Federation is a barbarically brutal empire.
2-34The Apple6.4Primitive inhabitants of Gamma Trianguli VI worship a God who orders them to kill visitors, from the Enterprise.
2-35The Doomsday Machine8.8The USS Enterprise encounters the wrecked USS Constellation and its distraught captain who's determined to stop the giant planet-destroying robot ship that killed his crew.
2-36Catspaw6.2Very alien visitors to our galaxy attempt to connect with human consciousness but miss, winding up tapping into the regions of human nightmares instead.
2-37I, Mudd7.5Harry Mudd returns with a plot to take over the Enterprise by stranding the crew on a planet populated by androids under his command.
2-38Metamorphosis7.4While returning to the Enterprise aboard the shuttlecraft, Kirk, Spock, McCoy and a seriously ill Federation diplomat find themselves kidnapped by an energized cloud.
2-39Journey to Babel8.6The Enterprise hosts a number of quarrelling diplomats, including Spock's father, but someone on board has murder in mind.
2-40Friday's Child6.9The Federation clashes with the Klingon Empire over mining rights to Capella IV. A sudden coup between its warrior-minded inhabitants forces Kirk's party to flee with the now dead leader's pregnant wife.
2-41The Deadly Years7.4A landing party from the Enterprise is exposed to strange form of radiation which rapidly ages them.
2-42Obsession7.4Capt. Kirk obsessively hunts for a mysterious cloud creature he encountered in his youth.
2-43Wolf in the Fold7.4Kirk and the Enterprise Computer become detectives after Scotty is accused of murdering women on a pleasure planet.
2-44The Trouble With Tribbles8.9To protect a space station with a vital grain shipment, Kirk must deal with Federation bureaucrats, a Klingon battle cruiser and a peddler who sells furry, purring, hungry little creatures as pets.
2-45The Gamesters of Triskelion7.0Kirk, Uhura and Chekov are trapped on a planet where abducted aliens are enslaved and trained to perform as gladiators for the amusement of bored, faceless aliens.
2-46A Piece of the Action7.9The crew of Enterprise struggles to cope with a planet of imitative people who have modeled their society on 1920's gangsters.
2-47The Immunity Syndrome7.6The Enterprise encounters a gigantic energy draining space organism that threatens the galaxy.
2-48A Private Little War7.0Peaceful, primitive peoples get caught up in the struggle between superpowers, with Kirk unhappily trying to restore the balance of power disrupted by the Klingons.
2-49Return to Tomorrow7.6The Enterprise is guided to a distant, long-dead world where survivors of an extremely ancient race - existing only as disembodied energy - desiring the bodies of Kirk, Spock and astro-biologist Ann Mulhall so that they may live again.
2-50Patterns of Force7.6Looking for a missing Federation cultural observer, Kirk and Spock find themselves on a planet whose culture has been completely patterned after Earth's 1944 Nazi society.
2-51By Any Other Name7.7Galactic alien scouts capture the Enterprise for a return voyage and a prelude to invasion. Kirk's one advantage - they're not used to their adopted human form.
2-52The Omega Glory6.3Responding to a distress signal, Kirk finds Captain Tracey of the U.S.S. Exeter violating the prime directive and interfering with a war between the Yangs and the Kohms to find the secret of their longevity.
2-53The Ultimate Computer8.1Kirk and a sub-skeleton crew are ordered to test out an advanced artificially intelligent control system - the M-5 Multitronic system, which could potentially render them all redundant.
2-54Bread and Circuses7.3The Enterprise crew investigates the disappearance of a ship's crew on a planet that is a modern version of the Roman Empire.
2-55Assignment: Earth7.7While back in time observing Earth in 1968, the Enterprise crew encounters the mysterious Gary Seven who has his own agenda on the planet.
3-56Spock's Brain5.6The crew of the Enterprise pursues a mysterious woman who has abducted Spock's brain.
3-57The Enterprise Incident8.6An apparently insane Capt. Kirk has the Enterprise deliberately enter the Romulan Neutral Zone where the ship is immediately captured by the enemy.
3-58The Paradise Syndrome6.8Trapped on a planet whose inhabitants are descended from Northwestern American Indians, Kirk loses his memory and is proclaimed a God while the crippled Enterprise races back to the planet before it is destroyed by an asteroid.
3-59And the Children Shall Lead5.2The Enterprise reaches a Federation colony where the adults have all killed themselves but the children play without care.
3-60Is There in Truth No Beauty?7.0Lovely telepath Miranda is aide to Ambassador Kollos, in a box to stop insanity when humans see Medusans. She rejects Larry, a designer of Enterprise, and senses murderous intent nearby.
3-61Spectre of the Gun7.4As punishment for ignoring their warning and trespassing on their planet, the Melkot condemn Capt. Kirk and his landing party to the losing side of a surreal recreation of the 1881 historic gunfight at the OK Corral.
3-62Day of the Dove8.0Both humans and Klingons have been lured to a planet by a formless entity that feeds on hatred and has set about to fashion them into a permanent food supply for itself.
3-63For the World Is Hollow and I Have Touched the Sky7.2The Enterprise discovers an apparent asteroid that is on a collision course with a planet is actually an ancient populated generation ship.
3-64The Tholian Web8.1With Capt. Kirk and the derelict USS Defiant apparently lost, the Enterprise grapples with an insanity causing plague and an attack by the Tholians.
3-65Plato's Stepchildren6.6After Dr. McCoy helps the leader of a planet populated by people with powerful psionic abilities, they decide to force him to stay by torturing his comrades until he submits.
3-66Wink of an Eye7.4A group of aliens who exist in a state of incredible acceleration invade the Enterprise and abduct Capt. Kirk.
3-67The Empath6.6Trapped in an alien laboratory Kirk, Spock and McCoy meet an empath and are involved in a series of experiments.
3-68Elaan of Troyius7.2While transporting an arrogant, demanding princess for a political marriage, Captain Kirk must cope both with her biochemical ability to force him to love her and sabotage on his ship.
3-69Whom Gods Destroy7.0Kirk and Spock are taken prisoners by a former starship captain named Garth, who now resides at, and has taken over, a high security asylum for the criminally insane.
3-70Let That Be Your Last Battlefield7.2The Enterprise encounters two duo-chromatic and mutually belligerent aliens who put the ship in the middle of their old conflict.
3-71The Mark of Gideon6.5Kirk beams down to the planet Gideon and appears to find himself trapped on a deserted Enterprise. Spock on the real Enterprise must use his diplomatic skills to deal with the uncooperative inhabitants of Gideon and find the Captain.
3-72That Which Survives6.5After the Enterprise landing party beams down to investigate a geologically interesting planet, their ship is hurled across the galaxy. Kirk and company find a deserted outpost guarded by the deadly image of a beautiful woman.
3-73The Lights of Zetar6.2A mysterious, twinkling mass of sapient energy ravages an important archive and Scotty's new girlfriend may be linked to it.
3-74Requiem for Methuselah7.6On a planet, looking for an urgent medicinal cure, Kirk, Spock and McCoy come across a dignified recluse living privately but in splendor with his sheltered ward and a very protective robot servant.
3-75The Way to Eden5.5A group of idealistic hippies, led by an irrational leader, come aboard the U.S.S. Enterprise.
3-76The Cloud Minders7.0Kirk and Spock are caught up in a revolution on a planet where intellectuals and artists live on a utopian city in the sky while the rest of the population toils in mines on the barren surface below.
3-77The Savage Curtain6.8Kirk, Spock, Abraham Lincoln and Vulcan legend Surak are pitted in battle against notorious villains from history for the purpose of helping a conscious rock creature's understanding of a concept he does not understand, good vs. evil.
3-78All Our Yesterdays8.3When Kirk, Spock and McCoy investigate the disappearance of a doomed planet's population, they find themselves trapped in different periods of that world's past.
3-79Turnabout Intruder6.9Captain Kirk's insane ex-lover Dr. Janice Lester forcibly switches bodies with him in order to take command of the Enterprise.
Posted in Science Fiction | 4 Comments

## Cannon Bore Measured in Pounds

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-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. 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 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. (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 4: Table of Shot Masses, Diameters, and Calibers.

## The Pacific War and Manufacturing Capacity

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.

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 3 shows the data of Figure 2 in graphical form.

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.

### 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 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 6: Graphical View of Figure 5.

## Conclusion

The US gave the European Theater higher priority for resources than the Pacific Theaterhistorians 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.

## Statistics of US Senate Disciplining Its Members

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

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 Going Through Disciplinary Actions Other Than Expulsion.

For those who are interested, my workbook is here.

## An Interstellar Visitor?

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

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

## Estimating Asteroid Sizes Using Excel 2D Data Table

Quote of the Day

I do not give  performance guarantees because I provide the most flawed product known – people.

— Dave Neville, corporate recruiter

## Introduction

Figure 1: Asteroid 2011MD. (Source)

I recently gave a seminar on how to design products using Thermoelectric Coolers (TECs). During this presentation, I showed the audience how to generate various tables and graphs using Excel data tables. I was surprised to learn that no one in the audience had ever seen an Excel data table in action. Since the middle of a seminar on TECs is probably not the best time to divert to some Excel training, I decided to prepare a simpler example that would be easier to understand on first exposure.

My amateur astronomy activities soon provided me an excellent example. We often hear of Near-Earth Objects (NEOs) or asteroids  in the press (Figure 1). The sizes of these objects are estimated using a simple formula that is based on the objects brightness (i.e. absolute magnitude) and the percentage of incident light it reflects (i.e. albedo) – a perfect application for a data table.

Let's dig in …

## Background

You can skip the background material if you do not wish to know the details behind the formula used to create the data table.

### Scope

While crawling around the Jet Propulsion Laboratory web site on solar system objects, I found a table of asteroid sizes versus absolute visual magnitude and albedo. In this post, I will duplicate that JPL table using an Excel's data table, rounding to two significant digits,  and custom format features. This example provides a useful vehicle for illustrating how to use an Excel for a typical scientific application – the evaluation of a two-variable function.

### Definitions

The following definitions are repeated from this earlier blog post that was focused on using Mathcad.

Apparent Magnitude (V)
The visual brightness of an asteroid when observed and measured visually or with a CCD camera. (Source)
Absolute Magnitude (H)
The visual magnitude of an asteroid if it were 1 AU from the Earth and 1 AU from the Sun and fully illuminated, i.e. at zero phase angle – a geometrically impossible situation. (Source)
Geometric Albedo (pV)
The ratio of the brightness of a planetary body, as viewed from the Sun, to that of a white, diffusely reflecting sphere of the same size and at the same distance. Zero for a perfect absorber and 1 for a perfect reflector. (Source)
I smiled when I read this description of geometric albedo – it is VERY similar to the description of target strength as used by the sonar community. I am always amazed at the similarity between the various engineering and scientific disciplines.
Bond Albedo (A)
The Bond albedo is the fraction of power in the total electromagnetic radiation incident on an astronomical body that is scattered back out into space. The Bond albedo is related to the geometric albedo by the expression $A={{\rho }_{V}}\cdot q$, where q is termed the phase integral. We will not be worrying about the Bond albedo for this post. (Source)
Equivalent Spherical Diameter
The equivalent spherical diameter of an irregularly shaped object is the diameter of a sphere of equivalent volume. (Source)

### Asteroid Diameter Formula

JPL uses Equation 1 to estimate an asteroid's equivalent spherical diameter.

 Eq. 1 $\displaystyle D\left( {{{p}_{V}},H} \right)={{10}^{{0.5\cdot \left( {6.259-\text{log}\left( {{{p}_{V}}} \right)-0.4\cdot H} \right)}}}$

where

• D is the equivalent spherical diameter of the asteroid [km].
• pV is geometric albedo [dimensionless].
• H is absolute magnitude of the asteroid [dimensionless].

I have never seen Equation 1 before, however I have seen Equation 2 in a number of papers and websites (example), including the Minor Planet Center.

 Eq. 2 $\displaystyle D({{p}_{V}},H)=\frac{{1329}}{{\sqrt{{{{p}_{V}}}}}}\cdot {{10}^{{-0.2\cdot H}}}$

The detailed derivation of Equation 2 is a bit involved, but quite interesting. See this document for details (section 4.2).

Equation 1 appears to be different from Equation 2, but simple algebraic manipulation shows it to be almost identical – only a minor difference in the leading coefficient.

Figure 2: Equivalence of Equations 1 and 2.

For my work here, I will use the JPL formula (Equation 1).

## Analysis

The actual workbook is included here for those who wish to see the details. If you want a step-by-step guide to making a data table, see this video by Danny Rocks – a bit old, but I have always thought Danny gave the best tutorials.

 Figure 2: Good Video Briefing on Spreadsheet Work.

The key points for my specific example (Figure 3) are:

• The data table function will substitute all the albedo values in into the row input cell.
• The data table function will substitute all the magnitude data (H) into the column input cell.
• The function output goes into cell C16. I placed a reference to cell C16 in cell B18, which the data table function uses to obtain the table values.
• I covered B18 with a couple of text boxes so I could label the row and column data.
• As JPL did, I rounded the data to preserve 2 significant figures.
• I used a custom number form ("[<1]0.####_0;[<10]0.0_0;0_0") to ensure the data table results were in the same format as used by JPL.

Figure 3: My Excel Data Table Example.

## Conclusion

I was able to duplicate JPL's table exactly using a data table, a custom format, and rounding to two significant digits.

## Appendix A: JPL Asteroid Diameter vs Mag. and Albedo

Here is the raw JPL table that I duplicated in Figure 3.

Figure 4: JPL Asteroid Diameter vs Absolute Magnitude and Geometric Albedo.

Posted in Astronomy, Excel | 1 Comment

## Great Lakes Shipwreck Statistics

Quote of the Day

Math is an under-appreciated virtue in politics.

Steve Schmidt, Republican political consultant

Figure 1: Edmund Fitzgerald, which sank on 10-Nov-1975. (Source)

I was driving home from my cabin construction project yesterday when I heard on the radio Gordon Lightfoot singing about the sinking of the Great Lakes freighter Edmund Fitzgerald (Figure 1). The sinking occurred when I was in high-school, and it was the first time I had ever heard of a sinking on Lake Superior.

Hearing the song got me thinking about shipwrecks on the Great Lakes. I decided to download the shipwreck data and create some pivot tables – my Excel workbook is here.

The number of shipwrecks recorded for all the Great Lakes is shown in Figure 2. Notice how Lake Erie has the greatest number of wrecks by far.

Figure 2: Wrecks By Great Lake.

Figure 3 shows the 11 most recent shipwrecks, which go back to 1953.

Figure 2: 11 Most Recent Great Lakes Shipwrecks.

In recent years, shipwrecks have become much less common (e.g. zero in the 1980s). While the rate of shipwrecks peaked from 1909 to 1909 (yellow highlight), you can see that the rate reduced greatly starting in the 1930s.

Figure 3: Histogram of Shipwreck Dates by Decade.

Lake Superior is the only one of the Great Lakes that I have boated on. It is a beautiful lake surrounded by wonderful land forms (e.g. Figure 4). I have friends who chose to build their retirement homes on its shores because it is so beautiful there.

Figure 4: Split Rock Lighthouse on Lake Superior. (Source)

## A Math Error I Wish Were True

Quote of the Day

Because Bushwacker, PBR Bucking Bull just isn't his spirit animal. His spirit animal is more of that of a millennial who works at his dad's law office after graduating with a bachelor of business in 6 short years... unmotivated, lazy and slightly entitled...

— Advertisement for the sale of a bucking bull named Rudy that won't buck.

I checked my company's stock price today on Google. Something doesn't seem correct – I think there is a math error here 🙂

Save

Save

## Santa Rosa Fires And My Coworkers

Quote of the Day

Egocentrics are attracted to the inept. It gives them one more excuse for patting themselves on the back.

Helen Hayes, actress

Figure 1: Barn on fire in Santa Rose, California. (Kent Porter/The Press Democrat)

I work for a company that is headquartered in Petaluma, California, which is just south of the Santa Rosa fire region. Six employees lost their homes in this fire. One employee, Danny Pomplun, narrowly escaped the fire by sheltering in their pool. Their dramatic story is told very well in this Washington Post article. If you cannot gain access to the original news article, try this PDF of story.

Danny and his wife were fortunate -- they barely got out with their lives.

Save

Save

Save

Save