Automatic Back-Up of ONS files: Google Spreadsheets, JCAMP-DX, Flickr

As many of you know, we have been heavily dependent upon publicly editable Google Spreadsheets for storing results and calculations relating to our Open Notebook Science projects. We have recently integrated automated processing of NMR files in JCAMP-DX format to calculate solubility data by using web services called directly from within the Spreadsheets.

That represents a lot of distributed technology that is susceptible to network or server problems. Andy Lang, who wrote the web services that currently calculate the solubility, has enabled the recall of previously calculated values via a quick database look-up. While this substantially reduces server load by avoiding lengthy calculations, it does mean that the final numbers do not exist in the Spreadsheets themselves.

In addition to these concerns, every time I give a talk to a group of librarians the issues of archiving and curation of new forms of scholarship are raised. These are valid concerns and I've been trying to work with several groups to deal with the problem in as automatic a way as possible.

We had initially considered a spidering service that would automatically follow every file linked to the ONS wikis and download the documents on a daily basis. This has turned out to be problematic because many of the links don't terminate directly on files, but rather user interfaces. For example, a typical link to a Google Spreadsheet does not lead to a simple HTML page that can be copied but rather to an interface to add data and set up calculations.

It turns out we can take a semi-automated solution that gets us to where we want to be but requires a bit more manual work. Google Spreadsheets can be exported as Excel spreadsheets, which store the results of web service calculations as simple values and include the link to the web service as a cell comment. All calculations within the Spreadsheet are also retained in this way. The trick is to "publish" the spreadsheet using the advanced option of exporting as an Excel file. This then becomes a simple URL.


Now, the only manual step left in the process is to copy these URLs to another BackUp Google Spreadsheet. Andy has created a little executable that steps through a list of these URLs and creates a backup on any Windows computer under a C:\ONS directory. It is simply then a question of setting up a Windows Scheduler service to run once a day and call the executable. All the files are named with the date as a first part of the name for easy sorting.



Besides Google Spreadsheets backed-up as Excel files, spectral JCAMP-DX files and Flickr images can be processed in the same way. In both these cases the user must specify the JDX or DX or JPG file directly. In Flickr you have to go through a few clicks to the download page for a given image but once you have that it works fine.

Andy has versioned this as V0.1 for good reason. It does do exactly what we want but there are a few caveats:

1) Any errors in specifying a file will abort the rest of the back-up. In future versions there would be tolerance for errors, with appropriate reporting of problems, perhaps by email.

2) Files don't necessarily have the correct extensions. For example, backed up Wikispaces pages have to be renamed with an HTML extension to be viewed in a browser. Note that Wikispaces has its own sophisticated back-up system that will put the entire wiki with all files directly uploaded onto the wiki into a single ZIP file - in either HTML or WIKITEXT format. Of course this will not include files residing outside - like Google Spreadsheets. Still I think there is no harm in including the wiki pages in the the list of files to be backed-up by Andy's system.

Going forward there are two types of collaborations that could help a lot:

1) Librarians who would be willing to archive UsefulChem and ONSChallenge files. Right now these are just a few Megs a day but this will increase as we continue to add to the list. To be reasonable about space I could see a protocol of keeping only one back-up per week or month for dates more than 30 days in the past. This is about what the Internet Archive does I think. It would certainly be unambigous to know for certain what was known at what time with multiple libraries maintaining archives.

2) Someone who knows how Google creates URLs for downloadable XLS exports would be mightly helpful. Similar for Flickr and JPG exports. Even just writing a script to spider all HTML pages linked to the wikis and blogs would save a lot of manual labor. The nice thing is that the results of the spidering code would just have to be dumped into the Back-Up Google Spreadsheet - which already backs itself up conveniently.

Mechanical Turk Does Solubility on Google Spreadsheet

This has been in the works for several weeks but I think we finally have a practical use for the Amazon Mechanical Turk to help with processing solubility data for the Open Notebook Science Challenge. Many thanks to Tenesha Gleason for the assistance over the phone and online - and to Deepak Singh for getting the ball rolling.

The original idea was to try to crowdsource the measurement of solubility in the laboratory using Mechanical Turk. That probably isn't realistic with the current workforce but I can definitely see that it could be done with the pre-training of select groups around the world. If done properly this could be a very efficient way to get all types of scientific research executed. But I suspect it would be quite difficult to allocate government agency funds for this purpose in a proposal. Luckily other more flexible organizations (like Submeta) are popping up to fill the gap.

So what is realistic then?

We tried different task descriptions and price points to see if we could at least get workers to look up non-aqueous solubility data. There were a few takers - but only one actually found a valid non-aqueous measurement (benzaldehyde in ethanol). All the others either gave aqueous solubility or other irrelevant data like molecular weight or solvent density. Perhaps once we have trained some people to do this properly it will make sense to use Mechanical Turk this way. But right now there is just too much chemistry knowledge required to handle queries of this format.

One of the advantages of Open Notebook Science is that the work is naturally modular because it has to be shared in as close to real time as possible. That doesn't mean that it works flawlessly but it does make it more likely that information that has to be shared will be easier to understand and use by anyone or who wishes to move the project forward. True open crowdsourcing depends on this.

