OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Social Interest Positioning – Visualising Facebook Friends’ Likes With Data Grabbed Using Google Refine

What do my Facebook friends have in common in terms of the things they have Liked, or in terms of their music or movie preferences? (And does this say anything about me?!) Here’s a recipe for visualising that data…

After discovering via Martin Hawksey that the recent (December, 2011) 2.5 release of Google Refine allows you to import JSON and XML feeds to bootstrap a new project, I wondered whether it would be able to pull in data from the Facebook API if I was logged in to Facebook (Google Refine does run in the browser after all…)

Looking through the Facebook API documentation whilst logged in to Facebook, it’s easy enough to find exemplar links to things like your friends list (https://graph.facebook.com/me/friends?access_token=A_LONG_JUMBLE_OF_LETTERS) or the list of likes someone has made (https://graph.facebook.com/me/likes?access_token=A_LONG_JUMBLE_OF_LETTERS); replacing me with the Facebook ID of one of your friends should pull down a list of their friends, or likes, etc.

(Note that validity of the access token is time limited, so you can’t grab a copy of the access token and hope to use the same one day after day.)

Grabbing the link to your friends on Facebook is simply a case of opening a new project, choosing to get the data from a Web Address, and then pasting in the friends list URL:

Google Refine - import Facebook friends list

Click on next, and Google Refine will download the data, which you can then parse as a JSON file, and from which you can identify individual record types:

Google Refine - import Facebook friends

If you click the highlighted selection, you should see the data that will be used to create your project:

Google Refine - click to view the data

You can now click on Create Project to start working on the data – the first thing I do is tidy up the column names:

Google Refine - rename columns

We can now work some magic – such as pulling in the Likes our friends have made. To do this, we need to create the URL for each friend’s Likes using their Facebook ID, and then pull the data down. We can use Google Refine to harvest this data for us by creating a new column containing the data pulled in from a URL built around the value of each cell in another column:

Google Refine - new column from URL

The Likes URL has the form https://graph.facebook.com/me/likes?access_token=A_LONG_JUMBLE_OF_LETTERS which we’ll tinker with as follows:

Google Refine - crafting URLs for new column creation

The throttle control tells Refine how often to make each call. I set this to 500ms (that is, half a second), so it takes a few minutes to pull in my couple of hundred or so friends (I don’t use Facebook a lot;-). I’m not sure what limit the Facebook API is happy with (if you hit it too fast (i.e. set the throttle time too low), you may find the Facebook API stops returning data to you for a cooling down period…)?

Having imported the data, you should find a new column:

Google Refine - new data imported

At this point, it is possible to generate a new column from each of the records/Likes in the imported data… in theory (or maybe not..). I found this caused Refine to hang though, so instead I exprted the data using the default Templating… export format, which produces some sort of JSON output…

I then used this Python script to generate a two column data file where each row contained a (new) unique identifier for each friend and the name of one of their likes:

import simplejson,csv



data = simplejson.load(open(fn,'r'))
for d in data['rows']:
	#'interests' is the column name containing the Likes data
	for i in interests['data']:
		print str(id),i['name'],i['category']

[I think this R script, in answer to a related @mhawksey Stack Overflow question, also does the trick: R: Building a list from matching values in a data.frame]

I could then import this data into Gephi and use it to generate a network diagram of what they commonly liked:

Sketching common likes amongst my facebook friends

Rather than returning Likes, I could equally have pulled back lists of the movies, music or books they like, their own friends lists (permissions settings allowing), etc etc, and then generated friends’ interest maps on that basis.

[See also: Getting Started With The Gephi Network Visualisation App – My Facebook Network, Part I and how to visualise Google+ networks]

PS dropping out of Google Refine and into a Python script is a bit clunky, I have to admit. What would be nice would be to be able to do something like a “create new rows with new column from column” pattern that would let you set up an iterator through the contents of each of the cells in the column you want to generate the new column from, and for each pass of the iterator: 1) duplicate the original data row to create a new row; 2) add a new column; 3) populate the cell with the contents of the current iteration state. Or something like that…

PPS Related to the PS request, there is a sort of related feature in the 2.5 release of Google Refine that lets you merge data from across rows with a common key into a newly shaped data set: Key/value Columnize. Seeing this, it got me wondering what a fusion of Google Refine and RStudio might be like (or even just R support within Google Refine?)

PPPS this could be interesting – looks like you can test to see if a friendship exists given two Facebook user IDs.

PPPPS This paper in PNAS – Private traits and attributes are predictable from digital records of human behavior – by Kosinski et. al suggests it’s possible to profile people based on their Likes. It would be interesting to compare how robust that profiling is, compared to profiles based on the common Likes of a person’s followers, or the common likes of folk in the same Facebook groups as an individual?

Written by Tony Hirst

January 4, 2012 at 11:06 am

20 Responses

