Tag Archives: Tableau

Making a global projected country polygon map layer for Tableau

~

I have been struggling to find a solution for a common use case. I want a tool for global thematic webmapping that:

  • Complies with the UN Geospatial Information Section’s policy on displaying national borders and disputed territories
  • Looks good and performs well for highlighting country areas for display on a global scale
  • Shows small island states without having to zoom
  • Can be displayed in a projection appropriate for global thematic maps (e.g. Robinson)
  • Can be used to create dashboards on BI platforms like Tableau

I finally found a good candidate, and I want to share so that others can use it as well. Creating this layer involves an interesting exercise in QGIS, Mapbox, and Tableau. You can read more about that below, or skip it and download this Tableau worksheet to incorporate it into your own dashboards or reproduce by yourself.

Click on the image to view and download the Tableau worksheet

Here is an example of a dashboard that uses the map:

Click on the image to view the interactive dashboard

The first stop was finding the right vector map layer. Fortunately, the UN Geospatial Information Section recently published the “cartotile” layer, which uses a stylized design and renders all the country borders and disputed territories in the UN approved manner. It displays small island states as squares which are clearly visible even at maximum zoom. If you’re building your own site and are comfortable using Mapbox-GL-JS then your solution is here.

But I wanted something I could use in Tableau. I got my hands on the geojson files for this layer (including country polygons and borders). Simply importing into Tableau would not work because the map would be displayed in Web Mercator (Tableau automatically converts all geographic layers to Web Mercator.) rather in than the Robinson projection I wanted. The Mercator projection is not a good choice for global thematic maps because the area distortion at the high latitudes is too great. And Tableau is not able to do other map projections.

Enter this clever workaround developed by Sarah Battersby. The ideas is to create your map in the Robinson projection (or whatever one you want) but trick Tableau into thinking it’s projected in Web Mercator. Obviously this will not work if you have other geographic data you want to overlay in Tableau because the country polygons will not be shown in correct geographic space. But for our purposes of highlighting country areas it will work just fine.

You can use QGIS to reproject the layer and then alter the layer properties to trick Tableau by changing the coordinate system to Web Mercator. Enjoy the invigorating feeling you get from committing this spatial transgression!

You can then upload this layer into Tableau and join it with your country data. But there is one more step for the full solution. Recall that our UN map has a layer with borders, including some dashed lines. To add the borders we need to create a base map. To do this, use the same trick in QGIS with the borders layer, and then save it and create and style a tileset in Mapbox using the altered layer. Finally this Mapbox map can be added as a background map in Tableau. It’s all explained by Sarah in another detailed post.

There are other options for making a webmap in projections that are not Web Mercator. Flourish’s Projection Map template works very well, and you don’t have to mess with the above workaround. It changes the projection for you. You can also use Carto, as in this example. A really interesting development is Adaptive Projections from Mapbox. With this tool the map projection changes as the user zooms, so that zoomed-out views appear in a projection applicable for global viewing (e.g. Robinson), but zoomed-in views appear in Mercator to avoid distortion. It’s a pretty cool technical achievement.

Please let me know if you know of any other solutions or ideas for this type of mapping.

Advertisement

Updated Global Mercury Pollution Viz and Graphics

~

One of the first posts on this blog was about using Tableau to visualize data on global emissions of mercury.  I’ve gotten suggestions from a few people and given the graphic a bit of a face lift. Click on the image to see the interactive viz:

Dashboard_1 (3)

Click for interactive graphic

I also used the same dataset to make some static graphics using ggplot2 and the ggthemes package. I’d love any input on how to improve the the look and feel of both these and the Tableau viz. I’ve always found picking good colors very challenging, so thoughts on the palettes are especially welcome.

hg.emissions.bysec

The 8 industry sectors with the highest global mercury emissions. Data for 2010 from the 2013 UNEP Global Mercury Assessment.

hg.emissions.bycty_fewm

Countries with the highest mercury emissions. Data for 2010 from the 2013 UNEP Global Mercury Assessment.

How Much Mercury is in Your Favorite Seafood?

