One of the many ways of using OpenRefine is as a toolkit for getting a feel for the range of variation contained within a dataset using the various faceting options. In the sense of analysis being a conversation with data, this is a bit like an idle chit-chat/'getting to know you' phase, as a precursor to a full blown conversation.

Faceted search or faceted browsing/navigation typically provides a set of search filters to a list of search results that limits or restricts the displayed results to ones that fulfill certain conditions. In a library catalogue, the facets might refer to metadata fields such as publication date, thus allowing a user to search within a given date range, or publisher:

Facets in a library catalogue

Where the facet relates to a categorical variable – that is, where there is a set of unique values that the facet can take (such as the names of different publishers) – a view of the facet values will show the names of the different publishers extracted from the original search results. Selecting a particular publisher, for example, will then limit the displayed results to just those results associated with that publisher. For numerical facets, where the quantities associated with the facet relate to a number or date (that is, a set of things that have a numerical range), the facet view will show the full range of values contained within that particular facet. The user can then select a subset of results that fall within a specified part of that range.

In the case of OpenRefine, facets can be defined on a per column basis. For categorical facets, Refine will identify the set of unique values associated with a particular faceted view that are contained within a column, along with a count of how many times each facet value occurs throughout the column. The user can then choose to view only those rows with a particular (facet selected) value in the faceted column. For columns that contain numbers, Refine will generate a numerical facet that spans the range of values contained within the column, along with a histogram that provides a count of occurrences of numbers within small ranges across the full range.

So what faceting options does OpenRefine provide?

Here’s how they work (data used for the examples comes from Even Wholesale Drug Dealers Can Use a Little Retargeting: Graphing, Clustering & Community Detection in Excel and Gephi and JSON import from the Twitter search API):

Exploring the set of categories described within a column using the text facet:

Faceted views also allow you to view the facet values by occurrence count, so it’s easy to see which the most popular facet values are:

You can also get a tab separated list of facet values:

Sometimes it can be useful to view rows associated with particular facet values that occur a particular number of times, particulalry at the limits (for example, very popular facet values, or uniquely occurring facet values):

You can look at the range of numerical values contained in a column using the numeric facet:

You can look at the distribution over time of column contents using the timeline facet:

Faceting by time requires time-related strings to be parsed as such; sometimes, Refine needs a little bit of help in interpreting an imported string as a time string. So for example, given a “time” string such as Mon, 29 Oct 2012 10:56:52 +0000 from the Twitter search API, we can use the GREL function toDate(value,"EEE, dd MMM y H:m:s") to create a new column with time-cast elements.

(See GRELDateFunctions and the Java SimpleDateFormat class documentation for more details.)

You can get a feel for the correlation of values across numerical columns, and explore those correlations further, using the scatterplot facet.

This generates a view that creates a set of scatterplots relating to pairwise combinations of all the numerical columns in the dataset:

Clicking on one of these panels allows you to filter points within a particular area of the corresponding scatter chart (click and drag a rectangular area over the points you want to view), effectively allowing you to filter the data across related ranges of two numerical columns at the same time:

A range of customisable faceting options are also provided, that allow you to define your own faceting functions:

• the Custom text… facet;
• the Custom Numeric… facet.

More conveniently, a range of predefined customized facets are provided that provide shortcuts to “bespoke” faceting functions:

So for example:

• The word facet splits strings contained in cells into single words, counts their occurrences throughout the column, and then lists unique words and their occurrence count in the facet panel. This faceting option thus provides a way of selecting rows where the contents of a particular column contain one or more specified words. (The user defined GREL custom text facet ngram(value,1) provides a similar (though not identical) result – duplicated words in a cell are identified as unique by the single word ngram function; see also split(value," "), which does seem to replicate the behaviour of the word facet function.)
• The duplicates facet returns boolean values of true and false; filtering on true values returns all the rows that have duplicated values within a particular column; filtering on false displays all unique rows.
• The text length facet produces a facet based on the character count of strings in cells within the faceted column; the custom numeric facet length(value) achieves something similar; the related measure, word count, can be achieved using the custom numeric facet length(split(value," "))

Note that facet views can be combined. Selecting multiple rows within a particular facet panel provides a Boolean 'OR' over the selected values (that is, if any of the selected values appear in the column, the corresponding rows will be displayed). 'AND' conditions, even within the same facet, create a separate facet panel for each 'AND'-ed condition.

Note: OpenCorporates is a database which makes available data about companies under an open license.

Looking back over my datajunkie notes, I may only have been using the OpenCorporates API since March 2012 but it’s become one of the richest data playgrounds for me, in part because of the far-ranging linking it affords both internally and externally, to other data sources.

Diving into the data for the first time, not even a year ago now, my first thought was to look for something structured that I could use as a warm-up exercise to familiarise myself with the API. Focusing primarily on UK companies, and looking through some of the results for some of the larger UK registered companies, I noticed that (recently registered?) trademark ownership information was available. (Specifically, the company data points to other OpenCorporates data records which include records for trademark registrations).

Included in the OpenCorporates recorded data was a unique WIPO (World Intellectual Property Organization) generated identifier for each trademark. A quick websearch revealed that WIPO publishes information about trademarks from URLs that include the trademark identifier, so I could use the OpenCorporates data – trademarks registered to a particular corporate entity – to draw down additional data from WIPO about particular trademarks. For trademarks that are registered images, this included an image file, so it was a relatively simple exercise to generate a quick sketch of (at least some of) the graphical trademarks registered a particular company.

One thing I noticed in searching for companies on OpenCorporates is that, for the bigger companies at least, there are lots of corporate entities associated with a particular company name. OpenCorporates currently provides an in-part crowd-sourced “community groupings” feature that tries to bundle together different companies that are part of corporate group, but as I poked around the data I noticed that director filings might provide one way of automatically grouping companies. And so I went graph hunting…

The new release of the OpenCorporates API makes it trivial to look up directors, but 6 months or so ago, all we had to hand to was partially structured director filings. It was enough, though, to be able to pull out the directors associated with a particular corporate entity. And having got a list of directors by company, we could do a search around a company with many corporate entities – Tesco, for example – and map out which entities were connected to which by virtue of common director names. Directors’ data is starting to appear as such on OpenCorporates, which makes this sort of mapping easier, although now we are faced with the problem of deciding whether a two directors records sharing the same name are part of the same “director grouping”!

Using network visualisation tools such as Gephi, it’s possible to easily decompose graphs such as these that show connections between companies and directors to a form that just shows co-director links (directors joined by a common company) or potential corporate groupings (companies connected by N or more common directors).

Another possible link between companies was their registered address, so we could also start to explore which similarly named companies might be sharing a physical office. It’s not hard to imagine a time when OpenCorporates will associate geolocation based data with corporate entities, which makes this route to identifying pattern and structure in the data from a geographical, location based perspective a ready possibility.

