Sunday, September 24, 2017

User-Generated Batch Printing in Tableau with JavaScript

I love using Tableau to publish interactive data visuals, but if you publish Tableau visuals in a workplace, sooner or later you're going to get a request for batch-printing.

If you have Tableau server, this can be done using tabcmd, but it requires work by the BI team for each request. It's okay for a few one-off requests, but if you have hundreds of users who want on-demand or customizable batch-printing, it's not a great solution. Instead, we can allow the user to do their own batch printing using JavaScript.

The basic plan is:
  1. Make a URL that generates Tableau dashboard PDFs
  2. Make a dashboard that can be filtered to a list of the URLs
  3. Use the JavaScript API to get the list of URLs
  4. Use JavaScript to batch-download the dashboard PDFs
If you'd like to skip the tutorial, here's a working html file that contains the code and you can skip to the bottom for a demonstration. Even if you read the tutorial, the html file is useful to see all the code in the right order, and the JavaScript libraries used.

Step 1: Make a URL that generates Tableau dashboard PDFs

Tableau will generate a pdf of a dashboard view if you include ".pdf" at the end of the workbook name in the url. Tableau can also receive filter values as url parameters, which will allow the user to customize the view. Andy Kriebel has a nice blog post on url parameters here. Andy's post generates PNG files, but PDF is the same concept.

In my case, I created a one-page of home price data by major US city, using Zillow data. Here's an example using my city:

The url to create this pdf is:

https://public.tableau.com/views/HomePriceOneSheet_0/HomePriceProfile.pdf?:showVizHome=no&CityState=AtlantaGA

To switch to a pdf of Chicago, and change the time comparison to five years:

https://public.tableau.com/views/HomePriceOneSheet_0/HomePriceProfile.pdf?:showVizHome=no&CityState=ChicagoIL&TimeComparison=FiveYear

Once you have your URL, make a calculated field in Tableau that generates the URL, given the user selection. In my case:

Step 2: Make a dashboard that can be filtered to a list of URLs

Make a worksheet that contains only the URL variable and your filter variables:

"Time Comparison" doesn't appear in my filter list because it's a parameter. Next, make this pretty by putting the worksheet on a small dashboard with filters showing and the URL list hidden.



Step 3: Use the JavaScript API to get the list of URLs

I adapted this code from Tableau's github page on the getData() functions. First, we initialize the visual:
var viz, sheet, table;  
function initViz() {  
     var containerDiv = document.getElementById("vizContainer"),  
          url = "https://public.tableau.com/views/HomePriceOneSheet_0/BatchPrint", 
          //replace with your tableau url  
          options = {  
               hideTabs: true,  
               hideToolbar: true,  
               onFirstInteractive: function() {  
                    document.getElementById('getData').disabled = false;  
          }};  
     viz = new tableau.Viz(containerDiv, url, options);  
 }  
Next, we use the getSummaryData() function to get the urls and reshape the data array. This command will be called in our html from a button click (below), after the user has made their selection in the Tableau visual.
function getSummaryData() {  
      sheet = viz.getWorkbook().getActiveSheet().getWorksheets().get("URLs");   
      //replace "URLs" with the name of your worksheet containing the URLs  
      options = {  
           maxRows: 0, // Max rows to return. Use 0 to return all rows  
           ignoreAliases: false,  
           ignoreSelection: true,  
      };  
      sheet.getSummaryDataAsync(options).then(function(t) {  
           table = t;  
           var tgt = document.getElementById("dataTarget");                      
           var data = table.getData()  
           var value_array = new Array();  
           //reshape array to fit download code  
           for (var i=0;i<data.length;i++){  
           value_array[i] = new Array (data[i][0].value);  
           }  
           //call download function from above  
           downloadAll( value_array )  
      });}  
Step 4: Use JavaScript to batch-download the dashboard PDFs

The last function in the above code, downloadAll() has not been defined. This function loops through the urls and downloads each pdf. I adapted downloadAll() from this code. Unfortunately this code only works in Chrome; other browsers will just show a single PDF.
function downloadAll(files){  
      if(files.length == 0) return;  
      file = files.pop();  
      var theAnchor = $('<a />')  
           .attr('href', file[0])  
           .attr('download',file[0])  
           .appendTo('body');  
      theAnchor[0].click();   
      theAnchor.remove();  
      downloadAll(files);  
      }  