Subscribe to comments with RSS.

  1. Tony,
    Do you also have a recipe for visualising networking activity in a closed Fb group? Thx.

    Steven Verjans

    January 4, 2012 at 12:23 pm

  2. @Steven If you are a member of a group, you can get things like members lists using:


    The API docs describe the other classes of group data that you can access: http://developers.facebook.com/docs/reference/api/group/

    Tony Hirst

    January 4, 2012 at 12:43 pm

  3. There’s no reason Google Refine shouldn’t be able to parse the JSON that was fetched if it is well-formed — and it should never just “hang.”* Please fill a bug report with the data that you were trying to parse.

    * “hangs” are usually server side errors that have been handled incorrectly causing the client to spin forever waiting for the results.

    Tom Morris (@tfmorris)

    January 4, 2012 at 8:33 pm

    • @tom Okay, I should have been clearer – I suspect that Google Chrome gave up the ghost as Refine was was trying to spawn hundreds of new columns. I find it hangs a lot; it somehow doesn’t like the fact that I have multiple windows and tabs open for days on end (or would if I didn’t have to keep restarting Chrome because of the way it keeps on stalling/hanging…)

      Tony Hirst

      January 4, 2012 at 9:03 pm

  4. awesome work! just a quick comment about facebook api token. There is a permission called offline_access, which allows you to get a token which doesnt expire. May be handy for your work.

    Mayank Kumar (@krmayank)

    January 13, 2012 at 10:17 pm

    • @ krmayank ooh… I didnlt realise that – will explore.. thanks… (you got a link for that?!)

      Tony Hirst

      January 14, 2012 at 12:07 am

  5. […] week is one by Tony Hirst showing all of your Facebook friends’ likes. The best part is he tells you exactly how to do it using Gephi and Google […]

  6. […] Social Interest Positioning – Visualising Facebook Friends’ Likes With Data Grabbed Using Google… 12 interests = simplejson.loads(d[ 'interests' ]) data = simplejson.load( open (fn, 'r' )) […]

  7. Thanks for this post. I’ve also been working with the set of things that all your Facebook friends like in something I’m calling the Like-O-Meter.

    Facebook: https://www.facebook.com/the.real.like.o.meter
    GitHub: https://github.com/cyberrodent/LikeOMeter

    It seems like it wouldn’t take much to write an app to grab all this as json to feed into Refine (or whatever). You can grab batches of “friends likes” at a go rather than going one at a time. graph.facebook.com/likes?ids=[comma_separated_list_of_uids]?access_token=ABC123

    Jeff Kolber

    January 18, 2012 at 2:33 am

  8. Hi,
    I tried using the python script for converting the JSON file to csv format file, but somehow its not working. I don’t know why it prints the data on the terminal and creates a .csv extension file but does not write anything to it. Can someone guide please?

    Anagha P

    February 24, 2012 at 9:49 pm

  9. Hi, Tony,
    thank you for letting me know Google Refine. If possible, could you help me with some issue? I want to add a column which makes such change (ABCZ1239 –> BCDA2340) based on the value of original column. It should be a piece of cake in Google Refine but I am far from understanding a GREL or similar expresson language. If you can make it easily please help me.

    Tritaya (@Tritaya)

    May 6, 2012 at 3:33 pm

  10. […] Open University, built a visualization that shows this connective data. He’s also a written a step-by-step guide on how he constructed the visualization with Google Refine and […]

  11. […] on Getting Started With The Gephi Network Visualisation App – My Facebook Network, Part I and Social Interest Positioning – Visualising Facebook Friends’ Likes With Data Grabbed Using Google… which described his experiments in analysing and visualising Facebook data. 51.379915 -2.331708 […]

  12. […] time ago, I did a demo of how to map the the common Facebook Likes of my Facebook friends (Social Interest Positioning – Visualising Facebook Friends’ Likes With Data Grabbed Using Google…). In part inspired by a conversation today about profiling the interests of members of particular […]

  13. […] Social Interest Positioning – Visualising Facebook Friends’ Likes With Data Grabbed Using Google… […]

  14. […] Open University, built a visualization that shows this connective data. He’s also a written a step-by-step guide on how he constructed the visualization with Google Refine and […]

  15. […] lots more to know and I’m still working through tutorials to cover some of that. Grabbing Facebook friends likes using OpenRefine varies slightly from the w/s recipe; I’ll post a new version over the next week or two. For […]

  16. https://graph.facebook.com/40796308305/posts?post_id&limit=20000&since=1356998400&access_token=xxxxx
    this gives thousands of user_id and names.

    However, when this URL is fetched in OpenRefine, I get a list of 100 users only. My question is why I am not getting the full list of those user_id and names?


    December 19, 2013 at 12:26 am

    • Amy – it’s been some time since I played with the Facebook API; I wonder if the results returned are actually paged to be capped at 100 results?

      Tony Hirst

      December 19, 2013 at 5:22 pm

Comments are closed.


Get every new post delivered to your Inbox.

Join 1,422 other followers

%d bloggers like this: