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.

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 2013.

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. To access the visualization, click here. There are three views of this data, accessed by tabs along the top.

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.

School staffing and students

Trends for the nation and each state in teachers, administrators, and students, presented in an interactive visualization.

Each year states report data to the National Center for Education Statistics. While NCES provides methods for extracting data, it isn’t an easy process, and opportunities to produce charts are limited. Here I present trends in teachers, administrators, and students for each state from 1998 to the school year ending in summer 2014, the most recent year of data that is available.

For each state, the charts show the growth in teachers, administrators, and students. For both teachers and students, the value used is full-time equivalency. A table also shows pupil/teacher ratio and pupil/administrator ratio.

There are some obvious mistakes in the data. An example is the number of administrators reported for Kansas for years 2007 through 2009. Figures obtained directly from Kansas State Department of Education show no sudden drop and increase in the count of administrators. Nonetheless, I have presented the data as retrieved from NCES.

Sorting on columns.
Sorting on columns.
For the nation as a whole, the count of students has increased 8.5 percent since 1998. The count of teachers (full-time equivalent) rose by 13.4 percent, and the number of administrators by 19.4 percent. Individual states vary widely, with many having increased administrators at a far faster pace than either students or teachers. Some states, however, have reduced the number of administrators, or the rate has grown slower than students and teachers.

Click here to open and use the visualization.

Data is from the Elementary/Secondary Information System (ElSi) at National Center for Education Statistics, part of the U.S. Department of Education and the Institute of Education Sciences. The number of administrators is calculated as the sum of “LEA Administrators” and “LEA Administrative Support Staff.” LEA Administrators is defined by NCES as “The count of Local education agency superintendents, deputy and assistant superintendents, and other persons with district-wide responsibilities such as business managers and administrative assistants. Excludes supervisors of instructional or student support staff.” LEA Administrative Support Staff is defined as “The count of Staff members who provide direct support to LEA administrators, including secretarial and other clerical staff.”

Using the visualization. Click for larger.
Using 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. These figures have been gathered and are presented in an interactive visualization.

Example from the visualization.
Example from the visualization.
For the past two years, individual income tax collections have been relatively flat. There are variations each month, but overall the trend is slightly up. Corporate income tax collections are on a slight downward trajectory.

Retail sales tax and compensating use tax have been rising for two years. A higher sales tax rate took effect on July 1, 2015, with the rate rising from 6.15 percent to 6.50 percent.

Cigarette taxes have risen rapidly since July 2015 when higher tax rates on these products took effect. The same trend is present in the tobacco products tax.

Severance taxes — tax collected on natural gas and oil as it is extracted from the ground — have been on a downward trend as prices for these produces have fallen. This is a sizable tax. In June 2014 collections of this tax were running at about $143 million per year. Two year later the rate is $28 million annually.

Click here to use the visualization.

Source of data is Kansas Division of the Budget.

State and local government employee and payroll

Considering all state and local government employees in proportion to population, Kansas has many, compared to other states, and especially so in education.

State and Local Government Employee and Payroll. Click for larger.
State and Local Government Employee and Payroll. Click for larger.
When considering all state and local government employees, Kansas spent $249.73 per person on payroll (March only).1 This was 15th highest among the states, District of Columbia, and nation as a whole. There were 14.9 citizens for each FTE (full-time equivalent employee), which ranks fourth highest.

In other words, Kansas has many government employees compared to other states, and these employees are costly, again compared to other states.

When considering all elementary and secondary education employees, Kansas spent $93.36 per person on payroll (March only). This was 15th highest among the states, District of Columbia, and nation as a whole. There were 33.8 citizens for each FTE (full-time equivalent employee), which ranks third highest.

In other words, Kansas has many elementary and secondary education employees compared to other states, and these employees are costly, again compared to other states.

Similar results are found for higher education employees. Fortunately, Kansas has zero employees working in state-owned liquor stores.