Last, we use html to call our JavaScript functions:
<body onload="initViz();">  
      <div id="vizContainer" style="width:100%; height:300px;"></div>  
   <div class="page-header">  
     <button id="getData" onclick="getSummaryData()" class="btn" disabled>Get Reports</button>  
   </div>  
      <div id="dataTarget"></div>  
 </body>  
Here's the code in action below. The files might take a few seconds to download because the Tableau Public server has to generate each one.

Home Price Profile by City: Batch Printing


A great next step for this project is to get the multiple file download to work in browsers other than Chrome. 

It would also be helpful to rename the PDF after the city instead of the dashboard name. However, JavaScript can't rename an external file, so this would require downloading the PDF to a web server, renaming, and then downloading to the client.

Another next step is to stitch together the pdfs. This also can't be done in JavaScript and would require an intermediate download to a web server.

Let me know in the comments if you use this code, or let me know on twitter.

Monday, December 12, 2016

Atlanta Parcel Data

Fulton County provides detailed property assessment data online, including property assessment, home age, and the property shapefile. The map below uses this data to show the construction date of each building in Inman Park:

Inman Park has several homes built before 1900, but the two most common decades are the 1920's and the 2000's. Scroll down for an interactive version with hover-over (best on a computer).

The next visual shows median home age by neighborhood. The oldest Atlanta neighborhoods are near downtown. These neighborhoods include Inman Park, Midtown, Adair Park, and Grant Park, all of which have a median home age of 1920. The visual also allows the user to add condos and town-homes to the median age, which changes the age significantly for some neighborhoods.


Unfortunately, the ages of homes built in the 1960's or earlier are only approximate. The graph below shows that home ages in the county data tend to be rounded to the nearest five or ten years.



This graph also suggests that the listed age of many homes that are older than 1920 are rounded up to 1920. If you people would like to help me out, you could spend several days researching the age of your home and your neighbors' and contacting the Fulton assessment office to correct the age. I understand that this can be approximated by looking at historical plat records, but several minutes of googling didn't tell me how to find those. (Update: see links at bottom of post.)

To view a map of home ages for any Atlanta neighborhood in Fulton County, use the interactive below. Mouse over a parcel to see more details or use the zoom tools in the upper left.



Dekalb County does not have similar data files publicly available. City of Atlanta has a parcel shapefile that includes Dekalb county, but the data is a few years old and has limited fields. Both counties have very functional interactive GIS maps online (FultonDekalb), and property lookups (FultonDekalb).

The parcel data can also be used to map appraisal values. Here's Morningside/Lenox by appraisal value:



The modal appraisal value is $500-600K, but there are a few homes appraised at over $2 million. Cross-checking with Zillow shows that homes in Morningside/Lenox tend to be listed for about 20% more than their appraisal value. 

Morningside/Lenox is actually a little larger to the east than the above map portrays, but that portion is in Dekalb County.

Use the interactive tool below to view appraisal data for any Atlanta neighborhood in Fulton County. 


We can also view average appraisal value by neighborhood on the map below. I've made a similar map using Zillow data, but the map using appraisals covers more neighborhoods and includes values of all homes. On the other hand, appraisal values are often lower than actual sale prices.


The map above also allows the user to add condos and town-homes. This feature demonstrates the importance of density for affordability. When condos and town homes are excluded, Midtown contains 752 homes with a median value of $498K. When condos and town homes are included, Midtown contains 8,920 homes with a median value of $207K.

Let me know in the comments or on twitter if you have questions or find anything interesting in the data.

Update: a helpful commenter from reddit shared a few links to research home ages:

BTW, there's a great resource for researching your property's history hereYou aren't going to find historical plat records, the best you can do is the old Sanborn Fire Insurance mapsThose will at least show you the shape of the building. There are some other great historic city maps here.

Wednesday, October 19, 2016

Atlanta MARTA Expansion

Atlanta voters will decide on a 0.5% sales tax to fund MARTA expansion this election. Major components of the plan include light rail on the BeltLine, more streetcar lines, and new MARTA stops on BeltLine intersections. 

MARTA has posted slides of planned expansion projects, but I have not seen projected ridership or a cost-benefit analysis. I consider the project in multiple ways below and decide that voters should not approve the tax. The proposed additions are poor choices, Atlanta residents have not demonstrated sufficient demand, the expansion would not be cost-effective, and MARTA already has growing funding sources.

