This week, we looked at the most recent reports from the Federal Election Commission (https://fec.gov/), which is responsible for overseeing the finances of election campaigns in the United States. This coming November, Americans will be electing a president, as well as many senators and all representatives to Congress. (There are also numerous state and local elections, but the FEC doesn’t appear to oversee their finances.)
Because each campaign has to submit its financial records to the FEC, we can get a picture of how much each has raised and spent, along with numerous other pieces of information regarding their finances. In theory, keeping campaign finances open and transparent reduces the influence of money in politics, although that would seem to be truer in theory than in practice.
If nothing else, this provided us with a new data set with which we can explore and learn not only about the world around us, but also the numerous ways in which we can use Pandas to analyze the data.
Data and 9 questions
This week’s data set is provided by the FEC. You can see all of the data sets that they offer from this page:
https://www.fec.gov/data/browse-data/?tab=bulk-data
The specific candidate data for this week’s questions come from the 2023-2024 election season, and is located here:
https://www.fec.gov/files/bulk-downloads/2024/weball24.zip
The data dictionary, which describes the fields in the data set, is available here:
https://www.fec.gov/campaign-finance-data/all-candidates-file-description/
I gave you nine tasks and questions for this week. As usual, a link to the Jupyter notebook that I used to answer these questions is at the bottom of this posting.
The data dictionary indicates the names (and meanings) of the columns in the actual candidate fund-raising data. Create a series containing the column names.
Before doing anything else, as usual, I loaded Pandas into Python and gave it the traditional alias:
import pandas as pdThis task might have seemed a bit weird. After all, a data dictionary is usually a document that tells us, the humans who are working to interpret the data, what it means. We can look at the data dictionary, understand the various types of data that we’ll be working with, know which columns we do and don’t want to load, and that’s it.
This week, I decided to take things a bit further — for reasons that became obvious in question 2. I wanted you to retrieve the data dictionary and turn it into a series based on that information.
Clearly, you could have opened the URL for the data dictionary and typed the column names into Pandas, manually creating a series.
But there’s a better way to do it, thanks to the “read_html” method in Pandas. Whereas most “read” methods in Pandas return a data frame based on a file on disk, read_html retrieves a URL and returns a list of data frames, one for each HTML table that it found on that page.
We can thus retrieve all of the data frames on the data-dictionary page with:
all_dfs = pd.read_html('https://www.fec.gov/campaign-finance-data/all-candidates-file-description/')In the case of this page, the data frame we want is the first, i.e., at index 0. I can thus get that data frame with:
df = all_dfs[0]However, I didn’t really want the full data frame. I just wanted the first column, which contained the names of columns for the FEC data itself. I can retrieve just the first column of that data frame with:
headers = (
all_dfs
[0] # first data frame in all_dfs
[0] # first column in the data frame
) The above will work… mostly. The problem is that this includes the first row of the HTML table, “Column name”, and treats it as one of the data’s actual column headers. We want to ignore that first row; in order to do that, I use “iloc” and retrieved all but the first row. Notice that I pass two arguments to iloc: The first is a slice, selecting the rows that I’m interested in. The second argument is 0, the column that we want from the data frame:
headers = (
all_dfs
[0]
.loc[1:, 0]
)The result is a series of strings, the names of the columns that we’ll soon be retrieving from the FEC site.
Now create a data frame based on the candidate data. Use the column names from question 1 as the column names for the data frame.
Now we’ll download the FEC data. One way to do it is to download the file, unzip it, figure out the format, and then use the appropriate method to read it into Pandas.
However, Pandas offers us an easier way: From downloading and inspecting the file, I know that it’s in CSV format, except that it’s using “|” (vertical bars) as field separators. Rather than downloading the file and then calling “read_csv”, I can just hand the URL to read_csv. Pandas will retrieve the file, open it, and even unzip it, returning a data frame:
url = 'https://www.fec.gov/files/bulk-downloads/2024/weball24.zip'
df = pd.read_csv(url,
sep='|')However, there is a bit of a problem with the above: The file does download correctly, and we do get a data frame. But the first line is assumed to contain headers, meaning that those values are being treated as column names. This is most definitely not the case. We can suppress the first line being used for headers with the “header=None” keyword argument. We can then provide names for those columns with the “names” keyword argument. And whadaya know, we can pass “headers”, the variable we just defined based on scraping the HTML table, as the value:
url = 'https://www.fec.gov/files/bulk-downloads/2024/weball24.zip'
df = pd.read_csv(url,
sep='|',
header=None,
names=headers)The resulting data frame contains 2,889 rows and 30 columns. If the data were bigger, then I might worry about paring down some of those columns, but I can live with them, given the relatively small size (about 1.5 MB, according to df.memory_usage) of the data.
We’ve now loaded the financial information into memory. Note that this file is not cumulative; it only contains data for the most recent FEC reporting period. We could, in theory, download previous reports and then compare them. But we’ve got enough interesting questions to answer here that I don’t see that as necessary.
Identify the 10 candidates with the highest total receipts. What states are they from, and does any location seem unusual?
Now that we have the data, we can start to ask interesting questions. For example: What 10 candidates have the highest total receipts?
In theory, each candidate appears once in this database. (We’ll see how true that assumption is in just a moment.) If that’s so, then we can find out how much each candidate has in total receipts by looking at the TTL_RECEIPTS column. If we sort the data frame by that column using “sort_values”, then we can find out who has received the most:
(
df
.sort_values('TTL_RECEIPTS', ascending=False)
)If I’m just interested in the 10 candidates who received the most, then I can add a call to “head” to get the top 10:
(
df
.sort_values('TTL_RECEIPTS', ascending=False)
.head(10)
)(You might prefer to use the “nlargest” method for this task, instead.)
However, we still haven’t found which states these top-fundraising candidates are from. We can do that by requesting the CAND_OFFICE_ST column, which indicates the state that the candidate is from:
(
df
.sort_values('TTL_RECEIPTS', ascending=False)
.head(10)
['CAND_OFFICE_ST']
)We get the following output:
2281 00
2342 00
2292 00
2327 00
2321 00
2440 CA
239 CA
2441 CA
2320 00
2330 00
Name: CAND_OFFICE_ST, dtype: objectI know that CA is the abbreviation for California, but what is 00? Turns out, that’s the FEC’s way of marking that someone is a candidate for national office (i.e., president) rather than from within a particular state (i.e., senator or congressional representative).
By the way, let’s look at that list of candidates again, retrieving just candidate name and state. And while we’re at it, let’s expand the number we look at to 15.
But before we do that, I want to make sure that numbers are displayed in an easier-to-read format, with only two numbers after the decimal point and commas every three digits:
pd.options.display.float_format = '{:,.2f}'.formatThe above sets the Pandas option “float_format” to something that is easier for us to read, using the “str.format” method.
Now let’s grab the top 15 fund-raising candidates:
(
df
.sort_values('TTL_RECEIPTS', ascending=False)
.head(15)
[['CAND_NAME', 'CAND_OFFICE_ST', 'TTL_RECEIPTS']]
)The results:
CAND_NAME CAND_OFFICE_ST TTL_RECEIPTS
2281 MERCER, LEE 00 192,000,000.00
2342 TRUMP, DONALD J. 00 56,699,777.27
2292 BIDEN, JOSEPH R JR 00 44,652,629.85
2327 DESANTIS, RON 00 31,647,462.11
2321 RAMASWAMY, VIVEK 00 26,609,179.77
2440 PORTER, KATHERINE CA 22,130,230.79
239 SCHIFF, ADAM CA 21,520,627.72
2441 SCHIFF, ADAM CA 21,520,627.72
2320 HALEY, NIKKI 00 18,709,236.41
2330 BURGUM, DOUG 00 15,179,665.77
2639 TESTER, R. JON MT 15,158,325.02
2359 KENNEDY, ROBERT F JR. 00 15,078,528.42
2318 JOHNSON, PERRY 00 14,572,964.20
2738 BROWN, SHERROD OH 14,414,183.00
187 MCCARTHY, KEVIN CA 14,004,196.29Wait… what? There are a lot of weird things going on here:
- Who the heck is John Mercer? And did he really raise $192 million? I can tell you that he exists (his Web site is here), but that this isn’t the first time that someone has raised questions about how much money he raised. For example, there’s a question from the politics Q&A forum on Stack Exchange, where someone says the FEC corrected this amount in the past. Instead of $192 million, he actually raised, um, zero. That correction doesn’t seem to have made it into the data set. However, the Web site shows that he both raised $192 million and spent $192 million, leaving him with about $800 (yes, 800 dollars) of cash on hand.
- We can understand how presidential campaigns would raise the most, or even that statewide California campaigns, such as the current campaign for senator, would need a lot. Maybe even Sherrod Brown’s re-election bid for senator from Ohio. But Jon Tester’s senatorial bid in Montana? Maybe it’s just me, but I never expected Montana to be a state where you need to raise many millions to run for senate.
- And why does Adam Schiff appear twice? It would seem that the data is the same, but that he has two separate candidate IDs.
This just goes to show that even a regulator that is supposed to be checking things very carefully can have weird and faulty data.
We often hear that federal elections require more money than state ones. Show the mean and median amounts raised for federal vs. state campaigns. How different are they? What does the difference between the mean and median tell us?
To answer this question, we have to calculate the mean and median for all federal candidates, and then the mean and median for all non-federal candidates. (Technically speaking, I guess I should call them “national” rather than “federal,” since senators and representatives are also federal employees, even if they are elected from their individual states.)
We’ve seen that “00” indicates that the race is a national one. In theory, we want to run “groupby” on federal vs. non-federal candidates. But how can we do that?
The easiest solution, I think, is to temporarily define a new column, “is_federal”, with a True or False value depending on if the candidate has “00” in their state column. We can use the “assign” method for that:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
)Notice that here, my lambda is simply asking whether the “CAND_OFFICE_ST” value is “00”. Those rows that have True in “is_federal” are national candidates, as opposed to state-based candidates.
Having done that, we can now group by our new column:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
.groupby('is_federal')['TTL_RECEIPTS'].agg(['mean', 'median'])
)The above groupby will give us a separate answer for each value of “is_federal”. We’ll calculate the mean and median for TTL_RECEIPTS. Notice that because I want to invoke two different aggregation methods, I use the “agg” method, which lets me pass a list of strings representing the methods.
The result:
mean median
is_federal
False 356,665.57 3,100.00
True 3,470,216.26 5,892.50In both cases (mean and median), federal offices raise a lot more than state offices. But we can also see that the mean is a lot higher than the median, demonstrating the power than outlier values have to skew the mean. (My favorite joke that demonstrates the issue with using the mean: Bill Gates walks into a bar. On average, everyone there is now a millionaire.) The median still shows a huge gap between fund-raising for these offices, but not by as much.
Notice also that while we all pay attention to the candidates at the top, especially president, there are lots of low-profile candidates who run and don’t raise very much. (They typically don’t win, either.)
Use a boxplot to show the total receipts for federal vs. state candidates. What does this tell us about fund-raising efforts?
One of the best tools we can use to understand our data is the “describe” method. It tells us, for each column (or for a particular series), the min, 0.25 quartile, median, mean, 0.75 quartile, and max. John Tukey (https://en.wikipedia.org/wiki/John_Tukey) encouraged statisticians to use these numbers to understand the data. He called it the “five number summary.”
If you’re thinking, “Wait, there are six numbers there,” then you’re right. Tukey didn’t include the mean in his summary.
Sometimes, it helps to see it graphically. Tukey thus devised the “boxplot,” also known as a “box and whiskers plot,” which is a visual version of the five-number summary. Pandas lets us create boxplots very easily with “plot.boxplot” (or “plot.box”).
I could run a boxplot on TTL_RECEIPTS as follows:
df['TTL_RECEIPTS'].plot.box()But I asked you to do something a bit more sophisticated, namely to have separate (parallel) boxplots for federal and non-federal office. For that, we’ll again need to use “assign” to find which rows are for national office. Then we can use the “by” keyword argument for “plot.box”, for a pseudo-groupby in our plot:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
[['TTL_RECEIPTS', 'is_federal']]
.plot.box(by='is_federal')
)The result:
In theory, a boxplot shows the min, 0.25, median, 0.75, and max values. But if a value is an outlier, than it is shown as a circle. (An outlier is defined, in the Pandas boxplot world, as 1.5 times the IQR (distance between 0.75 and 0.25) above the max or below the min.) We can see that there are a lot of outliers, meaning that while most races stay within a normal range, some people are raising quite a lot.
Moreover, we can see that the number of outliers, and the scale of those outliers, is far greater for the “True” boxplot, meaning for national office.
What are the mean and median amounts raised for each state (i.e., not federal) race? How about for each party?
Any time we want to calculate something for “each” value of a column, that’s almost certainly going to involve a grouping operation. Here, we would want to group by the state — but we want to ignore those rows for which the state is “00”. How can we do that?
We can start with the same use of “assign” as before:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
)
We have now created (again) the “is_federal” column with boolean values. Now let’s remove all of those rows for which “is_federal” is True:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
.loc[lambda df_: df_['is_federal'] == False]
)
Notice that I’m using “.loc” here. Using .loc allows me to select rows from the data frame based on a boolean series or (as I’ve done here) a function. Basically, I’m asking for all rows that are not associated with national races. Also notice that my lambda has a parameter “df_”, which is a shorthand for “temporary data frame.” .loc and the lambda get the data frame that includes “is_federal”, not the original df on which we started running our query. As a result, we would get an error asking for df[‘is_federal’], but it’s totally fine in the lambda to say df_[‘is_federal’].
Now that we have only state-based races, we can perform a groupby, asking for the mean and median amounts raised per state:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
.loc[lambda df_: df_['is_federal'] == False]
.groupby('CAND_OFFICE_ST')['TTL_RECEIPTS'].agg(['mean', 'median'])
)
The result:
mean median
CAND_OFFICE_ST
AK 278,142.51 12,393.24
AL 267,925.23 255.15
AR 358,319.55 509.27
AS 12,900.00 12,900.00
AZ 629,717.59 11,135.22
CA 528,285.13 6,574.00
CO 278,915.88 3,827.50
CT 370,358.55 636.50
DC 15,185.00 0.00
DE 488,933.61 143,809.91
FL 172,662.17 111.77
GA 159,005.36 75.50
GU 10,950.00 0.00
HI 162,761.07 724.00
IA 355,725.00 1,073.33
ID 71,397.18 132.50
IL 215,223.78 346.04
IN 290,842.72 4,288.52
KS 225,678.02 71.02
KY 366,056.61 2,807.49
LA 535,267.88 0.00
MA 273,645.32 0.00
MD 434,568.72 4,672.38
ME 346,514.53 53,083.06
MI 337,910.62 16,593.48
MN 397,215.72 2,863.44
MO 378,041.16 4,676.15
MP 1,075.51 1,075.51
MS 280,435.66 202.74
MT 901,749.73 8,000.00
NC 210,630.15 808.00
ND 653,979.40 68,809.04
NE 719,529.45 689,469.67
NH 184,768.78 500.00
NJ 327,461.84 711.35
NM 516,558.58 69.22
NV 619,657.23 14,667.84
NY 296,357.43 475.18
OH 602,823.62 5,102.76
OK 169,867.40 6,084.06
OR 182,502.98 5,189.81
PA 358,470.65 10,444.26
PR 296,931.78 0.00
RI 254,068.44 38,324.29
SC 741,526.81 107,382.71
SD 370,832.83 4,523.92
TN 300,926.10 0.00
TX 328,082.38 4,031.14
UT 315,742.87 7,253.00
VA 286,211.94 1,800.00
VI 238,633.58 238,633.58
VT 284,405.59 734.57
WA 416,856.44 5,635.08
WI 441,016.14 7,122.16
WV 281,369.84 9,162.48
WY 505,176.96 99,816.10Notice that the state abbreviations are alphabetized. That’s a normal thing to have happen when you run “groupby”; you can turn it off, but it usually makes it easier to understand.
If I want to find out the states with the highest and lowest mean and median, we can use “agg” again, passing it “idxmin” and “idxmax”, methods which tell us the index associated with the min and max values:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
.loc[lambda df_: df_['is_federal'] == False]
.groupby('CAND_OFFICE_ST')['TTL_RECEIPTS'].agg(['mean', 'median'])
.agg(['idxmin', 'idxmax'])
)The results:
mean median
idxmin MP DC
idxmax MT NEThe highest mean is in Montana, as we’ve seen before. The highest median is in Nebraska, also not where I would expect to see a lot of money put into races.
When we look at the minimum, we see that the median is Washington, DC. And the mean? It’s in MP… which, I must admit, was an abbreviation with which I wasn’t at all familiar. I mean, the US has 50 states, plus DC, plus some territories. What the heck is MP?
It’s the Northern Mariana Islands, of course: https://en.wikipedia.org/wiki/Northern_Mariana_Islands
I can understand why they would be spending a fairly low amount on campaigning, given their population of just over 55,000 people.
I also asked you to look for the amount raised by each party. Once again, I removed all national races and then grouped by the “CAND_PTY_AFFILIATION” column:
(
df
.assign(is_federal = lambda df_: df_['CAND_OFFICE_ST'] == '00')
.loc[lambda df_: df_['is_federal'] == False]
.groupby('CAND_PTY_AFFILIATION')['TTL_RECEIPTS'].agg(['mean', 'median'])
)The results:
mean median
CAND_PTY_AFFILIATION
(I) 1,300.00 1,300.00
CON 66.59 38.17
CRV 0.00 0.00
DEM 460,315.99 9,090.00
DFL 632,369.53 2,164.43
FL 22.17 22.17
GOP 4,544.00 4,544.00
GRE 2,118.48 314.85
IDP 0.00 0.00
IND 246,056.56 1,067.92
LIB 1,449.64 0.00
NNE 0.00 0.00
NON 6,212.85 8,521.54
NOP 5,535.00 5,535.00
NPA 57.14 0.00
OTH 1,447.50 127.50
PFP 0.00 0.00
PNP 1,166,432.00 1,166,432.00
REP 289,797.36 1,554.25
UN 8,066.74 9,117.52
UNI 203.85 203.85
UNK 37,459.66 4,601.56
UUP 8,206.00 8,206.00
W 4,087.68 1,047.80
WFP 0.00 0.00If you thought that the us only had two political parties… well, you’re mostly right, but you’re not totally right. I mean, there are really only two large, viable parties on the national level, but there are lots of “third” parties out there.
So, who raised the most and least, by mean and median?
mean median
idxmin CRV CRV
idxmax PNP PNPWell, “CRV” refers to the conservative party. I’ve heard of them, although other than in New York (where candidates often run on multiple party lists), I didn’t know they really existed.
But what is the PNP? I must admit that I’m not sure. The FEC has a list of parties, including their abbreviations, but PNP doesn’t show up anywhere. You can see that they raised a lot more than anyone else, but who are they? I’m not really sure.
I’m so glad that campaign fund-raising is transparent to the public.
How many candidates had less cash on hand at the end of the reporting period than at its start?
The data set includes two columns, COH_BOP and COH_COP (cash on hand, beginning of period and close of period) indicating how much money they actually have. I was curious to see who had less at the end of the period than at the beginning. Such a campaign might just be spending it well or they might be hoarding their cash until later on.
I decided to again use “assign”, first to add a new column “net_coh” showing how much they had at this point in time. I then defined a boolean column, “has_less_now”, indicating whether “net_coh” was negative.
Notice that when you’re using assign, you can refer to earlier columns in later keyword arguments. I’m sure that this is due in part to the fact that in modern versions of Python, the order of key-value pairs in a dict reflects the order in which they were assigned.
I then retrieved only the “has_less_now” column, and ran “value_counts” on it to find out how many campaigns did (and didn’t) owe money:
(
df
.assign(net_coh = lambda df_: df_['COH_COP'] - df_['COH_BOP'],
has_less_now = lambda df_: df_['net_coh'] < 0 )
['has_less_now']
.value_counts()
)Here’s what I got:
has_less_now
False 1896
True 993
Name: count, dtype: int64In other words, about one third of the campaigns had less money on hand at the end than at the start.
What 10 candidates netted the least cash on hand this period? What parties are they from, and are they running state or federal races? Also, how much debt do they owe (if any)?
Let’s now take this a bit further, to find out which candidates had the least net amount at the end of this period. First, we’ll again create the “net_coh” column. But then we’ll use “sort_values” on “net_coh” to find out who has the least. I can then grab the 10 top values, and look at five columns: Candidate name, party, state, net, and also any debts they owe:
(
df
.assign(net_coh = lambda df_: df_['COH_COP'] - df_['COH_BOP'])
.sort_values('net_coh')
.head(10)
[['CAND_NAME', 'CAND_PTY_AFFILIATION', 'CAND_OFFICE_ST', 'net_coh', 'DEBTS_OWED_BY']]
)Here’s the result:
CAND_NAME CAND_PTY_AFFILIATION CAND_OFFICE_ST \
2325 SCOTT, TIMOTHY E. REP 00
2792 SCOTT, TIMOTHY E. REP SC
350 PORTER, KATHERINE DEM CA
2415 SHELBY, RICHARD C REP AL
2051 ALLRED, COLIN DEM TX
2778 OZ, MEHMET DR REP PA
875 SPARTZ, VICTORIA REP IN
709 GREENE, MARJORIE TAYLOR REP GA
852 WALORSKI SWIHART, JACKIE REP IN
2599 STABENOW, DEBBIE DEM MI
net_coh DEBTS_OWED_BY
2325 -8,448,299.06 927,827.23
2792 -8,448,299.06 927,827.23
350 -7,434,958.86 0.00
2415 -5,463,830.18 0.00
2051 -1,981,809.69 0.00
2778 -1,570,065.57 25,563,728.81
875 -1,263,415.96 0.00
709 -1,098,446.69 550,000.00
852 -1,069,006.53 0.00
2599 -998,872.75 0.00
(The result was too wide, so it’s split across two portions.)
We see that the candidate who netted the least was Tim Scott, a senator from South Carolina who was running for president. I’m assuming that he’s listed twice because he was a national candidate now but was previously a state candidate, but I’m not sure. Regardless, he had about $8.5 million less on hand at the end of the period than the start. Oh, and he also owes about $1 million. Yikes!
Next up is Katherine Porter, a California representative who is running for senate from that state.
The others aren’t that interesting, except for two:
- Mehmet Oz ran for senate from Pennsylvania two years ago. It seems that he had $1.5 million less at the end of the period than the start. Does that mean he’s spending money from his campaign? Or that he’s raising money, and using it to pay off his (deep breath) $25 million debt? I’m not really sure.
- Marjorie Taylor Greene is a well-known Republican representative from Georgia. She’s not really my cup of tea, but I was under the impression that she is great at raising money. Maybe, but she had $1 million less at the end of the period than at the start, and she owes about $500 thousand.
Show a scatter plot comparing the amount received (TTL_RECEIPTS) with the total spent (TTL_DISB). Do the amounts seem highly correlated? What does the correlation calculation show?
Finally, I asked you to show a scatter plot comparing the amount received with the amount spent. Do they look correlated to you?
I created the plot with “plot.scatter”:
df.plot.scatter(x='TTL_DISB', y='TTL_RECEIPTS')The result:
It sure looks to me like a positive correlation here — namely, the more you take in, the more you spend. If we perform a numeric calculation, what will we see?
df[['TTL_DISB', 'TTL_RECEIPTS']].corr()The result:
TTL_DISB TTL_RECEIPTS
TTL_DISB 1.00 0.96
TTL_RECEIPTS 0.96 1.00It’s a very high positive correlation (0.96). Meaning that campaigns that raise more also spend more. Which means sense, since the whole point of raising the money is to spend it; if they’re investing it, then they’re doing something wrong.
That’s the analysis for this week! If you have comments, questions, or feedback, you’re welcome to share it here.
The Jupyter notebook I used is here: https://drive.google.com/file/d/1SG-Vl_CDS6Yto_eLmSFiU97MqZSPKOjI/view?usp=sharing
I’ll be back on Wednesday with more questions and puzzles about data analysis, Python, and Pandas based on current events.
Reuven