Choose Language Hide Translation Bar
Highlighted
Sciguy_1
Level III

Counting rows based on multi condition match

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 AColumn BColumn CColumn D (Counted Tally)
7ZS4
7ZS4
1AP

3

1AP3
1AP3
7ZS4
5EM2
5EM2
7ZS4

  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

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:Screen Shot 2019-06-20 at 1.34.14 PM.png

 

 

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:

Screen Shot 2019-06-20 at 1.39.37 PM.png

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. Screen Shot 2019-06-20 at 1.42.23 PM.png

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:drag.gif

 

 

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!

 

@julian 

View solution in original post

14 REPLIES 14
Highlighted
julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

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:Screen Shot 2019-06-20 at 1.34.14 PM.png

 

 

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:

Screen Shot 2019-06-20 at 1.39.37 PM.png

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. Screen Shot 2019-06-20 at 1.42.23 PM.png

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:drag.gif

 

 

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!

 

@julian 

View solution in original post

Highlighted
Sciguy_1
Level III

Re: Counting rows based on multi condition match

Thank you Julian the column approach addresses my application perfectly.
Highlighted
Sciguy_1
Level III

Re: Counting rows based on multi condition match

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. 

Highlighted
txnelson
Super User

Re: Counting rows based on multi condition match

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

summarize(byGroup=by(:Total in Group));
n items(byGroup)
Jim
Highlighted
julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

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;

unique.png

 

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

@julian 

Highlighted
Sciguy_1
Level III

Re: Counting rows based on multi condition match

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.

 

TUBEJARBUCKETSOGNo. Times Tube occurs per JAR X BUCKET X SOGNo. unique times Jar occurs per bucket X SOG combination 
ASXABEWDNH5001

3

2

 
CDVABEWDNH500132 
KNLABEWDNH500132 
NHSBENWDNH500122 

SDC

BENWDNH500122 
MKD

CAT

DEFC500441 
KDNCATDEFC500441 
AMDCATDEFC500441 
CMDCATDEFC500441 
MDNDOGDEFC500722 
VNFDOGDEFC500722 
RKE

EEL

DEFC500752 
LKDEELDEFC500752 
KDMEELDEFC500752 
WLDEELDEFC500752 
KMDEELDEFC500752 
FDLFLYSDER500862 
SMKFLYSDER500862 
KJMFLYSDER500862 
ALDFLYSDER500862 
NDEFLYSDER500862 
IEJFLYSDER500862 
DFRABESDER500832 
TREABESDER500832 
NFEABESDER500832 
Highlighted
julian
Community Manager Community Manager

Re: Counting rows based on multi condition match

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. 

Highlighted
Sciguy_1
Level III

Re: Counting rows based on multi condition match

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.

Highlighted
Sciguy_1
Level III

Re: Counting rows based on multi condition match

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.

Article Labels

    There are no labels assigned to this post.