Project Selection

Streetcars

This is the most obvious argument in a long list. In addition to a proposed streetcar extension, there are also street-level light rails that appear to be a streetcar by another name. Any proposal that includes streetcars is not a serious proposal for public money. It's ludicrous to me that our city would spend $100 million to install three miles of train tracks in a functioning street to do exactly what a bus can already do, with the exception of not being able to avoid obstacles or re-route during street closures.

Location

The current project plan is not MARTA's first choice. The agency had plans for an $8 billion expansion last year that included a northern expansion of heavy rail. However, funding for that plan was not approved. Instead, MARTA received permission for a referendum on sales tax in the city of Atlanta only, so they've developed a second plan, mostly inside city limits. This is significant because only 10% of the metro area's population lives inside the city. A public transit solution focused inside city limits is unlikely to have a large impact on congestion.

In addition to a limited geographic focus, the new plan does not serve the city's highest density areas. Consider the map below, which overlays MARTA stops and the BeltLine on top of a density map of Atlanta. The density map includes both residents and jobs. Existing MARTA rail has good coverage through the densest areas of downtown and midtown Atlanta. The BeltLine, on the other hand, circles the city mostly through less-dense areas. 



The busiest MARTA stops are those in high-density areas downtown, and those at the terminals, used as connectors for commuters who live outside the bounds of MARTA. Some of the least-busy stops are the intermediate east-west stops near the BeltLine. Residents there live in neighborhoods that are not dense enough to have large populations close to the station, and live close enough to downtown that waiting for a train isn't as fast as driving. BeltLine transit would be even less useful for these residents. Most riders would then have an additional transfer to get to their destination off the BeltLine, which further increases travel time and decreases use.


Project List


The project list includes $11.9 billion in costs. The cost estimates suggest $3.2 billion could be covered by federal grants, leaving $8.7 billion for the city. Of course, cost projections and ability to win federal grants are usually optimistic, so the price tag could be significantly higher. The proposed tax is projected to raise $2.5 to $3.5 billion over 40 years. Because the project cost exceeds the revenue, voters do not actually know what they are voting for- some parts might not be built at all, and some will be built regardless of the referendum, through BeltLine funding or growth in MARTA’s current revenue sources.

Demand for Public Transit

Trends

Trends over time are also helpful for thinking about the need for MARTA expansion. The graph below shows the percent change in the population, public transit usage, and traffic delay for the Atlanta metro area. The metro population grew 23% from 2002 to 2014, but public transit usage fell by 9.1% and traffic congestion fell by 3.7%. 


MARTA advocates have said that more public transit is needed for a growing population, but the trends over time do not support that claim. Although the population is growing, congestion and public transit use have not increased. Perhaps people are living closer to work, are more likely to telecommute, or traffic management has improved. Traffic patterns could change even more moving forward with the roll-out of self-driving cars. Atlanta should not commit to a new 40-year multi-billion dollar tax for transit infrastructure when demand is not growing and the future is even less certain.

The rising population leads to another interesting point. The majority of MARTA's current funding is through a sales tax. As the population grows, MARTA's funding increases, even though their ridership has not. Sales tax revenue from the growing population should then allow MARTA to pay for gradual expansions without new taxes.

Density

People who complain about Atlanta public transit often compare Atlanta to other cities with more public transit, like DC. But these comparisons are tenuous. DC is far denser than Atlanta, and public transit is much more effective in dense cities. Observe the relationship between density and transit use in large US cities. (From an earlier post
Daily trips assumes two unlinked trips per day):


Atlanta is one of the least dense cities in the US, but for our level of density we have more transit usage than other similar cities. 

Given that we already have high public transit use relative to density, building more transit is less likely to be a high-return investment.

Another interesting cross-city comparison is total commute time. Atlanta has the tenth longest commute times on this list, but about expected for a major city. Denser cities like New York, DC, and Boston have more public transit usage and longer commute times than Atlanta. These cities, of course, have a different commuting problem than Atlanta. Travel speed is slower in high-density cities because there are even more people in the way, and public transit makes sense for more commuters. However, Atlanta travel times are high because people are more spread out and have farther to go. Imposing the solution that works in New York or DC doesn't make sense for Atlanta.

