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.

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.

GDP by state and industry

An interactive visualization of a new data series from the Bureau of Economic Analysis.

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

This week BEA issued a release of a new series of data: 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. The announcement of the release of this data from BEA is here.

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.

In the visualization you may use several different presentations of the data and filter for specific states or industries. The series are presented as percentage change over time since the first values, so that growth, rather than magnitude, of GDP is shown.

Click here to open the visualization.

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

Kansas school employees by type

An interactive visualization of relative trends in Kansas school employment.

Kansas State Department of Education makes available tables of the number of employees working in Kansas schools. Employees are classified in two broad categories, Certified and Non-Certified. Within each category, employees are further classified by job type such as Superintendent, Curriculum Specialist, and Social Worker.

Visualization of certified employees, showing Principals and Curriculum Specialists highlighted.
Visualization of certified employees, showing Principals and Curriculum Specialists highlighted.
I’ve gathered the tables back to fiscal year 2002 (the 2001 – 2002 school year) and present them in an interactive visualization. There are separate visualizations for Certified and Non-Certified employees. In each, as shown in the instruction, you may check the check boxes to add or remove types of employees. For the employee types that are shown, you may click to highlight types apart from the others.

The line charts show the relative change in the number of employees. You may learn whether the number of employee type A is growing faster or slower than employee type B.

The visualization also holds tables showing the number of employees.

Click here to open the visualization in a new window.

Using the visualization.
Using the visualization.

Spending in the states, per capita

An interactive visualization holding per-capita spending in several categories for each state.

Tableau sort icon
Tableau sort icon
In the visualization you may select one of more spending categories, select any combination of states and regions, select years, and view data as a table or chart. By hovering near column titles and clicking on a sort icon, you may sort in ascending or descending order.

Of note: Some of the spending categories should not be selected at the same time, as the stacked bar chart adds them. For example, you would not want to select anything else if “Total Spending” is selected, as the other items are already included in “Total Spending.” Similarly, you would not want to select “Population” along with any items that are money amounts.

Data is from State & Local Government Finance Data Query System. 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 (2012). Date of Access: (07-Jan-2015). Visualization created using Tableau Public.

Click here to use the visualization.

Using the visualization. Click for larger version.
Using the visualization. Click for larger version.
A table from the visualization. Click for larger version.
A table from the visualization. Click for larger version.

Kansas NAEP scores for 2015

Reactions to the release of National Assessment of Educational Progress scores for Kansas and the nation. Also, an interactive visualization.

Results for the 2015 administration of the National Assessment of Educational Progress became available October 28. The test, sometimes called the “nation’s report card,” is described as “the largest nationally representative and continuing assessment of what America’s students know and can do in various subject areas.”

The Wichita Eagle didn’t have much to say on this, reporting “Results from the latest National Assessment of Educational Progress show that Kansas scores dropped in most areas since 2013, state education officials announced Wednesday. The decreases echo a downward trend in scores nationwide on the NAEP exam, also known as the Nation’s Report Card.”

The Kansas State Department of Education reported “Results from the 2015 National Assessment of Educational Progress (NAEP) exams, also known as the Nation’s Report Card, show that Kansas followed the national trend of decreasing scores. Across the nation this year, both fourth- and eighth-grade mathematic scores, as well as eighth-grade reading scores, are lower in 2015 than in 2013. Fourth-grade reading scores aren’t significantly different from 2013.”

The Lawrence Journal-World used the Associated Press story: “Kansas schoolchildren are faring worse on a test known as the nation’s report card. The state’s performance dip follows a national trend of falling scores on the National Assessment of Educational progress.” So too did the Topeka Capital-Journal.

The Kansas Association of School Boards noted “State and national education leaders, including KASB, are currently researching the latest National Assessment of Educational Progress scores, which were released earlier this week. Both nationally and in Kansas the 2015 NAEP results decreased slightly. … While Kansas results decreased slightly, Kansas student achievement remained above the national average in 4th- and 8th grade math and 8th grade reading and was the same as the national average in 4th grade reading. KASB is currently doing an in-depth analysis of the NAEP results and release its findings as soon as possible.”