In the visualization you may create your own tables. Click here to access the visualization. Source of data is U.S. Census Bureau.2 Visualization created using http://www.census.gov/govs/apes/.


Notes

  1. For total payroll (both full-time and part-time employees), the Census Bureau reports a value for a single month, that being March.
  2. U.S. Census Bureau. 2014 Annual Survey of Public Employment and Payroll. www.census.gov/govs/apes/.

State and local direct general expenditures

State and Local Direct General Expenditures, Per Capita, Kansas highlighted.
State and Local Direct General Expenditures, Per Capita, Kansas highlighted.
An interactive visualization of state and local direct general expenditures, per capita. Click here to open the visualization.

Data is from State & Local Government Finance Data Query System. Available here. 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-2013). Date of Access: (20-Jun-2016).

GDP by state and industry

An interactive visualization of gross domestic product by state and industry.

The Bureau of Economic Analysis, an agency of the United States Department of Commerce, has released Gross Domestic Product figures for the year 2015. I’ve gathered this data and present in it an interactive visualization using Tableau Public. This data is grouped by states and regions, and also by major categories of industry.

Source of data is Bureau of Economic Analysis, Regional Economic Accounts. Values are in current dollars.

Of note: The list of industries is not exclusive. That is, some categories such as “All industry total” and “Private industries” contain other categories. Use caution when selecting multiple categories.

Click here to access the visualization.

GDP by State and Industry example 2016-06

Kansas state tax collections, compared

An interactive visualization of tax collections by state governments shows Kansas distinguished from some of its neighbors.

Per-capita tax collections, Kansas and nearby states. Click for larger.
Per-capita tax collections, Kansas and nearby states. Click for larger.
As shown in the nearby illustration, Kansas collects more taxes than some nearby states, on a per-person basis. This information should guide Kansas legislators and policymakers and Kansas prepares to balance its budget. Does Kansas want to further separate itself from its neighbors with even higher taxes?

The values are from the United States Census Bureau, and are for tax collections by the state only. Local governmental entities like cities, counties, townships, improvement districts, cemetery districts, library districts, drainage districts, watershed districts, and school districts are not included.

You may use this interactive visualziaton to prepare your own analysis and illustrations. Of particular interest is the “State Total” tab. Here you can select a number of states and compare their tax burdens. (Probably three or four states at a time is the practical limit.)

Data is as collected from the United States Census Bureau, Annual Survey of State Government Tax Collections, and not adjusted for inflation. Visualization created using Tableau Public. Click here to access the visualization.

Using the visualization. Click for larger.
Using the visualization. Click for larger.

GDP by state and industry

An interactive visualization of gross domestic product by state and industry from the Bureau of Economic Analysis.

Composition of GDP by State, Kansas and U.S. Click for larger version.
Composition of GDP by State, Kansas and U.S. Click for larger version.
The Bureau of Economic Analysis is an agency of the United States Department of Commerce. BEA describes its role as “Along with the Census Bureau, BEA is part of the Department’s Economics and Statistics Administration. BEA produces economic accounts statistics that enable government and business decision-makers, researchers, and the American public to follow and understand the performance of the Nation’s economy. To do this, BEA collects source data, conducts research and analysis, develops and implements estimation methodologies, and disseminates statistics to the public.”

A relatively new series of data produced by BEA is gross domestic product (GDP) by state for 21 industry sectors on a quarterly basis. BEA defines GDP as “the value of the goods and services produced by the nation’s economy less the value of the goods and services used up in production.” It is the value of the final goods and services produced.

In describing this data, BEA says “These new data provide timely information on how specific industries contribute to accelerations, decelerations, and turning points in economic growth at the state level, including key information about the impact of differences in industry composition across states.” This data series starts in 2005.

I’ve gathered the data for this series for all states and present it in an interactive visualization using Tableau Public. I present the series in real dollars, meaning that BEA adjusted the numbers to account for changes in the price level, or inflation.

Click here to open the visualization.

