how to use jsl to calculate standard deviation by IDHi, 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 |
Advanced Statistical ModelingAutomation and ScriptingRe: how to use jsl to calculate standard deviation by IDSelect 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 IDFew 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, ., .];
