How to build a Google Fusion Tables map

These are the basic steps we talked about in class today for building a Google Fusion Tables map. There are lots of nuances and such, but this is just the basic stuff. There are four steps: Upload, set features styles, set info window and publish.

• Start with a good data set (we plotted points using this Dangerous Dogs list). While you could start with data that has a standard address, Google may or may not do a good jog geocoding that data. It's best if you have good Latitude and Longitude for each record. You can have a separate column each for Latitude and Longitude, or put them in the same column with a comma between them.
• Texas A&M has a free Geocoding service that is wonderful. Once it codes everything, it also tells you how close it got on the address. Watch out for anything that used ZIP code, as that wasn't good and you'll need to manually fix them.
• The Classic Google Maps makes geocoding a single address easy. Just find the place on the map, then right-click at that place and choose What's here, and it will put the Lat,Long in the search bar.
• Log into Fusion Tables. You have to have a regular free Google account (in other words, not your UTMail account.)
• Fusion Tables is connected to Google Drive. Go under Create and see if Fusion Tables is listed. If not, go to "Get more apps" and find it and add it.
• That said, I suggest you bookmark this link that shows only FT tables. It's hard to find otherwise, and is useful in searching for public tables.
• In Drive, go File > Create > Fusion Table (or choose New Table in the showtables list), which will take you to a window where you can upload your .xls file, or find one of your existing Google Spreadsheets.
• Let the wizard guide you through the upload until you see the rows of data.
• Note which columns are colored yellow, as FT thinks those are Locations. Sometimes you have to go under Edit > Change Columns and find the right Lat/Long column and change the type to "Location"
• Most times, FT will recognize the location columns (Lat/Long) and create a tab called "Map of [whatever]". If you don't have Lat/Long or shapes (covered later), then it might try to geocode based on an address field it finds. It might also ask for help to make it better, like adding a city or state, etc.
• Once you have the map and your points are plotted it's time to work on the feature styles and info window.
• While looking at the map tab, look left for Feature Styles. This is where you can pick what kind of marker you want to use for points. You can use a single style, or use different markers based on a column that has numeric values. You can also set the marker by name using a column.
• Click on Set Info Window to change what shows up in the pop-up window when you click on features in map. The Automatic sides lets you check on and off fields in the window. But if you want to edit what the label says, or add some simple HTML, then go to Custom and change the info window there.
• Once all that is set, there are two steps to publish.
• Click on the Share button at top right and set it to "View" for "Anyone with link can view" or for "Public. But DON'T USE THE URL THEY GIVE YOU THERE.
• Instead, now choose the menu under the Map tab and go to Publish. There you can get a link, iframe or javascript embed. to use in your blog.

Using shapefiles for data

Later in class, we published using shapes. I'll go over how to get your own shape files in a future class, but in this case we merged some Census data with an existing shapefile that was already public in Fusion Tables. This is a real abbreviated version:

• Upload your data file to Fusion Tables. Note the GEOID field, which as field unique to each county.
• Go either to the advanced search in Drive or the Showtables page and search for Texas County Shapefile. If it doesn't come up, make sure you are searching for "public tables." Once you open that file, copy the URL into your clipboard.
• Go back into your census data file, and go under File > Merge, and the put in the URL of the county shapefile and hit next.
• Match up GEOID column in your data file to the GEOID10 file in the shapefile and merge them. You can keep all the columns for this demonstration.
• A map tab was created for you upon the merge because you have a "geometry" column with all the shape information.
• Now you can use the Set Feature Style, and set the ploygon fills to color by a bucket on the Median Age field. You can also set up an Automatic Legend there.
• Set up your Info Window with the important information, publish your map like you did above and BAM!, you are done.

Easy sneazy.

Now, here is the dirty little secret. The Fusion Table part is the easy part. It's getting the data formatted the way you want it before you import it that is the trick. You have to watch things like:

• ID numbers that start with 0. Those fields have to be set as text fields in Excel, or you'll drop the leading zeros. Zip codes on the East Coast are bad. So are school codes.
• Fields you have to merge on need to be identical. If you are merging by county name, you can't have La Salle County in one file and LaSalle County in another.
• Fusion tables takes .kml for shapefiles (keyhole markup language), but most government supply in the multi-file .shp format. You can convert them at shpescape.com, or with QGIS, which will learn later in the course.

