Choose a series of rows between two other rows that contain specific values or strings

user_5161059 0
Viewed 71

EDIT:
If the number of occurrences of Keyword1 and Keyword2 does not match, then the solutions provided does not work. I have updated the dataframe and code below to reflect a similar mismatch.

ORIGINAL POST:
I have a dataframe of strings and I am trying to choose all the rows between two specific string values [Keyword1 and Keyword2].

I am using the code below:

import pandas as pd 
import numpy as np

df=pd.DataFrame(['A', 'B', 'C1', 'D', 'A', 'B', 'C2','C3', 'D','C4', 'B', 'C5'])
df.columns = ['Col1']

Keyword1= 'B'
Keyword2= 'D'

#Filter and delete file mode deliveries
a=df.index[df['Col1'] == Keyword1].tolist()
b=df.index[df['Col1'] == Keyword2].tolist() 
b=np.add(b, 1).tolist() 
 

index=[]
for i in range(len(b)):
    index_temp=np.arange(a[i],b[i]).tolist()
    index=index+index_temp

df_keep= df[df.index.isin(index)]   
df_del= df[~df.index.isin(index)]

While this does the work, I am wondering if there a more efficient way to perform the same task.

pandas dataframe python string
3 Answers

This should be more efficient than looping. You can create series of conditions c1 and c2 that are boolean series based off cumsum logic and idxmin that essentially tell you if values are between D and B. Using cumsum helps you identify the change of value to B or D which helps you find values in between:

df['s'] = ((df['Col1'] == 'D') | (df['Col1'] == 'B')).cumsum()
c1 = (df.index != df.groupby('s')['s'].transform('idxmin'))
c2 = ((df['Col1'] == 'B').cumsum() == (df['Col1'] == 'D').cumsum())
df_del = df[c1 & c2].drop('s', axis=1)
df_del
Out[1]: 
  Col1
4    A
9   C4

df_keep = df[~(c1 & c2)].drop('s', axis=1)
df_keep
Out[2]: 
   Col1
0     A
1     B
2    C1
3     D
5     B
6    C2
7    C3
8     D
10    B
11   C5
12    D
user_63667700

Just another try:

ix1= np.where(df.Col1.eq('B'))[0]
ix2= np.where(df.Col1.eq('D'))[0]
df_keep = pd.concat([df.Col1.iloc[start:end+1] for start, end in zip(ix1,ix2)])

Prints:

1      B
2     C1
3      D
5      B
6     C2
7     C3
8      D
10     B
11    C5
12     D
user_136288830

Here's a way:

df[((df.eq(Keyword2)*-1).shift().bfill() + df.eq(Keyword1)).cumsum().astype(bool)['Col1']]

Output:

   Col1
1     B
2    C1
3     D
5     B
6    C2
7    C3
8     D
10    B
11   C5
12    D

Use eq to assign 1 to keyword1 and -1 to keyword2 then use cumsum to find all places where value equals to 1 change it to True with astype, then boolean index the dataframe and dropna.

Details:

m1 = (df.eq(Keyword2)*-1).shift().bfill() #find the Keyword2
m1

Output:

    Col1
0    0.0
1    0.0
2    0.0
3    0.0
4   -1.0
5    0.0
6    0.0
7    0.0
8    0.0
9   -1.0
10   0.0
11   0.0
12   0.0

Nex,

m2 =  df.eq(Keyword1) #find the Keyword1
print(m2)

Ouput:

     Col1
0   False
1    True
2   False
3   False
4   False
5    True
6   False
7   False
8   False
9   False
10   True
11  False
12  False

And,

(m1 + m2).cumsum()

    Col1
0    0.0
1    1.0
2    1.0
3    1.0
4    0.0
5    1.0
6    1.0
7    1.0
8    1.0
9    0.0
10   1.0
11   1.0
12   1.0
user_63615310
Related