cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
learning_JSL
Level IV

how do i create two nested loops?

Hi - I am attempting to write a script that follows this workflow:

 

1 - begin a loop on a certain starting datetime (e.g. Oct 1, 2021 11:00 AM).  I'll refer to this as row n.  Obtain value from col1[row n].

2 - go to next row, n+1. set col2 = col1 [n+1] - col1[n].   

3 - go to next row, n+2, set col2 = col1 [n+2] -  col1[n]

4 - go to next row, n+3, set col2 = col1[ n+3] - col1[n].   Continue to loop to through each subsequent row until datetime[n+k] =  datetime[n]  + 7 days.   (Note that k will vary depending on the time increment between rows as the loop proceeds; e.g. the time increases from one row to the next by 5 mins, 15 mins, 1 hr, etc, depending on the row in question.)

5 - Repeat steps 1 to 4 for each new week of data by using a new "start date" obtained in step 4.  Continue looping through another 7 days of data.

6 - Repeat step 5 until data table runs out of rows.   

 

The goal is to do computations on col1 data that compare a week's worth of rows to the first row of that week, and then to repeat this over and over for each subsequent week (week 2, week 3, week 4, etc) until all rows have been processed.    : )

 

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User (Alumni)

Re: how do i create two nested loops?

Okay it sounds like you're saying 

 

Subtract the earliest flow point each day from each flow?  That's what you're trying to do?  I'm guessing because I don't think 13:15 AM is a thing.   

 

Names default to here(1);
dt = current data table(); // this assumes your table 
// I'm going to make a bunch of extra columns to kind of show you smaller steps

// we really want to be able to say, This is the first row of the day
// so let's make a date column
dt << New Column("Just Date", <<Formula(ShortDate(:DateTime)));
// now there are a bunch of ways to do this but if we just make sure it's sorted by date time.  
// We should be able to pick the first one easily enough. 

dt << Sort(By(:DateTime), Order( Ascending ), replace table);
// now we just want which row is the smallest per day
// for that we can colmin

// this is the column that will actually be the most useful.  
dt << New Column("smallest row for the day", formula(ColMin(Row(), :Just Date)));

// now we can just for make your difference column

dt << New Column("flow_difference", 
	formula(if(row() == :smallest row for the day, // if you don't care if it's missing don't need to do this
		.
	, // else
		:flow - :Flow[:smallest row for the day]
	))
);

you once you have the smallest row for the day, you only need to do the last new column however many times for as many columns as you want to compute the difference to.  

 

Hope I didn't overcomplicate things, cause I definitely didn't answer your original question of nested for loops.  

Vince Faller - Predictum

View solution in original post

9 REPLIES 9
vince_faller
Super User (Alumni)

Re: how do i create two nested loops?

Can you give a small data table with what you expect the data to be?  Or at least what the data table you're using looks like? It sounds like a column formula would be a simpler solution.  

Vince Faller - Predictum
vince_faller
Super User (Alumni)

Re: how do i create two nested loops?

I think you might be saying something like this? 

 

Names default to here(1);
dt = New Table( "Untitled 1133",
	Add Rows( 5 ),
	New Column( "Week1",
		Numeric,
		"Continuous",
		Format( "d/m/y h:m:s", 22, 0 ),
		Input Format( "d/m/y h:m:s", 0 ),
		Set Values( [3716957885, 3716957886, 3716957887, 3716957888, 3716957889] )
	),
	New Column( "Week2",
		Numeric,
		"Continuous",
		Format( "d/m/y h:m:s", 22, 0 ),
		Input Format( "d/m/y h:m:s", 0 ),
		Set Values( [3716957885, 3716957886, 3716957887, 3716957888, 3716957889] )
	),
	New Column( "Week3",
		Numeric,
		"Continuous",
		Format( "d/m/y h:m:s", 22, 0 ),
		Input Format( "d/m/y h:m:s", 0 ),
		Set Values( [3716957885, 3716957886, 3716957887, 3716957888, 3716957889] )
	)
);

