topic Re: Parse string of a column and fill another column in Discussions

Parse string of a column and fill another column

cchueng — Sat, 10 Jun 2023 23:37:56 GMT

Hi,

I have a table containing ProductName and I want to generate the OUTPUT column. The OUTPUT column a concatenation of ProductID with Serial#. How do I accurately parse the Serial# and create the OUTPUT column?

Serial # examples:

07-08_13 means Serial# 07, 08, 13
09-12 means Serial# 09, 10, 11, 12
01-03_05-08 means Serial# 01, 02, 03, 05, 06, 07, 08
01_04_05-08 means Serial# 01, 04, 05, 06, 07, 08
09_11_13_14 means Serial# 09,11,13,14

ProductName	OUTPUT
AABBCC_07-08_13	AABBCC-07
AABBCC_07-08_13	AABBCC-08
AABBCC_07-08_13	AABBCC-13
AABBCC_09-12	AABBCC-09
AABBCC_09-12	AABBCC-10
AABBCC_09-12	AABBCC-11
AABBCC_09-12	AABBCC-12
EEFFGG_02	EEFFGG-02
HHIIJJ_01-07	HHIIJJ-01
HHIIJJ_01-07	HHIIJJ-02
HHIIJJ_01-07	HHIIJJ-03
HHIIJJ_01-07	HHIIJJ-04
HHIIJJ_01-07	HHIIJJ-05
HHIIJJ_01-07	HHIIJJ-06
HHIIJJ_01-07	HHIIJJ-07
KKLLLMM_01-03_05-08	KKLLLMM-01
KKLLLMM_01-03_05-08	KKLLLMM-02
KKLLLMM_01-03_05-08	KKLLLMM-03
KKLLLMM_01-03_05-08	KKLLLMM-05
KKLLLMM_01-03_05-08	KKLLLMM-06
KKLLLMM_01-03_05-08	KKLLLMM-07
KKLLLMM_01-03_05-08	KKLLLMM-08
NNOOPP_01_04_05-08	NNOOPP-01
NNOOPP_01_04_05-08	NNOOPP-04
NNOOPP_01_04_05-08	NNOOPP-05
NNOOPP_01_04_05-08	NNOOPP-06
NNOOPP_01_04_05-08	NNOOPP-07
NNOOPP_01_04_05-08	NNOOPP-08
QQRRSS_09_11_13_14	QQRRSS_09
QQRRSS_09_11_13_14	QQRRSS_11
QQRRSS_09_11_13_14	QQRRSS_13
QQRRSS_09_11_13_14	QQRRSS_14

Re: Parse string of a column and fill another column

jthi — Wed, 06 Oct 2021 18:24:20 GMT

Most likely you could write a formula with Lag to do that (or script with JSL), but you can also attempt to do that interactively but it takes quite a few steps:

1. Select ProductName column

2. Go to Cols/Utilities/Text To columns and use both - and _ as delimiters

3. Go to Tables and Stack and add everything else besides first column to Stack Columns selection

5. Remove Label column

6. Select both columns which are left and go to Rows / Row Selection / Select Dublicate Rows and delete those rows

7. Select one cell with empty value and choose Select Matching Cells and delete those rows

8. Now choose both colums and right click on the column header and create formula

9. Open the just created formula and change the , to _

10. Rename and delete columns/formulas

11. If you need to have ProductName there, you should be able to keep it there if you don't accidentally delete it in first step like i did

If you have to have - or _ depending on the serialnumber you could handle it in the concatenate formula most likely

Edit: I checked the question a bit more accurately and this is a bit more complicated than I showed here. You will have to add calculations for some of the serial numbers because they are between numbers. This solution won't work in those because some rows will be removed when duplicates are removed.

Re: Parse string of a column and fill another column

Chris_Kirchberg — Wed, 06 Oct 2021 18:22:47 GMT

Hi @cchueng ,

