How can I combine two Pandas DataFrames with different, non-overlapping MultiIndexes?

user_1192393 0
Viewed 303

I have two DataFrames, each with a MultiIndex. The index columns have different names in the two DataFrames (in this minimal example, there is only a single index column in each of the DataFrames, but in a real application there could be multiple):

>>> print(flavor)
           Price
Flavor          
Vanilla      2.0
Chocolate    3.0

>>> print(topping)
           Price
Topping         
Syrup        0.4
Sprinkles    0.5

I want to combine the DataFrames by adding the "price" columns for all combinations. Expected result (the order of the rows or index columns is not important):

                     Price
Flavor    Topping
Vanilla   Syrup        2.4
          Sprinkles    2.5
Chocolate Syrup        3.4
          Sprinkles    3.5

This seems like a fairly straightforward operation. But apparently, I'm missing something, because none of these work:

combined = flavor + topping
combined = flavor.add(topping)
combined = flavor.combine(topping, lambda a, b: a+b)

Instead, I get an exception:

ValueError: cannot join with no overlapping index names

Full example:

import pandas as pd

flavor_index = pd.MultiIndex.from_tuples([("Vanilla",), ("Chocolate",)], names=["Flavor"])
flavor = pd.DataFrame({"Price": [2.0, 3.0]}, index=flavor_index)
print(flavor)

topping_index = pd.MultiIndex.from_tuples([("Syrup",), ("Sprinkles",)], names=["Topping"])
topping = pd.DataFrame({"Price": [0.4, 0.5]}, index=topping_index)
print(topping)

# ValueError: cannot join with no overlapping index names
# combined = flavor + topping
# combined = flavor.add(topping)
combined = flavor.combine(topping, lambda a, b: a+b)
print(combined)
pandas dataframe python
3 Answers

Create a multilevel index in topping by appending each unique flavor, then add flavor and topping dataframe on level=0

flavor.add(pd.concat({f:topping for f in flavor.index}), level=0)

                     Price
          Topping         
Vanilla   Syrup        2.4
          Sprinkles    2.5
Chocolate Syrup        3.4
          Sprinkles    3.5
user_128331660

You want the multiindex to be the cartesian product of the two indices. Since flavor and topping in your example have multiindices which really should be single indices, let's create a multiindex based on the first levels of the respective multiindices.

joint_index = pd.MultiIndex.from_product([flavor.index.get_level_values(0), topping.index.get_level_values(0)])

Then let's create the basic format of the dataframe you want

df = pd.DataFrame(index=joint_index, columns=['Price'])

and then populate it

for f, t in df.index:
    df.loc[(f, t), 'Price'] = flavor.loc[f,'Price'] + topping.loc[t,'Price']

The final result:

>>> df
                    Price
Flavor    Topping        
Vanilla   Syrup       2.4
          Sprinkles   2.5
Chocolate Syrup       3.4
          Sprinkles   3.5

as desired.

user_106076080

The best way I was able to find is to add a dummy index level (with constant value):

flavor  = pd.concat([flavor],  keys=['dummy'], names=['DUMMY'])
topping = pd.concat([topping], keys=['dummy'], names=['DUMMY'])

This gives (and analogously for topping):

>>> print(flavor)
                 Price
DUMMY Flavor          
dummy Vanilla      2.0
      Chocolate    3.0

I can then use any of the following methods to add the two DataFrames:

combined = flavor + topping
combined = flavor.add(topping)
combined = flavor.combine(topping, lambda a, b: a+b)

And finally, I remove the dummy column:

combined.index = combined.index.droplevel("DUMMY")

The result is as expected:

                     Price
Flavor    Topping         
Vanilla   Syrup        2.4
          Sprinkles    2.5
Chocolate Syrup        3.4
          Sprinkles    3.5

Why it doesn't work without the dummy column, I don't know.

In my application, I ended up adding a dummy column anyway (to work around another Pandas limitation), so that also took care of this problem.

user_11923930
Related