BW #17: Debt ceiling (solution)
The amount of debt the US can issue is approaching its limit, known as the "debt ceiling." What does the debt look like, and who holds it? Let's find out, using Pandas and ChatGPT!
As I write this, high-level US officials are still discussing the debt ceiling. That’s the maximum amount of money that the US federal government can officially owe. If the debt ceiling isn’t raised, then the US will end up defaulting on its loans — largely, but not only, meaning that it won’t make its regular payments to bondholders.
That would be rather bad, both for the US economy and for the world as a whole. That’s because US treasury bonds have long been seen as the most stable form of investment out there. You won’t make tons of money with treasury bonds, but you can be sure that you’ll get your money back. Which is why companies, governments, and individuals all buy treasury bonds — you might not earn that much interest, but at least you’ll get more than you would in a bank savings account.
If the US doesn’t pay its debts, then that raises all sorts of questions and causes lots of problems. Which is why financial markets are all talking about why the US needs to raise its debt ceiling in the coming days, before any of that would
This week, I thought we could look at data having to do with US debts. But I also thought that it would be fun and interesting not to write the Pandas code ourselves, but rather to ask ChatGPT to do it for us. Today, I’ll walk you through how I solved this problem, what’s going on behind the scenes, and even what obstacles I encountered in achieving my solution. I’m also going to record a video (which I’ll put on YouTube) walking through this solution, if you prefer to see it happen on screen.
Setup and configuration
In just six months, ChatGPT has become one of the most popular and best known technology products ever. I’ve read that over 100 million people signed up to use it within the first few weeks of it being released, which is pretty astonishing. (Even more so, if you consider that OpenAI hasn’t advertised its product at all; everything has come via word of mouth, social media, and the press.)
From the start, people noticed that ChatGPT was able to write code, along with poetry, songs, and short screenplays. (It can also read code and diagnose a variety of problems in it, which is pretty amazing.) But that was all with the first model, known as GPT-3.5. A newer model, GPT-4.0, is more sophisticated in a variety of ways — but it’s only open to paid subscribers, currently at a price of $20/month.
The above screenshot shows what it looks like when you are subscribed to ChatGPT Plus. At the top of the screen, you can select between the GPT-3.5 model, which runs faster and has no limits, and the GPT-4.0 model, which runs slower, is limited to 25 queries every three hours, and is more sophisticated.
If you’re subscribed to ChatGPT Plus, and if you’re using the GPT-4.0 model, then you can also choose from one of several options:
The above menu, visible when your mouse cursor hovers over the GPT-4 name, lets you optionally select to include browsing with Bing (i.e., including up-to-date Internet searches in your chat responses) or to use plugins. For our purposes, I’m going to select plugins:
Once you select “plugins,” you will then see a menu at the top of the screen where you can select which plugins you want to install.
My menu shows three plugin icons, the ones that I have activated. If you haven’t selected any, you’ll have to pull down the menu. That’ll show you the list of installed plugins, including those that you have activated. It’ll also let you go to the plugin store.
Clicking on the “plugin store” link brings you to this page:
For today’s exercise, we’ll be using Noteable. Find the plugin, click on “install,” and if you haven’t yet installed other plugins, it’ll be activated as well (with a blue checkmark). In the days since I started to use ChatGPT Plus and the plugin store, the number of available plugins has increased dramatically.
If you don’t see the Noteable plugin, it might be because they’re updating the software. Every update apparently requires OpenAI to approve the new version, and people who viewed my YouTube video from a few days ago complained that it had disappeared from the plugin store, before reappearing a little while later. So if you don’t see it, just wait a bit.
In order to use the Noteable plugin, you’ll need to create a free Noteable account. Indeed, from speaking with someone at Noteable earlier this week, I learned that you can create as many projects and notebooks as you want on the free version of the app. The only limitations are that you’re on a small instance of Amazon’s EC2 virtual machines, which might not have the memory or CPU to handle larger data analysis tasks. Also, if you don’t use the Noteable system for some time — I can’t remember precisely how long — then the machine shuts down. Your notebooks will still be available, but they’ll need to be rerun to keep their state. You can pay for an enterprise account, at $120/person, which gives you a private machine and more options for EC2 instances — but it sounds like the free version of Noteable will be more than adequate for my needs at the moment.
After you’ve created a Noteable account, you’ll need to create a project, which you can think of as a directory.
The above is what it looks like when I want to create a new project in my default Noteable space. Below the “create” menu, you can see that I already have a project called “ChatGPT playground,” which is where I’ve been doing my experimenting.
Now that Noteable is set up, I can go back to ChatGPT plus, making sure that I’m using GPT-4 and that the Noteable plugin is set up correctly. (There’s an authentication step that I’m forgetting or missing, but I remember it being very straightforward. I also remember that ChatGPT was very good at telling me what steps remained in order to finish setting up Noteable.)
Before continuing, we’ll also need to tell ChatGPT which Noteable project it should connect to via the plugin. You can do this by grabbing the full URL of your Noteable project, and then saying to ChatGPT:
Please use the Noteable project [INSERT_URL_HERE] for all notebooks.
Now that we have ChatGPT Plus, the Noteable plugin, and Noteable all set up, we can ask ChatGPT to perform all sorts of tasks for us. If the task has to do with downloading or analyzing data, then it’ll ask Noteable to do it.
This is a big shift: Instead of ChatGPT producing the Python and Pandas code that we could copy into our own Jupyter notebooks and run, here the AI is producing the code and then running it in a notebook. And the code that it produces, while not perfect, is still pretty darned amazing.
I’ve long thought that programming languages are frustratingly specific and pedantic because that’s the only way to avoid the ambiguity of natural language. Describing a technical task in English sounds like a great idea, but you can quickly discover many limits and gray areas. So when I posed yesterday’s question about the federal debt, it was after many, many hours of playing with ChatGPT, writing, editing, and repeating my questions in different ways to get the answer that I wanted. For me, it was frustrating to see how ChatGPT was so smart at turning my English into code — and yet so dumb at failing to understand what I was talking about.
Answering our questions
Yesterday, I posed 10 questions about the debt ceiling, and asked you to use ChatGPT to answer them. As you might have guessed, there wasn’t much work to do — because the questions, as I posed them, were precisely what I gave to ChatGPT. And I got the answers I was looking for. It was pretty amazing.
That’s not to say that things went swimmingly well all of the time. I sometimes ran up against ChatGPT’s limit of 25 queries every 3 hours. I sometimes saw ChatGPT get it wrong, realize that something was wrong, back up, and then try again. Sometimes, this retry worked perfectly, other times it didn’t, and at still other times it seemed to get stuck in an infinite loop. I spent a lot of time figuring out how to pose these questions, especially the questions at the end about which countries own American debt.
By the way, a nice thing about the GPT-4 model is that it accepts very long queries. So even if you’re limited to 25 questions every three hours, you can stick a lot of text in a single query. I tried to put all of my questions into a single query, but that nearly always ended up backfiring on me.
Let’s go through the questions (which are also the answers!) one by one, and I’ll comment on what I did and what I saw.
Because the questions are also the ChatGPT queries that I executed, I’ll put them in code-style blocks, as opposed to numbered questions (which is my usual style here):
Create a Jupyter notebook. In that notebook, create one Pandas data frame with the total amount of federal debt at https://fred.stlouisfed.org/series/GFDEBTN.
Use Seaborn to plot the total federal debt over the last three decades. The x axis should represent time, and the y axis should represent the amount of debt. Analyze the plot in Markdown, just below the plot.
When you ask ChatGPT to use a plugin, it shows you a little icon for each task it’s using the plugin for. When the Noteable plugin is in active communication with ChatGPT, it’s colorized, and says, “Using Noteable.”
When it’s done, the icon changes to gray and says (in the past tense) “Used Noteable.”
Notice that these icons have little arrows on them. You can open them up to see the JSON communication that went between the systems. In this case, ChatGPT communicated with Noteable in four ways:
It created a notebook called “Federal debt analysis.” Not a bad name!
It imported Pandas, Matplotlib, Seaborn, and datetime. It downloaded the data, asked for all date-looking columns to be parsed as dates (parse_dates=True), made the date column into the index, calculated today’s date, and then removed any rows from earlier than 30 years ago.
It created a Seaborn line plot, including an appropriate title.
It analyzed the plot, and gave an explanation in Markdown following the plot. The analysis wasn’t super insightful, but it wasn’t bad.
Pretty impressive all around, I have to say. I tend to shy away from raw Matplotlib calls, preferring to use Seaborn’s own API. So I probably would have called methods on “plt” much less. But… it got the job done, right?
Here was the plot it produced:
In the same notebook, download https://fred.stlouisfed.org/series/A091RC1Q027SBEA (interest payments on the Federal debt) into a separate data frame.
Use Seaborn to plot interest payments over the last three decades. The x axis should represent time, and the y axis should represent the interest payments. Analyze the plot in Markdown, just below the plot.
I learned the hard way that if you don’t tell ChatGPT to use the same Jupyter notebook, you can accidentally end up creating a new one. So I was explicit about using the same notebook, but also specifying a different data frame.
Other than that, the code was pretty similar this time around. It was smart enough to use a different variable name (df_interest). It was also smart enough to incorporate its previous analysis in this one, comparing the total federal debt with interest payments on that debt.
Here was the plot it created:
Use Seaborn to create a scatter plot, comparing the federal debt and the interest payments on the debt in the last three decades. Include a regression line in this plot. What correlations do you see? Describe your findings in Markdown, just below the plot.
This is where I was sort of hoping to stump ChatGPT. Creating a scatter plot isn’t that hard. But it requires that the two pieces of information be in the same data frame. That would mean joining the data frames together into a new one. Plus, a regression line is a really nice thing to see — but it’s not the most easily discovered option in the documentation for Seaborn scatter plots.
And yet? It performed like a champ, creating the following code:
df_merged = pd.merge(df, df_interest, left_index=True, right_index=True)
# Create the scatter plot with regression line
sns.lmplot(data=df_merged, x='GFDEBTN', y='A091RC1Q027SBEA')
# Set the title and labels
plt.title('Comparison of Federal Debt and Interest Payments Over the Last Three Decades')
plt.xlabel('Federal Debt')
plt.ylabel('Interest Payments')
# Show the plot
plt.show()
Notice that in my query, I didn’t say what data frames to use. I just asked for a scatter plot comparing the federal debt and interest payments made. ChatGPT figured out which data frame was which, then how to join them. Moreover, it used the correct Seaborn plotting system (“lmplot”), which is different than the usual method used for scatter plots.
Color me impressed. ChatGPT did magnificently here:
Then I discovered a file that had to do with the federal debt which I knew would be more challenging. That’s because the file ( https://ticdata.treasury.gov/Publish/mfh.txt) is sort of in a CSV format, sort of in a free-text format, and sort of in a fixed-width field format. I would give such a file to people taking my class in regular expressions, because it would be so challenging. Was ChatGPT up to the task?
The answer was “yes,” but only after many many many repeated attempts to get it to work. Here’s the text I ended up with:
Download data from https://ticdata.treasury.gov/Publish/mfh.txt. Before parsing the file, remove everything before the word "Japan". Also remove the line that starts with "Grand Total", and everything that follows it. Create a data frame from the text that remains. The data frame does *not* have any headers; interpret the first line as data. Each line consists of a country name and floating-point numbers. The fields are separated by two or more space characters. The first field on each line, the country name, should be made the index. The remaining values will all be floating-point numbers. The data frame's column names should be a combination of month name and year, starting with "Jan-2023" and going down, one month at a time, until reaching the final column.
Three things were difficult with getting ChatGPT to do what I wanted:
Removing the text before and after the actual data. I finally ended up saying that we should get rid of all of the initial and final rows.
The column names were brutal to figure out. Not only were the column names spread across two lines, but there was also the “Country” name above the index, which confused ChatGPT’s coding on several occasions. And the fact that the columns were floating point numbers in fixed-width fields,
The country names contained spaces. This threw off a lot of attempts to parse things.
In the end, ChatGPT still got it wrong. But it realized that it had made a mistake, and then tried again:
During some of my initial attempts, I was completely blown away when ChatGPT would say to me, “This file is not in CSV format. I’ll try to parse it with a custom function.” And sure enough, it went ahead and did so. I mean, that’s not a bad way to approach things, but … wow.
I want to stress that this took me a really long time to get right. It’s the sort of thing were I would say, “If only I could use a regular expression here” — but the whole point of ChatGPT is that you don’t use code, you use English. (And yes, it would have been fine with a regexp, but I was trying to talk to it in specific, yet non-programmer terms.)
It was also very smart about how to get rid of the initial and final lines, and how to skip any blank lines the file might contain:
# Remove everything before the word 'Japan'
data = data[data.index('Japan'):]
# Remove the line that starts with 'Grand Total', and everything that follows it
data = data[:data.index('Grand Total')]
# Split the data into lines
lines = data.split('\n')
# Create a list to hold the parsed data
parsed_data = []
# Parse each line
for line in lines:
# Split the line into fields
fields = re.split(' +', line.strip())
# Skip empty lines
if len(fields) < 2:
continue
# Convert the numeric fields to float
for i in range(1, len(fields)):
fields[i] = float(fields[i])
# Add the parsed line to the list
parsed_data.append(fields)
# Create a data frame from the parsed data
df_ticdata = pd.DataFrame(parsed_data)
I also love the comments that it inserts when writing code, so that you can debug your ChatGPT prompt.
I then decided to push it a bit harder, to see if it could understand instructions that a person would know how to handle creating a pie chart, but combining everything other than the 15 countries with the most holdings into a single pie piece:
Using Seaborn, create a pie chart showing what proportion of debt was owned by each country in Jan 2023. Do this for the top 15 countries; all others should be lumped into a single pie segment labeled "others".
It did a great job:
Notice, though, that in addition to the “Others” pie slice, there is also an “All Other” pie slice. I tried a few times to get ChatGPT to combine these into a single slice, but wasn’t successful. I’m sure that there’s a way to get it to work.
By the way, notice that for all the worries that you read in the press about how much US debt is owned by China, Japan actually owns more! And the UK owns not a small amount, either. Of course, Japan and the UK are liberal democracies, and China isn’t, so there is a bit of a difference there.
Using Seaborn, create an area plot showing the holdings of the top 5 countries holding treasury securities over time. Ignore "All Other" when choosing the top 5. Analyze the plot in Markdown, just below the plot. Turn it around, so that January 2022 is on the left, and January 2023 is on the right.
Oddly, the file listing how much debt is held by each foreign country has the most recent date on the left, and the oldest date on the right. I thought that it might be interesting to have ChatGPT turn that around, if it could, and produce a line plot.
Demonstrating the stochastic properties of a large-language model (i.e., you get different results each time), I got an error when running my query this time. But ChatGPT saw the error, and decided that it could save the situation by using raw Matplotlib instead of Seaborn:
And it worked just fine:
Pretty amazing, no?
I then decided to ask ChatGPT to create another pie chart. This time, though, I asked it to make the pie chart per continent, rather than per country. In other words, it would need to figure out which continent each country is on:
Create a pie chart showing what proportion of debt was owned by each continent, rather than each country. That is, we should have one pie slice for Asia, another for Europe, another for Africa, etc. Any countries that cannot be classified into a continent can remain named.
The result? It actually wanted me to do this hard work!
I wasn’t going to take such backtalk from an AI chatbot! I wrote:
Please use your knowledge of geography to classify each country into a continent. Then create the pie chart.
This was a mistake, because ChatGPT started to create such a dictionary for every country in the world. For reasons that I don’t quite understand, that started to take a long time. I thus stopped it in the middle, and asked it to do something slightly more precise:
Please use your knowledge of geography to classify each country in this data frame to a continent. Then create the pie chart.
To be honest, I had to get these solutions out, and it was taking a really long time to create a dictionary.
I decided to start an entirely new chat in ChatGPT, and re-entered my query to download the per-country debt ownership data. Then I retried this query, adding the sentence, “Use your best judgment to classify countries into continents.” This time, it gave me a completely new and different solution, one which depends on pycountry-convert, a package on PyPI.
ChatGPT then told me that the code would work, but that I need to install pycountry-convert on my own using pip. I hadn’t ever heard of this package before. But I went onto my Noteable notebook, installed the package, then re-ran the code that ChatGPT had created. And … it worked!
Is this precisely how I would have classified continents? No.
Is it how ChatGPT classified continents just yesterday? No.
But is it a solution? Yes, yes it is.
To the best of your ability, classify each of the countries as a democracy or a dictatorship. Create a pie chart showing what proportion of debt was owned by each type of government. If you can think of additional classifications for countries, feel free to use those. Any countries that cannot be classified into a governmental system can remain named.
When I used this query yesterday, it worked just fine. ChatGPT complained a bit, saying that it’s just a large language model, and doesn’t have access to such information. It told me where I could get such information, and how I could write code. But it didn’t write the code for me.
That’s when I got upset, and said:
You actually do know. I insist, even if you're wrong, that you produce this pie chart.
Well, I guess I got what I asked for:
I promise you, this worked yesterday!
I decided to try one more time:
The plot showed everything as "unclassified." Please create the pie plot using the classifications that you found and created in your code.
That didn’t help; I got the same all-unclassified output.
I thus tried again, in a new chat window. I repeated my prompt that downloaded the data. And then I tried a slight update and change to my prompt:
To the best of your ability, using whatever knowledge and resources you have at hand, classify each of the countries named in this data frame as a democracy or a dictatorship. Create a pie chart showing what proportion of debt was owned by each type of government. If you can think of additional classifications for countries, feel free to use those. Any countries that cannot be classified into a governmental system can remain named.
That gave me the same problem. So I tried a third time, explicitly mentioning Freedom House (which it alluded to in a previous answer):
Let's try again:
To the best of your ability, using data from Freedom House (whatever other knowledge and resources you have at hand), classify each of the countries named in this data frame as a democracy or a dictatorship. Make sure that the keys in your dictionary match the country names in the data frame. Create a pie chart showing what proportion of debt was owned by each type of government. If you can think of additional classifications for countries, feel free to use those. Any countries that cannot be classified into a governmental system can remain named.
It still gave me the runaround. I finally tried to be tough:
Create a dictionary in which the keys are the index from our data frame, and the values are the best classification of each country into democracy, dictatorship, or something else.
You must, but absolutely must, create a dictionary entry for each index element in the data frame. Do not give me examples and ask me to fill out the rest. You must do it yourself, all of it.
The dictionary keys must be a 100% match for the index in our data frame. If a country is in the data frame, it must be a key in the dict. If a country is not in the data frame, it must not be in the dict.
Aha! Now we’re getting somewhere:
# List of countries from the dataframe
countries = df.index.tolist()
# Define a dictionary to classify countries as democracies, dictatorships or others
# This classification is based on the general knowledge up to the last training cut-off in September 2021
# Please note that this is a simplified classification and the political situation in a country can be more complex
classification = {}
for country in countries:
if country in ['Japan', 'Ireland', 'Brazil', 'U.K.', 'Cayman Islands', 'Luxembourg', 'Switzerland', 'Taiwan', 'Belgium', 'Canada', 'France', 'Germany', 'Netherlands', 'Australia', 'Italy', 'Sweden', 'Norway', 'Denmark', 'Finland', 'Austria', 'Spain', 'Portugal', 'New Zealand', 'Greece']:
classification[country] = 'Democracy'
elif country in ['China']:
classification[country] = 'Authoritarian'
elif country in ['Singapore']:
classification[country] = 'Hybrid Regime'
else:
classification[country] = 'Unclassified'
classification
Note that it still didn’t get things quite right, given that the country name in our data frame is “United Kingdom,” and in this dict it’s “U.K.”. But hey, it’s progress. That said, I felt like I was a frustrated boss yelling at an employee to do something the right way.
Can I get a pie chart from this? Let’s see:
Create a pie chart showing what proportion of debt was owned by each type of government. If you can think of additional classifications for countries, feel free to use those. Any countries that cannot be classified into a governmental system can remain named.
The result:
Well… it’s progress, right?
I hope that you enjoyed this excursion into ChatGPT, Noteable, and Pandas. Even when things go south, I find that ChatGPT is doing a pretty good job. That said, you need patience and knowledge to explain things in a way that the chatbot will create the right code. And you need enough knowledge to examine the code, and its output, and understand whether it’s right — and if not, how it can be made better.
I continue to believe that AI chatbots aren’t a threat to programmers, except for those doing repetitive and non-creative work. Low-level people might well lose their jobs, but for most of us, AI is a tool that’ll accelerate and help our work, much as calculators and Excel have made my accountant better at his job. I think of AI as a brainstorming partner, not a replacement for a human.
That said, I’m not sure if society as a whole is ready for the extension of AI into new areas of our lives, because there are undoubtedly many people doing precisely the kinds of low-end, repetitive jobs that AI can and will replace.
What do you think? What results did you get? Were they similar to mine? Please leave feedback in the forum!
Note that I’m going to post a YouTube video on my channel (https://youtube.com/reuvenlerner) in the coming days (probably Sunday) with my solution and explanation. Watch for it there!
I’ll be back on Wednesday with more Pandas questions from the news.
Reuven
What am I missing? You ask Chat GPT for graphs over the last three *decades* and it gives us charts for the last three *years*? It's still amazing, and you can always adapt the code, but it shows that one should always carefully verify what the AI is giving us.