Categories: Fusion Tables, Spring 2014

Data-related blogs, sites and Twitter accounts

In no particular order, and sure to change, these are blogs and sites that you should read regularly for ideas, lessons and knowledge about data and journalism.

• Source
• Flowing Data
• The Functional Art
• The Guardian's Data Blog
• Good Infographics
• ProPublica
• WNYC DataNews
• Texas Tribune TribNerds
• Charts N Things
• Visualizing Data
• Chicago Trib Apps Team
• Data Driven Journalism
• Center for Investigative Reporting
• Technical Bent

Twitter accounts and people worth following

• GoodData
• NPRViz
• Chicago TribApps
• DataRemixed (Ben Jones of Tableau)
• Texas Tribune TribData

Some Tableau-centric data blogs

• VizWiz
• VizCandy
• Paint by Numbers
• Data Revelations
• DataRemixed
• Jewel Loree

Categories: Spring 2014

Spring 2014 course is on a waitlist

The good news is that there is enough interest for this class to make for Spring 2014! The bad news is if you haven't registered yet, it is full and you'll be on a wait list. That also probably means that students outside the Journalism department (LBJ school, for instance) probably won't get in unless there are drops.

It's great to have so much interest in the class. The limit of 16 is firm because this is a lab-based classroom, with lots of hands-on work during class. That makes it both fun and challenging.

I've been working on the syllabus and outline for the class and should post some drafts well before the semester, but I'm also working on following semesters where I split this course with Data Driven Reporting.

Spring 2014 course is a go!

I will be back again to teach Data Visualization at the University of Texas at Austin for Spring 2014. It will be a M-W course from 5-6:30 p.m. in CMA 4.146. The course is (08195) J 338G DATA VISUALIZATION or (08370) J 395 DATA VISUALIZATION for graduate students.  Spring 2014 schedule here.

Categories: Spring 2014

About this course

This class, Spring 2013, was the first time the University of Texas Journalism department has offered a course in data visualization, and I was lucky enough to teach it.

The topic is broad enough to cover a couple of semesters, but what I concentrated on during this first year was the introduction of concepts and free tools that any journalist could bring into their wheelhouse to produce better journalism.

The course Syllabus and Course Outline is available. The class should be offered again in the Spring, 2014.

Categories: Spring 2013

Working as one, the San Antonio Spurs

EDITOR'S NOTE: One of five final projects the Spring 2013 Data Visualization class for the University of Texas School of Journalism.

By Roy Varney, Rachel Marino, Arlena Cordero



A common adage in the game of basketball is that a fist is stronger than five fingers. Yet, in professional basketball, audiences are often subjected to prolonged sequences of disorganized one-on-one with little semblance of team play. If those selfish plays and players are individual fingers, then the San Antonio Spurs are a fist.

The Spurs, over the last 16 years, have become the face of selflessness in the NBA, and, in the process, the most dominant franchise in all professional sports. Since 1997, when San Antonio drafted power forward Tim Duncan, the Spurs have won four championships and over 70 percent of their games. In a time when big t.v. market franchises like the Chicago Bulls and New York Knicks struggled to find success, the Spurs remained a model of consistency.

San Antonio's winning ways began with the brains of the operation, Gregg Popovich. A former member of the United States Airforce, Popovich, has at times, come off as tight-lipped towards the media, but it has been his commitment and foresight that has driven the Spurs franchise to an unheard of period of excellence.

"Everyone in that organization survives at the pleasure of Gregg Popovich," said Dale Dye, head editor of Spurs-centric blog Pounding the Rock. "I'm a huge believer that organizations are successful from the top down."

Giving a head coach the autonomy to choose who works within an organization would often be considered a gamble. Seven NBA coaches have parted ways with their teams since November. Heavy turnover is the norm, not the exception. But Popovich is no ordinary coach: his ability to not only survive, but also adapt and excel in a rapidly changing game has set him apart.

In 2011, Popovich made a dramatic and surprising change to his coaching style. San Antonio began playing a form of run-and-gun, fast paced basketball that the Phoneix Suns had previously innovated.

