Python: Find String In Multiple Columns And Return It In New Column
Hi I have an excel data with multiple columns and i need to fined specific word and return it in new column the table look like this: ID col0 col1 col2 col3 col4 col5 1
Solution 1:
Here is a way using stack
and str.contains
with df.where
:
cols = ['col1', 'col2', 'col3', 'col4']
df['b'] = (df[cols].where(df[cols].stack().str.contains('b')
.unstack(fill_value=False)).ffill(1).iloc[:,-1])
print(df)
ID col0 col1 col2 col3 col4 col5 b
0 1 jack a/h t/m w/n y/h 56 NaN
1 2 sam z/n b/w NaN NaN 93 b/w
2 3 john b/i y/d p/d NaN 33 b/i
Solution 2:
I would use DataFrame.stack
with callable
:
cols = ['col1', 'col2', 'col3', 'col4']
df['b']=(df[cols].stack()
.loc[lambda x: x.str.contains('b')]
.reset_index(level=1,drop=1)
#.fillna('-') #for the expected output
)
Output
ID col0 col1 col2 col3 col4 col5 b
0 1 jack a/h t/m w/n y/h 56 NaN
1 2 sam z/n b/w NaN NaN 93 b/w
2 3 john b/i y/d p/d NaN 33 b/i
Solution 3:
In a bid to avoid selecting the columns, I used melt:
M = (df.copy()
.melt(id_vars='ID')
.loc[lambda x:x['value'].astype('str').str.contains('b')]
.drop('variable',axis=1))
pd.merge(df,M,how='left',on='ID').rename({'value':'b'},axis=1)
D col0 col1 col2 col3 col4 col5 b
0 1 jack a/h t/m w/n y/h 56 NaN
1 2 sam z/n b/w NaN NaN 93 b/w
2 3 john b/i y/d p/d NaN 33 b/i
Post a Comment for "Python: Find String In Multiple Columns And Return It In New Column"