CODEWITHSUNDEEP
pythonpandascsvdataframe
David
David

Reputation: 3

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

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)

Upvotes: 0

Views: 59

Answers (2)

BENY
BENY

Reputation: 323226

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

Upvotes: 2

Rocky Li
Rocky Li

Reputation: 5958

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']

Upvotes: 1

Related Questions