Growth of gross domestic product of manufacturing for Kansas and the United States, illustrating volatility in Kansas. Click for larger.
Growth of gross domestic product of manufacturing for Kansas and the United States, illustrating volatility in Kansas. Click for larger.

Kansas school salaries

Kansas school salaries for superintendents, principals, and teachers presented in an interactive visualization for each district, updated for 2016 data.

Recently Kansas Policy Institute noted the discrepancy in salary increases for Kansas public school management as compared to teachers. See Pay raises to superintendents and principals far outpace those to teachers.

In the article, David Dorsey writes: “A widely-shared solution to improving student outcomes is to put more money in the classroom. What does it say about the importance of student achievement to local school boards and administrations when pay increases are disproportionately higher to those who are not in the classroom?”

And later: “Much has been documented about teacher shortages, especially due to those leaving after only a few years in the profession. One way to reverse that trend would be for districts to make spending choices that would support the commitment to keeping quality teachers.”

Kansas State Department of Education has released salary figures for districts for the current school year, fiscal year 2016. Statewide, since 2008, the KSDE data shows these cumulative salary increases:

Superintendents: 12.2 percent
Principals: 11.8 percent
Teachers: 8.8 percent

If we start the comparison in 2009 the difference is larger, with increases of 8.2 percent for principals and 4.9 percent for teachers.

It’s also useful to look at individual districts. For example, for the Wichita public school district, there are these cumulative salary increases since 2008:

Superintendent: 53.9 percent
Principals: 7.0 percent
Teachers: 2.3 percent

The Wichita district has just one superintendent, so no matter how much the salary rises, it’s still the salary for just a single person and has a negligible effect on total district payroll costs. There are, however, 89 principals, so the increase for this category of employee matters much more.

But you have to wonder: What about the teachers?

I’ve gathered the data and present it in an interactive visualization. You may select any single district, or use district 999 for statewide totals. Click here to open the visualization in a new window. Data is from Kansas State Department of Education. Figures include fringe benefits and are not adjusted for inflation. Visualization created using Tableau Public. There are several missing values which can make the percentage change invalid for a single year.

Kansas school salaries. Click for larger.
Kansas school salaries. Click for larger.

Economic indicators in the states

During this century the Kansas economy has not kept up with the national economy and most neighboring 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.

Economic Indicators in the States ExampleThe 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). July 1992 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. Examples appear nearby. Click here to open the visualization in a new window.

  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.

Tax collections by the states

An interactive visualization of tax collections by state governments.

Each year the United States Census Bureau collects data from the states regarding tax collections in various categories. I present this data starting in 1993, in an interactive visualization.

The values are for tax collections by the state only, not local governmental entities like cities, counties, townships, improvement districts, cemetery districts, library districts, drainage districts, watershed districts, and school districts.

Of particular interest is the “State Total” tab. Here you can select a number of states and compare their tax burdens. (Probably three or four states at a time is the practical limit.)

Data is as collected from the United States Census Bureau, Annual Survey of State Government Tax Collections, and not adjusted for inflation. Visualization created using Tableau Public. Click here to access the visualization.

Using the visualization. Click for larger.
Using the visualization. Click for larger.

Spending in the states, per capita

An interactive visualization of per-capita spending in the states, by fund.

Is your state a big spender? Or is it frugal with your tax money? To see how your state compares with others in spending, use the interactive visualization below. The figures presented are per-person, and not adjusted for inflation.

The example appearing below shows general fund spending for Kansas and some surrounding states.

The visualization contains several views that present the data in different ways. Click here to open the visualization in a new window. Data is from National Association of State Budget Officers and U.S. Bureau of Economic Analysis (BEA); visualization created by myself using Tableau Public.

General fund spending per capita in Kansas and surrounding states. Click for larger.
General fund spending per capita in Kansas and surrounding states. Click for larger.

Kansas school salaries

Kansas school salaries for superintendents, principals, and teachers presented in an interactive visualization for each district.

Recently Kansas Policy Institute noted the discrepancy in salary increases for Kansas public school management as compared to teachers. See Pay raises to superintendents and principals far outpace those to teachers.