Revealing the implied structures that are hidden away inside the OpenCorporates database by virtue of common links between corporate entities, directors, and/or locations represents one significant form of value. But there is also much to be gained through linking the OpenCorporates data to other data sources as part of investigations that span datasets. A trivial example is a transparency supporting service that lets us quickly look up (fuzzily, it has to be said!) the directorships of local councillors. Using data from OpenlyLocal, we can pull down a list of councillor names for a particular council, and then look up those names as directors on OpenCorporates. Using open spending data, a further step might be to look up the companies that have received payments over £500 from the same council; and then look to see whether there are any matches.

Whilst preparing for a recent presentation about open data, it struck me that OpenCorporates has the potential to be disruptive in the sense of Clayton Christensen’s “Innovator’s Dilemma”: whilst the data quality may still be lacking in certain respects, OpenCorporates is good enough to use at least as a starting point for certain company related data searches. As the corporate mapping tools evolve, curating corporate groupings (both automatically/heuristically, and via human curators) will become ever easier and ever more accurate. As the director database evolves, I’m sure techniques will emerge for “de-duping” director entities.

The library world may have tools and ideas to help in this respect, for example via the notion of “Virtual International Authority Files” (VIAF), that provide comprehensive, authoritative identifiers for known entities or some of the competing(!?) personal identifier schemes e.g. (Open Researcher and Contributor ID (ORCID), International Standard Name Identifier (ISNI), both discussed here.). (To a certain extent, the aim of OpenCorporates appears to be the creation of such authority files for corporate entities globally, whatever territory they are registered in.)

An approach that I believe holds much promise is the OpenCorporates Reconciliation API. This provides a clean and efficient way of integrating look-ups to OpenCorporates with data cleansing tools such as OpenRefine. The reconciliation API provides a fuzzy match on a corporate name that returns a set of ranked “possible matches” in the OpenCorporates database and that makes it relatively easy to annotate third party datasets containing company names with OpenCorporates identifiers. This sort of tool may prove invaluable when trying to reconcile council spending data against corporate groupings.

I’m also hopeful for an appearance of a directors reconciliation service…;-)

By continuing to take an open approach to its data, providing robust linking strategies out to other identifier namespaces, in to the OpenCorporates namespace, and within OpenCorporates itself through corporate and director groupings, OpenCorporates can both add value to other services as well as gain value from external enrichment.

In this tutorial we will create a simple map of the Tour de France stations of the last 100 years.

Pre-Requirements

1. 1. Install and configure QGIS. Install from http://qgis.org. On most systems there should be a one-click installer that guides you through the process.

   2. We need to install the following handy plugins:

• Add Delimited Text Layer allows us to read and plot points from a CSV file.

• Edit Any Layer allows us to easily edit CSV layers

1. In menu click Plugins > Fetch Python Plugins. In the appearing dialog type in edit any in the the filter box to narrow down the list:

2. Select the plugin and click Install/upgrade plugin. Repeat the same for Add Delimited Text Layer.

3. Download country shapefile from naturalearthdata.com. We are looking for ne_50m_admin_0_countries.

4. Download our sample dataset from http://vis4.net/perugia13/tour-de-france.csv. 

Creating the Base Map Layer

1. Click Layer > Add Vector Layer > Browse and select the file 50m_admin_0_countries.shp. That's the shapefile containing the borders of all countries. Click Open to finally add it to the map.

2. Filter for countries with ISO code of France. Right-click on the layer and select Query from its context menu. In the text box SQL where clause enter the text: ISO_A3 = 'FRA'. Make sure to use single-quotes as double-quotes.       

3. Zoom to Metropolitan France. You can simply use the Zoom In tool

   

   and draw a rectangle around France.

    

4. You might have noticed by now that France looks rather compressed. That is because by default QGIS is using the Plate Carree projection (nerdily referred to by its EPSG code EPSG:4326). You can change the projection by clicking the following icon in the lower right of the window:

5. In the opening dialog activate the checkbox next to "Enable 'on the fly' CRS transformation". Then in the filter text field enter France to search for map projection spezialized for France. For instance you can pick ED50 / France EuroLambert. Click OK to activate the projection.

6. Let's change the default styling. Again, right-click the layer and select Properties. The next dialog should be opened with the Style tab selected by default. Click the button Change… to change the layer style.

7. Now we are going to disable the filling by selecting No Brush in Fill style drop-down. Change the border color to red and increase the border width to 1. Click OK to apply the styling.

8. By now the resulting map should look like this:

Adding the Tour de France Stations

• Now our map contains all the locations of Tour de France stations:

• Now we are going to size the stations according to how often they have been part of the tour. Right click the layer tour-de-france and select Properties in the context menu.

• Change the value in the Size field to a lower value such as 0,5.

• Now click Advanced > Size scale field > count to let QGIS use the values in the column count as radius for the symbols.

• 

  You might also want to make the symbols more transparent by moving the Transparency slider to 50%. Your map should now look like this:

• Since we must always size symbols by area, and not radius, we now need to correct our map. As the area of circles grows proportionally with the square of the radius, we need to compute the square roots of the counts to get proper radii.

• Usually you could have done this already during the data preparation phase and could have simply stored another column in the CSV file. You can also just load the CSV into a spreadsheet tool like Excel and add a new column with the square roots of the counts. However, you can also do this in QGIS using the Edit Any Layer plugin.

• In the menu Plugins select Edit Any Layer > Create Editable Layer. Select tour-de-france as input layer and chose a name for the output layer. I will simply use tour-de-france-2 here. Click OK to proceed.

• You will be asked for the coordinate system again. WGS84 should be selected by default so simply clicking OKshould work.

• Now open the attribute table by right-clicking the new layer and selecting Open Attribute Table in the context menu. You will now see all the data stored in the CSV. Activate editing mode by clicking on the little blue pencil icon (see screenshot). Then open the field calculator by clicking on the little calculator icon.

• Make sure that Create a new field is checked and enter a meaningful name for the new column, e.g. radius. As the square roots are going to be decimal numbers, select Decimal number (real) as Output field type. Finally enter the following formula into the Expression text field: sqrt(count). The dialog should now look like shown in the following screenshot. Click OK to proceed.

• Back in the attribute table you can take a look at the new column (you may have to scroll the table to the right). Now deactivate editing mode by clicking on the blue pencil icon again. QGIS will ask you if you agree to save the changes. Click Save, and Close the attribute table.

• Now hide the layer tour-de-france that we created in step 2 by deactivating its checkbox in the layer window on the left. Now we repeat the second step with the new layer (tour-de-france-2), but instead of count we will pick the column radius for sizing the symbols.

• If you like, change the color to blue and set the transparency to 50%. Finally the map should look like this:

Exporting to PDF

In the last section we are going to export our map to PDF.

• Optionally you can disable the black frame by disabling the checkbox General options > Show frame in the panel on the right.

