topic How can I find the row and column of a value within a data table? in Discussions

How can I find the row and column of a value within a data table?

natalie_ — Thu, 19 May 2016 15:31:54 GMT

Hi all,

I need to find the column and row number of a particular value within a data table. I know how to search for a value within a particular column (ie., row = dt1 << get rows where(:Device == "Device");) , which returns the row number, but what if I don't know what row it will be in?

Is it possible to do this in JMP? I see how to do it from going to edit then search, but is there a way to do this in JSL?

Re: How can I find the row and column of a value within a data table?

pmroz — Thu, 19 May 2016 16:51:26 GMT

If you're looking for text, then this example may be helpful:

dt = open("$sample_data\Bands Data.jmp");

char_cols = dt << get column names(Character, string);

search_string = "E310";

for (i = 1, i <= nitems(char_cols), i++,

match_rows = dt << get rows where(as column(dt, char_cols[i]) == search_string);

if (nrows(match_rows) > 0,

print("Column: [" || char_cols[i] || "] Rows: " || char(match_rows));

);

Re: How can I find the row and column of a value within a data table?

natalie_ — Thu, 19 May 2016 19:04:31 GMT

Thank you, your examples are always great!

I know that the value can only happen once in the file, so I made a while loop instead so that it would not continue to check the other columns for it.

file = Pick File();

dt = Open (file);

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

column(dt,i) << data type(Character);

column(dt,i) << set modeling type(continuous);

);

nCols = dt << Get Column Names ("Character");

found =0;

search_string = "Vtlin";

i=1;

While (found == 0,

match_rows = dt << get rows where(as column(dt, nCols)==search_string);

if(nrows(match_rows) > 0, found = 1, i++);

if(i==nCols, found =1, );

);

vtlinCol = i;