{"id":1072,"date":"2012-02-10T15:42:55","date_gmt":"2012-02-10T05:42:55","guid":{"rendered":"http:\/\/www.mappingonlinepublics.net\/dev\/?p=1072"},"modified":"2012-04-10T14:17:49","modified_gmt":"2012-04-10T04:17:49","slug":"resolving-short-urls-a-new-approach","status":"publish","type":"post","link":"https:\/\/mappingonlinepublics.net\/dev\/2012\/02\/10\/resolving-short-urls-a-new-approach\/","title":{"rendered":"Resolving Short URLs: A New Approach"},"content":{"rendered":"

When working with Twitter<\/em> data, one of the most interesting questions is always what URLs tweets are linking to. As Twitter<\/em> users discuss any given topic or issue, the URLs they share provide us with an indication of the online media they’re drawing on for information and\/or entertainment – and by counting which sites appear most frequently, we’re also able to measure the relative visibility or relevance of such sites).<\/p>\n

But of course, there’s a complication: the vast majority of URLs in tweets have been shortened using a variety of URL shorteners, and multiple short URLs may point to the same eventual target; additionally, it’s even possible – and not too uncommon – for shortener nesting to occur: for example, a bit.ly<\/em> short URL might subsequently be shortened by ow.ly<\/em>, and finally by t.co<\/em>, in the course of retweeting. Working with the short URLs themselves is less than useful, therefore – and we must find ways to resolve them to their eventual target.<\/p>\n

<\/p>\n

I’ve never been entirely happy with the approach to resolving short URLs which I’ve come up with previously<\/a> – this used Gawk (of course) and WGet as a somewhat clunky but generally functional solution. With each URL to be resolved triggering a separate WGet call, and generating temporary files along the way, the results were neither elegant nor particularly fast, though. Speed can only be improved up to a point, of course – the script will still have to ping each short URL to be resolved at least once, to see where it points, and that process is largely dependent on Internet access and server response speeds -, but nonetheless there was plenty of room for optimisation in my previous attempts.<\/p>\n

So, here’s a new approach to this problem. Instead of WGet, we’ll be using the command-line tool cURL<\/a> here, which is able to work with batch lists of URLs to process, and can be made to send its resulting output to a single file which we can then process again. The one downside of cURL is that – contrary to WGet – it only does a single URL resolution hop; this means that where there are nested URL shorteners, our script will need to be run at least twice to resolve the final remaining short URLs (but that’s a small price to pay for greater convenience).<\/p>\n

Update:<\/strong> It’s worth noting here that Twitter<\/em> has recently introduced its own URL shortener, t.co<\/em>, as a mandatory shortener – even links which have already been shortened using bit.ly<\/em> or other tools are now shortened again<\/em> to t.co<\/em> URLs. Recent yourTwapperkeeper<\/em> archives will contain only t.co<\/em> links, therefore. This also means that at least two passes of urlresolve.awk will be required to unshorten those URLs to their eventual destinations: one pass to remove the t.co<\/em> shortening, and another to resolve any remaining short URLs. You might even want to run a third pass for good measure (later passes should run considerably faster as they’ll find far fewer URLs still to resolve).<\/font><\/p>\n

Installing cURL<\/h3>\n

So, the first step to switching over is to install cURL itself, which is available for a wide variety of platforms here<\/a>. If you’re expecting your Twitter<\/em> data to contain https:\/\/… (secure http) URLs in addition to standard http:\/\/… links, make sure you install an SSL-capable version of cURL. On Windows XP, I’ve found the ‘Win32 – Generic’ cURL version by Dirk Paehl<\/a> to work well for me (for https support, use the version ‘with SSL’, and you’ll also need to install the openssl library available from the same site). On Windows Vista or 7, the ‘Win64 – Generic’ version by Don Luchini<\/a> is fine; looks like you also need to install the Microsoft Visual C++ Redistributable package, though (details here<\/a>). If you’re using a Mac, I’m afraid you’re on your own as far as installation goes – a list of package options is here<\/a><\/strike> – see Jean’s comment below for instructions.<\/p>\n

cURL (and the openssl libraries, if you need them) need to be placed in the command path. Since – if you’ve been using any of our Gawk scripts at all so far – you’ve already installed Gawk, the easiest solution is to place curl.exe (and any openssl .dll files you may also need) in the same directory as Gawk itself. Most likely, this is C:\\Program Files\\GnuWin32\\bin (on Windows XP) or C:\\Program Files (x86)\\GnuWin32\\bin (on Windows Vista\/7). To test whether cURL is installed and working, open a command window and try something like<\/p>\n

\n

curl --head --insecure https:\/\/google.com\/<\/p>\n<\/blockquote>\n

(change the https to http if you’ve installed a version of cURL which doesn’t do secure http). If your shell finds cURL, and cURL itself doesn’t complain that it’s missing a library somewhere, you’re ready to roll.<\/p>\n

Resolving URLs<\/h3>\n

The first step in resolving URLs is to extract them from a Twapperkeeper<\/em>\/yourTwapperkeeper<\/em> CSV\/TSV dataset. The process for this remains exactly as before – the existing urlextract.awk script from our scripts package<\/a> does this for us (and generates multiple lines in the resulting file if a tweet happens to contain multiple URLs. Simply run the script as follows:<\/p>\n

\n

gawk -F , -f urlextract.awk input.csv >output.csv<\/p>\n<\/blockquote>\n

(I don’t have to remind you to use \\t instead of , as the separator if you’re working with tab-separated files, do I?)<\/p>\n

Now, then, it’s time to unveil our new urlresolve.awk script, which replaces the previous solution:<\/p>\n

<\/p>\n
\r\n      \r\n# urlresolve.awk - resolves URL shortener URLs back to full URLs\r\n#\r\n# resolves shortened and other redirected URLs to their actual target\r\n# the script preserves the first line, expecting that it contains header information\r\n#\r\n# run urlextract.awk before running this script over the resulting file\r\n#\r\n# input format: CSV\/TSV generated by urlextract.awk, i.e.\r\n# url, [Twapperkeeper format]\r\n#\r\n# output format:\r\n# longurl, url, [Twapperkeeper format]\r\n#\r\n# script requires curl to be sitting in the command path\r\n# use an SSL-capable version of curl if you need to resolve https URLs\r\n#\r\n# script takes an optional argument maxlength, specifying the maximum length of URLs to be resolved\r\n# this is especially useful for running multiple passes over the dataset - after the first run,\r\n# maxlength can be reduced to pick up only the remaining short URLs\r\n#\r\n# maxlength defaults to 30 characters (including http:\/\/ etc.) if it isn't specified\r\n# \r\n# script will create temporary files in the current directory (for the curl output):\r\n# - [filename]_urllist_temp (a list of all URLs in the input CSV\/TSV file\r\n# - [filename]_urllist_temp_resolved (curl output from the URL resolution process)\r\n#\r\n# these files are not automatically deleted after the script finishes\r\n#\r\n# the script only performs one URL resolution pass - to resolve multiple nested URL wrappers\r\n# (e.g. bit.ly links shortened a second time by t.co, etc.), run urlresolve.awk over the output file again,\r\n# as often as desired (each round resolves an additional layer of URL shorteners\r\n#\r\n# NOTE: the script skips YFrog, Twitpic, Imgur, Twitgoo, and Instagram URLs - these don't resolve any further\r\n#\r\n# once URLs have been resolved, run urltruncate.awk over the resulting file to extract the domain names only\r\n#\r\n# released under Creative Commons (BY, NC, SA) by Axel Bruns - a.bruns@qut.edu.au\r\n\r\nBEGIN {\r\n\tif(!maxlength) maxlength = 30\t\t\t\t\t\t# maxlength defaults to 30 characters\r\n\r\n\tgetline header\r\n\tprint \"longurl\" FS header\r\n\r\n\tgetline < FILENAME\t\t\t\t\t\t\t\t\t\t\t\t\t# skip header row\r\n\twhile(getline < FILENAME) {\t\t\t\t\t\t\t\t\t\t\t\t# load all URLs to be resolved - urlextract.awk stored URLs in column 1\r\n\t\tif(match($1,\/(yfrog.us|yfrog.com|twitpic.com|imgur.com|twitgoo.com|instagr.am)\/) == 0) {\t\t# skip various image hosting short URLs - they don't resolve any further\r\n\t\t\tif(length($1) <= maxlength) urls[$1] = $1\t\t\t\t\t\t\t\t# only short URLs of up to maxlength characters will be resolved\r\n\t\t}\r\n\t}\r\n\tclose(FILENAME)\t\t\t\t\t\t\t\t# finish URL detection\r\n\r\n\ttempfile = FILENAME \"_urllist_temp\"\r\n\ttempfile_resolved = tempfile \"_resolved\"\r\n\r\n\tfor(url in urls) {\t\t\t\t\t\t\t\t# generate list of URLs for curl to resolve\r\n\t\tprint \"url=\\\"\" url \"\\\"\" > tempfile\r\n\t}\r\n\r\n\t\t\t\t\t\t\t\t\t\t\t# run curl, using the tempfile\r\n\tsystem(\"curl -K \" tempfile \" --head --insecure --write-out !!urlresolve!!,%{url_effective},%{redirect_url}, >\" tempfile_resolved)\r\n\tclose(\"curl -K \" tempfile \" --head --insecure --write-out !!urlresolve!!,%{url_effective},%{redirect_url}, >\" tempfile_resolved)\r\n\r\n\toldFS = FS\t\t\t\t\t\t\t\t\t# switch to CSV format for the moment\r\n\tFS = \",\"\r\n\r\n\twhile(getline < tempfile_resolved) {\t\t\t\t\t# curl output contains the full HTML headers, as well as the resolve information\r\n\t\tif($1 == \"!!urlresolve!!\") {\t\t\t\t\t# resolve information is in the format \"!!urlresolve!!,[original URL],[resolved URL]\"\r\n\t\t\tresolved[$2] = $3\r\n\t\t\tif(!$3) resolved[$2] = $2\t\t\t\t\t# for broken or otherwise unresolved URLs, we retain the original link\r\n\t\t}\r\n\t}\r\n\r\n\tclose(tempfile_resolved)\r\n\r\n\tFS = oldFS\r\n\r\n}\r\n\r\n{\r\n\tif(!($1 in resolved)) resolved[$1] = $1\t\t\t\t\t# for URLs longer than maxlength, or if curl failed to run because of severely broken URLs, retain the original link\r\n\tprint resolved[$1] FS $0\t\t\t\t\t\t\t# match resolved URLs and original links, and print\r\n}\r\n        <\/pre>\n
<\/code>\n      <\/div>\n
  
This script takes the output from urlextract.awk and resolves all short URLs in the original dataset; it adds the resolved URLs in a new column 'longurl' which is inserted before the existing data. The script is called as follows:<\/p>\n
\n
gawk -F , -f urlresolve.awk [maxlength=x<\/em>] input.csv >output.csv<\/p>\n<\/blockquote>\n
The (optional) maxlength argument specifies what we consider to be a short<\/em> URL, and can further speed up the processing time: since the very point of short URLs is that they're, well, short, we can assume relatively safely that comparatively long URLs already point to the final destination URL, and don't need resolving. If maxlength isn't specified, it defaults to a relatively conservative value of 30 characters; to save time, you could drop that value down to 25 or less. A typical bit.ly<\/em> URL, including the 'http:\/\/' part, is 20 characters long, a URL using Facebook'<\/em>s shortener fb.me<\/em> is 22 characters, and youtu.be<\/em> URLs clock in at 27 characters, so you'll need to work out your own comfort zone here.<\/p>\n
It's also important to note that (again also to save time) the script will automatically skip over any URLs pointing to the image hosting services YFrog<\/em>, Twitpic<\/em>, Imgur<\/em>, Twitgoo<\/em>, and Instagram<\/em>. The URLs used by such sites are short, but - to the best of my knowledge - don't resolve any further, so there's no need to process them here. (If you're aware of any other widely used non-resolving short URL services, or if any of the services listed above do occasionally resolve to different URLs, please let me know!)<\/p>\n
The urlresolve.awk script creates two temporary files in the working directory: filename<\/em>_urllist_temp and filename<\/em>_urllist_temp_resolved. These can be safely deleted once the script has finished, but may also be handy for spotchecking if any problems have occurred during URL resolution.<\/p>\n
As I've mentioned, cURL will only take one step in the URL resolution process. A multiply shortened URL won't have arrived at its final destination in one pass, therefore. It's useful to inspect the output file from the first pass visually (e.g. in Excel) to check whether there still are many shortened URLs remaining in the new 'longurl' column which urlresolve.awk has added. If so, simply run the script again, using the output file from the first pass as your input:<\/p>\n
\n
gawk -F , -f urlresolve.awk [maxlength=x<\/em>] output.csv >output2.csv<\/p>\n<\/blockquote>\n
Since the first pass will have resolved many short URLs already (so that they will now be above the maxlength threshold), this second pass should conclude considerably more quickly. It will add yet another new 'longurl' column to the left of the existing data table. Repeat the process as often as necessary if any particularly obstinate cases remain.<\/p>\n
Further Steps<\/h3>\n
Once you're happy with the URL resolution outcomes, it's easy to use the resulting dataset to find the most cited URLs or examine other relevant patterns in the data. In particular, it may also be useful to examine citation patterns not on the basis of fully qualified URLs, but by looking for the most cited domains only - this provides a better overview of which news or information sources overall were most widely used.<\/p>\n
To truncate URLs to their domain name, use our existing urltruncate.awk script (also from our Gawk scripts package<\/a>). It adds yet another new column, 'domain', to the left of the existing dataset, and is run as follows:<\/p>\n
\n
gawk -F , -f urltruncate.awk input.csv >output.csv<\/p>\n<\/blockquote>\n
Happy resolving!<\/p>\n","protected":false},"excerpt":{"rendered":"
When working with Twitter data, one of the most interesting questions is always what URLs tweets are linking to. As Twitter users discuss any given topic or issue, the URLs they share provide us with an indication of the online media they’re drawing on for information and\/or entertainment – and by counting which sites appear … <\/p>\n
Continue reading “Resolving Short URLs: A New Approach”<\/span><\/a><\/p>\n
<\/p>\n","protected":false},"author":2,"featured_media":1327,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,176,113,8],"tags":[157,7,156,155,6,298],"class_list":["post-1072","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-methods","category-processing","category-tools-2","category-twitter","tag-curl","tag-gawk","tag-resolving","tag-short-urls","tag-tools","tag-twitter","entry"],"_links":{"self":[{"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts\/1072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/comments?post=1072"}],"version-history":[{"count":0,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts\/1072\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/media\/1327"}],"wp:attachment":[{"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/media?parent=1072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/categories?post=1072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/tags?post=1072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}