How to check for a sequence of string values in pandas dataframe and output the subsequent

user_13943207 0
Viewed 777

I'm trying to check for the sequence of B-B-B in the dataframe.

d = {'A': ['A','B','C','D','B','B','B','A','A','E','F','B','B','B','F','A','A']}
testdf = pd.DataFrame(data=d)

array = []
seq = pd.Series(['B', 'B', 'B'])

for i in testdf.index:
    
    if testdf.A[i:len(seq)] == seq:
        
        array.append(testdf.A[i:len(seq)+1])

I get an error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

How can I get it working? I don't understand what's "ambiguous" about this code

My desired output here is:

A, F
pandas python
2 Answers

  1. The ambiguous comparison comes from the fact that when you test 2 Series for equalty (they should be same size), a pair comparison is done and you obtain a Series with only True/False value, you should then decide if you want all true, all false, at least one true ... using .any(), .all(), ...

    s1 = pd.Series(['B', 'B', 'B'])
s2 = pd.Series(['A', 'B', 'B'])

print(s1 == s2)
0    False
1     True
2     True
dtype: bool

print((s1 == s2).all())
False

  2. To access a subsequence, prefer the use of .iloc

  3. You need to use [i:i + len(seq)] and not [i:len(seq)] because this is a [from:to] notation

  4. You need to use Series.reset_index(drop=True) because to compare series they must have the same index, so as seq if always indexed 0,1,2 you need same for sht subsequence you compute (because testdf.A.iloc[1:3] is indexed 1,2,3]

  5. Verify the length before checking the Series to avoid an Exception at the end when the subsequence will be smaller

You end with :

values = {'A': ['A', 'B', 'C', 'D', 'B', 'B', 'B', 'A', 'A', 'E', 'F', 'B', 'B', 'B', 'F', 'A', 'A']}
testdf = pd.DataFrame(values)
array = []
seq = pd.Series(['B', 'B', 'B'])
for i in testdf.index:
    test_seq = testdf.A.iloc[i:i + len(seq)].reset_index(drop=True)
    if len(test_seq) == len(seq) and (test_seq == seq).all():
        array.append(testdf['A'].iloc[i + len(seq)])
print(array)  # ['A', 'F']
user_72126860

Instead of iterating over every row in the DataFrame, we can iterate over the much smaller sequence (Much beter when len(seq) << len(df)). Use shift + np.logical_and.reduce to locate the sequence in the DataFrame and where it ends. Then we'll roll to get the next row after, which are the values you want. (Modified slightly from my related answer here)

import numpy as np

def find_next_row(seq, df, col):
    seq = seq[::-1]  # to get last index
    m = np.logical_and.reduce([df[col].shift(i).eq(seq[i]) for i in range(len(seq))])

    m = np.roll(m, 1)
    m[0] = False  # Don't wrap around
    
    return df.loc[m]
    # return df.loc[m, col].tolist()

find_next_row(['B', 'B', 'B'], df, col='A')
#    A
#7   A
#14  F

If you just want the list and don't care for the DataFrame, change the return to what's currently commented out: return df.loc[m, col].tolist()

find_next_row(['B', 'B', 'B'], df, col='A')
#['A', 'F']
user_43333590
Related