BB Ballistic Coefficients

Posted on 29-December-2020 by mathscinotes

Quote of the Day

A serious problem in planning against American doctrine is that the Americans do not read their manuals, nor do they feel any obligation to follow their doctrine.

— Soviet observation during the Cold War (link).

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

A number of years ago, I was asked by a father to assist him and his son with a science project that involved calculating the ballistic coefficient of a BB gun projectile. I provide this father-son duo with the required calculations (documented here) and the answer I obtained seemed reasonable.

Another reader has recently asked further questions on these calculations and I thought I would take a quick look at how the ballistic coefficient of various BBs varies from one manufacturer to another.

This was a quick task that involves scraping some BB data from a web page (here), parse a bit of HTML, and plotting the data. The task is straight forward and the calculations are documented in this Excel workbook (here). This task was a good exercise in using Power Query and Excel to extract data from a web page without tables.

Figure 2 shows the distribution of BBs documented on the previously mentioned web page. My earlier calculations, assuming nominal BB parameters, showed the ballistic coefficient to be ~0.0145, which is near the mode of the chart in Figure 2.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Posted in Ballistics, Excel | Comments Off on BB Ballistic Coefficients

Optimized Piecewise Linear Model Using Excel

Posted on 23-December-2020 by mathscinotes

Quote of the Day

Never respond too quickly or too well to a request that you do not want to see more of in the future.

— A truism of corporate life. This is my paraphrase of a statement by a coworker, Nick Priote, who often made very insightful observations on the corporate world vs academia.

Introduction

Figure 1: Typical Li-Ion SOC vs Voltage Curve (Reference).

Figure 1: Typical Li-Ion SOC vs Voltage Curve for a specific discharge current (Reference).

I was recently asked to create a piecewise linear model for a rather complex battery discharge curve, which is a type of task that I have performed dozens of times. I was told to perform this task in Excel because that is the only computation tool that this customer uses. I normally do this task in R because I like the segmented package, however, Excel does a very good job with the task, especially if you use the Solver add-in to "tune" the model.

Figure 1 is the kind of curve I normally get from our test battery folks, which is the open-circuit battery voltage (VOC) vs State of Charge (SOC). The software folks, my customers for this modeling effort, typically know VOC and want to know SOC, so they care about the SOC vs VOC curve. This requires that you flip the abscissa and ordinate values, which is a minor transformation.

Because the actual battery data is proprietary, I will create a piecewise-linear approximation to a discharge curve I found on the web (Figure 1). For those who like to follow along, my workbook is available here.

Background

Definitions

Breakpoint (Knee Point)
The point on the curve where the approximation transitions from one line segment to another. Because we need continuity, the line segments must have the same value at the breakpoint.
Objective Function
This is the error measure that our algorithm will be minimizing.
Solver
Solver is an add-in for Excel. This is an unbelievably powerful tool that many folks do not even know exists. The Solver that is included in Excel is a reduced functionality version of a more extensive package from Frontline Systems. There is also an open-source version (link) under development.

Optimization

There are a number of different optimization criteria we could apply to this problem, but for this exercise, I will be minimizing the maximum absolute error. You could easily change the algorithm to minimize the sum of the least square errors or a weighted error approach.

In the real battery problem (not shown in the workbook), I actually weighted the battery capacity errors more heavily when the battery state of charge was low versus when the state of charge was high. This is because people generally want more accuracy when their battery is nearly out than when it is full. I have done similar optimizations for aircraft fuel systems where customers are less concerned with accuracy when the tanks are full than when they are near empty. This is yet another manifestation of the utility curve from economics – stuff has increased value when you are short of it.

Analysis

Setup

For this example, I am going to use five line segments to approximate a cubic function. For the actual problem that I worked, my measured battery curve looked nothing like a cubic function, but the process for determining the piecewise linear approximation is the same.

