Click on the numbered tabs to navigate this lesson.
Critical to any investigation and story-telling that invovles trends or the scale of human trafficking in a particular area is the movement of victims. Mapping out a network using simple online tools can rapidly speed up your investigation, and may be an important part of the information that a reader needs to know.
In this lesson, we are going to use the free online tool Flowmap.blue in order to visualise geographic data about reported victims of human trafficking. Flowmap.blue is an open source project which produces interactive maps you can embed in your story. The only requirement is that you credit the website when using a graphic created on it.
Full instructions for using Flowmap.blue can be found on its homepage. There are two ways to create a map, and we’re going to show you how to do it using Google Sheets as the data source. You will need a Google account to follow this tutorial, and it’s recommended that you have taken some of our basic Sheets tutorials first.
Open this Sheet and go to File>Make a copy in your own Google Drive.
The data in this sample Sheet is an extract from the database of human trafficking cases at the Counter Trafficking Data Collective.
Please note that this is not the full dataset of cases and the data has been prepared for the purposes of training only.
We have extracted a table containing information about a victim’s nationality in Column A and the country of exploitation in Row 1. So in Cell ZA9, you can see that 53 cases of trafficking have been recorded in Uganda in which the victims were from the Democratic Republic of Congo.
A full list of standardised country codes can be found here.
In the second tab of this Sheet, All country codes, we have created a separate list of all the codes used in the matrix.
Geocoding the map points
In order to draw a “flow”, or arc, showing the connections between nationality of victim and country of exploitation, we first need to goecode the country codes so that they can be referenced on a map. Fortunately, Flowmap has a tool that can do this.
In the second tab of this Sheet, All country codes, we have created a separate list of all the codes used in the matrix (both in columns and rows). Simply select all the values in Column A by clicking in cell A2 and dragging your mouse to the bottom of the table. Press CTRL + C to copy the list, and then open up the geocoding tool here and press CTRL + V to paste.
Importing the map points
Now click the Geocode button, and you should see a list of countries with a reference in latitude and longitude. Click anywhere in this panel, and press CTRL + A to select all the data in it, then CTRL + C to copy it to your clipboard.
Back in your Google Sheet, create a new tab using the + button in the bottom left, and paste your geocoded data into it.
Getting your data in to the right format
Flowmap.blue visualises data between points which have to be referenced by a numerical ID, so we will need to re-encode our data slightly. We are going to create list of IDs in the All country codes tab, by going to cell B2 and typing “1”, then going to cell C2 and entering the formula =sum(B2+1).
Click on the bottom right-hand corner of the cell C2 with your mouse and drag it all the way to the bottom of the table. You should now have a Sheet that looks like this.
Each country now has a reference number we can use for Flowmap.blue, but we need to do a bit more manipulation of our data yet.
Using the ID numbers with VLOOKUP
Now we are going to use the VLOOKUP (vertical lookup) formula to insert numerical IDs for each country into our tables we have created. VLOOKUP allows us to match the data in a cell to a row in another tab, and bring back some value from that row. If you haven’t used VLOOKUP before, there’s more information here.
In your tab with the Geocoded data we created just now, select column B (name), right click and select Insert column left. Call this new column id2. In cell B2, enter this formula:
=VLOOKUP(A2,‘All country codes’!A:B,2,false)
What this is doing is asking Google Sheets to look for the value in cell A2 (UAE) in columns A and B of your tab All country codes, and then return the value in the second column (counting from the left). This should be 1.
Now click in the bottom right-hand of cell B2, and drag to copy the formula over all cells to the bottom of the table. You should have a sheet that looks like this.
Using VLOOKUP on the cases table
In the same way, we need to use VLOOKUP on the cases table to replace country codes with their numerical ID.
Create a copy of the Count of cases tab by clicking on the down arrow next to the tab name and selecting Duplicate.
In Cell A2 (AF), enter this formula:
=VLOOKUP(‘Counts of cases’!A2,‘All country codes’!A:B,2,false)
This time, we are asking Sheets to look at the original table (Counts of cases) and find the ID number we generated in All country codes. It should return a value of 2. Drag the formula down to the bottom of the table.
We need to do the same thing in the top row of the table – but we need to use a slightly different formula. In cell B1 type out this:
=vlookup(‘Counts of cases’!B1,‘All country codes’!$A:$B,2,false)
Note the use of the dollar signs in the middle. This is so that when we copy the formula to the next column (C), Sheets doesn’t change the A:B to B:C and so on.
Now click the bottom right-hand corner of cell B2 and drag it out over the entire row to BD2. You should have a table that looks like this.
Creating the flows table
Click anywhere in the table you have just created and press CTRL + C to copy the entire table to your clipboard.
Flowmap.blue has a handy tool to convert this table into three columns, showing the first location, the second location and the number of people associated with this flow. Open the OD-Matric-Converter here, and click in the first pane. Press CTRL + V to paste your data. You’ll need to scroll to the beginning of your input and delete the words “country of citizenship”, now click Convert. Your page should look like this.
When you are happy, put your cursor in the second pane (Output TSV) and press CTRL + A, CTRL + C to copy the output.
Back in your Google Sheet, create a new tab and paste this fresh table into it.
Creating the template
Now you need to create a new spreadsheet with the correct layout that Flowmap.blue is expecting in order to create a map. Click on this link, and make a copy of the Sheet in your Google Drive (File>Make a copy).
Complete the first tab with information about the map you are creating.
In the second tab, copy the data from your Geolocation sheet (don’t include the first row).
And in the final tab (flows) copy the data from the table we just created showing the flow between points.
Creating your first Flowmap
Finally, click on the share button at the top right of your screen, and change the sharing settings to “Anyone on the internet” and “View”, then click Copy link.
You’ll see a field to paste this link on the Flowmap.blue homepage. This will then generate a link to your own map. If all has gone well, it should look like the one at this link.