Tag Archives: Visualizations

Following are visualizations of data. Many are interactive and created using Tableau Public. In some cases I’ve recorded myself using the visualization to tell a story, and all you have to do is watch.

Updated: Kansas tax receipts

Kansas tax receipts by category, presented in an interactive visualization.

The Kansas Division of the Budget publishes monthly statistics regarding tax collections. I’ve gathered these and present them in an interactive visualization. Updated with data through October 2018.

Click here to learn more and access the visualization.

Kansas school salaries

An interactive visualization of Kansas school salaries by district and category.

This visualization holds salaries of Kansas school superintendents, principals, and teachers. The visualization shows the average for each of these categories for each school district. The values are adjusted for inflation to the most current year values. Some data is presented on a per-pupil basis using full-time equivalent student counts.

The visualization includes both tables and charts. The source of the data is Kansas State Department of Education for salaries and enrollments, United States Bureau of Labor Statistics for price levels, and author’s calculations.

Click here to access the visualization.

Kansas school salaries. Click for larger.
Kansas school salaries on a per-student basis. Click for larger.
Example from the visualization. Click for larger.

Visualization: Kansas school salaries

An interactive visualization of Kansas school salaries by district and category.

This visualization holds salaries of Kansas school superintendents, principals, and teachers. The visualization shows the average for each employee category for each school district. The visualization includes both tables and charts. Some data is presented on a per-pupil basis using full-time equivalent student counts.

The values are adjusted for inflation to the most current year values.

KSDE says this data includes contracted salaries, including fringe benefits. More information from KSDE is available for superintendents, principals, and teachers.

Salary data is from Kansas State Department of Education. Inflation-adjusted data calculated using Consumer Price Index, all items, 1982-84=100 (series CUUR0000SA0) from U.S. Bureau of Labor Statistics. Visualization created using Tableau Public.

Click here to use the visualization.

For more visualizations, click here.

Example from the visualization. Click for larger.

GDP by metropolitan area and component

An interactive visualization of gross domestic product by metropolitan area and industry.

The Bureau of Economic Analysis, an agency of the United States Department of Commerce, gathers data about economic output, known as gross domestic product. The visualization presented here presents this data in tabular and graphic form.

Wichita and national GDP. Click for larger.
The GDP figures are real, meaning adjusted for inflation. They are annual numbers through 2017. The release this week also includes revisions for the prior year. In the case of Wichita, the revision was significant, with a loss in GDP being revised to a gain. See Wichita economy shrinks, and a revision for details.

A nearby example from the visualization compares Wichita metro GDP growth to that of the nation’s metropolitan areas.

Click here to access this visualization.

Example from the visualization. Click for larger.
Example from the visualization. Click for larger.

Visualization: GDP by metropolitan area and component

An interactive visualization of gross domestic product by metropolitan area and industry.

The Bureau of Economic Analysis, an agency of the United States Department of Commerce, gathers data about economic output, known as gross domestic product. The visualization presented here presents this data in tabular and graphic form.

The GDP figures are real, meaning adjusted for inflation. They are annual numbers through 2017.

Source of data is Bureau of Economic Analysis, an agency of the United States Department of Commerce.

Click here to access this visualization.

For more visualizations, click here.

Example from the visualization. Click for larger.
Example from the visualization. Click for larger.

Kansas tax receipts

Kansas tax receipts by category, presented in an interactive visualization.

The Kansas Division of the Budget publishes monthly statistics regarding tax collections. I’ve gathered these and present them in an interactive visualization.

In the nearby example from the visualization, we can see the rising trend in individual income taxes, due to the tax increase passed by the Kansas Legislature.

Click here to learn more and access the visualization.

Example from the visualization. Click for larger.

State government employees in Kansas

Kansas has more state government employees per resident than most states, and the trend is rising.

Each year the United States Census Bureau surveys federal, state, and local government civilian employees. 1 The amount of payroll for a single month (March) is also recorded. In this case, I’ve made the data for state government employees available in an interactive visualization.

For 2016, Kansas had 17.90 full-time equivalent state government employees per thousand residents. This ranked 15th among the states. These employees resulted in payroll cost of $979 per resident, which is 21st among the states.

Nearby is an example from the visualization showing state government employment count (full-time equivalent) per thousand residents for Kansas and some nearby states. It shows total employment, and in addition, education employment and hospital employment. (Since nearly all employees in Kansas elementary and secondary schools are employees of local government, not the state, the employees shown are working in higher education. See below for visualizations of local government employees.)