In the article, David Dorsey writes: “A widely-shared solution to improving student outcomes is to put more money in the classroom. What does it say about the importance of student achievement to local school boards and administrations when pay increases are disproportionately higher to those who are not in the classroom?”

And later: “Much has been documented about teacher shortages, especially due to those leaving after only a few years in the profession. One way to reverse that trend would be for districts to make spending choices that would support the commitment to keeping quality teachers.”

Statewide, since 2009, KSDE data shows these cumulative salary increases:

Superintendents: 7.9 percent
Principals: 7.4 percent
Teachers: 3.9 percent

It’s also useful to look at individual districts. For example, for the Wichita public school district, there are these cumulative salary increases since 2009:

Superintendent: 39.9 percent
Principals: 4.7 percent
Teachers: -0.8 percent, a decline

The Wichita district has just one superintendent, so no matter how much the salary rises, it’s still the salary for just a single person and has a negligible effect on total district payroll costs. There are, however, 89 principals, so the increase for this category of employee matters much more.

But you have to wonder: What about the teachers?

I’ve gathered the data and present it in an interactive visualization. You may select any single district, or use district 999 for statewide totals. Click here to open the visualization in a new window. Data is from Kansas State Department of Education. Figures include fringe benefits. Visualization created using Tableau Public. There are several missing values which can make the percentage increase invalid for a single year.

Kansas school salaries and change, statewide, through 2015. Click for larger version.
Kansas school salaries and change, statewide, through 2015. Click for larger version.

Kansas school spending: Visualization

An interactive visualization of revenue and spending data for Kansas school districts.

The accompanying visualization holds both nominal dollar amounts and amounts adjusted to reflect 2015 dollars. Data includes state aid, local aid, federal aid, and total spending for each school district, bot total and 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 — CUUR0000SA0 from U.S. Bureau of Labor Statistics. Visualization created using Tableau Public.

Click here to open the visualization in a new window.

An example from the visualization. This shows statewide spending, per pupil, adjusted for inflation. Click for larger version.
An example from the visualization. This shows statewide spending, per pupil, adjusted for inflation. Click for larger version.

Kansas, a rural state?

How does the population in Kansas compare to the nation and other states?

One of the most-often repeated themes in Kansas is that we are a rural state. Therefore, comparisons of Kansas to other states must be tempered and adjusted by this. It seems to be common knowledge.

Rural populations of the states. Click for larger version.
Rural populations of the states. Click for larger version.
There may be several ways to measure the “ruralness” of a state. One way is the percent of the state’s people that live in rural areas. The U.S. Census Bureau has these statistics. In the chart made from these statistics, Kansas is right in the middle of the states. 25.80 percent of Kansans live in rural areas.

That’s not too far from the country as a whole. For the entire United States, 80.7 percent of the population lives in an urban setting, according to the 2010 census. For Kansas, the figure is 74.2 percent.

Over time, Kansas is becoming more of an urban state, just as are most states and the country as a whole.

Do these numbers mean anything? It’s common for Kansas politicians to emphasize — even exaggerate — whatever connections they may have to a family farm. It’s part of a nostalgic and romanticized view of Kansas, the Kansas of Home on the Range. We are the “Wheat State” and “Breadbasket of the World,” and “One Kansas farmer feeds 128 people (plus you).”

So while Kansas is in the middle in the ranking of percent of population living in rural areas, our state’s politicians continue to play the “rural card.”

Voters and policymakers should keep this in mind, although politicians may not.

Click here to view and use an interactive visualization of states and urban population.

Percent urban population by state, with Kansas emphasized. Click for larger.
Percent urban population by state, with Kansas emphasized. Click for larger.

Pupil-teacher ratios in the states

Kansas ranks near the top of the states in having a low pupil-teacher ratio.

