Dividing by previous row in pandas with condition

user_12074357 0
Viewed 848

I'm trying to create a new column which is division of previous of row. But some of the rows have 0 value. So my conditionl statement is: If the division between 2 rows is infinity or nan, the value in Growth rate column will be 0. Else, it will be division with the previous row.

''' n=len(df) for j in range(0,n-1):

if ((df.iloc[j,4]/df.iloc[j-1,4]) == inf):
    df['GrowthRate']=0
else:
    df['GrowthRate'] = df.iloc[j,4]/df.iloc[j-1,4]
    j=j+1

'''

The output is all my values in the column are inf.

pandas conditional-statements python-3.x loops
2 Answers

if the value above is 0 then divide by np.inf, resulting in 0, else divide by previous value

col = df.iloc[:,4]
df['GrowthRate'] = col.div(col.where(col.eq(0), np.inf))

If you need fill NaN at the end: 

df['GrowthRate'] = col.div(col.where(col.eq(0), np.inf)).fillna(0)
user_118842370

Another solution using replace and fillna to deal with NaN and Inf:

import numpy as np

df['GrowthRate'] = (df.iloc[:, 4].div(df.iloc[:, 4].shift())
                    .fillna(0).replace([np.inf, -np.inf], 0))

Example

np.random.seed(2020)
df = pd.DataFrame(np.random.randint(-3, 4, (10, 5)))
print(df)

#    0  1  2  3  4
# 0 -3 -3  0  3  0
# 1  0  2  0 -3  2
# 2 -3 -3 -3 -1 -2
# 3  0  0 -1  3  0
# 4  3  2 -3  1  1
# 5 -3  3  1 -2 -2
# 6  2 -1 -2  3  2
# 7  2 -1  3  3  3
# 8  2  1  3  3  1
# 9 -1  0  1 -2  1

df['GrowthRate'] = (df.iloc[:, 4].div(df.iloc[:, 4].shift())
                    .fillna(0).replace([np.inf, -np.inf], 0))

[out]

   0  1  2  3  4  GrowthRate
0 -3 -3  0  3  0    0.000000
1  0  2  0 -3  2    0.000000
2 -3 -3 -3 -1 -2   -1.000000
3  0  0 -1  3  0   -0.000000
4  3  2 -3  1  1    0.000000
5 -3  3  1 -2 -2   -2.000000
6  2 -1 -2  3  2   -1.000000
7  2 -1  3  3  3    1.500000
8  2  1  3  3  1    0.333333
9 -1  0  1 -2  1    1.000000
user_102015800
Related