Two things are evident: The level of employment in Kansas is generally higher than the other states, and the trend in Kansas is rising when many states are level or declining. This data counters the story often told, which is that state government employment has been slashed.

If we look at data for state and local government employees, the conclusions are nearly the same.

Click here to learn more and access the visualization.

There are separate visualizations for local government employees only, and also for state and local government employees together. Click on state and local government employment of local government employment by state and function.

Example from the visualization, showing Kansas and other states. Click for larger.


Notes

  1. United States Census Bureau. Annual Survey of Public Employment & Payroll (ASPEP). Available at https://www.census.gov/programs-surveys/apes.html.

Visualization: State government employment by state and function

An interactive visualization of state government employment, grouped by state and function.

These are state government employees only. Local and federal government employees are not included.

Source of data is United States Census Bureau, Local Government Employment and Payroll Data: March 2016. The program’s page is Annual Survey of Public Employment & Payroll (ASPEP).

In the visualization, I’ve multiplied the March payroll number by 12 to produce an approximation of annual payroll. Using each state’s population for each year, I’ve also computed the annual payroll on a per-resident basis and the number of full-time equivalent (FTE) employees per thousand residents.

Click here to access the visualization.

For more visualizations, click here.

Example from the visualization. Click for larger.

Kansas state and local taxes

Among nearby states, Kansas collects a lot of taxes, on a per-resident basis.

The United States Census Bureau collects data from the states regarding tax collections. Some data is available for each quarter subdivided by category.

From the first quarter of 2011 to the first quarter of 2018, Kansas and its local governmental units collected an average of $681 per quarter per resident in taxes. Of nearby states and a few others, Arkansas and Iowa had higher values, and Iowa is higher by only one percent.

Some states had lower values, such as Colorado at $565 per quarter per resident (17.0 percent less than Kansas), Texas and Missouri both at $486 (28.6 percent less), and Florida at $470 (31.0 percent less).

To learn more about this visualization and create your own, click here.

Visualization: Quarterly state and local government tax collections

State and local tax collections presented in an interactive visualization.

The United States Census Bureau provides tax collection data for states and local governments on a quarterly basis. The data is provided by category such as total taxes, sales taxes, personal income taxes, etc. 1

The Bureau describes the program as:

The Quarterly Summary of State and Local Government Tax Revenue provides quarterly estimates of state and local government tax revenue at a national level, as well as detailed tax revenue data for individual states. The information contained in this survey is the most current information available on a nationwide basis for government tax collections.

While the state data records are ultimately from state government sources, the classification of taxes among the different categories is entirely the responsibility of the Census Bureau. Therefore, tax classification might not reflect the actual classification or presentation as requested by the various state government respondents. 2

This data is not adjusted for inflation. Some taxes, such a property taxes, show large seasonal variations.

I’ve gathered the data and have made it available in an interactive visualization. There are several views of the data, represented by the tabs at the top. You may select a time frame, the states that appear, and the tax categories that appear.

Click here to access the visualization.

For more visualizations, click here.

Example from the visualization. Click for larger.


Notes

  1. United States Census Bureau. Quarterly Summary of State & Local Tax Revenue (QTAX). Available at https://www.census.gov/programs-surveys/qtax.html.
  2. United States Census Bureau. About Quarterly Summary of State & Local Tax Revenue. Available at https://www.census.gov/programs-surveys/qtax/about.html.

Wichita checkbook updated

Wichita spending data presented as a summary, and as a list.

As part of an ongoing transparency project, I asked the City of Wichita for check register data. I’ve made the data available in a visualization using Tableau Public. This visualization is updated with data through August 31, 2018.

To learn more about this data and use the visualization, click here.

Example from the visualization. Click for larger.

Visualization: Wichita check register

Wichita spending data presented as a summary, and as a list.

As part of an ongoing transparency project, I asked the City of Wichita for check register data. I’ve made the data available in a visualization using Tableau Public. Click here to access the visualization. To access a simple list in csv format, click here.

For more visualizations, click here.

Analyzing this data requires a bit of local knowledge. For example, there is a vendor named “Visit Wichita” that started to receive monthly payments in March 2015. What about payments for January and February? Those were made to a vendor named “Go Wichita,” which changed its name to “Visit Wichita.”

