Binary operations don't broadcast across multiindex

[roblevy]

related #6360

Based on this SO question

Consider the following two Series:

x = pd.DataFrame({'year':[1,1,1,1,2,2,2,2],
                  'country':['A','A','B','B','A','A','B','B'],
                  'prod':[1,2,1,2,1,2,1,2],
                  'val':[10,20,15,25,20,30,25,35]})
x = x.set_index(['year','country','prod']).squeeze()

y = pd.DataFrame({'year':[1,1,2,2],'prod':[1,2,1,2],
                  'mul':[10,0.1,20,0.2]})
y = y.set_index(['year','prod']).squeeze()

which look like:

    year  country  prod
    1     A        1       10
                   2       20
          B        1       15
                   2       25
    2     A        1       20
                   2       30
          B        1       25
                   2       35

year  prod
1     1       10.0
      2        0.1
2     1       20.0
      2        0.2

I find it to be an extremely common task, to perform binary operations by distributing the values of y over a particular level of x. For example, I'd like to multiply all values of product 1 in year 1 by 10.0, regardless of country.

The required result is therefore as follows:

    year  country  prod
    1     A        1       100.0
                   2       2.0
          B        1       150.0
                   2       2.5
    2     A        1       400.0
                   2       6.0
          B        1       500.0
                   2       7.0

The binary operation .mul() doesn't work as expected:

>>> x.mul(y, level=['year','prod'])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/series.py", line 334, in f
    return self._binop(other, op, level=level, fill_value=fill_value)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/series.py", line 2075, in _binop
    this, other = self.align(other, level=level, join='outer')
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/series.py", line 2570, in align
    return_indexers=True)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/index.py", line 954, in join
    return_indexers=return_indexers)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/index.py", line 1058, in _join_level
    raise Exception('Join on level between two MultiIndex objects '
Exception: Join on level between two MultiIndex objects is ambiguous

To create the required result, the user currently has to do this:

x = x.reset_index('country').sort_index()
x.val = x.val * y
x = x.reset_index().set_index(['year',
                               'country',
                               'prod']).sortlevel()

[jreback]

@roblevy can you put your solution in the top section as well (then its easy to create expected result)

[roblevy]

Done!

[jreback]

gr8! love for you to work on this! otherwise prob won't be tackled for a while. Its not too tricky and would get you a lot of exposure to the code.

[roblevy]

:D Great

On 5 December 2013 23:18, jreback notifications@github.com wrote:

gr8! love for you to work on this! otherwise prob won't be tackled for a
while. Its not too tricky and would get you a lot of exposure to the code.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/5645#issuecomment-29949006
.

I think the example result has editing errors in it, where did the 400 come from? doesn't match your
description of the operation.

A very powerful operation to have added. Is there a sugary way to do it you had in mind?
I like the idea anyway.

[roblevy]

Hi @y-p. The example result is correct.

The 400 is x[year 2, country A, product 1] * y[year 2, product 1] = 20 * 20 = 400

Notice how the values of x are multiplied by different numbers from y depending on year and product.

As far as I'm concerned, this should "just work" when the user does x.mul(y)

Thanks, I see now.

[0o-de-lally]

@roblevy @jreback This feature is an important component to a project i'm working on. Any chance we can get this implemented on the .14 release as well? I see that a related bug was fixed for the same release. Seems like they belong together. How can I help get this released sooner?

[jreback]

@keyscores well, this is dependent on #6360 so that needs to be fixed first
that said, you can simply use the soln above. Its just as efficient, if not syntax friendly,
but you can wrap in a function

of course a PR would be welcom

[0o-de-lally]

@jreback I'll try the solution above. I can donate a small bounty as an incentive to move #6360 up to v0.14 , would that help? :)

[jreback]

@keyscores unfortunately no....:) that IS being worked on though

[0o-de-lally]

Ok it was worth a try. At least you know you have a cheerleader for that fix. Would love to see it in .14 :)