Mono is an open source version of the .NET framework; it allows you to run .NET applications not just on Windows but on Linux and the Mac. I've spent quite some time over the last week getting our Python spreadsheet, Resolver One, to run on the Windows version, and thought it would be worth sharing some experiences.
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.
Today I wrote the code required to call part of the OpenCL API from Resolver One; just one function so far, and all it does is get some information about your hardware setup, but it was great to get it working. There are already .NET bindings for OpenCL, but I felt that it was worthwhile reinventing the wheel -- largely as a way of making sure I understood every spoke, but also because I wanted the simplest possible API, with no extra code to make it more .NETty. It should also work as an example of how you can integrate a C library into a .NET/IronPython application like Resolver One.
I'll be documenting the whole thing when it's a bit more finished, but if you want to try out the work in progress, and are willing to build the interop code, here's how:
- Make sure you have OpenCL installed -- here's the NVIDA OpenCL download page, and here's the OpenCL page for ATI. I've only tested this with NVIDIA so far, so I'm keen to hear of any incompatibilities.
- Clone the dot-net-opencl project from Resolver Systems' GitHub account.
- Load up the
DotNetOpenCL.slnproject file in the root of the project using Visual C# 2008 (here's the free "Express" version if you don't have it already).
- Build the project
- To try it out from IronPython, run
- To try it in Resolver One, load
That should be it! If you want to look at the code, the only important bit is in
DotNetOpenCL.cs -- and it's simply an external method definition... the tricky
bit was in working out which OpenCL function to write an external definition for,
and what that definition should look like.
I've put a slightly tidied version of the notes I kept as I implemented this below, for posterity's sake; if you're interested in finding out how the implementation went, read on...
I'm putting together some spreadsheets that we're going to use to publicise Resolver One over the coming UK electoral campaign, and one set of data I needed was a list of Members of Parliaments' Twitter accounts indexed by an ID that I could use with TheyWorkForYou.com. I was delighted to discover Tweetminster, a site analysing MPs' tweets, and in particular their Twitter list of all UK MPs' accounts, but there was no link to TWFY.
So, given that no-one else seems to have done it, here's my own list:
- A Resolver One file listing Twitter accounts for TheyWorkForYou
- For those of you unlucky enough to not have a copy of Resolver One, here's a CSV file with exactly the same data.
Hope someone finds it useful. It's up-to-date as of this posting, and I'll endeavour to keep it up-to-date, at least for as long as we need it at work :-)
Didrik Pinte has put together a web page on the Python.org Wiki for the London Financial Python Users Group. Only a little content so far, but it will grow... if you're doing financial work in Python in London, do come along to the next meeting -- it will be 7pm next Monday (14 December) at MWB Regent Street, Liberty House 222 Regent Street, London W1B 5TR. You may have to put up with me talking for a while about a spreadsheet you already know everything about, but there will be interesting bits too ;-)
I've done another 3D example in Resolver One. This one uses Yahoo! Finance to download the close prices over the last two years for every stock that's currently in the Dow Jones index, then charts them in a 3D window which you can pan and zoom using the mouse. Here's a video showing it in action (click the image to view):
In my last post about animated 3D graphics in Resolver One (the souped-up spreadsheet the company I work for makes), I showed a bouncing, spinning cube controlled by the numbers in a worksheet. Here's something more sophisticated: a 3D model of the planets in our solar system, also know as an orrery (click the image for video):
I've been playing around with 3D graphics recently, and decided to find out what could be done using .NET from inside Resolver One. (If you haven't heard of Resolver One, it's a spreadsheet made by the company I work for -- think of it as Excel on steroids :-)
I was quite pleased at what I managed with a few hours' work (click the image for video):
Yesterday, my co-founder Robert Smithson presented a fascinating spreadsheet he's built in Resolver One to one of the Financial Times' two UK political correspondents, Jim Pickard. The spreadsheet gives predictions about the next UK general election using a clever methodology Robert has developed, and if you're interested in british politics or clever spreadsheets, you should definitely take a look.
[Update, 27 July] Looks like The Register found the story interesting too!
We kicked off the beta programme for version 1.5 of Resolver One today. It's got some really cool new features, including a console that lets you interact with your spreadsheets from a command-line-style interface, but there's one other change, a tiny one that enables something really interesting -- a combination of the spreadsheet's ease-of-programming with seriously parallel computing that I don't think is really possible with other tools.
We've been in touch with Digipede since Dan Ciruli, their Director of Products, blogged about Resolver One in January 2008. The Digipede Network is a system that allows you to easily code .NET programs that run on a grid of computers -- and he'd set up a Resolver One spreadsheet that was able to call into code running on a Digipede Network to perform part of its calculations, which was particularly impressive given that he only needed to spend five minutes or so putting it together. Looking at what he'd done, I found myself asking "wouldn't it be even cooler if the thing you ran on your compute farm was itself a spreadsheet?"