Similarly, there are payments made to both “Westar Energy” and “Westar Energy — EDI.” These are the same entities, just as “Visit Wichita” and “Go Wichita” are the same entity. To the city’s credit, the matching pairs have the same vendor number, which is good. But resolving this requires a different level of analysis.

Also, the purpose of payments may not be evident from the payee’s name. For example, “State Street” is the payee that has received the most money over the time period covered by this data. It is a custodial bank for the city’s retirement systems. 1

Of note, there are many checks issued in amounts $20 or less. Bank of America has estimated that the total cost of sending a business check ranges from $4 to $20. 2

It is by now routine for governmental agencies to post spending data like this, but not at the City of Wichita. Upon inquiry, city officials told me that the present financial management system “does not include many modern system features such as an ‘open checkbook.’” An “open checkbook” refers to a modern web interface where citizens can query for specific data and perhaps perform other analysis. An example is Denver’s open checkbook.

We’ve been promised a modern system for many years.

While the next-generation Wichita financial system will probably have such a feature, there’s no reason why citizens can’t experience some of the benefits now. The spreadsheet of spending data could easily be posted on the city’s website on a monthly basis. People like myself will take that data and make it more useful. The city has demonstrated that it is able to post documents to its website, so there is no reason why this should not be happening.

Example from the visualization. Click for larger.


Notes

  1. City of Wichita. Comprehensive Annual Financial Report. Available at http://www.wichita.gov/Finance/PensionDocuments/2015%20Pension%20CAFR.pdf.
  2. Wall Street Journal. U.S. Companies Cling to Writing Paper Checks. Available at https://www.wsj.com/articles/u-s-companies-cling-to-writing-paper-checks-1394494772.

Local government employment in Kansas

Kansas has nearly the highest number of local government employees per resident, compared to other states.

Local government employment by state. Click for larger.
Local government employment in education. Click for larger.
For all local government employees, Kansas had 50.59 per thousand residents in 2016, higher than all states (and areas) but the District of Columbia and Wyoming. These employees had an annual payroll of $2,141.16 per resident. Ten states were higher.

Considering elementary and secondary education, Kansas had 30.03 such employees per thousand residents. This was higher than all states but Vermont and Wyoming. The payroll for these employees was $1,150.85 per resident, with eleven states above Kansas.

Kansas is a small state in terms of population. Might small states have higher needs for employees on a per-resident basis? A plot of employees vs. population shows nearly no relationship between the two.

These are local government employees only. State and federal government employees are not included.

Of note, Hawaii has no local employees in elementary and secondary education, as it has one school district which is run by the state. 1

The source of this data is the United States Census Bureau. I’ve gathered it and placed in in an interactive visualization. Click here to learn about the visualization and use it to make your own charts and tables.

State population vs. local government employment per resident. Click for larger.

— Notes

  1. Wikipedia. Hawai’i Department of Education. Available at https://en.wikipedia.org/wiki/Hawai%27i_Department_of_Education.

Visualization: Local government employment by state and function

An interactive visualization of local government employment, grouped by state and function.

These are local government employees only. State and federal government employees are not included.

Source of data is United States Census Bureau, Local Government Employment and Payroll Data: March 2016. The program’s page is Annual Survey of Public Employment & Payroll (ASPEP).

In the visualization, I’ve multiplied the March payroll number by 12 to produce an approximation of annual payroll. Using each state’s population for each year, I’ve also computed the annual payroll on a per-resident basis and the number of full-time equivalent (FTE) employees per thousand residents.

Click here to access the visualization.

For more visualizations, click here.

Example from the visualization. Click for larger.

Visualization: School spending in the states

School spending in the states, presented in an interactive visualization.

The Elementary/Secondary Information System (ElSi) is a project of National Center for Education Statistics (NCES). NCES is “the primary federal entity for collecting and analyzing data related to education in the U.S. and other nations. NCES is located within the U.S. Department of Education and the Institute of Education Sciences.”1 Here is data from ElSi regarding per-pupil revenue and spending in the states.

Near the end of this article are definitions of each measure. There are measures for total expenditures and total current expenditures. The major difference is that the current expenditures measure does not include the cost of construction of schools and the expense of debt associated with that.

Of note, the values for “United States” are the average of the values for the states, computed with equal weight without regard for the total spending or number of students in each state.

As of the date of publication, data was available through the school year ending in 2015.