So lets come back to comparing solubility measurements made by our students with those in the literature. But now, instead of asking a high level query, we take the work we've already done on our side to find papers and ask the Turks to extract out the data from selected tables and figures.

I just tried this yesterday and it worked like a charm. I just grabbed a snapshot of the table of interest and asked to convert it to a Google Spreadsheet format that I can readily incorporate into the Solubiliby Summary master sheet. The Turk worker started working within minutes and was done in a quarter of an hour - total cost 46 cents.

This enabled me to add about 70 new entries into the master spreadsheet and allows us now to do quick comparisons using Rajarshi's web query interface. For example, it is pretty clear that the 4-nitrobenzaldehyde solubility measurements by Maccarone, E; Perrini, G. Gazzetta Chimica Italiana vol 112 p 447 (1982) in chloroform are consistent with our measurements in EXP212.


Now this is clearly NOT the case for the solubility of 4-nitrobenzaldehyde in methanol. Our 3 values from EXP212 and EXP205 are much lower than the values from the literature. Reading the experimental section we find that Maccarone and Perrini let their solutions equilibrate over 24 hours while we vortexed for only a few minutes. It is interesting that our chloroform values were consistent. Perhaps there are very large differences in solubility kinetics between solvents. Obviously we have to do this measurement again with much longer mix times.


On a more general note, using a Google Spreadsheet to collect Mechanical Turk results has the advantage that the progress can be monitored in real time and it is possible to chat with the worker. The person who worked on this project was obviously familiar with Google Spreadsheets and approached the task in a similar way that I would have. Here is a video of that process sped up ten fold:

Google Visualization API on ONS solubility data

Rajarshi has just tweaked his ONS solubility web query interface with Google Visualization tools to display the solubility of solutes in all available solvents in a chart, ranked lowest to highest. This kind of snapshot is perfect for finding possible errors and comparing duplicate runs or measurements using different techniques. The lab notebook page for any suspect measurement can be accessed by scrolling down the page and clicking on the reference link.

Currently this is only set up for selecting a solute. Give it a spin.

update: Rajarshi has a lot more details in this post

Tracking Results with Workflow Tables

Following my post about shifting the storage of chemistry experiments to a results-centric model, I received lots of good feedback.

Egon pointed out an ambiguity in specifying the addition of a compound and that is now fixed in RESULT0001, RESULT0002 and RESULT0003. Instead of

ADD methanol (InChIKey=OKKJLVBELUTLKV-UHFFFAOYAX, volume=1 ml)

we now have:

ADD compound (common name=methanol, InChIKey=OKKJLVBELUTLKV-UHFFFAOYAX, volume=1 ml)

Peter demonstrated some related work of his using CML to represent reactions taken from experimental sections of published articles. This looks tricky because there is usually a lot of missing information in journal articles but I definitely think it is worth doing. We're using our laboratory notebook (specifically the log sections) so we have reasonably complete information in most cases.

I certainly am interested in using CML to represent our result modules and I appreciate Peter's help in trying to translate some of our modules into CML. Hopefully everything can be specified with the existing components of CML and CMLReact.

But representing the information in machine-readable format is just one half of the equation. Being able get information back out with powerful queries is just as important.

Antony's comments about workflows got me to rethink the problem from a slightly different angle. Although the result files that I have been constructing are very flexible, until someone actually populates a database with the data they describe, it will be difficult to get aggregate information back out. The main problem is that to compare two workflows requires lining up the corresponding actions. It is doable but requires some intelligent processing, only possible once a database is in place.

However, by sacrificing a bit of the generality, we can gain a lot in the short term. The vast majority of reactions that we've carried out in my lab are just variations on the Ugi synthesis. All Ugi syntheses have an amine, an aldehyde, a carboxylic acid, an isonitrile and a solvent. It turns out that with a series of tables, we can represent all the workflows leading to a result in a way that enables ready comparison and sorting.

The first table records the time of action initiation (normalized to minutes) for each workflow. Since these are in absolute times from the start of the experiment, the order of the columns is unimportant. If we were looking for experiments where the aldehyde was added after the amine, we would simply substract the aldehyde addition time from the amine addition time and look for positive values. Also reactions involving only the formation of an imine would be a subset of the Ugi reaction with blanks for acids and isonitriles.

The second table records the quantities of compounds (normalized to millimoles) and the third records the duration of time variable actions (normalized to minutes). Examples of the latter include vortexing and centrifugation durations.

Two additional tables record the identity of the compounds, one using the InChIKey for machine recognition and the other a common name for human use.

I have represented all of the workflows with documented results for EXP150. Links are available to the raw image data on Flickr or JCAMP-DX files for the NMR and IR spectra on our server.

Using GoogleDocs is very nice for this kind of thing. Right clicking on any cell offers a Google search, which is extremely convenient for the InChIKey. It is also easy to make the data public this way and invite collaborators. (Speaking of which, I need some help to complete the conversion from the wiki to these tables :)