topic Re: Looking for the column number? in Discussions

Looking for the column number?

natalie_ — Fri, 28 Oct 2016 13:27:12 GMT

Hi all,

I need the column number. I have the name of the column, but it might not always be in the same space.

I first decided to use a While loop which stops iterating when the column is found. "n" would be the column number, but it isn't working.

colFound = 0;

n=0;

While(colFound == 0,

n++;

colName = Column Name(n);

if(colName==Vd, colFound=1, );

);

I get this error: invalid argument in access or evaluation of 'Column Name' , Column Name/*###*/(n)

However, if I use this (this one time I know the actual number) it returns the column name I am looking for:

col = Column Name(38);

show(col);

From a post (column number) , I used msharp's suggestion, but still no luck on getting the column number, it just returns the column name?

Re: Looking for the column number?

pmroz — Wed, 25 May 2016 18:09:47 GMT

column name is misleading - it returns the column, e.g. :height, and not "height". Also you never check to see if n goes beyond the number of columns in your table. This code will work:

dt = Open( "$sample_data\Big Class.jmp" );

Current Data Table( dt );

vd = "height";

colFound = 0;

n = 0;

ncols = ncol(dt);

While( !colFound & n <= ncols,

n++;

colName = (Column Name( n ) << get name);

If( colName == Vd,

colFound = 1

);

Re: Looking for the column number?

pmroz — Wed, 25 May 2016 18:19:09 GMT

For some reason the while loop was not working properly if the column name is not found. This is a bit cleaner:

dt = Open( "$sample_data\Big Class.jmp" );

Current Data Table( dt );

vd = "height";

colFound = 0;

n = 0;

ncols = ncol(dt);

for (i = 1, i <= ncols, i++,

colName = (Column Name( i ) << get name);

If( colName == Vd,

colFound = 1;

break();

);

Re: Looking for the column number?

natalie_ — Wed, 25 May 2016 18:44:50 GMT

Oh thank you for catching that!

My code is working well, thanks again.

Re: Looking for the column number?

jules — Wed, 25 May 2016 18:57:09 GMT

It looks like Peter beat me to responding, but here's another method without any loops, so it should be faster for a large number of columns.

dt = Open( "$sample_data\Analgesics.jmp" );

column_number = Contains(dt << get column names, Column("drug"));

Just replace "drug" with whatever column name you're searching for to get the associated column number.

julian

Re: Looking for the column number?

natalie_ — Wed, 25 May 2016 19:03:37 GMT

Thanks, glad you posted! I got it working. Now I understand a little more from both your posts

Re: Looking for the column number?

msharp — Wed, 25 May 2016 19:47:40 GMT

I think you miss understood my suggestion. My suggestion is that you should NOT find a column number. It's poor coding practice, in fact it's horrendous.

Re: Looking for the column number?

natalie_ — Wed, 25 May 2016 20:18:12 GMT

If I know there will always be a column named "Vd" in my data but it could be in a different location each time, why wouldn't I want to use the column number? After the Vd column, there will be "x" number of columns that I need to insert into a list. Since the number of columns is unknown until after I run the script and the names of these columns will be determined based on the result of my data, I don't see why using the column number is horrendous. I suppose as those columns are being created I could insert those into a list, but please explain why getting the column number is horrendous?

colFound = 0;

n=0;

ncols = ncol(dtSummary);

for(i=1, i<=ncols, i++,

colName = (Column Name(i) << get name);

if(colName=="Vd", colFound=1; break(), );

);

iCol = 1;

//iCol = Contains(dtSummary << get column names, Column("Vd"));

if(colFound ==1,

y_cols = {};

for(n=iCol+1, n<=(iCol + devicePass), n++,

y_cols = Insert(y_cols, n, n);

),);

Re: Looking for the column number?

msharp — Wed, 25 May 2016 21:59:08 GMT

Just as a rule of thumb. I would say, if your code or table is set up in such a way that it depends on column order, you are probably doing something ugly. It might work, but there is probably a more elegant, faster way to do it.

Order of data should really only be considered at the end of a script when you choose to display the data (and for the most part that's not even necessary since it's usually better to show the data in a graph and not in a table).

This being said, ugly code that works is still good code. In your case, assuming you have no control of how the data-tables are being fed into your script this may be the simplest solution. However, you could still condense the code like so: (Notice how I never need to know the column number).

colFound = 0;

ncols = ncol(dtSummary);

for(i=1, i<=ncols, i++,

if(colFound,

if(devicePass,

devicePass--;

Insert Into(y_cols, i, i);

break()

)

);

colName = (Column Name(i) << get name);

if(colName=="Vd", colFound=1);

);

Re: Looking for the column number?

natalie_ — Thu, 26 May 2016 13:44:23 GMT

Thank you msharp, I will keep this in mind. It is way cleaner!