Background and Databases: 2017 Census of Governments Finance Data

The coronavirus has accelerated a reckoning for U.S. state and local governments that had been building up for 25 years, and the consequences, which would have gradually become terrible anyway, will be severe.  Those public officials who made, and benefitted from, the past decisions that will lead to a future of higher taxes, diminished services, and deteriorating infrastructure will eventually leave the scene, perhaps soon.  Their replacements, faced with a crisis and the need to inflict pain on their constituents, might wonder how their community ended up in this situation?  How does their state or locality’s tax burden, in total and by type of tax, and spending, by government function, compare with other places, and how has that changed over time?  Who, compared with other places and compared with the past, has been taking out too much, and/or putting in too little? And to what extent would, should, and could later-born and future residents, who got no related benefits, be sacrificed to pay for the self-dealing of the past?

I have been using data from the Governments Division of the U.S. Census Bureau, and other data sources, to answer questions like these for the past 30+ years.  Including the Census of Governments, which takes place every five years, with the latest data for FY 2017 released late in 2019.   While revised data will be released at some point in 2020, given the coronavirus crisis I have decided to compile and analyze that which is now available.  This, the first of a series of posts, will describe where the data comes from and how I tabulated it.  It includes downloadable spreadsheets with data on state and local government revenues and expenditures, by category, per $1,000 of the personal income of everyone in each area, for all 50 states and the District of Columbia.  And data for all local governments combined in each county in New York and New Jersey, and many other selected counties around the country chosen for comparison.  Not only for FY 2017, but also – identically – for FY 2007 and FY 1997, for a 20-year trend.

Subsequent posts will include tables, charts and specific analyses of taxes, other revenues, local government education, state government colleges and universities, public safety, health and social services, infrastructure and amenities, and general government.  But as is my custom I’m making the data available up front, so anyone can download it, look at it, and make up their own mind before getting my take on it. My focus is New York and New Jersey, but there is far more in the spreadsheets linked from this post than I intend analyze on an avocational basis. And anyone could have this data to think about themselves, and have it right now.

The 2017 Census of Governments state and local government finances data, as I downloaded it from the Census Bureau, may be found here:

Before describing what I did with it to get it into final form, consider what the final form shows, in one of several spreadsheets that can be downloaded from this post (the rest are at the end).


You’ll see tabs for three worksheets at the bottom.  The first worksheet shows state government revenues and expenditures per $1,000 of personal income, for all 50 states. The second shows local government revenues and expenditures for all 50 states and the District of Columbia, plus New York City and the rest of New York State separately.

The third shows data for all local governments combined at the county level.  In that worksheet, New York State is grouped into four broad areas, and data is also provided separately for Fairfield County and the Rest of Connecticut.  Moving right, one finds the same data for other county areas around the country, chosen for comparison with either New York City, the Downstate Suburbs, the Upstate Urban Counties, or Rural/Small City New York.   Cook County (Chicago) and Alleghany County (Pittsburgh) for example.  And further right, one finds data for every county in New York and New Jersey individually.

My analysis takes the data item (revenue, expenditure, debt) for each place and presents it per $1,000 of the personal income of an each area’s residents.  Think of it this way. Your household earns X thousand dollars per year, and spends some percent of it on food, some percent of it on housing, some percent of it on clothing, some percent of it on entertainment, etc.  And the government takes some of your income, and everyone else’s income, and spends some percent of it on public schools, some percent of it on streets and highways, some percent of it on health care, etc.

The figures are in the tables are expressed per $1,000 of personal income, rather than as a simple percent of personal income, because otherwise spending differences in some categories would be too low to stand out.  For example, in FY 2017 the City of New York spent $3.49 per $1,000 of the income of its residents on fire protection, not including money spent on firefighter pensions.  Expressed as a percent of everyone’s income, that would be well less than one percent, about 0.349%.

The personal income data was downloaded from the federal Bureau of Economic Analysis, from the Local Area Personal Income dataset.

I use personal income, rather than population, to adjust the revenue and spending totals for the size of different areas, because I also want to adjust for the cost of living.  In places with higher average incomes, such as those in the Northeast Corridor, the average resident can afford to pay more in state and local taxes than the average resident of lower-income areas, such as those in the South and Southwest, without it being a greater burden as a percentage of their income.

At the same time, however, places with higher average incomes also tend to have higher costs of living, due to higher housing costs and higher wages in sectors that provide services direct to consumers.  Therefore, one could not expect to pay a teacher or police officer the same wages in the suburbs of New York City as in Texas or Oklahoma and expect them to be as well off, and thus attract workers of the same caliber.

