topic Re: Column mean from certain rows - then populate mean in a new column in Discussions

Column mean from certain rows - then populate mean in a new column

nickicmc5 — Wed, 30 Oct 2024 16:47:45 GMT

I'd like to calculate a column mean with the condition that it only includes the rows with a certain ID. In this case, I only want to average the data for the Blanks. I'd like the output to populate down a new column. The result will look like this:

I know how to achieve this with the Table>Summary application, but I don't know how to do it in my data table + have it populate down. Thank you for the help!

Re: Column mean from certain rows - then populate mean in a new column

jthi — Wed, 30 Oct 2024 17:02:52 GMT

You can use Summary and use Join/Update to add it to your original table. Or you can use formula. One such formula is

Col Mean(If(:Sample ID == "Blank", :Data, .))

Re: Column mean from certain rows - then populate mean in a new column

nickicmc5 — Wed, 30 Oct 2024 17:45:48 GMT

Thanks for your reply. I need the calculated column mean to be populated into a new column. Here is a script I've been going off of based on playing around with chatgpt, but it's not doing the trick.

// Assuming your data table is already open
dt = Current Data Table();

// Calculate the mean of the "Data" column where "Sample ID" is "Blank"
meanValue = Col Mean(If(:Sample ID == "Blank", :Data, Missing()));

// Create a new column for the Blank Average
dt << New Column("Blank Average", Numeric, Continuous);

// Populate the "Blank Average" column with the calculated mean for all rows
dt << Set Values(:Blank Average, meanValue);

Re: Column mean from certain rows - then populate mean in a new column

jthi — Wed, 30 Oct 2024 17:56:55 GMT

You can just add what I provided you with as a new formula column and JMP can very easily script that for you after that.

It would look something like this

Names Default To Here(1);

Current Data Table() << New Column("Blank Average", Numeric, Continuous, Formula(
	Col Mean(If(:Sample ID == "Blank", :Data, .))
));

Re: Column mean from certain rows - then populate mean in a new column

hogi — Wed, 30 Oct 2024 20:16:03 GMT

Please take care - Col aggregations like

Col Mean(If(:Sample ID == "Blank", :Data, .))

are very (!!!) slow due to the character comparison in the if expression.

This is ok for a few thousand rows, but for tables with millions of rows, this will take a lot of time.

Better: add a dummy column that maps the character column (:Sample ID) to numeric values and use the numeric column inside the if expression -> orders of magnitude faster!