ccm ccm -4 years ago 126
Python Question

Multiplying a DataFrame with duplicate index values

I am trying to multiply a DataFrame with a Series, where the DataFrame has the exact same index as the Series, albeit with duplicate index values. The desired result is that each row in the DataFrame is multiplied by the corresponding value in the Series, based on the values of the indices.

Multiplying a Series with duplicate index values works perfectly:

import pandas as pd
weights = pd.Series([10, 100], [0, 1])
s = pd.Series([1, 2, 3, 4], [0, 0, 1, 1])
s.mul(weights)


yielding the expected result:

0 10
0 20
1 300
1 400


Multiplying a DataFrame with duplicate index values, however, produces a ValueError: cannot reindex from a duplicate axis:

df = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [-1, -2, -3, -4]}, [0, 0, 1, 1])
df.mul(weights, axis=0)
...
ValueError: cannot reindex from a duplicate axis


How can I achieve the following result?

a b
0 10 -10
0 20 -20
1 300 -300
1 400 -400


Edit:

One option is to reindex the weights Series first:

df.mul(weights.reindex(df.index, method='ffill'), axis=0)

Answer Source

How about using apply method so that you multiply the data frame column by column?

df.apply(lambda col: col.mul(weights))

#     a    b
#0   10  -10
#0   20  -20
#1  300 -300
#1  400 -400
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download