"I would describe it as a lightning strike," Dye said. "Two years ago, out of nowhere, after spending over a decade with his hands stretched up to tell the team to slow down whenever they got moving too fast, in the preseason, after the sweep at the hands of the Phoenix Suns, Popovich is moving his arms like he's waving home a baserunner."

Indeed, San Antonio's pace, an advanced metric that counts the number of possessions a team plays in a game, has steadily risen from 90.7, or twentieth in the league in 2010, to 94.2, sixth in the league, in 2013. The Spurs have raised their offensive efficiency, another advanced metric that measures the number of points a team scores for every 100 possessions, from seventh in 2010 to first last year.

Story continues below visualization.
Higher offensive efficiency is better.
Lower defensive efficiency is better.
All data courtesy of basketball-reference.com

Learn About Tableau

Popovich, for his part, doesn't concern himself with these statistics.

"I'll look it up on my computer as soon as I buy one," Popovich recently joked when asked an analytics-oriented question.

Popovich focuses on the number of wins, and how his system can be effectively utilized to maximize this number. Duncan is the heart of this system. His ability to create offense for himself and his teammates in a high percentage, low risk fashion is exactly what Popovich instructs.

"Ruthlessly efficient, and ruthlessly meticulous about the way the offense is run," Dye, who writes under the pen name J.R. Wilco, described of the Spurs' system. "It's preparedness beforehand in order to dictate the tenor of what happens on the floor."

Meanwhile, the Spurs have continued to breathe life into Duncan's game through French point guard Tony Parker and Argentinian shooting guard Manu Ginobili. Together these three players have earned the nickname of "The Big Three," a homage to a similarly dominant Boston Celtics trio from the 1980s.

San Antonio's affection for foreign born players is no happy accident, as Popovich and RC Buford, their General Manager, visit Europe each summer to evaluate players. More recently, San Antonio acquired their starting center, Brazilian born Tiago Splitter, after he won the 2010 Spanish league Most Valuable Player award.

“Every team in the NBA has the option to recruit international players, you can’t really call that an advantage or a disadvantage,” said freelance writer and contributor for the Examiner, Scott Barretto.
“Since the Spurs drafted Duncan there has been a lot of international players added to the team. Popovich took his chances multiple times with a cheaper price tag, and hit the jack pot twice, with Parker and Ginobili.”

The Spurs' system works in such a way that complimentary players can be plugged in and replaced cheaply. Thanks to San Antonio's advanced scouting and player development, these players, like starting shooting guard Danny Green, will continue to be replenished.

"We can plug in these high production, low salary guys who work around our Big Three, our skill players, and, as a result, what that will net us is a whole bunch of people who know exactly what to do with the ball under all circumstances," Dye said.

However, no road to glory is unbloodied. Health has been a major obstacle for San Antonio. Through the years, a myriad of injuries during playoff time has inhibited the Spurs. In an attempt to mitigate this, Popovich has begun to periodically sit his starting players out during the regular season.

“Popovich is a straight-forward kind of guy,” Barretto said. “He manages his team strategically and knows when to rest his players. This is why Duncan, Parker, and Ginobili, despite their injuries, are able to dominate during regular season and the playoffs.”

This strategic management of minutes has been the source of controversy and drawn the ire of league officials. Popovich was fined $250,000 last November for holding all of his starters out from a nationally televised game against the Miami Heat. Despite this, Popovich sees no reason to change his mentality.

“What I do from my perspective is from a coaching perspective,” Popovich said in a public statement when asked about the fine. “And I think the league operates from a business perspective. And I think that's reflective in the action that they took.”

The mastery of a craft is a rare, near impossible task, but Popovich appears to have come as close as humanly possible. The Spurs, having swept the Los Angeles Lakers on Sunday, are one of the favorites to return to the NBA finals out the Western Conference. Each time San Antonio has reached the NBA Finals, they have won the championship. Can the team knock out a fifth?

"Assuming health, I give them their best shot in years," Dye said.

Categories: Project, Spring 2013

Affording Austin

EDITOR'S NOTE: One of five final projects the Spring 2013 Data Visualization class for the University of Texas School of Journalism

By Emily Donahue, Ning Sun and Mary Ellen Knewtson

Photo by David Ingram