Cost Benefit

One of the main benefits of public transit is that it removes cars from the road, which reduces traffic for other drivers. The Texas A&M Transportation Institute calculates the cost of congestion per driver for cities in the US. The Atlanta cost in 2014 is $1,130 per driver. This works out to $2.17 per commuting trip (2 trips per day, 261 work days per year). 

The external cost of commuting is similar to the operating cost of MARTA: the cost per trip for rail was $2.05 and the cost for bus (1) was $2.74. (Costs are net of fare- rail cost per trip was $3.14, minus an average fare of $1.09.) This seems to work out nicely- taxpayers are subsidizing public transit by the same amount we benefit by reducing traffic. However, this only includes the operating costs of MARTA, and not the capital costs. Capital costs are almost half of MARTA's total budget, so the total public subsidy to each ride is actually $4-$5, which is much greater than congestion costs.

Not only are current MARTA options not cost effective, but the proposed options will be even less so. The proposed options are in sub-optimal locations, which will reduce cost effectiveness, and the proportion of capital costs will be even higher, as the lines are not yet constructed. 

Equity

One of the arguments for public transit that is not necessarily addressed by cost benefit concerns is equity. Public transit through poor neighborhoods will give the poor access to more jobs, retail, and services and promote revitalization. Although the access component might be true (if usage is reasonably high), the revitalization component has not happened at current MARTA stops.

MARTA did not receive a single market-rate housing proposal for the Oakland City transit-oriented development project. Also, several neighborhoods near south and west MARTA stops have very low home values- several below $50 per square feet. Neighborhoods with existing rail are not being revitalized, so we shouldn't assume new rail would have a different effect. Public money would be better spent improving schools and crime in Atlanta's poorest neighborhoods, or creating more targeted attempts at investment. Also, voting against the tax does not eliminate public transit from BeltLine neighborhoods; they will still be served by bus.

Funding

The idea of public transit is inspirational- it's a public good that helps the poor, it connects communities, and reduces traffic congestion and pollution. Voting to support public transit gives a voter a warm glow. But let's consider not just the proposed funding, but current funding as well.

MARTA funding began with a 1971 vote that approved a 1% sales tax in Fulton and Dekalb counties. The sales tax was originally scheduled to drop to 0.5% but the 1% rate has been repeatedly extended, most recently in 2007 for a list of projects that included BeltLine transit. As explained above, the 1% sales tax is already a growing revenue stream as the population increases.

In addition to the current 1% MARTA sales tax, BeltLine transit also receives funding from the BeltLine tax allocation district (TAD). Property taxes from new developments and rising home values in the BeltLine TAD pay back the bonds used for BeltLine construction. So the proposed sales tax increase would be the third revenue source passed using the BeltLine as justification for funding.

Further, between the proposed MARTA tax and City of Atlanta infrastructure tax, Atlanta sales tax rates would rise to 8.9%- nearly the state maximum of 9.0%. This would limit the city's flexibility to raise future funds for valuable projects- especially since the new 0.5% MARTA tax would be locked in for 40 years.

Conclusion

We’re fortunate to live in a growing city- Atlanta will develop in exciting ways in the next forty years. But our optimism shouldn’t cause us to approve any public transit plan. Our leaders need to be accountable- present a plan of exactly what will be built, instead of presenting three times as many projects as the tax can fund. Build in neighborhoods that demonstrate demand for transit. Make a case for cost-effectiveness. And don’t build more ridiculous streetcars.

Wednesday, July 6, 2016

Atlanta Commuting Patterns

MARTA's proposed expansion plan has made me think about where people live and work in the city. A friend recently introduced me to the LEHD Origin-Destination Employment Statistics data which has work and home data by census block. This data shows interesting commuting trends in Atlanta. For example, most people who work at or near the airport tend to live south of the perimeter:

Most common home locations of employees at/near the airport

Larger end points indicate more people. Census tracts that make up less than 0.25% of the workforce are not included. So there are other employees north of the airport, just not enough to include in the map. The interactive version below has a tab, "Dots", that includes all locations and mouse-over that shows the actual number and percentage of commuters.

Another interesting census tract is the one that contains Emory University:


Most common home locations of employees at/near Emory

Emory employees tend to live close to campus or farther east. Notice the Emory image is more zoomed-in than the airport, and still shows most employees.

