import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randn(5, 3), columns=['A', 'B', 'C'])
df2 = pd.DataFrame(np.random.randn(5, 3), columns=['X','Y','Z'])
I can easily set the values in df to zero if they are less than a constant:
df[df < 0.0] = 0.0
can someone tell me how to instead compare to a column in a different dataframe? I assumed this would work, but it does not:
df[df < df2.X] = 0.0
IIUC you need to use lt and pass axis=0 to compare column-wise:
In [83]:
df = pd.DataFrame(np.random.randn(5, 3), columns=['A', 'B', 'C'])
df2 = pd.DataFrame(np.random.randn(5, 3), columns=['X','Y','Z'])
df
Out[83]:
A B C
0 2.410659 -1.508592 -1.626923
1 -1.550511 0.983712 -0.021670
2 1.295553 -0.388102 0.091239
3 2.179568 2.266983 0.030463
4 1.413852 -0.109938 1.232334
In [87]:
df2
Out[87]:
X Y Z
0 0.267544 0.355003 -1.478263
1 -1.419736 0.197300 -1.183842
2 0.049764 -0.033631 0.343932
3 -0.863873 -1.361624 -1.043320
4 0.219959 0.560951 1.820347
In [86]:
df[df.lt(df2.X, axis=0)] = 0
df
Out[86]:
A B C
0 2.410659 0.000000 0.000000
1 0.000000 0.983712 -0.021670
2 1.295553 0.000000 0.091239
3 2.179568 2.266983 0.030463
4 1.413852 0.000000 1.232334
