Split Column Based on Text

[#1]

Good afternoon.  I am attempting to split one column into three new columns and place a piece of the string from the original column into one of the three new columns based on the context of the text.  I understand that text to columns would parse out the string based on some delimiter, however, this doesn’t quite get me where I need to be.  I need a function that reads the text, checks for certain keywords (years, months or days) and then places a piece of the string (the numerical value that precedes “years”, “months” or “days”) in the appropriate column.  I see that the word() function can extrapolate the first word or value and this would be a start but then the value still needs to be placed in one of three potential columns based on the text. Does anyone have thoughts on how to code this?  Or ideas for a potential work-around?  Any insight is greatly appreciated. 

Example attached where column 1 shows the data in its current format and columns 2-4 are the ideal state.  Thank you!

Stephanie

[#2 · Accepted solution]

Here is one way to handle this

Names Default To Here( 1 );
dt = New Table( "Example dt",
	Add Rows( 10 ),
	Set Header Height( 46 ),
	New Column( "Age",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"2-day old", "43-day old", "3-day old",
			"3-month old", "4-month old", "5-day old",
			"32-day old", "13-year old", "10-year old",
			"10-month old"}
		)
	)
);
dt << New Column( "AgeYears" );
dt << New Column( "AgeMonths" );
dt << New Column( "AgeDays" );

For( i = 1, i <= N Rows( dt ), i++,
	Match( Word( 2, :Age[i], "- " ),
		"day",
			:AgeDays[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"month",
			:AgeMonths[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"year",
			:AgeYears[i] =
			Num( Word( 1, :age[i], "- " ) )
	)
);

 Above was corrected as noted.

[#3]

Hi,
I think there is a minor typo at the end of the script: in the last Match case, the receiving column should be :AgeYears[i], not :AgeMonths[i].
Best,
TS

[#4]

Good catch @Thierry_S .  Thank you !

[#5]

This is so awesome, thank you @txnelson ! This code is going to greatly reduce the time it takes me to clean my data, I greatly appreciate your response. Quick question: Is there a way to embed the new variables directly into the existing dt rather than creating a new one and then merging back to the original dt?  

Stephanie 

[#6]

I don't know what you are asking.  My example has one data table, which is then acted on to give you 3 new columns, in the original data table.  Isn't that what you are asking for?  If you just want the new columns created directly into an already existing data table, the JSL would be

Names Default to Here( 1 );
dt = Current Data Table();
// or dt = Data Table("your data table's name");

dt << New Column( "AgeYears" );
dt << New Column( "AgeMonths" );
dt << New Column( "AgeDays" );

For( i = 1, i <= N Rows( dt ), i++,
	Match( Word( 2, :Age[i], "- " ),
		"day",
			:AgeDays[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"month",
			:AgeMonths[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"year",
			:AgeYears[i] =
			Num( Word( 1, :age[i], "- " ) )
	)
);

It appears that you need to do some reading.  If you are new to JMP you need to read the Discovering JMP and Using JMP documents.  One really needs to know what is available in JMP for one to become a JSL scripter.  And also, after reading those 2 documents, you need to read the Scripting Guide.