Merging a dataframe with a Series

Question

A co-worker of mine is trying to combine a matrix and a Series and is trying to see if there is a native pandas way to do so instead of using a loop.

Example if I had a dataframe that consisted of

1, 2, 3
4, 5, 6
7, 8, 9

and a Series with values

13, 14, 15

Then the desired result would be

1, 2, 3, 13
1, 2, 3, 14
1, 2, 3, 15
4, 5, 6, 13
4, 5, 6, 14
4, 5, 6, 15
7, 8, 9, 13
7, 8, 9, 14
7, 8, 9, 15

Is there a pandas specific method of achieving this effect. I am not sure what specifically to search to find this and a few attempts of what was similar enough in my head failed.

possibly related to: http://stackoverflow.com/questions/13269890/cartesian-product-in-pandas — cel, Jun 02 '15 at 13:27
Can we assume that the Series and the DataFrame use the same index values? — holdenweb, Jun 02 '15 at 13:29

score 2 · Accepted Answer · answered Jun 02 '15 at 14:01

You can do this with merge. If you want the full Cartesian product, you can do the following:

import pandas as pd
df = pd.DataFrame([[1,2,3], [4,5,6], [7,8,9]])
df['key'] = 0
ser = pd.DataFrame({'data': [13,14,15], 'key': [0] * 3})

result = pd.merge(df, ser, on = 'key').drop('key', axis = 1)

I've checked result and it looks like what you want. More info if you search "Brief primer on merge methods (relational algebra)" in the pandas docs.

score 0 · Answer 2 · answered Jun 02 '15 at 14:01

Someone had posted an answer that helped me get what I wanted, but then deleted it before i could come back to accept.

Basically what I needed to do was make the Series a dataframe and add a common column to both assuming these are called a and b i just

In [1]: import pandas as pd

In [2]: a = pd.DataFrame({1:[1,4,7],2:[2,5,8],3:[3,6,9]})

In [3]: a
Out[3]:
   1  2  3
0  1  2  3
1  4  5  6
2  7  8  9


In [5]: b = pd.DataFrame({4:[13,14,15]})

In [6]: b
Out[6]:
    4
0  13
1  14
2  15

In [7]: a["key"] = 1

In [8]: b["key"] = 1

In [9]: c = a.merge(b, on="key")

In [10]: c
Out[10]:
   1  2  3  key   4
0  1  2  3    1  13
1  1  2  3    1  14
2  1  2  3    1  15
3  4  5  6    1  13
4  4  5  6    1  14
5  4  5  6    1  15
6  7  8  9    1  13
7  7  8  9    1  14
8  7  8  9    1  15

In [11]: del c["key"]

In [12]: c
Out[12]:
   1  2  3   4
0  1  2  3  13
1  1  2  3  14
2  1  2  3  15
3  4  5  6  13
4  4  5  6  14
5  4  5  6  15
6  7  8  9  13
7  7  8  9  14
8  7  8  9  15

In [13]:

Merging a dataframe with a Series

2 Answers2