> ## 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 #40: Sovereign Bonds (solutions)
- URL: https://www.bambooweekly.com/bw-40-sovereign-bonds-solution/
- Published: 2023-11-16T18:57:29.000Z
- Updated: 2026-08-23T09:37:02.000Z
- Description: Get better at: APIs, scraping, regular expressions, cleaning, outliers, and grouping.
- Author: Reuven M. Lerner
- Tags: api, web-scraping, regular-expressions, cleaning, outlier-detection, grouping

Governments need money: They have to pay salaries, build infrastructure, invest in research, pay their military, and so forth. In theory, they could use income from taxes to pay for such things. But in reality, revenues are often not quite enough to cover their expenses.

If you’re short on cash, then you can always take out a loan, right? Yes, but it’s often easier and cheaper to issue a bond — in the case of a country, what’s known as a “sovereign bond.” You say to the world, “Whoever wants to lend us $1,000, we’ll give it back to you in 10 years, and will pay you some annual interest, besides.”

A bond typically won’t give you a huge return on your investment — but it’s also very likely to be paid off, giving you a guaranteed return, but one that’s smaller than you might get with stocks. After all, a government isn’t going to stiff you, right?

Actually, some governments might well stiff you, what’s known as “defaulting.” To offset that chance, a government would have to give you a highest interest rate. Countries that have defaulted in the past, or whose political-economic situations aren’t looking too good, have to pay more interest. How much more? It depends on a number of factors, including the interest rate paid by their central bank, along with reports from various ratings agencies. A better rating means that a country can get away with paying less interest to its bondholders.

### Data and six questions