~

I’ve written before about mercury emissions, mercury as a commodity, and mercury use in artisanal mining. But the reason we pay so much attention to mercury is because of its human health impacts, and these are primarily caused by eating contaminated seafood.

Different types of seafood have different amounts of mercury. Because mercury is bioaccumulative, organisms that are higher on the food chain tend to have greater mercury concentrations. Of course, the particular environment where the organism lives also plays a big part.

Scientists have been interested in the mercury content of seafood for decades. Recently, a group of researchers undertook the herculean task of aggregating data from almost 300 studies. The result is the Seafood Hg Database (and an accompanying paper). The database contains the mean mercury concentrations measured in each study for one or more of 62 seafood categories. Overall, the database represents over 62,000 individual measurements from around the world.

It’s a great dataset to play around with and experiment with visualizations. In the graphic below, I plot mercury concentrations for a subset of common seafood types. Each circle represents the mean concentration measured in one study, and the size of the circle is proportional to the number of samples in that study. I’ve overlaid box plots for each seafood category that show the median of all the means, as well as first and third quartiles (whiskers go to 1.5x the IQR).

I think this is much more instructive than simply plotting the grand mean (average of all the study averages) for each seafood category. For one thing, you lose a lot of information on how much mercury concentration varies within a category. Take tilefish, for example. This is one of the species that EPA and FDA advise pregnant women not to eat. But there are relatively few studies of tilefish, and the mean mercury concentrations they measured vary by an order of magnitude.

Click on the image below to bring up the full interactive Tableau Public visualization:

Hg in seafood

Click on the image to see full version

One Chart that Explains Why Ukraine was Vulnerable to Revolution

~

After months of protests, Ukraine slipped into violence last week as government forces attacked protesters in Kyiv. Then, in a frantic 48 hours, President Viktor Yanukovych’s government collapsed, rival politician Yulia Timoshenko was released from prison, and Yakukovych fled into hiding.  It was a stunning victory for the “maidanovtsi”, those protesting on Kyiv’s Maidan and those supporting the protesters around the county and the world.

I’m reading Bruce Bueno de Mesquita’s The Predictioneer’s Game, which is about analyzing incentives to make political forecasts. This book got me thinking about Ukraine. Why did Yanukovych fall? Sure, he was corrupt, but so are many leaders in the region.

What happened in Ukraine was very complex. But it seems to me that at a basic level, the obvious corruption of the Yakukovych government,  combined with Ukraine’s relatively open and democratic society, led to an unstable situation.

To test this intuition, I looked at data from The Economist’s Democracy Index and Transparency International’s Corruption Perception Index. This plot shows where the former Soviet republics fit on the corruption – authoritarianism plane (click on the image for interactive version):

demo cor2

It is instructive to divide this plot into quadrants. The lower left quadrant shows those countries that are both very corrupt and authoritarian. These governments have survived very high levels of corruption in part because they resort to anti-democratic means of staying in power, such as restricting citizens’ political and civil rights.

The upper right quadrant contains nations with lower levels of corruption and authoritarianism. Chief among these are the Baltic states, which have enjoyed a high degree of stability. Georgia, although it experienced a revolution in 2003, has been more politically stable in recent years.

The lower right quadrant is a null set. We just don’t see countries that are very authoritarian but not very corrupt in this region. An example of a non-Eurasian country that sits in this quadrant would be the United Arab Emirates.

And then there’s the upper left quadrant: states that are less authoritarian but have high levels of corruption. Countries occupying this space have experienced lots of political instability. Kyrgyzstan has had two revolutions in the last decade: the Tulip Revolution of 2005, and the more violent second Kyrgyz revolution in 2010. Moldova suffered widespread unrest in 2009 (the so-called Twitter Revolution), although recent trends point to a more democratic and pro-European direction. And Ukraine had the Orange Revolution in 2004 before the political order was upended again last week as a result of Euromaidan.

Of course, there are many other factors that determine how likely a government is to fall. Economic growth and inequality surely play a part, as do the personalities and governing styles of individual leaders. Yakukovych, for example, was indecisive and incompetent, and many of his allies quickly abandoned him.

