{"id":137,"date":"2010-08-02T17:28:07","date_gmt":"2010-08-02T07:28:07","guid":{"rendered":"http:\/\/www.mappingonlinepublics.net\/dev\/2010\/08\/02\/using-gawk-to-resolve-url-shorteners\/"},"modified":"2022-02-22T22:16:14","modified_gmt":"2022-02-22T12:16:14","slug":"using-gawk-to-resolve-url-shorteners","status":"publish","type":"post","link":"https:\/\/mappingonlinepublics.net\/dev\/2010\/08\/02\/using-gawk-to-resolve-url-shorteners\/","title":{"rendered":"Using Gawk and Wget to Resolve URL Shorteners"},"content":{"rendered":"

Jean’s post today<\/a> points to a key problem in examining user activities on Twitter<\/em> and elsewhere – people are increasingly using bit.ly and other URL shorteners, which means that a) the same target URL might appear in any number of different shortened versions, and b) it’s no longer possible from a quick look at a list of URLs to select only those which are from a specific site (for example, YouTube<\/em> videos).<\/p>\n

For our purposes, that’s a significant problem – we might want to find out, for example, which were the most popular videos shared during the election campaign, the most popular articles on abc.net.au<\/em>, and so on. So, we need to resolve those shortened URLs back to their original state. This could be done through the APIs of the various shortening services, of course, but with literally hundreds<\/a> of different shorteners now available, that would probably require specific unshortening scripts for each service – far too much work. So, what can we do?<\/p>\n

<\/p>\n

Once again, our favourite CSV processing tool, Gawk<\/a>, can help. In fact, Gawk has some in-built networking functions, and there’s even a standard script for retrieving Web pages that might form the basis for an URL resolver tool – but unfortunately, the Windows version of Gawk doesn’t implement that networking functionality. But there’s a workaround using another open-source tool, Wget<\/a>: we can call it from a Gawk script to do the URL retrieval work and check for any URL redirections in the process. (Like Gawk, Wget is available in versions for all major operation systems.)<\/p>\n

URL Shortening<\/a> is necessary in twitter. But one step at a time. In the first place, here’s linkextract.awk, a Gawk script to extract the URLs from a list of tweets (for example, a Twapperkeeper<\/em> archive). Where there are multiple URLs in the same tweet, the script will create multiple lines; its output is a new CSV file which contains the URL in the first column, followed by all the original information.<\/p>\n

<\/code><\/code><\/p>\n

<\/code><\/code><\/p>\n

      \n# linkextract.awk - extract hyperlinks from Twapperkeeper export data\n#\n# this script takes a CSV archive of tweets in Twapperkeeper format\n#\n# expected data format:\n# 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\n#\n# output format:\n# link, [original fields]\n#\n# for tweets containing multiple links, the script adds new rows for each link\n#\n# Released under Creative Commons (BY, NC, SA) by Jean Burgess and Axel Bruns - je.burgess@qut.edu.au \/ a.bruns@qut.edu.au\n\nBEGIN {\n\tgetline header\n\tprint \"url,\" header\n\tIGNORECASE = 1\n}\n\n$0 ~ \/http([A-Za-z0-9[:punct:]]+)\/ {\n\na=0\ndo {\n\tmatch(substr($1, a),\/http([A-Za-z0-9[:punct:]]+)?\/,atArray)\n\ta=a+atArray[0, \"start\"]+atArray[0, \"length\"]\n\n\tif (atArray[0] != 0) print atArray[0] \",\" $0\n\n} while(atArray[0, \"start\"] != 0)\n\n}\n<\/pre>\n
<\/code><\/p>\n<\/div>\n
Normally, you’d want to invoke this script using a command line like gawk -F , -f linkextract.awk input.csv >output.csv<\/span>.<\/p>\n
So far, so good. Next, we need a script that resolves the links in the new ‘url’ column in the CSV to their full URLs (and leaves intact those URLs which don’t use URL shorteners, of course). For this, we need three interlocking scripts. First, urlget,awk: this simply takes a command-line input and calls up Wget (with the –spider option, to avoid having to retrieve the whole page), and sends the Wget output to a temporary file called urltemp.tmp, in the current directory).<\/p>\n
<\/code><\/code><\/p>\n
<\/code><\/code><\/p>\n
      \n# urlget.awk - use wget to spider a given URL (passed to the script as argument)\n#\n# outputs wget result as file 'urltemp.tmp' to current directory\n#\n# released under Creative Commons (BY, NC, SA) by Axel Bruns - a.bruns@qut.edu.au\n\n\nBEGIN {\n\n\tsystem(\"wget -t 1 -o urltemp.tmp --spider \" url)\n\n}\n\n\n<\/pre>\n