There are a couple of ways. Some are scripting base (look for a reply from someone suggesting this with and example) or you could use successive rounds of Text to Columns (example found here Solved: How can I split text in a column into multiple columns? - JMP User Community) Then you could concatenate the desired columns by selecting them and then either use a column formula or use the New Formula Column option after right clicking on one of the selected columns. Look for the Character options for concat. The column formula is more flexible and gives you a lot of options see the help on this topic (Create Formulas in JMP)

I hope that gives you some direction.

Best,

Re: Parse string of a column and fill another column

Chris_Kirchberg — Wed, 06 Oct 2021 18:30:06 GMT

@jthi ,

You beat me to it. :) Your explanation is more detailed and what I was thinking of too.

Re: Parse string of a column and fill another column

Chris_Kirchberg — Wed, 06 Oct 2021 18:38:51 GMT

Oh, your right. the - is interpreted as a range and the _ is interpreted as a true delimiter of items

Re: Parse string of a column and fill another column

cchueng — Wed, 06 Oct 2021 19:44:18 GMT

Thanks but I prefer a jsl rather than the interactive delimiter. Your method did not address the scenario when the Serial # 09-12. It should output rows like below. Not just AABBCC-09 and AABBCC-12. The "-" means "to" meaning range. So 09-12 means 09, 10, 11, 12.

AABBCC_09-12	AABBCC-09
AABBCC_09-12	AABBCC-10
AABBCC_09-12	AABBCC-11
AABBCC_09-12	AABBCC-12

Re: Parse string of a column and fill another column

jthi — Wed, 06 Oct 2021 19:47:00 GMT