• Now in the menu click on File > Export as PDF… to finally save the map as PDF. You can now open the map in other graphic tools such as Illustrator to do some fine tuning (adding title, labels etc).

Prerequisites

1. Download and install Tableau Public. So far there is only a Windows version available.
2. Download the dataset eurostat-youth.csv.

Loading a CSV File

1. Click Open data to open the data import window. From the list on the left pick Text File and select eurostat-youth.csv. Make sure that Field separator is set to "Comma". Click OK to proceed.

2. Note: If this step fails with an error message, try changing your system region to English in the Windows control panel (see screenshot). It seems that Tableau has cannot comma-separated values if comma is set as decimal separator for numbers in the system settings.
3. Tableau now lists all the columns of the table in the Data panel on the left. The columns are classified into Dimensions and Measures.

4. The dataset contains the following columns (all data is 2011 and aggregated on NUTS-2 level):

• secondary_edu: percentage of population with secondary education.
• youth_unemployed: percentage of people that aged between 18 and 24, unemployed and do not participate in education or training.
• unemployed_15_24M: percentage of unemployed males between 15 and 24.
• unemployed_15_24F: percentage of unemployed females between 15 and 24.

Analysing a Dataset

Now we are going to analyze the dataset using Tableau.

1. Now drag the field youth_unemployed from Measures to Columns. Then drag secondary_edu to Rows.

2. As you see Tableau computes the sums of the columns instead of plotting the individual values. To fix this we need to right-click the green fields and select Dimension.

3. If both fields are set to be treated as dimensions you should see a scatterplot like the one shown in the screenshot below. You can see that there is a negative correlation between education and youth unemployment.

4. Now drag the field country from Dimensions to Color to color the plot symbols by country. You can also drag the country to Shape to change the icon.

5. Add the fields country and geo_name to the Detail mark to include that piece of information in the tooltips.
6. Now you can use the color legend and quick filters to highlight and hide certain countries.
7. Focus on Turkey.
8. Plotting unemployment by gender.

Bonus: Creating a Map with Tableau

1. Now we can create a map easily: select the dimension lat and lon together with the measure count (while holding the Ctrl key) and click on Show Me to expand the list of suggested visualizations. Then click on the icon of the map with the blue circles. Click on Show Me again to hide the panel.

1. Now you should already see the complete table. Tableau is smart enough to use the square roots of the counts for the circles radii automatically, so we don't have to care about this.
2. You can make the circles transparent by clicking on Color in the panel Marks and moving the transparency slider. To change the size of the circles, click on Size and adjust the slider.
3. Now drag the field name to the Mark Label to add the city names as labels to the map.

Note: This is not a complete tutorial for creating a map with Tableau. The final steps of this tutorial are going to be added in the coming days. 

This tutorial goes through the basic process of creating simple, embeddable charts using Datawrapper.

Preparing the Dataset

1. Go to the Eurostat website and download the dataset Unemployment rate by sex and age groups - monthly average as Excel spreadsheet. You can also directly download the file here.
2. We now need to clean the spreadsheet. Make a copy of the active sheet to keep the original sheet for reference. Now remove the header and footer rows so that GEO/TIME is stored in the first cell (A1).
3. It's a good idea to limit the number of shown entries to something around 10 or 15, since otherwise the chart would be too cluttered. Our story will be about how Europe is divided according to the unemployment rate, so I decided to remove anything but the top 3 and bottom 3 countries plus some reference countries of interest in between. The final dataset contains the countries: Greece, Spain, Croatia, Portugal, Italy, Cyprus, France, United Kingdom, Norway, Austria, Germany.
4. Let's also try to keep the labels short. For Germany we can remove the appendix "(until 1990 former territory of the FRG)", since it wouldn't fit in our chart.
5. This is how the final dataset looks like in OpenOffice Calc.

Loading the Data into Datawrapper

1. Now, to load the dataset into Datawrapper you can simply copy and paste it. In your spreadsheet software look for the Select All function (e.g. Edit > Select All in OpenOffice).
2. Copy the data into the clipboard by either selecting Edit > Copy from the menu or pressing Ctrl + C (for Copy) on your keyboard.
3. Go to datawrapper.de and click the link Create A New Chart. You can do this either being logged in or as guest. If you create the chart as guest, you can add it to your collection later by signing up for free.
4. Now paste the data into the big text area in Datawrapper. Click Upload and continue to proceed to the next step.

Check and Describe the Data

1. Check if the data has been recognized correctly. Things to check for are the number format (in our example the decimal separator "," has been replaced with "."). Also check wether the row and column headers have been recognized.
2. Change number format to one decimal after "." to ensure the data is formatted according to your selected language (e.g. decimal comma for France).
3. Now provide information about the data source. The data has been published by Eurostat. Provide the link to the dataset as well. This information will be displayed along with the published charts, so readers can trace back the path to the source themselves.

4. Click Visualize to proceed to the next step.

Selecting a Visualization

1. Time series are best represented using line charts, so click on the icon for line chart to select this visualization.
2. Give the chart a title that explains both what the readers are seeing in the chart and why they should care about it. A title like "Youth unemployment rates in Europe" only answers half of the question. A better title would be"Youth unemployment divides Europe" or "Youth unemployment on record high in Greece and Spain"
3. In the introduction line we should clarify what exactly is shown in the chart. Click Introduction and type "Seasonally adjusted unemployment rates of under 25 aged". Of course you can also provide more details about the story.
4. Now highlight the data series that are most important for telling the story. The idea is to let one or two countries really pop out of the chart, and attract the readers attention immediately. Click Highlight and select Greece and Spain from the list. You might also want to include your own country for reference.
5. Activate direct labeling to make it easier to read the chart. Also, since our data is already widely distributed, we can force the extension of the vertical axis to the zero-baseline.
6. We can let the colors support the story by choosing appropriate colors. First, click on the orange field to select it as base color. Then click on define custom colors and pick red for high unemployment countries Greece and Spain. For countries with low youth unemployment such as Germany, Norway and Austria we can pick a green, or even better, a blue tone (to respect the color blind). Now the resulting chart should look like this:

7. Click Publish to proceed to the last step.

Publishing the Visualization

1. Now a copy of the chart is being pushed to the content delivery network Amazon S3, which ensures that it loads fast under high traffic.
2. Meanwhile you can already copy the embed code and paste it into your newsrooms CMS to include it in the related news article – just like you would do with a YouTube video.

Further tutorials can be found on the Datawrapper website. 

Charts

