{"id":2912,"date":"2015-03-02T09:00:52","date_gmt":"2015-03-01T23:00:52","guid":{"rendered":"http:\/\/mappingonlinepublics.net\/?p=2912"},"modified":"2015-03-31T12:02:54","modified_gmt":"2015-03-31T02:02:54","slug":"using-gawk-to-prepare-tcat-data-for-tableau-part-1","status":"publish","type":"post","link":"https:\/\/mappingonlinepublics.net\/dev\/2015\/03\/02\/using-gawk-to-prepare-tcat-data-for-tableau-part-1\/","title":{"rendered":"Using Gawk to Prepare TCAT Data for Tableau, Part 1"},"content":{"rendered":"

Much of the research we\u2019ve presented on this site over the years has built on yourTwapperkeeper<\/em>, our trusty old tool for gathering Twitter<\/em> data. But yTK<\/em> isn\u2019t the most modern of platforms any more, provides only a very limited user interface, and gathers only a fraction of the full metadata payload which the Twitter<\/em> API delivers alongside the tweets themselves. More recently, therefore, we\u2019ve increasingly switched our data gathering efforts to the Twitter Capture and Analysis Toolkit (TCAT)<\/a><\/em>, developed by the Digital Methods Initiative at the University of Amsterdam.<\/p>\n

TCAT<\/em> has its own limitations \u2013 for one, it only utilises the Twitter<\/em> streaming API, while yTK<\/em> also uses the search API for backfilling tweets in case of server outages; incidentally, a side effect of this is that it\u2019s impossible to use URLs like abc.net.au<\/em> as search terms in TCAT<\/em>, since the streaming API doesn\u2019t match search terms on resolved URLs, while the search API does. (Our ATNIX project depends on this functionality, and thus continues to rely on yTK<\/em>.) But overall, TCAT<\/em> provides a considerably more advanced solution for gathering Twitter<\/em> data these days, and so we\u2019ve had to develop new approaches to working with the datasets it generates.<\/p>\n

Additionally, data analytics tools have also moved on. Excel was never particularly well suited to any serious analysis, and in the past we\u2019ve done a great deal of data preprocessing using the command-line tool Gawk before loading the results into Excel for visualisation. These days, however, the software of choice is Tableau<\/a>, whose functionality is several generations ahead of what Excel has to offer and which can do on the fly much of the analysis which we\u2019ve had to painstakingly prepare during the preprocessing stage in the past. Tableau is far from inexpensive (although it\u2019s free for current students, and educational pricing is available), but simply has very few credible alternatives at this point.<\/p>\n

Connecting TCAT and Tableau, via Gawk<\/h3>\n

What\u2019s still needed, then, is a straightforward process for loading TCAT<\/em> data into Tableau. TCAT<\/em> already has a number of built-in export functions, but these aren\u2019t necessarily well-suited for our purposes, given the data format Tableau prefers. What\u2019s worse, TCAT<\/em>\u2019s full data export format has changed over time, so the structure of its outputs isn\u2019t necessarily very standardised. In this post, then, I\u2019m outlining a standard method for preprocessing TCAT<\/em> data specifically for Tableau, and below I\u2019m also sharing the scripts that implement this method.<\/p>\n

We\u2019re building here on the TCAT<\/em> function which exports all tweets in a dataset, subject to the specific parameters set by the user (timeframe, text and user filters, etc.):<\/p>\n

\"image\"<\/a><\/p>\n

This export results in a comma-separated values (CSV) file, which can already be imported into Tableau for analysis. However, what Tableau will struggle with is to extract some of the important information which isn\u2019t yet in the metadata: for example, which users are mentioned in a tweet, whether a tweet is a retweet or @reply, which hashtags are present in a tweet, and which URLs are being shared. The problem is that most of these elements could occur multiple times in the same tweet: for example,<\/p>\n

Hey @friend, have you seen this? #ohno RT @news: #BREAKING: World ends. http:\/\/t.co\/abcdefg<\/p><\/blockquote>\n

is both an @reply (or @mention, of @friend) and a retweet (of @news), and contains the two hashtags #ohno and #BREAKING. In a simple CSV which contains only one row of data for each tweet, such multiple metadata points per tweet are very difficult to represent.<\/p>\n

This is, of course, what relational databases are useful for, and Tableau provides the functionality to link multiple data sources (even in CSV or TSV form) in much the same way as if they were tables in a database. The easiest way to do this is based on the Twitter<\/em>-generated ID of each tweet, which is unique and stable. Assuming the example above has the ID 1, for example, we could generate three additional tables to document the @mentions, hashtags, and URLs contained in the tweet:<\/p>\n\n\n\n\n\n
\n

ID<\/span><\/span><\/b><\/p>\n<\/td>\n

\n

@mention type<\/span><\/span><\/b><\/p>\n<\/td>\n

\n

target user<\/span><\/span><\/b><\/p>\n<\/td>\n

<\/td>\n\n

ID<\/span><\/span><\/b><\/p>\n<\/td>\n

\n

hashtag<\/span><\/span><\/b><\/p>\n<\/td>\n

<\/td>\n\n

ID<\/span><\/span><\/b><\/p>\n<\/td>\n

\n

URL<\/span><\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

@reply<\/span><\/p>\n<\/td>\n

\n

friend<\/span><\/p>\n<\/td>\n

<\/td>\n\n

1<\/span><\/p>\n<\/td>\n

\n

#ohno<\/span><\/p>\n<\/td>\n

<\/td>\n\n

1<\/span><\/p>\n<\/td>\n

\n

http:\/\/t.co\/abcdefg<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/p>\n<\/td>\n

\n

retweet<\/span><\/p>\n<\/td>\n

\n

news<\/span><\/p>\n<\/td>\n

<\/td>\n\n

1<\/span><\/p>\n<\/td>\n

\n

#BREAKING<\/span><\/p>\n<\/td>\n

<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Note that the @mentions and hashtags table now each have two entries relating to the same tweet, because it\u2019s both an @reply and a retweet, and contains two different hashtags. Additionally, it would also be useful to resolve the t.co<\/em> link to its eventual destination, and perhaps to specifically extract the URL\u2019s domain name (so later on we can examine which sites are especially important in a dataset):<\/p>\n\n\n\n\n
\n

<\/a>ID<\/span><\/span><\/b><\/p>\n<\/td>\n

\n

Domain<\/span><\/span><\/b><\/span><\/p>\n<\/td>\n

\n

Long URL<\/span><\/span><\/b><\/span><\/p>\n<\/td>\n

\n

URL<\/span><\/span><\/b><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/span><\/b><\/span><\/p>\n<\/td>\n

\n

http:\/\/cnn.com<\/span><\/span><\/p>\n<\/td>\n

\n

http:\/\/cnn.com\/worldends<\/span><\/span><\/p>\n<\/td>\n

\n

http:\/\/t.co\/abcdefg<\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

These tables can then be linked with the main TCAT<\/em> datafile, which itself constitutes a large table of tweets and metadata.<\/p>\n

Introducing the TCAT-Process Scripts<\/h3>\n

Update:<\/strong> I’ve made further adjustments to these scripts to make them easier to use on a Mac. Please re-download the scripts package, and note the updated instructions below.<\/span><\/p>\n

All of this is relatively easy to do using our old friend Gawk, a programmable command-line tool which is designed to process and filter CSV\/TSV-format files. Below, I\u2019m sharing a suite of Gawk scripts that are designed to work through the following steps:<\/p>\n

    \n
  1. Take the TCAT<\/em> datafile, extract tweet IDs and texts.\u00a0\u00a0 \u2013>\u00a0\u00a0 datafile<\/em>-tweettext.tab<\/li>\n
  2. Identify @mention types and recipients.\u00a0\u00a0 \u2013>\u00a0\u00a0 datafile<\/em>-tweettypes.tab<\/li>\n
  3. Identify hashtags in tweets.\u00a0\u00a0 \u2013>\u00a0\u00a0 datafile<\/em>-hashtags.tab<\/li>\n
  4. Extract and resolve URLs in tweets.\u00a0\u00a0 \u2013>\u00a0\u00a0 datafile<\/em>-urlpaths.tab
    \n(This \u2013 optional \u2013 last step generates a number of temporary files for error checking, which can be deleted.)<\/li>\n<\/ol>\n

    The Gawk scripts automate the entire process \u2013 once everything is installed, a single Gawk call will do all the processing. Here are the scripts, as a downloadable ZIP archive<\/a>, and here\u2019s how to install them and set everything up to go:<\/p>\n