Looking for help on normalizing data from a specific value in another column

Question

I need to create a formula that is called relative Lumen flux @ 22.5A.  I have one column that has Lumen flux @ various different currents.  I want to create a new column that  normalizes the lumen flux @ different currents to the flux @ 22.5A.  So the formula would be like  φv/φv(22.5A) .  Please help on this.

wendy_o · Answer

To normalize data points at one current relative to that of the 22.5A distribution, you'll start by calculating the mean and standard deviation of Flux for each current group (including the 22.5A group).

Assuming that the Flux values are normally distributed, taking z-scores is then the easiest method of normalizing.

Find the Z-score of a value relative to its current group (e.g. Group A). This gives you the number of standard deviations your value is from its group's mean (all relative to that current group.)

Then take this Z-score and back calculate the x-value relative to the 22.5A group's mean and sigma. The "x-relative" formula shown below calculates these two translations in one step.

DaveLee · Answer

I run into situations like this and am attaching a snippet of code that should work. I made some assumptions about Column Names, which you'll have to adjust to your particle nomenclature.

//Manually Select and subset on that current density in mA/cm^2;

dt=current data table();

/* Extract a list of unique ages */

Summarize( a = By( :Name("Current A") ) );  //Assuming name of Current column is called "Current A";

Insert Into( a, "<Select Current (A) of Interest>", 1 );

a;

/* Create a modal dialog */

New Window( "Example",

<<Modal,

H List Box(

// Upon selecting a current, the script should then determine the appropriate Lumen Flux for that Current

Panel Box( "Select a Current:",

cb1 = Combo Box(

a,

<<SetFunction(

Function( {this},

selection = this << Get Selected();

r = Stack1 << Get Rows Where( :Name("Current A") == Num( selection ) );

)

),

)

);

sel=num(selection);

//Now determine the Lumen Flux at the Current of interest

//I'm assuming a name for your column of Lumen Flux

lumenflux=Column(Lumen Flux)[sel];

dt << new column("Relative Flux", numeric, continuous, formula(:Name("Relative Flux to Std") / lumenflux));