Per-well data and export to spreadsheet


I know that there are many posts and topics about getting or trying to get per-well data using the exporting to database module and then using CPA.

But, is there a straightforward way or module of getting per-well data and still use the export to spreadsheet module?,

For example, in a data set in which four images of a well are taken, get the average of those for images for a given measurement in order to get per-well data? As simple as this, is it possible?

Thanks a lot!




I think the most “straightforward” way is to identify a whole image as an object (many tricks can achieve this, for e.g. using ImageMath to brighten up the image, then using a low manual threshold in IdentifyPrimaryObjects)

Then in the “ExportToDatabase”, check the “Calculate the per-well mean values of object measurements”

It will then calculate an average of 4 “objects” (which are actually 4 full images)

Hope that helps

Hi Minh,

Thanks for the input!

Indeed, this sounds like a good workaround. However, what I would like to avoid is to use ExportToDatabase because we don’t use CPA, instead I would like to have this using the ExportToSpreadsheet module. But of course, this means per-image mean values, and not per-well :confused:

I see,

Then the workaround is probably:

  • In NameAndType, name each image from the same well as if they are 4 channels. For e.g. DNA1, DNA2, DNA3, DNA4
  • Then use Tile >> Within Cycle >> stitch DNA1, DNA2, DNA3, DNA4 together to form 1 big image (cover a whole well)
  • Identify object in this big image, as discussed above

Then you can directly use that option “Calculate the per-image mean value” in ExportToSpreadsheet

If you already had a complex pipeline with many fluorescent channels, I suggest to build this kind of per-well/per-image analysis in a separate pipeline

1 Like

That is a good solution ^^^.

I"m not sure it’s possible but you could perhaps try ExportToDatabase with the MySQLite option… once you’ve got the data in MySQLite you might be able to export as a spreadsheet from there?

Minh’s solution is interesting! However I would be concerned about tiling artifacts along the boundaries.

Anne’s solution (ExportToDatabase > sqlite database > then export as CSV) would be my preferred solution. There are many free tools to open sqlite databases (search the forum for some suggestions) and then export the data as a CSV. It is a fairly straightforward process.

Finally, there is an old Github Issue to make ExportToSpreadsheet aggregate by per-well values too!

It is “closed” for future consideration, but I would be happy to see this one resurrected!


Hi everyone!

Thanks for the input again! I will give a try to the work arounds.

But I agree with David, it would be great if the closed Github Issue would be reopened and reconsidered! :slight_smile:


Good suggestions above by Minh and Anne. I agree with David that Minh’s suggestion could create artifacts to inner borders of stitched image so that could be a bit dangerous (depending on the alignment/overlap of individual images).

I have once modified ExportToSpreadsheet module to export also well level statistics. It is a hack, for instance all calculations are done in the module whereas image based aggregates are calculated in the measurement class. Anyways, it works. We are nowadays mostly stitching images already before running CP pipeline so currently we don’t typically use the hacked version of the module but once in a while we get data from older microscopes which cannot be stitched.

The hacked version is written for CP 2.1.2 development version. If someone is interested, I could add it to 2.2.0 stable version and share the code in GitHub.

The request for this hack came from this post Exporting "per well" data and I know that the sender at least uses it :slight_smile:


Another, though slightly more annoying thing to do, would be to export individual spreadsheets per well (taking advantage of an extracted piece of “well” metadata and the fact that you can set output folders by metadata variables). You’d then have to re-concatenate all your data in some sort of script (or with something like cytominer-database), but it likely wouldn’t be much more painful than whatever you’re doing in your downstream analysis work anyway.

If it works @Anne_Carpenter 's and @David_Logan 's is likely the easiest suggestion, though IIRC you can’t make per-well means/medians etc with an SQLite database, only with a MySQL database, so I don’t think that’s actually an option.

I agree it’s a useful feature to have though; we have a major overhaul intended in the (hopefully) near future for the Export modules, and that’s definitely one we’re planning on adding if at all possible!

@Lassi_Paavolainen that is super! Thanks for providing the plugin.