<\/code><\/p>\n<\/div>\n
You could call this up using gawk -f urlget.awk -v url=http:\/\/abc.net.au\/<\/span> to see what it does – open the resulting ‘urltemp.tmp’ file to see the Wget output.<\/p>\n
The second script we need is parseurltemp.awk, which takes the Wget output and extracts from it what for our purposes is the relevant information. When called with the –spider option, Wget doesn’t retrieve the whole page at the URL, but instead simply tests that it is there and generates some header information. From this, we want to extract the original source URL (on line 2 of Wget’s output, following a retrieval timestamp), and the URL of the target page it it differs from the source. If it is different, the target appears on a line that starts with the word “Location:” – so we know what to look for here, too.<\/p>\n
UPDATE:<\/strong> Looks like there are cases where Wget produces output with multiple ‘Location:’ lines. I’ve now changed parseurl.awk to stop after the first one, which seems to contain what we’re after…<\/span><\/p>\n
<\/code><\/code><\/p>\n
<\/code><\/code><\/p>\n
      \n# parseurltemp.awk - extract redirection source and target URLs from wget spider output\n#\n# parses standard wget --spider output\n# extracts source URL from second line: e.g. --2010-08-02 14:41:47--  http:\/\/smh.com.au\/\n# extracts target URL from 'Location: ' statement\n# sets target = source if no target is found (i.e. no redirection present)\n#\n# output: source,target\n#\n# released under Creative Commons (BY, NC, SA) by Axel Bruns - a.bruns@qut.edu.au\n\n{\n\tif((NR == 2) && (match($0,\"--  \"))) { \n\t\tsource = substr($0, 26, length($0) - 25)\n\t\ttarget = source\t\t\t\t# in case no target location is found\n\t}\n\n\n\tif(match($0, \"Location\")) {\n\t\ttarget = substr($0, 11, length($0) - 22)\n\t\texit\n\t}\n\n}\n\nEND {\n\tprint source \",\" target\n}\n\n<\/pre>\n
<\/code><\/p>\n<\/div>\n
So, parseurltemp.awk simply checks the file it is fed for those two lines, and puts them out in a simple source,target CSV format. If no redirection from the source URL is detected, target is set to the same value as source. Again, this isn’t a script we want to call manually – so to join all of this together, then, we need a third script that invokes urlget.awk and parseurltemp.awk for each line of a CSV file that contains the URLs we want to resolve: urlresolve.awk.<\/p>\n
<\/code><\/code><\/p>\n
<\/code><\/code><\/p>\n
      \n# urlresolve.awk - resolves URL shortener URls back to full URLs\n#\n# script requires:\n# - wget to be sitting in the command path\n# - path variable to be set to common directory for AWK script\n# - urlget.awk and parseurltemp.awk to be in script path directory\n# \n# script will create a temporary file 'urltemp.tmp' in the current directory (for the wget output)\n#\n# released under Creative Commons (BY, NC, SA) by Axel Bruns - a.bruns@qut.edu.au\n\nBEGIN {\n\tpath = \"..\\\\_scripts\\\\\"\t\t# point this to AWK script directory\n\tgetline header\n\tprint \"longurl,\" header\n}\n\n\n{\n\n\toriginal = $0\n\n\tsystem(\"gawk -F , -v url=\" $1 \" -f \" path \"urlget.awk\")\n\n\t\"gawk -F , -f \" path \"parseurltemp.awk urltemp.tmp\" | getline $resolved\n\tclose(\"gawk -F , -f \" path \"parseurltemp.awk urltemp.tmp\")\n\n\tprint $2 \",\" original\n\n}\n<\/pre>\n
<\/code><\/p>\n<\/div>\n
This script, then, takes any CSV file which contains simple URLs in its first column; for each line of the CSV file, it first invokes urlget.awk to spider the source URL and generate the urltemp.tmp file, and then parseurltemp.awk to parse the temp file and identify the target of any URL redirection that may be in place. It adds the resolved target URLs to the CSV file as a new first column, called ‘longurl’ (with the resolved URL set to the original URL if no redirection was detected).<\/p>\n
Usually, this would then be invoked using something like gawk -F , -f urlresolve.awk output.csv >resolved.csv<\/span> (using the output.csv file created by the linkextract.awk script above). Depending on your own setup, you’ll also need to change the ‘path’ variable in the BEGIN statement of linkextract.awk – this should point to where all your gawk scripts are located.<\/p>\n
So there it is – a relatively lightweight Gawk\/Wget solution for resolving shortened URLs to the original. Plenty of room for improvement, no doubt – and we’d love to hear from anyone who has done any further work on these scripts. One small issue we’ve identified already: there’s a handful of sites which don’t report back full ‘Location’ URLs, but only relative ones (i.e. ‘\/home\/index.html’ instead of ‘http:\/\/example.com\/home\/index.html’). This doesn’t seem to be a particularly common problem, so I’m reluctant to spend much time adding a special fix to the scripts, but it does mean some manual tidying of the output may be necessary. UPDATE:<\/strong> My revised parseurl.awk seems to deal with those problems, too.<\/span><\/p>\n
Finally, a word of warning:<\/strong> with large input CSVs, this URL resolution process might take considerable time, as each URL needs to be spidered individually – right now, there’s no caching of Wget results built in here (in the time it’s taken me to write this post, my script has managed to ping about 3000 URLs, for what it’s worth). Also, the rapid-fire querying of URLs might upset some ISPs – so only try this at home if you know what you’re doing, folks…<\/p>\n","protected":false},"excerpt":{"rendered":"
Jean’s post today points to a key problem in examining user activities on Twitter and elsewhere – people are increasingly using bit.ly and other URL shorteners, which means that a) the same target URL might appear in any number of different shortened versions, and b) it’s no longer possible from a quick look at a … <\/p>\n
Continue reading “Using Gawk and Wget to Resolve URL Shorteners”<\/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,8],"tags":[7,297,9,298,31,30],"class_list":["post-137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-methods","category-processing","category-twitter","tag-gawk","tag-methods","tag-twapperkeeper","tag-twitter","tag-url-shorteners","tag-wget","entry"],"_links":{"self":[{"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts\/137","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=137"}],"version-history":[{"count":1,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":3494,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/posts\/137\/revisions\/3494"}],"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=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mappingonlinepublics.net\/dev\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}