Photo Art in Tableau

Do you know what pointillism is? It’s a technique of painting with small distinct dots to create an image. And you can do this with Tableau! Well, sort of.
I made this series of pointillistic representations of well known photographs and paintings for Tableau’s Viz As Art contest. Be sure to click on the “photoart” tab to view my collection of 10 images, as well at the “revolution” tab.

I used Processing to extract each pixel’s coordinate and colour value, convert RGB colour values to greyscale and output the table to a text file. The Processing code is listed below. Note that I am yet to find a way to properly highlight Processing syntax in WordPress, let me know if you use a plugin that understands Processing. If you are wondering about the RGB to grayscale conversion step, read this Wikipedia article.

PImage photo;
PrintWriter textFile;
//enter name of file you want to process (no extension)
String file = "Obama";

void setup() {
  //load image
 photo = loadImage(file + ".png");
  //initialize output text file
 textFile = createWriter("txtfiles/" + file + ".txt");
    for (int column = 0; column < photo.width; column++) {
      for (int row = 0; row < photo.height; row++) {
      //extract pixel's colour value
      color c = photo.pixels[row*photo.width+column];
 //convert RGB values to grayscale
      int g = round(red(c)*0.2126+green(c)*0.7152+blue(c)*0.0722);
 //print output to console (optional)
 println(column + "," + row + "," + g);
 //save output to text file
      textFile.println(column + "," + row + "," + g);
      textFile.flush();
      exit();
  }
 }
}

I imported the resulting text files to Tableau and plotted them as scatterplots, varying the symbol Size by the reverse of grayscale values (I wanted larger symbols for darker areas or lower grayscale values). I also binned the grayscale values and applied a 4 colour palette to points.

This technique works best with clean, contrasty images with easily discernible shapes. You may try to experiment with different sizes of original images, but I find that lower resolution gives better results, so before conversion to text I resampled all my source images to 100×100 pixel size. That results in a viz that has abstract quality, as opposed to being a close replication of the original.

Back to top|Contact me

Capital Punishment in the United States

This is my entry to the Tableau Storytelling Viz Contest. I wanted to draw attention to the issue of the capital punishment in the United States. US, along with Japan, are the only G8 countries where the death penalty is still legal and vigorously exercised. And with the number of executions conducted each year, US is ranked among countries like Iran, Iraq, China, North Korea or Saudi Arabia. Considering these countries’ human rights violation track record, US does not want to belong to that club.

I started with some fast facts about the death penalty, followed by a chart showing the number of executions in the US, starting with the first one in America (in 1608) , all the way to 2014. This is a very revealing chart, showing  how the number of death penalties and their methods changed throughout history. Don’t forget to click on the info buttons to learn about the major historical milestone events related to capital punishment.

The story follows with details of the more recent capital punishment statistics and comparison between US and other countries. Later I concentrate on Texas, which is an undisputed leader in the number of death penalties carried out in the US. I wanted to humanize the numbers by showing who the inmates were, their age, education and how much time they spent on death row. The most poignant are hundreds of last statements of executed offenders, in which they usually turn to religion, family, ask for forgiveness or claim their innocence.

If you believe that the death penalty has no place in the modern world, please check the Amnesty International’s website linked at the end of my story, check their work  and see if you want to get involved.

Sergio Rosales - The last words are just incredible… “Luis Ramirez” sustained his inocense up to the last moment, was he inocent? that’s why there’s many against Capital punishment, ’cause not guilty people may be murdered with no reason…

Great Viz

Greg Hanowski - Nice graphics and a lot of work putting it together I’m sure. What would be better to convince people would be a story of how those countries that don’t use the death penalty have lower crime rates than those that do, if that is true. I hope it is because I believe “thou shalt not kill” applies to the good guys too and if history proves anything, it’s that violence begets more violence.

Back to top|Contact me

Data Scraping with Google Refine and Excel

I was recently looking for information on the death penalty in the US and came across the website of Texas Department of Criminal Justice and their full list of offenders executed since 1982. Grim topic, without question, yet good example of interesting web data ready to be scraped. Here’s how I extracted data from the TDCJ website.

To get started, open this web page, select and copy all entries in the table and paste it in an Excel spreadsheet.

executed_offenders

This should give you a nice table, including data and links to “Offender Information” and “Last Statement” pages for each record. These subpages contain additional information that we want.  You can extract hyperlinks from “Link” column in Excel using a macro described in this tutorial.

Now, let’s extract the data hiding behind the linked subpages. I used Google Refine (it’s the same as Open Refine, by the way), which is a free download available on Google’s website. Once installed and executed it will run in your browser (you are running Chrome, aren’t you?). Create a project, choose to get data from This Computer and select the spreadsheet file you just created. Choose to parse data as “Excel file” and click “Create Project” button. Your imported data should look similar to this:

Google_Refine

Each Info Link leads to the “Offender Information” page with additional data about each offender. Some of these pages contain just a scanned image of a hard copy document but many contain information in a nicely structured and consistent format like this:

offender_info

Let’s say that we want to extract Date of Birth for each offender. Go to one of the sub-pages and view its source code (in Chrome, right-click on the page and select “View Page Source”. Search for “Date of Birth” and note its location in the HTML table:

source_code

The “Date of Birth” record is stored in the third <tr> and its first <td> element, and the main table’s class is “tabledata_deathrow_table”. Make a note of this and switch to Google Refine. Click on the drop-down menu of the column “Info Link”, select “Edit column” and “Add column by fetching URLs…”, give a new column a name and click OK.  We have over 500 pages to fetch so grab a coffee, and when you come back you’ll have a new column ready with a bunch of HTML code in each cell. Click again on the drop-down menu of the newly created column,  select “Edit column” and “Add column based on this column…”, name the column and put this code in the Expression window:

value.parseHtml().select("table.tabledata_deathrow_table")[0].select("td")[6].htmlText()

Note: remember that indexing starts at zero in Google Refine.

For the detailed explanation of this code refer to GREL (Google Refine Expression Language) documentation.

This should give you an idea on how to extract data from structured HTML pages with Google refine. Once you are done, export your table to Excel or text file and have fun with it in Tableau! I’ll post my interpretation of this data soon.

Back to top|Contact me

Earth Impact Craters Discovered to Date

There is a database of confirmed impact structures around the world. It was conceived by a Canadian scientist in 1955, and since then 184 craters were identified, most of them through studying aerial photographs. The database is currently maintained by the Planetary and Space Science Centre at the University of New Brunswick, Canada. Let them know if you think you discovered an impact crater!

Back to top|Contact me