Spreadsheet converts tweets for social network analysis in Gephi

EDIT 05/15/13: I’ve posted two scripts, one in PHP and one in Python, that overcome the main limitation of this spreadsheet–they pull in all mentioned names rather than just the first one. Download one or both here.

If you’ve ever wanted to visualize Twitter networks but weren’t sure how to get the tweets into the right format, this spreadsheet I’ve been using in my classes might be worth a try. It prepares Twitter data for importing into Gephi, an open-source network visualization platform. It requires a little cutting and pasting, but once you get the hang of it you’ll be visualizing social network data in no time. Here’s the link:

https://docs.google.com/spreadsheet/ccc?key=0ApYOPWoUBH_zdGY1MzVBOFFrLUhIelJMQkFFNDFGNGc#gid=1

Download the file and open it locally in Excel or OpenOffice to add your own data (right now it uses some of my recent tweets as example data). Prep your data in 4 steps:

1. Add the username(s) of your tweet author(s) to column A of the “code lives here” worksheet.
2. Add your author(s)’ tweets to column B.
3. Copy columns C through H as far down as your tweets go.
4. Export the “output lives here” worksheet as a CSV and open it in Gephi (you may need to copy the formulae in columns A and B as far down as your data go).

Here is a network graph of the example data. Each tie represents the first person I mentioned in one of my past 200 tweets as of today.

TAGS is a free and fairly easy way to pull Twitter data into Google Spreadsheets.