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.
An interactive visualization of spending by Kansas school districts.
The accompanying visualization holds both nominal dollar amounts and amounts adjusted to reflect 2017 dollars. Data includes state aid, local aid, federal aid, and total spending for each school district, both total and per pupil. The visualization includes both tables and charts.
Kansas school employment and ratios to students in an interactive visualization.
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
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
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. ↩
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.
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.
An interactive visualization of state and local direct general expenditures, per resident. Click here to use the visualization.
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.
Kansas has a lot of government employees when compared to other states, and especially so in education.
Considering all government employees — state and local — Kansas has 68.35 full-time equivalent (FTE) employees per thousand residents. Only two states and the District of Columbia have more.
For total elementary and secondary education employment, Kansas has 30.64 such employees (full-time equivalent) per thousand residents. Only two states have more.
Looking at nearby states and some Plains states commonly thought to be big spenders (Iowa and Minnesota), Kansas has more employees and more education employees, again on a per-resident basis.
This data comes from the United States Census Bureau. I’ve gathered it and present it in an interactive visualization. Click here to learn more about the visualization and to use it yourself.
Example from the visualization, showing Kansas and selected states. Click for larger.
An interactive visualization of labor force, employment, and unemployment rate for all metropolitan areas in the United States.
The Bureau of Labor Statistics, part of the United States Department of Labor, makes monthly employment and unemployment statistics available. I’ve gathered them for all metropolitan areas and present them in an interactive visualization.
The labor force, specifically the civilian labor force, are those people working, plus those people actively searching for work, minus people under 16 years of age, minus people living in institutions (for example, correctional facilities, long-term care hospitals, and nursing homes), minus people on active duty in the Armed Forces. 1
BLS defines unemployed people as: “Persons aged 16 years and older who had no employment during the reference week, were available for work, except for temporary illness, and had made specific efforts to find employment sometime during the 4-week period ending with the reference week. Persons who were waiting to be recalled to a job from which they had been laid off need not have been looking for work to be classified as unemployed.” 2
The unemployment rate is “the number unemployed as a percent of the labor force.” 3
In the visualization, you may select tabs to show a table or a chart. You may select a range of dates and the metro areas that appear.
The OES program produces employment and wage estimates for over 800 occupations. These are estimates of the number of jobs in certain occupations, and estimates of the wages paid to them. These estimates are available for the nation as a whole, for individual States, and for metropolitan statistical areas (MSAs), metropolitan divisions, and nonmetropolitan areas; national occupational estimates for specific industries are also available. 1
OES data is gathered through a semi-annual mail survey of non-farm establishments. Data is released annually in May using three years of data to improve reliability. For example, “The May 2017 employment and wage estimates were calculated using data collected in the May 2017, November 2016, May 2016, November 2015, May 2015, and November 2014 semi-annual panels.” 2
OES jobs category illustration. Click for larger.
BLS presents data in a hierarchy. 3 At the tops are groups, like “13-0000 Business and Financial Operations Occupations” in the nearby example.
There are then one or more subgroups like “13-1020 Buyers and Purchasing Agents.” Then, there are actual occupations, like “13-1021 Buyers and Purchasing Agents, Farm Products” and “13-1022 Wholesale and Retail Buyers, Except Farm Products.”
Drilling down. Click for larger.The visualization I created uses the hierarchical nature of the data: Groups, Subgroups, and Occupations. In the visualization, when you hover the mouse of a column heading, a “+” or “-” may appear. Click on these to expand or contract the data. This is also known as “drill down.”
In the Table by Area, the differential between each city and the highest-salaried city is shown in dollars and percent.
When looking at data using the Group or Subgroup level, the salary data is summarized by computing the average. This may not be the proper technique, and is why I classify this visualization as experimental.
Comparing average salaries for groups of occupations in different cities has problems. One is the number of workers in occupations. Considering management occupations, there are few chief executive officers but many other managers. The weight of the number of workers needs to be considered.
Also, the magnitude of salaries is an issue. Chief executive officer salaries vary widely, by tens of thousands of dollars. The data tells us that a CEO in Wichita earns $65,400 less than in Des Moines. That variation is greater than the average salary across all occupations.
Data is for the May 2017 release, the most current available.
County Business Patterns (CBP) is an annual series that provides subnational economic data by industry. This series includes the number of establishments, employment during the week of March 12, first quarter payroll, and annual payroll. This data is useful for studying the economic activity of small areas; analyzing economic changes over time; and as a benchmark for other statistical series, surveys, and databases between economic censuses. Businesses use the data for analyzing market potential, measuring the effectiveness of sales and advertising programs, setting sales quotas, and developing budgets. Government agencies use the data for administration and planning. 1
This visualization holds data from 2007 to the most recent release. Click here to access and use the visualization.
Considering all government employees in proportion to population, Kansas has many compared to other states, and especially so in education.
Each year the United States Census Bureau surveys federal, state, and local government civilian employees. I’ve gathered this data and present it in an interactive visualization using several views and supplementary calculations. 1
The Census Bureau collects both counts of employees and payroll dollars. Comparisons based on the number of employees are useful, bypassing issues such as differing costs of living and salaries in general.
Considering all government employees, Kansas has 68.35 full-time equivalent (FTE) employees per thousand residents. Only two states and the District of Columbia have more.
For total elementary and secondary education employment, Kansas has 30.64 such employees (full-time equivalent) per thousand residents. Only two states have more.
Click here to learn more about the visualization and to use it yourself.
In this example from the visualization showing Kansas and nearby states, Kansas stands out. Click for larger.
A visualization of federal, state, and local government civilian employees by state and function.
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. I’ve made this data available in an interactive visualization.
The Census Bureau describes the data:
The survey provides state and local government data on full-time and part-time employment, part-time hours worked, full-time equivalent employment, and payroll statistics by governmental function (i.e., elementary and secondary education, higher education, police protection, fire protection, financial administration, central staff services, judicial and legal, highways, public welfare, solid waste management, sewerage, parks and recreation, health, hospitals, water supply, electric power, gas supply, transit, natural resources, correction, libraries, air transportation, water transport and terminals, other education, state liquor stores, social insurance administration, and housing and community development).
The survey provides Federal Government data on total employees, full-time employees, and total March payroll by governmental function. There is no detail available for part-time employment, part-time hours worked, full-time equivalent, or full-time or part-time employee payrolls. Three functions apply only to the Federal Government and have no counterpart at the state and local government levels: national defense and international relations, postal service, and space research and technology. 2
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, the number of full-time equivalent (FTE) employees per thousand residents, and the number of residents per FTE employee.
The growth of population in Wichita compared to other areas.
Several Wichita city officials have noted that the population of the City of Wichita now exceeds that of Cleveland. This, to them, is a point of pride and sign of momentum in Wichita.
It’s true, at least the population facts. For 2016, the U.S. Census Bureau estimates the population of Wichita as 389,902 and Cleveland as 385,809. From the 2010 census, Wichita’s population was 382,368; Cleveland’s 396,815. 1
That Wichita moved up in population rank is more due to Cleveland losing 11,006 people (2.8 percent loss) while Wichita gained 7,534 people (2.0 percent gain).
Looking only at city population, however, misses the fact that the Cleveland metropolitan statistical area population is 2,058,844 compared to the Wichita MSA at 645,628, a difference of 3.2 times.
For most types of economic and demographic analysis, metropolitan statistical areas (MSA) are preferred to cities proper. The Census Bureau notes: “The general concept of a metropolitan or micropolitan statistical area is that of a core area containing a substantial population nucleus, together with adjacent communities having a high degree of economic and social integration with that core.” 2
Further, there is more economic data available at the MSA level (compared to the city level) from agencies like Bureau of Labor Statistics and Bureau of Economic Analysis. This data includes important measures like employment, labor force, unemployment rate, gross domestic product, and personal income.
City boundaries are still important, as Wichita, for example, can’t impose property or sales taxes outside the city limits. Nor can it write laws affecting neighboring towns or the county.
But not even schools respect city boundaries, with several large suburban school districts (Andover, Maize, Goddard) reaching far into the city limits of Wichita.
While Wichita may be the 50th largest city, its rank is not as high when considering metropolitan areas. Worse, its rank is slipping as other areas grow at a faster clip. In the 1990 and 2000 census, Wichita was the 80th largest metro area. By 2010 Wichita’s rank had fallen to 82, and for 2017 the rank is 89.
Growth of Wichita MSA population and economy
Wichita officials incessantly talk about momentum. Using a misguided measure of regional size and growth (Wichita is larger than Cleveland!) is one example.
Unfortunately, there are many other examples. Recently Wichita’s mayor spoke of a “thriving city” and that “we’re going to continue our growth pattern.” 3
Recently Greater Wichita Partnership president Jeff Fluhr told a group of young people this:
From the innovation campus at Wichita State University and development along the Arkansas River in downtown, including a new baseball stadium, to the conversations happening now about a new convention center and performing arts facility, Fluhr said the momentum is pushing to keep Wichita on par with the development of other communities around the country.
That development, which has in recent years expanded to incorporate the entire region, is a critical component to attracting and retaining talent — the exact kind of talent in the ICT Millennial Summit crowd. 4
In announcing his candidacy for Sedgwick County Commission, Wichita city council member Wichita City Council Member Pete Meitzner (district 2, east Wichita) said, “We have enjoyed great progress and growth during my two terms as a City Council member and I plan to do my part to assure Sedgwick County is part of this continued success.” 6
But these pictures — thriving, growth, progress, momentum — just aren’t true, according to the best statistical evidence. Wichita is shedding jobs. 7 In 2016 the Wichita economy shrank. 8 Our labor force is declining. 9 Sedgwick County shows a decline in employees and payroll in 2016. 10
Finally, as can be seen in the nearby chart of population growth in the Wichita metro area and a few other examples. Wichita’s growth rate is low, and is slowing. (The other metro areas in the chart are our Visioneering peers plus a few others.)
It is terribly unfortunate that the Wichita economy is not growing. What’s worse is the attitude of our city leaders. If we don’t confront our problems, we probably won’t be able to solve them.
In an interactive visualization I’ve prepared from census data, you can compare growth in metropolitan statistical areas. Click here to access the visualization.
Wichita and other population growth. Click for larger.
—
Notes
Annual Estimates of the Resident Population for Incorporated Places of 50,000 or More, Ranked by July 1, 2016 Population: April 1, 2010 to July 1, 2016. Source: U.S. Census Bureau, Population Division. Release Date: May 2017 ↩
An interactive visualization of populations of metropolitan statistical areas, or MSA, in the United States.
For most types of economic and demographic analysis, metropolitan statistical areas (MSA) are preferred to cities proper. The United States Census Bureau notes: “The general concept of a metropolitan or micropolitan statistical area is that of a core area containing a substantial population nucleus, together with adjacent communities having a high degree of economic and social integration with that core.” 1
There is more economic data available at the MSA level (compared to the city level) from agencies like Bureau of Labor Statistics and Bureau of Economic Analysis. This data includes important measures like employment, labor force, unemployment rate, gross domestic product, and personal income.
I’ve gathered data from the Census Bureau and present it in an interactive visualization using Tableau Public. Click here to access the visualization.
Data is from Annual Estimates of the Resident Population: April 1, 2010 to July 1, 2017. Source: U.S. Census Bureau, Population Division. For counties, municipios, metropolitan statistical areas, micropolitan statistical areas, metropolitan divisions, and combined statistical areas, March 2018. Also earlier Census Bureau publications.
Figures are for total lane miles, urban and rural, using data reported by the Federal Highway Administration for 2016. 1
Besides a graphic table of population, total lane miles, and lane miles per thousand persons, there are three scatter plots. These plot each state’s population, area, and population density compared to lane miles.
Click here to access the visualization at Tableau Public.
Kansas has a lot of highway miles compared to its population. Interactive visualization included.
Kansas has nearly 100 lane miles of highway per thousand persons, a value exceeded by only five states, with two of those barely higher than Kansas. This figure is for total lane miles, urban and rural, using data reported by the Federal Highway Administration for 2016. 1
Besides a graphic table of population, total lane miles, and lane miles per thousand persons, there are three scatter plots. These plot each state’s population, area, and population density compared to lane miles.
In each plot, I’ve identified Kansas. (In the interactive visualization you can identify each state.) In all three charts, Kansas is an outlier.
These charts do not include Alaska, California, and Texas. These three states are outliers — Alaska because of its area, and the other two because of their size and high population. In the interactive visualization, of course, you may include these states and exclude any others.
Figures are for total lane miles, urban and rural, using data reported by the Federal Highway Administration for 2016. 1
Besides a graphic table of population, total lane miles, and lane miles per thousand persons, there are three scatter plots. These plot each state’s population, area, and population density compared to lane miles.
Click here to access the visualization at Tableau Public.
An interactive visualization of tax collections by state governments.
Each year the United States Census Bureau collects a summary of taxes collected by each state for 5 broad tax categories and up to 25 tax subcategories. 1 I’ve collected this data and made it available in an interactive visualization.
You may recall that Kansas raised personal income tax rates in 2017 and made the new rate retroactive to January 1, 2017. But that change doesn’t seem to have affected this data. For 2016, Kansas collected $768 per person in individual income taxes, and for 2017, $799. Here’s why:
For most states, including Kansas, this data is for the fiscal year, not the calendar year. 2 New withholding tax tables were not available until June 27, 2017, just three days before the end of fiscal year 2017. 3
Census data shows that some counties in Kansas are growing faster than others.
Each year the United States Census Bureau publishes County Business Patterns (CBP). The Bureau describes the data as follows:
County Business Patterns (CBP) is an annual series that provides subnational economic data by industry. This series includes the number of establishments, employment during the week of March 12, first quarter payroll, and annual payroll. This data is useful for studying the economic activity of small areas; analyzing economic changes over time; and as a benchmark for other statistical series, surveys, and databases between economic censuses. Businesses use the data for analyzing market potential, measuring the effectiveness of sales and advertising programs, setting sales quotas, and developing budgets. Government agencies use the data for administration and planning. 1
What does this data tell us about counties in Kansas? I gathered the data back to 2005 and made the data in an interactive visualization available here. In the nearby illustration I show the data for large Kansas counties, starting in 2010. (In the visualization you may adjust all these parameters.) The data is indexed so that we can see relative changes independent of the size of the county.
In the chart, we can see that some Kansas counties are doing better than others. Notably, Sedgwick County shows a decline in employees and payroll in 2016.
Example from the visualization showing Kansas counties. Click for larger.
Each year the United States Census Bureau publishes County Business Patterns (CBP). The Bureau describes the data as follows:
County Business Patterns (CBP) is an annual series that provides subnational economic data by industry. This series includes the number of establishments, employment during the week of March 12, first quarter payroll, and annual payroll. This data is useful for studying the economic activity of small areas; analyzing economic changes over time; and as a benchmark for other statistical series, surveys, and databases between economic censuses. Businesses use the data for analyzing market potential, measuring the effectiveness of sales and advertising programs, setting sales quotas, and developing budgets. Government agencies use the data for administration and planning. 1
This interactive visualization presents this data back to 2005, for NAICS code 00, which is “Total for All Sectors.” Click here to access.
