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
sophiaw
Level III

Searching a List For a Value

Hi,

 

I have a list that I made from a delimited string. That string looks like this: (number)|(name)|(number)|(name)|(number).... etc. What I have done is taken that string and parsed it by the delimiter ('|'). I then took that list of all the values and made a new list of just the "names" and created new columns with it. The problem that I have run into is when I parse the string and place it into each column. Because, not every string in every row starts with the same value. I had to get all the "names" from two rows of data. So the first row, third row, fifth row, etc should be the same, but which "name" their strings end on is different. Subsequently, every second, fourth, sixth, etc rows start with a different "name" (where the odd rows left off). The list of names that I get to make the columns is all the names that exist, but I want to be able to place all the numbers in their proper columns.

 

Part of the script that I used to parse all the data is:

 

// Get all the pin names

List = {};

list = {};

list2 = {};

 

// Iterate through the first rows of :Test_Result to get the pin names

list = words(column(dt4,"Test_Result")[1], "|");

list2 = words(column(dt4,"Test_Result")[2], "|");

// First value in string is useless information, so remove it

list2 = Remove(list2,1);

one_test_result_list = concat(list,list2);

// Get all the pin names starting with the second value

For(i = 1, i <= nitems(one_test_result_list)/2, i++,

     Assign(List, one_test_result_list[i*2]);

);

 

I have no idea how to do this. Because the "number" following every "name" in the string is the value that corresponds to that name. So if I see that name, I need to place the next value into that name's column.

 

Thank you so much for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: Searching a List For a Value

Not sure I'm answering the right question either.  Maybe this will help:


dt = New Table( "Untitled",New Column( "dogs"),New Column( "cats"),New Column( "mice"),New Column( "horses"));


load = function({txt},


  dt << addrow(1);


  list=words(txt,"|");


  for(i=1,i<nitems(list),0,


    columnName = list; i=i+1;


    columnValue = list; i=i+1;


    column(dt,columnName)[] = num(columnValue);


  )


);


load("mice|3|horses|7");


load("cats|3|mice|9");


9612_dogsmicehorsescats.PNG

Line 1 makes a template data table and assumes you know all the possible column names.  Line 2 begins a user function that takes a single argument, txt.  Lines 3 through 10 are the function: add a row to the table, make a list from the text, go through the list two items at a time, get a name, get a value.  Line 8 makes a column from a character name and uses the empty [ ] to insert a value into the current row.  Lines 11 and 12 are example calls to the function.

Craige

View solution in original post

6 REPLIES 6
vince_faller
Super User (Alumni)

Re: Searching a List For a Value

Not sure I understand you but it sounds like you just want to do this?

str = "1|'face'|2|Shatner|3|Spock|4|Seahorse";

list = words(str, "|");

n = nitems(list)/2;

index = 1::n;

evens = index*2;

names = list[evens];



Or possibly associative arrays?

Vince Faller - Predictum
sophiaw
Level III

Re: Searching a List For a Value

That is close! I already have the code that parses the data and I create a new list with just the names. What I'm really after is after I create those new columns, I have to populate them with all the numbers. Since the number following every name is the value for that name, I want to just search the list for the name, and place the next value in that column. Sort of like this:

(Assume these are columns):      face   |   Shatner   |   Spock   |   Seahorse

                                                  2      |       3         |      4       |        5

Since the first number to me is useless, I just ignore it, but the numbers following the name are the values I care about. But, suppose one of those values were missing:

str = "1|Shatner|3|Spock|4|Seahorse|5";

I still have all the original columns, but some row in my data table could be missing some value in the middle, or the beginning, I want it to search the list and skip missing values:


(Assume these are columns):      face   |   Shatner   |   Spock   |   Seahorse

                                                          |       3         |       4      |        5

I'm not sure how possible this is, but not every row starts with the same value. But it should have every value after it finds the first one. So all I really need is to find which column the first value belongs in, and then just fill in all the columns after that.

Thank you!

pmroz
Super User

Re: Searching a List For a Value

dt = New Table( "Example", Add Rows( 4 ),

     New Column( "Raw Data", Character, Nominal,

           Set Values(

                {"1|face|3|Spock|4|Seahorse|5", "1|face|2|Shatner|3|Spock|5",

                "1|face|2|Shatner|3|Spock|4|Seahorse|5",

                "1|Shatner|3|Spock|4|Seahorse|5"}

           )

     )

);

col_names = dt << get column names(string);

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

     word_list = words(dt:raw data[i], "|");

     for (k = 2, k <= nitems(word_list), k=k+2,

           one_col = word_list[k];

           one_num = word_list[k+1];

          

           if (!contains(col_names, one_col),

                dt << new column(one_col, character, nominal);

                insertinto(col_names, one_col);

           );

           column(dt, one_col)[i] = one_num;

     );

);

Craige_Hales
Super User

Re: Searching a List For a Value

Nice handling of column names that are not known ahead of time.

Craige
Craige_Hales
Super User

Re: Searching a List For a Value

Not sure I'm answering the right question either.  Maybe this will help:


dt = New Table( "Untitled",New Column( "dogs"),New Column( "cats"),New Column( "mice"),New Column( "horses"));


load = function({txt},


  dt << addrow(1);


  list=words(txt,"|");


  for(i=1,i<nitems(list),0,


    columnName = list; i=i+1;


    columnValue = list; i=i+1;


    column(dt,columnName)[] = num(columnValue);


  )


);


load("mice|3|horses|7");


load("cats|3|mice|9");


9612_dogsmicehorsescats.PNG

Line 1 makes a template data table and assumes you know all the possible column names.  Line 2 begins a user function that takes a single argument, txt.  Lines 3 through 10 are the function: add a row to the table, make a list from the text, go through the list two items at a time, get a name, get a value.  Line 8 makes a column from a character name and uses the empty [ ] to insert a value into the current row.  Lines 11 and 12 are example calls to the function.

Craige
sophiaw
Level III

Re: Searching a List For a Value

Thank you Craige! That is exactly what I'm looking for! It works perfectly!