Possible to specify column order in MeasurementExporter in QuPath?

Is it possible to control to column order for the measurements that get exported with Measurement Exporter? I’ve tried to group the exports by type rather than channel as you see below (so that all the means are next to each other, and then all the std devs), but the order seems fixed.

The ability to define the column order would save me some very computationally expensive machinations in Excel on very large files.

def channelNum =  getCurrentServer().getMetadata().sizeC //get number of channels
def Channel = getCurrentServer().getMetadata().getChannels().collect { c -> c.name } //get channel names

def columnsToInclude = ["Image", "Name", "Cell: Area"]
for (int i=0; i<channelNum; i++) {
   columnsToInclude.add("Cell: "+Channel[i]+" mean"); 
 }
for (int i=0; i<channelNum; i++) {
   columnsToInclude.add("Cell: "+Channel[i]+" std dev"); 
}
//print columnsToInclude

def exportType = PathCellObject.class

// Choose your *full* output path
def outputpath = buildFilePath(PROJECT_BASE_DIR, getProjectEntry().getImageName()+'.csv')

String outputPath = outputpath.minus('Image:')

def outputFile = new File(outputPath)
def entry = getProjectEntry()
entryList = []
entryList << getProjectEntry()
print "Exporting data for file:" + entryList
// Create the measurementExporter and start the export
def exporter  = new MeasurementExporter()
                  .imageList(entryList)            // Images from which measurements will be exported
                  .separator(separator)                 // Character that separates values
                  .includeOnlyColumns(columnsToInclude as String[]) // Columns are case-sensitive
                  .exportType(exportType)               // Type of objects to export
                  .exportMeasurements(outputFile)        // Start the export process

print "Export complete!"

Hi @kteplitz ,

I don’t think it’s possible in an easy way unfortunately…
But you could run this:

import java.nio.charset.StandardCharsets
import com.google.common.collect.LinkedListMultimap;
import qupath.lib.gui.commands.SummaryMeasurementTableCommand;
import qupath.lib.gui.measure.ObservableMeasurementTableData;
import qupath.lib.images.ImageData;
import qupath.lib.objects.PathCellObject;
import qupath.lib.projects.ProjectImageEntry;

def imageCols = new HashMap<ProjectImageEntry<?>, String[]>()
def nImageEntries = new HashMap<ProjectImageEntry<?>, Integer>()
def allColumns = new ArrayList<String>()
def valueMap = LinkedListMultimap.create()
def pattern = "(?=(?:[^\"]*\"[^\"]*\")*[^\"]*\$)"
def entry = getProjectEntry()
def type = PathCellObject.class
def separator = ", "
def excludeColumns = []
def includeOnlyColumns = []
FileOutputStream stream = new FileOutputStream(new File("Path/to/your/file.csv"))    // Change this to your path!!
def columnOrder = [2, 1, 0]    // Change this to the order you'd like!! 

try {
    ImageData<?> imageData = entry.readImageData()
    ObservableMeasurementTableData model = new ObservableMeasurementTableData()
    model.setImageData(imageData, imageData == null ? Collections.emptyList() : imageData.getHierarchy().getObjects(null, type))
    List<String> data = SummaryMeasurementTableCommand.getTableModelStrings(model, separator, excludeColumns)

    // Get header
    String[] header
    String headerString = data.get(0)
    if (headerString.chars().filter(e -> e == '"').count() > 1)
        header = headerString.split(separator == "\t" ? "\\" + separator : separator + pattern , -1)
    else
        header = headerString.split(separator)

    imageCols.put(entry, header)
    nImageEntries.put(entry, data.size()-1)

    for (String col: header) {
        if (!allColumns.contains(col)  && !excludeColumns.contains(col))
            allColumns.add(col)
    }

    // To keep the same column order, just delete non-relevant columns
    if (!includeOnlyColumns.isEmpty())
        allColumns.removeIf(n -> !includeOnlyColumns.contains(n))

    for (int i = 1; i < data.size(); i++) {
        String[] row
        String rowString = data.get(i)

        // Check if some values in the row are escaped
        if (rowString.chars().filter(e -> e == '"').count() > 1)
            row = rowString.split(separator == "\t" ? "\\" + separator : separator + pattern , -1)
        else
            row = rowString.split(separator);

        // Put value in map
        for (int elem = 0; elem < row.length; elem++) {
            if (allColumns.contains(header[elem]))
                valueMap.put(header[elem], row[elem])
        }
    }

} catch (Exception e) {
    print e.getLocalizedMessage()
}

try (PrintWriter writer = new PrintWriter(new OutputStreamWriter(stream, StandardCharsets.UTF_8))){
    def allColumnsInOrder = []
    for (int nCol: columnOrder) {
        allColumnsInOrder.add(allColumns.get(nCol));
    }

    writer.write(String.join(separator, allColumnsInOrder))
    writer.write(System.lineSeparator())

    Iterator[] its = new Iterator[allColumnsInOrder.size()]
    for (int col: columnOrder) {
        its[col] = valueMap.get(allColumns.get(col)).iterator()
    }


    for (int nObject = 0; nObject < nImageEntries.get(entry); nObject++) {
        for (int nCol: columnOrder) {
            if (Arrays.stream(imageCols.get(entry)).anyMatch(allColumnsInOrder.get(nCol)::equals)) {
                String val = (String)its[nCol].next()
                print val
                // NaN values -> blank
                if (val == "NaN")
                    val = ""
                writer.write(val)
            }
            if (nCol < allColumns.size()-1)
                writer.write(separator)
        }
        writer.write(System.lineSeparator())
    }
} catch (Exception e) {
    print "Error writing to file: " + e.getLocalizedMessage()
}

stream.flush()
stream.close()

Which is basically some sort of copy paste from what the MeasurementExporter currently does. It’s truly not what I’d call great code, and I really mean it since it’s copy pasting a big chunk from the current code and tweaking a few things, but I think it’ll do if you really don’t have other options to swap column order… Hopefully it’ll run ok for you!

PS : Don’t forget to change the column order and the output path!

EDIT: You’ll notice also that I’ve change the column names to import. In this case, it just get all the columns I think, but you can change that in the same way you were previously doing!

3 Likes

@melvingelbard Thanks. I will try this. In the meantime, I’ve just broken up the measurements into multiple output files. It helps both with the ordering and with the file size issues I was having.