CODEWITHSUNDEEP
pythonpandas
Roby
Roby

Reputation: 2061

Pandas DataFrame multiplication with respect to MultiIndex

I want to multiplicate the following two DataFrames with respect to the MultiIndex foreach dimension of the second DataFrame

base = pd.DataFrame({'date':pd.date_range('2000-01-01', periods=3),
                    'id':[1,2,1],
                    'V1':[.3,.3,.1]}).set_index(['date','id'])
print (base)
                V1
date       id          
2000-01-01 1   0.3
2000-01-01 2   0.1
2000-01-02 1   0.3
2000-01-02 2   0.1


ref = pd.DataFrame({'1':[1,2],'2':[1,2]}, index=[1,2])
ref.index.name = 'id'
print (ref)
    1  2
id
1   2  1  
2   1  2

And archive this:

                 V1
date       id          
2000-01-01 1   (2*0.3 + 1*0.1)
2000-01-01 2   (2*0.1 + 1*0.3)
2000-01-01 1   (2*0.3 + 1*0.1)
2000-01-01 2   (2*0.1 + 1*0.3)

I tried it with this code snipped (foreach id):

base.unstack(level='id').apply(lambda x: np.asarray(x) * np.asarray(ref.loc[??]), axis=1).stack().groupby(level='date').sum()

Upvotes: 1

Views: 317

Answers (1)

piRSquared
piRSquared

Reputation: 294328

Since you want products and sums... I think dot product.

base.V1.unstack().dot(ref).stack()

date         
2000-01-01  1    0.7
            2    0.5
2000-01-02  1    0.7
            2    0.5
dtype: float64

setup
This provides the data the OP shows

base = pd.DataFrame(
    dict(V1=[.3, .1] * 2),
    pd.MultiIndex.from_product(
        [pd.to_datetime(['2000-01-01', '2000-01-02']), [1, 2]])
)

ref = pd.DataFrame({1:[2,1],2:[1,2]}, index=pd.Index([1, 2], name='id'))

Upvotes: 1

Related Questions