Get all Row and Column Positions of cells with strings starting with a pattern

user_13356494 0
Viewed 150

I have a dataframe and I'd like to know the locations (row and column indexes) of cells with strings starting with Nr.:

df = pd.DataFrame({'A':['Nr.Example1',2,'CAT'],
                'B':[3,'Nr.Example2','Nr. Example3'],
                'C':[4,'Nr. example 4','DOG']})

Considering that, based on the dataframe above, the rows and columns of interest would be:

'Nr.Example1' = (0, 'A') or (0, 0)
'Nr.Example2' = (1, 'B') or (1, 1)
'Nr. Example3' = (2, 'B') or (2, 1)
'Nr. example 4' = (1, 'C') or (1, 2)

I'd like an output like:

locs = [(0, 'A'),(1, 'B'),(2, 'B'),(1, 'C')]
or
ilocs = [(0, 0),(1, 1),(2, 1),(1, 2)]

I could use

locs = df[df == 'Nr.Example1'].stack().index.tolist()

or

ilocs = np.where(df.values == 'Nr.Example1')

Run these lines of code looping over every single possibility and append the results into a list. HOWEVER, I don't know in advance all the possibilities. I do know that it will always begins with Nr..

pandas dataframe python
2 Answers

You can stack the DataFrame, and find indices of all records that str.startswith 'Nr.':

df.stack().str.startswith('Nr.')[lambda x: x==True].index.tolist()

Output:

[(0, 'A'), (1, 'B'), (1, 'C'), (2, 'B')]

Or with numpy:

np.argwhere(np.char.startswith(df.values.astype(str), 'Nr.'))

Output:

array([[0, 0],
       [1, 1],
       [1, 2],
       [2, 1]])
user_67927430

via np.where:

index = (np.where(df.astype(str).applymap(lambda x: x.startswith('Nr.')).values ==  True))
result = list(zip(index[0],index[1]))

Output:

[(0, 0), (1, 1), (1, 2), (2, 1)]
user_154380330
Related