Menu
I´m struggling with a task that should be simple, but it is not working as I thought it would. I have two numeric dataframes A and B with multiindex and columns below:
A = A B C D
X 1 AX1 BX1 CX1 DX1
2 AX2 BX2 CX2 DX2
3 AX3 BX3 CX3 DX3
Y 1 AY1 BY1 CY1 DY1
2 AY2 BY2 CY2 DY2
3 AY3 BY3 CY3 DY3
B = A B C D
X 1 a AX1a BX1a CX1a DX1a
b AX1b BX1b CX1b DX1b
c AX1c BX1c CX1c DX1c
2 a AX2a BX2a CX2a DX2a
b AX2b BX2b CX2b DX2b
c AX2c BX2c CX2c DX2c
3 a AX3a BX3a CX3a DX3a
b AX3b BX3b CX3b DX3b
c AX3c BX3c CX3c DX3c
Y 1 a AY1a BY1a CY1a DY1a
b AY1b BY1b CY1b DY1b
c AY1c BY1c CY1c DY1c
2 a AY2a BY2a CY2a DY2a
b AY2b BY2b CY2b DY2b
c AY2c BY2c CY2c DY2c
3 a AY3a BY3a CY3a DY3a
b AY3b BY3b CY3b DY3b
c AY3c BY3c CY3c DY3c ## Heading ##
I´d like to multiply A * B broadcasting over the innermost level of B, I want the resulting dataframe R, below:
R= A B C D
X 1 a (AX1a * AX1) (BX1a * BX1) (CX1a * CX1) (DX1a * DX1)
b (AX1b * AX1) (BX1b * BX1) (CX1b * CX1) (DX1b * DX1)
c (AX1c * AX1) (BX1c * BX1) (CX1c * CX1) (DX1c * DX1)
2 a (AX2a * AX2) (BX2a * BX2) (CX2a * CX2) (DX2a * DX2)
b (AX2b * AX2) (BX2b * BX2) (CX2b * CX2) (DX2b * DX2)
c (AX2c * AX2) (BX2c * BX2) (CX2c * CX2) (DX2c * DX2)
3 a (AX3a * AX3) (BX3a * BX3) (CX3a * CX3) (DX3a * DX3)
b (AX3b * AX3) (BX3b * BX3) (CX3b * CX3) (DX3b * DX3)
c (AX3c * AX3) (BX3c * BX3) (CX3c * CX3) (DX3c * DX3)
Y 1 a (AY1a * AY1) (BY1a * BY1) (CY1a * CY1) (DY1a * DY1)
b (AY1b * AY1) (BY1b * BY1) (CY1b * CY1) (DY1b * DY1)
c (AY1c * AY1) (BY1c * BY1) (CY1c * CY1) (DY1c * DY1)
2 a (AY2a * AY2) (BY2a * BY2) (CY2a * CY2) (DY2a * DY2)
b (AY2b * AY2) (BY2b * BY2) (CY2b * CY2) (DY2b * DY2)
c (AY2c * AY2) (BY2c * BY2) (CY2c * CY2) (DY2c * DY2)
3 a (AY3a * AY3) (BY3a * BY3) (CY3a * CY3) (DY3a * DY3)
b (AY3b * AY3) (BY3b * BY3) (CY3b * CY3) (DY3b * DY3)
c (AY3c * AY3) (BY3c * BY3) (CY3c * CY3) (DY3c * DY3)
I tried using pandas multiply function with level keyword by doing:
b.multiply(a, level=[0,1])
but it throws an error: "TypeError: Join on level between two MultiIndex objects is ambiguous"
What is the right way of doing this operation?
720
I'd simply use DF.reindex on the lesser shaped DF to match the index of that of the bigger DF's shape and forward fill the values present in it. Then do the multiplication.
B.multiply(A.reindex(B.index, method='ffill')) # Or method='pad'
Demo:
Prep up some data:
np.random.seed(42)
midx1 = pd.MultiIndex.from_product([['X', 'Y'], [1,2,3]])
midx2 = pd.MultiIndex.from_product([['X', 'Y'], [1,2,3], ['a','b','c']])
A = pd.DataFrame(np.random.randint(0,2,(6,4)), midx1, list('ABCD'))
B = pd.DataFrame(np.random.randint(2,4,(18,4)), midx2, list('ABCD'))
Small DF:
>>> A
A B C D
X 1 0 1 0 0
2 0 1 0 0
3 0 1 0 0
Y 1 0 0 1 0
2 1 1 1 0
3 1 0 1 1
Big DF:
>>> B
A B C D
X 1 a 3 3 3 3
b 3 3 2 2
c 3 3 3 2
2 a 3 2 2 2
b 2 2 3 3
c 3 3 3 2
3 a 3 3 2 3
b 2 3 2 3
c 3 2 2 2
Y 1 a 2 2 2 2
b 2 3 3 2
c 3 3 3 3
2 a 2 3 2 3
b 3 3 2 3
c 2 3 2 3
3 a 2 2 3 2
b 3 3 3 3
c 3 3 3 3
Multiplying them after making sure both share a common index axis across all levels:
>>> B.multiply(A.reindex(B.index, method='ffill'))
A B C D
X 1 a 0 3 0 0
b 0 3 0 0
c 0 3 0 0
2 a 0 2 0 0
b 0 2 0 0
c 0 3 0 0
3 a 0 3 0 0
b 0 3 0 0
c 0 2 0 0
Y 1 a 0 0 2 0
b 0 0 3 0
c 0 0 3 0
2 a 2 3 2 0
b 3 3 2 0
c 2 3 2 0
3 a 2 0 3 2
b 3 0 3 3
c 3 0 3 3
Now you can even supply the level parameter in DF.multiply for broadcasting to occur at those matching indices.
138
We are talking about broadcasting, thus I would like to bring in NumPy supported broadcasting here.
The solution code would look something like this -
def numpy_broadcasting(df0, df1):
m,n,r = map(len,df1.index.levels)
a0 = df0.values.reshape(m,n,-1)
a1 = df1.values.reshape(m,n,r,-1)
out = (a1*a0[...,None,:]).reshape(-1,a1.shape[-1])
df_out = pd.DataFrame(out, index=df1.index, columns=df1.columns)
return df_out
Basic idea :
1] Get views into the dataframe as multidimensional arrays. The multidimensionality is maintained according to the level structure of the multindex dataframe. Thus, the first dataframe would have three levels (including the columns) and the second one has four levels. Thus, we have a0 and a1 corresponding to the input dataframes df0 and df1, resulting in a0 and a1 having 3 and 4 dimensions respectively.
2) Now, comes the broadcasting part. We simply extend a0 to have 4 dimensions by introducing a new axis at the third position. This new axis would match up against the third axis from df1. This allows us to perform element-wise multiplication.
3) Finally, to get the output multindex dataframe, we simply reshape the product.
Sample run :
1) Input dataframes -
In [369]: df0
Out[369]:
A B C D
0 0 3 2 2 3
1 6 8 1 0
2 3 5 1 5
1 0 7 0 3 1
1 7 0 4 6
2 2 0 5 0
In [370]: df1
Out[370]:
A B C D
0 0 0 4 6 1 2
1 3 3 4 5
2 8 1 7 4
1 0 7 2 5 4
1 8 6 7 5
2 0 4 7 1
2 0 1 4 2 2
1 2 3 8 1
2 0 0 5 7
1 0 0 8 6 1 7
1 0 6 1 4
2 5 4 7 4
1 0 4 7 0 1
1 4 2 6 8
2 3 1 0 6
2 0 8 4 7 4
1 0 6 2 0
2 7 8 6 1
2) Output dataframe -
In [371]: df_out
Out[371]:
A B C D
0 0 0 12 12 2 6
1 9 6 8 15
2 24 2 14 12
1 0 42 16 5 0
1 48 48 7 0
2 0 32 7 0
2 0 3 20 2 10
1 6 15 8 5
2 0 0 5 35
1 0 0 56 0 3 7
1 0 0 3 4
2 35 0 21 4
1 0 28 0 0 6
1 28 0 24 48
2 21 0 0 36
2 0 16 0 35 0
1 0 0 10 0
2 14 0 30 0
In [31]: # Setup input dataframes of the same shape as stated in the question
...: individuals = list(range(2))
...: time = (0, 1, 2)
...: index = pd.MultiIndex.from_tuples(list(product(individuals, time)))
...: A = pd.DataFrame(data={'A': np.random.randint(0,9,6), \
...: 'B': np.random.randint(0,9,6), \
...: 'C': np.random.randint(0,9,6), \
...: 'D': np.random.randint(0,9,6)
...: }, index=index)
...:
...:
...: individuals = list(range(2))
...: time = (0, 1, 2)
...: P = (0,1,2)
...: index = pd.MultiIndex.from_tuples(list(product(individuals, time, P)))
...: B = pd.DataFrame(data={'A': np.random.randint(0,9,18), \
...: 'B': np.random.randint(0,9,18), \
...: 'C': np.random.randint(0,9,18), \
...: 'D': np.random.randint(0,9,18)}, index=index)
...:
# @DSM's solution
In [32]: %timeit B * A.loc[B.index.droplevel(2)].set_index(B.index)
1 loops, best of 3: 8.75 ms per loop
# @Nickil Maveli's solution
In [33]: %timeit B.multiply(A.reindex(B.index, method='ffill'))
1000 loops, best of 3: 625 µs per loop
# @root's solution
In [34]: %timeit B * np.repeat(A.values, 3, axis=0)
1000 loops, best of 3: 487 µs per loop
In [35]: %timeit numpy_broadcasting(A, B)
1000 loops, best of 3: 191 µs per loop
Note that I am not claiming this is the right way to do this operation, only that it's one way to do it. I've had issues figuring out the right broadcast pattern in the past myself. :-/
The short version is that I wind up doing the broadcasting manually, and creating an appropriately-aligned intermediate object:
In [145]: R = B * A.loc[B.index.droplevel(2)].set_index(B.index)
In [146]: A.loc[("X", 2), "C"]
Out[146]: 0.5294149302910357
In [147]: A.loc[("X", 2), "C"] * B.loc[("X", 2, "c"), "C"]
Out[147]: 0.054262618238601339
In [148]: R.loc[("X", 2, "c"), "C"]
Out[148]: 0.054262618238601339
This works by indexing into A using the matching parts of B, and then setting the index to match. If I were more clever I'd be able to figure out a native way to get this to work but I haven't yet. :-(
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