Tabulating the data per $1,000 of personal income adjusts for both the local cost of living and the capacity of local residents to pay taxes, and thus provides a more fair comparison from the place to place, and with the national average.

In the spreadsheet, the data per $1,000 of personal income is in the form of formulas, not numbers.  It can be converted to numbers by blocking the cells and using the command edit-copy-paste special-values and number formats.  You can see the data as downloaded from the Census Bureau, along with my modifications and adjustments, below the table.  Including the codes for the Bureau’s detailed data items, and their descriptions.  I have tried to put modified cells in bold.

The data also has to be adjusted for the complex relationships between the federal, state and local governments.  For this reason, spending data by category is presented for “direct spending.”

In some cases states spend money on services and benefits they provide themselves, with higher education, corrections (prison), and unemployment insurance examples of functions where states do most of the work.  But more often they merely pass money on to local governments or (as in the case of health care funded by Medicaid) the private sector.  The Census Bureau distinguishes between “direct spending” on actual public services and benefits, and “intergovernmental” spending, the portion of the budget sent to some other government.  And between “own source” taxes and other revenues (such as fees and fines) and intergovernmental revenue received from some other level of government.

Take, for example, Medicaid-funded health care at a hospital run by New York City’s Health and Hospitals Corporation.  Medicaid is a state program in New York, but local governments are required to contribute to it.  So the City of New York might “spend” a dollar on inter-governmental “public welfare” aid to the State of New York (Medicaid is lumped in with public welfare in revenue data because it is a categorical, income restricted federal program).  The State of New York, meanwhile, could then “spend” that same dollar as “public welfare” aid to the City of New York, as a payment to the Health and Hospitals Corporation.  And then the City of New York could then spend that same dollar a third time, as direct Public Hospital spending on health care.

In total spending, that same dollar could be spent three times. In direct spending it is spent only once.  For comparisons of spending by function across places, only “direct expenditures” should be used.

It is to analyze intergovernmental spending that I was required to download and compile more detailed data than the Census Bureau provides in its own summary tables. I want to be able to identify how much of local government spending in each category is financed by fees for that specific service, and/or by categorical federal and state aid that can’t be used for anything else, rather than general local revenues.  The detailed data, with data for all data items by state and type of government, is found in the statetypeufile on the Bureau’s site. It is a text file that “unzips.”

As for pension contributions, because of the backward way the Bureau tabulates them – as revenues for the pension fund rather than expenditures from the budget – and because state-run funds cover local employees, these are only tabulated at the state level, and for New York City and the Rest of NY State separately.  You’ll find some pension data in the “state” and “local” spreadsheets, but not in the “county area” spreadsheet.  The Bureau publishes most pension data in separate tables. A more detailed analysis of public pension systems over the decades was included in the Sold Out Futures analysis here.

That source data may be found here.

The division of responsibilities between state and local government, and the structure of local government, varies from place to place.

In most of the country, for example, public schools are a local government function, but in Hawaii the state government runs all the schools, and this affects the U.S. average. And in some states such as New Jersey the state government has taken over some local school districts, and their spending now counts as “state government spending” rather than “local government spending.”  In most of the country, and in New York City, mass transit is classified as a local government activity, whereas in all of New York State outside New York City and in New Jersey it is classified as state government.  That is true even though New York City Transit has been part of the state-run MTA since 1968. Comparing New York City’s local government spending in these categories with the New Jersey and U.S. totals based on the original data, therefore, would be misleading, because some of the spending would be missing.

In the local government by state part of the spreadsheet, therefore, state spending on elementary and secondary education, public transit, airports and seaports is generally re-tabulated as local government spending, to get a similarly measured total for each state.   You can see the related state data below the local government data in the spreadsheet.

For mass transit spending within New York State, I allocated New York State spending as recorded by the Census Bureau to different broad areas of the state, and some (due to MetroNorth) to Connecticut, based on data from the National Transit Database.  Allocating some of MetroNorth’s fare revenue and expenditures to Connecticut, and some of the Long Island Railroad’s to New York City, based on the number of non-CBD train stations in each area, is an improvement for this effort compared with my prior four Census of Governments compilations.

As an added complication, for historical reasons all the revenues, expenditures and debt of the Port Authority of New York and New Jersey are tabulated as local government in New York City.  (Aside from the Waterfront Commission, a tiny agency, the City of New York and the Port Authority of New York and New Jersey are the only local governments within the borders of New York City).  Where possible I divide Port Authority revenues and expenditures between the New York City (and state) and New Jersey. You can look at the formulas in data items in bold to see how.  This is just an approximation.  Right now, the Port Authority is rebuilding LaGuardia Airport, but for each time period I just allocated two-thirds of its airport expenditures, operating and capital, to New York City and one-third to New Jersey.