Kansas Governor Sam Brownback issued a statement: “Today’s NAEP scores reflect the need for real education reform to benefit our students. This is a complex issue with no single cause or solution and today’s results confirm a trend showing that even though education funding has increased by more than $1 billion over the past decade, NAEP scores have remained largely flat. … While our Kansas schools remain above the national average, we can and should do more. We want our students to excel and have the skills they need to succeed in school and life in the 21st century. To do that, we must work to get more dollars into the classroom and into the infrastructure our teachers need to improve student performance, particularly in math. We need flexibility at the local level to address students’ needs, and we should support the great efforts of the thousands of teachers who work every day to help give our students opportunity for a brighter future.”

Some of these statements compared Kansas scores to the national average. That is not appropriate if there are subgroups that score at different levels, and if the composition of these subgroups varies significantly between states or the national average. That is the case with Kansas, which has significantly lower minority populations than the nation and some states. Care must be used when making comparisons.

To assist in understanding NAEP scores, I’ve updated two interactive visualizations with 2015 data. One visualization shows subgroups based on race/ethnicity, and the other shows subgroups based on national school lunch program eligibility, which is a commonly-used surrogate for income.

Each visualization has a number of tabs that display data in different ways. Most tabs allow for filtering of data in several ways.

Click here for the visualization based on race/ethnicity, and here for lunch eligibility.

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

Sedgwick County delinquent property tax

Here is an interactive version of the list of delinquent property taxes in Sedgwick County. This list is printed in a local newspaper three times each summer.

The Sedgwick County Treasurer issues a disclaimer regarding this list, which is that some of the taxes may have been paid, or are waiting the result of a grievance or protest.

Click here to open the interactive list in a new window. As shown in the illustration below, you may click to expand the addresses of properties for an owner, and sort by any column.

Sedgwick County delinquent tax list instructions 2015-10

Kansas school support

An interactive visualization of data provided to members of the Kansas 2015 Special Committee on K-12 Student Success.

The Kansas 2015 Special Committee on K-12 Student Success held its first meeting on October 23. As part of the meeting, data on school spending was made available. Of particular interest may be the data on instruction spending.

Dale M. Dennis, Deputy Commissioner of Education, provided committee members these definitions of instruction spending categories:

Instruction — Includes the activities dealing directly with the interaction between teachers and students. This catgory [sic] includes only regular and part-time teachers, teacher aides or assistants, homebound teachers, hospital-based teachers, substitute teachers, and teachers on sabbatical leave.

Student Support Services — Includes the following services: attendance and social work, guidance, health, psychological, speech pathology and audiology.

Instructional Support Services — Includes the following services: improvement of instruction, library and media, instruction-related technology, and academic student assessment.

Example table from visualization.
Example table from visualization.
Committee members were supplied with spreadsheets holding one year’s spending. I’ve gathered the spreadsheets for the three years that were provided and present them in one interactive visualization. One view of the data shows the data items for each school district, with the three years shown together. I added amount per pupil calculations.

Example from visualization.
Example from visualization.
A second view shows the per-pupil values as a line graph over the three years.

This spending data represents Kansas state support only and does not include spending from federal or local funding sources. The provided data was not adjusted for inflation.

Click here to open the visualization in a new window. Visualization created using Tableau Public.

Wichita’s growth in gross domestic product

An interactive visualization of gross domestic product for metropolitan areas.

Gross domestic product is the sum of the value of all goods and services produced for a period of time. The Bureau of Economic Analysis makes this statistic available for metropolitan areas. GDP is not the only way to measure the economic health of a region, but it is one way. I’ve gathered the data and made it available in an interactive visualization.

Growth of GDP for Wichita and selected cities. Click for larger version.
Growth of GDP for Wichita and selected cities. Click for larger version.
When using the visualization you may select total GDP, or GDP for private industry or government alone. You may select any number of metropolitan areas to appear on the chart. By clicking metro names in the legend, you can highlight or emphasize the series for one metro area. Use Ctrl+click to select more than one at a time.

Of note, recently James Chung delivered a lecture in Wichita. As part of the presentation, he mentioned three areas that he thought were doing things well: Cedar Rapids, Des Moines, and Omaha. A nearby illustration shows the visualization of the growth of GDP for these metro areas and Wichita. You can see that GDP for these areas have grown faster than has GDP for Wichita. (This visualization shows GDP change since the start of the chart, so that the growth of metro areas of different sizes can be compared.)

