Urbanization in the US

Playing with Tableau, I created the video below of how the US Mainland slowly become more urbanized over its founding.

 

Data gathered from Wikipedia and the US Census.

It is neat to watch Manifest Destiny in action.  We were predominately rural until about 1920.  First we moved west until the 1870s.  With no more land to take from France, the Indians, and Mexico, we started moving towards cities as we became a more industrialized society.  In the 1910s most Americans we in cities as they moved off the farm and into factories.

 

US Urbanization by Region

 

While the largest in area, the Western States are the most urbanized.  This is because there are relatively fewer farms and most people live in a narrow strip by the Pacific Ocean.  The South and Midwest are the least urbanized by are slowly catching the other areas.  People move into the cities of the South and people leave farming towns of the Midwest for better economic opportunities.

 

Median Age by County 2016

This migration has lead younger people away from rural areas.  This as caused the median age of many rural areas to increase.

Using Business Objects 4.x to Create Tableau Reports

Tableau Workshops - miso -While Tableau has a great visualization capabilities, getting data into it from other databases is a chore unless the schema is simple.  Joining tables is easily through the GUI, but if the request requires complex filters it soon requires custom SQL. 

When this happens, Tableau is no help making it easy to code the SQL.  There are no hints or prefills like modern code programs have.  Therefore, I leverage the work of my Business Objects Universe authors.  They took the time to create the correct database joins needed to build visualization fast.  While Business Objects doesn’t produce the cleanest SQL, it will nearly always work in Tableau.

image

1. In BOXI (Business Object web version,) create the query

2. On the Query Panel control bar, click View Script

 

image

3. In the Query Script viewer, click Use custom query script

4. Highlight the SQL

5. Right-click > Copy (CTRL + C)

 

image

6. In Tableau, connect to a New Data Source

I have tested this and it works for DB2, SQL Server, and Oracle.  While it can work for Access so long as their is no spaces in table and field names, though there are ways to get around it by switching the [] brackets with double quotes.

 

image

7. Enter your database server information and ID/password.

For this to work, you will need at least Read Only access to the database in question.

 

image

8. If necessary, choose the correct Schema.

Most complex databases will have this, but products such as Access will not.

 image

9. At the bottom of the Table section, double-click New Custom SQL

 

image

10. In the Edit Custom SQL popup, right-click > Paste (CTRL + V)

 

image

The SQL should be displayed.  Now the SQL should be cleaned a bit before clicking OK. 

  • Remove anything after the WHERE or GROUP BY clauses such as FOR READ ONLY.  Most databases don’t like it.
  • If there is custom field formatting in the SELECT clause such as Date formatting remove it.  That is what Tableau is for.  If the SQL will be used in lots of workbooks, I like to give them a standard name.  This makes it easier to keep naming structures constant.
  • CASE as well as Min/Max statements work fine. 
  • If you want, clean the SQL to make it a bit more readable.  Things such as adding friendly field name helps when error checking is necessary.

11. Click OK

Hopefully there are no errors.

 

image

If there is an error click Show Details in the error box to see the problem (this examples show a missing comma after one of the variables.)  Unfortunately, Tableau only show the first error it encounters.

2016 ISU Professor/Administrator Pay Breakdown

Illinois State University - FIREI was doing a little research on teacher pay and came across the Illinois Board of Higher Education site, which lists the compensation of all professors and many administrators.  With is in hand, I through it into Tableau and profiled Illinois State University (ISU.)

 

image

ISU is top heavy.  It pays the administrators and unit directors more than other institutions.  They also tend to pay their professors and adjuncts less than most other institutions. 

 

image

ISU also uses many more atypical (likely adjunct) teachers.  There are almost twice as many part time instructors than other public universities.  This is likely because it is a teaching college as well as having a vocational/technical bent.  Bringing in people with real world experience can improve learning.  It is also why the pay for instructors/lecturers’ pay is low. 

 

image

Excluding the lower paid people, the mix is fairly typical with a few more associate professors on average.  With instructors removed, the median salary is $80,700 vs other universities $83,400. 

 

image

When looking at a Pareto chart of compensation, there is a budge of low paid instructors with compensation in the $10 to $30K range.  There is another bulge around $80 to 120K.

Overall, the administrators and high positioned people at ISU are well paid compared to other universities.  It also relies on part-time and adjuncts more than others.

Want to play with the data and compare it to other schools?  Go here: 2016 ISU Professor Pay Visualization.

Increase Virtual Memory for Stat Programs

In my work, I run many models using gigabytes and larger datasets.  Even with 32GB of RAM, sometimes programs crash for lack of memory.  Since it is unlikely that my work machine will receive a memory upgrade in the near future, another option is to expand Windows virtual memory.  While this is unlikely to speed up the formulation of results, allows programs more memory space to run models.  

This workflow is for Windows 7.  It will work for Vista, but some of the steps are slightly different for 8 and 10.

 