I used a separate, more detailed public school dataset to allocate New Jersey state elementary and secondary school spending to Essex County, Hudson County, Passaic County and Camden County, based on the time periods when the state took over the schools of Newark, Jersey City, Paterson and Camden respectively.  Newark, Jersey City and Paterson schools have been returned to local control, but only after FY 2017.

I group New York State’s counties into four broad regions. The “Downstate Suburbs” are Nassau, Suffolk, Westchester, Rockland, and Putnam Counties.  The “Upstate Urban” counties are Albany, Broome (Binghamton), Dutchess, Erie (Buffalo), Monroe (Rochester), Niagara, Oneida (Utica), Onondaga (Syracuse), Orange, Rensselaer (Troy), Saratoga and Schenectady.  Rural New York is the remaining counties, with their rural areas and small cities and towns. Data for these areas, along with NYC, will be used in bar charts in subsequent posts.   But if one is interested in how individual counties within these areas, and within New Jersey, compare with each other, that information is available in the spreadsheet.

Note the row in the “county area” local government revenue table for the percent of housing units that are second homes.  Many vacation areas, including many counties in Rural New York and on the Jersey Shore, appear to have very high local taxes, especially property taxes, as a percent of their residents’ personal income.  The same may be said of the entire states of Vermont, New Hampshire, and Maine.  But that income, as tabulated by the Bureau of Economic Analysis, only includes the income of these areas’ permanent residents.  Taxes are collected from and services provided to part time residents, with income from elsewhere, as well.  The percent second home is included to allow the viewer to account for this.

While most local government revenues, expenditures and debt within New York City are accounted for by a single local government, the City of New York, that is not common.  In general, within the same area one finds multiple layers of local governments – counties, municipalities, sometimes townships, school districts, and other special districts.  For a fair comparison with New York City, one needs to add all these types of local governments together.

In the past, most of the local government data I have used is from the Census Bureau’s “County Area” files.  Quoting the Bureau’s documentation from the 2012 Census of Governments:

County area data afford the user a more complete picture of government activities than data for any one type of local government.  This is because a service provided by county governments in one state may be provided by a different type of government in another.  For example, Connecticut and Rhode Island do not have county governments, but they do have township governments that provide a wide range of services.  On the contrary, thirty states do not have township governments at all. Thus, by examining the sum of all governments within the geographic area of a county, the services provided by local governments in different states are more comparable than they otherwise would be.  The County Area files also make State and Local government finance data more compatible with other county-based data published by the Census Bureau.

The Bureau had always published this data with caveats.

First, the revenues obtained by governments within a county area may not derive exclusively from the residents of that county area.  For instance, a sizeable portion of sales taxes obtained by local governments in popular tourist destinations would be paid by people who are visiting, but do not live in, that county area.  In a similar vein, the expenditures of local governments may not solely benefit the residents of the county area.  Consider a county in an urban area that supports a large number of people who commute to work from neighboring counties.  Local governments in the urban county would incur expenses on infrastructure used by commuters – water, sewerage, road maintenance, mass transit, and the like – in addition to county area residents.   

Additionally, there are local governments that serve more than one county area.  In these cases, the Census Bureau practice is to assign the total financial information for the government into one county only – typically, the county in which the government’s headquarters is located. For instance, the Washington Metropolitan Transit Authority serves a number of county areas in both Maryland and Virginia, but in our County Area file, all of its finances are included in the District of Columbia’s totals. 

You’ll see the “transit income” data field that I have used, in some cases, to calculate mass transit spending per $1,000 of personal income, though not with the same level of effort and specificity that I have in metro New York.

For the 2017 Census of Governments, however, Bureau has (for the moment) decided not to produce County Area files, along with some other tabulations that were very useful.  Where have you gone Donna Hirsh (a long-retired Census Bureau staffer who produced tabulations like this), the nation turns its lonely eyes to you. It has decided that because many special districts have broader service areas, the county area data is too misleading to publish.

Here is an example.  For FY 2017 local governments in Mecklenburg County NC (Charlotte) had a total of $169.80 in direct expenditures per $1,000 of county residents’ personal income.  The North Carolina local government average was $106.75, and the U.S. average was $110.85.  Charlotte, it would appear, is a big government city.  A closer look, however, shows that Mecklenburg had $85.38 in expenditures in the local government Public Hospital function per $1,000 of personal income, compared with the state average of just $17.34 and the U.S. average of $6.36.  The reason is the Charlotte-Mecklenburg Hospital Authority, an entity that provides hospital and other health services for a broad area of North Carolina, not just Mecklenburg County.

