Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How to calculate moving average of column with 10 million entries ?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jan 30, 2019 8:39 PM
| Last Modified: Jan 30, 2019 8:44 PM
(7167 views)

I am trying to run the below formula on a column with 10 million rows to calculate the moving average , but the script keeps running forever and jmp stops responding: thisTable << New Column( "av", Numeric, Continuous, Formula( Mean( :x[Index( Row() - 99999, Row() )] ) ) ); Is there any limitation or something wrong with the formula. How can I calculate the moving average in this case?

- Tags:
- csv
- jsl
- moving average

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You are correct, the formula was in error. I was not setting the value of "theSum" correctly. The following should get correct results:

```
If( Row() == 1,
theLag = 3;
theSum = 0;
);
If( Row() <= theLag,
theSum = theSum + :yield;
theAvg = theSum / Row();
,
theAvg = ((theSum - :yield[Row() - theLag]) + :yield) / theLag;
theSum = (theSum - :yield[Row() - theLag]) + :yield;
);
theAvg;
```

Jim

8 REPLIES 8

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

Created:
Jan 30, 2019 9:10 PM
| Last Modified: Jan 30, 2019 9:40 PM
(7159 views)
| Posted in reply to message from jojmp 01-30-2019

Try this and see if it runs faster

`Col Moving Average( :x, 1, 999999, 0 )`

Look into the Scripting Index for documentation and example

Or, you might try this, which eliminates the requirement to calculate the mean for a list of 100000 rows for each row's calculation, and cut it down to a single subtraction, a single addition, and one division.

```
If( Row() == 1,
theLag = 100000;
theSum = Sum( :x[Index( 1, theLag )] );
);
If(
Row() < theLag, theAvg = Col Moving Average( :x, 1, theLag, 0 ),
Row() == theLag, theAvg = theSum / theLag,
theAvg = ((theSum - :x[Row() - theLag]) + :x) / theLag
);
theAvg;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

this script does not work on JMP12 (Col Moving Average not supported on JMP12)

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

The Col Moving Average() function can easily be taken out of the formula. Taking the approach provided in the second formula in my previous post, the calculation of the moving average by summing the data and then dividing, will give you what you want. Please make sure you understand how the code that is provided to you works. Here is a new modification of the formula:

```
If( Row() == 1,
theLag = 100000;
theSum = 0;
);
If(
Row() <= theLag,
theSum = theSum + :x;
theAvg = theSum/Row(),
theAvg = ((theSum - :x[Row() - theLag]) + :x) / theLag
);
theAvg;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

I tried the suggested solution but it does not give the correct answer, couldyu please help

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You are correct, the formula was in error. I was not setting the value of "theSum" correctly. The following should get correct results:

```
If( Row() == 1,
theLag = 3;
theSum = 0;
);
If( Row() <= theLag,
theSum = theSum + :yield;
theAvg = theSum / Row();
,
theAvg = ((theSum - :yield[Row() - theLag]) + :yield) / theLag;
theSum = (theSum - :yield[Row() - theLag]) + :yield;
);
theAvg;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

Created:
May 28, 2019 5:54 AM
| Last Modified: May 28, 2019 5:59 AM
(6593 views)
| Posted in reply to message from txnelson 05-28-2019

yes, now it doesnt throw any error but I have to check if it will work for 10 million entries

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

Created:
May 28, 2019 8:01 AM
| Last Modified: May 28, 2019 8:18 AM
(6580 views)
| Posted in reply to message from jojmp 05-28-2019

It should work for 10,000,000 rows.

Please reply with the results of your testing.

Here is a faster version of the formula.....it removes the need for a second calculation of the Sum

```
If( Row() == 1,
theLag = 3;
theSum = 0;
);
If( Row() <= theLag,
theSum = theSum + :yield;
theAvg = theSum / Row();
,
theSum = (theSum - :yield[Row() - theLag]) + :yield;
theAvg = theSum / theLag;
);
theAvg;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to calculate moving average of column with 10 million entries ?

For the point and click folks out there like me you can get a moving average on a column by right clicking the header of the column of interest and selecting "New Formula Column" > Row > Moving Average. You will have to make another click or two to decide whether or not you want to use weighting, but it was pretty fast once I clicked OK for my simulated 10M row data table.

HTH

Bill

Article Labels

There are no labels assigned to this post.