topic Re: Counting rows based on multi condition match in Discussions

Counting rows based on multi condition match

Sciguy_1 — Thu, 20 Jun 2019 17:06:47 GMT

I want to count rows in my dataset if 3 columns (A-B-C) match. I want to enter the value in a new column (D). Here is an example. Thank you for your assistance!

Column A	Column B	Column C	Column D (Counted Tally)
7	Z	S	4
7	Z	S	4
1	A	P	3
1	A	P	3
1	A	P	3
7	Z	S	4
5	E	M	2
5	E	M	2
7	Z	S	4

Re: Counting rows based on multi condition match

jules — Thu, 20 Jun 2019 17:47:02 GMT

Hi @Sciguy_1

There are a few ways you could go about this. I think a formula column approach is relatively straightforward, with two columns, first, one that counts the occurrences of a match incrementally, and then a second column that finds the max value of that count by group, which is what you're calling a counted tally here. Here's what that table looks like:

And here are the column formulas:

Counting in groups by the columns used for a match:

Col Cumulative Sum( 1, :Column A, :Column B, :Column C )

and the Max of that count column, also grouped by the columns used for a match

Col Maximum( :Count In Groups, :Column A, :Column B, :Column C )

Table is attached for your reference.

Alternatively, if you wish to create a table that lists unique rows only once, with the number of times they matched, you can use Tables > Summary. Enter in your columns as Group, and hit okay. Here's what that setup looks like:

This will return a summary table that shows the unique combinations of values in those three columns, along with a column showing the number of occurrences in the data table.

Once you have this table, you can select, and then drag the N Rows column from the summary table directly into your original table, and JMP will handle all the Matching for you to update the original table with those values. Here's what that looks like:

Finally, the script for making the table is also saved as a Source script to the summary table:

Data Table( "Example.jmp" ) << Summary(
	Group( :Column A, :Column B, :Column C ),
	Freq( "None" ), Weight( "None" )
)

I hope this helps!

@jules

Re: Counting rows based on multi condition match

Sciguy_1 — Fri, 21 Jun 2019 15:54:12 GMT

Thank you Julian the column approach addresses my application perfectly.

Re: Counting rows based on multi condition match

Sciguy_1 — Fri, 21 Jun 2019 19:06:33 GMT

To go one step further- Lets say I wanted to add another column that showed the number of times a combination occurred within a subset. So in the example, in the "total in group" column there are 4-"4", 3-"3", and 2-"2". If all the rows (1 thru 9) represented the entire subset, the answer in this case would be 3 because a "4" occurred, a "3" occurred, and a "2" occurred. So in the new column "total in subset" every row would have a 3. I cant figure out how to get figures summed in that manner.

Re: Counting rows based on multi condition match

txnelson — Fri, 21 Jun 2019 19:24:41 GMT

create a new column and use the following formula for the new column

summarize(byGroup=by(:Total in Group));
n items(byGroup)

Re: Counting rows based on multi condition match

jules — Fri, 21 Jun 2019 19:26:36 GMT

Hi @Sciguy_1,

I believe I understand what you're asking, but let me reiterate back to make sure. You'd like a new column that has a count of the number of unique combinations, regardless of how many times those combinations occurred? In the subset you provided, there are 3 unique combinations of the three columns, and as you pointed out, one of the combinations occurred 4 times, another 3 times, and another 2 times. Another way to say this is how many unique rows do you have, as defined by the first three columns. If this is indeed what you're after, the following column formula will do, which essentially counts up the number of times a 1 occurs in the "Count In Groups" Columns, i.e., the number of unique groups:

As Constant(
	uniqueRowCount = Summation(
		i = 1,
		N Row(),
		If( :Count In Groups[i] == 1,
			1,
			0
		)
	)
);
uniqueRowCount;

I've attached the new table here as well. Is this what you were looking to do?

@jules

Re: Counting rows based on multi condition match

Sciguy_1 — Fri, 21 Jun 2019 20:40:48 GMT

That is exactly what I am wanting to do, you explained it quite well. But upon applying the formula I am not getting the correct values. Let me supply a new example just to make sure we are on the same wavelength. This is a very large and quite dynamic data set. Hope this better explains and does not confuse the issue. Thank you.

