nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Deleting multiple columns based on strings in their name

joshua

Hi,

I need to delete multiple columns based the strings they contain but sometimes I want to keep some others even if they don't contain those strings.

So by following this post's solution Deleting multiple columns based on column name

dt = Open( "$sample_data/Hollywood Movies.jmp" );
col_names =  dt << Get Column Names( String );

For( i = N Items( col_names ), i > 0, i--,
	If(
		Not(
			Contains( col_names[i], "Gross")  | contains(col_names[i], "Genre"   ) | col_names[i] == "theme"),

	Remove From( col_names, i)
	)
);

So, what I want to have is remove anything not contains with "Gross" or "Genre" but if the column name is == "theme" don't remove it.

It seems it does not work for now ! Why?

The second bullet to this question

How can I see col_names as a data table so I can know what is the output without hovering on the col_names. Because sometimes I have a long list of col names and hovering is not efficient to see what is in that column list.

Thanks!

Accepted answers

txnelson

dt = Open( "$sample_data/Hollywood Movies.jmp" );
col_names =  dt << Get Column Names( String );


dt:Profitability << delete formula;

For( i = N Items( col_names ), i > 0, i--,
	If(
		Not(
			Contains( col_names[i], "Gross")  | contains(col_names[i], "Genre"   ) | col_names[i] == "theme"),

	Remove From( col_names, i)
	)
);
// At this point in the script, you have a data table 
// with all of the original columns(Profitability is now a static column)
// And you have a JSL List called "col_names", which is stored in memory
// so you can do anythin you want given this
// You could just write the contents of the list to the log.  Remember, the 
// list "col_names", only contains the names of the columns, it does not 
// contain the whole column(name, properties and data)
Print( col_names );
// or
Write( col_names );
// or
Show( col_names );
// That will give you the list of columns that are going to be deleted
// or before you want to see the columns in their entirety you can create
// a new data table with just those columns

newdt = dt << subset( all rows, columns( col_names ) );

// The statement below, is a perfectly valid statement, however, it will
// not work the way you think it will.  The end result of this statement
// is to create a new list of column names. 
// {"Genre", "Domestic Gross", "Foreign Gross", "World Gross", "Opening Wknd Gross"}
deleted_columns = delete columns( col_names );

All comments

txnelson

From the JSL you provided, you have left out the most critical statement

dt << delete columns( col_names );

This is the line that actually does the deletions. However, in this sample case, it has an additional issue. The final list of columns

{"Genre", "Domestic Gross", "Foreign Gross", "World Gross", "Opening Wknd Gross"}

has the column "World Gross" in it, which is used in the calculation of column "Profitability". When the "delete columns" message is processed it detects the issue and displays in the log

Cannot delete the selected columns while some are referenced by the formulas of remaining columns.
Removing a formula leaves the data unchanged. Removing references replaces each reference with an empty value. These effects are permanent and cannot be undone.

If you delete the formula from the "Profitability" column, it will convert the values to static values,

dt:Profitability << delete formula;

then the "delete columns" will work properly.

Concerning your issue about hovering over a variable to see it's value, and wanting to create a data table to be able to see the values, there is a much simpler way to do that. Rather than hovering over the "col_names" variable, what you need to do, is to highlight it, and then right click and select "Run Script". By rule, if anything in a JMP script is highlighted, the "Run Script" will only run the highlighted lines/statements/variables. The results will be displayed in the log.

joshua

Hi Jim,

Thanks for your answer. Ok I see about what you saying.

Lets say we don't care about the formulas. Let's delete all of the formula connections and remove the columns that I wanted.

and I want to create (and see ) those deleted columns as a datatable

by following your suggestion

dt = Open( "$sample_data/Hollywood Movies.jmp" );
col_names =  dt << Get Column Names( String );


dt:Profitability << delete formula;

For( i = N Items( col_names ), i > 0, i--,
	If(
		Not(
			Contains( col_names[i], "Gross")  | contains(col_names[i], "Genre"   ) | col_names[i] == "theme"),

	Remove From( col_names, i)
	)
);

deleted_columns = delete columns( col_names );

this does not create still what I need

txnelson

dt = Open( "$sample_data/Hollywood Movies.jmp" );
col_names =  dt << Get Column Names( String );


dt:Profitability << delete formula;

For( i = N Items( col_names ), i > 0, i--,
	If(
		Not(
			Contains( col_names[i], "Gross")  | contains(col_names[i], "Genre"   ) | col_names[i] == "theme"),

	Remove From( col_names, i)
	)
);
// At this point in the script, you have a data table 
// with all of the original columns(Profitability is now a static column)
// And you have a JSL List called "col_names", which is stored in memory
// so you can do anythin you want given this
// You could just write the contents of the list to the log.  Remember, the 
// list "col_names", only contains the names of the columns, it does not 
// contain the whole column(name, properties and data)
Print( col_names );
// or
Write( col_names );
// or
Show( col_names );
// That will give you the list of columns that are going to be deleted
// or before you want to see the columns in their entirety you can create
// a new data table with just those columns

newdt = dt << subset( all rows, columns( col_names ) );

// The statement below, is a perfectly valid statement, however, it will
// not work the way you think it will.  The end result of this statement
// is to create a new list of column names. 
// {"Genre", "Domestic Gross", "Foreign Gross", "World Gross", "Opening Wknd Gross"}
deleted_columns = delete columns( col_names );

joshua

why

col_names[i] == "theme"<BR />

is not still working ?