923593_en-US

liliconcom

Normalize a column

TLDR: How do I normalize a column to itself?

 

See attached. I have:

• Column A = 0,1,2,3. Maybe think of this as like an ID number
• Column B. Think of this as a timestamp
• Column C. This is the data

Now I want column D to have the data in Column C, ONLY when B=1.

As you can see in the attached file, I have manually copy-pasted the yellow cells from C into D. Pasted over and over.

Therefore when I do D/C, I'm basically normalizing the whole of column C, to these special yellow values. This is the reason I'm doing all this, is to find D/C. So a value of 0.9 in this column means C is lower than the yellow C's, and 1.1 means it's higher than the yellow C's. 

 

So, long story short, what is the formula that goes in D, to be able to get the yellow values?

Automation and ScriptingBasic Data Analysis and ModelingRe: Normalize a column

edit

Sorry, I need to pay more attention.  You weren't asking how to get D/C.

 

Select C and D, then right click.  New Formula Column > Combine > Ratio (reverse order)

 

Re: Normalize a column

Sorry if I'm still misunderstanding, but if you want D to be missing when B is not 1 and D to have the values of C when B is one, try this formula.

 

Choose( :B, :C, . )

Re: Normalize a column

Thanks for your answer. Not exactly. I simply want a formula in column D, that will reproduce those numbers.

 

That formula will take the yellow cells in C, and apply it to the entire column D. So that now the entire column D is the repeated yellow cells as shown. (Presumably in the formula somewhere, we would hardcode B=1.)

 

Probably I confused things by manually typing in D in the example. I manually typed in those numbers, but I want a formula that will populate D for me.

Re: Normalize a column

Thanks for your answer, but here is a comparison of your answer, and what I want which is just a formula for D. So compare the final column and D and you'll see they are not the same.

Re: Normalize a column

Isn'1 the formula just

If( :B == 1, 1, :D / :C )

 

Re: Normalize a column

I'm sure there is a way to script a solution, but I would instead propose that switching from your Tall data format to a Wide data format makes more sense in this case. When using JMP formulas, it is always easiest when all the terms needed for that calculation are in the same row. (Sometimes this is impossible, then clever scripting is needed. But in this case, it is possible.)

My suggestion: Split the data table so that it is in a Wide format, with a separate Column for each "B". Then normalizing (diving by C) becomes easy. 

You can recode the column names to make them a bit more readable: 

After adding the new formula columns, you end up with: 

Would this work for you? I attached the table for your reference. 

Re: Normalize a column

Use a grouped Col aggregation like col Median() to define the values of column D as 

Col median(if (:B==1,:C), :A)

You can also skip Column D and directly calculate :C/Denominator.
There is an AddIn in the Marketplace which does such Normalization automatically.

Re: Normalize a columnNo, I want to find the formula for D. In my example I just manually typed in the numbers for D but I want a formulaRe: Normalize a column

what is wrong with

Col median(if (:B==1,:C), :A)

?

Re: Normalize a column

Hogi, This is the solution! Thanks! A little weird that I have to use a "median" function since there is no median in sight, but I see what it's doing. Thanks! I'll also check out the Marketplace Addin

Re: Normalize a column

Thanks! This does work, but is a bit unweildy for a human to view when I have a large dataset. Hogi's answer is simpler but thanks for the tip anyway!