How to check over a DataFrame and return columns that contain particular strings?

user_13592838 0
Viewed 72

I have the following df:

                                  0      1      2           3          4
3                 Services rendered            2000        HK$’000 HK$’000
5  Audit and audit related services            999           1,800   9,999
8              financial statements  HK$’000   788           600     1,233,456

I want to capture columns which have both currency and financial number e.g. HK$’000 and 1,800 or 600.

  • In this case, df[3] and df[4] is the column in interested, not df[1] or df[2]

The desire result is returning a DataFrame: df[[3, 4]] as the following

    3          4
3  HK$’000 HK$’000
5  1,800   9,999
8  600     1,233,456

I tried

df_fin = df[df.apply(lambda x: x.str.contains('HK')) | df.apply(lambda x: x.str.contains('\d,?\d*'))]
df_fin.iloc[:, df_fin.any().to_list()]

but it captures df[1] and df[2] too. How may I have the desired result?

Thank you.

pandas dataframe python
2 Answers

We could break it down into steps, to make it easier to pull off. The assumption here is that the entire dataframe is just strings :

Create regexes :

regex1 = "HK\$’\d+$"
regex2 = "^\d+[\d,]+$"

Filter for the first regex :

df = df.loc[:, df.apply(lambda x: x.str.contains(regex1)).any()]
df

    1       3         4
3   None    HK$’000 HK$’000
5   None    1,800   9,999
8   HK$’000 600     1,233,456

Now, filter for the second regex:

df = df.loc[:, df.apply(lambda x: x.str.contains(regex2)).any()]
df


     3        4
3   HK$’000 HK$’000
5   1,800   9,999
8   600     1,233,456
user_71757130

One way using pandas.DataFrame.replace with pandas.Series.str.isnumeric:

d = {"HK\$’": "1", ",": ""}
df = df.astype(str)
cols = []
for c in df:
    if df[c].str.contains("HK\$’").any():
        if df[c].replace(d, regex=True).str.isnumeric().all():
            cols.append(c)
print(df[cols])

Output:

         3          4
3  HK$’000    HK$’000
5    1,800      9,999
8      600  1,233,456
user_70937410
Related