Growth of GDP for Wichita and selected cities. Click for larger version.
Growth of GDP for Wichita and selected cities. Click for larger version.
Another illustration compares Wichita to several cities that were part of the Wichita Metro Chamber of Commerce’s city-to-city visits. While in some years the visit has been to cities like Austin that have grown rapidly, that is not always the case.

Click here to open the visualization in a new window.

Data is from Bureau of Economic Analysis, an agency of the United States Department of Commerce. Visualization created using Tableau.

Using the visualization.
Using the visualization.

Kansas school expenditures per pupil

The Kansas State Department of Education publishes a series of worksheets titled Expenditures Per Pupil. Here is the data gathered and presented in an interactive visualization.

Click here to open the visualization in a new window.

Using the visualization.
Using the visualization.

Kansas school fund balances

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

As Kansans debate school funding, as the Kansas Supreme Court considers ordering more school spending, and as school spending boosters insisting that school spending has been slashed, a fact remains constant: Kansas schools don’t spend all the money they’ve been given. Fund balances have been growing almost every year, including this year.

Fund balances are necessary for cash flow management. The issue is what levels of balances are necessary. Based on recent data from the Kansas State Department of Education, fund balances rose rapidly after 2008, and have remained largely level since 2011.

I’ve gathered data about unspent Kansas school funds and presented it as an interactive visualization. You may explore the data yourself by using the visualization. Click here to open the visualization in a new window. Data is from Kansas State Department of Education. Visualization created using Tableau Public.

Example from Kansas school fund balances visualization.
Example from Kansas school fund balances visualization.

Wichita school district checkbook updated

Data now available through August 2015.

USD 259, the Wichita public school district, makes its monthly checkbook register available. I’ve gathered the monthly spreadsheets made the consolidated available for analysis through Tableau Public.

The workbook (click here to open it in a new window) has a number of tabs, each showing the same data organized and summarized in a different way.

There are some caveats. First, not all school district spending is in this database. For each year, the total of the checks is in the neighborhood of $350 million, while the total spending for USD 259 is over $600 million. So there’s spending that isn’t included in this checkbook data.

Second, there are suppliers such as “Commerce Bank Visa BusinessCard.” Payments made to this supplier are over $7 million per year. These payments from the district’s checkbook undoubtedly pay a credit card bill, and this alone doesn’t let us know what the $7 million was spent on.

wichita-school-checkbook-data-quality-exampleThere are some data quality issues, as seen nearby.

USD 259 supplies this advice with this data: “The information you find may cause you to ask more questions. If so, the person to contact is Wichita Public School’s Controller, Barbara Phillips. She can be reached at (316) 973-4628, or at bphillips@usd259.net.”

Using the Wichita school district visualization
Using the Wichita school district visualization

Kansas private nonfarm employment by county

Here is an interactive visualization of private nonfarm employment in Kansas, for each county. Data is from Bureau of Economic Analysis, part of the U.S. Department of Commerce. Click here to open the visualization.

The sample below shows job growth for the state as a whole, along with the five largest counties. Click it for a larger version.

Kansas private nonfarm employment by county, five largest counties, 2015-09

Quarterly gross domestic product by state

Today the Bureau of Economic Analysis released a new series of statistics. From the accompanying press release:

Today, the U.S. Bureau of Economic Analysis (BEA) is releasing prototype quarterly gross domestic product (GDP) by state statistics for 2005–2014. The quarterly GDP by state statistics are released for 21 industry sectors and are in both current and inflation-adjusted chained (2009) dollars.

The new data are intended to provide a fuller description of the accelerations, decelerations, and turning points in economic growth at the state level, including key information about the impact of industry composition differences across states. Relative to the August 2014 release, the new prototype statistics incorporate new and revised source data and cover an additional year of economic activity.

Statistics for the first quarter of 2015 are not being released as BEA continues to evaluate its methodology based on data users’ comments and evaluations received after the first release of prototype quarterly GDP by state statistics last September.

I’ve gathered a subset of the data and present it in an interactive visualization. In this subset, I include only these industries: All industry, Private industry, and Government. Click here to open the visualization in a new window.

Quarterly GDP by state and industry, new prototype 2015-09 instructions

National Transit Database, an interactive visualization

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

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.

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.
OpExp Total: Total operating expense.

There is also a set of cities named “Wichita peers.” These are cities that Wichita has been compared to in a variety of situations. Some I selected because they were of similar size to Wichita.

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

Click here to access the visualization.