Replace some values in a column with previous values from another column

ttoropov
ttoropov Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 8

Good day!

I am trying to replace some of the values in one column (empty ones) with values from another column but from previous row. Can't find a solution.

For example: I have columns "A" and "B". What should I do, if I need to replace empty cells A3, A7, A20 etc (where 3, 7, 20 are the numbers of rows from the top) with the values from B2, B6, B19 etc cells?

What would you recommend?

Best Answer

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,131 Neuron
    Answer ✓

    This can be done with a Window recipe. Assuming you have a dataset like this:

    image.png

    Create a Window recipe and set the Window Definition. You need to have a sort order to fetch the previous value so pick the column(s) that has your rows ordered properly:

    image.png

    Then set the aggregation to section to calculate the lag of the row (previous). You can also use the lead (next) if you want:

    image.png

    Run the recipe and you will have the output like this:

    image.png

    Now simply replace the value of A by B in a Prepare recipe using whatever logic you want.

Answers

Setup Info
    Tags
      Help me…