Dividing Each Row by the Previous One

Dividing each row by the previous one

First divide by shifted values per groups:

df['ratio'] = df['value'].div(df.groupby('city')['value'].shift(1))
print (df)
city hour value ratio
0 NY 0 12 NaN
1 NY 12 24 2.0
2 LA 0 3 NaN
3 LA 12 9 3.0

Then remove NaNs and select only city and ratio column:

df = df.dropna(subset=['ratio'])[['city', 'ratio']]
print (df)
city ratio
1 NY 2.0
3 LA 3.0

Dividing each row by the previous row

With your shown samples, please try following. Simple explanation would be, creating 2 variables m1 and m2. Where m1 has everything from CSCO column to rest of the columns with shift shifting rows with 1. In m2 having everything from CSCO column to till last column value, finally dividing them to get values.

m1 = df.loc[:,'CSCO':].shift()
m2 = df.loc[:,'CSCO':]
df.loc[:,'CSCO':] = m2/m1

Output of DataFrame will be as follows:

         Date      CSCO  INTC      MSFT       WBA
0 2003-01-03 NaN NaN NaN NaN
1 2003-01-04 0.900000 1.0 0.750000 0.666667
2 2003-01-05 0.888889 1.0 0.833333 0.750000

How to divide a row by the contents of the previous row with using loops

Use, div, shift, and fillna:

df.div(df.shift(1)).fillna(df).astype(int)

Output:

   A   B
0 1 10
1 2 2
2 4 8
3 4 3

Python Pandas Fast Way to Divide Row Value by Previous Value

Use Series.shift by col1 column with Series.div for division:

df["result1"] = np.log(df["col1"].div(df["col1"].shift()))
#alternative
#df["result1"] = np.log(df["col1"] / df["col1"].shift())
print (df)
col1 result result1
0 5 NaN NaN
1 0 -inf -inf
2 3 inf inf
3 3 0.000000 0.000000
4 7 0.847298 0.847298
5 9 0.251314 0.251314
6 3 -1.098612 -1.098612
7 5 0.510826 0.510826
8 2 -0.916291 -0.916291
9 4 0.693147 0.693147

I test both solutions:

np.random.seed(0)

col1 = np.random.randint(0,10,size=10000)
df = pd.DataFrame({'col1':col1})


In [128]: %timeit df["result1"] = np.log(df["col1"] / df["col1"].shift())
865 µs ± 139 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [129]: %timeit df.assign(result=lambda x: np.log(x.col1.pct_change() + 1))
1.16 ms ± 11.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


In [130]: %timeit df["result1"] = np.log(df["col1"].pct_change() + 1)
1.03 ms ± 14.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


np.random.seed(0)

col1 = np.random.randint(0,10,size=100000)
df = pd.DataFrame({'col1':col1})

In [132]: %timeit df["result1"] = np.log(df["col1"] / df["col1"].shift())
3.7 ms ± 189 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [133]: %timeit df.assign(result=lambda x: np.log(x.col1.pct_change() + 1))
6.31 ms ± 545 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)


In [134]: %timeit df["result1"] = np.log(df["col1"].pct_change() + 1)
3.75 ms ± 269 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Dividing each item of a row by a specific value

Try this

library(data.table)
setDT(df)[,.SD/particular_number]
#> A B C D E particular_number
#> 1: 0.1818182 0.36363636 0.3181818 4.4545455 0.04545455 1
#> 2: 0.1470588 1.32352941 0.1176471 0.9411765 0.08823529 1
#> 3: 0.4347826 0.08695652 0.0000000 0.5217391 0.47826087 1

Created on 2021-06-04 by the reprex package (v2.0.0)

Dividing by previous row in pandas with condition

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

SQL Divide previous row balance by current row balance and insert that value into current rows column Growth

I am assuming your data is already prepared as shown in the table. Now you can try Lead() function to resolve your issue. Remember format() function is used for taking only two precision.

SELECT *,
FORMAT((ISNULL(LEAD(Balance,1) OVER (ORDER BY RowNum), 1)/Balance),'N2') Calculation
FROM #MonthlytLoanBalance2

Divide a value in a Dataframe column with the previous value in the same column

Use Series.shift instead of apply:

df['D'] = df['C'] / df['C'].shift()

# index A B C D
# 0 3 2 5 NaN
# 1 4 7 6 1.200000
# 2 2 4 8 1.333333

Optionally chain Series.fillna if you want 0 instead of NaN:

df['D'] = df['C'].div(df['C'].shift()).fillna(0)

# index A B C D
# 0 3 2 5 0.000000
# 1 4 7 6 1.200000
# 2 2 4 8 1.333333


Related Topics



Leave a reply



Submit