topic Re: count unique in Discussions

count unique

joann — Sat, 10 Jun 2023 23:26:28 GMT

Hi, I have a table with transaction data like the below.

transactionID, individualID, product

1, a, apple

2, a, orange

3, b, apple

4, b, banana

5, c, orange

I want to do in tabulate, not using script, to count how many unique individualID by product.

Does anyone know how to do this?

I can do summary table using individualID and product first, and then do a tabulate on it to get the uniqueID count by product, but this is 2 steps. Wondering is there a way we can do it in tabulate just one step cause i constantly need to do this for many different groups?

Thank you!

Re: count unique

brady_brady — Thu, 25 Feb 2021 04:11:29 GMT

I am unsure of how to use Tabulate to do this, but if you're open to using another platform, this is really easy to do using the JMP Query Builder:

1) Select Tables > JMP Query Builder from the main menu.

2) In the ensuing window, click the "Build Query" button, located in the window's lower right corner.

3) In the ensuing window:

Double-click on the product column, casting it into the "included columns" pane.
Double-click on the individual ID column, casting it into the "included columns" pane.
Right-click on the t1.product column, now in the "included columns" pane, and select "Order By".
From the drop-down menu in the t1.individualID column's Aggregation field in the "included columns" pane, select Count DISTINCT.
At the bottom of the window, click "Run Query", producing a table with what I believe you are after. (?)

Cheers,

Brady

Re: count unique

jthi — Thu, 25 Feb 2021 06:59:28 GMT

It seems like Tabulate platform doesn't behave nicely with Character datatype for analysis. You could change individualID to numeric (recode or create formula to new column) and then use tabulate:

Re: count unique

ih — Thu, 25 Feb 2021 13:37:44 GMT

One more option is to calculate that in your original data table using a column with the col sum() function with a 'by' variable, but referencing the number 1 instead of a column, which turns it into a 'count' function. If you need it in the tabulate or table format, you could then tabulate using an average or median:

Re: count unique

joann — Thu, 25 Feb 2021 20:21:39 GMT

Thank you so much Brady!

Never used this query builder and it's super powerful!

I just tried taking 2 source tables and did query, and it's so smart that it matched the individual ID on its own and returned the correct unique counts.

2 questions to the query builder:

1. I could see 3 green triangles as attached after I ran the above query. To re-run the same query with updated data, which triangles should I use?

2. If I have many different queries, how do I store them in one place, and then later when I got the latest data, to apply the queries on it again?

Re: count unique

joann — Thu, 25 Feb 2021 20:25:42 GMT

Thank you! This is good and quick way as well!

Re: count unique

joann — Thu, 25 Feb 2021 20:27:18 GMT

Thank you! I just tried and it works! never thought I can use col sum to achieve this. Smart!

Re: count unique

brady_brady — Thu, 25 Feb 2021 21:29:28 GMT

I could see 3 green triangles as attached after I ran the above query. To re-run the same query with updated data, which triangles should I use?

It depends... The "Source" script produces a new table when run, while the "Update From Database" script refreshes the present table (i.e., without creating a new table.)

Note: A handy feature of JMP data tables is that if you name a script "On Open", it will run whenever the JMP data table containing it is opened. So, you can rename the "Update from Database" script to "On Open" to assure that you're always starting off with updated results. Of course, this script can still be run whenever you wish, by clicking the green arrow.

If I have many different queries, how do I store them in one place, and then later when I got the latest data, to apply the queries on it again?

There are several ways to address this, I'll offer a couple of the easiest.

- You could use the "On Open" trick mentioned above if you have simple queries with no derivative dependencies. (i.e., no single query needs to be run prior to any other, to obtain correct results for its table)

- You could create a "master" script, by copying + pasting each of the scripts into it. The order may or may not matter, depending on your situation, and you may need to insert controls into the script to make sure results from prior queries are finished before proceeding.

I've not had to do much of this in practice, so it might be worthwhile to ask the community this question in a new discussion, to see what are actually the most popular approaches "in the field", and why.

Cheers,

Brady

Re: count unique

joann — Mon, 01 Mar 2021 23:05:56 GMT

Thank you so much Brady! These are really great knowledge and I'll practice each of them! Really appreciate the help!

Re: count unique

ih — Mon, 01 Mar 2021 23:18:03 GMT

Yet another option: you could also make a summary column right in the tabulate function:

Names default to here(1);

dt = Open("$Sample_data/big class.jmp");

dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				Transform Column( "Age Count", Formula( Col Sum( 1, :age ) ) )
			),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :age ) )
	)
);

Re: count unique

WJ — Sun, 17 Jul 2022 06:33:51 GMT

Thank you very much. This is the most efficient way to generate table results for counting unique values and proportions.

Re: count unique

hogi — Mon, 23 Jan 2023 22:21:04 GMT

Is there a shortcut for N categories or count unique in the right click New Formula Column menu?
I guess there is one (as it is needed so often), but I couldn't find it - what's the name?

NB: not all of the replies here referred to N categories or count unique - some are more like N rows per unique entry.

Re: count unique

txnelson — Tue, 24 Jan 2023 01:11:21 GMT

~~There is not a right click solution to this issue, as far as I know.~~ The old school way that I approach this is:

Summarize( bygroup = by( :age ) );
UniqueCount = N Items( bygroup );

Re: count unique

hogi — Tue, 24 Jan 2023 17:49:08 GMT

Thanks Jim :)

For my application I need a function which uses the Group By option of the right click New formula Column menu to calculate and distribute the respective values - similar to Col Number (Count), but just counting unique/distinct entries.

There is nothing like

N categories(column, bygroups)

- or a an option just count unique values for the Col Number function?

Big-Class application case:
A user want to calculate the percentage of age groups, with mean height > 60, separately for male and female. Then N categories could be a nice way to do this:

PercentTallAgeGroups = N Categories(if(Col Mean(:height,:age, :sex) > 60,:age, .), :sex) / N Categories(:age, :sex)*100

More reasonable application cases:
- ratio of defects per measured wafer for several lots (for a data set with chip-fine data)

- ratio of cities with >0.1% Covid mortality for several countries

Workaround:

Generate a Summary Table with the needed by groups and merge it back into the main table.

Re: count unique

hogi — Tue, 27 Jun 2023 13:09:03 GMT

If other users are interested as well to have N Categories not just in the Tables/Summary Platform, but also via JSL ...
- here is the wish:

Re: count unique

hogi — Sun, 29 Oct 2023 22:32:37 GMT