topic Re: How do you assign a variable from a list to a row in a data table? in Discussions

How do you assign a variable from a list to a row in a data table?

gable64 — Sat, 10 Jun 2023 23:45:47 GMT

I am working with a data table that has variable number of rows. I want to assign a categorical description that I will define to a new column called 'Reason 1'. This 'Reason 1' column will be assigned when the 'Name' column matches any of the strings in any of the lists that I have defined.

The 'Name' row entry will match exactly with an entry in the ::slitter, ::label, or ::case lists.

I want to assign the different reason 1 codes to all rows and then analyze the data according to each Reason 1.

dt1 << New Column("Reason 1", Character, "Nominal");

Nrows = N Rows(dt1);

For( i =1, i<=Nrows, i++
		For( j=1, j<= N items( ::slitter ), j++,
		If(:Name(i) == Eval(slitter(j)), :Reason 1(i) = "Slitter");
		);
	);


// %Slitter% description codes
slitter = {"TM2300-370",
"TM2300-380",
"TM2300-385",
"TM2300-390",
"TM2300-395",
"TM2300-400"};

// %Label% description codes (8)
::label = {"TM2300-020",
"TM2300-025",
"TM2300-070",
"TM2300-075",
"TM2300-080",
"TM2300-085",
"TM2300-090",
"TM2300-095"};

//%Case% description codes (7)
::case = {"TM2300-030",
"TM2300-035",
"TM2300-040",
"TM2300-045",
"TM2300-050",
"TM2300-055",
"TM2300-060"};

Re: How do you assign a variable from a list to a row in a data table?

txnelson — Tue, 15 Mar 2022 20:57:44 GMT

You have a couple of syntax errors, and an inefficient For loop to check for matches that can be replaced by using a Contains() function. I have also added a method for creating the new formula column to do the same calculation.

Names Default To Here( 1 );

dt1=current data table();
dt1 << New Column( "Reason 1", Character, "Nominal" );

// %Slitter% description codes
slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"};

Nrows = N Rows( dt1 );

For( i = 1, i <= Nrows, i++,
	If( Contains( slitter, :Name[i] ) == 1,
		:Reason 1[i] = "Slitter"
	)
);

// or
dt1 << New Column( "Reason 2",
	character,
	formula(
		If( Row() == 1,
			slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"}
		);
		If( Contains( slitter, :Name ) == 1,
			:Reason 2 = "Slitter"
		);
	)
);

Re: How do you assign a variable from a list to a row in a data table?

pmroz — Wed, 16 Mar 2022 13:14:29 GMT

There's another way that uses get rows where and contains. No loop required.

dt = data table("Sample Data Set-1");

// %Slitter% description codes
slitter = {"TM2300-370", "TM2300-380", "TM2300-385", "TM2300-390", "TM2300-395", "TM2300-400"};

match_rows = dt << get rows where(contains(slitter, :Name));

dt:Reason 1[match_rows] = "Slitter";

Re: How do you assign a variable from a list to a row in a data table?

gable64 — Wed, 23 Mar 2022 18:28:20 GMT

My mind initially went to a loop to complete what I wanted to do, but thank you for this alternative solution!

This categorized my data exactly like I wanted.

Re: How do you assign a variable from a list to a row in a data table?

gable64 — Wed, 23 Mar 2022 18:30:40 GMT

For whatever reason, the loop was not assigning the variable to one particular list out of 5 lists I wanted to assign. The 'Get rows where' function accomplished what I wanted.

Thank you for spending time on this solution!

- Logan