Exporting per-object classifications

cellprofiler-analyst

#1

[copied from an email exchange]

We have two investigators that are relying heavily on CPA 2.0 for counting
the incidence of a given subjective set of phenotypes, but they were
wondering also if there was a way to use CPA classifier to generate the
original object measurements file with an added column indicating the
classification type (either in the Mysql/SQLite tables or the Excel files…
Doesn’t matter to us which…)

Example Application: In cross sections of murine spinal cord, we have been
using CPA classifier to identify axonal cross sections of myelinated axons
and have set up classifier to identify only axonal cross sections of
sufficient “good” quality to perform measurements of the “G ratio”, a
primitive, but widely used measurement for the extent of myelination, which
looks at the ratio of axonal diameter to (axonal diameter + myelination
diameter). We can get CPA classifier to easily identify those axonal cross
sections of sufficient quality for measurement using a wide range of
measurements, but the # of rules generated (by the student’s drag and drop
efforts) is in excess of 40 rules (cross section quality varies a lot from
animal to animal from these groups-which is apparently normal within the
field), which also then requires re-running of CP to obtain the G-ratio
measurements.

In other words, the counts of positively recognized objects, do not
necessarily reflect the final desired measurement, so the data must be
re-run each time the group runs a new animal (new artifacts of
preparations)using the rule set. Since the data is actually contained in the
original CP output files, we wanted to know if it was possible to link the
original measurements to the CPA classification results directly.

It would be great if CPA could gather and plot such statistics based on
classification type.

(I suspect that it can/does, but we’ve been having problems figuring out to
which file it is that CPA writes its classifications results. After some
limited dabbling I am convinced that something in the properties file for
CPA is mis-configured and is causing us to have this problem… -but I’m not
sure.)

Am I missing something fundamental in this?

Thanks Again…
David Moore


Grouping by classified objects
Issue with saving per object classification
Classification results for a whole population
Using classifier results in the scatter plot
#2

Hi David,

Adding the following field to your database will tell Classifier to write your object classifications out to the table named “XXX”.

class_table = XXX

Be warned that this table will be completely erased and written over each time you run “score all”, so make sure that 1) the table name you choose doesn’t already exist, and 2) if you wish to save multiple results, make sure to rename the old tables. Note: It WILL warn you before overwriting an existing table so you can tell it not to.

The resultant table columns will be: ImageNumber, ObjectNumber, ClassName, ClassNumber … this way you can link the results back to the existing per_object table, and either look at the class name (e.g. “positive”, “negative”) or simply a number (1,2,3…).

The manual (available from the website) lists all valid properties fields that can be used to configure CPA. I hope this is the functionality you’re looking for. If not, let me know and I’ll try to help you out.

Cheers!
Adam


#3

David was further wondering how he could take the information in the class_table and use it to remove all “negative” objects from his per object table while using SQLite. Here’s my solution:

After having used CPA to Classify the cross section, the instructions to biologists will be something like this:

  • Open SQLite Database Browser

  • Open the .db file associated with your screen (this is printed in the CPA console after you open to your properties file).

  • In the “Database Structure” view, verify that the per_image, per_object, and class_table (whatever you called it) are there.

  • Go to the “Execute SQL” view and execute the following statements (replacing XXX for your class table name, and “positive” with whatever class name you used for the):

[code]CREATE TABLE tt AS SELECT o.*, c.class AS “class”, c.class_number AS “class_number” FROM per_object AS o, XXX AS c WHERE o.ImageNumber=c.ImageNumber AND o.ObjectNumber=c.ObjectNumber AND WHERE class=“positive”;

CREATE UNIQUE INDEX obkey ON per_object (ImageNumber, ObjectNumber);

DROP TABLE per_object;

ALTER TABLE tt RENAME TO per_object;[/code]

  • Finally, click the save button, and restart CPA… it will no longer recognize the existence of any of the non-“positive” objects.