This week, we looked at some data about the sovereign bond market, from the site "World Government Bonds" ([https://www.worldgovernmentbonds.com/](https://www.worldgovernmentbonds.com/?ref=bambooweekly.com)). This site tries to summarize the latest data about various countries’ bonds.

Here are my six questions, along with my detailed solutions; a link to my Jupyter notebook is at the bottom of this post.

### Retrieve the per-country bond ratings from [https://www.worldgovernmentbonds.com/world-credit-ratings/](https://www.worldgovernmentbonds.com/world-credit-ratings/?ref=bambooweekly.com) into a data frame, in which the country name is the index, and the columns consist of the three main ratings agencies: S&P, Moody's, and Fitch.

Before doing anything else, I’m going to load Pandas into memory:

```
import pandas as pd
```

With that in place, what can I do next? There isn’t a CSV file that I can download, but the data does appear to be in an HTML table. My first thought was thus to use the “[read\_html](https://www.bambooweekly.com/pandas-read-html/)” method to scrape the page and return a list of data frames, one for each table it finds. I thus wrote:

```
ratings_url = 'https://www.worldgovernmentbonds.com/world-credit-ratings/'
df = pd.read_html(ratings_url)
```

Unfortunately, this failed. The reason? I got a “Forbidden” error, aka HTTP error 403, from the site. It would seem that the site doesn’t want people scraping its data, so anyone trying to read it in a programmatic way will be turned away.

Rather than give up, I decided to use another Python package, “[requests](https://docs.python-requests.org/en/latest/index.html?ref=bambooweekly.com)”. The requests package provides us with a complete HTTP client, meaning that it’s basically a full-fledged browser inside of Python. Maybe requests wouldn’t set off any alarms? Let’s see:

```
import requests
r = requests.get(ratings_url)
```

It worked! We got the content of the site back, in a response object. But how can we go from that object to parsing the HTML?

It turns out that read\_html can be passed a URL, but it can also be passed a Python file-like object. Meaning, if I can somehow get the content into a file-like object (i.e., not necessarily a file, but something that implements the same API as files), then we will be able to pass the data to read\_html.

The first step in doing so is to get the HTML content out of the response object. That’s easy; we can use “r.content”. But that content isn’t even returned as a string; rather, it’s returned as a “[bytes](https://docs.python.org/3/library/stdtypes.html?highlight=bytes&ref=bambooweekly.com#bytes)” object. Fortunately, we can turn this bytes object into a string with the “decode” method.

But how can we turn a string into a file-like object? We can use the “StringIO” class, which comes with Python’s standard library and creates an in-memory object implementing the same API as a file.

In other words:

- We’ll get the response from the URL
- We’ll retrieve the bytes from that response
- We’ll turn the bytes into a Python string
- We’ll pass the Python string to a StringIO
- We’ll pass that StringIO to read\_html
- read\_html returns a list of data frames
- We’re interested in the first (and only) data frame, at index 0
- We retrieve the data frame at index 0, and assign it to ratings\_df

Here’s what it looks like in code:

```
from io import StringIO
ratings_df = pd.read_html(StringIO(r.content.decode()))[0]
```

Sure enough, ratings\_df now contains the data from that site. Not too shabby! But I told you that we don’t need all of the columns, and that we should turn the countries into the index. So let’s do that:

```
ratings_df.columns = "Flag Country S&P Moody's Fitch DBRS".split()
ratings_df = ratings_df.drop(['Flag', 'DBRS'], axis='columns')
ratings_df = ratings_df.set_index('Country')
```

On the first line, I replaced the column names that came from the Web page with shorter, easier-to-write names. I then removed two of them, using the “[drop](https://www.bambooweekly.com/pandas-drop/)” method. Notice that I can pass a list of strings, rather than a single string, in order to drop multiple columns. Also, I have to specify that I’m dropping columns, rather than the default of rows.

Finally, I invoke “[set\_index](https://www.bambooweekly.com/pandas-set-index/)” to use one of the existing column (“Country”) as the data frame’s new index.

In both the “drop” and “set\_index” methods, Pandas returns a new data frame based on the original one. I could have used method chaining here, but decided that I would instead use plain ol’ assignment, because of the need to set the names of the columns.

At the end of all of this code, I have a data frame with three columns (one for each of the three ratings agencies), and whose index contains country names.

![](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-2f0564e705-1ae3-468e-913a-636e5d1e3df0_982x1056.png)

### Which countries' bond ratings are all of the highest grade, namely AAA or Aaa?

Let’s say that you want the greatest assurance that your money will be safe, and you’re willing to take something of a hit on the yield (i.e., the interest you’ll earn). A good choice would be to invest in one of the countries that has gotten the highest rating (AAA or Aaa) from the bond ratings agencies.

So, what countries would those be?

There are a few ways that we could attack this. I decided to use the sneaky trick of treating True as 1 and False as 0\. Normally, I tell people in my classes not to depend on this, since we’re fortunate to have real boolean types in Python — but in this case, ti really comes in handy.

My goal is to replace every AAA or Aaa rating with True, and every other rating with False. With that in place, I’ll then be able to count the number of True values in each row, and keep only those rows equal to 3.

First, I’ll need to find those rows in which every value is either AAA or Aaa. I decided to use “[isin](https://www.bambooweekly.com/pandas-isin/)”, a data frame method:

```
(
    ratings_df
    .isin(['AAA', 'Aaa'])
)
```

This returns a data frame whose index and columns are identical to ratings\_df, but whose values are all True or False. Next, we’ll sum the data frame along its columns, giving us a series whose values are all integers — the result of summing True (1) and False (0) across each row:

```
(
    ratings_df
    .isin(['AAA', 'Aaa'])
    .sum(axis='columns')
)
```

This is the series that we get back:

```
Country
Argentina         0
Australia         3
Austria           0
Bahrain           0
Bangladesh        0
                 ..
United Kingdom    0
United States     1
Venezuela         0
Vietnam           0
Zambia            0
Length: 74, dtype: int64
```

Next, we want to keep only those rows that have a value of 3\. For this, we’ll use “.[loc](https://www.bambooweekly.com/pandas-loc/)” along with a lambda, indicating that we only want those rows with a value of 3:

```
(
    ratings_df
    .isin(['AAA', 'Aaa'])
    .sum(axis='columns')
    .loc[lambda s_: s_ == 3]
)
```

Here’s what we get back:

```
Country
Australia      3
Denmark        3
Germany        3
Netherlands    3
Norway         3
Singapore      3
Sweden         3
Switzerland    3
dtype: int64
```

We could stop there, but I thought it would be useful for us to just grab the country names, which we can do with “[index](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.index.html?ref=bambooweekly.com)”:

```
(
    ratings_df
    .isin(['AAA', 'Aaa'])
    .sum(axis='columns')
    .loc[lambda s_: s_ == 3]
    .index
)
```

The result is as follows:

```
Index(['Australia', 'Denmark', 'Germany', 'Netherlands', 'Norway', 'Singapore',
       'Sweden', 'Switzerland'],
      dtype='object', name='Country')
```

We now know where it’s safest to invest our money.

But wait, where is the United States? As you might have heard, the US nearly hit its “debt ceiling” several times in the last few years. If it were to hit that ceiling, then it wouldn’t be able to borrow any more money. Which would mean not paying bondholders. Which, given the number of bonds out there, would be pretty catastrophically bad. The debt ceiling has always been raised, but the US has come perilously close to failing to do so. And so, while treasury bonds are still considered to be a very safe investment, and thus have low interest rates… but the US is no longer among the best-of-the-best in terms of ratings.

### Which countries' bond ratings are investment grade? (This includes any rating starting with A, any rating with three Bs, and ratings starting with Baa.)

As I’ve already written above, there’s a strong correlation between a bond’s yield (i.e., its interest rate) and the likelihood of default. Countries with high ratings can pay less, in part because there is more demand for their bonds, which are seen as safer.

Bonds with high ratings are known as “investment grade,” because investment firms feel confident that they will be safe investments. In this question, I asked you to show which countries have investment-grade bonds.

This question is similar to the previous one, but now we’re looking for a variety of ratings. In theory, we could just put all of those ratings into a list and use “isin”, as above. But I thought it would be more interesting to look for patterns of text. Which means: Regular expressions!

(If you haven’t yet learned regular expressions, please check out my free e-mail tutorial at [RegexpCrashCourse.com](https://regexpcrashcourse.com/?ref=bambooweekly.com). I promise, they aren’t that hard!)

My idea is as follows:

- Replace any bond rating starting with A, with three Bs in it, or starting with Baa with the integer 1.
- Replace any remaining bond rating with the integer 0.
- Replace any bond rating listed as “-” (a dash) with 0.
- Sum the columns, as before
- Any country with a value of 3 has investment-grade bonds.

The trickiest part is the first one, namely finding bond ratings starting with A, with three Bs, or starting with Baa and replacing them with 1\. I can do that with the “[replace](https://www.bambooweekly.com/pandas-replace/)” method, passing a list of strings (regular expressions) that I want to look for, the value 1 that I want to replace them with, and regex=True to indicate that I see these strings as regular expressions, not literal strings:

```
(
    ratings_df
    .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True)
)
```

When we use ^ in a regular expression, it means that the pattern needs to appear at the start of the string. So:

- ^A means “anything starting with A”
- ^B{3} means “anything starting with BBB”
- ^Baa means “anything starting with “Baa”

We have now replaced those with 1s. The rest of the ratings should be replaced with 0\. I can do that with another call to replace, this time looking for any value that contains only letters. I use the character class \[A-Za-z\], which means “one or more capital or lowercase letters”:

```
(
    ratings_df
    .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True)
    .replace('[A-Za-z]', 0, regex=True)
)
```

This might appear to be the end of our need to replace values, but it turns out that there are also dashes, which I guess we could/should have turned into NaN when we loaded the data, but we didn’t. I’ll turn those into zeroes, also:

```
(
    ratings_df
    .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True)
    .replace('[A-Za-z]', 0, regex=True)
    .replace('-', 0)
)
```

We have now successfully replaced all of the bond ratings with 1s and 0s. Which means that we can now do what we did before, namely sum the numbers across the columns, giving us a number between 0 and 3 for each row. Then we can filter them out with loc and a lambda, keeping only those with 3:

```
(
    ratings_df
    .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True)
    .replace('[A-Za-z]', 0, regex=True)
    .replace('-', 0)
    .sum(axis='columns')
    .loc[lambda s_: s_ == 3]
)
```

The result is a fairly long list:

```
Country
Australia         3.0
Austria           3.0
Belgium           3.0
Bulgaria          3.0
Canada            3.0
Chile             3.0
China             3.0
Croatia           3.0
Cyprus            3.0
Czech Republic    3.0
Denmark           3.0
Finland           3.0
France            3.0
Germany           3.0
Hong Kong         3.0
Hungary           3.0
Iceland           3.0
India             3.0
Indonesia         3.0
Ireland           3.0
Israel            3.0
Italy             3.0
Japan             3.0
Kazakhstan        3.0
Latvia            3.0
Lithuania         3.0
Malaysia          3.0
Malta             3.0
Mexico            3.0
Netherlands       3.0
New Zealand       3.0
Norway            3.0
Perù              3.0
Philippines       3.0
Poland            3.0
Portugal          3.0
Qatar             3.0
Romania           3.0
Singapore         3.0
Slovakia          3.0
Slovenia          3.0
South Korea       3.0
Spain             3.0
Sweden            3.0
Switzerland       3.0
Taiwan            3.0
Thailand          3.0
United Kingdom    3.0
United States     3.0
dtype: float64
```

Now, this doesn’t mean that all of these countries’ bonds have the same yield, or are equally risky. Not to pick on Kazakhstan or Thailand here, but they’re not in exactly the same economic situation as the US or Switzerland. However, all of these countries’ sovereign bonds are considered investment grade. I remember reading that this designation is actually quite important, because certain investment funds have, as part of their charters, that they may only invest in investment-grade bonds.

### Create a data frame based on the page of central bank rates ([https://www.worldgovernmentbonds.com/central-bank-rates/](https://www.worldgovernmentbonds.com/central-bank-rates/?ref=bambooweekly.com)) where the country is the index, and there are two float columns, Rate and Variation.

Next, I asked you to create another data frame, this time based on the central bank rates listed on the World Government Bonds site. I asked you to keep only two columns (rate and variation), to make sure that they’re floats, and to have the country name (again) as the index.

Let’s start with very similar code to what we used to scrape and create our first data frame:

```
rates_url = 'https://www.worldgovernmentbonds.com/central-bank-rates/'
r = requests.get(rates_url)
rates_df = pd.read_html(StringIO(r.content.decode()))[0]
rates_df.columns = "Flag Country Rate Variation Period".split()
rates_df = rates_df.drop(['Flag', 'Period'], axis='columns')
rates_df = rates_df.set_index('Country')
```

We download the page with the central bank rates, create a data frame, rename the columns, drop the unneeded columns, and set the “Country” column to be the index. So far, so good.

But when I try to turn the remaining columns into floats, I get an error. That’s because the “Rate” column includes the string “ %” after each number. And the “Variation” column contains the string “ bp” after each number. (It might be obvious that % will be in a rate column, but what is “bp”? It stands for “basis points,” and it’s a common measure in the financial world, where 1 basis point is 0.01\. So 50 basis points is 0.5.)

In order to turn our columns into floats, we’ll first need to get rid of the strings that are preventing the conversion from taking place. An easy way to do this is with the “[removesuffix](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.removesuffix.html?ref=bambooweekly.com)” method. As as general rule, you don’t want to use for loops in Pandas. Instead, we can ask Pandas to apply the “removesuffix” method to each element in a column via the “str” accessor. We get back a new series in return, which we can assign back to the original column, replacing it. But before we’ve done that, we’ll use “astype” to get a new float column:

```
rates_df['Rate'] = rates_df['Rate'].str.removesuffix('%').astype('float')

rates_df['Variation'] = rates_df['Variation'].str.removesuffix('bp').astype('float') / 100
```

Note that we don’t have to remove the space character, because the “float” conversion ignores leading and trailing whitespace. The result is a data frame with 74 rows and two columns, along with countries in the index:

![](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-2fec9e1fec-47cc-4f5d-bdd9-aac65f617c02_894x1378.png)

### Calculate the mean and median central-bank interest rates, separated by countries that are and aren't investment grade.

I then asked you to calculate the mean and median interest rates — not for all of the countries, but per group of countries. Once again, I asked you to divide them into those that are interest grade and those that aren’t.

Solving this problem means using both of the data frames that we’ve created so far:

- We’ll use the rating\_df to identify which countries have investment-grade bonds, and which don’t.
- We’ll use the new data frame, with the central-bank rates, for our calculations of mean and median.

Let’s start with a variation of the query that we used earlier, to determine which countries have investment-grade bonds. Before, we just wanted to know which were investment grade, keeping only those rows with a value of 3\. Now, though, we want to separate investment-grade countries from those that aren’t, which means keeping all rows around, but with an indication of which group each is in.

I decided to use “[assign](https://www.bambooweekly.com/pandas-assign/)” for this. After doing all of the regexp manipulations we previously saw, I then used a lambda to determine whether the sum of a row’s values added up to 3\. If so, then the IG column was set to True, otherwise it was set to False:

```
numeric_ratings_df = (
    ratings_df
    .replace(to_replace=['^A', '^B{3}', '^Baa'], value=1, regex=True)
    .replace('[A-Za-z]', 0, regex=True)
    .replace('-', 0)
    .assign(IG=lambda df_: df_.sum(axis='columns') == 3)
)
```

I assigned the resulting data frame to numeric\_ratings\_df, producing the following data frame:

![](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-2f277774dc-7b0e-44d6-821b-21b95e13d998_1098x1376.png)

With that in place, we can now perform the real query: I’ll [join](https://www.bambooweekly.com/pandas-join/) together the central-bank rates (rates\_df) with the numeric ratings (numeric\_ratings\_df). Joining requires that both data frames share an index, which is definitely the case here. The resulting data frame contains one row per country, along with all of the rating information and also their interest rates.

I then want to calculate both mean and median. Normally, I can only calculate one of these at a time, but I can use the “[agg](https://www.bambooweekly.com/pandas-agg/)” method, and then pass a list of what I want to calculate.

But wait: Running an aggregate method on the entire result will give me one value for the data frame. I want two values, one for each value of the IG column. To do this, I’ll need to use “[groupby](https://www.bambooweekly.com/pandas-groupby/)”:

```
(
    rates_df
    .join(numeric_ratings_df)
    .groupby('IG')['Rate']
    .agg(['mean', 'median'])
)
```

Here’s the result that I get:

![](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-2f193e25d4-a8b5-4ed3-aa20-069c40e4792a_694x416.png)

In other words, non-investment grade sovereign bonds have a mean of 17 percent, and a median of 10 percent. That’s right: If you’re willing to invest in non-investment grade sovereign bonds, then you can earn a lot of interest! Assuming that the bond is fully paid in the end. Notice how much higher the mean is than the median; this implies that there are one or two large values pulling the mean up.

On the investment-grade side, we see mean and median numbers that are fairly similar to one another, indicating that when countries have investment-grade bond status, they’re not going to be sky high or rock bottom, pulling the mean super high or super low.

### What countries' interest rates are more than one standard deviation above the mean? What do bond ratings agencies say about them?

Finally, let’s see what countries are the biggest outliers in this data set. I asked you to find which countries’ interest rates are at least one standard deviation above the mean. A standard deviation is a measure of the wobbliness of the value; in a normal distribution of values, a standard deviation of 0 means that all of the values are the same, whereas 10 would mean that most values are 10 above or 10 below the mean.

First, we have to calculate the threshold — what is the mean rate, plus one standard deviation? We can do it this way:

```
rates_df['Rate'].mean() + rates_df['Rate'].std()
```

Here, we’re calculating “[mean](https://www.bambooweekly.com/pandas-mean/)” on the Rates column, and then adding to it the value of the [standard deviation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.std.html?ref=bambooweekly.com). Any row whose rate is higher than that should be kept, perhaps using loc:

```
.loc[lambda df_: df_['Rate'] > rates_df['Rate'].mean() + rates_df['Rate'].std()]
```

But wait — on what data frame should we run this loc query? It’ll need to be the result of joining rates\_df and ratings\_df (not numeric\_ratings\_df), so that we can perform this comparison and also find out what ratings agencies said:

```
(
    rates_df
    .join(ratings_df)
    .loc[lambda df_: df_['Rate'] > rates_df['Rate'].mean() + rates_df['Rate'].std()]
)
```

The 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-2f15b26983-a909-4c0b-83d6-80ce312abc39_1312x504.png)

To anyone who follows economic news, none of these are particular surprises:

- Argentina has defaulted on its debt multiple times in the last century, and is everyone’s example of a country whose bonds will never be paid off. It is currently experiencing awful inflation, which it’s trying to curb with 133% interest rates, a rate that was recently hiked by 15%. (Ouch!) Argentina has a presidential run-off coming up, and the economy, not surprisingly, is a big topic there.
- Turkey’s president Erdogan saw that his country was having bad inflation, and pressed the central government to lower interest rates, even though that was almost guaranteed to crash the currency and increase inflation. Then he won re-election, and the central bank started to slam on the brakes, with interest rates now at 35%.
- Venezuela defaulted on some of its bonds in 2017, leading ratings agencies to abandon it (hence the NaN) or give it super bad ratings. Their economy has been in generally bad shape for a while, but the US just announced that they will start easing restrictions on trade with Venezuela, which will presumably make things a bit better.

There you have it for this week! My Jupyter notebook is at [https://drive.google.com/file/d/1ItYZ-7WPEyoDKJEIMiZdJgr0tcQFiMWh/view?usp=sharing](https://drive.google.com/file/d/1ItYZ-7WPEyoDKJEIMiZdJgr0tcQFiMWh/view?usp=sharing&ref=bambooweekly.com) . Please share comments, questions, and suggestions in the discussion thread.

I’ll be back on Wednesday with another bunch of Pandas questions based on current events.

Reuven