I have two pandas DataFrames, with one of them having index and columns that are subsets of the other. For example:
DF1 =
date a b c
20170101 1.0 2.2 3
20170102 2.1 5.2 -3.0
20170103 4.2 1.8 10.0
...
20170331 9.8 5.1 4.5
DF2 =
date a c
20170101 NaN 2.1
20170103 4 NaN
What I want is element-wise multiplication by matching both the index and column. i.e. only DF1[20170103]['c'] will be multiplied with DF2[20170103]['c'], etc.
The resulting DF should have the same dimension as the bigger one (DF1), with missing values in DF2 set as the original DF1 value:
result DF =
date a b c
20170101 1.0 2.2 6.3
20170102 2.1 5.2 -3.0
20170103 16.8 1.8 10.0
...
20170331 9.8 5.1 4.5
What's the best/fastest way to do this? The real-life matrices are huge, and DF2 is relatively sparse.
I think you need vectorized function mul:
df = DF1.mul(DF2, fill_value=1)
print (df)
a b c
date
20170101 1.0 2.2 6.3
20170102 2.1 5.2 -3.0
20170103 16.8 1.8 10.0
20170331 9.8 5.1 4.5