This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

- JMP User Community
- :
- Discussions
- :
- How to apply a script on subsets of a datatable

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.

Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Created:
Mar 22, 2023 04:11 AM
| Last Modified: Jun 10, 2023 07:59 PM
(439 views)

Hello,

In my datatable I have 6 data subsets (distinguished one another by Column X where each subset has a different letter "a", "b", "c", "d", "e" and "f"). How could I apply a script to all 6 subsets independently and get the 6 results of my script in one column?

Thank you in advance for your support!

Best

Claire

------

Script currently applied to entire datatable:

```
dt = Current Data Table();
dt << New Column("sumtempj",
Numeric,
Continuous,
<<Set Each Value(
As Constant(
ts = :"Rounded TempJ" << get values;
f_row = Min(Loc(ts, 121));
t_idx = Index(f_row, f_row + 359);
sumtempj = Sum(ts[t_idx]) - (360 * 121);
);
If(
Row() == f_row, sumtempj,
, .
);
)
);
```

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Just based on the script you have here, I would add index column, create subsets based on the unique letters, run formula on each of those and then update data back to original table

-Jarmo

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Just based on the script you have here, I would add index column, create subsets based on the unique letters, run formula on each of those and then update data back to original table

-Jarmo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Created:
Mar 22, 2023 09:04 AM
| Last Modified: Mar 22, 2023 09:21 AM
(422 views)
| Posted in reply to message from jthi 03-22-2023

Hi Jarmo,

Thanks for the tip!

I'm only a beginner in JSL and now have this script (see below). The result column "sumtempj" appears in each sub datatable produced but there is no result in the column. Can you see the mistake?

Best

Claire

Script:

```
dt = Current Data Table();
// Perform the subsets
listDT = dt << Subset(
By( :BBI and SteriRun ),
All rows,
Selected columns only( 0 ),
columns( :RoundedTempJ, :BBI and SteriRun, :Sterilisation duration )
);
// Loop across all new data tables and run sumtempj
For( i = 1, i <= N Items( listDT ), i++,
listDT[i] << New Column( "sumtempj",
Numeric,
Continuous,
<<Set Each Value(
As Constant(
ts = :Rounded TempJ << get values;
f_row = Min( Loc( ts, 121 ) );
t_idx = Index( f_row, f_row + 359 );
sumtempj = Sum( ts[t_idx] ) - (360 * 121);
);
If(
Row() == f_row, sumtempj,
, .
);
)
)
);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

There might be other ways of doing this, but as I don't know your data I cannot really suggest anything. Here is one solution which uses subsets which could work

```
Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
// add indexing to original data
dt << New Column("IDX", Numeric, Continuous, << Set Each Value(Row()));
// add data column to table as empty
new_col = dt << New Column("sumtempj", Numeric, Continuous);
col_name = new_col << get name;
// get unique groups to uniq_vals
Summarize(dt, uniq_vals_str = by(:age));
// in this case we have to convert uniq_vals back to numeric values BUT it isn't always necessary
// (summarize changes them to strings)
uniq_vals = Transform Each({uniq_val_str}, uniq_vals_str, num(uniq_val_str));
// loop over those unique values
For Each({uniq_val}, uniq_vals,
// create subset
uniq_val_rows = dt << Get Rows Where(:age == uniq_val);
dt_temp = dt << Subset(rows(uniq_val_rows), output table(char(uniq_val)), invisible);
// perform calculation
Column(dt_temp, col_name) << Set Each Value(
Col Max(:height);
);
// update values
dt << Update(
With(dt_temp),
Match Columns(:IDX = :IDX)
);
// wait is here for demo purposes
wait(1);
close(dt_temp, no save);
);
dt << Delete Columns("idx");
```

-Jarmo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

This is a bit too advanced for me, but thank you for the inputs! I managed to find a way to get the result without too much manual handling and for now it's good enough.

Best

Claire

- © 2024 JMP Statistical Discovery LLC. All Rights Reserved.
- Terms of Use
- Privacy Statement
- About JMP
- JMP Software
- JMP User Community
- Contact