Southern counties tend to be very small, so local governments are more likely to serve more than one of them than is the case elsewhere.

I still believe, however, that having county area comparisons that are imperfect for some data items is better than having no useful comparisons between places and over time at all.

And so, I have downloaded the Bureau’s “Individual Unit” file, with each data item for each local government, and added them up at the county area level using the “sumif” function in Excel.  The result is the “county area” data one sees in these spreadsheets for every county in New York and New Jersey, Fairfield County, and 46 other highly populated urban and suburban counties around the United States. As of the date of this post, I am the only source for this information.

This painstaking process provides a source of possible error. Those using this data should understand that this is a one-person effort, and though I have tried as hard as I can to triple check and correct my work, it may not be perfect.  The source data is also imperfect.  Noted the Bureau five years ago:

The data in these files are not subject to sampling error because the data are from a census, or complete survey, of all local and state governments in the United States.  The data are subject to various non-sampling errors such as nonresponse error (errors caused by governmental units that do not return their completed forms), response error (errors introduced by the survey respondent), processing errors (keying or other errors introduced during the handling of the questionnaires), or coverage errors (errors introduced by not having a complete listing of governmental units).  Census Bureau staff review and edit individual government units to reduce response and processing errors.    Additionally, data for government units that do not respond is imputed in an effort to reduce the effects of nonresponse error.

I was told years ago that cooperation with the U.S. Census Bureau by local governments has been going down as fiscal crises, often generated by public employee pension underfunding, take hold.  Perhaps the Bureau is waiting to publish final data, hoping that more governments will own up to the past and allow it to replace its current estimates with hard data.

While reporting for New York State is close to 100 percent, it seems the accuracy of the data reported is getting worse, particularly for New York City.  After gradually rising to over $31 billion in FY 2016, the Bureau reported New York City’s total local government salaries and wages (code Z00) at just $13 billion in 2017. The state total was not affected, and all the governments in the state did not add to the state total. This is yet another in a series of increasingly suspicious errors in data on the amount of money NYC’s public employees are paid, and the city’s population, that have showed up in Bureau of Economic Analysis and Bureau of Labor Statistics data as well.   BLS Employment and Wages data currently shows for annual local government payroll in New York City.  The number for all the years from 2010 to 2017 had been cut compared with prior reporting, and then 2018 soared.

NYC Local Gov Payroll BLS

I found the same thing with regard to Bureau of Economic Analysis data last fall.

Along with errors in population that only affected New York City – to the point where the different parts of New York State were less than the state total.

At the Bureau’s suggestion, I substituted March payroll data from the employment phase of the Census of Governments, multiplied by 12, for code Z00 in this tabulation of Census of Governments finance data.

Another notable error concerns state government Medical Vendor Payments, code E74, most of which are made under the Medicaid program, in Connecticut.  This fell, according to the current dataset, from $6.05 billion in FY 2016 to just $2.46 billion in FY 2017.  I think I was able to figure out where the error came from.  As that state has faced more and more budget cuts as a result of soaring pension expenditures, advocates for health care spending – in a series of documents I reviewed – have been emphasizing the statecost of the Medicaid, not the entire cost of the program including federal aid, to argue it is a good deal.  So I believe the lower figure for FY 2017 is the state cost alone, not total expenditures.  According to this document…

The total cost of Medicaid was $5.96 billion that year, and by subtraction non-Medicaid medical vendor payments had totaled $200.4 million the year before.  So I replaced the Bureau’s figure for E74 with $6.16 billion.

I lack the interest to track down everything like this on my own time for free, and the Census Bureau lacks the money.  Suffice it to say that the closer local governments are to New York City, where I know what is going on from following it for three decades, the better the data as I present it is.

Finally, with limited government cooperation with, and general public indifference to, the Census of Governments, it has not been modernized very much since the 1950s. This has created big problem with the way it treats pensions and other employee benefits, a soaring share of state and local government spending.

If you have a single function government, such as a school district or a sewer district, it is pretty easy for the Census Bureau to figure out which function its employee benefit costs should be allocated to.  The City of New York, on the other hand, has agencies providing services for just about every local government function there is.

