topic Re: Creating New Rows based on Averages from Previous Rows? in Discussions

Creating New Rows based on Averages from Previous Rows?

WebArmadillo869 — Tue, 20 Jun 2023 20:49:23 GMT

Hello,

Perhaps this is a quick fix, but I have a large data-set in JMP with many categories, one of which is a subgroup that each entry belongs to. I'm looking to take averages of the data collected across the entire subgroup and use it to create a whole new row. Is this possible using scripting? I am hoping for an optimized workflow where all it takes is a button to do this across different files.

EX:

Bucket	Age	Height (cm)
1	30	100
2	35	120
1	30	90
2	40	150

A script would provide me with new rows that average stats by buckets 1 and 2:

Bucket	Age	Height (cm)
1 (Average)	30	95
2 (Average)	37.5	135

If this is easy enough (and scalable), I am also looking for a way to generalize these averages, i.e average across BOTH buckets 1-2 or from buckets 1-4 instead of just one. Thanks so much

Re: Creating New Rows based on Averages from Previous Rows?

Jeff_Perkinson — Tue, 20 Jun 2023 21:07:04 GMT

Hi @WebArmadillo869, welcome to JMP!

I suspect that you're thinking of JMP like a spreadsheet, and that's not a good thing. Read for the reasons you should not think of JMP like that.

Instead, for what you're looking for, creating averages for groups, you should use Tables->Summary and you'll get lots of flexibility for summarizing your data into a new table. You can see more about using Summary in

Alternatively, you can use Analyze->Tabulate to create a report table with the averages. See more about Tabulate in

Re: Creating New Rows based on Averages from Previous Rows?

jthi — Tue, 20 Jun 2023 21:11:11 GMT

Are you creating these new rows to same data table? If you are, are these averages used for reporting purposes (JMP datatable isn't a spreadsheet)? Or could these new rows be in a new data table in which case Summary table could work (you could also concatenate it back to the original table). If you want to have them in same table take a look at Summarize() and Add Rows() besides the Summary table and concatenation.

For the generalization easiest option is most likely to create new column which would have "common" bucket across the other buckets and you would then use that for grouping.