How to search for a partial string in a dataframe and return a specific cell using Pandas?

Question

I've got a dataframe with non-standard columns (not the same info all the way down). I want to search for a particular phrase that may or may not appear in some column of a row and then return the value of the succeeding cell. For example:

A               B               C
How many?       5               Blah blah
Blah            How many?       7
How many?       13              Blah
Blah            Blah            Blah

I'm trying to figure out how to search for the phrase "How many?" and then return the value in the next cell (5/7/13/null)

Yeah, I'm basically trying to search for a phrase and if it's found, then return that cell and the cell to the right of it (which is always the "value" in my data set). There is a lot of garbage in my data set so want to essentially just return those 2 items and dump everything else in the row. — David
– David, Commented Nov 8, 2018 at 15:25

BENY · Accepted Answer · 2018-11-08 15:37:23Z

2

With Boolean and shift

df[df.eq('How many?').shift(1,axis=1).fillna(False)]
Out[142]: 
     A    B    C
0  NaN    5  NaN
1  NaN  NaN    7
2  NaN   13  NaN
3  NaN  NaN  NaN

Update

s1=df.eq('How many?').shift(1,axis=1).fillna(False)
s2=df.eq('How many?')
df[s1|s2]
Out[154]: 
          A          B    C
0  How many?         5  NaN
1       NaN  How many?    7
2  How many?        13  NaN
3       NaN        NaN  NaN

edited Nov 8, 2018 at 15:37

answered Nov 8, 2018 at 15:22

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BENY Over a year ago

@Alex yep , fixed

David Over a year ago

This is perfect, thanks! Still fairly new to python/pandas and didn't know about shift. I assume the 1 when you call shift is to return the cell 1 to the right? Is there an easy way to return both the cell with the search term and that one?

Rocky Li · Accepted Answer · 2018-11-08 15:34:34Z

1

Use numpy array instead for easier indexing:

mask = df.values == 'How many?'
your_list = [df.values[i, j+1] for i, j in zip(*np.where(mask)) if j < df.values.shape[1]-1]
# yourlist = ['5', '7', '13']

edited Nov 8, 2018 at 15:34

answered Nov 8, 2018 at 15:25

Rocky Li

5,9862 gold badges21 silver badges36 bronze badges

1 Comment

Rocky Li Over a year ago

@Alex fixed. Missed a * operator.

Collectives™ on Stack Overflow

How to search for a partial string in a dataframe and return a specific cell using Pandas?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related