topic Re: Search the string in Column and copy the numeric value in 2nd column in Discussions

Search the string in Column and copy the numeric value in 2nd column

Jackie_ — Fri, 09 Jun 2023 23:26:18 GMT

I have an Excel Worksheet consisting of two columns, one columns has strings with person name and other column contain age of the person. I have almost a list of 500 person. Is there any way I can automate it?Like using script to assign age based on the person name ?

Re: Search the string in Column and copy the numeric value in 2nd column

txnelson — Mon, 09 Mar 2020 19:41:06 GMT

Assuming the list of 500 names is in a JMP data table, and you have that csv file with the Names and Ages, you can easily input the csv file, and then use

Tables==>Join

to match the name/age data table to the table with the list of 500 names.

Re: Search the string in Column and copy the numeric value in 2nd column

Jackie_ — Mon, 09 Mar 2020 19:54:18 GMT

Yes that's the easiest approach, but the problem is JMP data file has 10000 data sets of different names and the excel sheet has random 500 names from 10000 data sets of jmp file. So I want something which can search those particular name in the excel and past the age from jmp data file

Re: Search the string in Column and copy the numeric value in 2nd column

txnelson — Mon, 09 Mar 2020 20:08:49 GMT

You have 10,000 JMP data tables....correct? What are the data table names? Can that be used to determine how to find the data table that has the age for a given name in the Excel spreadsheet?

Can you provide information about what data are in the 10,000 JMP data tables, and how do you get the age from the data table, once you find the correct JMP data table?

More specifics are really needed before one can attempt to solve your problem.

Re: Search the string in Column and copy the numeric value in 2nd column

Jackie_ — Tue, 10 Mar 2020 13:30:44 GMT

Hi Txnelson,

The JMP file has 10,000 rows and 2 columns. The 1st column name is Person and the 2nd is age. Yes it can be used to find the data table that has the age for a given name in the Excel spreadsheet.

The JMP data file has 10,000 rows having different names and ages (Strings and numbers)

Both the person name and age are listed in the JMP data file.

I have a excel file which has two columns and 500 rows, the 1st column has the persons name and the 2nd contains ages. I am trying to write a script which will look for all 500 data (person name) from excel from in the JMP data file and copy the age value corresponding to the person's name in the excel file.

Re: Search the string in Column and copy the numeric value in 2nd column

Mark_Bailey — Tue, 10 Mar 2020 13:33:03 GMT

This case would be a good time to import the Excel workbook with the names and ages and then load them into an associative array for lookup.

Re: Search the string in Column and copy the numeric value in 2nd column

Jackie_ — Tue, 10 Mar 2020 14:46:31 GMT

Hi mark,

Thanks for the suggestions. I am new to the JMP could you please suggest how I can start ?

Re: Search the string in Column and copy the numeric value in 2nd column

txnelson — Tue, 10 Mar 2020 15:30:50 GMT

While @Mark_Bailey suggestion of using an Associative Array, will work, I believe that a simple Join will probably solve the problem.

Open the JMP table with 10,000 rows
Open in JMP, the Excel file
Using the JMP table from Excel as the base table, use
1. Tables==>Update to match the names and add in the ages to the base table
Save the updated table back as an Excel file

I suggest you take the time to read the documentation "Discovering JMP" and "Using JMP"

Help==>JMP Documentation Library

Re: Search the string in Column and copy the numeric value in 2nd column

Mark_Bailey — Tue, 10 Mar 2020 16:08:25 GMT

Here is a functioning example to learn from:

Names Default to Here( 1 );

// open reference data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make container for lookup
lookup  = Associative Array();

// get names and ages for lookup
For Each Row(
	Insert Into( lookup, dt:name, dt:age );
);

Close( dt, No Save );

// open new table (use Big Class again, delete existing age column)
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Delete Columns( :age );

// make new age column and populate it using lookup table
dt << New Column( "new age", "Numeric", "Continuous" );
For Each Row(
	:new age  = lookup[ :name ];
);

Re: Search the string in Column and copy the numeric value in 2nd column

KimRas2 — Fri, 17 Mar 2023 12:40:37 GMT

Hi Mark,

I have a similar task, but my "key" is a continuous data type.

In Excel it is very easy to create a look-up table and use it to transform highly un-linear characteristics.

In this case it is battery voltage data to estimates of state-of-charge (SoC) and vice versa. So, I would like to convert e.g. a data table with measured voltage data to SoC estimates. One challenge is that my look-up table may/will not have all the many voltage levels existing in the data, so I need the SoC corresponding to the nearest voltage level. E.g. voltage data is 3.801 V and look-up table has 3.800 V as the nearest and a corresponding SoC of 51%.

Sometimes my task is to convert "backwards" (SoC => voltage), but hopefully I could create that solution on my own given good inspiration ;)

BR, Kim.

Re: Search the string in Column and copy the numeric value in 2nd column

jthi — Fri, 17 Mar 2023 14:03:38 GMT

If I understand correctly what you want to do (basically join by minimum distance) there are quite a few ways of doing this (kdtable, vptree, loc, distance, addins, ...). Below is one example

Names Default To Here(1);

dt_lookup = New Table("Untitled",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([3.8, 4.8, 4])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([50, 52, 51]))
);

dt = New Table("Untitled",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([3.801, 4.801, 4.000, 3.7, 4.802, 4.799]))
);

lookup_values = Column(dt_lookup, "Column 2") << get values;
search_values = Column(dt_lookup, "Column 1") << get values;

dt << New Column("Column 2", Numeric, Continuous, << Set Each Value(
	As Constant(tab = KDTable(search_values););
	{rows, dist} = tab << K nearest rows(1, Matrix(:Column 1));
	lookup_values[rows[1]];
));

Below are some demonstrations for other options using matrices

Names Default To Here(1);
lookup_values = [50, 52, 51];
search_values = [3.8, 4.8, 4];
search_val = 4.3;

// These should return row from dt_lookup
kdt = KDTable(search_values);
{kd_rows, kd_dist} = kdt << K nearest rows(1, Matrix(search_val));

vpt = VPTree(search_values);
{vp_rows, vp_dist} = vpt << K nearest rows(1, Matrix(search_val));

dist = Distance(search_values, Matrix(search_val));
lmin_dist = LocMin(dist);

abs_calc = Abs(search_values - search_val);
lmin_abs = LocMin(abs_calc);

show(dist, lmin_dist, abs_calc, lmin_abs, kd_rows, kd_dist, vp_rows, vp_dist);

Re: Search the string in Column and copy the numeric value in 2nd column

Craige_Hales — Fri, 17 Mar 2023 17:47:16 GMT

interpolate( ) would also be a good choice.