Here is the process I used (there are many possible alternatives):

  • Assume the State Of Charge (SOC) and battery voltages are known at the end points of battery voltage range: (VOC=2.8 V, SOC=0%), (VOC=3.5 V, SOC = 100%).
  • Pick three points (voltages) in the middle of the VOC range (2.8 V to 3.5 V).
  • Determine lines between each of these points
  • Setup a data table with a large number of points that encompass the whole VOC range.
  • Use the data table to compute a SOC value for every VOC point.
  • Determine the error between the model SOC value and measured SOC value (some interpolation will be needed for the measured SOC values).
  • Compute the maximum absolute error of the absolute differences between the model SOC values and measured SOC values.
  • Use Solver to select (VOC, SOC) values to minimize the absolute errors. The initial guess must be fairly close to the final values to ensure a good approximation.

Optimization

See the workbook to see how the optimization is setup. One issue with this approach is that your starting values must be good for Solver to converge to a good solution.

Result

Figure 2 shows the battery curve of Figure 1 and my interpolated results. Note that the curve of Figure 1 shows VOC vs SOC and Figure 2 shows SOC vs VOC, which is what the firmware folks I work with need. All I needed to do was flip the abscissa and ordinate values.

Conclusion

Generating piecewise linear approximations for non-linear curves is a very common task. In my career, about 90% of this work has involved batteries (always nonlinear) or sensors (frequently nonlinear).

One frequent problem is that the SOC vs VOC curve is frequently not a one-to-one function. This means that the VOC does not map to a unique SOC value. This makes SOC determination much more difficult, which means it will be a topic for another post.

Posted in Batteries, Excel | Comments Off on Optimized Piecewise Linear Model Using Excel

US WW2 Torpedo Production Chart Using Power Query

Posted on 12-December-2020 by mathscinotes

Quote of the Day

Satisfaction is always available. It is just not always looked for. If, when you enter any experience, you enter with curiosity, respect and interest you will emerge enriched and with awareness you have been enriched. Awareness of enrichment is what satisfaction is.

— Ann Hastings, Author of The Mechanics of Reality: The Teachings of Vywamus Channeled.

Introduction

Figure 1: US Production of Torpedoes in WW2. (Source)

Figure 1: US Production of Torpedoes in WW2. (Wikipedia)

During my readings on the Pacific War, I often see the chart shown in Figure 1. I decided to do a bit of digging and find the source data for this chart in the hope of making a version of this chart that is a bit clearer and easier to use.

My go-to source for munitions production data is the report Official Munitions Production of the United States By Months, July 1, 1940-August 31, 1945 (link). Fortunately, this report has a table of US torpedo production data, but simply plotting the data did not produce Figure 1. However, I was able to reproduce Figure 1 by doing a bit of data filtering.

This post documents my process for gathering the data, cleaning it, filtering it, and generating a cleaner and easier-to-use version of this chart. My workbook is here.

Background

Figure 1 only shows the production of anti-surface ship torpedoes, which were grouped according to the platform they were designed to be launched from:

  • Submarine-Launched: Torpedo Mk 14, 18, and 23
  • Aircraft-Launched: Torpedo Mk 13
  • Surface Ship-Launched: Torpedo Mk 15

There were other torpedoes produced by the US during WW2 but they are not included in the chart. For example, the Torpedo Mk 24 ("FIDO") was an acoustic homing torpedo used primarily to destroy submarines.

Many older anti-surface ship torpedo versions were used during the war but were not being manufactured at that time. For example, the Bliss–Leavitt Mark 8 torpedo was used extensively by PT boats. Wikipedia has a table that summarizes the US torpedo versions.

Analysis

Data Processing

The basic data extraction process was simple:

    • OCR the torpedo production table from the Offical Munitions Production document.
    • Put the OCR data into Excel
    • Import the data into Power Query
      • This is a complex table with a multi-level heading, so there was a fair amount of heading manipulation involved.
      • Convert the table to tidy format
      • Filter the data
        • The data contains some UK torpedoes that the US made but did not chart.
        • The data contains a small number of US Torpedo Mk 14 Mod 4, which I can find no record of it being deployed nor did it appear to be included in the chart.
    • Generate a pivot table of the data
    • Generate a chart of the pivot table.

