Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Tabulating discrete ranges in a matrix



I have a large matrix dataset in the following format in a workflow:




CelI-ID  |     X43g     h53l       44hj      fit43       lo8       …..#16434

atabb    |    0.454    0.976    1.342    4.312    2.765

aabba   |

gtgaa    |

gccag   |

agcta    |

...#759  |



I would like to tabulate specific occurrence's of discrete gene numbers for multiple genes that occur in a subset of cells. For example: how many of the cells express gene "fit43" between 1-1.5, 1.5-2, 2-2.5 etc, and gene "lo8" between 1-1.5, 1.5-2, 2-2.5.

I am having some trouble finding a good solution that can move through the data and provide the output without having to manually do this gene by gene and range by range. Thanks for the help!

Inactive User
Not applicable

Transpose the genes into rows and then have the unified range applied and an indicator/type column to identify the range that was matched. Then you can cross tab it back as either an indicator or the range value. Done.


That helped, im trying to get to another solution as well, so let me add on another dimension I am having trouble with. I have the data matrix (assuming we only work with one at a time) with 3000-5000 cells (rows), each cell has 16,500 genes (columns). We need to separate out specific cells (rows) into multiple batches based on a single gene expression range (one batch has an expression range of 1-1.5, another 1.5-2, 2-2.5 etc). Typically we end up with around 200-500 cells(rows) between all the bathes (each still having 16,500 columns). I then need to analyze every batch the same way as discussed in the previous post. Right now, I have to manually rerun each batch, and each gene within each batch using a multi-row formula as shown in the uploaded workflow. 


I would like to figure out how to do everything in one go: Batch Matrix based on a gene set of ranges --> count number of rows (cells) that fall within a set of ranges for multiple genes --> output data.