Austin is booming. The population grew 37 percent in the decade ending in 2010. It ranks as one of the fastest growing cities in the country, regularly topping “Best of” lists for events such as SXSW, Austin City Limits Music Festival and F-1 racing. And, Austin fared better than most of the nation throughout the recession that started in 2008 – in February (the most recent statistics available) the unemployment rate was a mere 5.4 percent compared 7.7 percent for the nation as a whole.

If that’s not enough evidence to convince you, just take a look at the skyline. Big developments dot the landscape and construction cranes dot the horizon. And the real estate market is on fire. Austin’s median home price reached $220,000 in March 2013, the highest ever, according to data from the Austin Board of Realtors.

But all that glitters is not necessarily gold. That same real estate boom has priced many families out of the market. And it’s not just home prices. Rental prices have climbed steadily for the past 12 months.

Tableau project missing for some reason

Steven Tamayo is a Social Work major at the University of Texas at Austin. He rents a one-bedroom at The Axis in the West Campus neighborhood. It normally would have cost $1,200 a month. But his financial aid package qualified him for the SMART program – a developer incentive to incorporate affordable housing into mixed use housing units. He pays just $799.
Currently, there are no requirements that developers include affordable housing in their plans, but there are incentives. The City of Austin’s SMART Housing Policy provides fee waivers to developers who meet the policy’s standards, among them providing affordable housing options for low and moderate income families. “SMART” is an acronym for “Safe, Mixed-income, Accessible, Reasonably-priced, Transit-oriented.”

“Whenever I found out I was going to be able to pay rent that was significantly cheaper than what it was supposed to be I just took it and ran with it,” Tamayo said.

“Finding affordable housing stock has been a challenge for this city for a decade,” said Paul Hilgers the President of the Austin Board of Realtors. “It’s a huge issue for every urban community but now with the demand we have it’s really a huge issue for Austin.”

The city’s skyrocketing housing prices are simply a matter of supply and demand, Hilgers said. “There are challenges, that continue to and obstacles to affordability that when compounded with the market, make it very difficult for housing to be built.”

Austin currently has a limited supply of affordable housing available to lower income families and individuals. And it’s not clear whether the city will boost that any time soon. Last November, Austin voters did not approve a bond proposal that would have built new housing units within Austin’s city limits. Prop 15 was the only one of 18 ballot proposals that did not pass.

Affordable housing advocates are preparing a revised proposal for this November. Although the details aren’t yet finalized, it’s likely that a bond measure would include similar provisions to the last, which the City of Austin’s website says included money to fund development of affordable rental housing, preserve existing affordable housing and develop transitional housing for the homeless.

“Austin has gone through tremendous growth but part of that growth has meant there are fewer options for lower income families,” said Elliot McFadden, the manager of a pro-bond group called Keep Austin Affordable. “We see housing bonds as a critical part of creating more of that kind of housing and helping those folks stay in Austin.”

McFadden said a new affordable housing bond would have both home ownership and rental components, geared to serve families making half or less of Austin’s average income. For a family of four that would be about $39,000 per year, he said. The bond’s home ownership component would fund home repair programs for seniors, the disabled and fixed-income individuals. It also would go toward so-called “land banks”, which are plots of land purchased by groups such as Habitat for Humanity that will eventually be used to help low-income individuals purchase a home.

Photo by Jennifer Morrow

The Austin Board of Realtors is waiting to see the revised ballot language before it makes a decision to support a bond proposal this November, according to Board president Paul Hilgers.

In a city that has historically and politically opposed both density and sprawl, Hilgers said, developers are facing the complex problem of meeting marketplace demands while finding ways to meet the city’s need for affordable housing.

“[Voters] want to protect their neighborhood’s character,” Hilgers said. “In many cases that means they are fine with affordable housing as long as it’s not in their neighborhood.”

Because of these competing interests, the eventual solution will have to be one that is multifaceted, he said.

Keep Austin Affordable’s Elliott McFadden agrees. “Certainly housing bonds are not the only solution, but they are a good part of the solution,” he said.

Both also agree affordability is critical to the city’s long-term health. As Hilgers put it, “it impacts our transportation system, our air quality, our education system, our educational system, our healthcare system. Having good safe decent affordable housing is a critical component for any community’s sustainability.”

Data Sources: Austin Board of Realtors, City of Austin

Categories: Project, Spring 2013