Search for string in column name and return the entire colum

Question

Is there a way to search for a string or substring in the column name and extract the entire column which name contains that particular string?

My data:

I want to search for "total" in the data frame and extract the entire column (the last column in this case)

Thank you in advance!

@MykolaZotko Yes. But there can be multiple keywords like "amount, total, balance" etc — M_Arora, Jan 21 '21 at 07:57
Does this answer your question? [Find column whose name contains a specific string](https://stackoverflow.com/questions/21285380/find-column-whose-name-contains-a-specific-string) — Mykola Zotko, Jan 21 '21 at 09:06

anurag · Accepted Answer · 2021-01-21T08:27:37.573

1

If you are searching for keywords like Total, explicitly in column names, use the following:

For this dummy data:

Description,Qty,Unit Cost (AED), Total Cost (AED), Amount (xyz)
string 1, 3, 3000, 9000, 9500
string 1, 3, 3000, 9000, 9500
string 1, 3, 3000, 9000, 9500
string 1, 3, 3000, 9000, 9500
string 1, 3, 3000, 9000, 9500
string 1, 3, 3000, 9000, 9500

Try the following code:

import pandas as pd
import re

df = pd.read_csv('test.csv')
print(df)

col = [name for name in df.columns if len(re.findall(r'\b(?:total|amount)\b', name.lower()))!=0]

if len(col)!=0:
    print(df.loc[:, col])

edited Jan 21 '21 at 08:27

answered Jan 18 '21 at 08:57

anurag

1,715
1
8
28

assuming, only one of the column names has the word 'Total' in it! – anurag Jan 18 '21 at 08:58
This code looks for an exact match, while I want to make the code work for even a substring like "total" in the above dataframe – M_Arora Jan 21 '21 at 08:01
I made some edits, see if that serves the purpose! made the `name` in lower case, and search for `total` – anurag Jan 21 '21 at 08:09
It throws an ```IndexError``` because ```.iloc``` requires numerical index but it got ```total```. I've explained my entire problem in the comments above. @anurag – M_Arora Jan 21 '21 at 08:15
let me check the problem – anurag Jan 21 '21 at 08:20
@M_Arora, plz verify if the updated code works for you! – anurag Jan 21 '21 at 08:28
1

Yes, perfectly. Thank you so much @anurag – M_Arora Jan 21 '21 at 08:49
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/227630/discussion-between-m-arora-and-anurag). – M_Arora Jan 21 '21 at 11:58

score 0 · Answer 2 · answered Jan 18 '21 at 08:16

You can try str.contains -

>>> total_mask = df['Description'].str.contains('total|Total')
>>> total_mask
0     True
1    False
2     True
Name: Description, dtype: bool

>>> df.loc[total_mask,:]
          Description  Total Cost (AED)
0  Compensation Total             100.0
2        Total Amount              20.0

Search for string in column name and return the entire colum

2 Answers2