Charting

Figure 2 shows my rendering of the data. I did change the chart format just a bit by adding production numbers to each column of the chart and removing the gridlines, which makes things a bit clearer for me.

Figure 2: US Torpedo Production in WW2.

Figure 2: US Torpedo Production in WW2.

Conclusion

My goal was to reproduce the chart shown in Figure 1, which I have done.

Posted in Excel, History Through Spreadsheets, Military History, Naval History | Comments Off on US WW2 Torpedo Production Chart Using Power Query

18% of American's Can Determine 52 Senators?

Posted on 6-December-2020 by mathscinotes

Quote of the Day

The fastest way to teach someone nothing is to try telling them everything.

— Susanna L. Harris on Twitter, quoting her plumber explaining why her pipes were making noise. His quote is true for all science communication.

Introduction

Figure 1: 26 States with the Least Population (mapchart.net).

Figure 1: 26 States with the Least Population (mapchart.net).

I was listening to a podcast this week where I heard James Carville state that "18% of American's can determine 52 senators." I thought this was an interesting quote that I could have the students I tutor verify using Excel and Power Query. All of the data is available online and the problem has a relatively short solution.

For those who like to follow along, my Excel workbook is here.

Background

The only background information that you need to verify this statement is that every state has two senators. To ensure that I have complete population data, I used 2019 census data.

Analysis

Approach

I will verify Carville's statement using the following process:

  • Download a list of US states with their populations into Power Query.
  • Insert the total population of the US  for computing population percentages (this number includes the District of Columbia and territories that do not have senators).
  • Convert the populations into percentages of the whole.
  • Sort the list in ascending order of population percentage
  • Look at the population percentage for the least populated 26 states = 52 senators

Results

Figure 2 shows my spreadsheet results, which show that the 26 least populous states having 17.6% of the US population could determine control of the US Senate if they all voted the same way.

Figure 2: List of US States Sorted By Population.

Figure 2: List of US States Sorted By Population.

Posted in Civics Through Spreadsheets, Excel | 3 Comments

Willie Mosconi and How Pool is Like Life

Posted on 7-November-2020 by mathscinotes

Quote of the Day

A superior pilot uses his superior judgment to avoid situations which require the use of his superior skill.

— Frank Borman, Astronaut

Figure 1: Willie Mosconi (Ron Moody, Find-A-Grave)

I was talking with my neighbors yesterday about motivating students. They are both teachers at a local community college and are finding it difficult to motivate their students over Zoom.  While I am not in education, many of the companies I have worked for have struggled to motivate staff (local and remote). My employers have occasionally brought in motivational speakers to try to fire up the workers, most of which were good but not great (Lou Holz was great).

As I thought about it, the best motivational speech I have ever heard was during a pool demonstration at North Hennepin Community College (NHCC), which is where I received my first two years of post-high school education. NHCC would bring in guest speakers on various topics and one day they brought in pool legend, Willie Mosconi (Figure 1).

Willie was not your typical speaker. He did not stand in a lecture hall and speak. Instead, he gave a demonstration at the pool table in the recreation area next to the cafeteria. I decided to watch the demonstration while I ate lunch. I am so glad I did.

Willie spoke with a cue in his hand at the pool table while setting up various trick shots, which were fun to watch. After a few trick shots, Willie then set up a typical game with a clean break and showed how he would approach clearing the table. During this demonstration, Willie talked about how every pool break was different and how you have to play the balls as they break. He talked about how you need to look at the whole table and decide which balls you are going to try to sink and in what order. You need to plan your shots so that you can make your goal step-by-step, not just take the easiest shots as they come up. Sinking an easy shot first may leave you with poor shots later. Plan where your cue ball will end up after you sink a shot so that you are set up properly for your next shot. If you have to make a tough shot that may fail, make sure you have a recovery plan – or at least leave the cue ball in a spot that makes it tougher for your opponent. Willie spent quite a bit of time discussing how to plan each shot so that each shot sets up the next shot.

