Home‎ > ‎Resources‎ > ‎

Fusion Tables Tutorial - Migration data

This tutorial will guide you through the process of visualizing a table that contains descriptive geographic data (country names) and create interactive maps and graphics that you can embed on your website or blog.

Sample Data set: Movement of people across borders

The table used for this example contains migration data per country and was downloaded in the World Bank Website:
http://wdi.worldbank.org/table/6.13#

Download the data as an Excel spreadsheet. Make sure to save the table as an Excel (.xls) file and not as a web HTML file. A copy of the data as an Excel sheet can be downloaded here.

Edit the column names to have one cell only per column name and add "Country" to the first attribute of the table that contains the names for each country.

A copy of the clean spreadsheet can be found here. Save it to your computer. You will use this file on your next step.

Accessing the data in Fusion Tables

Google Fusion Tables can be accessed through your Google Drive: https://drive.google.com 

Fusion Tables are a special type of tables that can be stored in your Google drive the same way you store documents, spreadsheets, presentations, forms, etc

Log in with your Google account and click the Create button. Select the Fusion Table option. If you don't see it, click on "Connect more apps" and type "Fusion Table" in the search box. This will take you to the Import new table

In the Import new table dialog, select the option on the left "From this computer". Click on Browse and upload the clean Excel table that you saved earlier.

Click Next.

Check that your table has column names correctly set up in row 1 and that matches the graphic on the right

Click Next. Click Finish.

What you see now is your initial spreadsheet as a Fusion Table. The first thing you might notice is that the Country field has all its records highlighted in yellow.

Creating a map in Fusion Tables

Fusion Tables automatically looks for a column that contains geographic information (in this case, country names) and identifies it as the column to do the geo-location of each record on your table.

Click the Map of Country tab and you will notice that the geocoding process will start locating each row based on the Country column. Behind the scenes, Fusion Tables is comparing the name on each record with places names on the Google Maps Database.

When this process is done, you will see a world map with a dot on each country that has be geo-located.

If you click on any of those dots, you will see that each dot corresponds to a record in the Fusion Table, and the pop-up window displays all the other column values for that record.

Check your results. You will notice that some records (such as Georgia or Egypt were places in the United States). Go back to the Rows 1 tab.

Navigate to the "Egypt, Arab Rep." row. Click on that record and you will see a small toolbar appears. Select the pen symbol to edit your record.

Change the name of the country to Egypt.

Click on edit geocode. You will see that now it places Egypt in the country of Egypt.

Accept the changes and save your edits.

You will notice that the row is not highlighted in yellow anymore. This means it has been geo-located.










Customizing the Configuration Window

You can change the display of the pop-up window for each country. In order to do this, click on the inverted triangle right next to Map of Country and select Change info window layout.

In the Automatic tab, you can check or uncheck the columns that you want to display in the pop-up window.

Uncheck the Personal remittances columns. Click Save. Check one country in the map view to see the new pop-up window.

Using basic Html code, you can customize the look and feel of the info window layout. Open to the Change info window layout. Select the Custom window and copy and paste the following text:

<div class="googft-info-window"
    style="font-family: sans-serif; width: 310px; height: 180px; overflow-y:auto">
      <h2 class="color: brown"><P ALIGN=Center>{Country}</h2>

<p>The Net Migration (in thousands) during the year 2012 was <b><em>{Net migration thousands 2012}</em></b> </p>
  <p>The total number of refugees according to its country of origin was <b>{Refugees by country of origin thousands 2011}</b> during 2011.</p>
</div>


Check your results. The pop-up window should look like the image on the right.

Merging two tables

Notice that each country is represented with a dot, which is not the most intuitive way of visualizing entire countries. If we want to represent each country as a polygon, it is possible to do so by joining or merging our table with another table that has country boundaries. The merge will be based on a common field, in this case, country names.

Go to File and select the Find a table to merge with... option. In the Suggest tables matching on section, select Country.

You will notice that a list of tables with a match percentage. Type country boundaries on the search box and click the search button.

Select the table with the highest match percentage, that should be the one at the top of the list. Your window should look like the figure on the right.

View the table. If you look closely at the column names, there is one that matches best the country names for your table. This is the WB2012 column and you will use it to match countries between both tables.

Click Next.

Select country as the matching column for the Movement of people across borders table. Select WB2012 as the matching column for the World Country Boundaries table.

Click Merge and look at your results. You will notice that Egypt is not displayed. Go back to your table and change Egypt to "Egypt, Arab Rep.". Take a look at the merged table now.

It automatically displays the changes to your original table. A copy of the new merged table can be accessed here

Configuring your Map Style

In your map view, click the inverted triangle and select Change map styles.

In the Polygons section, select Fill color and the Gradient tab. Check the Show a gradient button and for column choose Refugees by country of origin. Click Save.

Your map should display each country based on a gradient by the number of refugees in 2012.

Open the map style again and select Automatic Legend. Check the Show polygon fill legend and click Save.

Change the pop-up window style copying the HTML code that you used for your previous map.

Sharing and Embedding your Map

Now that your map is ready you want to share it and/or embed it in your website or blog. In order to do this, you need to change the privacy settings for your map.

Click on the inverted triangle and select Publish. Change your visibility to Anyone with the  link. Click on Publish again. Copy the HTML code and paste it into your website or blog (make sure your website is in HTML mode when pasting the code).

Your results should be similar to the map below:

Creating Interactive Graphics and Charts

Click the  + sign next to the Map of geometry and select the Add Chart option.
The default chart is a scatter plot of the first numeric column in your table.

For your continuous variable, check the box next to Emigration rate of tertiary educated to OECD countries in 2000.

For the Y axis, choose POP1993. Press the Change appearance button and check the log scale box.

Click the inverted triangle next to the chart tab and select Publish. Copy and paste the HTML code to your website.

Your result should look like the graphic on the right









Experiment with the other charts available in Fusion Tables. Below are some examples.

Network Graphic of Net Migration in 2012

Emigration rate of tertiary education

Resources

List of open source data visualization tools:
http://www.americas.datafest.net/resources/data-tools
http://www.creativebloq.com/design-tools/data-visualization-712402

SelectionFile type iconFile nameDescriptionSizeRevisionTimeUser
Ĉ
View Download
  48k v. 2 Oct 20, 2013, 10:41 AM Patricia Carbajales
Ĉ
View Download
  50k v. 1 Oct 20, 2013, 10:39 AM Patricia Carbajales
Comments