Using compartments to input CSVs with custom names

[GaiaGerbaka]

I'm using CellProfiler to create Nuclei, Cells and Cytoplasm profiles, but I created each of these three CSVs using different CellProfiler methods. For example I have CSVs names Nuclei_Cellpose3, Cells_Cellpose3 and Cytoplasm_Cellpose3. I was able to run the cytotable convert() method for the Nuclei.csv, Cells.csv and Cytoplasm.csv and it outputted the expected parquet, I used this code snippet

convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable",
    dest_datatype="parquet",
    concat=True,
    preset="cellprofiler_csv",
    no_sign_request=True,
)

In order to do the same for Nuclei_Cellpose3.csv, Cells_Cellpose3.csv and Cytoplasm_Cellpose3.csv, I wrote the following snippet

convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_cellpose",
    dest_datatype="parquet",
    compartments=["Nuclei_Cellpose3", "Cells_Cellpose3", "Cytoplasm_Cellpose3"],
    join=True,
    joins="ImageNumber,ObjectNumber",
    page_keys={
        'join': 'ImageNumber', 
        'Cells_Cellpose3': 'ObjectNumber', 
        'Nuclei_Cellpose3': 'ObjectNumber', 
        'Cytoplasm_Cellpose3': 'ObjectNumber'
    },
    preset=None
)

but I get this error

CytoTableException: No matching key found in page_keys for source_group_name: all_files.csv. Please include a pagination key based on a column name from the table.

Then I tried adding a key for all_files.csv by modifying the code above, and it threw an SQL error, so I changed the join value as follows":

convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_cellpose",
    dest_datatype="parquet",
    compartments=["Nuclei_Cellpose3", "Cells_Cellpose3","Cytoplasm_Cellpose3"],
    join=True,
    joins="ImageNumber,ObjectNumber",
    page_keys={
        'image': 'ImageNumber', 
        'Cells_Cellpose3': 'ObjectNumber', 
        'Nuclei_Cellpose3': 'ObjectNumber', 
        'Cytoplasm_Cellpose3': 'ObjectNumber', 
        'join': 'Cytoplasm_Number_Object_Number',
        'all_files.csv': 'ImageNumber'
        },
    chunk_size = 10000,
    sort_output=True,
    preset=None
)

This time the code run without error but never finishes and does not create any file (tried running for 1hr, while default took 3m)

[GaiaGerbaka]

I also tried reading the presets.config["cellprofiler_csv"] in order to make my own preset to accomodate for my file names. Here is the preset I made

cellpose3 = {
    'CONFIG_SOURCE_VERSION': {'cellpose3': 'v1.0'}, 
    'CONFIG_NAMES_COMPARTMENTS': ('Nuclei_Cellpose3', 'Cells_Cellpose3', 'Cytoplasm_Cellpose3'), 
    'CONFIG_NAMES_METADATA': ('image',), 
    'CONFIG_IDENTIFYING_COLUMNS': ('ImageNumber', 'ObjectNumber', 'Metadata_Well', 'Metadata_Plate', 'Parent_Cells', 'Parent_Nuclei'), 
    'CONFIG_PAGE_KEYS': {
        'image': 'ImageNumber', 
        'Cells_Cellpose3': 'ObjectNumber', 
        'Nuclei_Cellpose3': 'ObjectNumber', 
        'Cytoplasm_Cellpose3': 'ObjectNumber', 
        'join': 'Cytoplasm_Number_Object_Number'
        }, 
    'CONFIG_CHUNK_SIZE': 1000, 
    'CONFIG_JOINS': """            
        SELECT
            image.Metadata_ImageNumber,
            COLUMNS('Image_FileName_.*'),
            Cytoplasm_Cellpose3.* EXCLUDE (Metadata_ImageNumber),
            Cells_Cellpose3.* EXCLUDE (Metadata_ImageNumber, Metadata_ObjectNumber),
            Nuclei_Cellpose3.* EXCLUDE (Metadata_ImageNumber, Metadata_ObjectNumber),
        FROM
            read_parquet('Cytoplasm_Cellpose3.parquet') AS cytoplasm
        LEFT JOIN read_parquet('Cells_Cellpose3.parquet') AS cells USING (Metadata_ImageNumber)
        LEFT JOIN read_parquet('Nuclei_Cellpose3.parquet') AS nuclei USING (Metadata_ImageNumber)
        LEFT JOIN read_parquet('image.parquet') AS image USING (Metadata_ImageNumber)
        WHERE
            Cells_Cellpose3.Metadata_ObjectNumber = cytoplasm.Metadata_Cytoplasm_Parent_Cells
            AND Nuclei_Cellpose3.Metadata_ObjectNumber = cytoplasm.Metadata_Cytoplasm_Parent_Nuclei           
        """
    }