This works by creating a new table called tt that is populated with all columns from the per_object table + the class and class_number columns from the class_table, AND only copies those objects who match the “positive” class. The last two lines of SQL code dump the existing object table and rename tt to “per_object.”

-Adam


#4

I have put the class_table = per_class in my CPA properties file.

However Classifier seems to only generate the class_table when I select from the Classifier menu “write temporary table for plate viewer”. I presume this is correct?


#5

Hrm, this shouldn’t be necessary in the most recent version of CPA. In fact the menu option you mention doesn’t exist anymore. Could you refresh my memory what version you’re using and on what OS?


#6

[quote=“afraser”]Hi David,

Adding the following field to your database will tell Classifier to write your object classifications out to the table named “XXX”.

class_table = XXX

Be warned that this table will be completely erased and written over each time you run “score all”, so make sure that 1) the table name you choose doesn’t already exist, and 2) if you wish to save multiple results, make sure to rename the old tables. Note: It WILL warn you before overwriting an existing table so you can tell it not to.

The resultant table columns will be: ImageNumber, ObjectNumber, ClassName, ClassNumber … this way you can link the results back to the existing per_object table, and either look at the class name (e.g. “positive”, “negative”) or simply a number (1,2,3…).

The manual (available from the website) lists all valid properties fields that can be used to configure CPA. I hope this is the functionality you’re looking for. If not, let me know and I’ll try to help you out.

Cheers!
Adam[/quote]

Hi, I know this thread is old but I’d like to note with the newest release it seems that you can’t do this with SQLite (or at least it doesn’t create the table XXX when it opens the properties file). I saw this in the properties file itself:

[code]

======== Output Per-Object Classes ========

OPTIONAL

Here you can specify a MySQL table in your Database where you would like

Classifier to write out class information for each object in the

object_table

class_table = Per_Class[/code]

So am I correct in thinking that the “class_table” isn’t possible for SQLite?

I’d also like to eventually filter out the “negative” objects using CPA but perhaps I’ll start a new thread on that.


#7

Hi Scott,

Two quick questions:

  1. How are you checking to see if the table exists?
  2. When are you checking?

-Adam


#8

[quote=“afraser”]Hi Scott,

Two quick questions:

  1. How are you checking to see if the table exists?
  2. When are you checking?

-Adam[/quote]

Well, I’ll answer the questions, but I also have to report that I found a workaround.

  1. I’m checking by a) going to the TableViewer and selecting Load Table from Database and b) Using SQLite Manager 0.6.8 an extension for Firefox (which is a nice alternative to SQLite Database Browser 2.0 that you mentioned)
  2. I’m checking after doing “Score All” in the Classifier

As for the workaround:

  1. I put “class_table = Per_Class” in the properties file as directed but
  2. I also opened the DB from SQLite Manager and created the table manually (see screenshot)
  3. Then, when I run “Score All” it does give the warning “This will over-write the Per_Class table bla bla”
  4. And, when I use the TableViewer, the Per_Class table is there with all the Classes assigned to each object (which I then export as a .csv)


#9

Well I’m glad you found a workaround, although I’m unfortunately perplexed as to why it’s not writing the class_table for you unless it already exists. I tried it myself and checked the code path and everything looks good. I must be missing something.

-Adam

PS: that sqlite manager extension sounds pretty sweet!


#10

Well, to let you know I’m running the latest versions (as of 6/26/11) of CPA and CP on 64-bit windows 7 ultimate N with SP1.


#11

[quote=“afraser”]Well I’m glad you found a workaround, although I’m unfortunately perplexed as to why it’s not writing the class_table for you unless it already exists. I tried it myself and checked the code path and everything looks good. I must be missing something.

-Adam

PS: that sqlite manager extension sounds pretty sweet![/quote]

CPAnalyst is now creating the table automagically. Your code is good. It must have been something with SQLite manager accessing the DB at the same time CPAnalyst was… Problem Exists between Chair and Computer :stuck_out_tongue:


#12

Ah excellent! Glad you were able to figure that out!