Ok, this might work. Requires JMP16 due to usage of For Each (can be replaced with For for other versions. This is heavily scripted solution, with little or no comments and will require specific starting table to work (needs OUTPUT column).

Names Default To Here(1);

dt = New Table("Untitled 4993",
	Add Rows(32),
	Compress File When Saved(1),
	New Column("ProductName",
		Character,
		"Nominal",
		Set Values(
			{"AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "EEFFGG_02",
			"HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "KKLLLMM_01-03_05-08",
			"KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08",
			"NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08",
			"QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14"}
		)
	),
	New Column("OUTPUT",
		Character(16),
		"Nominal",
		Set Values({"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""})
	)
);


Summarize(dt, uniqProd = by(:ProductName));

valList = {};
For Each({prod}, uniqProd,
	prodPart = Word(1, prod, "_");
	serialPart = Substr(prod, Contains(prod, "_") + 1); //remove start
	possibleRanges = Words(serialPart, "_"); //if they contain -
	For Each({val}, possibleRanges,
		If(Contains(val, "-"), //range
			rangeVals = Words(val, "-");
			For Each({val1}, Index(Num(rangeVals[1]), Num(rangeVals[2])), 
				valToAdd = Substr("0", Length(Char(val1))) || Char(val1);
				Insert Into(valList, prodPart || "-" || valToAdd)
			);
		,
			Insert Into(valList, prodPart || "-" || Substr("0", Length(Char(val))) || Char(val))
		)
	);
);

dt[0,"OUTPUT"] = valList;

Wondering if something like this could be done "easily" with JMP's own pattern matching @Craige_Hales

Re: Parse string of a column and fill another column

cchueng — Wed, 06 Oct 2021 20:16:09 GMT

Hi Chris,

I have a script that will address some of the ProductName and sometimes will parse wrongly. Can you review and add codes to address those ProductNames that are not working so far?

Will work for below examples :

EEFFGG_02

HHIIJJ_01-07

KKLLLMM_01-03_05-08

Will not work yet for below examples :

AABBCC_07-08_13

NNOOPP_01_04_05-08

QQRRSS_09_11_13_14

dt=current data table();

(dt << New Column( "ProductID", Character )) << Set Selected;
(dt << New Column( "OUTPUT", Character )) << Set Selected;
 
//Parse string in ProductName column 

For Each Row(
    dt,
    //Assign the ProductID value
    :ProductID = Word( 1, :ProductName, "_-" ); //"_"
 
    //If the ProductName changes, capture the start and end
    If( :ProductName != Lag( :ProductName, 1 ),
       
        start = Num( Word( 2, :ProductName, "_-" ) );
        end = Num( Word( 3, :ProductName, "_-" ) );
            
    );
    
  //Assign the OUTPUT value
    :OUTPUT = :ProductID || "-" || Repeat( "0", 2 - Length( Char( start ) ) ) || Char( start );
    
    //After end value & there is an _, set start to value after _
    If( start == end & Contains( :ProductName, "_"),
        start = Num( Word(-2, :ProductName, "_-" ) ),
        start++);
);

Re: Parse string of a column and fill another column

cchueng — Wed, 06 Oct 2021 21:44:33 GMT

Hi jthi,

Thanks for the script but my table is actually dynamic. Each day, I will pull up this table from a database with a different date range and different sets of ProductName will appear. So it is not always 32 rows. Your script can almost work but can you address the array size? Thanks.

Re: Parse string of a column and fill another column

jthi — Thu, 07 Oct 2021 08:19:40 GMT

It should be fairly easy. Do you have as many rows in the pulled data as you will have serial numbers?

Re: Parse string of a column and fill another column

cchueng — Sat, 09 Oct 2021 05:22:31 GMT

Hi jthi,

Yes, the number of rows matched the number of serial numbers.

Re: Parse string of a column and fill another column

jthi — Sat, 09 Oct 2021 07:03:35 GMT

If that is the case, it should already be able to handle it. I did small modifications (creation of OUTPUT column if it is missing). Currently it does rely on the fact that the datatable is sorted by ProductNames, which could cause issues at some point, or then you can make sure the input table is sorted (easiest solution).

You should be able to use the For Each part can be even used without datatable as long as you have list variable named uniqProd which has ProductNames. After that is just figuring out how to add values to datatable.

Names Default To Here(1);

//Test table
/*
dt = New Table("Untitled 4993",
	Compress File When Saved(1),
	New Column("ProductName",
		Character,
		"Nominal",
		Set Values(
			{"AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "EEFFGG_02",
			"HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "KKLLLMM_01-03_05-08",
			"KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08",
			"NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08",
			"QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14"}
		)
	)
);
*/

dt = Current Data Table();

Summarize(dt, uniqProd = by(:ProductName));

valList = {};
For Each({prod}, uniqProd, //loop for each ProductName
	prodPart = Word(1, prod, "_");
	serialPart = Substr(prod, Contains(prod, "_") + 1); //remove start
	possibleRanges = Words(serialPart, "_"); //if they contain -
	For Each({val}, possibleRanges, //Loop for serialnumber inside the ProductName
		If(Contains(val, "-"), //range
			rangeVals = Words(val, "-");
			For Each({val1}, Index(Num(rangeVals[1]), Num(rangeVals[2])), 
				valToAdd = Substr("0", Length(Char(val1))) || Char(val1);
				Insert Into(valList, prodPart || "-" || valToAdd)
			);
		,
			Insert Into(valList, prodPart || "-" || Substr("0", Length(Char(val))) || Char(val))
		)
	);
);

//if starting table doesn't have OUTPUT column create it
If(!Contains(dt << Get Column Names("String"), "OUTPUT"), 
	dt << New Column("OUTPUT", Character, "Nominal");
);

//dt[0,"OUTPUT"] = valList; subscripting, maybe a bit more difficult to understand
dt:OUTPUT << Set Values(valList);

Simplified version what they script tries to do:

1. Script gets all unique values in ProductName column

2. Script loops over each of those in two part For Each loop

3. First loop loops over the unique values and splits them into Product and number parts

4. Serialnumber part is then looped again to split it into single values. These single values are concatenated with Product part and then the string is inserted into valList list (this 4. isn't exactly as simple as I explained here).

5. After both loops have finished you should have a list, which has all possible serialnumbers. Product part will be in alphabetical order.

6. This list is then added to OUTPUT if it exists, if it doesn't it is first created.

Re: Parse string of a column and fill another column

cchueng — Tue, 12 Oct 2021 17:06:37 GMT

Hi jthi,

Your script will not work if my ProductName is a mixed of chars and numbers. I attached a table. This is how most of my productName will look like. Thanks for helping me out.

Re: Parse string of a column and fill another column

jthi — Mon, 11 Oct 2021 05:10:34 GMT

Quickly checking there are three different issues:

1. Breaking the "rule" that there is always same amount of ProductNames as should be serials. N4TA59_13-19 in data only once, should be 7 times
2. Serial number part not starting with _ (N4PX74-01-06)
3. Range not starting with _ (N4TA47_13-14-15-16-17-18) or these should be just single values

All these will require different fixes. Are these data quality issues with your data or should the script be able to handle all of these?

Re: Parse string of a column and fill another column

txnelson — Mon, 11 Oct 2021 09:47:49 GMT

Here is a script that produces a new table that expands the rows as requested. By placing the data into a new table, it bypasses the issue where there are not a sufficient number of rows for the expansion. It does appear to handle all of the known patterns.

Names Default To Here( 1 );
dt = Current Data Table();

dt2 = New Table( "The Output",
	New Column( "Product Name", character ),
	New Column( "ProductID", Character ),
	New Column( "OUTPUT", Character )
);

For( dtRow = 1, dtRow <= N Rows( dt ), dtRow++,
	If( dtRow == 1 | dt:ProductName[dtRow - 1] != dt:ProductName[dtRow],
		prodID = Word( 1, dt:ProductName[dtRow], "_" );
		remainder = Substr( dt:ProductName[dtRow], Length( prodID ) + 2 );
		While( Word( 1, remainder, "_" ) != "",
			theWord = Word( 1, remainder, "_" );
			If( Contains( theWord, "-" ),
				numDigets = Length( Word( 1, theWord, "-" ) ) - 1;
				zeros = Repeat( "0", numDigets );
				For( loop = Num( Word( 1, theWord, "-" ) ) + 1,
					loop <= Num( Word( 2, theWord, "-" ) ), loop++,
					dt2 << Add rows( 1 );
					theRow = N Rows( dt2 );
					dt2:ProductName[theRow] = dt:ProductName[dtRow];
					dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
					dt2:output[theRow] = dt2:ProductID[theRow] || "-" ||
					Substr( zeros, Length( Char( loop ) ) ) || Char( loop );
				);
			,
				dt2 << Add Rows( 1 );
				theRow = N Rows( dt2 );
				dt2:output[theRow] = prodID || "-" || Word( 1, theWord, "-" );
				dt2:ProductName[theRow] = dt:ProductName[dtRow];
				dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
			);
			remainder = Substr( remainder, Length( theWord ) + 2 );
		);
	)
);

Re: Parse string of a column and fill another column

cchueng — Mon, 11 Oct 2021 15:23:07 GMT

The ProductName data are manually entered. These are exceptions and will improve over time. Please ignore them.

Re: Parse string of a column and fill another column

cchueng — Tue, 12 Oct 2021 03:42:48 GMT

Thanks Jim but the OUTPUT appears to be incorrect. For example the first 3 lines, the OUTPUT should be :

AABBCC-07

AABBCC-08

AABBCC-13

Re: Parse string of a column and fill another column

txnelson — Tue, 12 Oct 2021 04:19:30 GMT

It just had a minor error where a -1 was not removed when I restructured my code.

Names Default To Here( 1 );
dt = Current Data Table();

dt2 = New Table( "The Output",
	New Column( "Product Name", character ),
	New Column( "ProductID", Character ),
	New Column( "OUTPUT", Character )
);

For( dtRow = 1, dtRow <= N Rows( dt ), dtRow++,
	If( dtRow == 1 | dt:ProductName[dtRow - 1] != dt:ProductName[dtRow],
		prodID = Word( 1, dt:ProductName[dtRow], "_" );
		remainder = Substr( dt:ProductName[dtRow], Length( prodID ) + 2 );
		While( Word( 1, remainder, "_" ) != "",
			theWord = Word( 1, remainder, "_" );
			If( Contains( theWord, "-" ),
				numDigets = Length( Word( 1, theWord, "-" ) ) - 1;
				zeros = Repeat( "0", numDigets );
				For( loop = Num( Word( 1, theWord, "-" ) ),
					loop <= Num( Word( 2, theWord, "-" ) ), loop++,
					dt2 << Add rows( 1 );
					theRow = N Rows( dt2 );
					dt2:ProductName[theRow] = dt:ProductName[dtRow];
					dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
					dt2:output[theRow] = dt2:ProductID[theRow] || "-" ||
					Substr( zeros, Length( Char( loop ) ) ) || Char( loop );
				);
			,
				dt2 << Add Rows( 1 );
				theRow = N Rows( dt2 );
				dt2:output[theRow] = prodID || "-" || Word( 1, theWord, "-" );
				dt2:ProductName[theRow] = dt:ProductName[dtRow];
				dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
			);
			remainder = Substr( remainder, Length( theWord ) + 2 );
		);
	)
);

Please make sure you take the time to study the code, so you completely understand how it is working.

Re: Parse string of a column and fill another column

jthi — Tue, 12 Oct 2021 15:22:32 GMT

Because the data is manually entered this will most likely not handle all special cases, but seems to be working for current values:

Names Default To Here(1);

//Test table
/*
dt = New Table("Untitled 4993",
	Compress File When Saved(1),
	New Column("ProductName",
		Character,
		"Nominal",
		Set Values(
			{"AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_07-08_13", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "AABBCC_09-12", "EEFFGG_02",
			"HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "HHIIJJ_01-07", "KKLLLMM_01-03_05-08",
			"KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08", "KKLLLMM_01-03_05-08",
			"NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08", "NNOOPP_01_04_05-08",
			"QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "QQRRSS_09_11_13_14", "N4TA47_13-14-15-16-17-18", "N4TA59_13-19", "N4PX74-01-06"}
		)
	)
);
*/

dt = Current Data Table();
Summarize(dt, uniqProd = by(:ProductName));

newVals = Associative Array();
For Each({prodname_str}, uniqProd,
	prodPart = Word(1, prodname_str, "_-");
	serialPart = Substr(prodname_str, Length(prodPart) + 2);
	//find and remove next range from serialPart
	newVals[prodname_str] = {};
	While(Length(serialPart) > 0 & serialPart != "_" & serialPart != "-",
		nextRange = Regex(serialPart, "\d*-\d*");
		If(IsMissing(nextRange),
			break();
		);
		serialPart = Substitute(serialPart, nextRange, "");
		If(Contains({"-", "_" }, Substr(serialPart, 1, 1)),
			serialPart = Substr(serialPart, 2); //remove -_ if first character
		);
		
		nextRange = Words(nextRange, "-");
		rangeVals = Index(Num(nextRange[1]), Num(nextRange[2]));
		For Each({val}, rangeVals,
			val = char(val);
			val = Substr("00", Length(val) + 1) || val;
			Insert Into(newVals[prodname_str], prodPart||"-"||val);
		);
	);
	singleVals = Transform Each({val}, Words(serialPart, "_"),
		val = char(val);
		val = Substr("00", Length(val) + 1) || val;
		prodPart||"-"||val;
	);
	Insert Into(newVals[prodname_str], singleVals);
);

vals = {{}, {}};
For Each({{key, value}}, newVals,
	productname_list = Repeat({key}, N Items(value));
	Insert Into(vals[1], productname_list);
	Insert Into(vals[2], Sort List(value));
);

//add values to new table
dt = New Table("Productname",
	New Column("ProductName", Character, "Nominal", Set Values({})),
	New Column("OUTPUT", Character, "Nominal", Set Values({}))
);
dt:ProductName << Set Values(vals[1]);
dt:OUTPUT << Set Values(vals[2]);

Re: Parse string of a column and fill another column

cchueng — Sun, 24 Oct 2021 23:05:35 GMT

Hi Jim,

Can it be done such that there is no dt2? Can you add the ProductID and Output columns in dt?

Thanks!