cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
natalie_
Level V

Looking for the column number?

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?




1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Looking for the column number?

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

      );

);

View solution in original post

9 REPLIES 9
pmroz
Super User

Re: Looking for the column number?

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

      );

);

pmroz
Super User

Re: Looking for the column number?

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

      );

);

natalie_
Level V

Re: Looking for the column number?

Oh thank you for catching that!

My code is working well, thanks again.

julian
Community Manager Community Manager

Re: Looking for the column number?

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

natalie_
Level V

Re: Looking for the column number?

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

msharp
Super User (Alumni)

Re: Looking for the column number?

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.

natalie_
Level V

Re: Looking for the column number?

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

      ),);

msharp
Super User (Alumni)

Re: Looking for the column number?

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

);

natalie_
Level V

Re: Looking for the column number?

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