1

Consider the following two series.

Let x be:

x
a   10
b   20
c   30
Name: x_value

And let y be:

y
d   100
e   200
Name: y_value

Ideally, the result would have a MultiIndex along with the cartesian product of the series' cross values:


      x_value y_value
x  y
a  d    10      100
   e    10      200
b  d    20      100
   e    20      200
c  d    30      100
   e    30      200

I have seen similar questions (e.g. cartesian product in pandas) about cross merge, but I haven't found anything about Series so far (let alone a MultiIndex of initial indices approach).

The part that seems troublesome to me is how I'd get to work with Series, instead of DataFrames.

tdy
  • 36,675
  • 19
  • 86
  • 83
theodosis
  • 894
  • 5
  • 15

2 Answers2

4

pd.merge() works on Series, but it doesn't keep the index.

df = pd.merge(x, y, how='cross')
df

   x_value  y_value
0       10      100
1       10      200
2       20      100
3       20      200
4       30      100
5       30      200

You can just make the MultiIndex yourself:

df.index = pd.MultiIndex.from_product([x.index, y.index], names=['x', 'y'])
df

     x_value  y_value
x y                  
a d       10      100
  e       10      200
b d       20      100
  e       20      200
c d       30      100
  e       30      200
wjandrea
  • 28,235
  • 9
  • 60
  • 81
  • This is straightforward. The only part that I cannot completely understand is how would the MultiIndex that we construct afterwards align correctly with the values. Is there some behind the scenes mechanism that I'm missing? Thanks! – theodosis Dec 21 '22 at 21:49
  • @ttsak I'm not really sure what you're asking. When you take a Cartesian product of the values and a Cartesian product of the indexes, they have to align; that's just how Cartesian products work. – wjandrea Dec 21 '22 at 21:55
  • My question might be rather thoughtless. My only concern was that some indices would be mistakenly aligned with values from other pairs. But, I guess that behind the scenes, both `MultiIndex.from_product` and `cross merge` respect the initial ordering of indices / values? – theodosis Dec 21 '22 at 22:02
  • 1
    @ttsak Yes, ordering is respected – wjandrea Dec 21 '22 at 22:07
3

Another solution is to use reset_index and merge, then set_index multiindex:

s1 = pd.Series([10,20,30], index=[*'abc'], name='x_values')
s2 = pd.Series([100, 200], index=[*'de'], name='y_values')

s1.reset_index().merge(s2.reset_index(), how='cross').set_index(['index_x', 'index_y'])

Output:

                 x_values  y_values
index_x index_y                    
a       d              10       100
        e              10       200
b       d              20       100
        e              20       200
c       d              30       100
        e              30       200
Scott Boston
  • 147,308
  • 15
  • 139
  • 187