nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

213943_en-US

liliconcom

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 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

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.

@julian

Re: Counting rows based on multi condition match

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

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);

</CODE>      </LINGO-BODY><LINGO-SUB id="lingo-sub-214889" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214889" slang="en-US" mode="NONE">Hi <A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/14945" target="_blank">@Sciguy_1</A> ,   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:   <img src="https://us.v-cdn.net/6038925/uploads/images/18030i629C729C275487D5/Screen%20Shot%202019-06-26%20at%205.20.49%20PM.png" role="button" title="Screen Shot 2019-06-26 at 5.20.49 PM.png" alt="Screen Shot 2019-06-26 at 5.20.49 PM.png" />   <img src="https://us.v-cdn.net/6038925/uploads/images/18031i3CBC6C15082459EE/Screen%20Shot%202019-06-26%20at%205.20.54%20PM.png" role="button" title="Screen Shot 2019-06-26 at 5.20.54 PM.png" alt="Screen Shot 2019-06-26 at 5.20.54 PM.png" />   Hope this gets your further along, <A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/2026" target="_blank">@julian</A> </LINGO-BODY><LINGO-SUB id="lingo-sub-214377" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214377" slang="en-US" mode="NONE">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.</LINGO-BODY><LINGO-SUB id="lingo-sub-214361" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214361" slang="en-US" mode="NONE">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.</LINGO-BODY><LINGO-SUB id="lingo-sub-214360" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214360" slang="en-US" mode="NONE">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.</LINGO-BODY><LINGO-SUB id="lingo-sub-214152" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214152" slang="en-US" mode="NONE">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. </LINGO-BODY><LINGO-SUB id="lingo-sub-214149" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214149" slang="en-US" mode="NONE">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. <TABLE border="1"><TBODY><TR><TD>TUBE</TD><TD>JAR</TD><TD>BUCKET</TD><TD>SOG</TD><TD>No. Times Tube occurs per JAR X BUCKET X SOG</TD><TD>No. unique times Jar occurs per bucket X SOG combination</TD><TD> </TD></TR><TR><TD>ASX</TD><TD>ABE</TD><TD>WDNH</TD><TD>5001</TD><TD>3</TD><TD>2</TD><TD> </TD></TR><TR><TD>CDV</TD><TD>ABE</TD><TD>WDNH</TD><TD>5001</TD><TD>3</TD><TD>2</TD><TD> </TD></TR><TR><TD>KNL</TD><TD>ABE</TD><TD>WDNH</TD><TD>5001</TD><TD>3</TD><TD>2</TD><TD> </TD></TR><TR><TD>NHS</TD><TD>BEN</TD><TD>WDNH</TD><TD>5001</TD><TD>2</TD><TD>2</TD><TD> </TD></TR><TR><TD>SDC</TD><TD>BEN</TD><TD>WDNH</TD><TD>5001</TD><TD>2</TD><TD>2</TD><TD> </TD></TR><TR><TD>MKD</TD><TD>CAT</TD><TD>DEFC</TD><TD>5004</TD><TD>4</TD><TD>1</TD><TD> </TD></TR><TR><TD>KDN</TD><TD>CAT</TD><TD>DEFC</TD><TD>5004</TD><TD>4</TD><TD>1</TD><TD> </TD></TR><TR><TD>AMD</TD><TD>CAT</TD><TD>DEFC</TD><TD>5004</TD><TD>4</TD><TD>1</TD><TD> </TD></TR><TR><TD>CMD</TD><TD>CAT</TD><TD>DEFC</TD><TD>5004</TD><TD>4</TD><TD>1</TD><TD> </TD></TR><TR><TD>MDN</TD><TD>DOG</TD><TD>DEFC</TD><TD>5007</TD><TD>2</TD><TD>2</TD><TD> </TD></TR><TR><TD>VNF</TD><TD>DOG</TD><TD>DEFC</TD><TD>5007</TD><TD>2</TD><TD>2</TD><TD> </TD></TR><TR><TD>RKE</TD><TD>EEL</TD><TD>DEFC</TD><TD>5007</TD><TD>5</TD><TD>2</TD><TD> </TD></TR><TR><TD>LKD</TD><TD>EEL</TD><TD>DEFC</TD><TD>5007</TD><TD>5</TD><TD>2</TD><TD> </TD></TR><TR><TD>KDM</TD><TD>EEL</TD><TD>DEFC</TD><TD>5007</TD><TD>5</TD><TD>2</TD><TD> </TD></TR><TR><TD>WLD</TD><TD>EEL</TD><TD>DEFC</TD><TD>5007</TD><TD>5</TD><TD>2</TD><TD> </TD></TR><TR><TD>KMD</TD><TD>EEL</TD><TD>DEFC</TD><TD>5007</TD><TD>5</TD><TD>2</TD><TD> </TD></TR><TR><TD>FDL</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>SMK</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>KJM</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>ALD</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>NDE</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>IEJ</TD><TD>FLY</TD><TD>SDER</TD><TD>5008</TD><TD>6</TD><TD>2</TD><TD> </TD></TR><TR><TD>DFR</TD><TD>ABE</TD><TD>SDER</TD><TD>5008</TD><TD>3</TD><TD>2</TD><TD> </TD></TR><TR><TD>TRE</TD><TD>ABE</TD><TD>SDER</TD><TD>5008</TD><TD>3</TD><TD>2</TD><TD> </TD></TR><TR><TD>NFE</TD><TD>ABE</TD><TD>SDER</TD><TD>5008</TD><TD>3</TD><TD>2</TD><TD> </TD></TR></TBODY></TABLE></LINGO-BODY><LINGO-SUB id="lingo-sub-214145" slang="en-US" mode="NONE">Re: Counting rows based on multi condition match</LINGO-SUB><LINGO-BODY id="lingo-body-214145" slang="en-US" mode="NONE">Hi <A href="https://community.jmp.com/t5/user/viewprofilepage/user-id/14945" target="_blank">@Sciguy_1</A>,   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:   <PRE><CODE class=" language-jsl">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?

@julian

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)

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.

Re: Counting rows based on multi condition match

Thank you Julian the column approach addresses my application perfectly.

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:

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" )
)