Sunday, January 19, 2014

Using Tableau for AISD teacher turnover statistics

There wasn't really anything new with my latest interactive on teacher turnover in AISD, but it did make me realize something: I need to make a clean list of Central Texas schools (or all of Texas) with all their latitude and longitudes for accurate mapping.

In this case, we were mapping all 125 or so AISD schools. We ran a list of Austin school addresses through A&M Geocoding service, which I've written about before, but we had to manually match them with the data we had on turnover rates.

The district data had partial campus IDs, and our address list, which was not limited to AISD, had full campus IDs that didn't match our data, so I couldn't easily join them. We'll fix that, as with this project we come out with a good, manually-checked source list of the AISD addresses. With the help of intern Beth Cortez-Neavel, we'll add district and campus IDs so the next time this comes up, a quick join in Access will get us there.

Cometdocs also saved my bacon again. It does a much better job converting PDF documents to spreadsheets than even Adobe's full Acrobat suite. I can't recommend Cometdocs enough. I fact, I ask that you sign up using this link, so we can both get more free conversion per week.

Saturday, January 11, 2014

How we built the Austin Homicide Project

There has been plenty of great news applications that have focused on homicides, notably HomicideWatch.org and the LA Times' Homicide Report. The HomicideWatch folks have turned their site into a cottage industry, leasing their platform to other publications, like the Chicago Sun-Times, The Trentonion and others.

We wanted to do something similar in Austin ... to write about and follow each case of homicide in our area. Unlike Chicago and L.A., which can top 500 slayings a year, we typically have fewer than 30 each year in Austin. We usually write about each case, but we wanted to follow them better, hence the birth of the Austin Homicide Project.