Since these data series cover substantial periods of time, I’ve also used the Consumer Price Index2 to adjust the figures for the effects of inflation. Each measure has a companion whose name starts with “i.” This is the value adjusted for inflation, based on the CPI. You may choose to view the values as reported by ElSi, which are in current dollars. These are the values not adjusted for inflation. Or, you may use the “i.” measures, which are in constant dollars.3

This data is presented in an interactive visualization created using Tableau Public. There are three views of this data, accessed by tabs along the top. To access the visualization, click here.

For more visualizations, click here.

School spending in Kansas and the United States. Click for larger.
School spending in Kansas and the United States. Click for larger.

Definitions of measures

Total Revenues (TR) per Pupil (MEMBR) [State Finance]
Total revenues per student ate the total revenues from all sources (tr) divided by the fall membership as reported in the state finance file.

Total Expenditures (TE11+E4D+E7A1) per Pupil (MEMBR) [State Finance]
This is the Total Expenditures (Digest) divided by the fall membership as reported in the state finance file. The Total Expenditures (Digest) is the subtotal of Direct State Support Expenditures for Private Schools (e4d).

Total Current Expenditures for Public El-Sec (TE5) per Pupil (MEMBR) [State Finance]
This is the total current expenditures for public elementary and secondary education (te5) divided by the fall membership as reported in the state finance file. The Expenditures for equipment, non-public education, school construction, debt financing and community services are excluded from this data item.

Local Revenues (STR1+R2) per Pupil (MEMBR) [State Finance]
Local revenues per student are the total of all local revenue categories (strl and r2) divided by the fall membership as reported in the state finance file. Local revenues are raised and allocated by local governments.

State Revenues (R3) per Pupil (MEMBR) [State Finance]
State revenues per student are revenues received by the LEAs from the state (r3). divided by the fall membership as reported in the state finance file.

Federal Revenues (STR4) per Pupil (MEMBR) [State Finance]
Federal revenues per student are federal revenues (str4) divided by the fall membership as reported in the state finance file.


Notes

  1. National Center for Education Statistics. About us. nces.ed.gov/about/.
  2. United States Bureau of Labor Statistics. Consumer Price Index. www.bls.gov/cpi/.
  3. The U.S. Census Bureau explains: ” Constant-dollar values represent an effort to remove the effects of price changes from statistical series reported in dollar terms. The result is a series as it would presumably exist if prices were the same throughout as they were in the base year-in other words, as if the dollar had constant purchasing power.” Current versus Constant (or Real) Dollars. www.census.gov/topics/income-poverty/income/guidance/current-vs-constant-dollars.html.

Visualization: Kansas school spending by district

An interactive visualization of spending by Kansas school districts.

The accompanying visualization holds both nominal dollar amounts and amounts adjusted for inflation to reflect 2018 dollars. Data includes state aid, local aid, federal aid, and total spending for each school district, per pupil. The visualization includes both tables and charts.

Spending and revenue data is from Kansas State Department of Education. Inflation-adjusted data calculated using Consumer Price Index, all items, 1982-84=100 (series CUUR0000SA0) from U.S. Bureau of Labor Statistics. Values are adjusted to 2018. Visualization created using Tableau Public.

Click here to use the visualization.

For more visualizations, click here.

Visualization: Kansas school employment

Kansas school employment and ratios to students in an interactive visualization. Updated through 2018.

Here are certified Kansas school employees by district presented in an interactive visualization. There are several views that express the data in different ways. The source of data is Kansas State Department of Education. 1

To access the visualization, click here. For more visualizations, click here.

According to KSDE, certified employees include: “All certified personnel, calculated at full-time equivalency (FTE). This includes superintendents, associate/assistant superintendents, administrative assistants, principals, assistant principals, directors/supervisors of special education, directors/supervisors of health, directors/supervisors of vocational education, instructional coordinators/ supervisors, all other directors/supervisors, other curriculum specialists, practical arts/ vocational teachers, special education teachers, pre-kindergarten teachers, kindergarten teachers, all other teachers, library media specialists, school counselors, clinical or school psychologists, nurses, speech pathologists, audiologists, school social work services, reading specialists/teachers, and others. 2

PK-12 Teachers include: “Practical arts/vocational teachers, kindergarten teachers, pre-kindergarten teachers, reading specialists/teachers, and all other teachers, calculated at full-time equivalency. 3

These are not the only employees of school districts. 4


