Creating Twitter Timelines from Twapperkeeper Data

This is the first in what will be an irregular series of methods posts outlining some of our approaches to working with datasets from various sources. Part of our work over the next few weeks will be to examine what happens in the Australian Twittersphere around the upcoming federal election, so I figured it would be a good idea to start with some of the basics of working with Twapperkeeper data. (Note that what I’ll outline here is a working solution, but not necessarily an elegant one – if anybody has a better suggestion, we’d love to hear it.)

Twapperkeeper is an online tool for capturing (public) tweets that contain specific #hashtags, keywords, or @usernames. The datasets it creates are delivered in a standard comma-separated value (CSV) format – including fields such as the tweet itself, the username of the poster, and a timestamp in various formats, as well as a few other bits of backend information.

One of the most immediate points of interest in working with a Twapperkeeper dataset is often to get a sense of the tweet timeline: how does the volume of tweets change over time, for example in response to events occurring in the world? The datasets provide that information – but to create an accurate visualisation of the timeline needs some doing. In this post, I’m going to work through an example, using Twapperkeeper data collected by my colleague Jean Burgess during the recent Australian Labor Party leadership spill (centred around the #spill hashtag and a few related ones).

By default, the Twapperkeeper dataset includes two timestamp fields: created_at (human-readable, e.g. “Sat Jun 19 00:28:04 +0000 2010”, and time (in Unix format, e.g. “1276907284”). Both are problematic: the format for created_at can be inconsistent – sometimes the ‘+0000’ GMT offset is placed before the year, sometimes after it -, and the Unix timestamp has a second-by-second resolution that may be too high if we’re dealing with lower volumes of tweets (e.g. only a few relevant tweets per minute, or per hour).

So, to be able to visualise the data in a more meaningful way, we’ll need to condense the timestamps to larger units of time – minutes, hours, days. I’ve put together a quick Gawk script which helps with this (Gawk is a command-line tool that’s perfect for processing CSV files – and it’s available for Unix, Windows, and Mac):

      
# explodetime.awk - Explode tweet time information
#
# this script takes a CSV archive of tweets, and adds various additional timestamp representations
#
# expected data format:
# text,to_user_id,from_user,id,from_user_id,iso_language_code,source,profile_image_url,geo_type,geo_coordinates_0,geo_coordinates_1,created_at,time
# (i.e. typical Twapperkeeper format)
#
# output format:
# text,to_user_id,from_user,id,from_user_id,iso_language_code,source,profile_image_url,geo_type,geo_coordinates_0,geo_coordinates_1,time,timestamp,year,month,day,hour,minute
#
# tweet time is provided at various levels of specificity for time-based activity mapping:
#
# FIELD     - CONTENT
# time      - full human-readable timestamp
# timestamp - full Unix timestamp
# condensed - Unix timestamp, condensed to 100-second intervals (i.e. last two digits changed to '00')
# year      - YYYY only
# month     - YYYY-Mon
# day       - YYYY-Mon-DD
# hour      - YYYY-Mon-DD HH
# minute    - YYYY-Mon-DD HH:MM
#
# Released under Creative Commons (BY, NC, SA) by Axel Bruns - a.bruns@qut.edu.au

BEGIN {
	print "text,to_user_id,from_user,id,from_user_id,iso_language_code,source,profile_image_url,geo_type,geo_coordinates_0,geo_coordinates_1,time,timestamp,condensed,year,month,day,hour,minute"
}

{
	print $0  "," substr($13,0,8) "00," strftime("%Y",$13) "," strftime("%Y-%b",$13) "," strftime("%Y-%b-%d",$13) "," strftime("%Y-%b-%d %H",$13) "," strftime("%Y-%b-%d %H:%M",$13)
} 
        

This is called up as follows:

gawk -F , -f explodetime.awk sourcefile.csv >outputfile.csv

The resulting CSV output file contains a number of additional columns: ‘condensed’, in which the last couple of digits in the Unix timestamp (the seconds) are changed to zero, and a range of columns which provide a range of condensed human-readable timestamp variations – from the year only through to a ‘YYYY-Mon-DD HH:MM’ format.

For many purposes, the higher-level human-readable versions will be enough – provided there’s at least one relevant tweet in any given hour, for example, you could load this new CSV file into Excel and create a pivot table that plots the new ‘hour’ field (on the X-axis) against a count of all tweets during that hour (on the Y-axis). For our #spill data, this would look as follows:

But there’s a problem. This method only works if there are data for each period – Excel isn’t smart enough to recognise that we’re dealing with time-based data here, and won’t automatically insert empty spaces where there’s a gap in the timeline. Take an excerpt from the pivot table for our #spill dataset, for example:

For some reason (perhaps Twitter or Twapperkeeper was down), no #spill tweets appear in the dataset for hour 17 of 24 June. But that gap doesn’t appear in the Excel graph above – instead, hour 18 follows on directly from hour 16: 5 p.m. never happened. Excel’s limittaions severely compromise our results: gaps don’t appear, and in fact the times listed on the X-axis aren’t even evenly spaced!

To fix this, we could manually insert zeroes into our data (e.g. a new line ‘2010-Jun-24 17’, ‘0’ in the table above), but that isn’t feasible for large datasets, of course. Instead, here’s a somewhat easier way to do it:

1. Run the Gawk script above over the dataset to add those extra columns.
2. Load into Excel, and create a pivot table: plot ‘condensed’ (the Unix timestamp, rounded down to 100-second intervals) against the count of ‘timestamp’ (pretty much any other field will do as well – we’re just counting, not summing).
3. Now copy both columns of the pivot table into a new worksheet.
4. Select both copied columns, and insert a ‘Scatter with only Markers’ graph.

The crucial benefit of using a scatter graph is that, contrary to other graph types, it does preserve gaps in the data. For that reason, I’m also using a scatter plot with markers for data points, rather than with interpolation lines – in our example above, an interpolation line would suggest that there were somewhere between 141 and 256 tweets in hour 17 of 24 June, but that’s clearly incorrect. Instead, in the graph below, you can just make out a gap in tweet activity after the second major spike – just as the data tell us there should be.

Unfortunately, there’s not much point labelling the unit markers on the horizontal axis, because we’ve had to use Unix timestamps for the data itself – so the unit markers would read something like ‘1276914303’. However, set an interval of 3600 (60 seconds x 60 minutes = 1 hour) for minor axis markers and 86400 (3600 seconds x 24 hours = 1 day) for major axis markers, and they’ll correspond to hours and days. To start and finish the X-axis at a given point in time, use a tool like this to find the timestamp to corresponds to that time (don’t forget to set the appropriate GMT offset!), and use those timestamp values as minimum and maximum values for the X-axis.

So, for our purposes, the three core days (23 to 26 June 2010) in the #spill tweet data look like this – note the now much more obvious gaps in the data during parts of day two:

The purpose of this post has been to describe the methods for creating this graph; I’m not going to go into its meaning here, but will leave that for another post. It’s very notable how the early excitement spiked in the latter hours of day one (23 June), with a lull during the early morning hours (in Australia) of 24 June. There’s another time of peak activity during the middle of 24 June, before and after the actual spill vote, though at a lower level of intensity – perhaps people had moved over to follow the live broadcasts on radio and TV, or discussion had branched out from #spill to other tags?