TUBE	JAR	BUCKET	SOG	No. Times Tube occurs per JAR X BUCKET X SOG	No. unique times Jar occurs per bucket X SOG combination
ASX	ABE	WDNH	5001	3	2
CDV	ABE	WDNH	5001	3	2
KNL	ABE	WDNH	5001	3	2
NHS	BEN	WDNH	5001	2	2
SDC	BEN	WDNH	5001	2	2
MKD	CAT	DEFC	5004	4	1
KDN	CAT	DEFC	5004	4	1
AMD	CAT	DEFC	5004	4	1
CMD	CAT	DEFC	5004	4	1
MDN	DOG	DEFC	5007	2	2
VNF	DOG	DEFC	5007	2	2
RKE	EEL	DEFC	5007	5	2
LKD	EEL	DEFC	5007	5	2
KDM	EEL	DEFC	5007	5	2
WLD	EEL	DEFC	5007	5	2
KMD	EEL	DEFC	5007	5	2
FDL	FLY	SDER	5008	6	2
SMK	FLY	SDER	5008	6	2
KJM	FLY	SDER	5008	6	2
ALD	FLY	SDER	5008	6	2
NDE	FLY	SDER	5008	6	2
IEJ	FLY	SDER	5008	6	2
DFR	ABE	SDER	5008	3	2
TRE	ABE	SDER	5008	3	2
NFE	ABE	SDER	5008	3	2

Re: Counting rows based on multi condition match

jules — Fri, 21 Jun 2019 20:51:58 GMT

It appears that every row in your table is unique, so I would expect "No. Times Tube occurs per JAR X BUCKET X SOG" to be 1 for the entire table. Am I misunderstanding this format?

Also - attaching your JMP data table, or a subset of it, with your column formulas intact will be much more helpful to diagnose what is happening, rather than pasting the table into the body of the message.

Re: Counting rows based on multi condition match

Sciguy_1 — Mon, 24 Jun 2019 16:55:49 GMT

You are correct, every row is unique. Here is a subset of the real dataset. I created a couple columns representing what I am trying to accompolish. The column 'number of times BUCKET appears per SOG' I manually populated.The NULL value can be treated as its own BUCKET. The second column needs to show the number of times J1 appears per: SOG X BUCKET combination. Hope this makes things easier thank you for your assistance.

Re: Counting rows based on multi condition match

Sciguy_1 — Mon, 24 Jun 2019 16:55:55 GMT

Re: Counting rows based on multi condition match

Sciguy_1 — Mon, 24 Jun 2019 17:20:25 GMT

I see I made an error. The header on the last column jar_id equals column J1. Would also like to know the number of times T1 occurs per J1 X SOG X BUCKET combination. Thank you.

Re: Counting rows based on multi condition match

jules — Wed, 26 Jun 2019 21:22:35 GMT

Hi @Sciguy_1 ,

Here's a table that accomplishes what I think you're after, using the formulas I showed before but adapted for each combination. My calculation for "# times BUCKET appears per: SOG (Correct Answer)" does not match the one you said you manually created, but I am pretty sure what I've done here is correct since it squares with my own manual counting. Can you check and confirm I am not missing something though?

You'll notice that you need a pair of columns for each of these counts, one that counts incrementally in each grouping, and another that finds the total. I've hidden the incremental count columns in the table, but note that you must have these. You can generalize this to really any number of combinations, you're just adjusting the formulas as shown below to include the right columns as your by groups:

Hope this gets your further along,

@jules

Re: Counting rows based on multi condition match

ms — Thu, 27 Jun 2019 01:03:22 GMT

An alternative to using two columns to arrive at the counts would be to count the rows of each combination with the ColNumber() function.

For example:

Col Number(Row(), :J1, :SOG, :BUCKET);

Re: Counting rows based on multi condition match

Sciguy_1 — Thu, 27 Jun 2019 13:29:26 GMT

I am still having issues getting this to work. The 2 equation version Julian suggested- the first equation is giving me all 1's so not sure what I am doing incorrectly. The single equation method does give me the total number of times a particular bucket appears per SOG. The other part I am still trying to get is the number of times I have to grab a bucket. For example if I grab a bucket and take 4 samples or grab it again and take 19 samples- in each case I only grabbed the bucket once. From my data example EXULU appears a total of 4 times in the 621 SOG. I am wanting to create a tally that would count those 4 times as 1 time. EH8QK appears a total of 19 times in the 621 SOG but I want to count those all as 1 as well. I hope this better explains the second half of the problem. Thank you!

Re: Counting rows based on multi condition match

jules — Fri, 28 Jun 2019 16:10:18 GMT

I'm glad the formula @ms posted works for you! I'm curious why mine is failing on your actual table, but perhaps our time is better spent on your other question rather than diagnosing what went wrong with my inferior two-column method!

Can I ask what problem creating that tally is solving? It seems as though you'll just end up with a column of 1s by doing this, since matter the number of occurrences of the pattern you're counting it as 1. I don't think I'm fully grasping what you're wanting to do or what it's helping you accomplish. I think if I knew the end goal I might be able to suggest a simpler overall method than a series of formula columns.

@jules