Merge SQlite database

cellprofiler-analyst

#1

Hi there,
I have only been using cell profiler and the analysis software 2-3 weeks now. What I would like to do is get all my data into a single sqlite database to analyze in CPA. I have a complete dsRNA screen (72-384 well plates). I setup a CP pipline and exported the data to both a spread sheet and to a sqlite database. Since I only have access to a computer at night, I analyzed 6-8 plates a night using the same pipeline and had it export to a different folder for each batch of plates. I have been trying to adapt the properties file to load all the analysis into a single sqlite database for analysis. I have read many of the posts but have been unable to figure out how to adapt any of the previous ideas, and I could not find the tool creatMasterTablewizard.py, which I thought may work. Any help would be greatly appreciated.

Thank you
James


Append experiments to existing database?
#2

Hi James,

Unfortunately, we do not have tools to combine SQLite databases. CreateMasterTableWizard was written for MySQL databases on a remote server as opposed to SQLite. This webpage mentions possible solutions that you could try.

Along with the SQLite database, did your pipelines also output an output file (i.e, DefaultOUT.mat or the like). If so, the same measurements are stored there as well. In this case, an alternte the other possibility is to use the MergeOutputFiles data tool to merge the individual output files into one. Then you can use the ExportToDatabase data tool (note: not the module) to export the merged measurements to a new SQLite database.

Regards,
-Mark


#3

Dear Mark,
Thank you very much for your help. The pipeline I generated does output a DefaultOUT.mat file. I unfortunately have had difficulty getting the mergeoutputfiles to work. Meaning, I have opened CP and have used the the mergeoutput data tool under the drop down menu and I can not get the window to populate with any files names. I have tried a few variations of where I have saved the data or what I have called files. I also tried to use the data tool by running the examplepercent positive CP training set that generates a defaultOUT.mat file. I duplicated that file in an attempt to merge them but once again I can not get the window to populate with any file names.

I am running cellprofiler (r10997) on a os10.6 mac tower.

I really like the idea and I think it will work, I just feel as if I am doing something silly with the data tool.

Once again thanks for your help.

James


#4

Hi James,

This is an issue that we have attempted to fix in our source code, though it has not yet been released. However, you can try it out with our latest public build from source code here which has the bugfix. The trunk build is not as heavily vetted as our releases, so you should take that into account; please note the caveats mentioned on the page linked.

Regards,
-Mark


#5

Hi there,

I tried to use Mark’s suggestion (merging defaultout.mat files and then the ExportToDatabase tool) to merge 2 databases, but I wanted to include the thumbnail images in order to use the classifier. However, when I ran ExportToDatabase, I got the following error:

Traceback (most recent call last):
File “cellprofiler\gui\datatoolframe.pyc”, line 216, in on_run
File “cellprofiler\modules\exporttodatabase.pyc”, line 1715, in run_as_data_tool
File “cellprofiler\modules\exporttodatabase.pyc”, line 1732, in run
File “cellprofiler\cpimage.pyc”, line 629, in get_image
File “cellprofiler\cpimage.pyc”, line 673, in get_image_provider
AssertionError: No provider of the Overlay image

I’m assuming that the thumbnails aren’t written to the .mat file, so it can’t now find them. Is there a way round this?

Thanks,
Francesca


#6

I’m not sure about how to fix the particular error (but can dig into it), but my understanding is that you don’t strictly NEED the thumbnails to have the images show up in the classifier, it just makes it a bit faster to load the tiles. Can you try exporting the database without the thumbnails and just see if CPA works as you expect?


#7

I was just trying that as you were typing and yes - it works!!
Unfortunately it doesn’t show the image I specify (An overlay of object outlines onto the original colour image), but the black and white raw image, which is a little trickier to work with.
But it works and is definitely doable. Thanks!


#8

Glad to hear it!

You should be able to control which channels are shown in your ExportToDatabase settings and/or after the fact in your properties file, FWIW.