topic how to use jsl to calculate standard deviation by ID in Discussions

how to use jsl to calculate standard deviation by ID

emmablue — Wed, 19 Jun 2024 18:41:27 GMT

Hi, I have a table similar to this, and I wanted to select IDs where there are more than one entry and calculate standard deviation of the result grouped by ID. For example, in this case, I need to select ID 12345 and calculate standard deviation of the first 3 results.

I know I can do this by tabulate, but my actual table is very big, more than 1 million rows, tabulate by ID and calculate standard deviation turns out to be extremely time consuming. Most IDs in my actual table have only one row. Is there anyway I can do something like this and then calculate standard deviation for the remaining results group by ID? Thank you for your suggestion!

select where(n rows(grouping columns (:ID))>1)

ID	result
12345	0.3
12345	0.4
12345	0.2
23456	0.5
34567	0.2

Re: how to use jsl to calculate standard deviation by ID

jthi — Thu, 20 Jun 2024 05:30:04 GMT

Select your grouping column (ID)

Then select the column you wish to perform the calculation on and pick Standard Deviation (note the Grouping by id)

Re: how to use jsl to calculate standard deviation by ID

jthi — Thu, 20 Jun 2024 05:29:46 GMT

Few more options

Create summary table

After you have the summary table, you can click on the stddev column and hold mouse down for some time. This allows you to "pick" the column and drop it to your original table

You could also have used Update with the summary table

You can create similar table using Tabulate (note that id is nominal)

Then from red triangle, make into data table

you can then use Update (or join) to get the results back to your table.

There are also many scripting options, here is one using Summarize()

Names Default To Here(1);

dt = Current Data Table();

Summarize(ids = by(:ID), stddevs = Std dev(:result));
show(ids, stddevs); 
// ids = {"12345", "23456", "34567"}; // note that these are strings even if they were numbers originally
// stddevs = [0.1, ., .];