topic JSL : Get a list of columns containing a specific value in Discussions

JSL : Get a list of columns containing a specific value

Starwatcher — Fri, 09 Jun 2023 19:54:30 GMT

Hello,

I would like to list all columns containing a specific value in any of their rows and get the listing of those columns.

I start by listing all character columns in a list and loop in it to check if a specific value is contained in each of the columns. Here is the code I used. Any idea how to help me? Thank you in advance

col = dt << get column names( character );
nc = N Items( col );
colList = {};

For( i = 1, i <= nc, i++,
	If(Contains (as column(dt,col[i]) ,"null"),
	Insert Into( colList, col[i] ))
);

Re: JSL : Get a list of columns containing a specific value

txnelson — Thu, 12 Aug 2021 14:11:54 GMT

I think this might do what you want

Names Default To Here( 1 );
dt = current data table();
col = dt << get column names( character );
nc = N Items( col );
colList = {};

For( i = 1, i <= nc, i++,
	If( N Rows( dt << get rows where( As Column( dt, col[i] ) == "null" ) ) > 0,
		Insert Into( colList, col[i] )
	)
);

Re: JSL : Get a list of columns containing a specific value

jthi — Thu, 12 Aug 2021 15:11:29 GMT

Couple of more ways below. I would suggest using txnelson's solution as it is easier to understand.

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(5),
	New Column("Column 1", Character, "Nominal", Set Values({"null", "a", "s", "d", "vb"})),
	New Column("Column 2", Character, "Nominal", Set Values({"a", "a", "a", "a", "a"})),
	New Column("Column 3", Character, "Nominal", Set Values({"a", "a", "a", "a", "a"})),
	New Column("Column 4", Character, "Nominal", Set Values({"null", "a", "a", "a", "a"})),
	New Column("Column 5", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4, 5])),
	New Column("Column 6", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4, 5])),
	New Column("Column 7", Character, "Nominal", Set Values({"a", "a", "a", "null", "a"}))
);

//using Loc and matrix subscripting
char_col_names = dt << Get Column Names(character);
null_cols = {};
For(i = 1, i <= N Items(char_col_names), i++,
	If(N Items(Loc(dt[0, char(char_col_names[i])], "null")) > 0,
		Insert Into(null_cols, char_col_names[i]);
	);
);
Show(null_cols);

//Using list flattening and loc with "2d" matrix

//List flattening function from here https://community.jmp.com/t5/Discussions/JSL-Question-How-to-flatten-quot-a-list-of-lists/td-p/56535
rec = Function({ll},
	{i},
	If(Is List(ll),
		For(i = 1, i <= N Items(ll), i++,
			If(!Is List(ll[i]),
				Insert Into(llnew, ll[i]), //else
				rec(ll[i])
			)
		)
	)
);
flatten = Function({ulist},
	{llnew = {}},
	rec(ulist);
	llnew;
);

col_names = dt << Get Column Names();
//https://community.jmp.com/t5/JSL-Cookbook/Using-Loc-with-a-2D-Matrix/ta-p/195207
null_loc = Loc(flatten(dt[0, col_names ]), "null") - 1;
column_loc = Mod(null_loc, N Items(col_names )) + 1;
Show(col_names[column_loc]);

If you run into "issues" with the speed of the script you can see if this topic could help Should you Loop through a data table or use Recode or use Get

Re: JSL : Get a list of columns containing a specific value

Starwatcher — Fri, 13 Aug 2021 07:44:26 GMT

Thank you, this works like a charm!

Re: JSL : Get a list of columns containing a specific value

Starwatcher — Fri, 13 Aug 2021 08:01:08 GMT

Many solutions, thank you for these insights!