• Avoid 3D-charts at all costs. The perspective distorts the data, what is displayed 'in front' is perceived as more important than what is shown in the background.
• Use pie charts with care, and only to show part of whole relationships. Two is the ideal number of slices, but never show more than five. Don't use pie charts if you want to compare values (use bar charts instead).
• Always extend bar charts to the zero baseline. Order bars by value to make comparison easier.
• Use line charts to show time series data. That's simply the best way to show how a variable changes over time.
• Avoid stacked area charts, they are easily misinterpreted.
• Prefer direct labeling wherever possible. You can save your readers a lot time by placing labels directly onto the visual elements instead of collecting them in a separate legend. Also remember that we cannot differentiate that many colors.
• Label your axes! You might think that's kind of obvious, but still it happens quite often that designers and journalists simply forget to label the axes.
• Tell readers why they should care about your graphic. Don't waste the title line to simply say what data is shown.

Color

Colors are difficult. They might make a boring graphic look pretty, but they really need to be handled with care.

• Use colors sparingly. If possible, use only one or two colors in your visualization.
• Double-check your colors for the color blind. You can use tools such as ColorOracle to simulate the effect of different types of color blindness.
• Say goodbye to red-green color scales. A significant fraction of the male population is color blind and have problems differentiating between red and green tones. Red-blue or purple-green are common alternatives.
• In doubt, use color scales from colorbrewer2.com

Maps

• Don't use the Mercator projection for world maps. The distortion of area is not acceptable. Use equal-area projections instead.
• Size symbols by area, not diameter. A common mistake is to map data values to the radius of circles. However, our visual system compares symbols by area. Use square root to compute radii from data.

Recommended reading

• Dona Wong: Wallstreet Journal Guide to Information Graphics
• News Apps Style Guide by ProPublica
• Jean-Luc DuMont: Trees, maps, theorems – effective communication for rational minds
• Darrel Huff: How to lie with statistics

The slides that accompanied this workshop can be found here. 

Social Network Analysis (SNA) allows us to identify players in a social network and how they are related to each other. For example: I want to identify people who are involved in a certain topic - either to interview or to understand what different groups are engaging in debate.

What you’ll need:

