This week, we looked at changes in the minimum wage in the United States. While the federal minimum wage has been $7.25/hour since 2009, each individual state can raise it beyond that level. In such cases, the state’s minimum wage applies. Given that the federal minimum wage hasn’t changed in about 15 years, and that inflation was rather high over the last few years, it shouldn’t come as a surprise that a number of states’ minimum wage is now higher than the federal one.
We compared the different states, finding which raised their minimum wage on January 1st — and if so, by how much.
Data and six questions
The data this week comes from FRED, the St. Louis Fed's portal for economic data (https://fred.stlouisfed.org/). The data consists of CSV files from (nearly) all 50 states, with info and download links from this page:
https://fred.stlouisfed.org/release?rid=387
Part of the challenge this week involves figuring out how to download the data. I will say that when you go to a particular state's minimum-wage page (e.g., https://fred.stlouisfed.org/series/STTMINWGOH), click on "Download" and then select "CSV," you'll get a two-column CSV file with a date column (January 1st of the year for which we're reporting) and a value column. Notice that the data on FRED comes from URLs that end with `STTMINWGXX`, where the `XX` can be replaced by the state's two-letter code. That's not quite enough to get the data, but it's a good start.
I gave you six questions and tasks based on the data. Here are my solutions; a link to my Jupyter notebook is below.
Create a series containing the abbreviations for all US states. I found a source here: https://github.com/jasonong/List-of-US-States
Before doing anything else, I loaded Pandas:
import pandas as pdIf you go to the GitHub repo that I mentioned, you’ll see that it contains the state data in both CSV and YAML formats. You might then (naturally) assume that since clicking on the link for the CSV file (https://github.com/jasonong/List-of-US-States/blob/master/states.csv) displays it, you can use that same URL to retrieve the data into Pandas.
Pandas most often reads CSV data from files, we can pass “read_csv” a URL (string) as an argument, and get a data frame back. There’s no need to download the file before calling read_csv on it. You might thus try the following:
df = pd.read_csv('https://github.com/jasonong/List-of-US-States/blob/master/states.csv')As you’ll discover upon trying this, though, you’ll get an error. The problem is that while the filename indicates we’re looking at “states.csv”, we’re actually looking at GitHub’s HTML page containing that, along with many other things. If we want the raw CSV file, we need to go to a different URL.
Fortunately, we can find that URL pretty easily by clicking on the “raw” button. That takes us to a different (but related) URL, one which gives us the CSV file on its own: https://raw.githubusercontent.com/jasonong/List-of-US-States/master/states.csv
url = 'https://raw.githubusercontent.com/jasonong/List-of-US-States/master/states.csv'
df = pd.read_csv(url)There’s nothing wrong with the data frame that we create in this way. However, I wanted to create a series with just the state abbreviations. The easiest way to do this is to just select the “Abbreviation” column from the data frame as soon as we download it:
url = 'https://raw.githubusercontent.com/jasonong/List-of-US-States/master/states.csv'
us_states = (
pd
.read_csv(url)
['Abbreviation']
)You might be wondering why I didn’t pass the “usecols” argument to read_csv, indicating that we were only interested in the “Abbreviation” column, and thus shrinking the size of the original data frame that we created. The reason is that we still would have ended up with a data frame, albeit a one-column data frame. I decided that given the small size of the data frame we created, it would be just fine to retrieve it all and then grab one column into a variable, “us_states”.
We now have a series, us_states, with 51 two-character string elements (50 states + DC). This will come in handy now, as we build the data frame with minimum-wage information about each of them.
Create a data frame in which there is one row per year, and the columns are two-letter state abbreviations. Be sure to treat the index as datetime values. If no data appears to be available for the state, then ignore it.
FRED (https://fred.stlouisfed.org/), the online information portal created and maintained by the St. Louis Fed, offers information about minimum-wage rates in various states at a set of fairly uniform URLs. Given a state’s two-letter abbreviation, we can get data about that state’s minimum wage at
https://fred.stlouisfed.org/series/STTMINWGXX
For example, if we want to see information for Illinois, whose two-letter abbreviation is “IL”, we can go to
https://fred.stlouisfed.org/series/STTMINWGIL
However, this link brings us to FRED’s display page for the data. It doesn’t give us the data itself. To retrieve that, we can click on the “download” button, select “CSV” from the menu, and download the file. If you look at the URL, though, you might be amazed by its length:
A lot of this has to do with creating graphs, and doesn’t affect the CSV file in any way. But some of it does, including the starting and ending dates. I managed to trim the URL down quite a bit, without any obvious loss in data:
url = f'https://fred.stlouisfed.org/graph/fredgraph.csv?id=STTMINWG{one_state}&cosd=1968-01-01&coed=2024-01-01&fq=Annual&fam=avg&gsnd=2020-02-01&vintage_date=2024-01-03&nd=1968-01-01'I’m sure that there are still sections that I could remove, but I was a bit nervous about doing so.
Notice that the above isn’t a URL, but rather the assignment of a string — an f-string, at that — to a variable. Inside of the f-string, I reference the variable “one_state”, which should contain the two-letter abbreviation of the state we’re looking at.
If I want to retrieve the data for each state, then I should basically be able to do the following, iterating over the values in our us_states series:
for one_state in us_states:
print(one_state)
url = f'https://fred.stlouisfed.org/graph/fredgraph.csv?id=STTMINWG{one_state}&cosd=1968-01-01&coed=2024-01-01&fq=Annual&fam=avg&gsnd=2020-02-01&vintage_date=2024-01-03&nd=1968-01-01'
pd.read_csv(url)This is a great start, except for four things:
- Some states don’t have minimum-wage information in FRED,
- We have created some data frames, but we haven’t stored them anywhere,
- We haven’t combined the downloaded data into a data frame, and
- We want to treat the index as a “datetime” type, rather than a string.
Let’s start with handling the states that don’t have any data in FRED. In such cases, we’ll get an exception, an HTTPError. That’s not a native exception to Python; it’s actually from the “urllib” module in Python’s standard library. We can trap it by saying:
from urllib.error import HTTPError
for one_state in us_states:
print(one_state)
try:
url = f'https://fred.stlouisfed.org/graph/fredgraph.csv?id=STTMINWG{one_state}&cosd=1968-01-01&coed=2024-01-01&fq=Annual&fam=avg&gsnd=2020-02-01&vintage_date=2024-01-03&nd=1968-01-01'
pd.read_csv(url)
except HTTPError as e:
print(f'Error: {e}')
This ensures that we’ll get all of the state data where it exists, and we won’t end up with an unhandled exception if something goes wrong. Notice that I still print the state that we’re on with each iteration of our “for” loop; that’s a practice that I’ve had for a very long time, allowing me to see that things are progressing when they might take a long time.
I also indicated that I wanted the “DATE” column, which is common to all of the downloaded CSV files, to be of type “datetime”, and to be the index. Both of these are easily handled with options to read_csv:
- parse_dates — we pass a list of column names that we want to turn into datetime values
- index_col — we pass a string (for one column) or a list (for a multi-index) of the column(s) we want to be the index.
In this case, we’ll pass both of these keyword arguments, and both will have the value of DATE. It’s really convenient to have the data in a uniform format, allowing us to do such a thing. Our code now looks like:
from urllib.error import HTTPError
for one_state in us_states:
print(one_state)
try:
url = f'https://fred.stlouisfed.org/graph/fredgraph.csv?id=STTMINWG{one_state}&cosd=1968-01-01&coed=2024-01-01&fq=Annual&fam=avg&gsnd=2020-02-01&vintage_date=2024-01-03&nd=1968-01-01'
pd.read_csv(url, parse_dates=['DATE'], index_col='DATE')
except HTTPError as e:
print(f'Error: {e}')
We’re doing everything right now, except for the biggest and hardest part: Taking each of these individual, one-column data frames and turning them into a larger data frame.
If we were just trying to combine two data frames, we would obviously want to use “pd.concat”, which takes a list of data frames and returns one new one, the result of combining them. In this case, though, it seems a bit more complex; I want to combine them, but I want each of the original data frames to be a separate column in the new, combined one, with the state abbreviation as a column name. Moreover, I want the index to represent a superset of all the dates mentioned in all of the downloaded state data.
Well, good news: pd.concat can do all of that! We just need to pass it a dictionary in which the state abbreviations are the keys, and the values are data frames. It’ll return a new data frame whose index combines all of the individual data frames’ indexes.
Let’s create the dictionary:
d = {}
for one_state in us_states:
print(one_state)
try:
url = f'https://fred.stlouisfed.org/graph/fredgraph.csv?id=STTMINWG{one_state}&cosd=1968-01-01&coed=2024-01-01&fq=Annual&fam=avg&gsnd=2020-02-01&vintage_date=2024-01-03&nd=1968-01-01'
d[one_state] = pd.read_csv(url, parse_dates=['DATE'], index_col='DATE')
except HTTPError as e:
print(f'Error: {e}')
When the loop has finished running, we’ll have dict whose keys are strings (state abbreviations) and whose values are single-column data frames with datetime indexes.
We can turn this dictionary into a data frame with:
df = pd.concat(d, axis='columns')Notice that we have to specify axis='columns' to ensure that they’re placed side by side, rather than top-and-bottom.
The good news? It worked:
AK AZ AR
STTMINWGAK STTMINWGAZ STTMINWGAR
DATE
1968-01-01 2.10 NaN NaN
1969-01-01 2.10 NaN NaN
1970-01-01 2.10 NaN 1.10
1971-01-01 2.10 NaN 1.10
1972-01-01 2.10 NaN 1.20
1973-01-01 2.10 NaN 1.20
1974-01-01 2.10 NaN 1.20
1975-01-01 2.10 NaN 1.20
1976-01-01 2.80 NaN 1.90
1977-01-01 2.80 NaN 1.90
1978-01-01 2.80 NaN 1.90
1979-01-01 3.40 NaN 2.30This is a subset of the data. As we can see, the index shows the years (really, January 1st of each year) for which we have data for a state. The columns are the state abbreviations. And we have values where one was reported, and NaN where one wasn’t.
And yet, something seems weird here: What’s with the multi-index?
Well, we did set the index with pd.concat. But there was already a column name in the original data frame that we downloaded. We could modify our downloading code to remove that column name. But another approach would be to use the “droplevel” method, which lets us remove part of a multi-index. Calling droplevel(1, axis='columns') removes the second part (level 1) of the columns:
df = (
pd
.concat(d, axis='columns')
.droplevel(1, axis='columns')
)The result? A data frame with 57 rows (one for each year of data) and 46 columns (one for each state + DC for which we have data). If any state is missing data for one of those years, we’ll have a NaN value there.
Which states haven't reported any minimum-wage data?
We know that some states haven’t reported any minimum-wage data, or at least that it isn’t available via FRED, because we got errors downloading data for those states. Whether that’s because they don’t have their own minimum wage (i.e., they defer to the federal one), or it’s because they don’t want to provide the data, or because FRED just doesn’t have it — I’m not sure, to be honest.
Of course, one way for us to know which states haven’t reported any data would be to collect the state abbreviations that raise exceptions when downloading.
But assuming that we weren’t smart enough to do that, and that we currently have the us_states series and the our data frame with the state abbreviations as column names — what now?
This is one of those cases where we could do it in Pandas, but that it’s just going to be easier to do it using Python’s core data types. Specifically, we’ll use Python’s “set” class. A set in Python is basically a dictionary’s keys, without any values. A set’s elements must all be hashable (immutable, more or less), they cannot repeat, and they cannot be accessed via an index. But you can search inside of a set, and you can perform set operations.
If we turn us_states into a set, and then we turn the columns of df into a set, we can find out which are missing by subtracting one from the other:
set(us_states) - set(df.columns)Here’s what I get:
{'AL', 'LA', 'MS', 'SC', 'TN'}In other words, five states (Alabama, Louisiana, Mississippi, South Carolina, and Tennessee) haven’t reported minimum-wage rates for their states. From a bit of non-exhaustive research, it looks like none of them have their own rates, which explains why they haven’t reported them.
For each state that did report data, what's the earliest year that we have on record? Which five states were the most recent to report minimum-wage values?
Different states enacted their own minimum-wage laws at different times. Thus, the first date for which we have data for each state is different.
I wanted to know: For each state, when did they start to report their minimum-wage information?
Given a single series with a datetime index and sorted by index, then I could run “dropna” and grab the first element from the index.
But on a data frame, where each column will give me a different value, it gets a bit more complex. Fortunately, we can take advantage of a Pandas method known as “first_valid_index”. If we run this method on a series, then we get the index corresponding to the first non-NaN value in the series.
All we have to do, then is run this method on each of the columns in df. That’ll give us the first non-NaN index for each column.
How can we run the method on each column? We can use the “apply” method:
(
df
.apply(pd.Series.first_valid_index)
)This returns a series whose index are state abbreviations, and whose values are dates — the dates on which the each state first reported values.
We can sort the series by with “sort_values”:
(
df
.apply(pd.Series.first_valid_index)
.sort_values()
)We now have the states (our index) sorted from the earliest to latest first record of minimum-wage info.
Which five states were the most recent to report this info? Let’s sort in reverse order, and then run “head”:
(
df
.apply(pd.Series.first_valid_index)
.sort_values(ascending=False)
.head()
)The result:
AZ 2007-01-01
FL 2006-01-01
IA 1991-01-01
MO 1991-01-01
KS 1979-01-01
dtype: datetime64[ns]In other words, we see that Arizona was the most recent state to have its own minimum wage, followed by Florida, Iowa, Missouri, and Kansas.
In which states did the minimum wage go up on January 1st of this year? Show the five states in which it went up the greatest amount. Show the five states in which it went up the greatest percentage.
This week’s topic was inspired by the reports that a number of states raised their minimum wage on January 1st. Which states were these?
We can use the “diff” method on a data frame to find out how much each row changed from the previous row. The first row in the data frame will have NaN values, but each subsequent row will have a float indicating by how much it differed. (This is a simple example of a “window function.”)
We can thus write:
(
df
.diff()
)The thing is, we want to find out which states had a change in 2024. This means that we really don’t care (for now) about the history; we just want to retrieve the row for January 1st, 2024. We can do that with “loc”:
(
df
.diff()
.loc['2024-01-01']
)This returns a series, where the index consists of df’s column names, and the values are from that first row.
Finally, we only want those elements that are greater than 0 — meaning, where the the minimum wage increased on January 1st. We can do that by using loc along with lambda, filtering the series:
(
df
.diff()
.loc['2024-01-01']
.loc[lambda s_: s_>0]
)Finally, we’ll ask to see the index:
(
df
.diff()
.loc['2024-01-01']
.loc[lambda s_: s_>0]
.index
)The result:
Index(['AK', 'AZ', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'HI', 'IL', 'ME', 'NE',
'NV', 'NJ', 'NY', 'OR', 'MD', 'MI', 'MO', 'RI', 'SD', 'VT', 'WA'],
dtype='object')It would see that 23 states, including Washington DC, raised their minimum wage at the start of the year.
To see the five states in which it rose the most, we can once again use diff, loc, and lambda to find where it rose at all, and then sort the results, grabbing the top five:
(
df
.diff()
.loc['2024-01-01']
.loc[lambda s_: s_>0]
.sort_values(ascending=False)
.head(5)
)Here’s the result I got:
HI 2.00
OR 1.95
NY 1.80
NV 1.75
MD 1.75
Name: 2024-01-01 00:00:00, dtype: float64We see that Hawaii increased its minimum wage by $2, followed by Oregon, New York, Nevada, and Maryland.
That’s great, but we also want to know the five states in which it went up the greatest percentage. We can do that with a close cousin to diff, namely “pct_change”:
(
df
.pct_change(fill_method=None)
.loc['2024-01-01']
.loc[lambda s_: s_>0]
.sort_values(ascending=False)
.head(5)
)I got the following results:
NV 0.184211
HI 0.166667
OR 0.144444
NE 0.142857
MD 0.132075
Name: 2024-01-01 00:00:00, dtype: float64We can see that four states (Nevada, Hawaii, Oregon, and Maryland) not only increased their minimum wage by a large amount of money, but it was also a large increase relative to their previous minimum wages. Given more time, I might be interested in seeing where they rank today on minimum wage, and where they ranked last year. If they were the bottom half of minimum wage rates, then pushed through big raises, that would account for both.
By the way, if you’re curious to know where minimum wage is the highest, I can find it with
df.loc['2024-01-01'].sort_values(ascending=False).head(10)The result:
DC 17.00
WA 16.28
CA 16.00
NY 16.00
CT 15.69
OR 15.45
NJ 15.13
MD 15.00
MA 15.00
CO 14.42
Name: 2024-01-01 00:00:00, dtype: float64We see that Oregon and Maryland (two of the four states on both of our previous lists) are in the top 10.
For each decade, show the mean minimum wage across all states. Show it as a line plot.
Finally, I asked you to take each decade and plot the mean minimum wage. Originally, I thought that it might be interesting to plot the mean minimum wage across all states, and see how it has risen over the years. To do that, we can say:
(
df
.mean(axis='columns')
.plot.line()
)Notice that here, I’m using the “mean” method, but I’m asking it to calculate the mean across all of the columns, giving me one result per row (i.e., per year). The default is for the axis to be rows, giving us one result per column. I then invoke “plot.line” to get a line plot:
But I actually asked you to show me, per decade, how the mean minimum wage looked on a decade-by-decade basis. To do that, I basically expected you to use “resample”, a method that we can only use on a data frame whose index is a time series (i.e., of type “datetime”):
(
df
.resample('10Y')
.mean()
)In other words, we asked Pandas to start with the first date in the system, and calculate the mean for the values in each column for each 10-year period. (This isn’t exactly a decade, since it counted starting in 1968. So it did 1968-1977, 1978-1987, etc.) You can think of resampling as performing a “groupby” but by date.
I then calculated the mean across all of the columns, and plotted that line:
(
df
.resample('10Y')
.mean()
.mean(axis='columns')
.plot.line()
)The result, as you can imagine, is a bit smoother than the previous line:
That’s it for this week! My Jupyter notebook is at https://drive.google.com/file/d/1hJSReyrGeUfXVxWBWh8EOaIpjAedKvkD/view?usp=sharing. I welcome comments and corrections.
And of course, I’ll be back next Wednesday with more Pandas puzzles based on current events.
Reuven