Notes

  1. Kansas State Department of Education. School Finance Reports, Selected School Statistics — District Totals. Available at https://datacentral.ksde.org/school_finance_reports.aspx.
  2. Kansas State Department of Education. About the Selected School Statistics – District Report (PDF). Available at http://www.ksde.org/Portals/0/School%20Finance/SelectedStats/SelSchStats_Dist%20Intro.pdf.
  3. Ibid.
  4. There are also, according to KSDE, non-certified employees, which are Assistant Superintendents, Business Managers, Business Directors/Coordinators/Supervisors, Other Business Personnel, Maintenance and Operation Directors/Coordinators/Supervisors, Other Maintenance and Operation Personnel, Food Service Directors/Coordinators/Supervisors, Other Food Service Personnel, Transportation Directors/Coordinators/Supervisors, Other Transportation Personnel, Technology Director, Other Technology Personnel, Other Directors/Coordinators/Supervisors, Attendance Services Staff, Library Media Aides, LPN Nurses, Security Officers, Social Services Staff, Regular Education Teacher Aides, Coaching Assistant, Central Administration Clerical Staff, School Administration Clerical Staff, Student Services Clerical Staff, Special Education Paraprofessionals, Parents as Teachers, School Resource Officer, and Others. See Kansas State Department of Education. Non-Certified Personnel Report. http://www.ksde.org/Portals/0/School%20Finance/reports_and_publications/Personnel/NonCertPer%20Cov_St%20Totals.pdf.

Visualization: Economic indicators in the states

A visualization of coincident and leading economic indicators for the states.

The Federal Reserve Bank of Philadelphia calculates two indexes that track and forecast economic activity in the states and the country as a whole.

The coincident index is a measure of current and past economic activity for each state. This index includes four indicators: nonfarm payroll employment, the unemployment rate, average hours worked in manufacturing, and wage and salary disbursements deflated by the consumer price index (U.S. city average). The average value for the complete year 2007 is given the value 100. 1

The leading index predicts the six-month growth rate of the state’s coincident index. In addition to the coincident index, “the models include other variables that lead the economy: state-level housing permits (1 to 4 units), state initial unemployment insurance claims, delivery times from the Institute for Supply Management (ISM) manufacturing survey, and the interest rate spread between the 10-year Treasury bond and the 3-month Treasury bill.” 2

Positive values mean the coincident index is expected to rise in the future six months, while negative values mean it is expected to fall.

I’ve created an interactive visualization of these two indexes. Click here to open the visualization in a new window.

For more visualizations, click here.


Notes

  1. Federal Reserve Bank of Philadelphia. State Coincident Indexes – a monthly coincident index for each of the 50 states. Philadelphiafed.org. Available at www.philadelphiafed.org/research-and-data/regional-economy/indexes/coincident.
  2. Federal Reserve Bank of Philadelphia. State Leading Indexes – current & future economic situation of 50 states with special coverage of Pennsylvania, New Jersey, & Delaware. Philadelphiafed.org. Available at www.philadelphiafed.org/research-and-data/regional-economy/indexes/leading.

Visualization: National transit database

An interactive visualization of data over time from the National Transit Database.

Do you wonder how much it costs to run your transit system? The National Transit Database holds data for transit systems in the U.S. I’ve gathered some key statistics and presented them in an interactive visualization.

In the case of Wichita, we see that “OpExp per PMT” for 2015 is $1.02. This is total operating expense per passenger mile traveled. It’s not the cost to move a bus a mile down the street. It’s the cost to move one passenger one mile. And, it is operating cost only, which means the costs of the buses are not included.

Some definitions used in the database:

  • UZA: The name of the urbanized area served primarily by a transit agency.
  • UPT: Unlinked passenger trips.
  • PMT: Passenger miles traveled.
  • Total OpExp: Total operating expense.

The visualization holds three tabs. One is a table of figures. The other two illustrate data for a single transit system or single mode.

Click here to access the visualization.

For more visualizations, click here.

Example from the visualization for Wichita. Click for larger.

State and local direct general expenditures, per resident

An interactive visualization of state and local direct general expenditures, per resident.

Data is from State & Local Government Finance Data Query System, available at slfdqs.taxpolicycenter.org/pages.cfm. The Urban Institute-Brookings Institution Tax Policy Center. Data from U.S. Census Bureau, Annual Survey of State and Local Government Finances, Government Finances, Volume 4, and Census of Governments (1977-2015). Date of Access: (16-Oct-2017). Data is not adjusted for inflation.

Click here to use the visualization.

For more visualizations, click here.