Remove index that isn't followed by an index in other pd.Series

user_13897544 0
Viewed 42

I have two Pandas Series, I want to filter out data which has an index that is not followed by an index in the other Series. So when joining these Series, based on index, only every other datapoint should be from data1 and vice versa.

Code:

import pandas as pd
import matplotlib.pyplot as plt

data1={1:20, 3:30, 5:40, 6:50}
data2={2:10, 4:20, 7:30, 9:40}
data1=pd.Series(data1)
data2=pd.Series(data2)
print(data1)
print(data2)

ax=plt.subplot()
ax.plot(data1,marker='o')
ax.plot(data2,marker='o')
plt.show()

Output:
(Arrows show data points which I want filtered out)

1    20
3    30
5    40
6    50
dtype: int64
2    10
4    20
7    30
9    40
dtype: int64

enter image description here

Expected pandas function for solving my problem:

expectedData1=data1[data1.index.isNotFollowedBy(data2.index)]
expectedData2=data2[data2.index.isNotFollowedBy(data1.index)]

Expected output from that function:

1    20
3    30

6    50
dtype: int64
2    10
4    20

9    40
dtype: int64
pandas dataframe python
2 Answers

IIUC, here is one idea with Dataframe.align:

m = lambda s: s[s.notna() & s.shift(-1).isna()]
s1, s2 = map(m, data1.align(data2))

Result:

print(s1)

1    20.0
3    30.0
6    50.0
dtype: float64

print(s2)

2    10.0
4    20.0
9    40.0
dtype: float64
user_128331660

You can join the two series together in a dataframe and compare them according to your comment using shift() and filter out those rows.

df = data1.reset_index().join(data2.reset_index(), rsuffix='_y')
df = df[(df['index'].shift(-1) > df['index_y']) | (df['index'] == df['index'].iloc[-1])]
data1 = df.iloc[:,0:2].set_index('index').iloc[:,0]
data2 = df.iloc[:,2:].set_index('index_y').iloc[:,0]

Then, you can plot:

ax=plt.subplot()
ax.plot(data1,marker='o')
ax.plot(data2,marker='o')
plt.show()

enter image description here

user_63667700
Related