Neighboring census tracts can have much different commuting patterns. The south downtown census tract that includes city hall has employees who mostly commute from further south:


Most common home locations of south downtown employees (includes city hall)

And the downtown census tract that includes the Coke headquarters has more employees that commute from north of the office:


Most common home locations of north downtown employees (includes Coke)
I use Coke as an example of a well known employer in that census tract. But both of the downtown census tracts described span several blocks, and contain multiple employers. The interactive tool below shows this more clearly. Use the zoom tools in the upper left to more closely see which neighborhoods are covered. Or try the "Dots" tab to see a more complete view.



The visual also allows you to toggle from viewing all home locations of a given work location to viewing the reverse- all work locations of a given residential neighborhood. Using this option, I can see that most people in my neighborhood commute to downtown/midtown, or north to Emory or further. Very few commute south or east.


Most common work locations of residents in my census tract (West Kirkwood)
I started this data analysis as part of a post on the MARTA expansion, but decided on a stand-alone post. If you're interested in the MARTA post, follow me on twitter of google+, or use the e-mail subscription box on the right.

Sunday, June 12, 2016

Senators who voted against the Assault Weapons Ban



I'm feeling angry and upset about the mass killing in Orlando. My earlier, more hyperbolic title was, "Does your Senator support Terrorism?" but I've edited it to something more precise.

You can call, e-mail, or write your senators with this link, or find your congressperson here.

An assault weapons ban wouldn't affect most murders in the US, but it would be a good start. Here's a nice article by Adam Gopnik that suggests other sensible gun control steps.

Information on the 2013 assault weapons bill is here or here.

Sunday, May 15, 2016

WNBA Data Visuals: 2015 Season

I made a series of data visuals to understand the WNBA. Preview below; the full blog is posted at my new dynamic site (for mobile-responsive data views).


Tuesday, May 10, 2016

City of Atlanta Budget, FY2017

The City of Atlanta recently released their proposed Fiscal Year 2017 budget. The city provides a 640-page document explaining the budget, as well as interactive data visuals in the Atlanta Budget Explorer (ABE). I was part of the team that created ABE in 2014 after we built a prototype in a city hackathon. To supplement the information in ABE this year I created the interactive visuals below to explore the 2017 proposed budget.

The first visual shows the 2017 budget by department, and the changes from the 2016 budget in both dollar amounts and as a percentage. The visual is interactive; click a department to view spending by office and account.


The data show the police department is the largest city department at $181.39 million, which is a $6.8 million increase from 2016, or 3.9%. However, if you click on the police services, you'll see that the budget for uniform patrol has dropped by 1.5% (closer to 3% in real terms, assuming inflation). The Account drill-down shows that total salaries for sworn officers is increasing by 2.1%, so the decline in uniform patrol spending does not indicate a decline in the total number of officers. The budget book (p. 75) confirms there is not a decline in staff. 

The largest increase in the police department is $6.29 million for "SSP Administration". Consulting the budget book (p. 351), I can see that "SSP" is the "Strategy and Special Projects Division". However, it is unclear to me whether the SSP increase is new spending, or just a re-organization of existing services (perhaps out of uniform patrol, explaining the decrease). This is a good example of the breadth and limitations of the budget data used to create the visuals. As one might expect, the budget data set does not have detailed department descriptions or narratives. Therefore, the budget book is a useful compliment. For more detail, you can attend the public hearing on May 12 or email the city council.

The next visual shows budget changes by department over time. This visual is also interactive by clicking a department.


This view shows which departments have been consistently growing, such as city council, whose budget rose from $6.37 million in 2013 to a proposed $12.71 million in 2017.

The last visual shows revenue, both the 2017 amounts and the multi-year changes.


Property tax is the largest source of city revenue, but the visual shows the city raises money in several different ways. Property tax revenue is projected to fall this year, despite the city's growth. This is because some of the city's new construction falls in Tax Allocation Districts and due to roll-backs from the Georgia Taxpayer Bill of Rights.

The visuals above only show budget and revenue from the city's general fund. Both ABE and the budget book have more details on other funds, such as special revenue.

Of course the budget data and budget book don't answer every question about the budget (and can't be expected to do so), but both the level of detail and the accessibility of the information are impressive.

Update: Councilman Alex Wan has a nice Youtube video encouraging engagement in the budget process.