nc = ncols(dt);

for(i=1, i<=nc, i++, 
	col_name = column(i) << Get Name(); // get the original column name
	Eval(EvalExpr( // you have to do this so you can substitute in the actual col_name instead of a variable
		dt << New Column(col_name || " comparison", 
			// just take current row minus first row
			formula(Expr(column(dt, col_name))[row()]-Expr(column(dt, col_name))[1])
		)	
	));
);
Vince Faller - Predictum
learning_JSL
Level IV

Re: how do i create two nested loops?

Thank you Vince.  I'm going through this now....as a JSL newbie(!)  I have a couple of questions that will help me follow your approach.  Why were 5 rows to added to a new data table?  Why did you need to  "set values" and why were those five numbers selected?   Your script is for 3 weeks but my actual dataset has about 120 weeks. 

Finally, how does the script know which column is the value on which to compute (i.e. "flow" in my example)?  (My data table has more columns than the three shown in my example.)   Wouldn't it be easier to assign the column name rather than toggling through the columns?  I'm probably missing something here.    

learning_JSL
Level IV

Re: how do i create two nested loops?

The script produced a new table with 5 rows and the following 6 columns:

week1, week2, week3, week1 comparison, week2 comparison, week3 comparison.  

(The 5 rows were for datetimes of 10/13/2021 8:18:05 AM through 10/13/2021 8:18:09 AM.)

 

I am trying to end up with only one new column in my existing table, a column with the results of the computation described in my original post.  I would have all of the original data table rows along with one new column computed in accordance with the description in my original post.

vince_faller
Super User (Alumni)

Re: how do i create two nested loops?

I just made a sample table of what I guessed you were saying.  I just made it today() + 1 second per row.  

 

I didn't know anything about your table so I just made it do all the columns.  

 

and I can't understand your original post. 

Vince Faller - Predictum
learning_JSL
Level IV

Re: how do i create two nested loops?

For context, attached is my actual JMP table if it helps.  (I am wanting to compute based on the column "Pred Formula LOG_ECOLI_PEAR".)

learning_JSL
Level IV

Re: how do i create two nested loops?

Sure.  Thank you.  Attached is a snippet of a dataset that I created in excel as an example.

 

 

vince_faller
Super User (Alumni)

Re: how do i create two nested loops?

Okay it sounds like you're saying 

 

Subtract the earliest flow point each day from each flow?  That's what you're trying to do?  I'm guessing because I don't think 13:15 AM is a thing.   

 

Names default to here(1);
dt = current data table(); // this assumes your table 
// I'm going to make a bunch of extra columns to kind of show you smaller steps

// we really want to be able to say, This is the first row of the day
// so let's make a date column
dt << New Column("Just Date", <<Formula(ShortDate(:DateTime)));
// now there are a bunch of ways to do this but if we just make sure it's sorted by date time.  
// We should be able to pick the first one easily enough. 

dt << Sort(By(:DateTime), Order( Ascending ), replace table);
// now we just want which row is the smallest per day
// for that we can colmin

// this is the column that will actually be the most useful.  
dt << New Column("smallest row for the day", formula(ColMin(Row(), :Just Date)));

// now we can just for make your difference column

dt << New Column("flow_difference", 
	formula(if(row() == :smallest row for the day, // if you don't care if it's missing don't need to do this
		.
	, // else
		:flow - :Flow[:smallest row for the day]
	))
);

you once you have the smallest row for the day, you only need to do the last new column however many times for as many columns as you want to compute the difference to.  

 

Hope I didn't overcomplicate things, cause I definitely didn't answer your original question of nested for loops.  

Vince Faller - Predictum
learning_JSL
Level IV

Re: how do i create two nested loops?

Thanks Vince!  This will be helpful.  I think I'm on the right track now.  : )