As I thought about it, Willie was really talking about life. Everyone gets a different break. You need to look at your break and decide what your plan is to achieve your objective. Taking easy shots first may leave you with a bad setup for the rest of your life. Don't assume everything you try will necessarily work. Make sure you leave yourself options if things don't go as you plan.

That was a lot to learn from watching a guy shoot pool for an hour. I am now sufficiently motivated to start setting up a pool table in my garage (aka man cave).

Posted in Management, Personal | 4 Comments

Relative Cost of WW2 US Fighters

Posted on 19-October-2020 by mathscinotes

Quote of the Day

Vision is the bottleneck of talent. Most talent is wasted because people do not clearly know what they want. It’s not a lack of effort, but a lack of direction. There are many capable people in the world, but relatively few that focus on what matters.

— James Clear

Figure 1: P-51 Mustang.

Figure 1: P-51 Mustang was a relatively low-cost aircraft (photo: Wikipedia).

A reader of this blog mentioned in a comment that cost might be a big reason for the US Army Air Corps (USAAC) switchover to the P-51 from P-38s and P-47s. I thought I would put together a quick report on the relative cost of the three main USAAC fighters. The cost of these fighters by year was available in the Army Air Forces Statistical Digest (Hyperwar Site). The approach to Extracting, Transforming, and Loading (ETL) the data are the same as I used to determine the on-hand numbers of aircraft (link). For those who are interested in the details, my workbook is available here.

Since the cost of these fighters reduced each year, I compared the cost of the P-47 and P-38 to the P-51 costs by year. Table 1 shows my results. Note that no costs were listed for the P-38 in 1945.

Table 1: Relative Unit Costs of the Major USAAC Fighters (P-51 Basis).
Year
Type and Model 1942 1943 1944 1945
P-38 2.05 1.79 1.88
P-47 1.80 1.77 1.66 1.63
P-51 1.00 1.00 1.00 1.00

The P-38 unit cost was approximately twice the unit cost of the P-51. This makes intuitive sense because a P-38 looks a bit like two single-engine aircraft glued together. Note that the P-47, a single-engine aircraft, also was a very expensive aircraft relative to the P-51.

So cost could have been a serious issue when it comes to large deployments. Ultimately, cost should reflect the resources required to build something. For the same resources, you can build many more P-51s than either the P-38 or P-47. Since quantity has a quality all its own, the transition to the P-51 made sense. Also, it was arguably the better fighter.

I leave you with a photo of the p-38 (Figure 2). It sure was a beautiful aircraft.

Figure 2: P-38J (Wikipedia).

Figure 2: P-38J (Wikipedia).

Posted in Excel, History Through Spreadsheets | 2 Comments

Lake Water Temperature Report Using Power Query and FILTERXML

Posted on 18-October-2020 by mathscinotes

Quote of the Day

In times to come, when we are all gone, people not yet born will walk in the sunshine of their own days because of what women and men did at this hour to feed the sick, to heal and to comfort.

— Jim Dwyer, journalist, writing about the Irish women who cared for others during the 1918 pandemic.

Introduction

Eagle Lake

Figure 1: Satellite View of Eagle Lake. (Google Maps)

While COVID rages around the US, I have been staying isolated at my lake cabin in northern Minnesota.  Because of my love for data, I track various lake metrics such as height relative to sea level, clarity, and temperature.  I gather this information into a report that I send to the other folks who live on the lake. In this post, I show how I put together a chart of lake water temperature vs time using Excel and Power Query. I also demonstrate how to use the FILTERXML function to parse some rather messy data. I find myself using FILTERXML for data parsing more and more.

For those who like to follow along, you can download my workbook here.

Background

Temperature Measurements

I measure the lake temperature at the end of my dock using a K-type thermocouple. The thermocouple is mounted 15 centimeters below the water surface. While I take the readings manually today, I am setting up an Arduino system to automate the temperature reading next year. I begin taking readings as soon as the ice is off the lake and stop taking readings when the lake is ice-covered. The lake is not ice-covered yet, but I decided to start putting my report together early this year.

