CODEWITHSUNDEEP
pythonpandasconcatenationseries
Kane Chew
Kane Chew

Reputation: 4123

Concatenate multiple pandas series efficiently

I understand that I can use combine_first to merge two series:

series1 = pd.Series([1,2,3,4,5],index=['a','b','c','d','e'])
series2 = pd.Series([1,2,3,4,5],index=['f','g','h','i','j'])
series3 = pd.Series([1,2,3,4,5],index=['k','l','m','n','o'])

Combine1 = series1.combine_first(series2)
print(Combine1

Output:

a    1.0
b    2.0
c    3.0
d    4.0
e    5.0
f    1.0
g    2.0
h    3.0
i    4.0
j    5.0
dtype: float64

What if I need to merge 3 or more series?

I understand that using the following code: print(series1 + series2 + series3)yields:

a   NaN
b   NaN
c   NaN
d   NaN
e   NaN
f   NaN
...
dtype: float64

Can I merge multiple series efficiently without using combine_first multiple times?

Thanks

Upvotes: 11

Views: 23529

Answers (4)

cs95
cs95

Reputation: 402932

Combine Series with Non-Overlapping Indexes

To combine series vertically, use pd.concat.

# Setup
series_list = [
    pd.Series(range(1, 6), index=list('abcde')),
    pd.Series(range(1, 6), index=list('fghij')),
    pd.Series(range(1, 6), index=list('klmno'))
]


pd.concat(series_list)

a    1
b    2
c    3
d    4
e    5
f    1
g    2
h    3
i    4
j    5
k    1
l    2
m    3
n    4
o    5
dtype: int64

Combine with Overlapping Indexes

series_list = [
    pd.Series(range(1, 6), index=list('abcde')),
    pd.Series(range(1, 6), index=list('abcde')),
    pd.Series(range(1, 6), index=list('kbmdf'))
]

If the Series have overlapping indices, you can either combine (add) the keys,

pd.concat(series_list, axis=1, sort=False).sum(axis=1)

a     2.0
b     6.0
c     6.0
d    12.0
e    10.0
k     1.0
m     3.0
f     5.0
dtype: float64

Alternatively, just drop duplicates values on the index if you want to take only the first/last value (when there are duplicates).

res = pd.concat(series_list, axis=0)
# keep first value
res[~res.index.duplicated(keep='first')]
# keep last value
res[~res.index.duplicated(keep='last')]

Upvotes: 19

White
White

Reputation: 637

Agree with what @codespeed has pointed out in his answer.

I think it will depend on user needs. If series index are confirmed with no overlapping, concat will be a better option. (as original question posted, there is no index overlapping, then concat will be a better option)

If there is index overlapping, you might need to consider how to handle overlapping, which value to be overwritten. (as example provided by codespeed, if index are matching to different values, need to be careful about combine_first)

i.e. (note series3 is same as series1, series2 is same as series4)

import pandas as pd
import numpy as np


series1 = pd.Series([1,2,3,4,5],index=['a','b','c','d','e'])
series2 = pd.Series([2,3,4,4,5],index=['a','b','c','i','j'])
series3 = pd.Series([1,2,3,4,5],index=['a','b','c','d','e'])
series4 = pd.Series([2,3,4,4,5],index=['a','b','c','i','j'])


print(series1.combine_first(series2))



a    1.0
b    2.0
c    3.0
d    4.0
e    5.0
i    4.0
j    5.0
dtype: float64



print(series4.combine_first(series3))



a    2.0
b    3.0
c    4.0
d    4.0
e    5.0
i    4.0
j    5.0
dtype: float64

Upvotes: 1

miradulo
miradulo

Reputation: 29720

Presuming that you were using the behavior of combine_first to prioritize the values of the series in order as combine_first is meant for, you could succinctly make multiple calls to it with a lambda expression. 

from functools import reduce
l_series = [series1, series2, series3]
reduce(lambda s1, s2: s1.combine_first(s2), l_series)

Of course if the indices are unique as in your current example, you can simply use pd.concat instead.

Demo

series1 = pd.Series(list(range(5)),index=['a','b','c','d','e'])
series2 = pd.Series(list(range(5, 10)),index=['a','g','h','i','j'])
series3 = pd.Series(list(range(10, 15)),index=['k','b','m','c','o'])

from functools import reduce
l_series = [series1, series2, series3]
print(reduce(lambda s1, s2: s1.combine_first(s2), l_series))

# a     0.0
# b     1.0
# c     2.0
# d     3.0
# e     4.0
# g     6.0
# h     7.0
# i     8.0
# j     9.0
# k    10.0
# m    12.0
# o    14.0
# dtype: float64

Upvotes: 5

Vaishali
Vaishali

Reputation: 38415

You would use combine_first if you want one series's values prioritized over the other. Its usually used to fill the missing values in the first series. I am not sure whats the expected output in your example but looks like you can use concat

pd.concat([series1, series2, series3])

You get

a    1
b    2
c    3
d    4
e    5
f    1
g    2
h    3
i    4
j    5
k    1
l    2
m    3
n    4
o    5

Upvotes: 0

Related Questions