Divide current row with previous row in Dataiku

Solved!
YiXiangChong
Level 2
Divide current row with previous row in Dataiku

Hi I have a question. How does one divide the current row with previous row in Dataiku? For example, let's say I have a column that contains stock prices, and I would like to calculate the change in price by dividing current row with previous row and minusing 1. How does one do that?

0 Kudos
1 Solution
YiXiangChong
Level 2
Author

Here was how I solved it: I used the "Window" recipe. First, I partitioned by the correct column at which to perform subset operations on. Next, I order column by datetime (if there is one). Finally, I selected "Lag" for the column Price under Aggregations to get the lag. Next, using a "Prepared" recipe and "formula", I divided the current Price column with the newly created "Lag" column to get the change in price as wanted. 

View solution in original post

3 Replies
tgb417

@YiXiangChong 

Take a look at the window visual recipie.  

Training materials that might be helpful can be found here.

https://academy.dataiku.com/data-pipelines/513267

Hope that helps

The idea is that you would order by some value like a trading day. You might group by some other value like a specific stock.  Then you would want the lag value from the earlier day's trading.  In a separate step, you would then do your arithmetic to get your percent change.  Or you might do this in a custom agrigation.  

Hope this helps.

--Tom
YiXiangChong
Level 2
Author

 Thank you for your answer. I did manage to solve it

YiXiangChong
Level 2
Author

Here was how I solved it: I used the "Window" recipe. First, I partitioned by the correct column at which to perform subset operations on. Next, I order column by datetime (if there is one). Finally, I selected "Lag" for the column Price under Aggregations to get the lag. Next, using a "Prepared" recipe and "formula", I divided the current Price column with the newly created "Lag" column to get the change in price as wanted.