CODEWITHSUNDEEP
pythonpandas
Zhubarb
Zhubarb

Reputation: 11895

Column operation conditioned on previous row

I presume similar questions exist, but could not locate them. I have Pandas 0.19.2 installed. I have a large dataframe, and for each row value I want to carry over the previous row's value for the same column based on some logical condition.

Below is a brute-force double for loop solution for a small example. What is the most efficient way to implement this? Is it possible to solve this in a vectorised manner?

import pandas as pd
import numpy as np

np.random.seed(10)
df = pd.DataFrame(np.random.uniform(low=-0.2, high=0.2, size=(10,2) ))
print(df)

for col in df.columns:
    prev = None
    for i,r in df.iterrows():
        if prev is not None:
            if (df[col].loc[i]<= prev*1.5) and (df[col].loc[i]>= prev*0.5):
                df[col].loc[i] = prev

        prev = df[col].loc[i]
print(df)

Output:

          0         1
0  0.108528 -0.191699
1  0.053459  0.099522
2 -0.000597 -0.110081
3 -0.120775  0.104212
4 -0.132356 -0.164664
5  0.074144  0.181357
6 -0.198421  0.004877
7  0.125048  0.045010
8  0.125048 -0.083250
9  0.125048  0.085830

EDIT: Please note one value can be carried over multiple times, so long as it satisfies the logical condition.

Upvotes: 2

Views: 56

Answers (2)

Zhubarb
Zhubarb

Reputation: 11895

I came up with this:

keep_going = True
while keep_going:
    df = df.mask((df.diff(1) / df.shift(1)<0.5) & (df.diff(1) / df.shift(1)> -0.5) & (df.diff(1) / df.shift(1)!= 0)).ffill()
    trimming_to_do = ((df.diff(1) / df.shift(1)<0.5) & (df.diff(1) / df.shift(1)> -0.5) & (df.diff(1) / df.shift(1)!= 0)).values.any()
    if not trimming_to_do:
        keep_going= False

which gives the desired result (at least for this case):

print(df)
          0         1
0  0.108528 -0.191699
1  0.053459  0.099522
2 -0.000597 -0.110081
3 -0.120775  0.104212
4 -0.120775 -0.164664
5  0.074144  0.181357
6 -0.198421  0.004877
7  0.125048  0.045010
8  0.125048 -0.083250
9  0.125048  0.085830

Upvotes: 0

BallpointBen
BallpointBen

Reputation: 13780

prev = df.shift()
replace_mask = (0.5 * prev <= df) & (df <= 1.5 * prev)
df = df.where(~replace_mask, prev)

Upvotes: 1

Related Questions