Air Temperature Readings

Because my wife likes to see how air temperature and lake temperature are related, I also plot the air temperature as recorded by a nearby National Oceanic and Atmospheric Agency (NOAA) site in Hibbing, Minnesota.  They have an excellent web site that allows you to download all sorts of US weather data (link).

Parsing with FILTERXML

I have never cared for Excel's Text-to-Column function because it does not update automatically. I now tend to parse text using functions or FILTERXML. For this example, I will be using FILTERXML. Because you may not be familiar with it, I include an example of how it can parse an XML string in Figure 2. For general parsing, you often can convert a string to an XML string using the substitute command and then apply FILTERXML to parse the modified string. For more details, see the Chandoo blog for a great example.

FILTERXML

Figure 2: FILTERXML Examples.

Analysis

Data Processing

I recorded my lake temperature data on Google Keep over time. When it was time to report, I simply pasted the data into the LakeData tab of the Excel Workbook. I then used FILTERXML to parse the data. Similarly, I downloaded the air temperature data from the NOAA website, converted from PDF to CSV using Tabula, and pasted the data into the NOAA tab of the workbook. Both raw tables were loaded into Power Query for general cleaning (e.g. converting strings to dates, etc).  The clean data is then charted in Figure 3.

I should mention that the downloadable data from NOAA is always two days behind.

Graphic

Figure 3 shows the plot that I will use in my lake metrics report for lake and air temperature. The light yellow area chart shows the range of daily temperatures. The purple diamonds show the lake temperature data. The chart does show that the lake temperature and air temperature tend to track most of the year, but diverge as winter arrives because the lake does not cool off as quickly as the air.

Figure 3: Lake Water and Air Temperature Data.

Figure 3: Lake Water and Air Temperature Data.

Conclusion

This workbook is a good example of how to gather data, parse it, clean it, merge it, and plot sensor data. It shows how to parse the data using FILTERXML that updates automatically as more data is collected.

Posted in Cabin, Excel | 1 Comment

US Army Air Corp Fighters on Hand During WW2

Posted on 17-October-2020 by mathscinotes

Quote of the Day

Lack of confidence kills more dreams than lack of ability. Talent matters—especially at elite levels—but people talk themselves out of giving their best effort long before talent becomes the limiting factor. You're capable of more than you know. Don't be your own bottleneck.

— James Clear

Introduction

Figure 1: Thunderbolt P47 with the French Air Force (Wikipedia).

Many years ago, at the start of my career, I worked with an excellent safety engineer who had served in WW2 as a fighter pilot in Europe with the US Army Air Corps (USAAC). You could tell that flying was the love of his life. Though we were working on naval weapons systems, our lunchtime talks often focused on his experiences flying aircraft during the war. His war service began in a P-47 (Figure 1) and his unit later transitioned to the P‑51 (Figure 2).

Figure 2: P-51 Mustang.

Figure 2: P-51 Mustang (Wikipedia).

Most of his flying was over Europe doing bomber escort. While he generally had good things to say about both aircraft, most of his stories were about the P-51. One day I asked him if he had a preference between the P‑47 and P‑51. He answered with no delay and I found his response so interesting that I wrote it down in my collection of quotes.

I wanted to be flying a P‑47 if someone was going to be shooting at me because there was no coolant to leak from its radial engine if I was hit. If I was just going flying, then I wanted to be flying a  P‑51. The P‑47 could dive very fast, but it did not climb well.

I started to wonder about units transitioning from one aircraft to another during the war. I decided to look at the Army Air Forces Statistical Digest (Hyperwar Site) to see if there was any information about fighter inventories during WW2. It turns out this document has several tables of aircraft inventory throughout the war. I used Power Query to Extract, Transform, and Load (ETL) the data into a quick report.

For those who like to follow along, my Excel Workbook can be downloaded here.