image

 

  1. On the desktop, click Start
  2. Type control panel

 

image

 

  1. If the Control Panel looks like the above picture, click View by: > Small icons

 

image

 

  1. In the Control Panel, click System

 

image

 

  1. In System, click Advanced system settings

 

image

 

  1. In System Properties under the Advanced tab, click Settings…

 

image

 

  1. In the Performance Options popup, click Advanced tab
  2. On the Advanced tab under Virtual memory, click Change… button

 

image

 

Now there are various ways to change it.  This system has two drives. 

The C: drive is a smallish Solid State (SSD) drive.  While increasing the memory allocation here provides the fastest access time, there isn’t much space in a 256GB drive.  I normally either leave it as [System Managed] or remove it all together to increase space for programs.  If you are going to set it manually, try not to use more that 20% or so of the drive. 

The D: drive is a large traditional hard drive.  Because of its 2TB size and the fact that programs do not install here, it is a good place to store swap disks.  Again, the size should take up too much of the drive unless it is the only thing that will be on it.  Try 64GB to 128GB start and go larger if programs continue to crash.

  1. Click the drive name
  2. Click Custom size
  3. Enter the initial size (MB)
  4. Enter the Maximum size (MB)
    1. This don’t need to be the same, but I like symmetry.
  5. Click Set button
  6. Click OK to close the Virtual Memory Window
  7. Click OK to close the Performance Option Window
  8. Click OK to close the System Options window

Generally, Windows will ask for a reboot.  Do it at the first possibility to enable the suggestion.

Note:  For best results, if you use a traditional hard drive, defrag it first to give Windows a large contiguous block of space on the drive.  It may reduce thrashing.  This step is not necessary on a SSD.  Happy quanting

Durbin Toes the Line, Kirk Goes Against the Grain

ProPublica has an API that exports Congressional voting patterns so that people can review their representatives voting habits.  In downloading the data for senators, I noticed so marked differences in the voting patterns between the two senators for Illinois.

Note:  The data below was cleaned and compiled by me.  If you want to a better look at the charts or download the data go here: https://public.tableau.com/profile/steve.rubendall#!/vizhome/CongressVotingPatterns/Data

image

Kirk is sort of a rebel, with the 3rd highest rate of voting against his own party in the Senate.  I’d chalk this up to representing a very Democratic state.  Other notable rebels are two former presidential candidates Rand Paul and Ted Cruz.

Durbin on the other hand toes the party line.  He is the 3rd lowest in voting against his party.  This is likely because he is the Minority Whip.  For all that Berne wants to be, he toes the Democratic party line more often than not, voting against the Dems less than others in his party.

 

 

image

As for votes missed Kirk ‘misses’ about 6% of the votes about double Durbin’s number this term.  This chart is stark for former presidential candidates.  Marco Rubio and Ted Cruz miss 1 in 3 votes with Berne missing over 1 in 3.

 

 

image

When looking at the Congress as a whole, the Democrats are more unified in their voting patterns in both houses.  The Senate is especially ruckus for the Republicans with over half of their party voting against itself over 1 in 10 times.

 

 

image

In general, the my house rep, Darin LaHood, is a  pretty solid vote for the Republicans.  I guess he has to be since our previous rep, Aaron Schock resigned because of his decorative and travel escapades.

Tableau Data Mining Fun – McLean County’s Sales Tax Referendium

2014-03-08 21_54_26-McLean County, IL - Official WebsiteI am following the debate around Bloomington about the March 18 referendum to add a 1% sales tax for schools.  Like any issue pitching money v kids, the conservatives v progressives are battling it out.  Those voting yes think about our children’s education, those against don’t want to see taxes go up.

Note: to educate yourself on the issue, I recommend reading Stifel Nicolaus Presentation to MCSD [PowerPoint] for details.

Unfortunately, neither this presentation nor the rhetoric online do good job comparing McLean county to other in the state.  So, what does the data say about what we already spend? 

I looked into this issue using 2012 Illinois State Board of Education data and threw it into Tableau to pretty up the spreadsheets.


This graphic is a is a little small. If you want to see these dashboards in their full glory, go here.

Mclean county is 28th (of 102) in spending, roughly a bit above the average, which is skewed by Chicago as they spend far more per student. We are ranked 8th in both our ACT scores and ISAT (an elementary school test.)

Not bad, though the county average mask the highly variable quality of our schools depending on which school a student attends. It is safe to assume that State Farm’s professional base gives are big lift overall.

Overall, I will vote No not because I dislike paying taxes for good causes, but because sales taxes are regressive, hurting the poor more. They pay a higher proportion of income on goods, which are taxed. Wealthier people spend more on services and save more, both of which are untaxed locally. I’d rather see a progressive income tax or property tax levy hike, if it is needed. But increased spending rarely increases our kids preparedness for life.

I might have voted differently if our schools where severely underfunded or our schools were underperforming, which the data suggests that neither is true.