Regular expressions and Resolver One column-level formulae

Recently at Resolver we've been doing a bit of analysis of the way people, parties and topics are mentioned on Twitter and in the traditional media in the run-up to the UK's next national election, on behalf of the New Statesman.

We've been collecting data, including millions of tweets and indexes to newspaper articles, in a MySQL database, using Django as an ORM-mapping tool -- sometime in the future I'll describe the system in a little more depth. However, from our perspective the most interesting thing about it is how we're doing the analysis -- in, of course, Resolver One.

Here's one little trick I've picked up; using regular expressions in column-level formulae as a way of parsing the output of MySQL queries.

Let's take a simple example. Imagine you have queried the database for the number of tweets per day about the Digital Economy Bill (or Act). It might look like this:

+------------+----------+
| Date       | count(*) |
+------------+----------+
| 2010-03-30 |       99 |
| 2010-03-31 |       30 |
| 2010-04-01 |       19 |
| 2010-04-02 |       12 |
| 2010-04-03 |        2 |
| 2010-04-04 |       13 |
| 2010-04-05 |       30 |
| 2010-04-06 |      958 |
| 2010-04-07 |     1629 |
| 2010-04-08 |     1961 |
| 2010-04-09 |     4038 |
| 2010-04-10 |     2584 |
| 2010-04-11 |     1940 |
| 2010-04-12 |     3333 |
| 2010-04-13 |     2421 |
| 2010-04-14 |     1319 |
| 2010-04-15 |     1387 |
| 2010-04-16 |     3194 |
| 2010-04-17 |      860 |
| 2010-04-18 |      551 |
| 2010-04-19 |      859 |
| 2010-04-20 |      685 |
| 2010-04-21 |      528 |
| 2010-04-22 |      631 |
| 2010-04-23 |      591 |
| 2010-04-24 |      320 |
| 2010-04-25 |      363 |
| 2010-04-26 |      232 |
+------------+----------+

Now, imagine you want to get these numbers into Resolver One, and because it's a one-off job, you don't want to go to all the hassle of getting an ODBC connection working all the way to the DB server. So, first step: copy from your PuTTY window, and second step, paste it into Resolver One:

Right. Now, the top three rows are obviously useless, so let's get rid of them:

Now we need to pick apart things like | 2010-03-30 | 99 | and turn them into separate columns. The first step is to import the Python regular expression library:

...and the next, to use it in a column-level formula in column B:

Now that we've parsed the data, we can use it in further column-level formulae to get the dates:

...and the numbers:

Finally, let's pick out the top 5 dates for tweets on this subject; we create a list

...sort it by the number of tweets in each day...

...reverse it to get the ones with the largest numbers of tweets...

...and then use the "Unpack" command (control-shift-enter) to put the first five elements into separate cells.

Now, once we've done this once, it's easy to use for other data; for example, we might want to find the fives days when Nick Clegg was mentioned most on Twitter. We just copy the same kind of numbers from MySQL, paste them into column A, and the list will automatically update:

So, a nice simple technique to create a reusable spreadsheet that parses tabular data.