BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
ReginaHong

Occasional Contributor

Joined:

Oct 12, 2018

To find the increase or decrease of values in each row in the same column

Hi,

 

The objective that I want to achieve in this task is if the values change from "1 to 2" or "2 to 1"  for 2 consecutive days in the Sigma column, then remove that two rows. Can It be done? 

Please refer attached my data set.

Thank you.

Eg1.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ms

Super User

Joined:

Jun 23, 2011

Solution

Re: To find the increase or decrease of values in each row in the same column

There are several ways to do this. Here's one example using JSL. It selects both rows that match the criteria. Un-comment the last line of code to delete the rows. Note that as rows are deleted new row pairs with "1 to 2" or "2 to 1" may be created, but the days will not be consecutive.

//Example Table
dt = New Table("untitled 28994",
    Add Rows(39),
    New Column("Day", Numeric, "Continuous", Format("Best", 12), Set Values(1 :: 39)),
    New Column("Sigma", Numeric, "Continuous", Set values(J(39, 1, Random Integer(0, 2))))
);


// Locate row sequence pairs which product = 2
n = N Row(dt);
m = Loc(:Sigma[1 :: n - 1] :* :Sigma[2 :: n] == 2);
dt << select rows(m |/ m + 1);
// dt << delete rows;

 

4 REPLIES 4
txnelson

Super User

Joined:

Jun 22, 2012

Re: To find the increase or decrease of values in each row in the same column

Of course it can be done....however, your description is a bit confusing to me. Could you please elaborate by indicating on your attached example, which of the Days would be removed?
Jim
ReginaHong

Occasional Contributor

Joined:

Oct 12, 2018

Re: To find the increase or decrease of values in each row in the same column

For example, in day 5 and 6, the sigma value has change from 1 to 2. Hence, to remove the rows in day 5 and 6. So it will keep checking for the rest of the rows and delete it if there are changes from 1 to 2 or 2 to 1.

 

Thank you.

Highlighted
ms

Super User

Joined:

Jun 23, 2011

Solution

Re: To find the increase or decrease of values in each row in the same column

There are several ways to do this. Here's one example using JSL. It selects both rows that match the criteria. Un-comment the last line of code to delete the rows. Note that as rows are deleted new row pairs with "1 to 2" or "2 to 1" may be created, but the days will not be consecutive.

//Example Table
dt = New Table("untitled 28994",
    Add Rows(39),
    New Column("Day", Numeric, "Continuous", Format("Best", 12), Set Values(1 :: 39)),
    New Column("Sigma", Numeric, "Continuous", Set values(J(39, 1, Random Integer(0, 2))))
);


// Locate row sequence pairs which product = 2
n = N Row(dt);
m = Loc(:Sigma[1 :: n - 1] :* :Sigma[2 :: n] == 2);
dt << select rows(m |/ m + 1);
// dt << delete rows;

 

ReginaHong

Occasional Contributor

Joined:

Oct 12, 2018

Re: To find the increase or decrease of values in each row in the same column

Hi,

 

Thank you for the solution below. However, I noticed that it has removed day 11 which the sigma is "0 to 2". But I only want to remove rows with sigma " 1 to 2" or "2 to 1" only. Please advise.

 

Thank you.