ExportToDatabase Module

We have run into a problem using the ExportToDatabase Module using Cell Profiler 2.0, on revision 9777. It appears the data in the Per Object CSV files are out of order relative to the variables listed in the .SQL file. Objects in the exported Per Object CSV file appear to be in alphabetical order. For example, when the expected order is for Nuclei, Cells, Cytoplasm, the columns are exported in order of Cells, Cytoplasm, and Nuclei.

Then, using the spreadsheet files, we can re-arrange them and get it to work, but it’s a time-consuming manual process, prone to human error. So, we were able to diagnose the problem this way, but not able to fix it. Attached for your reference are the original and fixed Per Object CSV files along with the SQL file with the variables.

Chuck
SQL__SETUP.SQL.zip (1.4 KB)
SQL_1_8_Object (fixed).csv (801 KB)
SQL_1_8_Object (orig).csv (798 KB)

Thanks for reporting this. I’ll add this to the known bugs list.

For a workaround easier than editing the CSVs, you might consider using separate object tables, and either creating a VIEW in MySQL to join them (we do this for some experiments with a very large number of measurements), or just to simplify your fixup process.

The ordering of columns issue was fixed in revision 9816 by leek. Below is the relevant patch:

svn diff -r9814:9816 svnrepos.broadinstitute.org/Cel … llProfiler

Index: cellprofiler/modules/exporttodatabase.py

— cellprofiler/modules/exporttodatabase.py (revision 9814)
+++ cellprofiler/modules/exporttodatabase.py (revision 9816)
@@ -638,6 +638,10 @@
column_defs = self.get_pipeline_measurement_columns(pipeline,
image_set_list)
obnames = set([c[0] for c in column_defs])

  •    #
    
  •    # In alphabetical order
    
  •    #
    
  •    obnames = sorted(obnames)
       return  obname for obname in obnames
                if not self.ignore_object(obname, True) and
                obname not in (cpmeas.IMAGE, cpmeas.EXPERIMENT, 
    

@@ -1006,17 +1010,20 @@
if file_object_name == cpmeas.OBJECT:
# the object number
object_row.append(j+1)

  •                for object_name, feature, coltype in columns:
    
  •                    if object_name not in object_list:
    
  •                        continue
    
  •                    values = measurements.get_measurement(object_name,
    
  •                                                          feature, i)
    
  •                    if (values is None or len(values) <= j or
    
  •                        np.isnan(values[j])):
    
  •                        value = "NULL"
    
  •                    else:
    
  •                        value = values[j]
    
  •                    object_row.append(value)
    
  •                #
    
  •                # Write out in same order as in the column definition
    
  •                for object_name in object_names:
    
  •                    for object_name_to_check, feature, coltype in columns:
    
  •                        if object_name_to_check != object_name:
    
  •                            continue
    
  •                        values = measurements.get_measurement(object_name,
    
  •                                                              feature, i)
    
  •                        if (values is None or len(values) <= j or
    
  •                            np.isnan(values[j])):
    
  •                            value = "NULL"
    
  •                        else:
    
  •                            value = values[j]
    
  •                        object_row.append(value)
                   csv_writer.writerow(object_row)
           fid.close()