The NYC Comptroller has for decades refused to try to allocate pension, health benefit, and other benefit costs among city agencies in its accounting.  During the Administration of Mayor Bloomberg, the Office of Management and Budget provided this information, at least to some extent, in its “full agency cost” tables.  The DeBlasio Administration gradually cut back those tables, and now that it is believed that they can get away with anything, has apparently eliminated the table altogether.  The worse a deal the serfs are getting, the greater the temptation of public officials to deceive, just as in the corporate sector, and no one (else) is calling them out on it.

To the extent that they show up at all in Census of Governments data, many of the City of New York’s benefit costs are included under code E89, Other and Unallocable.  According to the Bureau’s classification manual this category includes lump-sum contributions for employee benefits (retirement, unemployment and workers’ compensation, health and life insurances, etc.) other than transfers to own insurance trusts; premiums for government-wide fire, auto, liability, and other such insurances; judgments and compensation for injury to persons or property, in addition to very small functions in a variety of categories.

With 2.6% of the U.S. population, New York City accounted for 13.9% of U.S. local government spending in E89 for FY 2017.  That is up from 12.0% in 2007 and 11.0% in FY 1997. NYC’s share of the U.S. population is down from 2.7% in 2007 and 2.9% in 1997.

Now that you know about what it is you will be seeing, here is a spreadsheet of selected state government revenues and expenditures per $1,000 of personal income for FY 1997, FY 2007, and FY 2017, for the U.S., New York State, New Jersey, Connecticut, and selected other states.


Here is a table of local government revenues for those same states, and for New York City and the Rest of New York State separately.


And here is the same spreadsheet, but with data was well for the Downstate Suburbs, Upstate Urban Counties, Rural NY and Fairfield County, from “County Area calculations.”


For the same states and areas, here are local government expenditure tables for all three years.



These are the states and areas I have selected.  But anyone can use these tables as templates and substitute the data for any states, and any of the counties for which I have re-created the County Area file.  Just delete the data and area name that is there now.  And edit-copy-paste special-values and number formats the exact same data to the exact same spots from the large spreadsheet for all areas for FY 2017 linked earlier.


And these identical spreadsheets for FY 1997 and FY 2007:



The subject-by-subject posts will follow, starting with taxes. But for those who have read so far, here is my spoiler.  In terms of state and local government priorities, whatever interests already had more to start with, compared with other places, have taken more still. The tax burden has increased in New York, and fallen in low tax states.  In those low-tax states, public service advocates and employee unions have been begging to be cut less, while in high-tax states they keep demanding more and more and more – often without any expectation of anything in return.  Entitlement just, it seems, just fuels more entitlement, everywhere in the country.   With regard to state and local government, the unwillingness to let the serfs know the actual situation and face the facts is absolutely bi-partisan, especially in places that are outliers, one way or the other.

Why there hasn’t been more of a pushback, or perhaps even an outright rebellion, I don’t know.  But a lack of comparative information certainly doesn’t help.  That is something I have tried do something about.  After all my efforts, please download the spreadsheets and see for yourself.


This is actually my fifth compilation of data on state and local government over the past year-plus.  First, I used the annual Census Bureau finances data available by state (and for New York City and the Rest of New York State separately) each year since the 1970s to measure the extent to which each state’s future has been sold out by state and local government debts, inadequate past infrastructure investment, and underfunded and/or retroactively increased public employee pensions. The first post in that four-post series, with data through FY 2016, is here.

Detailed discussions of debt, infrastructure and pensions, followed by an overall “Sold Out Future” ranking, followed in subsequent posts.

The Census Bureau also produces a more timely, and more detailed, compilation of data specifically on public elementary and secondary school finances. I analyzed that data for FY 2017, 2007 and 1997 in a two-post sequence starting with this one.

Spreadsheets with data for every school district in New York and New Jersey are included, compared with the U.S. average and other places, with per-student revenues and expenditures by category, with and without an adjustment for the higher cost of living here.

More detailed data on mass transit finance is also available from the federal government, as part of the National Transit Database from the Federal Transit Administration.  I compared FY 2018 with FY 2008 here.

I also compiled data from the employment phase of the 2017 (and 2007 and 1997) Census of Governments, along with related private sector data from the Bureau of Labor Statistics, and published a series of posts on public and related private employment by function per 100,000 area residents, starting with this one.

I’ll make an effort to try to match the color scheme for different functions in the employment analysis with the finance charts that will be produced over the next few weeks.

1 thought on “Background and Databases: 2017 Census of Governments Finance Data

  1. Pingback: State & Local Taxes: 2017 Census of Governments Finance Data | Saying the Unsaid in New York

Comments are closed.