Stack Overflow Asked on January 7, 2021
Suppose I have a dataframe df as:
df = pd.DataFrame({'Index': [1, 2, 3, 4, 5],
'Name': ['A', 'B', 100, 'C', 'D'],
'col1': [np.nan, 'bbby', 'cccy', 'dddy', 'EEEEE'],
'col2': ['water', np.nan, 'WATER', 'soil', 'cold air'],
'col3': ['watermelone', 'hot AIR', 'air conditioner', 'drink', 50000],
'Results': [1000, 2000, 3000, 4000, 5000]})
Out
Index Name col1 col2 col3 Results
1 A NaN water watermelone 1000
2 B bbbY NaN hot AIR 2000
3 100 cccY water air conditioner 3000
4 C dddf soil drink 4000
5 D EEEEE cold air 50000 5000
I have a list: matches = ['wat','air']
How can I choose all rows with col1
or col2
or col3
containing i
in matches
.
Expected output:
Index Name col1 col2 col3 Results
1 A NaN water watermelone 1000
2 B bbbY NaN hot AIR 2000
3 100 cccY water air conditioner 3000
5 D EEEEE cold air 50000 5000
You can use .T
to transpose the dataframe and str.contains
to check the values column-wise and then transpose back (also str.contains
can have multiple values passed to if separated with |
, which is why I change the list to a string with matches = '|'.join(matches)
).
The benefit of transposing the dataframe is that you can use column-wise pandas method instead of looping through rows or a long lambda x:
list comprehension. This technique should have good performance
compared to a lambda x
with axis=1
answer:
# df = df.set_index('Index')
matches = ['wat','air']
matches = '|'.join(matches)
df = df.reset_index(drop=True).T.fillna('')
df = df.T[[df[col].str.lower().str.contains(matches).values.any() for col in df.columns]]
df
Out[1]:
Name col1 col2 col3
0 A water watermelone
1 B bbbY hot AIR
2 B cccY water air conditioner
4 D EEEEE cold air eat
Correct answer by David Erickson on January 7, 2021
Try this as well:
df = df[df['col1'].str.contains('|'.join(matches))|df['col2'].str.contains('|'.join(matches))|df['col3'].str.contains('|'.join(matches))]
Prints:
Name col1 col2 col3
1 A aadY water watermelone
2 B bbbY air hot AIR
3 B cccY water air conditioner
5 D EEEEE cold air eat
Answered by sharathnatraj on January 7, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP