Looking for the column number?

[#1]

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?

[#2]

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

      );

);

[#3 · Accepted solution]

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();

      );

);

[#4]

Oh thank you for catching that!

My code is working well, thanks again.

[#5]

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​

[#6]

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

[#7]

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.

[#8]

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);

      ),);

[#9]

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);

);

[#10]

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