So what are the lessons here? Well, if you are going to blatantly siphon money away from your constituents while ignoring many of their basic needs, you better rule with an iron fist. If not, they are going to rise up and throw you out. Or better yet, don’t run a corrupt regime in the first place.

The events in Ukraine illustrate how a relatively democratic society, with a strong civil society and a (mostly) free press can be an important check on corruption in government. Although far from being “fully democratic” in the eyes of international indices, Ukraine was democratic and open enough for Euromaidan to take root and ultimately succeed.

What if Ukraine split in two?

~

If you’re interested in Ukraine, you are probably aware of the country’s east-west political and enthno-linguistic divisions. I wrote about this in a couple of recent posts. Not long ago, I began to wonder what Ukraine would look like if it split into two nations. Now, I don’t think this is going to happen, nor do I think it would be in the best interest of Ukraine. But with protests continuing in Kyiv and in many of  the regions, it’s worth investigating what these two hypothetical nations would look like.

For this exercise, I used data on Ukraine’s oblasts (regions) that I had gathered for an earlier post, and plugged them into Tableau Public. First, I had to decide where to put the new border. I took the vote shares for Yanukovych in the 2010 elections for each region and plotted them in ascending order:

two ukraines vote share

There is a sharp break where the vote share jumps to above 50% – a natural place for the division. Incidentally, it is interesting and unexpected that Zakarpatskaya region, in the far west of the country, had the highest level of Yanukovych support of all the Timoshenko-majority regions. What is going on there?

Transferring that division to the map produces the following result:

two ukraines map2

Let’s look at the key features of these two imaginary countries:

two ukraines table

West Ukraine is a bit larger, and has a slightly higher population – ~24 million versus ~21 million. It’s landlocked, and shares borders with all of Ukraine’s current neighbors. East Ukraine has a higher per capita income, and occupies  all of Ukraine’s Black Sea Coast.

Sheet 6

The chart above illustrates some additional features of the countries. East Ukraine is much more urban than the west, and contains many more Russian speakers (although it has a large minority of Ukrainian speakers). West Ukraine has a much smaller minority of Russian speakers.

I encourage you to take a look at the entire interactive visualization in Tableau by clicking on the image below.

Dashboard_1

More on the Regional Political Geography of Ukraine (Interactive Visualization Included)

~

Last week I wrote a post about Ukraine’s stark regional differences in language, ethnicity, and politics. There was a fair bit of interest, so I found some more data on regional demographic, ethnic, linguistic, and economic indicators in Ukraine and played around a bit in Tableau. I produced this interactive graphic, which illustrates the results of the 2010 presidential election for each region and presents some indicators that were correlated with support for Viktor Yanukovich  (native Russian speakers, ethnic Russians, urban population, and average wage).

I hope to play around some more with these regional data. What sorts of illustrations would you be interested in seeing?

And if you just can’t get enough of Ukrainian regional geography, this guy is the man!

ukr political geography

Click on the image to go to the interactive graphic

Using Tableau Public to Make an Interactive Visualization

~

Dashboard_1 For the last several weeks I’ve been experimenting with Tableau Public, a powerful, free software package for data analysis and visualization. I’m impressed by the software. It’s certainly the best free product I’ve worked with.

My big project on Tableau is an interactive graphic showing global mercury emissions by country and sector. I was able to get a nice dataset from the Arctic Monitoring and Assessment Program. These data were part of the 2013 UNEP Global Mercury Assessment. So they are very up-to-date, and with the adoption of the new Minamata Convention on Mercury, the topic is quite relevant. Good ingredients for an nice viz.

The graphic combines three elements: 1) a world map showing mercury emissions by country in a color gradient scale, 2) a tree map showing the mercury emissions of regions and their constituent countries as a part of total global emissions, and 3) a bar graph showing the makeup of emissions by industry sector for the world or selected countries. All the elements are linked so that selecting objects in one changes the other elements.

Check it out.