Analysing over 300,000 rows in Excel to make pretty graphs

Question

I'm doing a research module for my Computer Science degree, and for my topic, I have collected over 500,000 tweets using the Twitter Streaming API, using a ruby script to store them in a Mongo database (BSON/JSON). I started recording the tweets on Tuesday 7th Feb, and stopped the following Tuesday, so there is a week's worth of tweets.

Here is what the spreadsheet looks like.

I have successfully exported around 300,000 tweets to an excel spreadsheet (I can hear groans already).

I would like to make some time series charts, for example volume of tweets over time and eventually include followers_count as a weighting. But I'm unsure as to how I would calculate this. I think I need to make the created_at column more meaningful to excel but converting it to a date/time it can understand.

I've also had a go with Rapid miner and managed to import a spreadsheet and convert the created_at field into something the program can understand, but I didn't really have any idea what I was doing after that!

I'd really appreciate some hints as I'm a bit stuck right now.

Answer 1

You can import the spreadsheet into R.

Then you can use the function qplot, from the ggplot2 package. It doesn't get much easier than that.

Answer 2

You absolutely need to do this in R. Here's why:

#Pull last 500 tweets for a given topic. Based on:
#http://blog.ouseful.info/2011/11/09/getting-started-with-twitter-analysis-in-r/
require(twitteR)
rdmTweets <- searchTwitter('#R ', n=500)
df <- do.call("rbind", lapply(rdmTweets, as.data.frame))
head(df)

#Calculate tweets per minute
library(data.table)
library(lubridate)
DT <- data.table(hour=hour(df$created), minute=minute(df$created), n=1)
DT <- DT[,list(n=sum(n)),by=c('hour', 'minute')]

#Plot 
library(ggplot2)
DT$time <- ISOdatetime(year(today()), month(today()), day(today()), DT$hour, DT$minute, 0)
p <- ggplot(DT, aes(time, n)) + geom_line()
print(p)

11 lines of code to create the sort of plots you want. If you import the data straight from your spreadsheet, it will be very, very easy to work with. data.table can aggregate summary stats for about 10 million tweets on my laptop before it chokes, and scales up to far more than that on better hardware.

Answer 3

whuber is right -- particularly given your background in computer science -- but ...

The DATEVALUE function will convert many types of text strings into an Excel numeric date. Otherwise, you may have to play around with the FIND and MID functions to get the substrings you need. I can definitely see that the FIND and MID approach will work on the snippet you sent.

Then, your best bet is to use a pivot table to organize the data into some meaningful chunks to graph.