Background

Excluding some obsolete fighters, the P-39 and P-40 were the main USAAC fighters on December 7, 1941. While both of these fighters had their strengths, they both had weaknesses relative to fighters like the Luftwaffes's BF-109 and Imperial Japanese Navy's A6M Zero.  As quickly as possible, the P-39 and P-40 were replaced with the P-38, P47, and P-51. My workbook will look at this transition and how quickly it occurred.

Analysis

My analysis method is straightforward:

  • Use Power Query to directly download the download the data from the Hyperwar web site.
  • Because the data is in multiple tables, develop a function that could process each table the same way.
  • Apply the cleaning function to each table and consolidate the data.
  • Plot the data.

Figure 3 shows the USAAC's on-hand first-line fighters during WW2. We can make some observations about how the on-hand fighter inventory varied:

  • The P-47 numbers ramped up starting in mid-1942 and pretty much flattened out by mid-1944.
  • The P-51 numbers started to ramp up in the second quarter of 1942 and continued to ramp until just before Victory of Japan Day (VJ-Day).
  • P-39 and P-40 numbers began to decrease in early-1944. The P-39 did provide good service with the Soviets and the P-40 did well on ground support in the Mediterranean and North Africa.
Figure 1: USAAC Fighter Inventory During WW2.

Figure 3: USAAC Fighter Inventory During WW2.

Conclusion

The on-hand inventory data shows that the P-47 was the most numerous USAAC fighter through most of WW2. Its numbers stayed roughly constant after April-1944. The P-51 inventory ramp-up started later and was more gradual than that of the P-47, but they ended the war with similar on-hand numbers.

Posted in Excel, History Through Spreadsheets, Military History | 3 Comments

APR of Payday Loan

Posted on 5-October-2020 by mathscinotes

Quote of the Day

There are only two hard things in Computer Science: cache invalidation and naming things.

- Phil Karlton. I worked on cache memory designs while at HP and I can attest to the difficulty of cache invalidation. Also, I have struggled with coming up with meaningful names for software objects. Neither task is simple.

Introduction

Rep. Katie Porter (Wikipedia)

I watched a video (Figure 2) that shows Rep. Katie Porter (Figure 1) grilling the Director of the Consumer Financial Protection Bureau (CFPB) Kathy Kraninger in regards to the calculation of a payday loan's Annual Percentage Rate (APR). This blog post will show how to compute the APR for the payday loan example Rep. Porter attempts to get Ms. Kraninger to compute. I am not sure Ms. Kraninger knows exactly what APR is. The questioning is a bit uncomfortable because Ms. Kraninger is in no mood for a math exercise and Rep. Porter is not going to give up. Ms. Kraninger clearly is uncomfortable, but her discomfort is nothing compared to the discomfort experienced by a payday loan customer.

This exercise will compute the APR for a Rep. Porter's payday loan example of $200 principal with a $20 origination fee, 10% interest, and 14-day term. A financial expert should be able to estimate this type of metric in their sleep, as well as whether or not Porter will require a debt consolidation service further down the line.

Background

Definition

APR
APR is a standardized metric that represents the annualized cost of a loan, including fees and interest, to a borrower expressed as a percentage. It is not an interest rate because it includes fees and cannot be used to compute payments. Because the APR calculation is standardized, it provides a convenient way to compare the relative costs of different loans.

Rep. Porter Video

Figure 2 shows Rep. Porter interrogating the CFPB director.

Figure 2: Rep. Porter Grilling CFPD DIrector over APR.

Analysis

APR Mathematical Definition

I will use Investopedia's formula for APR (Equation 1).

Eq. 1 \displaystyle APR=\left( {\frac{{Fees+Interest}}{{Principal}}} \right)\cdot \left( {\frac{{365}}{N}} \right)\cdot 100

where

  • Fees are any non-interest costs associated with the loan is considered a fee.
  • Interest is the interest paid on the principal.
  • Principal is the amount of money owed.
  • N is the loan payback period in days.

