> ## 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 #57: International arms trade (solutions) - URL: https://www.bambooweekly.com/bw-57-international-arms-trade-solution/ - Published: 2024-03-14T16:01:12.000Z - Updated: 2026-08-23T09:36:53.000Z - Description: Get better at: CSV files, grouping, stack and unstack, window functions, and plotting - Author: Reuven M. Lerner - Tags: csv, grouping, stack-unstack, window-functions, plotting This week, we looked at arms-trade data released earlier this week by the Stockholm International Peace Research Institute (SIPRI, [https://www.sipri.org](https://www.sipri.org/?ref=bambooweekly.com)). This was reported in a number of outlets, including Politico, whose article ([https://www.politico.eu/article/france-overtake-russia-world-weapons-exporter/](https://www.politico.eu/article/france-overtake-russia-world-weapons-exporter/?ref=bambooweekly.com)) noted that as of 2023, France is the world’s second-largest arms exporter, replacing Russia, whose arms exports went down substantially. SIPRI records every sale of military equipment — tracking the seller, the buyer, when the order was made, how much it was for, and when it was delivered. The sellers are listed as countries, and the buyers are either countries or non-state actors (e.g., Hamas). SIPRI’s latest data is current through the end of 2023; to be honest, I’m impressed that they’re able to gather this sort of information at all, given that governments are typically less than open about what they’re buying, and from whom. I thought that it would be interesting to find out who is selling, who is buying, and what changes we have seen over the last number of years. ### Data and six questions This week's data comes from SIPRI's arms transfer database: [https://armstransfers.sipri.org/ArmsTransfer/](https://armstransfers.sipri.org/ArmsTransfer/?ref=bambooweekly.com) I asked you to download a CSV file with information about all arms “transfers,” as they refer to them: [https://armstransfers.sipri.org/ArmsTransfer/TransferRegister](https://armstransfers.sipri.org/ArmsTransfer/TransferRegister?ref=bambooweekly.com) On this page, ask for information from 2000 through 2023\. The sorting doesn't matter, but we *do* want deliveries broken down by year (so click on that checkbox). Then click on the red "Download as CSV" button. This will take a little while to run, but within a minute or so, you'll get a file called "trade-register.csv" downloaded to your computer. That'll be our data set for this week's exercises. A data dictionary describing the methodology used to compiled the database is at [https://www.sipri.org/databases/armstransfers](https://www.sipri.org/databases/armstransfers?ref=bambooweekly.com) As I noted yesterday, the database records arms sales using TIV, "trend-indicator value," a SIPRI-specific amount. You can compare TIV across years, countries, and products, but you can't directly convert it to dollars or any other currency. This week, I have six tasks and questions for you. (And yes, I indicated yesterday that I had seven. Whoops!) Here are my detailed solutions; as always, the Jupyter notebook I used to solve these problems is at the bottom of this edition. ### Create a data frame from the "trade-register.csv" file, keeping the column headers. We can start, as always, by loading Pandas: ``` import pandas as pd ``` Assuming that the file is called “trade-register.csv”, we should be able to load it into a data frame with “[read\_csv](https://www.bambooweekly.com/pandas-read-csv/)”, a common Pandas method: ``` filename = 'trade-register.csv' df = pd.read_csv(filename) ``` However, this doesn’t work, giving us an error: ``` ParserError: Error tokenizing data. C error: Expected 1 fields in line 12, saw 17 ``` Here, Pandas is complaining that it expected a certain number of fields, but got a different number. That’s because Pandas looks at the first line of a CSV file, and uses that to figure out how many fields there should be. If any subsequent line has the wrong number, we get the above ParserError exception. The problem is that the SIPRI file starts with several explanatory and copyright lines: ``` Transfers of major conventional arms from All countries to All countries. Deals with deliveries or orders made for year range '2000' to '2023' A '?' in a column indicates uncertain data. The ?Number delivered? and the ?Year(s) of deliveries? refer only to deliveries in the selected year(s). An empty field for ?Number ordered? indicates that data is not yet available. SIPRI trend-indicator values (TIVs) are in millions. An empty field for ?SIPRI TIV for total order? indicates that data (on the number ordered and/or the TIV per unit) is not available. A '0' for ?SIPRI TIV of delivered weapons? indicates that the volume of deliveries is between 0 and 0.5 million SIPRI TIV; and an empty field indicates that no deliveries have been identified. Figures may not add up to stated totals due to the conventions of rounding. For the method used for the SIPRI TIV and explanations of the conventions; abbreviations and acronyms see . Source: SIPRI Arms Transfers Database (c) SIPRI. Data generated: 13 Mar 2024 4:39:22 PM ``` This is all great, but when Pandas reads the file, it assumes that there will be only one field per line, because there aren’t any commas on the first line. We can fix this by telling it to ignore the first few lines, and that the headers describing the columns are on a later line in the file. In this case, the headers start on line 12, which Pandas would refer to as line 11 (because of 0-based indexing). However, I found that I actually needed to pass header=10 in order for the file to be read correctly: ``` df = pd.read_csv(filename, header=10) ``` That worked just fine, and gave me a data frame with 20,480 rows and 17 columns. We could have gotten a smaller data frame if we had asked for each of the orders to be listed in a single row, rather than broken up by delivery. However, this gave us results that were different than were in the SIPRI summary. I took that to mean that their analysis used the broken-down delivery dates, and then recorded when the arms were delivered, rather than when they were ordered. In this case, “read\_csv” worked just fine. But when I was researching the topic for Bamboo Weekly, I found that several of SIPRI’s files weren’t readable by “read\_csv”. I got UnicodeDecodeError exceptions when trying to read the files — an indication that the files were written using something other than Unicode’s UTF-8 encoding, which is what Pandas (and Python) expect by default. To read such a file, we can pass the “encoding” keyword argument to “read\_csv”: ``` df = pd.read_csv(filename, header=10, encoding='Latin-1') ``` Again, that wasn’t necessary with this file, but you will undoubtedly encounter this problem in the future. But wait a second: How did I know that the problematic file was encoded in Latin-1? Is there any automated way to figure it out? The answer is “yes,” to at least some degree: The “file” command on MacOS and Linux, when run in the terminal against a file, will make a pretty good guess regarding its encoding. For example: ``` ❯ file ~/Downloads/trade-register.csv /Users/reuven/Downloads/trade-register.csv: ASCII text ``` “ASCII text” is the most generic encoding available, but it’s a subset of both UTF-8 and Latin-1\. So I could actually use either encoding, including the default, and read it fine. But one of the other SIPRI files looked like this: ``` ❯ file trade-register.csv trade-register.csv: ISO-8859 text, with very long lines (312) ``` Here, you can see that it’s guessing at an encoding of ISO-8859\. That covers many different languages with sub-encodings such as ISO-8859-1 (Western Europe) and ISO-8859-8 (English + Hebrew). It won’t always be able to tell you which language or sub-encoding the file used, but it often can. I also, in preparing this article, discovered the “chardet” utility, which uses heuristics to determine the character set and encoding: [https://pypi.org/project/chardet/](https://pypi.org/project/chardet/?ref=bambooweekly.com) ![](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-2fdc5a4737-5695-40ed-9e34-a2e53c46f434_1024x1024.jpg) Arms traders in simpler times ### Which five countries exported the greatest total TIV in 2023? (Use the "TIV delivery values" column.) Another way to phrase this question is: Which five countries are the greatest arms exporters, as of 2023? I already mentioned the Politico article in which they pointed to the fact that France is now #2, pushing Russia out of that spot. What are the other three, though? We’ll start by using “[loc](https://www.bambooweekly.com/pandas-loc/)” with a “lambda” expression to select only those rows from the file in which “delivery year” was 2023. ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] ) ``` Remember that this works because “lambda” returns a function object. That function is then applied to the data frame (which we assign to the local parameter “df\_”). The function’s result is a boolean series whose value is “True” wherever “Delivery year” is equal to 2023. When you put a boolean series inside of “loc”, you get back only those elements of the original data frame that match up with a “True” value. This is known as a “mask index.” Now that we’re looking only at values from 2023, we can use a “[groupby](https://www.bambooweekly.com/pandas-groupby/)” to calculate the total amount delivered by each supplier: ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .groupby('Supplier')['TIV delivery values'].sum() ) ``` This returns a “groupby” object, on which we can run one or more aggregation methods. Remember that in this “groupby”, we will get a result for each unique value in the “Supplier” column. And we’ll calculate the total “TIV delivery values” for each supplier. We get back a series whose index contains supplier (country) names, and whose values are the total TIV for each supplier in 2023\. We can get the five largest values (along with their indexes) with the “[nlargest](https://www.bambooweekly.com/pandas-nlargest/)” method: ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .groupby('Supplier')['TIV delivery values'].sum() .nlargest() ) ``` The result: ``` Supplier United States 11287.01 Germany 3287.07 China 2431.71 France 2012.17 Italy 1436.81 Name: TIV delivery values, dtype: float64 ``` This doesn’t quite match up with the story from Politico, which said that France is now #2 and that Russia is #3\. I’m guessing that I looked at the wrong columns, or calculated it incorrectly. But we see that the US is, by far, the largest supplier of arms, followed distantly by Germany, China, France, and Italy. Russia, by this measure, comes it at #6. ### Which five countries had the greatest increase in exports from 2022 to 2023? Which had the greatest decrease? The Politico article indicated that the Ukraine war has shaken things up quite a bit, for both Russia and Western countries. How much have things changed? What countries have been exporting more, and which have been exporting less? We can start by looking only at the years 2022 and 2023\. We can do that by again applying a “lambda” inside of “loc”. But this time, we can’t use “==”, because we need two different years. I like to use the “[isin](https://www.bambooweekly.com/pandas-isin/)” method when we’re searching for a limited range: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] ) ``` With that in hand, I’ll run a “groupby”. This time, I’ll group by two different columns — first by delivery year, and then by supplier. That’s because I want to differentiate between the different delivery years. I’ll still run “sum” on the “TIV delivery values” column: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() ) ``` The result is a series with a two-level multi-index, the outer level being the delivery year, and the inner level being the supplying country. That’s nice, but I want to compare the two years. To do that, I’ll take the years (2022 and 2023) and turn them into columns, using the “[unstack](https://www.bambooweekly.com/pandas-unstack/)” method: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=0) ) ``` We now have a data frame in which the index contains countries, and the columns (2022 and 2023) show the total arms exports for each country. There are a few countries with NaN values for one or both years; we can get rid of those with “[dropna](https://www.bambooweekly.com/pandas-dropna/)”: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=0) .dropna() ) ``` We’re now (finally!) ready to perform our calculation. We can use “pct\_change” to calculate the percentage change from one row to the next. However, we don’t want to compare rows (i.e., different countries); we want to compare one year to the next! We can do that by telling it to use axis=“columns”: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=0) .dropna() .pct_change(axis='columns') ) ``` The result is a data frame whose 2022 column is all NaN values, and whose 2023 column indicates the percentage change (rise or fall) from the 2022 values. We can thus grab the 2023 column, and run both the “[nsmallest](https://www.bambooweekly.com/pandas-nsmallest/)” and “[nlargest](https://www.bambooweekly.com/pandas-nlargest/)” aggregation functions, passing their names in a list to “[agg](https://www.bambooweekly.com/pandas-agg/)”: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=0) .dropna() .pct_change(axis='columns') [2023] .agg(['nsmallest', 'nlargest']) ) ``` The result: ``` nsmallest nlargest Supplier Slovenia -0.936508 NaN Egypt -0.888889 NaN Jordan -0.790234 NaN Belarus -0.741824 NaN Belgium -0.720527 NaN Croatia NaN 9.097436 Brazil NaN 5.420927 Pakistan NaN 5.333333 Portugal NaN 4.101010 India NaN 3.941176 ``` Now, this isn’t wrong per se. But a 93 percent drop in Slovenian arms exports might not mean much, if they weren’t exporting much to begin with. By contrast, the increases do make sense. I’m thus going to modify our query a bit, keeping only those rows where the 2022 total was at least 500: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2022, 2023])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=0) .dropna() .loc[lambda df_: df_[2022] > 500] .pct_change(axis='columns') [2023] .agg(['nsmallest', 'nlargest']) ) ``` The results: ``` nsmallest nlargest Supplier Russia -0.512616 NaN France -0.384211 NaN United Kingdom -0.277096 NaN United States -0.276104 NaN Italy -0.162742 NaN Germany NaN 1.219404 Israel NaN 0.331418 China NaN 0.167609 Turkiye NaN 0.138883 Spain NaN -0.031222 ``` We see that Russia indeed declined by 50 percent, as the Politico article described. But we see that all of the big arms-supplying companies declined — France, the UK, and the US. I would guess that the US decline has to do with the political gridlock over trying to supply arms to Ukraine, but I’m not sure about other countries. We can, meanwhile, see a huge uptick in arms exports from Germany and Israel, and moderate increases from China and Turkey (aka Turkiye). We could, using the data set, find out to whom these countries were exporting — but I’ll leave that as an exercise to the reader. ### What happened to US, UK, French, and Russian exports in 2020 through 2023? Draw a line plot showing the total TIV for each country, in each of those years. How would you interpret this? Let’s start by keeping only the four years that are of interest to us, again using “isin”. Truth be told, I thought about using “range” here, but four years is on the borderline for me: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2020, 2021, 2022, 2023])] ) ``` Now that we’ve limited the years, let’s also limit the supplying countries. We can do this with a second, similar call to “loc” and “lambda”. Notice that because we are performing our test on “df\_”, the function’s local variable, we operate not on the original data frame, but rather on what we got back after the first call to “loc”. Each method in the chain operates on the output from the previous method: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2020, 2021, 2022, 2023])] .loc[lambda df_: df_['Supplier'].isin(['United States', 'Russia', 'France', 'United Kingdom'])] ) ``` Now we want to perform our grouping, again creating a series with a two-level multi-index: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2020, 2021, 2022, 2023])] .loc[lambda df_: df_['Supplier'].isin(['United States', 'Russia', 'France', 'United Kingdom'])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() ) ``` We can then use “unstack” to turn our multi-indexed series into a data frame. We previously used “unstack” to turn level 0 (i.e., the outer part of our multi-index) into column names. But here, we actually want level 1 (i.e., the inner part of the multi-index) to be the columns. We thus call “unstack(level=1)”: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2020, 2021, 2022, 2023])] .loc[lambda df_: df_['Supplier'].isin(['United States', 'Russia', 'France', 'United Kingdom'])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=1) ) ``` Finally, we can invoke “plot.line” to get a line plot for these years and these countries: ``` ( df .loc[lambda df_: df_['Delivery year'].isin([2020, 2021, 2022, 2023])] .loc[lambda df_: df_['Supplier'].isin(['United States', 'Russia', 'France', 'United Kingdom'])] .groupby(['Delivery year', 'Supplier'])['TIV delivery values'].sum() .unstack(level=1) .plot.line() ) ``` This is what we get as a 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-2f5abd2295-9f0c-4c96-9dd6-73dcf3f5b07b_571x432.jpg) We can see that from 2022 to 2023, all four of these countries’ military exports dropped a fair amount. However, the United States, even after the drop, still exports far more than anyone else. I’m still puzzling over how and why everyone’s export sales are dropping, besides what I mentioned before, namely the political issues. Russia’s exports almost certainly reflect not only their reduction in arms exports to ensure they have enough for their own war, but also the international sanctions on them. ### What countries imported the greatest total TIV of weapons in 2023? What explanation do you have for this? So far, we’ve been looking at exports. Who was importing arms in 2023? To answer this question, we’ll once again: - Limit the rows to those where “Delivery year” is 2023 - “groupby” on two columns, “Delivery year” and “Recipient” (rather than “Supplier”), summing TIV delivery values - Get the 10 largest importers with “nlargest” ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .groupby(['Delivery year', 'Recipient'])['TIV delivery values'].sum() .nlargest(10) ) ``` Here are the results: ``` Delivery year Recipient 2023 Ukraine 4012.22 Pakistan 2129.35 Qatar 1805.47 India 1428.35 Poland 1374.34 Saudi Arabia 1315.25 Egypt 1130.15 Japan 1102.90 Turkiye 936.10 UAE 901.75 ``` It probably shouldn’t come as a surprise that Ukraine is importing far more arms than anyone else. They didn’t have much of a domestic arms-manufacturing industry (although I’m under the impression that this is changing, given their obvious need for weapons to defend against Russia), and both the US and Europe are selling and giving them quite a bit. My biggest surprise on this list? Qatar, which I never thought of as a country that spends so much on defense. ### Create a table whose index consists of suppliers, whose columns are armament categories, and whose values are the total TIV for that category in that supplier, for Ukrainian imports in 2023. Finally, I asked you to create a data frame that let us track which countries sold which types of arms to Ukraine in 2023. If you realized that I was asking you to create a [pivot table](https://www.bambooweekly.com/pandas-pivot-table/), congratulations! Pivot tables are basically 2D groupby operations. After paring down the rows to only include 2023 and the recipient “Ukraine”, let’s consider what we want: - The index for our result should show the suppliers - The columns should show the categories of arms - The values should be our total TIV - The aggregation method should be “sum” Let’s start off by paring down the rows, using two “loc” and “lambda” calls in a row: ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .loc[lambda df_: df_['Recipient'] == 'Ukraine'] ) ``` We can call “pivot\_table” to create our pivot table. Having laid out the logic above, we can now say: ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .loc[lambda df_: df_['Recipient'] == 'Ukraine'] .pivot_table(index='Supplier', columns='Armament category', values='TIV delivery values', aggfunc='sum') ) ``` Remember that when we’re building a pivot table, both “index” and “columns” need to get categorical data, much like the column(s) we pass to a “groupby” operation. The “values” argument should specify a numeric column, since we’ll be calling an aggregation function on it. By default, the aggregation function is “mean”, but we can use anything else, if we pass it to “aggfunc”. And here, we do just that, invoking “sum”. The result is a surprisingly long list of countries that are supplying arms to Ukraine. I was curious to see how much in total was being supplied per country, and how much total Ukraine was getting per category. I thus passed the “margins=True” keyword argument to the query, and got an “All” column on the right and an “All” row at the bottom: ``` ( df .loc[lambda df_: df_['Delivery year'] == 2023] .loc[lambda df_: df_['Recipient'] == 'Ukraine'] .pivot_table(index='Supplier', columns='Armament category', values='TIV delivery values', aggfunc='sum', margins=True) ) ``` In this way, we can see that the largest categories of arms being imported by Ukraine are missiles and armored vehicles. And the largest suppliers are the United States, Germany, and Poland. Given that Poland was among the largest arms importers in 2023, part of me wonders whether they turned around and exported some of those arms to Ukraine. The data set does indicate new vs. secondhand materials, but there’s (again) a limit as to how much I can research each week! I hope that you found this interesting. The Jupyter notebook I used was here: [https://drive.google.com/file/d/1J9CMtUDlqy3VcnqwmkEObmgx5lnk27bd/view?usp=sharing](https://drive.google.com/file/d/1J9CMtUDlqy3VcnqwmkEObmgx5lnk27bd/view?usp=sharing&ref=bambooweekly.com) I’ll be back next week with more Pandas problems based on current events. Reuven