Dividing each row by the previous one
First divide by shift
ed 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 NaN
s 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
A Better Way Than Looping and Calling Functions That Loop and Call Another Functions
Is There a Short-Hand for Nth Root of X in Python
Python: Searching for Common Values in Two Files
Sqlalchemy, Prevent Duplicate Rows
How to Create a Common Function to Execute a Python Script in Jenkins
Flask Installed, But Modulenotfounderror: No Module Named 'Flask'
Python: Assign Labels to Values in an Array
Pandas: Merge Data Frames on Datetime Index
Cursor.Fetchone() Returns None But Row in the Database Exists
How to Deal With Certificates Using Selenium
Splitting Dataframe into Multiple Dataframes
Typeerror: Unsupported Operand Type(S) for ** or Pow(): 'List' and 'Int'
How to Find the Unit Digits of a Specific Number
Using Continue in a Try and Except Inside While-Loop
Webscraping Financial Data from Morningstar
_Corrupt_Record Error When Reading a Json File into Spark
How to Skip Empty Dates (Weekends) in a Financial Matplotlib Python Graph
Fbprophet Installation Error - Failed Building Wheel for Fbprophet