topic Re: Subset with added formula column - almost there in Discussions

Subset with added formula column - almost there

wendy1123 — Sat, 10 Jun 2023 23:30:44 GMT

I am trying to create a script to subset data which also adds a column with a formula in it. I am close (sort of). I can create the subset, with the new column, but the new column only has dots in it.

Here is what I have so far:

Data Table( "Melt Curve Raw Data" ) << Subset(
	By( :Well Position ),
	All rows,
	Selected columns only( 0 ),
	columns( :Temperature, :Fluorescence,  New Column( "Normalized Fluorescence", formula(
	(:Fluorescence - Col Min(:Fluorescence )) / (Col Max(:Fluorescence )
	-Col Min(:Fluorescence )))
	))
	);

After searching, I saw something about an eval command, but as a beginner I can't figure out how to add it in so it works.

**Ideally I would also be able to add the new column directly after the Temperature column instead of at the end

Thank you in advance for any advice!!

Re: Subset with added formula column - almost there

txnelson — Wed, 26 May 2021 12:04:13 GMT

New Column() is not permitted in the column specification for Subset(). At least I can not find such in the documentation, nor does it work in a very simple subset example, with it included. Therefore, I have modified your script to create the column in the original data table, then subset the tables and then go back and delete it from the original table.

Data Table( "Melt Curve Raw Data" ) <<
New Column( "Normalized Fluorescence",
	formula(
		(:Fluorescence - Col Min( :Fluorescence )) / (
		Col Max( :Fluorescence ) - Col Min( :Fluorescence ))
	)
);
	
Data Table( "Melt Curve Raw Data" ) <<
Subset(
	By( :Well Position ),
	All rows,
	Selected columns only( 0 ),
	columns( :Temperature, :Fluorescence, :Normalized Fluorescence )
);
	
Data Table( "Melt Curve Raw Data" ) <<
delete columns( :Normalized Fluorescence );

Re: Subset with added formula column - almost there

wendy1123 — Wed, 26 May 2021 23:39:01 GMT

Hi Jim! Thank you for your advice. I wish I could use your method, but unfortunately, when I add the column before doing the subsets, the new column uses the previous max & mins so the calculations are wrong.

Adding the new column after the subset works for me, not sure why, and it is correctly titled, but the formula doesn't "calculate" which is why I thought it might be the "eval" thing I've seen. When I go into the new columns, it has "suppress eval" on but I don't know how to fix that without going in by hand to all of the subsets and correcting it.

I attached a screenshot of what it looks like to me. The column appears, but with the dots. When I go into column info, it has the "suppress eval" checked, and unchecking it solves the error, but it's not ideal to have to go into all of the subsets individually to uncheck the box. I tried adding different codes to the script to turn off suppress eval, but none of them work so far.

Re: Subset with added formula column - almost there

txnelson — Wed, 26 May 2021 21:02:12 GMT

Here are 2 solutions to the issue. The first one changes the calculation to use the min and max values for the particular Well Position that is being evaluated.

Data Table( "Melt Curve Raw Data" ) << New Column( "Normalized Fluorescence",
	formula(
		(:Fluorescence - Col Min( :Fluorescence, :Well Position )) / (
		Col Max( :Fluorescence, :Well Position )
		-Col Min( :Fluorescence, :Well Position ))
	)
);
	
Data Table( "Melt Curve Raw Data" ) << Subset(
	By( :Well Position ),
	All rows,
	Selected columns only( 0 ),
	columns( :Temperature, :Fluorescence, :Normalized Fluorescence )
);
	
Data Table( "Melt Curve Raw Data" ) << delete columns( :Normalized Fluorescence );

The second one creates the subsetted data tables, and then goes back and add the new column for each of the new data tables produced

new tables = Data Table( "Melt Curve Raw Data" ) <<
Subset(
	By( :Well Position ),
	All rows,
	Selected columns only( 0 ),
	columns( :Temperature, :Fluorescence, :Normalized Fluorescence )
);
	
For( i = 1, i <= N Items( new tables ), i++,
	new tables[i] << New Column( "Normalized Fluorescence",
		formula(
			(:Fluorescence - Col Min( :Fluorescence, :Well Position )) / (
			Col Max( :Fluorescence, :Well Position )
			-Col Min( :Fluorescence, :Well Position ))
		)
	)
);

Re: Subset with added formula column - almost there

wendy1123 — Wed, 26 May 2021 21:39:32 GMT

Thank you so much!! The first solution worked perfectly. I'm sure you hear this all the time, but you are a life saver!