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

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.

• Get thee to a database, why woulds't thou be a breeder of analytical sins? – whuber Feb 16 '12 at 22:43
• Because I'm a noob at data analysis! Oh and the excel spreadsheets were exported from a Mongo Database. – Robin Feb 16 '12 at 22:48
• That sounds like a strong case for sticking with a database app so you can learn to do the data processing right! – whuber Feb 16 '12 at 23:18
• @whuber +1 to your first comment for a terrible attempt at Shakespeare. :) – Michelle Feb 17 '12 at 0:19
• Hi, If you could send me a sample of the data you have(IN Excel) and the Charts required, I would probably be able to help you get started! – Vaibhav Garg Feb 22 '12 at 3:32

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.

• Yes so simple :\ – Robin Feb 17 '12 at 9:37

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

#Pull last 500 tweets for a given topic. Based on:
df <- do.call("rbind", lapply(rdmTweets, as.data.frame))

#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.

• Great thank you, that blog link at the top of the script looks very informative too. – Robin Feb 18 '12 at 14:21
• @Zach - After the command "DT <- DT[,list(n=sum(n)),by='hour, minute']" this R beginner got an error message: "Error in eval(expr, envir, enclos) : object ' minute' not found". Any idea why? – rolando2 Sep 20 '12 at 14:54
• @rolando2 Thanks for the bug report. Replace that line with DT <- DT[,list(n=sum(n)),by=c('hour', 'minute')]. I updated the code in my post. You could also use 'hour,minute', instead of 'hour, minute'. In the latter formulation, data.table is looking for a column with space in the name. – Zach Sep 20 '12 at 16:19
• @Zach - i appreciate it. – rolando2 Sep 20 '12 at 18:29

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.