Subscribe Bookmark RSS Feed

Need help with For loop

rk1988

New Contributor

Joined:

Oct 24, 2017

Hi I have a table like this and what I want to do is:

--> For every row, do a +-1 to X and +-1 to Y and take median of those four Age values and save it under a new column. Can someone please help?

 

X    Y     Age

-1   2      27

2   -1      23

4    5      56

-7  -4     43

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Attached is a sample data table, with the 4 new columns, with their formulas, and the Median column, with it;s formula.  Below is an example of the formulas I placed into the data table.  This is the X-1, Y column formula

dt = Current Data Table();
TheRow = Row();
Xval = :X - 1;
Yval = :Y;
MyRows = dt << get rows where( :X == Xval & :Y == Yval );
If( N Rows( MyRows ) > 0,
	TheResult = dt:Age[MyRows[1]]
);
Jim
7 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

@rk1988 : 
   Let us assume your table is called "dt" 
The following piece of code should add and subtract 1 to X and Y as new columns . 

dt << New Column("AddXBias",Numeric,Continuous,Formula(:X+1))
    << New Column("SubXBias",Numeric,Continuous,Formula(:X-1))
    << New Column("AddYBias",Numeric,Continuous,Formula(:Y+1))
    << New Column("SubYBias",Numeric,Continuous,Formula(:Y-1)); 

Now , the question is which columns you want to take the mean of ? 

If it is just the age column , this should do it 

AgeMean = Col Mean(:Age); 

If it is some other values, please provide clear direction on which values and I will try to help 

Best
Uday
rk1988

New Contributor

Joined:

Oct 24, 2017

Thanks for your response :) Let me clarify...

 

For each row:

1. add 1 to X, now go to that row with coordinates (X+1,Y) and grab the age value --> this is variable Age1

2. subtract 1 from X, now go to that row with coordinates (X-1,Y) and grab the age value --> this is variable Age2

3. add 1 to Y, now go to that row with coordinates (X,Y+1) and grab the age value --> this is variable Age3

4. subtract 1 from Y, now go to that row with coordinates (X,Y-1) and grab the age value --> this is variable Age4

5. Calculate median of Age1...4 and save that under a new column

Repeat steps for each row...

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

@rk1988 : 
      Sorry , not really following your requirement here . If you were to introduce a bias in variables "X" and "Y" as you have explained , the values in column "Age" will not be influenced. Age1 to Age4 and their mean is fixed unless the bias is introduced in Age. 

 

     And that mean can be acquired by : 

Col Mean(:Age) ;

     Apologies that I am unable to follow , would you like to demonstrate what you want in an excel sheet manually , so may be it is more clear .

Best
Uday
txnelson

Super User

Joined:

Jun 22, 2012

I am looking for further specification.  For a given case where X=3 and Y=-1, are you looking for the value of the column Age, where X=X-1, and Y is unchanged:  Using your small sample table 

     :Age==>where(X==2 and Y==-1)==>23

Is this what you are looking for?

Jim
rk1988

New Contributor

Joined:

Oct 24, 2017

That is correct, that way we get four different Age values from the four different coordinates combinations. 

txnelson

Super User

Joined:

Jun 22, 2012

Solution

Attached is a sample data table, with the 4 new columns, with their formulas, and the Median column, with it;s formula.  Below is an example of the formulas I placed into the data table.  This is the X-1, Y column formula

dt = Current Data Table();
TheRow = Row();
Xval = :X - 1;
Yval = :Y;
MyRows = dt << get rows where( :X == Xval & :Y == Yval );
If( N Rows( MyRows ) > 0,
	TheResult = dt:Age[MyRows[1]]
);
Jim
rk1988

New Contributor

Joined:

Oct 24, 2017

Thank you very much, great help!