# IWMW12 Data Hacks

Last couple of days I’ve been at IWMW12 hosted this year at University of Edinburgh. I’ve already posted [Data Visualisation Plenary/Workshop Resources](https://hawksey.info/blog/2012/06/iwmw12-data-visualisation-plenaryworkshop-resources/) which has my slides from the plenary. I was teaming up with Tony Hirst (OU) and have included his slides to the page. Because of living 'almost locally' and other family commitments I missed out on most of the social events, instead I got drunk on data working into the early hours to find what stories I could uncover from the #IWMW12 stream. In this post I’ll show you what I’ve come up with and some of the highlights in trying to turn raw data into something interesting/meaningful (or pointless if you prefer). Interestingly a lot of what I cover here uses the same techniques used in my recent [The story data tells us about #CitizenRelay guest post](http://www.citizenrelay.net/the-story-data-tells-us-about-citizenrelay-guest-post-by-martin-hawksey-mhawksey/), so I’ve got an emerging templated workflow emerging which I can deploy at events which makes me wonder if I should be getting organisers pay my travel/accommodation as an event data amplifier?

## UK University Twitter Account Community

On day one Brian Kelly mentioned some work by Craig Russell to collate a table of UK University Social Media accounts which featured in a guest post on Brian’s blog titled [Further Evidence of Use of Social Networks in the UK Higher Education Sector](http://ukwebfocus.wordpress.com/2012/06/06/guest-post-further-evidence-of-use-of-social-networks-in-the-uk-higher-education-sector/). You can get the [data Craig has compiled from a Google Spreadsheet](https://docs.google.com/spreadsheet/ccc?key=0AvmGs2D9eZgxdFVfWmFQZ2dNcjhBa2h5aXF2aUtyYWc#gid=0). Looking at this two things immediately sprung to mind. First that the document could be made more ‘glanceable’ just using some simple conditional formatting, and second there was a nice list of Twitter accounts to do something with. [![image](https://hawksey.info/wp-content/uploads/2012/06/image_thumb2.png "image")](https://hawksey.info/wp-content/uploads/2012/06/image7.png)Here’s a link to [my modified version Craig’s spreadsheet](https://docs.google.com/spreadsheet/ccc?key=0AqGkLMU9sHmLdDgzQjhxdnNQaFYtcDBsLUVOV01oaVE#gid=0). It uses the importRange formula to pull the data in so it creates a live link to the source document. For the conditional formatting I looked for text containing ‘http’ turning the cell text and background green. The [HTML view of this is a lot cleaner looking](https://docs.google.com/spreadsheet/pub?key=0AqGkLMU9sHmLdDgzQjhxdnNQaFYtcDBsLUVOV01oaVE&gid=0). On the [Twitter Accounts sheet](https://docs.google.com/spreadsheet/ccc?key=0AqGkLMU9sHmLdDgzQjhxdnNQaFYtcDBsLUVOV01oaVE#gid=3) extract the account screen names by pulling everything after the last ‘/’ and remove most of the blank rows using a unique formula. Putting this list into the [free MS Excel add-in NodeXL](http://nodexl.codeplex.com/) and using the Import > From Twitter List Network lets you get data on which of these accounts follow each other. I played around with visualising the network in NodeXL but found it easier in the end to put the data into [Gephi](http://gephi.org/) getting the image below. These ‘hairballs’ have limited value and you’re best having [a play with the interactive version](http://hawksey.info/gexf-js/index.html#ukhei.gexf), which is an export of Gephi visualised using the [gexf-js tool by Raphaël Velt](https://github.com/raphv/gexf-js) (De-hairballing is something Clement Levallois ([‏@seinecle](mailto:‏@seinecle)) and he kindly sent me a post to [a new tool he’s creating called Gaze](http://insights.exploreyourdata.com/2012/03/23/the-quest-for-structure-in-networks/)). ![UK HEI Twitter Accounts](http://mcdn.hawksey.info/wp-content/uploads/2012/06/ukhei.jpg "UKHEI Twitter Accounts")## The #IWMW12 Twitter Archive Two More Ways

[![Timeline](https://hawksey.info/wp-content/uploads/2012/06/image8.png "Timeline")](http://embed.verite.co/timeline/?source=tNbCst6bLcECPwS9xasnQ0g&maptype=toner&lang=en&start_at_end=true)As part of #iwmw12 I was collecting [an archive of tweets](https://docs.google.com/spreadsheet/pub?key=0AqGkLMU9sHmLdGY5b1NHckZGN3dOanR2dFdWeTBrZXc&output=html) which already gives you the [TAGSExplorer view](http://hawksey.info/tagsexplorer/?key=0AqGkLMU9sHmLdGY5b1NHckZGN3dOanR2dFdWeTBrZXc&sheet=oaw). I also use the [Topsy API and Google Spreadsheet to extract tweets](https://docs.google.com/spreadsheet/ccc?key=0AqGkLMU9sHmLdE5iQ3N0NmJMY0VDUHdTOXhhc25RMGc#gid=3) which is then passed into [Timeline by Vérité](http://timeline.verite.co/) which gives you a nice sense of the event. \[If anyone else would like to make their own twitter media timeline [there is a template in this post](https://hawksey.info/blog/2012/04/topsy-media-timeline-v2/) (it is easy as make a copy of the template, enter your search terms and publish the sheet).\] 
### Searchable archive

[![Searchable archive](https://hawksey.info/wp-content/uploads/2012/06/image9.png "Searchable archive")](http://hawksey.info/labs/arc/iwmw12.html)New way number one is [a filterable/searchable archive of IWMW12 tweets](http://hawksey.info/labs/arc/iwmw12.html). Using the Google Visualisation API I can create a custom interface to the Google Spreadsheet of tweets. This solution uses some out-of-the-box functionality including [table paging](http://code.google.com/apis/ajax/playground/?type=visualization#table_paging), [string filtering](http://code.google.com/apis/ajax/playground/?type=visualization#stringfilter_control) and [pattern formatting](http://code.google.com/apis/ajax/playground/?type=visualization#pattern_formatter). Using the pattern formatter was the biggest achievement as it allows you to insert [Twitter Web Intents](https://dev.twitter.com/docs/intents) functionality (so if you click to reply to a tweet it pulls up Twitter’s reply box. I also processed the archive using R to get a term frequency to make a [d3 based wordcloud](http://www.jasondavies.com/wordcloud/) (I’ve started looking at how this can be put into a more general tool. Here’s [my current draft](http://hawksey.info/tagsexplorer/arc.html?key=0AqGkLMU9sHmLdGY5b1NHckZGN3dOanR2dFdWeTBrZXc) which you should be able to point any TAGS spreadsheet at (this version also includes a [Chart Range Filter](http://code.google.com/apis/ajax/playground/?type=visualization#chartrangefilter_control) letting you view a time range). I definitely need to write more about how this was done!) [![Filter by time](https://hawksey.info/wp-content/uploads/2012/06/image_thumb3.png "Filter by time")](https://hawksey.info/wp-content/uploads/2012/06/image10.png)### Mappable archive

One of the last things I did was to filter the twitter archive for tweets with geo-location. Using the [Spreadsheet Mapper 3.0 template](http://www.google.com/earth/outreach/tutorials/spreadsheet3.html) I was able to dynamically pull the data to generate a time stamped KML file. The timestamps are ignored when you [view in Google Maps](https://maps.google.com/maps?f=q&q=https://docs.google.com/spreadsheet/pub%3Fkey%3Dt0AqRq0Ltbwt1JjtV_6IhXA%26single%3Dtrue%26gid%3D0%26output%3Dtxt%26time1%3D4108268), but if you [download the kml file](https://docs.google.com/spreadsheet/ccc?key=t0AqRq0Ltbwt1JjtV_6IhXA&single=true&gid=0&output=txt) it can be played in Google Earth (you’ll have to adjust the playback control to separate the playback heads – I tried doing this in the code but [the documentation is awful!](https://developers.google.com/earth/documentation/reference/)) [![Google Earth playback](https://hawksey.info/wp-content/uploads/2012/06/image_thumb4.png "Google Earth playback")](https://hawksey.info/wp-content/uploads/2012/06/image11.png)Or if you have the Google Earth browser plugin [a web version of IWMW12 geo-tweets is here](http://dl.dropbox.com/u/7860124/iwmw12-geo.html) (also embeded below): So there you go … or as said by Sharon Steeples > [\#iwmw12](https://twitter.com/search/%2523iwmw12) OK. I'm getting this theme now... Content \*is\* data. Store it. Catalog it. Share it. Present it visually. And re-use it.
> 
> — Sharon Steeples (@sharonsteeples) [June 19, 2012](https://twitter.com/sharonsteeples/status/215006941250981888)