Dataframe Manipulation - Capturing A Change In Value

I currently have a dataframe as below, which shows a change in position, add 1 unit, subtract 1 unit or do nothing (0). I'm looking to create a second dataframe with the net posi

Solution 1:

Here is a vectorized solution:

df['CiP'].where(df['CiP'].replace(to_replace=0, method='ffill').diff(), 0).cumsum()

Explanation:

• The call to replace replaces 0 values by the preceding non-zero value.
• The call to diff then points to actual changes in position.
• The call to where ensures that values that do not really change are replaced by 0.
• After this treatment, cumsum just works.

---

Edit: If you have multiple columns, then define a function as above and apply it.

def position(series):
    return series.where(series.replace(to_replace=0, method='ffill').diff(), 0).cumsum()

df[list_of_columns].apply(position)

This could be slightly faster than explicitly looping over the columns.