I am struggling doing a basic conditional elementwise multiplication between two dataframes. Let's assume I have the following two dataframes:
df1 = pd.DataFrame({'A': [-0.1,0.3,-0.4, 0.8,-0.5,-0.1,0.3,-0.4, 0.8,-1.2],'B': [-0.2,0.5,0.3,-0.5,0.1,-0.2,0.5,0.3,-0.5,0.9]},index=[0, 1, 2, 3,4,5,6,7,8,9])
df2=pd.DataFrame({'C': [-0.003,0.03848,-0.04404, 0.018,-0.1515,-0.02181,0.233,-0.0044, 0.01458,-0.015],'D': [-0.0152,0.0155,0.03,-0.0155,0.0151,-0.012,0.035,0.0013,-0.0005,0.009]},index=[0, 1, 2, 3,4,5,6,7,8,9])
The idea is to multiply df1 and df2.shift(-1)
(elementwise, not matrix multiplication) depending on the values of df1. If (df1>=0.50 or df1<=-0.50)
then I multiply df1 and df2.shift(-1). Otherwise, I just put 0.
The desired result in this example should be the following (with columns names being the column names of df1 as well as df1 index):
df3=pd.DataFrame({'A': [0,0,0, -0.1212,0.010905,0,0,0, -0.012,'NaN'],'B': [0,0.015,0,-0.00755,0,0,0.00065,0,-0.0045,'NaN']},index=[0, 1, 2, 3,4,5,6,7,8,9])
I tried the following code:
import numpy as np
import pandas as pd
df3=np.where((df1>=0.50 or df1 <=-0.50),df1*df2.shift(-1),0)
And I get The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). Thank you.
Use
|
for bitwiseOR
withDataFrame
constructor:Numpy solution should be faster: