columns

[#1]

Hello everyone,

I have several columns:

1- “Average number” columns

2- “number” columns

with number ranging from 1 to 10,000.

Normally each column belonging to group (2) should also belong to group (1) and vice versa. So we should have the same number of columns in both groups. 

However, columns belonging to group 1 do not belong to group 2 and vice versa.

Example : "Average 730" but the "730" column does not exist.

The objective is to have the intersection of these 2 groups so that there are the same columns in the 2 groups.

[#2]

Associative array can help when making the intersection Associative Arrays in Set Operations (jmp.com) if you can make the keys look same. Or you could stack the tables, make them look similar and then perform a join. I think the most difficult part might be how you can get those lists of columns you are interested in and this depends on your data.

[#3]

The columns are in the same data table.

Here is an example taken from my database:

I aim to remove the columns "duration 721", "730" and "750"

[#4 · Accepted solution]

One option using associative arrays

Names Default To Here(1);

dt = Open("$DOWNLOADS/AA.jmp");

// build 
aa_duree = Associative Array();
aa_nonduree = Associative Array();

For Each({col_name}, dt << Get Column Names("String"),
	If(Word(1, col_name) == "durée",
		aa_duree[Word(-1, col_name)] = col_name;
	, !IsMissing(Regex(col_name, "\d+")),
		aa_nonduree[col_name] = 1;
	,
		continue()
	);
);
aa_duree_copy = aa_duree;
aa_duree_copy << Intersect(aa_nonduree);
cols_to_keep = Insert(aa_duree_copy << get keys, aa_duree_copy << get values);

aa_duree_copy2 = aa_duree;
aa_nonduree_copy = aa_nonduree;
aa_duree_copy2 << remove(aa_nonduree);
aa_nonduree_copy << remove(aa_duree);
cols_to_remove = Insert(aa_nonduree_copy << get keys, aa_duree_copy2 << get values);

show(cols_to_keep, cols_to_remove);

There are also other options, like joining stacked table(s) or using just lists

[#5]

Great thank you it works!

However the columns are in the "cols_to_remove" list, how can I delete these columns directly in the data table?

[#6]

Use dt<<Delete Columns(column1, column2, ...)