topic Re: Recode multiple columns with list in Discussions

Recode multiple columns with list

MargaretWM — Fri, 09 Jun 2023 16:57:49 GMT

I want to recode multiple columns using a conditional formula by going through each column with a For Each function. I am not able to recode successfully with the following code:

//Create list of columns to be recoded 
ColNames = {dt:Name( "col 1" ), dt:Name( "col 2" ), dt:Name( "col 3" ), dt:Name( "col 4" )};
 
For Each( {listitem}, ColNames,
	Recode(
		listitem,
		{Lowercase( _rcNow ), If( Contains( _rcNow, "too much" ),
			"1000",
			_rcNow
		), Regex( _rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE )}
	)
);

My hunch is that the body of the For Each clause is incorrect -- that I have to somehow call out the columns more clearly.

The data table as "dt" is established earlier in the code. For the record, I don't get an error when I use the script but the columns are not recoded properly. Thanks.

Re: Recode multiple columns with list

jthi — Wed, 27 Apr 2022 19:34:49 GMT

Maybe you just need to use Recode Column instead of Recode and then tinker a bit on how to reference on columns.

This seems to be working, at least it replaces too much with 1000 in col 1:

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(1),
	New Column("col 1", Character, "Nominal", Set Values({"too much"})),
	New Column("col 2", Character, "Nominal", Set Values({"a"})),
	New Column("col 3", Character, "Nominal", Set Values({"c"})),
	New Column("col 4", Character, "Nominal", Set Values({"d"}))
);

    //Create list of columns to be recoded 
ColNames = {"col 1"};
For Each({listitem}, ColNames,
	dt << Recode Column(
		//As Column(dt, listitem),
		AsColumn(listitem),
		{Lowercase(_rcNow), If(Contains(_rcNow, "too much"),
			"1000",
			_rcNow
		), Regex(_rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE)},
		Update Properties(1),
		Target Column(Column(listitem))
	)
);

Re: Recode multiple columns with list

MargaretWM — Wed, 27 Apr 2022 20:27:03 GMT

I adapted the code here for my purposes (several columns in the list) but it still does not work:

ColNames = {dt:Name("col 1"), dt:Name("col 2"),
 dt:Name("col 3"), dt:Name("col4")};
 
 For Each({listitem}, ColNames, 
	dt<<Recode Column(
	AsColumn(listitem),
	{Lowercase( _rcNow ), If( Contains( _rcNow, "too much" ),
		"1000",
		_rcNow
	), Regex( _rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE )},
	Update Properties(1),
	Target Column(Column(SPcol))
));

Were you suggesting I go column by column to recode?

Re: Recode multiple columns with list

jthi — Thu, 28 Apr 2022 04:36:02 GMT

I'm not sure which formats for columns Recode Columns requires. You can see that we are using different syntaxes for ColNames (I'm using list of column names, and you are using old way to reference columns (Name("col name"), it has been replaced by "col name"n), also we are using different columns in Target Column.

If run the example I gave with this data table:

I end up with this

Here is updated example with new table and more columns added to ColNames list

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("col 1", Character, "Nominal", Set Values({"1000", "too much", "too much"})),
	New Column("col 2",
		Character,
		"Nominal",
		Set Values({"too much", "", ""}),
		Set Display Width(65)
	),
	New Column("col 3", Character, "Nominal", Set Values({"c", "", "too much"})),
	New Column("col 4", Character, "Nominal", Set Values({"d", "", ""}))
);
wait(1);
    //Create list of columns to be recoded 
ColNames = {"col 1", "col 2", "col 3", "col 4"};
For Each({listitem}, ColNames,
	dt << Recode Column(
		//As Column(dt, listitem),
		As Column(listitem),
		{Lowercase(_rcNow), If(Contains(_rcNow, "too much"),
			"1000",
			_rcNow
		), Regex(_rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE)},
		Update Properties(1),
		Target Column(Column(listitem))
	)
);

Re: Recode multiple columns with list

MargaretWM — Thu, 28 Apr 2022 14:47:49 GMT

Thanks for your help! Jordan Hiller helped me understand your original reply. I don't understand why using the list of columns as strings works while using a list of actual columns does not work, but I follow your solution now anyway. Helpful to see how column names are called out now too. Good reminder. Thanks again.

Re: Recode multiple columns with list

jthi — Thu, 28 Apr 2022 15:01:12 GMT

I always like using column names and then figuring out (or at least try to...) how to use them when I need to manipulate columns / get values from columns. Usually it is one of As Column(), Column() or Eval(), but sometimes you have to get more creative.

Here are few snippets that might come handy from time to time:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

// set name which would usually break
Column(dt, "name") << Set Name("@na-me");

str_col_list = dt << Get Column Names("String");
show(str_col_list);
column_list = dt << Get Column Reference(str_col_list);
show(column_list);
ascolumn_list = Transform Each({col_name}, str_col_list,
	Name Expr(As Column(dt, col_name))
);
show(ascolumn_list);

Also see and Names (jmp.com) regarding Name("col name")