Our police and courts reporters (Ciara O'Rourke, Julie Chang and Jazmine Ulloa) have kept a Google Spreadsheet with basic information about each homicide for the last couple of years, but to utilize that data in a news application, I wanted to control how the data was entered to keep it clean and consistent. That was a big part of the project ... creating the data management interfaces. But we also wanted to do something special with the display.

Our data interactives "team" is really just a pair at this point: myself and interactives designer/artist Rob Villalpando. We don't have a "developer" nor a model-view-controller infrastructure to build upon, so we pieced together a series of online tools to create our news app.

So, here is how we did it:

A responsive Foundation

Rob has been experimenting with different ways to make our interactive content more mobile-friendly, but we've always struggled to find a solution that works well within our content management system. Probably the best example is the fantastic brewery guide, where he used Foundation. But as we struggled with iframes and wraps, it's been made clear that we need to get out of the CMS to get the visual display we are seeking. This is our first Foundation project on a new (to us) server where I hope we can create some great work. Of course, without the CMS, we have to solve a lot of infrastructure we miss, like metrics, ad serving, access to content, etc.

Caspio for the data entry and display

I used the online database platform Caspio to build the data entry and storage system. We've subscribed to the service for a long time and it works pretty well as a tool to collect structured data and spit it back out, but it is search-engine hostile. Most of their data is invisible to Google. We used their SEO extension for the first time with this project, but it has many limitations and only scratches at problem. But it did allow us to build an application to collect details about victims, suspects and incidents that lead to each homicide, and build datapages to search and display those records.

Google Feeds API for news content

We wanted to utilize all the content we already write about these cases, so we created RSS feeds out of our content management system for each victim and suspect, and stored feeds in the homicide database. We use the Google Feeds API to display those headlines on the detail pages for each person.

Google Maps API for incident maps

As we record each incident in the database, we include a latitude and longitude so we can display an interactive map using the Google Maps API. I've been toying with the idea to make this a static map instead, especially for navigating on a phone. I've tested how to do it Static Maps API, and have considered showing for mobile only. What are your thoughts on that?

DocumentCloud for documents

The free DocumentCloud project is a great research tool for journalists, but the search display widgets also are handy for displaying specific document sets. With each document we upload, we include metadata that we also store with that person's record in our Caspio database, so we can customize the search widget on our display pages. I set the key:value pair as a javascript variable, so I can check for existence before drawing the document search results.

Fusion Tables for overall map

The big map of all the incidents is a standard Google Fusion Tables map. I wish we could publish a JSON output directly from Caspio to feed an online map, but it is simple enough to download the data out of Caspio and then upload it into Fusion Tables, and there isn't that much movement in the data anyway. I did have to tackle a CSS issue where the map tools were obscured, but found the solution on Zurb's site.

Highcharts for stats

Rob put together the statistics using Highcharts, which we already pay for. You could do the same kind of work with the free Google Charts API, which we've also used before, but Highcharts is prettier. This is also a hand-managed data set, and includes APD crime stats in addition to our own data.


Sunday, January 05, 2014

Home affordability in Central Texas


I did some work with Marty Toohey on his story about the "affordability" of Austin, creating a data visualization about home sales using Tableau. It wasn't ground-breaking work (ha!) but it is fairly interesting to see how the sales distribution of home sales have changed over the last decade.

I mostly did this work so that Marty could see trends to write about, and it's an example to show how Tableau is an excellent tool for discovery and exploration of data. Check out how the percentage of homes priced at $100k range and the $200k range have changed over the past decade.

Now for the humbling part: How could this visualization be better? What could I have done to make it more interesting? More useful?

Because I just took at quick peak at Chartbeat to see how folks are interacting with that page, and the average length of stay is 30 seconds. Um ....

Saturday, January 04, 2014

We're hiring a news apps developer

5/17/2914 UPDATE: We are still seeking this position.

We're formalizing our news and data interactives work at the Statesman and we are looking for a developer to join Rob Villalpando and I to form a new News Interactives Team (or some other snazzy name we think up.) Rob has been doing some great work in recent years with news interactives and projects, and I've really enjoyed my work with data-intensive research, analysis and visualizations, but now it's time to "kick it up a notch."

I’m calling this a News Applications Specialist, and you can see the job requirements below.

But let me put it another way: If you read this story, and say, “Yep, that’s me” or “I want to make a difference like that,” then you are the kind of person I’m looking for. If you can buy into this philosophy and help us do some of the same type of work they do, then you are a good candidate. If you understand 80% of the the posts on that blog, then you are probably a really good candidate. If you understand it AND were already a regular reader of that blog, then OMG CALL ME!

I’m looking of someone who loves to create and to share what they know and what they learned today. We’ll soak it up. We also have a lot to offer, so you’ll grow and learn, too.

Resumes and cover letters can be sent to me:

Christian McDonald
Data Editor
Austin American-Statesman
cmcdonald@statesman.com

NEWS APPLICATIONS SPECIALIST:

The Statesman is looking for a news applications specialist to join a new data interactive team. This specialist will work with reporters, editors and other team members to design and build interactive graphics, data visualizations and news applications to support journalism ventures. Because existing members come out of a data analysis and interactive graphics background, candidates with strong programming and user interaction skills are strongly preferred.

JOB DUTIES & TASKS:

  • Communicate with both technical and non-technical colleagues to serve as a bridge between content and digital design of applications and visualizations.
  • Work as part of a newsroom team to bring our most important stories to our readers in a compelling way across all our various digital platforms.
  • Develop, code, test and debug news apps for mobile and wired platforms.
  • Share and expand knowledge with other team members, and to learn from the experience of others.
  • Research new technology and best practices and tools and analyze for best fit, usage, stability and performance.
  • Some reporting and contacting sources for data and information.
  • Communicate with both technical and non-technical colleagues to serve as a bridge between content and digital design of applications and visualizations.
  • Work as part of a newsroom team to bring our most important stories to our readers in a compelling way across all our various digital platforms.
  • Develop, code, test and debug news apps for mobile and wired platforms.
  • Share and expand knowledge with other team members, and to learn from the experience of others.
  • Research new technology and best practices and tools and analyze for best fit, usage, stability and performance.
  • Some reporting and contacting sources for data and information.


SKILLS  & EXPERIENCE:

  • Demonstrated ability to turn concepts into user-focused apps
  • Strong design skills and experience in print and/or digital presentation of data
  • Programming skills to develop apps for HTLM5/CSS3/Javascript environment and others that may emerge
  • Familiarity with web API’s and common data visualization libraries.
  • Experience with a server-side scripting language such as PHP, Ruby or Python and with a server-side web development frameworks such as Rails or Django is preferred.
  • Understanding of data structures and database management, and experience with web service development is preferred.
  • Some reporting experience preferred.





  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP