Joshua R. Bruce

Visualizing Per-Capita Federal Financial Assistance to States (with R)

23 Aug 2017


There are often discussions about the appropriate level of funding for the US federal government. Recently, this discussion took on renewed vigor as the Trump administration released a budget plan that would dramatically shrink domestic, non-defense spending.

As someone who does research on federal expenditures, and who is generally interested in politics, I wanted to know more about this debate. It’s also an excuse to play around with maps in R, which can be tricky but very satisfying. Hence, this post - a look at federal financial assistance to state and local governments from the US federal government. I start by looking at broad funding levels, and then dig a little deeper on the EPA and funding to colleges and universities.

The data here are only for fiscal year 2016 (i.e., Oct. 1, 2015 - Sept. 30, 2016), but could easily be expanded back to at least FY 2000. The data are available from USASpending.gov, a repository of all spending on contracts, grants, and other forms of assistance provided by the federal government. I specifically look at grants, not contracts, which include most forms of non-contractual assistance from the government to non-federal entities, such as food stamps and research grants.

Get the data

To download the data, which I call govt_assistance_2016 in the code below, proceed to the “Download Center” tab on the USASpending website. Select all agencies and fiscal year 2016. After you submit the request, it will generate the dataset, which may take a few minutes. Save this .csv file in your working directory. USASpending also provides a codebook for the data available; Section 4 of the codebook pertains to grants. The following analysis also requires a variety of R packages, listed below.

<?prettify?>

# Loading data and required packages for visualizing federal spending data
setwd('~/My Working Directory')
require(stringr)
require(ggplot2)
require(rgdal)
require(maps)
require(maptools)
require(mapproj)
require(viridis)
require(rgeos)
require(dplyr)
require(fiftystater)
require(plyr)

govt_assistance <- read.csv('fed_assistance_fy_2016.csv', stringsAsFactors = F)

Having loaded the data, we can now start to see where funding goes. Fist, by using length(table(govt_assistance$principal_place_state_code)), we can see there are 60 types of “states” that receive funding, indicating that non-state entities are included. I’m specifically interested in the 50 states, so I want to create a new data frame of just those records (I prefer to create new data frames rather than drop rows incase I make a mistake).

To begin, I make a table of the places where federal funds go. This is a count of grants, of which there are several sub-types, to each of the 60 state entities. This table is useful in part because it tells us states are identified by all-caps, two-letter abbreviations, which we can use to extract the records we want using R’s built-in list of state abbreviations.

<?prettify?>

# Create a table of grant frequency by state entity
table_of_state_grants <- as.data.frame(table(
    govt_assistance$principal_place_state_code),
    stringsAsFactors = F)

<?prettify?>

# Look at top six rows from table_of_state_grants
head(table_of_state_grants)

<?prettify?>

##   Var1  Freq
## 1      75527
## 2   00    98
## 3   AK  6373
## 4   AL  7990
## 5   AR  5042
## 6   AS   487

This table tells us not only how many grants went to entities in each state, but also that the states are identified by two-letter, all-caps abbreviations. The next section of code subsets the data to only the 50 US states.

<?prettify?>

# Create data frame of only grants that went to the 50 US states
govt_assistance_states <- govt_assistance[which(
    govt_assistance$principal_place_state_code
    %in% as.list(toupper(state.abb))), ]

Grant recipients

Having subset the data, we can now look at what types of entities in each state were receiving grants, according to the classification in the USASpending data.

<?prettify?>

# Make a data frame of grant recipient types 
grant_recipient_types <- as.data.frame(table(govt_assistance_states$recipient_type), 
                                        stringsAsFactors = F)
print(grant_recipient_types)

<?prettify?>

##                                                    Var1   Freq
## 1                                  00: State government 365575
## 2                                 01: County government   5988
## 3                       02: City or township government   8778
## 4                       04: Special district government   2254
## 5                       05: Independent school district  13153
## 6  06: State controlled institution of higher education  68415
## 7                                      11: Indian tribe  13819
## 8                                   12: Other nonprofit  45113
## 9                          20: Private higher education  31154
## 10                                       21: Individual  11738
## 11                              22: Profit organization   3633
## 12                                   23: Small business   8007
## 13                                        25: All other   8777

In this case, I’m interested in funding that went to a state or lower-level governmental unit, which are the first five types of entities listed in the table. As such, I’ll now make a new data frame that includes only these five types of recipients.

<?prettify?>

