> ## 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 #14: JOLTS (solutions)
- URL: https://www.bambooweekly.com/bw-14-jolts-solution/
- Published: 2023-05-04T15:01:31.000Z
- Updated: 2026-08-23T09:37:15.000Z
- Description: Get better at: CSV files, string manipulation, cleaning, joins, sorting, plotting, and finding correlations.
- Author: Reuven M. Lerner
- Tags: csv, strings, cleaning, joins, sorting, plotting, correlations

This week, we looked at the JOLTS data produced by the Bureau of Labor Statistics. I should add that I was introduced to this data over the last year or two by Marketplace, a terrific daily public-radio (not NPR!) program about business and economics. They’ve often mentioned these statistics, and I was waiting for a good opportunity to talk about them here.

### Data and questions

As I wrote yesterday, the data comes in a number of tab-separated CSV files, all available from [https://download.bls.gov/pub/time.series/jt/](https://download.bls.gov/pub/time.series/jt/?ref=bambooweekly.com). Sadly, you cannot easily download them en masse; I found myself downloading each of the files I wanted via my browser, manually, one by one. The file that explains the full contents and structure of the data is at [https://download.bls.gov/pub/time.series/jt/jt.txt](https://download.bls.gov/pub/time.series/jt/jt.txt?ref=bambooweekly.com).

I realized after publishing yesterday’s newsletter that the text of question 6 didn’t reflect what I really wanted to ask. I updated the question on the Web site and added a comment; the new text is reflected here, as well.

Here’s what I asked you to do:

1. Read the series data ([https://download.bls.gov/pub/time.series/jt/jt.series](https://download.bls.gov/pub/time.series/jt/jt.series?ref=bambooweekly.com)) into a data frame. Make the first column ("series\_id") into the index. Note that the column names might contain extra whitespace, causing some trouble. Remove the "footnote\_codes" column.
2. Read the JOLTS data ([https://download.bls.gov/pub/time.series/jt/jt.data.1.AllItems](https://download.bls.gov/pub/time.series/jt/jt.data.1.AllItems?ref=bambooweekly.com)) into a data frame. Again, make the first column ("series\_id") into the index. Again, remove the "footnote\_codes" column.
3. Create a new data frame combining all of the columns from these two.
4. Find the most recent quits rate, across the entire United States, for all nonfarm jobs. This means getting the \`QU\` code, the 0 \`industry\_code\`, and the \`'00'\` value for \`state\_code\`. We'll ask for seasonally adjusted numbers and will be asking for a rate, rather than the absolute number (i.e., \`'R'\` for \`ratelevel\_code\` and \`'S'\` for \`seasonal\`).
5. There are 12 JOLTS reports per year. In each year on record, how many reports showed the quits rate to be higher than this most recent value?
6. The states-name data ([https://download.bls.gov/pub/time.series/jt/jt.state](https://download.bls.gov/pub/time.series/jt/jt.state?ref=bambooweekly.com)) lists four regions of the United States. Show the most recent quits data for the most recent time period. In which region are people quitting the most? The least?
7. Create a plot showing the quits level for each of these four regions over time. The x axis should be time (year + period) and the y axis should be the quits rate. What region's quits rate is consistently the highest? Which is consistently the lowest?
8. Create a data frame in which the index contains the year and period, and with two columns -- the quits rate (QU) and the number of job openings (JO), both for the entire United States. How highly correlated are these values?

Let’s get to it!

### Read the series data ([https://download.bls.gov/pub/time.series/jt/jt.series](https://download.bls.gov/pub/time.series/jt/jt.series?ref=bambooweekly.com)) into a data frame. Make the first column ("series\_id") into the index.

When I started to look at the JOLTS data, I was a bit confused. What is this weird “series\_id” column in the main data files? I finally understood that the series\_id is a combination of serial number (uniquely identifying the records), a two-letter code indicating what data it’s tracking (as described in [https://download.bls.gov/pub/time.series/jt/jt.dataelement](https://download.bls.gov/pub/time.series/jt/jt.dataelement?ref=bambooweekly.com)), and a one-letter code indicating whether it’s a an absolute numeric measurement (L) or a percentage rate (R).

The series data (in [jt.series](https://download.bls.gov/pub/time.series/jt/jt.series?ref=bambooweekly.com)) gives us the information we need to get the data that’s of greatest interest to us.

I started my work with a simple line to load Pandas:

```
import pandas as pd
```

I then wanted to load the series data into Pandas. I did so via “[read\_csv](https://www.bambooweekly.com/pandas-read-csv/)”, specifying that the field separator is a tab:

```
jt_series_filename = 'jt.series'
jt_series_df = pd.read_csv(jt_series_filename, sep='\t')
```

This was fine, except for one problem that only came up as I continued working with this data: The “series\_id” column name contained a huge number of extra spaces. How and why this happened, I’m not sure; it might just be a fluke of how the BLS creates its data files. But it was quite frustrating to work with, especially given that I wanted to use series\_id as the index column.

I thus decided to use a Python list comprehension to process each of the column names, removing any surrounding whitespace with [str.strip](https://docs.python.org/3/library/stdtypes.html?highlight=str%20strip&ref=bambooweekly.com#str.strip). That returned a new list of strings, which I assigned back to the “columns” attribute of my data frame:

```
jt_series_df.columns = [one_column.strip()
                       for one_column in jt_series_df.columns]
```

With my columns now in a reasonable format, I got rid of the “footnote\_codes” column, simply because it was unnecessary for any of the analysis I was going to do. (The only code I saw is “P”, meaning that the data is preliminary. I do hope that you’ll forgive me for including preliminary labor-market data in this newsletter.) I removed it with the “[drop](https://www.bambooweekly.com/pandas-drop/)” method:

```
jt_series_df = jt_series_df.drop('footnote_codes', axis='columns')
```

Notice two things about using “drop”: First, in order to remove a column, I need to pass the “axis” keyword argument. You could use a number (1, in the case of columns), but I much prefer to use the word “columns”, which is clearer to me.

The second thing to remember is that “drop”, like so many other methods in Pandas, can theoretically take an “inplace=True” keyword argument. In such a case, the original data frame is modified, and the method returns None. However, the core Pandas developers have been saying for a while that inplace=True doesn’t necessarily improve speed or memory use, and that we should avoid using it.

Next, I wanted to set the index of our data frame to the (in)famous “series\_id” column. That’s easily done with “set\_index”; note that here, as well, I don’t pass inplace=True, which means that we get a new data frame back, one which we assign back to the original variable “jt\_series\_df”:

```
jt_series_df = jt_series_df.set_index('series_id')
```

### Read the JOLTS data ([https://download.bls.gov/pub/time.series/jt/jt.data.1.AllItems](https://download.bls.gov/pub/time.series/jt/jt.data.1.AllItems?ref=bambooweekly.com)) into a data frame. Again, make the first column ("series\_id") into the index.

This is where we take the actual data file (AllItems) and turn it into a data frame. Note that the BLS provides a file containing just the current JOLTS values. Because some of the questions I’ve asked look back over time, we need the full data.

I basically repeated the same commands that I used with the series data here, so I’ll just present the code:

```
jt_allitems_filename = 'jt.data.1.AllItems'
jt_df = pd.read_csv(jt_allitems_filename, sep='\t')
jt_df.columns = [one_column.strip()
                 for one_column in jt_df.columns]
jt_df = jt_df.drop('footnote_codes', axis='columns')
jt_df = jt_df.set_index('series_id')
```

I now have a second data frame, jt\_df, with the actual JOLTS values, and with series\_id as the index.

### Create a new data frame combining all of the columns from these two.

I want to do some analysis on the JOLTS data, but my selections will require some information from each of our two existing data frames. I’ll thus join them together, getting a new data frame combining their info.

For a long time, I wondered what the difference was between the “join” and “merge” methods in Pandas. After all, both of them seem to do the same thing, right? The bottom line is that join works on the data frames’ indexes, which must be aligned. By contrast, the merge method allows you to join on any columns from the input data frames.

Since I’ve already set things up to have the series\_id as the index on both data frames, we can [join](https://www.bambooweekly.com/pandas-join/) them without any trouble:

```
df = jt_series_df.join(jt_df)
```

The new data frame has a whole bunch of columns, only a subset of which we’re going to use in our queries.

And now it’s time to level with you a bit: The real reason why I removed “footnote\_codes” from both of the original data frames is that this column appeared in both input files. When you join two data frames together, you’re creating a new data frame, the union of the inputs. Column names must be unique across those inputs; if they aren’t, then you get an error, True, you can pass “lsuffix” or “rsuffix” keyword arguments, telling Pandas what suffix to add to any column names that are duplicated, but I decided to avoid the whole thing by just removing that column.

### Find the most recent quits rate, across the entire United States, for all nonfarm jobs.

Now that we have our complete data frame in place, we can start to query it!

We wanted to find:

- quits data, which is dataelement\_code “QU”
- non-farm data, which is industry\_code 0
- all of the US, which is state\_code “00”
- seasonal value of “S”, meaning seasonally adjusted rates
- ratelevel\_code of “R”, meaning we want the rate

We can do that with the following query:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R')]
```

This returns all of the rows that we wanted. But we didn’t just want the rows; we wanted the most recent quits rate.

I’ll first modify my query to use the two-argument version of “[.loc](https://www.bambooweekly.com/pandas-loc/)”, specifying not only what rows I want, but also what columns. That’ll reduce the size of the output, allowing me to concentrate on what’s really important for this query:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R')['year', 'period', 'value']]
```

Now that I have a data frame of only three columns, I’ll sort on two of them — first on “year”, and then on “period”, by passing a list of column names to “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)”:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R')['year', 'period', 'value']].sort_values(['year', 'period'])
```

Because sorting works in ascending order by default, the final row will contain the value that’s of interest to us. We can grab that with [tail](https://www.bambooweekly.com/pandas-tail/):

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R')['year', 'period', 'value']].sort_values(['year', 'period']).tail(1)
```

We find that the most recent JOLTS data point to a quit rate of 2.5 percent. In other words, this survey is saying that in the most recent month, 2.5 percent of working Americans left their jobs.

I’ll add that some people prefer to use the “[query](https://www.bambooweekly.com/pandas-query/)” method. I’m still not totally sold on it, but it does make the query a bit easier to write and read:

```
df.query("dataelement_code == 'QU' and industry_code == 0 and state_code == '00' and seasonal == 'S' and ratelevel_code == 'R'")[['year', 'period', 'value']].sort_values(['year', 'period']).tail(1)
```

Well, it does make things easier to write and read *except* for the fact that the string cannot be broken up across lines. Notice that using “query” means that you can avoid lots of quotes, which is nice. In some cases, it can also execute more quickly, although I haven’t quite figured out when that is the case. (I’m open to feedback and thoughts on this!)

### There are 12 JOLTS reports per year. In each year on record, how many reports showed the quits rate to be higher than this most recent value?

If you’re thinking, “Wow, a rate of 2.5 percent sounds really high,” then you’re not alone! One of the reasons I’ve heard about the JOLTS data is because the numbers have been so spectacularly high in the last few years.

Or so I’ve been told. Does the JOLTS data support such a conclusion? Let’s find out!

I could have asked to see in how many reports we saw a larger quits rate than 2.5 percent. But I asked for something slightly different, namely to find out how many times per year we saw a quits rate greater than 2.5 percent.

I decided that I would just ignore years without any such reports; there’s no need to have a long list of years with zeroes next to them. (But if you did that, it’s fine!)

I decided to basically repeat the query that I did above, adding an additional condition, namely that the value had to be at least 2.5:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R') &
        (df['value'] >= 2.5), ['year', 'value']]
```

This returns all of those rows for seasonally adjusted non-farm jobs, with the quits rate across the entire United States, where the quits rate is at least 2.5 percent.

If I want to know how often such rates occur per year, I can use “[groupby](https://www.bambooweekly.com/pandas-groupby/)”. The thing is, what aggregate method will I want to use? We’re used to using “mean” in such contexts, but here, we’ll just use “[count](https://www.bambooweekly.com/pandas-count/)”. I’ll group by year, and apply count to the “value” column:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R') &
        (df['value'] >= 2.5), ['year', 'value']].groupby('year')['value'].count()
```

The result:

```
year
2021    10
2022    12
2023     3
Name: value, dtype: int64
```

That’s right: In the more than 20 years that JOLTS has collected data, we’ve only seen a quits rate of 2.5 percent or more in the last few years. The “great resignation,” as I’ve heard it referred to, is definitely a thing; we can see that starting in 2021, 2.5 percent of all Americans were quitting their jobs nearly every month. That’s a really big number. (And it might include you!)

### The states-name data ([https://download.bls.gov/pub/time.series/jt/jt.state](https://download.bls.gov/pub/time.series/jt/jt.state?ref=bambooweekly.com)) lists four regions of the United States. Show the most recent quits data for the most recent time period. In which region are people quitting the most? The least?

We’ve been looking at the US as a whole. But economic data is rarely distributed uniformly; some regions are better, and others are worse. The JOLTS data divides the US into four regions. How did these four regions stack up in the most recent data?

Here, I had to modify my query such that “state\_code” was no longer “00”, but that it was one of the four regional value strings. In such cases, I like to use “[isin](https://www.bambooweekly.com/pandas-isin/)”, which returns True or False depending on whether the series value is contained in a list of values. Here’s how my query looks now:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']]
```

Notice that the second argument I pass to “loc” is a list of four columns: year, period, state\_code, and value. I’ll want to sort based on year and month (period), but I’ll need to know which value goes with which region (i.e., state code).

I can sort the resulting rows as before, by passing a list of strings to sort\_values:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']].sort_values(['year', 'period'])
```

Since there are four regions, and I want to know the quits value for each of those four regions, I’ll then apply tail(4) to the result:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']].sort_values(['year', 'period']).tail(4)
```

Finally, to make things a bit nicer looking, I’ll set the index to be “state\_code”:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']].sort_values(['year', 'period']).tail(4).set_index('state_code')
```

The results are as follows:

```
MW    2.4
NE    1.9
SO    3.0
WE    2.3
```

In other words, the quits value in the south is 3.0 percent, whereas in the northeast, it’s only 1.9 percent. Either of these numbers would normally be considered quite high, but the difference is pretty striking. Does this show that there is greater economic optimism in the southern United States than in the Northeast? Maybe, and I can guess at some reasons for this, but I’m really not sure. I’m guessing that breaking it out by economic sector, rather than looking at all non-farm work, would help to clarify what’s happening here.

### Create a plot showing the quits level for each of these four regions over time. The x axis should be time (year + period) and the y axis should be the quits rate.

I then asked you to plot the four regions’ quit rates over time. Plotting is fairly straightforward with Pandas. But how can we take the quits data that we have for each region, and massage it into a format where the dates form the index and the regions form the columns?

The answer: A pivot table! A pivot table takes an existing data frame, and:

- We choose one categorical column to be the index
- We choose another categorical column to be the columns
- We choose a numeric column to be the values

Here, we want the index to be a combination of year and period. The rule of thumb in Pandas is that wherever you can specify a single column as a string, you can specify multiple columns as a list of strings. That’s what I’ll do here, too.

If you’re thinking that a pivot table also needs to invoke an aggregation method, that’s sort of true. By default, we’ll use the “mean” method. But if there’s only one value for each year-month and state\_code combination, then the mean is just the value itself

I thus invoke “[pivot\_table](https://www.bambooweekly.com/pandas-pivot-table/)” on the result of our previous query, where we got quits data for the regions:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']].pivot_table(index=['year', 'period'], columns='state_code', values='value')
```

Sure enough, I get a nice listing, per date, of each region’s quits value. I can then invoke [plot.line](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html?ref=bambooweekly.com) on the pivot table:

```
df.loc[(df['dataelement_code'] == 'QU') &
       (df['industry_code'] == 0) &
        (df['state_code'].isin(['MW', 'NE', 'SO', 'WE'])) &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'), 
       ['year', 'period', 'state_code', 'value']].pivot_table(index=['year', 'period'], columns='state_code', values='value').plot.line()
```

I get the following result:

![](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-2ffec1517e-0659-42ca-8229-524c0b896c3d_640x480.jpg)

We can thus see that for a long time now, the south (green line) has had a higher quits rate than the other regions, and that the northeast has had a lower quits rate than the others.

### Create a data frame in which the index contains the year and period, and with two columns -- the quits rate (QU) and the number of job openings (JO), both for the entire United States. How highly correlated are these values?

Finally, the US currently has a very large number of unfilled jobs. That’s part of the problem with the economy; employers have to compete in order to attract workers, which leads to higher wages, and thus higher inflation. Can we see a correlation between the quits rate and the number of jobs openings over time? That is, if we look at the quits rate and the number of open jobs for each time period, how much do they move in lockstep with one another?

We can calculate this in Pandas with the “[corr](https://www.bambooweekly.com/pandas-corr/)” method. It takes a data frame, and calculates the correlation between the numeric columns.

In order to call “corr”, we’ll thus need to create a data frame in which the dates (years + periods) are the index, and the columns contain the quits and job-opening numbers. This means modifying our query to only look for those values for “dataelement\_code”, and then (as above) creating a pivot table:

```
df.loc[(df['dataelement_code'].isin(['QU', 'JO'])) &
       (df['industry_code'] == 0) &
        (df['state_code'] == '00') &
        (df['seasonal'] == 'S') &
        (df['ratelevel_code'] == 'R'),
       ['year', 'period', 'dataelement_code', 'value']].pivot_table(index=['year', 'period'], columns='dataelement_code', values='value').corr()
```

Whenever you invoke “corr”, the diagonal will contain 1.0, showing that every column correlates to itself 100 percent. Which makes sense, of course.

In this case, we see that job openings and the quits rate are correlated by 86 percent, which is very high. Remember that [correlation isn’t causation](https://xkcd.com/552/?ref=bambooweekly.com), so we can’t say what led to what here. However, it does mean that if and when the number of open jobs goes down, people will be less likely to leave their current job. After all, if you know that you can get a new job, then why not leave the current one? But if you aren’t so sure, then you’ll hesitate more.

Any thoughts, comments, or suggestions? Leave them in the comments!

Here’s my Jupyter notebook for the week: [https://drive.google.com/file/d/1vsTivTS0Y9jDER07ojAChIsKsTfSuENz/view?usp=drive\_link](https://drive.google.com/file/d/1vsTivTS0Y9jDER07ojAChIsKsTfSuENz/view?usp=drive%5Flink&ref=bambooweekly.com)

I’ll be back next Wednesday with a new problem from current events.

Reuven