topic Re: Split Column Based on Text in Discussions

Split Column Based on Text

sagrim — Fri, 09 Jun 2023 23:40:07 GMT

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

Re: Split Column Based on Text

txnelson — Thu, 08 Oct 2020 20:50:52 GMT

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.

Re: Split Column Based on Text

Thierry_S — Thu, 08 Oct 2020 20:47:37 GMT

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

Re: Split Column Based on Text

sagrim — Thu, 08 Oct 2020 22:34:49 GMT

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

Re: Split Column Based on Text

sagrim — Thu, 08 Oct 2020 22:35:54 GMT

Good catch @Thierry_S . Thank you !

Re: Split Column Based on Text

txnelson — Fri, 09 Oct 2020 00:47:42 GMT

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.