state_local_assistance <- govt_assistance_states[which(
    govt_assistance_states$recipient_type %in% grant_recipient_types[c(1:5),1]),]

Types of assistance

Within the federal assistance data, there are a number of variables that tell us about each grant. One of the most important for understanding funding levels is to look at new and continuing grants (rather than a funding adjustment or revision to a previous grant). The action_type variable identifies whether a grant is new, continuing, revised, or adjusted. I’m interested in only the new and continuing grant disbursements, so I subset to just those records.

<?prettify?>

# create subset data frame of new and continuing funding
state_local_assistance <- state_local_assistance[which(
    state_local_assistance$action_type == 'A: New assistance action' |
        state_local_assistance$action_type == 'B: Continuation (funding in succeeding budget period which stemmed from prior agreement to fund amount of the current action)'),]

We’re left with 107,177 records, totaling over $476 billion in domestic assistance from the federal government to state and local entities.

State-by-state federal funds

I next use dplyr to calculate the total funding received by each state. I label this variable state_assistance, which is then used in the next code chunk to map the data.

<?prettify?>

# calculate state-by-state funding totals
total_state_funding <- state_local_assistance %>% 
    group_by(principal_place_state_code) %>%
        summarise(state_assistance = sum(fed_funding_amount)) 

At this point, we can finally start to visualize how federal funds are distributed across the country. I start with a simple map of the fifty states shaded by their total funding amount. You can read more about creating maps in R with ggplot2 and fiftystater on this page. (And for more on the color palette I use, check out the viridis package.)

<?prettify?>

# Load fifty-state shape file from fiftystater package
data('fifty_states')

# Add column to data with lower-case state names rather than abbreviation
total_state_funding$state_id <- ''

# Loop to look up state name; make lower case to match map data
for(i in 1:nrow(total_state_funding)){
    total_state_funding$state_id[i] <-
        tolower(state.name[grep(total_state_funding$principal_place_state_code[i], 
                        state.abb)])
}

# Plot map
ggplot(total_state_funding, aes(map_id = state_id)) + 
    # map points to the fifty_states shape data
    geom_map(aes(fill = state_assistance), map = fifty_states) + 
    expand_limits(x = fifty_states$long, y = fifty_states$lat) +
    coord_map() +
    scale_x_continuous(breaks = NULL) + 
    scale_y_continuous(breaks = NULL) +
    labs(x = "", y = "", title = 'Federal Spending to US States') +
    theme(legend.position = "bottom", 
    panel.background = element_blank()) + 
    fifty_states_inset_boxes() +
    scale_fill_viridis(option = 'viridis')

Per-capita state spending

Not surprisingly, California, New York, and Texas get the most money. These are also the three most populous states. Thus, a reasonable feature to take into account is population. The next code block calculate per-capita spending by drawing in US Census population estimates for each state.

There are multiple ways to incorporate demographic data into analyses. For example, the Census makes population estimates available for download on its website. However, in the interest of keeping the analysis contained within R, I will use the censusapi package. Before using this package, you’ll need to set up an API key on the Census website, which you can do here. It’s worth spending some time getting to know the censusapi package, starting with the list of available APIs. You can make a table of these with as.data.frame(listCensusApis()). The API I use here is the “Vintage 2016 Population Estimates: Population Estimates,” known as ‘pep/population’ in the API. You can also see the list of variables included in a Census API with the listCensusMetadata() command (in this case, listCensusMetadata(name="pep/population", type = "variables", vintage = 2016)).

<?prettify?>

# Get population data from censusapi
# Set your Census API key using Sys.setenv(CENSUS_KEY='YOUR_API_KEY')

# Download Census population estimates by state for 2016
population_data <- getCensus(name = 'pep/population', 
                                vars = c('STATE', 'POP'), 
                                region = "state:*", 
                                vintage = 2016)

The STATE variable in the population data is the state FIPS code, rather than the state name. We haven’t made use of FIPS codes thus far, and so need to convert them to names or abbreviations. Thankfully, the noncensus package is here to help. After creating a table with state abbreviations and corresponding FIPS codes, I run a loop to add the abbreviations to the population data frame.

<?prettify?>

# Load noncensus package 
require(noncensus)
data("counties")

# Use dplyr to create table of states and state FIPS codes 
state_fips <- distinct(select(counties, state, state_fips))

# Make character instead of factor
state_fips$state <- as.character(state_fips$state)
state_fips$state_fips <- as.character(state_fips$state_fips)

