Combining two series in pandas along their index

Question

I have two series in pandas.

series 1:

id        count_1
1            3
3           19
4           15
5            5
6            2

and series 2:

id        count_2
1           3
3           1
4           1
5           2
6           1

How do I combine the tables along the ids to form the below?

id        count_1    count_2
1            3        3
3           19        1
4           15        1
5            5        2
6            2        1

Andy Hayden · Accepted Answer · 2013-08-06T14:53:44.667

20

You can use concat:

In [11]: s1
Out[11]:
id
1      3
3     19
4     15
5      5
6      2
Name: count_1, dtype: int64

In [12]: s2
Out[12]:
id
1     3
3     1
4     1
5     2
6     1
Name: count_2, dtype: int64

In [13]: pd.concat([s1, s2], axis=1)
Out[13]:
    count_1  count_2
id
1         3        3
3        19        1
4        15        1
5         5        2
6         2        1

Note: if these were DataFrame (rather than Series) you could use merge:

In [21]: df1 = s1.reset_index()

In [22]: s1.reset_index()
Out[22]:
   id  count_1
0   1        3
1   3       19
2   4       15
3   5        5
4   6        2

In [23]: df2 = s2.reset_index()

In [24]: df1.merge(df2)
Out[24]:
   id  count_1  count_2
0   1        3        3
1   3       19        1
2   4       15        1
3   5        5        2
4   6        2        1

edited Aug 06 '13 at 14:53

answered Aug 06 '13 at 14:43

Andy Hayden

359,921
101
625
535

Thanks for this - in your first example the 'id' in col 1, sits in the row below the 'count_1' and 'count_2' col headings - to get them all in the same row do I just follow your second example? – user7289 Aug 06 '13 at 14:58
you can use reset_index, which moves the index to one of the columns. – Andy Hayden Aug 06 '13 at 15:04
@user7289 i.e. `pd.concat([s1, s2], axis=1).reset_index()` – Andy Hayden Aug 06 '13 at 15:09
Thanks Andy, that last method in comments works...I cant seem to get the merge to work though...sorted now :) – user7289 Aug 06 '13 at 15:18
What about merging 2 series with non overlapping index by their index? – Royi Apr 27 '21 at 18:19

Combining two series in pandas along their index

1 Answers1

Linked