topic Re: Formula for Grouping months by season in Discussions

Formula for Grouping months by season

Onjai — Tue, 10 Oct 2017 21:31:39 GMT

Hi,

I thought this was a rather simple formula to create, but found myself receiving formatting errors.

I have a column, character type, that contains months (October, November, December, and so on...). I created a new column called "season" and wrote an IF(Contains(...formula to claasifiy if the month is October Then = fall, and so on through the list creating the 4 categories (summer, winter, spring, fall). However, the CONTAINS will not take the OR.

i.e.

If(Contains(:month, "October" | "November"), "fall", Empty())

What am I missing? Any assistance would be appreciated.

Re: Formula for Grouping months by season

ian_jmp — Tue, 10 Oct 2017 21:43:31 GMT

Take a look at the 'Match()' function in 'Help > Scripting Index'.

Re: Formula for Grouping months by season

txnelson — Tue, 10 Oct 2017 23:06:34 GMT

If(Contains(:month, "October") | Contains(:month, "November"), "fall", "")

The above is the correct syntax for the approach you were taking. However, Ian's approach is a more readable, and therefore more proper method

Re: Formula for Grouping months by season

pmroz — Wed, 11 Oct 2017 12:31:55 GMT

You can search a column for a list of values. Here's a formula that uses that approach.

dt = New Table( "test", Add Rows( 12 ), 
	New Column( "Month", Character, "Nominal",
		Set Values(
			{"January", "February", "March", "April", "May", "June", "July",
			"August", "September", "October", "November", "December"}
		)
	),
	New Column( "Season",
		Character,
		"Nominal",
		Formula(
			spring_list = {"March", "April", "May"};
			summer_list = {"June", "July", "August"};
			fall_list   = {"September", "October", "November"};
			winter_list = {"December", "January", "February"};
			If( Contains( spring_list, :Month ), "Spring",
				Contains( summer_list, :Month ), "Summer",
				Contains( fall_list,   :Month ), "Fall",
				Contains( winter_list, :Month ), "Winter",
				
			);
		)
	)
);

Re: Formula for Grouping months by season

dale_lehman — Wed, 11 Oct 2017 13:07:26 GMT

As Jim points out, the sytax requires that the OR statement repeat the column = within the statement. Match does work better, but why not just use the column Recode utility (which will produce the match formula)?

Re: Formula for Grouping months by season

Onjai — Wed, 11 Oct 2017 13:52:10 GMT

Ian,

Worked fine! Nice and clean.

Thank you and the rest of the fine folks in the jmp community.

Cheers!