Pandonic way of adding two Series with respect to bin-membership of index

user_10909217 0
Viewed 59

Setup

>>> import pandas as pd
>>> import numpy as np
>>> s1 = pd.Series([100, 1000, 10000], index=[0, 2, 5])
s1
0      100
2     1000
5    10000
dtype: int64
>>> s2 = pd.Series(np.arange(7)*10)
>>> s2
0     0
1    10
2    20
3    30
4    40
5    50
6    60
dtype: int32

Assumptions:

  • The index of s1 is guaranteed to be monotonically increasing.
  • There is no index in s2 smaller than the minimum index of s1.

Clarification: The index of s2 cannot be assumed to have any particular order!

Desired result

I want to add the values of s1 to the values of s2 as follows (see comments for explanation):

>>> result
0      100 # 100 + 0, because index 0 is in [0, 2)
1      110 # 100 + 10, because index 1 is in [0, 2)
2     1020 # 1000 + 20, because index 2 is in [2, 5)
3     1030 # 1000 + 20, because index 3 is in [2, 5)
4     1040 # 1000 + 40, because index 4 is in [2, 5)
5    10050 # 10000 + 50, because index 5 is in [5, inf)
6    10060 # 10000 + 50, because index 6 is in [5, inf)
dtype: int64

Attempt

I created versions of s1 and s2 where the bins are the index.

>>> edges = [*s1.index, np.inf]
>>> s1_binned = pd.Series(s1.values, index=pd.cut(s1.index, bins=edges, right=False))
>>> s2_binned = pd.Series(s2.values, index=pd.cut(s2.index, bins=edges, right=False))
s1_binned
[0.0, 2.0)      100
[2.0, 5.0)     1000
[5.0, inf)    10000
dtype: int64
>>> s2_binned
[0.0, 2.0)     0
[0.0, 2.0)    10
[2.0, 5.0)    20
[2.0, 5.0)    30
[2.0, 5.0)    40
[5.0, inf)    50
[5.0, inf)    60
dtype: int32

Then I indexed into s1 with the index of s2 to get the values to add.

>>> to_add = s1_binned[s2_binned.index]
>>> to_add
[0.0, 2.0)      100
[0.0, 2.0)      100
[2.0, 5.0)     1000
[2.0, 5.0)     1000
[2.0, 5.0)     1000
[5.0, inf)    10000
[5.0, inf)    10000
dtype: int64

Finally I can add the values of to_add to s2.

>>> s2 + to_add.values
0      100
1      110
2     1020
3     1030
4     1040
5    10050
6    10060
dtype: int64

I have the feeling that there is a better solution, but I don't have much experience with "mapping" values to bins.

pandas python
2 Answers

Your feeling was correct - there is a more pandonic way:

s2+s1.reindex_like(s2).fillna(method='ffill')

Let's break it down:

  • s1.reindex_like(s2) returns a series whose index is like s2, with NaN where new indices were added (i.e. empty rows):

  • fillna(method='ffill') fills each of those empty rows with the previous non-empty value

EDITED: In comments, OP explained that s2's index is not assumed to be sorted.
For example:

import pandas as pd
import numpy as np
s1 = pd.Series([100, 1000, 10000], index=[0, 2, 5])
s2 = pd.Series(np.arange(7)*10)
s2 = s2[[2,3,5,1,0,4,6]]

So that s2 is

2    20
3    30
5    50
1    10
0     0
4    40
6    60
dtype: int64

Apparently, my method still works:

s2+s1.reindex_like(s2, method='ffill')

Returns

2     1020
3     1030
5    10050
1      110
0      100
4     1040
6    10060
dtype: int64

Which, if I understand correctly, is the desired output.

user_88746960

Use Series.add with new Series created by Series.reindex with method='ffill':

s1 = s1.sort_index()
s2 = s2.sort_index()

s = s2.add(s1.reindex(s2.index, method='ffill'))
#similar solution
#s = s2.add(s1.reindex_like(s2, method='ffill'))
print (s)
0      100
1      110
2     1020
3     1030
4     1040
5    10050
6    10060
dtype: int64
user_29010020
Related