BookmarkSubscribeRSS Feed
ValarieSimmons

Community Trekker

Joined:

Mar 2, 2016

How to get columns names that contains certain words given by user?

Hi,

 

I am looking for advice regarding getting columns that contains certain words that I am interested in.

 

For instance:

I would like to get any column names that contains "Number" in the given sample data provided below. Expected returned list of columns names should be {"Flight Number" , "Tail Number"}. However, I am not able to get anything returned. My codes are as follows:

 

 

dt = open("$SAMPLE_DATA/Air Traffic.jmp");
a= "Number";
col=dt<<get column names(string);
nc=nitems(col);
xxx={};
for(i=1,i<=nc,i++,
  if(contains(col,a),
  insert into(xxx,i);
  );
);

 

 

Any help or advice will be greatly appreciated! Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

Re: How to get columns names that contains certain words given by user?

Remember 'col' will be a list. So try:

 

 

dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );
a = "Number";
col = dt << get column names( string );
nc = N Items( col );
xxx = {};
For( i = 1, i <= nc, i++,
If( Contains( col[i], a ),
Insert Into( xxx, col[i] )
)
);

 

 

Print( xxx );

8 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

Re: How to get columns names that contains certain words given by user?

Remember 'col' will be a list. So try:

 

 

dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );
a = "Number";
col = dt << get column names( string );
nc = N Items( col );
xxx = {};
For( i = 1, i <= nc, i++,
If( Contains( col[i], a ),
Insert Into( xxx, col[i] )
)
);

 

 

Print( xxx );

ValarieSimmons

Community Trekker

Joined:

Mar 2, 2016

Re: How to get columns names that contains certain words given by user?

Thank you, Ian!

Exactly the solution I'm looking for.

Best Regards,

Val

ENTHU

Contributor

Joined:

May 13, 2018

Re: How to get columns names that contains certain words given by user?

Hi,

 

I am stuck with a similar problem.I need to check if the columns contain either of the 2 strings and then replace column value with value in another column.

Here's the code -

For(i=1,i<= NRows(dt),i++,
if(contains(:step[i],"NA")| contains(:step[i],".") ,
:step[i] = description[i];
)

);

 

But I get an error saying - argument is wrong type in access or evaluation of 'Contains'

What am I doing wrong here?

cwillden

Community Trekker

Joined:

May 1, 2017

Re: How to get columns names that contains certain words given by user?

This is pretty different from the original thread.  I suggest creating a new topic.  Does your step column have a numeric data type?

-- Cameron Willden
ENTHU

Contributor

Joined:

May 13, 2018

Re: How to get columns names that contains certain words given by user?

No.The column type is character.

ValarieSimmons

Community Trekker

Joined:

Mar 2, 2016

Re: How to get columns names that contains certain words given by user?

Hi Ian Ian@JMP,

 

An extension to what I was previously working on:

 

Using the same data as the previous question, based on the selected words that I want from the available columns in data table "Air Traffic", I am expecting an output as shown below if the available columns in the data contains the words in the "SelectedNames" column at their respective rows:

11978_pastedImage_2.png

 

However I am getting this error message in Log & no insertion of any correct column names in the second column:

11979_pastedImage_3.png

11980_pastedImage_4.png

 

These are my codes, can you help me to check what's wrong with them?

 

dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );
a = {"Number", "Flight"};
col = dt << get column names( string );
nc = N Items( col );
dt1 = New Table( "Table1" );
c1 = (dt1 << New Column( "SelectedNames", character, set values( a ) )) << get values;
dt1 << New Column( "AvailableColumns", character );
xxx={};
For( j = 1, j <= N Items( c1 ), j++,
For( i = 1, i <= nc, i++,
If( Contains( col[i], a[j] ),
Insert Into( xxx, col[i] )
);
Column( dt1, 2 )[j] = Eval( xxx );
xxx = {};
);
 

 

ian_jmp

Staff

Joined:

Jun 23, 2011

Re: How to get columns names that contains certain words given by user?

You need to distinguish between a list and the type of items that it holds (which, in general, don't have to be homogenous). The error is due to the fact that you are trying to put list xxx (which happens to contain items that are all character), into a cell in a character column.

 

The best way to do this will depend on what happens next in your code. I suspect that you might want to have 'AvaliableColumns' as an expression column, rather than a character column:

 

 

dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );
a = {"Number", "Flight"};
col = dt << get column names( string );
nc = N Items( col );
dt1 = New Table( "Table1" );
c1 = (dt1 << New Column( "SelectedNames", character, set values( a ) )) << get values;
dt1 << New Column( "AvailableColumns", expression );
For( j = 1, j <= N Items( c1 ), j++,
xxx = {};
For( i = 1, i <= nc, i++,
If( Contains( col[i], a[j] ), InsertInto(xxx, col[i]) );
);
Column( dt1, 2 )[j] =  xxx;
 
);

 

ValarieSimmons

Community Trekker

Joined:

Mar 2, 2016

Re: How to get columns names that contains certain words given by user?

Thanks again Ian.

Your solution works like a charm!

Best Regards,

Val