Fusion Tables Tutorial - Mexico Drug Murders

Preparing Your Data in Fusion Tables

For this session of the workshop, you will be working with a csv file created by The Guardian, containing the number of murders per month of the different municipalities in Mexico since 2006. For more information about this data, please visit: http://www.guardian.co.uk/news/datablog/2011/jan/14/mexico-drug-war-murders-map.

  1. Download the data modified for this workshop. Save the data to your desktop and rename it adding your own Full Name to the .csv file name. Credit: The Guardian. The original data can be found here
     2.  Import the data into Fusion Tables. Log into Gmail with your personal account.
           2.a Go to Google Documents: Click on Create - Table (beta). See graphic below.

            2.b Import the table choosing From this Computer and browse to the table you just downloaded.

            2.c Click Next and leave the defaults as shown below:

            2.d Click Next and Finish. Your table should look something like this:



            2.e Get acquainted with your data. Fusion Tables is very similar to other spreadsheets programs.
            Use the scroll button to see all the fields and make sure you understand what they mean.


Create a Google Site to visualize the outputs of Fusion Tables

  Each one of us is going to create their own Google Site to publish the graphics and maps we will be creating in Fusion Tables.
  1. Go to: https://sites.google.com/
  2. Click on Create
  3. This is the website you will use to save the graphics and maps created during this session.

Map Visualization

Using Fusion Tables we will be creating something similar to this map:



Notice there is a column with text highlighted in yellow. Fusion Tables chooses the most left column of any table that possibly contains geographic information. In this case, "Municipio, Entidad Federativa Mexico" is the column we want to use to geocode our table.

The original dataset only had the municipalities, but not the country or state, so if you use that data, some of the municipios, such as Durango, will get located in places outside of Mexico. This is why The Guardian added the Entidad Federativa Mexico string to the location field.

If the column you want to use for geocoding is not hightlighted in yellow, go to Edit - Modify Columns and select the column you want to use. Under Type, make sure you choose Location, as shown below:


  1. Geocoding

    Click on Visualize - Map
    The Google engine starts geocoding (locating objects on the surface of the Earth) based on the "Municipio, Entidad federativa Mexico" field.


  2. Changing the Symbology

    Click on one of the dots to see the window-pop up that comes with it. By default every location has the same red dot and the pop-up window will include all of the columns from the table.

    Click on Configure Styles:


    You have several options to display your locations:

    - Fixed: uses the same default symbol for all points
    - Bucket: uses different symbols based on classes made from buckets of a numeric field
    - Column: you can have different symbols for different rows based on a column that you populate with the symbols you want. For more information, click on the Learn More link.
    In this case, we will use a column that I have previously populated with the marker name I wanted to use "parks".

    Click on Column. Choose Buckets as the column. We will use the field "TOTAL DRUG WAR DEATHS". Switch your visualization and go back to Table. Scroll to that Field. Right click on the field and select Sort Ascending and Sort Descending. The minimum number of deaths is 1 and the maximum number is 6437. We will divide the column in 4 buckets. Choose the symbols for each bucket as indicated in the window below.


  3. Changing the Pop-Up Window

    Click on one of the new markers to see the pop-up window. You will notice that by default, all of the columns are added.

    Click on Configure Info Window:


    In the Automatic tab, all of the columns are checked to be displayed on the map. Uncheck some of them and see the results on the pop-up window.

    Click on Configure Info Window again. In the Custom tab, you can do basic HTML coding to change the color and size of fonts, alignments and programmatic text added to the pop-up window.

    Copy and paste the text below into the Configure Info window (Custom HTML text box):

    <div class="googft-info-window"
    style="font-family: sans-serif; width: 400px; height: 180px; overflow-y:auto">
    <h2 class="color: brown">{Municipio, Entidad federativa Mexico}</h2>
    The total number of Drug War Deaths for this municipality since December 2006 is: <b>{TOTAL DRUG WAR DEATHS}</b><br><br>
    The total number of deaths in <b>2009 </b>was<b> {2009 total}</b>.
    In <b>2010 </b>was<b> {2010 total}</b><br>
    The increase in murders from 2009 to 2010 for this municipality is <b>{Increase 2009-2010}</b>
     <p>See more information about this data at <a href="http://www.guardian.co.uk/news/datablog/2011/jan/14/mexico-drug-war-murders-map">
          The Guardian Website</a></p>
    </div>


    Your window should look something like this:


    Click on the Save button.

    Congratulations! You just created your first Map using Fusion Tables!!
  4. Sharing your Map!

    There are several ways that you can share your map:

    - Get link: copy and paste the link to anybody who would like to see the file as it is in Fusion Tables.

    - Share: it gives specific people permission to access the datatable you just created in Fusion Tables. Emails them the link to the map. They will need to sign it to see it. This button is also used to change the privacy options of your table, so you can embed it on your website.


    - Export to Kml: creates a static copy of the map data for viewing in Google Earth. You can use email the file to colleagues or upload it to your website to share as single file. However, any changes to the Fusion Table that you do afterwards will not appear in this static copy of your data.

    Save the Kml to your local hard drive and double click to open the file in Google Earth. Explore your data briefly. Below is a snapshot of the Kml in Google Earth.

- Get Kml Network Link: it creates a dynamic link so you can make changes to your Fusion Tables data after you already shared it and these changes will be reflected in Google Earth. However, your map must be shared as Public or Unlisted for this to happen.


In Google Earth, click on Add menu - Network Link. You will have a dynamic connection to your data in Fusion Tables.

- Get embeddable Link: it allows you to embed the interactive map in your website. This option also requires you to share the map as Public or Unlisted.