1. Gephi (http://gephi.org)
2. OpenRefine (http://openrefine.org)
3. The Sample Spreadsheet
4. Another sample Dataset
5. Bonus: The twitter search to graph tool

Step 1: Basic Social Networks

Throughout this exercise we will use Gephi for graph analysis and visualization. Let’s start by getting a small graph into Gephi.

Take a look at the sample spreadsheet - this is data from a fictional case you are investigating.

In your country the minister of health (Mark Illinger) recently bought 500,000 respiration masks from a company (Clearsky-Health) during a flu-scare that turned out non-substantial. The masks were never used and rot away in the basement of the ministry. During your investigation you found that during the period of this deal Clearsky-Health was consulted by Flowingwater Consulting and paid them a large sum for their services. A consulting company owned by Adele Meral-Poisson. Adele Meral-Poisson is a well known lobbyist and the wife of Mark Illinger.

While we don’t need to apply network analysis to understand this fictional case - it helps understanding the sample spreadsheet. Gephi is able to import spreadsheets like this through its “Import CSV” function. Let’s do this.

Walkthrough Importing CSV into Gephi

1. Save the Sample Spreadsheet as .csv (or click download as → comma seperated values if using google spreadsheet)
2. Start Gephi
3. Select File → Open
4. Select the csv file safed from the sample spreadsheet
5. You will get a import report - check whether the number of nodes and edges seem correct and there are no errors reported

6. The default values are OK for many graphs of this type. If the links between the objects in your spreadsheet are not unilateral but rather bilateral: e.g. lists of friendship, relationships etc., select "undirected" instead of "directed".
7. For now we’ll go with directed - so click “OK” to import the graph.

Now we have imported our simple graph and already see some things on the screen. Let’s make it a little nicer by playing around with Gephi a bit.

Walkthrough: Basic Layout in Gephi

See the grey nodes there? Let’s make this graph a little easier to read.

1. Click on the big fat “T” on the bottom of the graph screen to activate labels

2. Let’s zoom a bit, click on the button on the lower right of the graph window to open the larger menu

3. You should see a zoom slider now, slide it around to make your graph a little bigger:

4. You can click on individual nodes and drag them around to arrange them nicer.

Step 2: Getting Data Out of Twitter

Now we have this, let’s get some data out of Twitter. We’ll be using the Twitter search for a particular hashtag to find information, who talks about it, with whom and what they talk about. Twitter offers loads of information on their API for search. It’s here: https://dev.twitter.com/docs/api/1/get/search.

It basically all boils down to using https://search.twitter.com/search.json?q=%23 tag (the %23 is the "#" character encoded - so %23ijf corresponds to #ijf). If you open the link in the browser you will get the data in JSON format - a format that is ideal for computers to read - but rather hard for you. Luckily Refine can help with this and turn the information into a table. (If you’ve never worked with Refine before, consider having a quick look at the Cleaning Data with Refine recipe at the School of Data.

Walktrough: Get JSON Data from Web APIs into Refine

1. Open Refine
2. Click Create Project
3. Select “Web Adresses”
4. Enter the the following url https://search.twitter.com/search.json?q=%23ijf - this searches for the #ijf hashtag on twitter.
5. Click on “Next”
6. You will get  a preview window showing you nicely formatted json:

7. Hover over the curly bracket inside results and click this selects the results as the data to import into a table
8. Now name your project and click “Create project” to get the final table

By now we have the all the tweets in a table. You see there is a ton of information to each tweet: we’re interested in who communicates with whom, and about what: so the columns we care about are the “text” column and the “from_user” column. Let’s delete all the others. (To do so use “All → Edit Columns → remove/reorder Columns”)

The "from user" is stripped of the characteristical @ in front of the username that is used in tweets - since we want to extract the usernames from tweets later, let’s add a new column with "from" as @tweets. This will involve a tiny bit of programming - don’t be afraid it’s not rocket science.

Walkthrough: Adding a New Column in Refine

1. On your "from_user" column Select “Edit column → add column based on this column...”

2. Whoah - Refine wants us to write a little code to tell it what the new column looks like
3. Let’s program then: Later on we’ll do something the built-in programming language doesn’t let us do, luckily it offers two alternatives Jython (basically Python) and Clojure. We’ll go for Clojure as we’ll need it later.
4. Select Clojure as your language
5. We want to prepend “@” to each name (here “value” refers to the value in each row)
6. Enter (str “@” value) into the expression field

7. See how the value has been changed from "peppemanzo" to "@peppemanzo" - what happened? In Clojure “str” can be used to combine multiple strings: (str “@” value) therefore combines the string “@” with the string in value - what we wanted to do.
8. Now simply name your column (eg. “From”) and click on OK you will have a new column.

OK we got the first thing of our graph: the "from user" - now let’s see what the users talk about. While this will get a lot more complicated - don’t worry we’ll walk you through....

Walkthrough: Extracting Users and Hashtags from Tweets

1. Let’s start with adding a new column based on the text column.
2. The first thing we want to do is to split the tweet into words - we can do so by entering (.split value “ “) into the expression field (make sure your language is still Clojure).

3. Our tweet now looks very different - it has been turned into an “array” of words (an array is simply a collection, you can recognize it by the square brackets).
4. We don’t actually want all words, do we? We only want those starting with "@" or "#" - users and hashtags (so we can see who’s talking with whom about what) - so we need to filter our array.
5. Filtering in Clojure works with the “filter” function, it takes a filter-function and an Array  - the filter-function simply determines whether the value should be kept or not. In our case the filter-function looks like “#(contains? #{\# \@} (first %))” - looks like comic-book characters swearing? Don’t worry, "contains?" basically checks if something is in something else, here whether the first character of the value ("first %") is either "#" or "@ (#{\# \@})" - exactly what we want. Let’s extend our expression:

6. Whoohaa, that seemed to have worked! Now the only thing we need to do is to create a single value out of it - Remember we can do so by using “str” as above.
7. If we do this straight away we run into a problem: before we used “str” as (str “1st” “2nd”) now we want to do (str [“1st” “2nd”]) because we have an array - Clojure helps us here with the "apply" function: (apply str [“1st” “2nd”]) converts (str [“1st” “2nd”]) to (str “1st” “2nd”). Let’s do so...

8. Seems to have worked. Do you spot the problem though?
9. Exactly the words are joined without a clear seperator, let’s add a separator: The easiest way is to interpose a character (e.g. a comma) between all the elements of the array - Clojure does this with the interpose function. (interpose “,” [1 2 3]) will turn out to be [1 “,” 2 “,” 3]. Let’s extend our formula:

10. So our final expression is:

(apply str (interpose "," (filter #(contains? #{\# \@} (first %)) (.split value " "))))

Looks complicated but remember, we built this from the ground up.

11. Great - we can now extract who talks to whom! Name your column and click “OK”  to continue.

Now we have extracted who talks with whom, but the format is still different from what we need in Gephi. So let’s clean up to have the data in the right format for Gephi.

Waltkthrough Cleaning Up

1. First, let’s remove the two columns we don’t need anymore: the text and the original from_user column - do this with “all → edit columns → remove and reorder columns
2. Make sure your “from” column is the first column

3. Now, let’s split up the to column so we have one row in each entry: use “to → edit cells → split multi valued cells” enter “,” as separator

4. Make sure to switch back to “rows” mode.
5. Now let’s fill the empty rows: Select “from → edit cells → fill down”
6. Notice that there are some characters in there that don’t belong to names (e.g. “:” ?) Let’s remove them.
7. Select “to → edit cells → transform...”
8. To replace our transformation is going to be (.replace value “:” “”)

You’ve now cleaned your csv and prepared it enough for Gephi, let’s make some graphs! Export the file as csv and open it in Gephi as above.

A Small Network from a Twitter Search

Let’s play with the network we got through Open Refine:

1. Open the csv file from Open Refine in Gephi
2. Look around the graph - you’ll see pretty soon that there are several nodes that don’t really make sense: “from” and “to” for example. Let’s remove them
3. Switch Gephi to the “data laboratory” view

4. This view will show you nodes and edges found

5. You can delete nodes by right clicking on them (you could also add new nodes)
6. Delete “from” “to” and “#ijf” - since this was the term we searched is going to be mentioned everywhere
7. Activate the labels: it’s pretty messy right now so let’s add some layouting. To layout simply select the algorithm in layout and click “play” - see how the graph changes.
8. Generally combining “Force Atlas” with “Fuchterman Reingold” gives nice results. Add “Label Adjust” to make sure text does not overlap.
9. Now let’s make some more adjustments - let’s scale the label by how often things are mentioned. Select "label size" in the ranking menu.

10. Select “Degree” as rank parameter

11. Click on “Apply” - you might need to run the “Label Adjust” layout again to avoid overlapping labels
12. With this simple trick, we see what kind of topics and persons are frequently mentioned

Great - but it has one downside - the data we’re able to get via Open Refine is very limited - so let’s explore another route.

A Larger Network from a Twitter Search

Now we analysed a small network from a search - let’s deal with a bigger one. This one is from a week of searching for the Twitter hashtag "#ddj" (you can download it here).

The file is in .gexf format - a format for exchanging graph data.

Walkthrough: Network Analysis Using Gephi

1. Open the sample graph file in Gephi
2. Go to the "Data view" and remove the "#ddj" node
3. Enable Node labels
4. Scale labels by Degree (number of edges from this node)
5. Apply “Force Atlas”, “Fuchterman Rheingold” and “Label Adjust” (remember to stop the first two after a while).
6. Now you should have  a clear view of the network

7. Now let’s perform some analysis. One thing we are interested in is: who is central and who’s not: in other words: Who is talking and who is talked to.
8. For this we will run statistics (found in the statistics tab on the right) - we will use the “Network diameter” statistics first - they tell us about "eccentricity", "betweenness centrality" and "closeness centrality". Betweenness centrality tells us which nodes connect nodes: in our terms: high betweenness centrality are nodes who are communication leaders. Low betweenness centrality are topics.
9. Now we ran our test, we can color the labels according to this. Select the label color ranking and “betweenness centrality”.

10. Pick colors as you like them - I prefer light colors and a dark background.

11. Now let’s do something different. Let’s try to detect the different groups of people who are involved in the discussion. This is done with the “Modularity” statistic.
12. Color your labels using the “Modularity Class” - now you see different clusters of people who are involved in the discussion

Now we have analysed a bigger network - found the important players and the different groups active in the discussions - all by searching Twitter and storing the result.

Bonus: Scraping the Twitter Search with a Small Java Utility

If you have downloaded the .jar file mentioned above - it’s a scraper extracting persons and hastags from Twitter - think of what we did previously but automated. To run it use:

java twsearch.jar "#ijf" 0 ijf.gexf 

This will search for #ijf on Twitter every 20 seconds and write it to the file ijf.gexf - the .gexf format is a graph format understood by Gephi. If you want to end data collection: press "ctrl-c" - simple isn’t it? - In fact the utility just runs using Java - it is written entirely in Clojure (the language we used to work with the tweets above).

Steve Doig at the School of Data Journalism in Perugia. Image by Lucy Chambers

Microsoft Excel is a powerful tool that will handle most tasks that are useful for a journalist who needs to analyze data to discover interesting patterns. These tasks include:

• Sorting
• Filtering
• Using math and text functions
• Pivot tables

Introduction to Excel

Excel will handle large amounts of data that is organized in table form, with rows and columns. The columns (which are labeled A, B, C…) list the variables (like Name, Age, Number of Crimes, etc.) Typically, the first row holds the names of the variables. The rest of the rows are for the individual records or cases being analyzed. Each cell (like A1) holds a piece of data.

Modern versions of Excel will hold as many as 1,048,576 records with as many as 16,384 variables! An Excel spreadsheet also will hold multiple tables on separate sheets, which are tabbed on the bottom of the page.

Sorting

One of the most useful abilities of Excel is to sort the data into a more revealing order. Too often, we are given lists that are in alphabetical order, which is useful only for finding a particular record in a long list. In journalism, we usually are more interested in extremes: The most, the least, the biggest, the smallest, the best, the worst. 

Consider the data used in this workshop, a list of the provinces of Italy showing the number of various kinds of crimes reported during a recent year.  Here is how it looks sorted in alphabetical order of province name:

Far more interesting would be to sort it in descending order of the total number of crimes, with the most crime-ridden city at the top of the list:

There are two methods of sorting. The first method is quick and can be used for sorting by a single variable. Put the cursor in the column you wish to sort by (“Delitti in totale” in this case) and then click the Z-A button:

But beware! Put the cursor in the column, but DO NOT select the column letter (C, in this case) and then sort. Consider the example below:

Doing that will sort ONLY the data in that column, thereby disordering your data! Notice well how this can happen!

The other method of sorting is for when you want to sort by more than one variable. For instance, suppose we wish to sort the crime data first by Territerio in alphabetical order, but then by “Delitti in Totale” in descending order within each Territerio. To do that, go to the toolbar, click on “Data” and then “Sort…”, and then choose the variables by which you wish to sort. Then click “OK”.

The result will be this:

Filtering

Sometimes you want to examine only particular records from a large collection of data. For that, you can use Excel’s Filter tool. On the toolbar, go to “Data…Filter…Autofilter”. Small buttons will appear at the top of each column:

Suppose we wish to see only the records from the territerio of Lazio. Click on the button on the Territerio column and choose Lazio from the list. This is the result:

Notice that you now are seeing only rows 36, 44, 78, 80 and 104.

More complicated filters are possible. For instance, suppose you wish to see only records in which “Delitti in totale” is greater than or equal to 50,000. Click on the button and choose “Custom Filter…”:

You could also, for instance, choose records in which “Delitti in totale” is greater than 50,000 and “Omicidi” is less than or equal to 25.

Functions

Excel has many built-in functions useful for performing math calculations and working with dates and text. For instance, assume that we wish to calculate the total number of crimes in all the provinces. To do this, we would go to the bottom of Column C, skip a row, and then enter this formula IN Cell C106: =SUM(C2:C104). The equals sign (=) is necessary for all functions. The colon (:) means “all the numbers from Cell C2 to Cell 104”. The result is this:

(The reason for skipping a row is to separate the sum from the main table so that the table can be sorted without pulling the sum into the table during the sorting operation. This way the sum will stay at the bottom of the column.

Often you will want to do a calculation on each row of your data table. For instance, you might want to calculate the crime rate (the number of crimes per 100,000 population), which would let you compare the crime problem in cities of different sizes. To do this, we would create a new variable called “Crime Rate” in Column L, the first empty column. Then, in Cell L2, we would enter this formula:
=(C2/J2)*100000.  This divides the total crimes by the population, then multiplies the result by 100,000.  (Notice that there are no spaces and no thousands separators used in the formula.) Here is the result:

It would be very tedious to repeat writing that calculation in each of 103 rows of data. Happily, Excel has a way to rapidly copy a formula down a column of cells. To do that, you careful move the cursor (normally a big fat white cross) to the bottom right corner of the cell containing the formula. When it is in the right spot, the cursor will change to a small black cross. At that point, you can double-click and the formula will copy down the column until it reaches a blank cell in the column to the left. This would be the result:

Notice that the formula changes for each row, so that Row 6 is =(C6/J6)*100000.

Now, if we sort by Crime Rate in descending order, we see the cities with the worst crime problems:

and sorting in ascending order, the least crime:

Here are some other useful Excel functions that can be used in similar ways:

(You can add, subtract, multiply or divide by using the symbols + - * and /)
=AVERAGE – calculates the arithmetic mean of a column or row of numbers
=MEDIAN – finds the middle value of a column or row of numbers
=COUNT – tells you how many items there are in a column or row
=MAX – tells you the largest value in a column or row
=MIN – tells you the smallest value in a column or row

There are also a variety of text functions that can join and cut apart text strings. For instance:

If “Steve” is in Cell B2 and “Doig” is in Cell C2, then =B2&” “&C2 will produce “Steve Doig”. And =C2&”, “&B2 will produce “Doig, Steve”. Other text functions include:

=SEARCH – this will find the start of a desired string of text in a larger string.
=LEN – this will tell you how many characters are in a text string.
=LEFT – this will extract however many characters you specify starting from the left.
=RIGHT -- this will extract characters starting from the right.

You can also do date arithmetic, such as calculating the number of days or years between two dates, or hours, minutes and/or seconds between two times. For instance, to calculate on April 24, 2010, the age in years of someone whose birth date is in cell B2, you could use this formula: =(DATE(2010,4,24)-B2)/365.25. The first part of the formula calculates the number of days between the two dates, then that is divided by 362.25 (the .25 accounts for leap years) to produce the years. Another useful date function is =WEEKDAY, which will tell you on which day of the week a chosen date falls. For instance =WEEKDAY(DATE(1948,4,21)) returns a 4, which means I was born on a Wednesday.

Excel offers well over 200 functions in a variety of categories beyond just math, dates and text: Financial, engineering, database, logical, statistical, etc. But it is unlikely that you will need to be familiar with more than a dozen or so functions, unless you are a journalist with a very specialized beat such as economics.

Pivot Tables

One of Excel’s best tricks is the ability to summarize data that is in categories. The tool that does this is called a pivot table, which creates an interactive cross-tabulation of the data by category.

To create a pivot table, every column of your data must have a variable label; in fact, it is always good practice to put in a variable label any time you insert or add a new column. First, you make sure your cursor is on some cell in the table. Then go to the tool bar and click on “Data…Pivot Table Report”. A window will pop up called the “Pivot Table Wizard”. Just hit “Next…Next…Finish” on the three steps of the wizard.

This will open a new sheet that looks like this:

To build a pivot table, you should visualize the piece of paper that would answer your question. Our example data shows 103 provinces in the 20 Territorios of Italy. Imagine that you wanted to know the total number of crimes in each Territorio. The piece of paper that would answer that question would list each Territorio, with the total number of crimes next to each name.

To build this pivot table, we would use the mouse to pick up “Territorio” from the list of variables in the floating box to the right, and place it in the “Drop Row Fields Here” box. We would then take the “Delitti in totale” variable and put it in the “Drop Data Items Here” box. This would be the result:

If you click the cursor into the “Total” Column and hit the Z-A button to sort, you will get this:

It is possible to make very complicated pivot tables, with multiple subtotals. But I recommend making a new pivot table for each question you want to answer; several simple tables are easier to understand than one very complicated table that tries to answer many questions at once.

The  button on the variable list opens up a box that will let you make a variety of other choices about how to summarize and display the result:

Other Excel Tips

Excel will import data that comes in a variety of formats other than the native *.xls that Excel uses. For instance, Excel can readily import text files in which the data columns are separated by commas, tabs, or other characters, like this:

If you find a web page with data in table format (rows and columns), Excel can open it as a spreadsheet.

Excel also will let you format your data to make it more readable. For instance, “Format…Cells…Number” will allow you to put thousands separators in your numbers, like this:

Finding Data

Government agencies are starting to make some of their data available in Excel or other formats. For instance, ISTAT.IT has very comprehensive data about Italian demographics, economy, crime, etc. Many of their tables can be downloaded directly as Excel files.

One trick to find interesting data would be to use Google and add these search terms: site:.it filetype:xls.

Note:The slides from this workshop are available on SlideShare.

Need Help?
Feel free to send me an email at steve.doig@asu.edu. I will be glad to give you advice if I can. 

If you've ever received two enveloped pleas from one charity with an eye on your checkbook – and you probably have – you've taken a hit from a database that’s misfired, a loose cannon that’s been spraying surplus names all over your postal code. You know what's gone wrong, too: the database has listed you twice, either by sanctioning identical entries of your name and address, or by registering two variants of your name and/or place of residence, gulling the database into believing that you're in fact two different persons.

Either way, the point is made. Spreadsheet data redundancies and discrepancies need to be ruthlessly shown the door, lest they infiltrate the data and lay all your lovely, crafty formulas to waste. The need for data integrity is integral, a primordial piece of spreadsheet infrastructure without which your analytical engine is doomed to stall; and while infrastructure is boring, it’s necessary, too. So let’s consider three data integrity issues, all of which I’ve encountered in real-world spreadsheets, and see how they might be resolved.

Start with this very simple batch of data, representing say, serial contributions to a political campaign:

Feed these into a pivot table, and you’ll get something like this:

Now that doesn’t look right, does it? You’ll find among the first principles of pivot tables the axiom that items gathered into the Row (or Column) Labels area appear once each – and Mr. Bowie’s gotten himself invited twice. What went wrong?

The problem - and again I’ve bumped up against this problem more than once – is that the two Bowie entries were in fact respectively keyed:

David Bowie

David Bowie[space]

And those entries are simply incomparable. Typing David Bowie[space] is, relative to David Bowie, tantamount to having typed Barack Obama – they’re two, irretrievably separate items. The obvious workaround? Delete the space. That will work, but this remedy isn’t overwhelmingly practicable if you’re faced with 20,000 rows of names, all of which need to be vetted for precisely the same potential anomaly. The real remedy, rather, lies in the workings of a low-profile and concise Excel function called TRIM, which pares superfluous spaces from cell entries.

For example - say David Bowie[space] finds itself in cell A3. If so,

=TRIM(A3)

will return David Bowie, minus the following space. If you work proactively, then, and post TRIM in an uninhabited column, copy it down the parallel column’s worth of entries (in this case A) and finish off the process with a Copy>Paste Special Values sequence back onto the A column, your gratuitous space problem should be cleared away – and David Bowie will haunt your pivot table just once.

Next case. This one's the classic predicament, the one with which we opened this post. Here the data suffer from record duplication, a particular impediment to lists built atop unique entries, e.g., a membership roster or roll of university students. Here's another elementary example, which should nevertheless embody the problem:

(Again, extrapolate to a list 20,000 names long.)

You've doubtless observed a complication here. Some records exhibit a two-field duplication, and others only one; and before you proceed you need to ask yourself exactly which duplicates you want ousted from the larger list. In this case it seems clear that you'll want to squelch only those records in which both fields are matched across two or more records – here, only Mary Walters. Again, that judgment won't come as easily with 20,000 names worth of students, and that's where Excel's Remove Duplicates feature comes in. Click anywhere among the records, then click the Data tab > Remove Duplicates button in the Data Tools button group (again, I’m using Excel 2010).  You'll see:

Tick the My data has headers box (I'd have written data have, but Microsoft didn't ask me), thus overriding the Column A and B entries above with First Name and Surname. Leave First Name and Surname ticked, because again we want to search for record duplication across both fields, an important point. Click OK and you'll see:

No grammar check there! But no matter; Remove Duplicates has done its job, having extirpated the redundant entry for Mary Walters.

Just keep in mind that a cogent implementation of Remove Duplicates requires a bit of a think-through. After all, a real-world university enrollment list might very well divulge any number of different students owning the same last and surname, and so you'd need to stipulate additional duplicate search parameters in order to  snag truly redundant entries, e.g. by ticking First Name, Surname, and Address, assuming these fields feature in the data. Indeed – large organizations assign their members unique IDs is precisely in order to impose one, irreducibly unambiguous identifier upon their members; think of the UK National Insurance or American Social Security Numbers, for example (and you're looking at someone who has both). On the other hand, of course, there's no definitive way for Excel to know if the two instances of Mary Walters in my primitive list signify the same or two different individuals; again, one assumes additional clarifications would avail in a real-world worksheet.

The third integrity issue is perhaps the most nettling, because it asks the most of the user/investigator – a good old eyeballing of the data, banded with equally retro data entry and re-entry chores. I’ll exemplify the issue by recalling a request I received from the Times Higher Education (UK) weekly to review some of their spreadsheet data in connection with their World University Rankings. I looked at ranking data for both 2011 and 2012, and copied and pasted the data from the two years into one megasheet; and in the course of that perusal I began to notice that some universities reported different spellings across the two years, e.g.

University of California, Berkeley
University of California Berkeley

University of Illinois at Chicago

University of Illinois – Chicago

And yes, a lot of this:

University of Chicago

University of Chicago[space]

Now of course Excel is the paradigmatic idiot savant; it can sum 500,000 numbers at the tap of an Enter key, even as it lacks the human, inferential savvy to declare the above pairs equivalent.

What to do? Here the problem is the absence of redundancy among the pairs, and in order to achieve the necessary twinnings you could first apply the TRIM stratagem I recommended earlier. Next, you could sort the university names, (closely) observe how neighboring names are spelled, and decide which variant should prevail, in the event a university presents itself with disparate spellings. You could also run the names through a pivot table and assign the institution Name field to the Row Labels area, where again each uniquely-spelled name will appear but once. And here, unique appearances mean you could have a problem. You don’t want to see Harvard’s name twice in the pivot results – you want to see it once, even as it appears twice in the data – once for 2011, once for 2012. But whatever the means for trapping errant spellings, you’ve going to have to do some quaint 20th century editing and retyping.

Something that charity didn’t do. And that’s why they mailed you twice.

Reading through the Online Journalism blog post on Getting full addresses for data from an FOI response (using APIs), the following phrase – relating to the composition of some Google Refine code to parse a JSON string from the Google geocoding API – jumped out at me: “This took a bit of trial and error…”

Why? Two reasons… Firstly, because it demonstrates a “have a go” attitude which you absolutely need to have if you’re going to appropriate technology and turn it to your own purposes. Secondly, because it maybe (or maybe not…) hints at a missed trick or two…

So what trick is missing?

Here is an example of the sort of thing you get back from the Google Geocoder:

{ “status”: “OK”, “results”: [ { "types": [ "postal_code" ], “formatted_address”: “Milton Keynes, Buckinghamshire MK7 6AA, UK”, “address_components”: [ { "long_name": "MK7 6AA", "short_name": "MK7 6AA", "types": [ "postal_code" ] }, { “long_name”: “Milton Keynes”, “short_name”: “Milton Keynes”, “types”: [ "locality", "political" ] }, { “long_name”: “Buckinghamshire”, “short_name”: “Buckinghamshire”, “types”: [ "administrative_area_level_2", "political" ] }, { “long_name”: “Milton Keynes”, “short_name”: “Milton Keynes”, “types”: [ "administrative_area_level_2", "political" ] }, { “long_name”: “United Kingdom”, “short_name”: “GB”, “types”: [ "country", "political" ] }, { “long_name”: “MK7″, “short_name”: “MK7″, “types”: [ "postal_code_prefix", "postal_code" ] } ], “geometry”: { “location”: { “lat”: 52.0249136, “lng”: -0.7097474 }, “location_type”: “APPROXIMATE”, “viewport”: { “southwest”: { “lat”: 52.0193722, “lng”: -0.7161451 }, “northeast”: { “lat”: 52.0300728, “lng”: -0.6977000 } }, “bounds”: { “southwest”: { “lat”: 52.0193722, “lng”: -0.7161451 }, “northeast”: { “lat”: 52.0300728, “lng”: -0.6977000 } } } } ] }

The data represents a Javascript object (JSON = JavaScript Object Notation) and as such has a standard form, a hierarchical form.

Here’s another way of writing the same object code, only this time laid out in a way that reveals the structure of the object:

1.  {
2.  "status": "OK",
3.   "results": [ {
4.     "types": [ "postal_code" ],
5.     "formatted_address": "Milton Keynes, Buckinghamshire MK7 6AA, UK",
6.     "address_components": [ {
7.       "long_name": "MK7 6AA",
8.       "short_name": "MK7 6AA",
9.       "types": [ "postal_code" ]
10.     }, {
11.       "long_name": "Milton Keynes",
12.       "short_name": "Milton Keynes",
13.       "types": [ "locality", "political" ]
14.     }, {
15.      "long_name": "Buckinghamshire",
16.       "short_name": "Buckinghamshire",
17.      "types": [ "administrative_area_level_2", "political" ]
18.     }, {
19.      "long_name": "Milton Keynes",
20.       "short_name": "Milton Keynes",
21.       "types": [ "administrative_area_level_2", "political" ]
22.     }, {
23.       "long_name": "United Kingdom",
24.       "short_name": "GB",
25.       "types": [ "country", "political" ]
26.     }, {
27.       "long_name": "MK7",
28.       "short_name": "MK7",
29.       "types": [ "postal_code_prefix", "postal_code" ]
30.     } ],
31.     "geometry": {
32.       "location": {
33.         "lat": 52.0249136,
34.         "lng": -0.7097474
35.       },
36.       "location_type": "APPROXIMATE",
37.       "viewport": {
38.         "southwest": {
39.           "lat": 52.0193722,
40.           "lng": -0.7161451
41.         },
42.         "northeast": {
43.           "lat": 52.0300728,
44.           "lng": -0.6977000
45.         }
46.       },
47.      "bounds": {
48.         "southwest": {
49.           "lat": 52.0193722,
50.           "lng": -0.7161451
51.         },
52.         "northeast": {
53.           "lat": 52.0300728,
54.           "lng": -0.6977000
55.         }
56.       }
57.     }
58.   } ]
59.  }

Making Sense of the Notation

At its simplest, the structure has the form: {“attribute”:”value”}

If we parse this object into the jsonObject, we can access the value of the attribute as jsonObject.attribute or jsonObject["attribute"]. The first style of notation is called a dot notation.

We can add more "attribute:value" pairs into the object by separating them with commas: a={“attr”:”val”,”attr2″:”val2″} and address them (that is, refer to them) uniquely: a.attr, for example, or a["attr2"].

Try it out for yourself… Copy and paste the following into your browser address bar (where the URL goes) and hit return (i.e. “go to” that “location”):

javascript:a={"attr":"val","attr2":"val2"}; alert(a.attr);alert(a["attr2"])

(As an aside, what might you learn from this? Firstly, you can “run” JavaScript in the browser via the location bar. Secondly, the JavaScript command alert() pops up an alert box:-)

Note that the value of an attribute might be another object.

obj={ attrWithObjectValue: { “childObjAttr”:”foo” } }

Another thing we can see in the Google geocoder JSON code are square brackets. These define an array (one might also think of it as an ordered list). Items in the list are address numerically. So for example, given:

arr[ "item1", "item2", "item3" ]

we can locate “item1″ as arr[0] and “item3″ as arr[2]. (Note: the index count in the square brackets starts at 0.) Try it in the browser… (for example, javascript:list=["apples","bananas","pears"]; alert( list[1] );).

Arrays can contain objects too:

list=[ "item1", {"innerObjectAttr":"innerObjVal" } ]

Can you guess how to get to the innerObjVal? Try this in the browser location bar:

javascript: list=[ "item1", { "innerObjectAttr":"innerObjVal" } ]; alert( list[1].innerObjectAttr )

Making Life Easier

Hopefully, you’ll now have a sense that there is structure in a JSON object, and that that (sic) structure is what we rely on if we want to cut down on the “trial an error” when parsing such things. To make life easier, we can also use “tree widgets” to display the hierarchical JSON object in a way that makes it far easier to see how to construct the dotted path that leads to the data value we want.

A tool I have appropriated for previewing JSON objects is Yahoo Pipes. Rather than necessarily using Pipes to build anything, I simply make use of it as a JSON viewer, loading JSON into the pipe from a URL via the Fetch Data block, and then previewing the result:

Another tool (and one I’ve just discovered) is an Air application called JSON-Pad. You can paste in JSON code, or pull it in from a URL, and then preview it again via a tree widget:

Clicking on one of the results in the tree widget provides a crib to the path…

Summary

Getting to grips with writing addresses into JSON objects helps if you have some idea of the structure of a JSON object. Tree viewers make the structure of an object explicit. By walking down the tree to the part of it you want, and “dotting” together* the nodes/attributes you select as you do so, you can quickly and easily construct the path you need.

* If the JSON attributes have spaces or non-alphanumeric characters in them, use the obj["attr"] notation rather than the dotted obj.attr notation…

P.S.: Via my feeds, though something I had bookmarked already, this Data Converter tool may be helpful in going the other way… (Disclaimer: I haven’t tried using it…)

If you know of any other related tools, please feel free to post a link to them in the comments:-)