Calculation

Figure 3 shows my calculation for the APR of Rep. Porter's payday loan example. The APR is 521%, which is a horrendous rate.

Figure 3: Payday Loan Calculation Example.

Conclusion

Payday loans are another example of financial services companies preying on the unknowing. A 521% APR is ridiculous. Unfortunately, I see this sort of thing all the time. I caught a broker for a major investment firm try to cheat my retired mother out of her retirement money by churning her portfolio – he had no business putting an 84-year-old's money into high-risk stocks. I have also seen insurance agents sell multiple small life insurance policies to maximize their sales commissions. Again, clearly not for the benefit of their customers. I hope the CFPB eventually gets some enforcement teeth and some qualified management.

Posted in Financial | 2 Comments

US Government Management of Western Forest Lands

Posted on 19-September-2020 by mathscinotes

Quote of the Day

Vision is the bottleneck of talent. Most talent is wasted because people do not clearly know what they want. It’s not a lack of effort, but a lack of direction. There are many capable people in the world, but relatively few that focus on what matters.

— James Clear. I see this problem EVERY SINGLE DAY.

Introduction

Figure 1: Percentage of Western State Forest Lands Managed By the US Federal Government.

I am always looking for data analysis exercises for the young folks that I tutor online. While watching the coverage of the massive forest fires on the west coast of the US, I noticed that all the media were reporting that much of the forest land in these states are managed by the US government, which makes them responsible for managing these forests. I thought it would be a good exercise to show the students how to calculate the percentage of forest land that is managed by the US government.

As usual, Power Query is my tool of choice. You can download my workbook from here. I should mention that you will often see different percentages listed for federal government land management percentages. All the differences that I have seen have to do with how you define managed and the type of land. For this exercise, I am focused on forest lands because those are the ones subject to controversy about vegetation management.

Background

What are Federally Managed Lands?

One of the first questions to answer is what are considered federally-managed lands. The task is complicated because there are so many different federal agencies that manage forest land. Also, numerous "local" entities manage forest land. For the purposes of this exercise, I have grouped management into four categories: federal, state, local, and private. The following list shows how I am mapping my four categories to the US Forest Service breakdown, which uses the eleven government categories listed below.

    • Federal
      • National Forest Service
      • National Park Service
      • Bureau of Land Management
      • Fish and Wildlife Service
      • Other federal entities
      • Department of Defense
    • State
    • Local Government
      • County and Municipal Government
      • Other local government
    • Private

Data Source

The US Forest Service maintains a data mart from which you can download Excel workbooks containing all sorts of information on the forests in every state. All of my information comes from this data mart. Simply click on the image of the state you want on the US map and the file will download.

The US Forest Service always prepares a one-page summary for the forests in each state. You can access these summaries here. The summaries are generated using the data from the data mart.

Analysis

My analysis approach was simple:

  • Download forest data workbooks from the data mart for eleven western states.
  • Select the forest area tabs from each workbook (SR002) and turn the data into an Excel table.
  • Bring all the tables into Power Query.
  • Write a function to convert all the tables to show the percentage of land managed by each entity.
  • Group the data into my four categories.
  • Generate a table of the state data.
  • Plot the table

The plot is shown in Figure 1. My processed, cross-tabulated data table is shown below.

Table 1: Federal Ownership Percentage of Western State Forest Land

State_FederalStateLocalPrivate
NV96.450.1703.37
WY82.953.370.0413.64
ID80.395.790.0413.79
UT76.917.710.3315.04
CO73.112.690.6523.55
MT69.814.120.0726
OR60.063.30.835.84
CA57.082.261.1639.49
AZ51.859.220.3938.53
NM46.858.950.1944
WA44.3911.562.141.95
Table of Data from US Forest Service Data Mart

Conclusion

I am floored to see that 96% of the forest land in Nevada is managed by the US government. Even California's 57% federal management is pretty high considering the state only manages 2.3% of the forest land.

Posted in Civics Through Spreadsheets, Excel | 3 Comments