Pupil-Teacher Ratios in the States. Click for larger version.
Pupil-Teacher Ratios in the States. Click for larger version.
Data from National Center for Education Statistics, ELSI Elementary and Secondary Information System, shows that Kansas is near the top of the states in pupil-teacher ratio, meaning that Kansas has many teachers compared to the number of students.

A common complaint in Kansas is that class sizes have been rising. While pupil-teacher ratio is not the same measure as class size, the question is this: If Kansas has a low pupil-teacher ratio, but class sizes are large and rising, what are these teachers doing?

In the chart of pupil-teacher ratios over time, we see that while the ratio in Kansas rose during the 2014 school year, the trend over time is down, meaning that the number of teachers has increased faster than enrollment. Also, note the position of Kansas compared to other states. The pupil-teacher ratio in Kansas is lower than in most states.

Click here to access the visualization.

Pupil-teacher Ratios in the States, Kansas highlighted.
Pupil-teacher Ratios in the States, Kansas highlighted.

Kansas state and local tax revenue

Tax group definitions.
Tax group definitions.
In order to simplify this chart, I created four groups of taxes. As there are many taxes that are small in amount, I group them together as “Other.” For the group “Sales” I include the general sales and use tax, plus the cigarette and tobacco tax, plus liquor and beer tax, as these are of the same nature as the general sales tax.

Note this is both taxes collected by the state, and also by local governments.

Source of data is Kansas Tax Facts, various years. Values are nominal; not adjusted for inflation. To access the interactive visualization that is the basis of the example shown below, click here.

Kansas State and Local Tax Revenue. Click for larger.
Kansas State and Local Tax Revenue. Click for larger.

Kansas school employment

Kansas school employment declined for the current school year, and ratios of employees to pupils rose.

Figures released by the Kansas State Department of Education show the number of teachers and certified employees declined for the 2015-2016 school year.

The number of Pre K through grade 12 teachers fell to 30,413 from 30,868, a decline of 1.48 percent. Certified employees fell to 41,405 from 41,975, or by 1.36 percent.

Enrollment fell too, from 464,395 to 463,504, or 0.19 percent. As a result, the ratios of teachers to students and certified employees to students rose. The pupil-teacher ratio rose from 15.04 pupils per teacher to 15.24. For a school with 1,000 students, this change would be caused by the loss of one teacher.

The relative change in enrollment and employment is not the same in every district. The Kansas City school district saw its pupil-teacher ratio continue to decline, although the certified employee-pupil ratio rose slightly.

Of note, Kansas school fund balances rose slightly this year, both in absolute dollars and dollars per pupil.

I’ve gathered the numbers from KSDE and present them in an interactive visualization. to open it in a new window.

Kansas School Employment State Totals. Click for larger.
Kansas School Employment State Totals. Click for larger.

Employment by state and industry

An interactive visualization of employment in the states.

I’ve gathered employment data from the Bureau of Economic Analysis is an agency of the United States Department of Commerce, for the states and present it in an interactive visualization using Tableau Public. In the visualization you may use several different presentations of the data and filter for specific industries. The series are presented as the percentage change since the first values, so that relative growth, rather than magnitude, of employment is shown.

Growth in private nonfarm employment, Kansas emphasized. Click for larger.
Growth in private nonfarm employment, Kansas emphasized. Click for larger.
The nearby example from the visualization shows growth in private nonfarm employment, with Kansas emphasized against the other states.

Click here to access the visualization.

Employment by metropolitan area

An interactive visualization of employment in metropolitan areas.

Growth in Employment by MSA. Wichita is the bottom line.
Growth in Employment by MSA. Wichita is the bottom line. Click for larger version.
I’ve gathered employment data from the Bureau of Economic Analysis is an agency of the United States Department of Commerce, for all available metropolitan areas and present it in an interactive visualization using Tableau Public. In the visualization you may use several different presentations of the data and filter for specific areas and industries. The series are presented as the percentage change since the first values, so that relative growth, rather than magnitude, of employment is shown.

In the nearby example we can see that Wichita –- the bottom line — has performed poorly compared to some peers of interest.

Click here to access the visualization.