presets.config["cellpose_csv"] = cellpose3
convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_cellpose",
    dest_datatype="parquet",
    concat=True,
    join=False, 
    preset="cellpose_csv",
    sort_output=True
)

This again runs without bugs but does not finish or create any files (tried for an hour).

[d33bs]

Thanks @GaiaGerbaka for reaching out about this and for providing some of the code you used!

In looking through what you shared I noticed that the second and third code snippet examples included the line: joins="ImageNumber,ObjectNumber", which is invalid SQL. The joins parameter expects a SQL statement which can be used to join together the data.

I'm not as sure about the third snippet and what might be happening. Could I ask for a subset of the data you're working with (perhaps just two rows per file would be enough)? This might help troubleshoot what's happening.

When you mention it runs without bugs but does not create any files could I also confirm that the process completes?

[GaiaGerbaka]

Thanks for getting back to me @d33bs!

Ah I see why joins="ImageNumber,ObjectNumber" wouldn't work, I tried passing it SQL commands similar to the cellprofiler preset

"""
        SELECT
            image.Metadata_ImageNumber,
            COLUMNS('Image_FileName_.*'),
            cytoplasm.* EXCLUDE (Metadata_ImageNumber),
            cells.* EXCLUDE (Metadata_ImageNumber, Metadata_ObjectNumber),
            nuclei.* EXCLUDE (Metadata_ImageNumber, Metadata_ObjectNumber)
        FROM
            read_parquet('Cytoplasm_Cellpose3.parquet') AS cytoplasm
        LEFT JOIN read_parquet('Cells_Cellpose3.parquet') AS cells USING (Metadata_ImageNumber)
        LEFT JOIN read_parquet('Nuclei_Cellpose3.parquet') AS nuclei USING (Metadata_ImageNumber)
        LEFT JOIN read_parquet('image.parquet') AS image USING (Metadata_ImageNumber)
        WHERE
            cells.Metadata_ObjectNumber = cytoplasm.Metadata_Cytoplasm_Parent_Cells
            AND nuclei.Metadata_ObjectNumber = cytoplasm.Metadata_Cytoplasm_Parent_Nuclei
    """

But this run also does not complete.

The files I'm using are in this s3 buket "s3://cellpainting-gallery/cpg0043-segmentation/broad/workspace/analysis/2025_08_21_Batch1/BR00116991/analysis/BR00116991-A01-1/" and I'm trying to join Nuclei_Cellpose3.csv , Cells_Cellpose3.csv and Cytoplasm_Cellpose3.csv

Regarding your last question, I meant the code does not raise any errors but it also does not complete the run and no files are created.

Let me know if there's any other information I can provide, thanks for the help!

[d33bs]

Hi @GaiaGerbaka - thanks for the added details here!

I worked with the dataset and found that there were a few configuration adjustments needed to get things to work in your code. The following is a link to a gist of a notebook which I ran on Google Colab: https://gist.github.com/d33bs/72114a30e5a301a6bdc1227037d66099 . Note: this streams the data directly from the cloud instead of using a local system path. You could still fall back to a local path for the source_path if you'd like, depending on your constraints.

Please let me know if you have any questions or if you're still seeing any issues!

[ErinWeisbart]

Thanks for pointing us in the right direction @d33bs !

As a naive user of CytoTable, I would really like to see the option supported to use a preset (e.g. cellprofiler_csv) and pass in just custom column names without needing to pass in custom joins, page_keys, etc. It seems like those could easily be inferred in-script without the biologist needing to perform the find and replace in the join and passing it in?

e.g. in this experiment we measured in a single pipeline different CellProfiler outputs that are exactly the same columns/relationships as standard Cells.csv, Cytoplasm.csv, and Nuclei.csv just with different names. Naively, I would expect I could do something like this:

# for my actual default compartments of Cells.csv, Cytoplasm.csv, and Nuclei.csv
convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_default.parquet",
    dest_datatype="parquet",
    concat=True,
    preset="cellprofiler_csv",
    no_sign_request=True,
)
# for my first variation
convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_default.parquet",
    dest_datatype="parquet",
    concat=True,
    preset="cellprofiler_csv",
    compartments=("Cells_Cellpose3", "Nuclei_Cellpose3", "Cytoplasm_Cellpose3"),
    no_sign_request=True,
)
# for my second variation
convert(
    source_path=source_path,
    source_datatype="csv",
    dest_path="cytotable_default.parquet",
    dest_datatype="parquet",
    concat=True,
    preset="cellprofiler_csv",
    compartments=("Cells_CellposeSAM", "Nuclei_CellposeSAM", "Cytoplasm_CellposeSAM"),
    no_sign_request=True,
)

[d33bs]

Thanks @ErinWeisbart ! I love your idea and want to see if we can implement this. I believe we might do something similar for SQLite table names currently so it follows that we'd do this for filename / compartment references.