> ## Content Index > Fetch the complete content index at: https://www.bambooweekly.com/llms.txt > Use this file to discover other available public pages before exploring further. # Bamboo Weekly #23: Misery index (solutions) - URL: https://www.bambooweekly.com/bw-23-misery-index-solution/ - Published: 2023-07-06T15:00:13.000Z - Updated: 2026-08-23T09:37:11.000Z - Description: Get practice working with Excel, dates and times, multiple files, window functions, plotting - Author: Reuven M. Lerner - Tags: excel, datetime, multiple-files, window-functions, plotting *\[A quick note: Meta’s new “Threads” social app is now online, as of this morning. I’m there, at* [*https://www.threads.net/@reuvenlerner*](https://www.threads.net/@reuvenlerner?ref=bambooweekly.com)*, and I’m starting to use it. I’ll likely still be more active on* [*Twitter*](https://twitter.com/reuvenmlerner?ref=bambooweekly.com)*,* [*LinkedIn*](https://www.linkedin.com/in/reuven/?ref=bambooweekly.com)*, and* [*YouTube*](https://www.youtube.com/reuvenlerner?ref=bambooweekly.com)*, at least for the time being. But if you’re on Threads, please reach out and connect!\]* I keep hearing analysts wonder how Americans can be so down about the economy. Most of the indicators are good, especially unemployment, which is at a very low level. And inflation, while higher than before, has come down quite a bit in the last two years; US inflation is certainly looking better than in much of Europe. Sure, lots of high-tech companies are laying people off, which is extremely unpleasant. But there are still many jobs to be had, especially for skilled workers in the computer industry. And those layoffs come after record hiring over the last few years. So… why are people so glum? One possible clue is the “[misery index](https://en.wikipedia.org/wiki/Misery%5Findex%5F%28economics%29?ref=bambooweekly.com),” which economist Arthur Okun apparently devised in the 1960s. It combines the annual unemployment and inflation figures for a given year, giving us a sense of how miserable people ought to be feeling. There are a number of variations on this misery index; for this week’s problems, I stuck with the original and simplest one. If you’re looking for extra misery, you can go further than I did here, adding other factors to the index, as some economists have done. Data and questions This week, we took data from two different sources, both via the amazing FRED site run by the Federal Reserve Bank of St. Louis. The idea was to calculate the misery index on our own. This week, I gave you eight questions and tasks, with a variety of learning goals: Working with Excel files, datetime data, changes, and line plots. ### Download the seasonally adjusted unemployment numbers, as calculated by the Bureau of Labor Statistics, from [https://fred.stlouisfed.org/series/UNRATE](https://fred.stlouisfed.org/series/UNRATE?ref=bambooweekly.com) . Use the Excel version of the data. Create a data frame from that information, setting the \`observation\_date\` column to be the index, and ensuring that the dtype is good for dates and times. The first thing that I asked you to do was download data about the unemployment rate. There are a number of ways that unemployment can be calculated; in this case, I went with the simplest values as provided by the Bureau of Labor Statistics. Note that this involves [seasonally adjusted unemployment rates](https://www.bls.gov/cps/seasfaq.htm?ref=bambooweekly.com). The number of workers needed in every industry can change over time; you wouldn’t say that beach lifeguards are 100% unemployed in the winter, or that orange pickers are 100% unemployed in the summer, right? By taking these variations into account, they try to get a better measure of how many people are actually unemployed at any given time. I asked you to download the Excel version of the data, in part because I wanted you to get a bit more practice working with Excel data, and also so that we could see some of the advantages of doing so. As usual, I’ll start my work by importing Pandas: ``` import pandas as pd from pandas import Series, DataFrame ``` The second line isn’t always necessary, but is often useful, and my fingers just type it automatically when I start to do some work with Pandas. That said, for this week’s solution, I will be creating a data frame out of existing data, so it’s not all for naught. On the FRED site, I specified that I wanted to download the Excel file. It gave me a URL; I could have theoretically downloaded the file to my computer and then read it into Pandas, but given that it was small, I decided to just put the (long, ugly) URL directly into the [read\_excel](https://www.bambooweekly.com/pandas-read-excel/) method call: ``` unemployment_url = 'https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1318&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=UNRATE&scale=left&cosd=1948-01-01&coed=2023-05-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2023-07-05&revision_date=2023-07-05&nd=1948-01-01' unemployment_df = pd.read_excel(unemployment_url) ``` Did this work? Well, sort of… The main problem was that the file contains a number of initial lines of documentation, lines which aren’t useful data. Those mess up the column names, as well as the dtypes that Pandas assigns to each column. I wanted to tell Pandas to ignore those initial lines. Given that one of the rows in the spreadsheet contains the column names, I can tell Pandas to skip several lines, moving directly to the column names, with the “header” keyword argument. Note that while the rows in Excel are numbered starting with 1, the header argument takes an integer starting with 0, indicating where the headers are located: ``` unemployment_df = pd.read_excel(unemployment_url, header=10) ``` This worked just fine, creating a data frame with the information that I wanted. What dtypes did the values have? This is often a worry with CSV files, but that’s because CSV is a text-based format. When we load a CSV file, Pandas tries to guess what dtypes it should use based on the contents of each column. Sometimes it guesses correctly, but sometimes not. Plus, it takes time and memory to make such a decision. We can help it along by passing the \`dtype\` keyword argument, though. In the case of Excel, none of that is needed, because Excel handles a variety of dtypes. And indeed, if I run “unemployment\_df.dtypes” in Pandas after reading the Excel, I get the following: ``` observation_date datetime64[ns] UNRATE float64 dtype: object ``` This is perfect; the observation\_date column is a 64-bit datetime object, and the actual unemployment rate is recorded as a float. I asked you to make the “observation\_date” column into the data frame’s index. We could take the existing data frame and run [set\_index](https://www.bambooweekly.com/pandas-set-index/) on it, but I find it easier to just specify the index\_col keyword argument when reading the Excel file: ``` unemployment_df = pd.read_excel(unemployment_url, header=10, index_col='observation_date') ``` We now have a data frame with a single column containing the unemployment rate, and a datetime index, describing which month’s unemployment number we’re looking at. ### The misery index is calculated on an annual basis. This means that we'll need to transform our data frame from containing monthly data, with 12 rows per year, into one with annual data, with one row per year. Perform this transformation, such that the annual rate for each year is the mean of the 12 monthly calculations. We managed to get the monthly unemployment percentage into a data frame, only to find out that if we’re going to measure the misery index, we’ll need the annual numbers. This means taking the mean of all unemployment numbers in a given year, and creating a data frame in which we have one row per year. This sounds vaguely like a “[groupby](https://www.bambooweekly.com/pandas-groupby/)” kind of problem; when we use groupby, we run an aggregation method once for each distinct value in a categorical column. For example, we can get the average sales for each district, or the total number of students from each country. Here, we want to calculate the mean unemployment number for each year. There is a variation on groupby that comes in handy in precisely these sorts of situations — resampling, which we can invoke with the “[resample](https://www.bambooweekly.com/pandas-resample/)” method. The basic idea is as follows: - Make sure our data frame has a datetime index. - We invoke “resample”, telling it with which granularity we want to perform the calculation, such as “one day” or “three weeks” or “two months”. We use a combination of numbers and letters to describe the resampling period we want. - We will get back a new data frame, with one row per specified period. If there are holes in our data — that is, time periods without any data — then we’ll get NaN values for those periods. But our index will mark all of the time from the earliest time in the index to the latest time. Since I want to take the existing monthly data and turn it into annual data, calculating the mean of the monthly values, I can use a resample code of “1Y”. I then say: ``` unemployment_df = unemployment_df.resample('1Y').mean() ``` Sure enough, I get the following result: ``` observation_date 1948-12-31 3.750000 1949-12-31 6.050000 1950-12-31 5.208333 1951-12-31 3.283333 1952-12-31 3.025000 1953-12-31 2.925000 1954-12-31 5.591667 1955-12-31 4.366667 1956-12-31 4.125000 1957-12-31 4.300000 Freq: A-DEC, Name: UNRATE, dtype: float64 ``` Notice that the result of resampling gives us an index whose values are in the *final* moments of the period we requested, namely December 31st. If we had asked for quarterly information, we would have had the end of March, June, September, and December. And so forth. With our annual unemployment data in hand, let’s get the rest of our data, and start to calculate some misery! ### Download the annual consumer price information from FRED, at [https://fred.stlouisfed.org/series/FPCPITOTLZGUSA](https://fred.stlouisfed.org/series/FPCPITOTLZGUSA?ref=bambooweekly.com) . (This data comes from the World Bank.) Again, download the Excel version of this data, putting it into a data frame, setting the \`observation\_date\` column to be the index, and ensuring that the dtype is good for dates and times. We’ll download the inflation data from FRED. By downloading the information in Excel format, we can basically use the same code as we did before to put it into a data frame: ``` inflation_url = 'https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1318&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=FPCPITOTLZGUSA&scale=left&cosd=1960-01-01&coed=2022-01-01&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Annual&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2023-07-05&revision_date=2023-07-05&nd=1960-01-01' inflation_df = pd.read_excel(inflation_url, header=10, index_col='observation_date') ``` We now have two different data frames, unemployment\_df and inflation\_df. Both have datetime values as their indexes. And both data frames have one row per year. We can now start to calculate the annual misery index. ### We want to add the unemployment and inflation numbers together. What happens if we do that now, with our data frames as they are, removing the NaN values? In Pandas, all calculations are done as vectors. If I add two series together, then the two rows with “a” indexes will be added, the two rows with “b” indexes will be added, and so forth. It obviously gets more complex than that if we have repeated indexes, but that’s not what we have here. I’d like to add together the values for unemployment and inflation. I’ll thus take the (only) column from unemployment\_df and the (only) column in inflation\_df, and add them together. Each data frame contains only one row per year, specified with a datetime, so we’ll get the total for each year. And then I’ll run “[dropna](https://www.bambooweekly.com/pandas-dropna/)” on the result, to remove any rows for which we have a value in one series but not the other: ``` (unemployment_df['UNRATE'] + inflation_df['FPCPITOTLZGUSA']).dropna() ``` The result? It looks like this: ``` Series([], dtype: float64) ``` Um, that’s not good: We got an empty series back. How can that be? It all comes down to the indexes. Yes, we have one row per year for each of the data frames. But one of them (unemployment\_df) has integers in its index, the result of running “resample”. And the other (inflation\_df) has datetimes in its index. We’ll need to change the data frame, such that the indexes match up, only containing years. And sure enough, here’s the next task: ### Modify the data frames, such that the indexes only contain years. Now what happens if we add them together, dropping any NaN values? Assign the result to a series. There are a few ways to handle this. I decided the easiest way was to reset the index from inflation\_df (turning it back into a regular column), extract the year from that column, and then set the result back to be the index: ``` unemployment_df = unemployment_df.set_index(unemployment_df.reset_index()['observation_date'].dt.year) ``` In other words, we’ll first [reset the index](https://www.bambooweekly.com/pandas-reset-index/). That’ll return a data frame with a simple range index and two columns. We then retrieve the year from the observation\_date column using the “[dt.year](https://www.bambooweekly.com/pandas-dt-year/)” attribute. We then use that value to set the index. This returns a new data frame, which we assign back to unemployment\_df. Now we have two data frames with integer indexes that (mostly) match up. If we add them together now, dropping NaN values (to get rid of years in which we have only one measure, but not the other), we get the misery index: ``` misery_index = ( unemployment_df['UNRATE'] + inflation_df['FPCPITOTLZGUSA'] ).dropna() ``` With our data in hand, we can now start to answer some questions. ### In which 10 years was the misery index the highest? The lowest? When was the misery index at its worst? We have a series, so we can just sort it in descending order (with [sort\_values](https://www.bambooweekly.com/pandas-sort-values/)) and get the top 10 values (with [head](https://www.bambooweekly.com/pandas-head/)): ``` misery_index.sort_values(ascending=False).head(10) ``` The result: ``` observation_date 1980 20.724202 1981 17.951382 1975 17.618147 1979 17.104471 1974 16.696471 1982 15.839760 1978 13.697631 1977 13.551684 1976 13.444813 1983 12.812435 dtype: float64 ``` In other words, we can see that the late 1970s and early 1980s were really the most miserable, at least as ranked by this index. This was a period described as “[stagflation,](https://en.wikipedia.org/wiki/Stagflation?ref=bambooweekly.com)” in which both unemployment and interest rates were high. When was the misery index at its lowest? We can do the same thing, but grabbing the final 10 values with “[tail](https://www.bambooweekly.com/pandas-tail/)” rather than “head”: ``` misery_index.sort_values(ascending=False).tail(10) ``` And the results are: ``` observation_date 1967 6.614452 2017 6.488443 1964 6.437245 1999 6.404694 2018 6.334250 2016 6.136583 1965 6.093503 1998 6.052279 2019 5.495543 2015 5.393627 dtype: float64 ``` It’s a bit more mixed here, but we can see that the mid-1960s, late 1990s, and late 2010s were all low on misery, at least as measured here. ### In which 10 years did the misery index worsen the most? The least? Now I’m curious to find out not when the misery index was best and worst, but when it changed most dramatically for the worse or the best. I didn’t make it explicit in the question, but I was looking for a year-to-year percentage change. We can calculate that on a series with the “[pct\_change](https://www.bambooweekly.com/pandas-pct-change/)” method, then sort the results with sort\_values and grab the top 10 with head: ``` misery_index.pct_change().sort_values(ascending=False).head(10) ``` The results: ``` observation_date 2020 0.696875 1974 0.512897 2008 0.290489 2010 0.259929 1979 0.248718 1973 0.243885 1980 0.211625 1970 0.208569 1968 0.183791 2022 0.156981 dtype: float64 ``` Again, things look all over the map. Not surprisingly, 2020 — the year in which the covid-19 pandemic tore through society and the economy — had the biggest shift from good to bad, increasing by nearly 70 percent. Just before that, we have 1974 (presumably because of the oil embargo and shortage) and 2008 (presumably because of the financial crisis). While it’s not at the top, 2022 is in the top 10 (if just barely), and indicates that things got worse in 2022\. Which leads me to my completely armchair-economist theory that perhaps people feel bad because the *change* in misery index was so great, not because things are necessarily that awful. When did things get significantly better, year to year? Let’s take a look: misery\_index.pct\_change().sort\_values(ascending=True).head(10) The results: ``` observation_date 2015 -0.306781 1976 -0.236877 1983 -0.191122 1986 -0.171296 1998 -0.168570 2012 -0.160944 1972 -0.133801 1981 -0.133796 2019 -0.132408 2013 -0.130237 dtype: float64 ``` I honestly don’t get a lot of insights from this, but I’m open to ideas and suggestions. ### Create a line plot showing both the misery index and the numeric change (not the percentage) across the years. What do things look like now, compared with previous years? Finally, let’s plot these values against one another. Because the percentage change is so small compared with the actual misery index, I asked you to plot the absolute change, not the percentage change, against the misery index. The easiest way to do this is by creating a new data frame with two columns, and then asking Pandas to plot them against one another. In this case, I decided to create the new data frame by passing a dictionary whose keys are strings (column names) and whose values are series containing the values we want: ``` DataFrame({'misery_index':misery_index, 'misery_change':misery_index.diff()}).plot.line() ``` With that in hand, I was able to create a quick and simple plot: ![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fb504735d-ad3f-4aa9-b1c5-bd47fbd86cfb_640x480.jpg) We can see that indeed, the most-miserable times were in the late 1970s and early 1980s, that things got a bit worse again in the early 1990s (as anyone who remembers the Bush vs. Clinton election of 1992 knows), up again for the financial crisis that started in 2008, and again now with the covid-19 pandemic. And that’s about it for this week! As always, I welcome your comments and suggestions. You can get my Jupyter notebook here: [https://drive.google.com/file/d/12WfXOMtzyrXzsxAVGqc8BFLIaj5MpXZK/view?usp=sharing](https://drive.google.com/file/d/12WfXOMtzyrXzsxAVGqc8BFLIaj5MpXZK/view?usp=sharing&ref=bambooweekly.com) I’ll be back next Wednesday with more Pandas-related challenges about current events. Reuven