nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How can I initialize my data wrt a fixed value?

Matteo_Rotti

Hi, I would like to implement a formula to “initialize” some data in our tables. Specifically, I would like to subtract from all elements in a column the value of the first repetition of the test run ( the repetition info is contained in another column).
I attach the table I am working on. The formula would be integrated in Column “X_35”. The idea is to assign to each test the value contained in X_22 obtained at the first repetition (Column X_21) of the analysis, filtering however by test condition, in particular I am interested in separating by other columns (e.g. X_5, X_7 and X_8). After that I would apply the difference for each row, obtaining the offsets for each point from the first repetition. I would like to find a solution using a column formula if possible, avoiding entering values manually.

I am working with JMP 17

Thanks

all data Anonymized.jmp

Accepted answers

txnelson

A column formula can be used to create what you want

As Constant( dt = Current Data Table() );
currentType = :Type;
theInitialValue = :Value[(dt << get rows where( :Type == CurrentType & :Repetition == 1 ))[1]];
:Value - theInitialValue;

The method works, however, given a data table of 55,000+ the formula is not very efficient.

A more efficient method is to use a simple script to create a new table that contains just the initial values, and then merge the table back into the original table, where the matching will allow for the replication of the initial values to all of the matching records

names default to here(1);
dt=
New Table( "Example2",
	Add Rows( 8 ),
	New Column( "Type",
		Character,
		Set Values( {"A", "A", "A", "A", "B", "B", "B", "B"} )
	),
	New Column( "Repetition",
		Numeric,
		Set Values( [1, 2, 3, 4, 1, 2, 3, 4] )
	),
	New Column( "Value",
		Numeric,
		Set Values( [4, 10, 8, 7, 6, 9, 5, 10] )
	)
);

// Create a table with the initial values
dt << select where(:Repetition == 1 );
initialDT = dt << subset( selected rows(1), columns({:Type,:Value}));

// Rename column Value to Initial Value
initialDT:Value << set name("Initial Value");

// Merge the values back to the original table
dt << Update(
	With( initialDT ) ,
	Match Columns( :Type = :Type )
);

// Create a column that contains the difference
dt << New Column( "Difference",
	formula( :Value - :Initial Value )
);

All comments

txnelson

Welcome to the Community.

I am a bit confused as to what you are asking for. What I am see in your data, is that

Column X_31 defines each of the 8 levels of the data, with each of these groupings of rows for column X_21 having subgrouping values of 1 up to 980.

What I think you want to do, is for a given row, that does not have the X_21 value of 1, to look at its values for X_5, X_7 and X_8 and then find the initial value for X the matching values for the column X_22 and subtract that value from the current rows value for column X_22.

I am not at all confident in my assessment. Upon examining the data, there are far more combinations for column X_5, X_7 and X_8 for each grouping of X_31 initial values.

What you are currently showing in your provided data table, where the values of X_35 are copied from Column X_22 if the value of X_21 is equal to 1, is an easy thing to do in JMP. Understanding what you want for the other rows is confusing. If you could provide what would be your expected result for row 7 and row 14 and the steps taken to come up with your result would be very helpful.

Matteo_Rotti

Hi, thanks for the quick response. Sorry for my poor explanation but it is more complex to explain than, I hope, to solve.

I will try with a schematic table.

The goal is to get the “Initial Value” column, which has, for each measure, the value corresponding to the first repetition depending on the type of test, identified here as A or B. Then, with a simple difference I get the value shifted from the first experiment starting from 0.

The formula you saw in other message is my attempt to write the “Initial Value” column using an if condition with the number of repetitions, my problem is what to write in the other condition to get the same value for a different number of repetitions.

Thank you again

Example_Table.jpg

txnelson

A column formula can be used to create what you want

As Constant( dt = Current Data Table() );
currentType = :Type;
theInitialValue = :Value[(dt << get rows where( :Type == CurrentType & :Repetition == 1 ))[1]];
:Value - theInitialValue;

The method works, however, given a data table of 55,000+ the formula is not very efficient.

names default to here(1);
dt=
New Table( "Example2",
	Add Rows( 8 ),
	New Column( "Type",
		Character,
		Set Values( {"A", "A", "A", "A", "B", "B", "B", "B"} )
	),
	New Column( "Repetition",
		Numeric,
		Set Values( [1, 2, 3, 4, 1, 2, 3, 4] )
	),
	New Column( "Value",
		Numeric,
		Set Values( [4, 10, 8, 7, 6, 9, 5, 10] )
	)
);

// Create a table with the initial values
dt << select where(:Repetition == 1 );
initialDT = dt << subset( selected rows(1), columns({:Type,:Value}));

// Rename column Value to Initial Value
initialDT:Value << set name("Initial Value");

// Merge the values back to the original table
dt << Update(
	With( initialDT ) ,
	Match Columns( :Type = :Type )
);

// Create a column that contains the difference
dt << New Column( "Difference",
	formula( :Value - :Initial Value )
);

hogi

If you do such normalize steps more often, you might consider Normalization GUI .

Besides subtracting the "first" value it provides many more options like "last" , N-1, "median", "mean" (the latter ones even accounting for row exclusion).

You can decide if you want to calculate the values - or if you prefer a formula which automatically updates the values when the input column gets updated.

Matteo_Rotti

Thanks!
I am now trying to implement the code with all the variables I need, at the moment everything is working.