You will embed the map you just created into your own Google Site.
  1. Share the map

    1.1 From the map view, click the Share button.

    1.2 Under Visibility options, select Public or Unlisted radio button

  2. Embed the map

    2.1 Click Get Embeddable Link

    2.2 Copy the HTML code in the box (select the code and click Control + C)


    2.3 Navigate to your Google Site that you created at the beginning of this session.


    2.4 Click on the Edit Page button

    2.5 Change the edit settings to HTML editing mode by clicking on the Edit HTML source button

    2.6 Paste the HTML code into your Google Site window that appears.

    2.7 Click Save to stop editing and save your edits.


Congratulations! You have your own website with an interactive map all created by yourself with no programming experience!!

LINE CHART: Total Drug War Deaths Per Municipality

Our goal is to create an interactive line chart graphic like the one shown below that displays the total number of drug war deaths per municipality.


  1. Go to Visualize - Line:

  2. Select Municipio, Entidad federativa Mexico as your X Axis and TOTAL DRUG WAR DEATHS as your Y Axis


  3. Apply a Filter. We want to show in this graphic only the municipalities with more than 200 total murders. Click on options and follow the settings shown below. Click the Apply button after you add the condition.



  4. Click on Get Embeddable Code



  5. Copy and Paste the HTML code in your Google Site. Remember, you need to be in an editing mode and it has to be in an HTML viewing setting.

  6. Save your edits on your website and explore the line chart.
Congratulations! You just created your first interactive Graphic with Fusion Tables!!

Bar Chart:  Increase in number of deaths between 2009 and 2010

In this exercise we will be creating an interactive graphic that displays the increase or decrease in numbers of deaths between 2009 and 2010. We will restrict the data to show only municipalities with more than 250 total murders. In order to do this, we will again have to "filter" the database on the fly. The results are displayed below.


  1. Go to Visualize - Bar:

  2. Add a Filter: only the municipalities with more than 250 total deaths. Following the settings below:


    These are the settings for the Filter:
    Filter field: TOTAL DRUG WAR DEATHS. Relation: greater than (>). Condition: 250

  3. Click on the Apply button

  4. Select the graphic fields:
    Entity: Municipio, Entidad federativa Mexico
    Value: Increase 2009-2010

  5. Click on Get Embeddable Code

  6. Copy and Paste the HTML code in your Google Site. Remember, you need to be in an editing mode and it has to be in an HTML viewing setting.

  7. Save your edits on your website and explore the bar chart.

PIE CHART: Percentage of number of deaths in 2010 by municipality

We are going to build an interactive graphic that shows the percentage that each municipality had in the total number of deaths for the year 2010. For this purpose, we will have to filter the data to display only municipalities with more than 400 total murders. The final graphic will look like this:

  1. Go to Visualize - Pie.

  2. Add a Filter: only the municipalities with more than 400 total murders. Following the settings below:


    Settings for the Filter:
    Filter field: TOTAL DRUG WAR DEATHS. Relation: >. Condition: 400

  3. Click on the Apply button

  4. Enter the graphic settings. Entity: Municipio, Entidad federativa Mexico. Value: 2010 total

  5. Click on Get Embeddable Code

  6. Copy and Paste the HTML code in your Google Site. Remember, you need to be in an editing mode and it has to be in an HTML viewing setting.

  7. Save your edits on your website and explore the pie chart.

SCATTER PLOT: Increase in deaths from 2009 to 2010 by total number of deaths

The goal of this graphic is to show the trend in numbers of deaths from 2009 to 2010 based on the total number of deaths.

  1. Go to Visualize - Scatter.

  2. Follow the settings on the graphic below:

  3. Click on Get Embeddable Code

  4. Copy and Paste the HTML code in your Google Site. Remember, you need to be in an editing mode and it has to be in an HTML viewing setting.

  5. Save your edits on your website and explore the scatter plot.


TIMELINE: Number of deaths per month Dec 2006-Nov 2010 

In this final exercise we want to build an interactive timeline that displays the total number of deaths per month to try to see some trends per year. Our final graphic should look like this:


  1. Get the data. In this case, we will use another table that I modified to fit the timeline format. Click here to get the data and download it to your computer. If you have time, take a look at the original table and compare the results you get in Fusion Tables. This will help you to bring your own timeline data in the future.

  2. Import the data into Fusion Tables. Go to Google Documents: Click on Create - Table (beta). Import the table from computer and browse to where you saved the table. Make sure you select row 2 as the header for the table, just as shown below:


  3. Go to Visualize - Timeline:

  4. Follow the expression below:


  5. Explore the timeline graphic. Click on the vertices on the chart to see their values and click on one of the scroll buttons (shown below) to narrow your timeline.

  6. Click on Get Embeddable Code

  7. Copy and Paste the HTML code in your Google Site. Remember, you need to be in an editing mode and it has to be in an HTML viewing setting.

  8. Save your edits on your website and explore the timeline graphic.

Congratulations! You just finished your Introductory Session in Fusion Tables!!

ċ
Mexico drug war monthly deaths since 2006modifiedbyPC.csv
Download
  1k v. 1 Apr 6, 2012, 9:01 AM Patricia Carbajales
Ĉ Mexico drug war monthly deaths since 2006.xlsx
View Download
  9k v. 1 Apr 5, 2012, 8:13 PM Patricia Carbajales
ċ
Mexico drug war murders municipal breakdown from The Guardian.xls.csv
Download
  168k v. 1 Apr 5, 2012, 8:00 PM Patricia Carbajales
Ĉ Mexico drug war murders original data.xls
View Download
  1095k v. 1 Apr 5, 2012, 8:00 PM Patricia Carbajales
Comments