# Make columns for state name and abbreviation in population data
# Re-use principal_place_state_code as name for merge later
population_data$principal_place_state_code <- ''

# Loop to match state name and abbreviation to FIPS in population_data
for(i in 1:nrow(population_data)){
    population_data$principal_place_state_code[i] <- 
        state_fips$state[which(state_fips$state_fips ==
                                population_data$STATE[i])]
}

# clean up data frame by only keeping columns with abbreviations and population
population_data <- subset(population_data, 
                            select = c('principal_place_state_code', 'POP'))

Now, we can merge the population data with the total state spending data and calculate per-capita spending.

<?prettify?>

# Merge population data with total_state_funding data frame
total_state_funding <- left_join(total_state_funding,
                                    population_data,
                                    by = 'principal_place_state_code')

# Divide 2016 spending by estimated 2016 population
total_state_funding$per_capita_funding <- as.numeric(total_state_funding$state_assistance)/
as.numeric(total_state_funding$POP)

# Summarize per-capita funding
summary(total_state_funding$per_capita_funding)

<?prettify?>

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   434.7  1211.0  1505.0  1521.0  1824.0  2769.0

As we can see, federal funding to state and local government entities ranged from $434.70/person to $2,769/person in fiscal year 2016. We can now map this using a slightly altered version of the code block above.

<?prettify?>

# Plot map - per-capita federal funding in FY 2016
ggplot(total_state_funding, aes(map_id = state_id)) + 
    # map points to the fifty_states shape data
    geom_map(aes(fill = per_capita_funding), map = fifty_states) + 
    expand_limits(x = fifty_states$long, y = fifty_states$lat) +
    coord_map() +
    scale_x_continuous(breaks = NULL) + 
    scale_y_continuous(breaks = NULL) +
    labs(x = "", y = "", title = 'Federal Spending to US States') +
    theme(legend.position = "bottom", 
    panel.background = element_blank()) + 
    fifty_states_inset_boxes() +
    scale_fill_viridis(option = 'viridis')

To get a sense of the actual values, we can also print a rank-ordered table of per-capita federal funding.

<?prettify?>

# Print a rank-ordered table of top 10 states per-capita federal funding
options(tibble.print_min = 50)
total_state_funding[order(total_state_funding$per_capita_funding, 
decreasing = T), 
c(3,5)] # columns for state_id and per_capita_funding 

<?prettify?>

## # A tibble: 50 x 2
##          state_id per_capita_funding
##             
##  1         alaska          2768.7554
##  2        vermont          2508.5928
##  3       new york          2405.7763
##  4       kentucky          2217.5354
##  5      louisiana          2153.0957
##  6   rhode island          2113.8055
##  7     new mexico          2034.6928
##  8       arkansas          1986.8485
##  9  massachusetts          1932.4569
## 10   north dakota          1921.1059
## 11        montana          1861.3508
## 12     california          1830.5677
## 13       delaware          1826.5266
## 14    mississippi          1814.5886
## 15  west virginia          1801.9148
## 16    connecticut          1766.0705
## 17       michigan          1750.9256
## 18         oregon          1706.3330
## 19          maine          1697.9878
## 20         hawaii          1586.1937
## 21        wyoming          1582.6338
## 22        arizona          1579.7738
## 23     new jersey          1564.4301
## 24        indiana          1533.1844
## 25      minnesota          1528.4687
## 26           iowa          1481.9301
## 27     washington          1435.8609
## 28       missouri          1428.5307
## 29       maryland          1370.7874
## 30   pennsylvania          1355.9804
## 31        alabama          1307.1957
## 32           ohio          1299.9173
## 33         nevada          1270.8444
## 34          texas          1252.0911
## 35       oklahoma          1240.8785
## 36       colorado          1231.7817
## 37      tennessee          1216.1975
## 38       illinois          1209.7643
## 39 south carolina          1208.4932
## 40  new hampshire          1200.9699
## 41 north carolina          1150.6585
## 42          idaho          1079.1449
## 43      wisconsin          1074.1797
## 44   south dakota          1067.0331
## 45        georgia          1034.1041
## 46        florida           930.6878
## 47           utah           877.6799
## 48         kansas           724.3746
## 49       virginia           668.7814
## 50       nebraska           434.6791

End Note

I had originally intended to go into agency-specific funding, but time and energy have gone elsewhere.