Extract rows based on conditions Pandas Python

Question

I need to extract rows if certain conditions are applied.

1. the column col1 should contain all the words in the list list_words.
2. the last word should be Story
3. the last word in the next row should b ac:

This is my current code:

import pandas as pd

df = pd.DataFrame({'col1': ['Draft SW Quality Assurance Story', 'alex ac', 'anny ac', 'antoine ac','aze epic', 'bella ac', 'Complete SW Quality Assurance Plan Story', 'celine ac','wqas epic', 'karmen ac', 'kameilia ac', 'Update SW Quality Assurance Plan Story', 'joseph ac','Update SW Quality Assurance Plan ac', 'joseph ac'],
                   'col2': ['aa', 'bb', 'cc', 'dd','ee', 'ff', 'gg', 'hh', 'ii', 'jj', 'kk', 'll', 'mm', 'nn', 'oo']}) 
print(df)

list_words="SW Quality Plan Story"
set_words = set(list_words.split())
#check if list_words is in the cell
df['TrueFalse']=pd.concat([df.col1.str.contains(word,regex=False) for word in list_words.split()],axis=1).sum(1) > 1 

print('\n',df)
#extract last word
df["Suffix"] = df["col1"].str.split().str[-1]
print('\n',df)
df['ok']=''
for i in range (len(df)-1):
    if ((df["Suffix"].iloc[i]=='Story') & (df["TrueFalse"].iloc[i]=='True') & (df["Suffix"].iloc[i+1]=='ac')):
        df['ok'].iloc[i+1]=df["Suffix"].iloc[i+1]

print('\n',df)  

output:

                                         col1 col2  TrueFalse Suffix ok
0           Draft SW Quality Assurance Story   aa       True  Story   
1                                    alex ac   bb      False     ac   
2                                    anny ac   cc      False     ac   
3                                 antoine ac   dd      False     ac   
4                                   aze epic   ee      False   epic   
5                                   bella ac   ff      False     ac   
6   Complete SW Quality Assurance Plan Story   gg       True  Story   
7                                  celine ac   hh      False     ac   
8                                  wqas epic   ii      False   epic   
9                                  karmen ac   jj      False     ac   
10                               kameilia ac   kk      False     ac   
11    Update SW Quality Assurance Plan Story   ll       True  Story   
12                                 joseph ac   mm      False     ac   
13       Update SW Quality Assurance Plan ac   nn       True     ac   
14                                 joseph ac   oo      False     ac   

line 13 should be set to False

desired output :

                                        col1 col2  TrueFalse Suffix     
1   Complete SW Quality Assurance Plan Story   gg      True  Story   
2                                  celine ac   hh      True  ac   
3    Update SW Quality Assurance Plan Story    ll      True  Story   
4                                 joseph ac    mm      True  ac   

Answer 1

Here is one way you can accomplish this.

Pd.concat and use of .all to check if all the words are present.

Check if the same columns endswith story.

Check if the next column (df.shift(-1)) ends with ac.

Edit: After reading some comments it appears that you also want the next line that ends in ac to be True.

I added additional code at the end to add this condition.

import pandas as pd

df = pd.DataFrame({'col1': ['Draft SW Quality Assurance Story', 'alex ac', 'anny ac', 'antoine ac','aze epic', 'bella ac', 'Complete SW Quality Assurance Plan Story', 'celine ac','wqas epic', 'karmen ac', 'kameilia ac', 'Update SW Quality Assurance Plan Story', 'joseph ac','Update SW Quality Assurance Plan ac', 'joseph ac'],
                   'col2': ['aa', 'bb', 'cc', 'dd','ee', 'ff', 'gg', 'hh', 'ii', 'jj', 'kk', 'll', 'mm', 'nn', 'oo']}) 
print(df)

list_words="SW Quality Plan Story"
set_words = set(list_words.split())
#check if list_words is in the cell
df['TrueFalse']=(pd.concat([df['col1'].str.contains(word) for word in set_words],axis=1).all(axis=1)) & (df['col1'].str.endswith('Story')) & (df['col1'].shift(-1).str.endswith('ac'))

##Make sure line ends with ac and prev line follows conditions
df['AC_COL'] = df['TrueFalse'].shift(1).fillna(False)
df['Final_TrueFalse'] = df['TrueFalse'] | df['AC_COL']
print(df[['col1','col2','Final_TrueFalse']])

                                                         col1 col2  Final_TrueFalse
0           Draft SW Quality Assurance Story   aa            False
1                                    alex ac   bb            False
2                                    anny ac   cc            False
3                                 antoine ac   dd            False
4                                   aze epic   ee            False
5                                   bella ac   ff            False
6   Complete SW Quality Assurance Plan Story   gg             True
7                                  celine ac   hh             True
8                                  wqas epic   ii            False
9                                  karmen ac   jj            False
10                               kameilia ac   kk            False
11    Update SW Quality Assurance Plan Story   ll             True
12                                 joseph ac   mm             True
13       Update SW Quality Assurance Plan ac   nn            False
14                                 joseph ac   oo            False

Answer 2

Here are your all different conditions, and their intersection:

# Condition 1: all words in col1 minus all words in set_words must be empty
df["condition_1"] = df.col1.apply(lambda x: not bool(set_words - set(x.split())))

# Condition 2: the last word should be 'Story'
df["condition_2"] = df.col1.str.endswith("Story") 

# Condition 3: the last word in the next row should be ac. See `shift(-1)`
df["condition_3"] = df.col1.str.endswith("ac").shift(-1) 

# When all three conditions meet: new column 'conditions'
df["conditions"] = df.condition_1 & df.condition_2 & df.condition_3

# Back to your notation:
# TrueFalse: rows that fulfill all three conditions along with their next rows
df["TrueFalse"] = df.conditions | df.conditions.shift(1)                                                                                         
df["Suffix"] = df.col1.apply(lambda x: x.split()[-1]) 

Now your desired output:

>>> print(df[["col1", "col2", "TrueFalse", "Suffix"]][df.TrueFalse])
                                        col1 col2  TrueFalse Suffix
6   Complete SW Quality Assurance Plan Story   gg       True  Story
7                                  celine ac   hh       True     ac
11    Update SW Quality Assurance Plan Story   ll       True  Story
12                                 joseph ac   mm       True     ac

FYI, all the dataframe:

>>> print(df[["col1", "col2", "TrueFalse", "Suffix"]])                                                                                                       
                                        col1 col2  TrueFalse Suffix
0           Draft SW Quality Assurance Story   aa      False  Story
1                                    alex ac   bb      False     ac
2                                    anny ac   cc      False     ac
3                                 antoine ac   dd      False     ac
4                                   aze epic   ee      False   epic
5                                   bella ac   ff      False     ac
6   Complete SW Quality Assurance Plan Story   gg       True  Story
7                                  celine ac   hh       True     ac
8                                  wqas epic   ii      False   epic
9                                  karmen ac   jj      False     ac
10                               kameilia ac   kk      False     ac
11    Update SW Quality Assurance Plan Story   ll       True  Story
12                                 joseph ac   mm       True     ac
13       Update SW Quality Assurance Plan ac   nn      False     ac
14                                 joseph ac   oo      False     ac