CODEWITHSUNDEEP
pythonpython-2.7pandas
Amber Z.
Amber Z.

Reputation: 379

Python offset column value with previous record value if the record meets a condition

I'm brand new to Python and am stuck on how to conditionally offset values. I've successfully been able to use the shift function when I just need to create a new column. However, this doesn't seem to work with a function.

Original df:

BEGIN   SPEED   SPEED_END
322     28      0
341     0       23
496     5       1
500     0       0
775     0       0
979     0       0
1015    0       0
1022    0       14
1050    11      6

I want the BEGIN value to be changed to the previous record BEGIN value and the SPEED value to be changed to the previous record SPEED value on records where SPEED=0 and the previous SPEED_END=0.

So the table above should be:

BEGIN   SPEED   SPEED_END
322     28      0
322     28      23
496     5       1
500     0       0
500     0       0
500     0       0
500     0       0
500     0       14
1050    11      6

I've tried a lot of different things. Currently, I've tried:

def cont(row,param):
    if row['SPEED'] == 0 and row['SPEED_END'].shift(1) == 0:
        val = row[param].shift(1)
    else:
        val = row[param]
    return val

df['BEGIN'] = df.apply(cont, param='BEGIN', axis=1)

But this gives me the error:

AttributeError: ("'float' object has no attribute 'shift'", u'occurred at index 0')

Any suggestions are appreciated!!

Upvotes: 4

Views: 1147

Answers (2)

root
root

Reputation: 33793

You can use mask and ffill:

begin_cond = (df['SPEED'] == 0) & (df['SPEED_END'].shift(1) == 0)
df['BEGIN'] = df['BEGIN'].mask(begin_cond).ffill().astype(int)

Essentially, mask will replace the values in df['BEGIN'] where begin_cond is True with NaN. Then, ffill will forward fill the NaN values with the last valid value in df['BEGIN'].

The resulting output:

   BEGIN  SPEED  SPEED_END
0    322     28          0
1    322      0         23
2    496      5          1
3    500      0          0
4    500      0          0
5    500      0          0
6    500      0          0
7    500      0         14
8   1050     11          6

Upvotes: 5

ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

I will propose a two-step solution that will SHOCK you.

df['begin_temp'] = df.begin.shift(1)
df['begin_shifted'] = df.ix[( df.SPEED== 0)  | (df.SPEED_END== 0), 'begin_temp']

and then

df.ix[df.begin_shifted.isnull(),'begin_shifted'] = df.ix[df.begin_shifted.isnull(),'begin']

Upvotes: 0

Related Questions