Community Trekker

Joined:

Jun 23, 2011

Set Binary Flag for Continuous Grouping of Data

Hi Folks,

I have some snow data and I want to be able to determine the starting and ending dates of the presence of snow by various time intervals (month, water year, year) using Tables | Summary.

I want to set col(x) value to 1 when the current row plus the next 5 rows for snow contain snow data (> 0). I can get this to work well when I have more than 10 continuous rows of snow. When the number of continuous rows of snow is < 10 then I get the following results using the following code:

Flag Snow
0 0.0
0 0.0
1 1.0
1 2.0
0 3.0
0 4.0
1 5.0
1 6.0
0 0.0
0 0.0
0 0.0
0 0.0

If(
:Name( "snow" ) > 0 & Lag( :Name( "snow" ) > 0, -1 ) &
Lag( :Name( "snow" ) > 0, -2 ) & Lag(
:Name( "snow" ) > 0,
-3
) & Lag( :Name( "snow" ) > 0, -4 ) | :Name( "snow" ) >
0 & Lag( :Name( "snow" ) > 0, 1 ) &
Lag( :Name( "snow" ) > 0, 2 ) & Lag(
:Name( "snow" ) > 0,
3
) & Lag( :Name( "snow" ) > 0, 4 ),
1,
0
)

Any ideas on how to do this the correct, or a better, way? I need to create different threshold levels for this calculation so having something that would work for 7, 10, 21 continuous days of data is essential
4 REPLIES

Community Trekker

Joined:

Jun 23, 2011

Re: Set Binary Flag for Continuous Grouping of Data

Try following formula. The"n=5" defines that it will set flag to 1 for all events when >5 rows in a row have snow data>0. Hope this helps.

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Local

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
{i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">5

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> s

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">J

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">*

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)}

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">For

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">-

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'"><=

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">++,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">If

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">:

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">Snow[

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Row

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">()

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Empty

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">()]

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">>

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 3">
s[i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">2

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">=

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> s[(i

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">2

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">-

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">]

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">+

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 2">
)

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
style="mso-tab-count: 1">

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">If

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: #0000dd; FONT-FAMILY: 'Courier New'">Max

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'"> s
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">>

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">)

Community Trekker

Joined:

Jun 23, 2011

Re: Set Binary Flag for Continuous Grouping of Data

Thanks aim for the suggestion. I learned a lot about additional ways to program JMP based on the code you provided. The only hiccup was that I had to change the n=5 to be 1 less than the desired value. That is, when n=5, the number of continuous rows of snow data needed to be 6 before the binary flag was set. By changing to n=4 the program set the flag when there was 5 contiguous rows of data. Thanks for your suggestion and help with this.

Community Trekker

Joined:

Jun 23, 2011

Re: Set Binary Flag for Continuous Grouping of Data

Glad it helped. For the number of continuous rows setting the flag I was going of your original description "I want to set col(x) value to 1 when the current row plus the next 5 rows for snow contain snow data (> 0)."

For strict requirement n=5 just change

style="WORD-SPACING: 0px; FONT: 16px/20px 'Times New Roman'; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; TEXT-ALIGN: left; orphans: 2; widows: 2; webkit-border-horizontal-spacing: 0px; webkit-border-vertical-spacing: 0px; webkit-text-decorations-in-effect: none; webkit-text-size-adjust: auto; webkit-text-stroke-width: 0px">
class=Apple-style-span
style="FONT-SIZE: 13px; LINE-HEIGHT: 16px; FONT-FAMILY: arial, helvetica, sans-serif">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>

style="FONT-SIZE: 10pt; COLOR: rgb(0,0,221); FONT-FAMILY: 'Courier New'">If

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: rgb(0,0,221); FONT-FAMILY: 'Courier New'">Max

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>
s
class=Apple-converted-space>
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">>

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;

into

style="WORD-SPACING: 0px; FONT: 16px/20px 'Times New Roman'; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; TEXT-ALIGN: left; orphans: 2; widows: 2; webkit-border-horizontal-spacing: 0px; webkit-border-vertical-spacing: 0px; webkit-text-decorations-in-effect: none; webkit-text-size-adjust: auto; webkit-text-stroke-width: 0px">
class=Apple-style-span
style="FONT-SIZE: 13px; LINE-HEIGHT: 16px; FONT-FAMILY: arial, helvetica, sans-serif">
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">
class=Apple-style-span
style="WORD-SPACING: 0px; FONT: 16px/20px 'Times New Roman'; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; TEXT-ALIGN: left; orphans: 2; widows: 2; webkit-border-horizontal-spacing: 0px; webkit-border-vertical-spacing: 0px; webkit-text-decorations-in-effect: none; webkit-text-size-adjust: auto; webkit-text-stroke-width: 0px">
class=Apple-style-span
style="FONT-SIZE: 13px; LINE-HEIGHT: 16px; FONT-FAMILY: arial, helvetica, sans-serif">
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>

style="FONT-SIZE: 10pt; COLOR: rgb(0,0,221); FONT-FAMILY: 'Courier New'">If

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: rgb(0,0,221); FONT-FAMILY: 'Courier New'">Max

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">(

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>
s
class=Apple-converted-space>
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">
color=red>>=

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">n

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">1

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">,

style="FONT-SIZE: 10pt; COLOR: teal; FONT-FAMILY: 'Courier New'">0

style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Courier New'">
class=Apple-converted-space>
)

style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Courier New'">;

to keep the code more readable.

Community Trekker

Joined:

Jun 23, 2011

Re: Set Binary Flag for Continuous Grouping of Data

Sorry,

I forgot that I wrote it that way. You certainly solved the problem as originally stated. I like the change listed below. Thanks again for your help with this issue.