How do I rename the columns of a Pandas DataFrame using Regex?

user_7275091 0
Viewed 240

I have a dataframe that has column names like below, with some of the column name having substring within []

'date', 'customer','region[level1]','priority[s2]','planning[WIP]' ...
 Jan.10,  ABC     ,   East[3]      ,    Normal[1] ,    TBD[WIP]    ...

I want to rename all the columns so that only the value within the [] is kept.

'date','customer','level1','s2','WIP' ...

I've tried below code but kept getting error code 'NoneType' object has no attribute 'groups'. Which part went wrong?

import re
for i in range(len(df.columns)):
  df.columns.value[i] = [re.match(r"[^[]*\[([^]]*)\]", col_name).groups()[0] for col_name in df.columns]
pandas python regex
3 Answers

You can try str.replace Details on the regex found here

df = pd.DataFrame(['date','customer','region[level1]','priority[s2]','planning[WIP]'], columns=['col'])

df['col'].str.replace(r'(.*\[)|(\].*)', '', regex=True)

# 0        date
# 1    customer
# 2      level1
# 3          s2
# 4         WIP
# Name: col, dtype: object

To change column names

df = pd.DataFrame([[1,1,1,1,1]], columns=['date','customer','region[level1]','priority[s2]','planning[WIP]'])
df.columns = df.columns.str.replace(r'(.*\[)|(\].*)', '', regex=True)

#   date    customer    level1  s2  WIP
# 0 1   1   1   1   1
user_66606380

you can use apply and re.sub() like below:

df = pd.DataFrame([['Jan.10','ABC','East[3]','Normal[1]','TBD[WIP]']], columns=['date','customer','region[level1]','priority[s2]','planning[WIP]'])

df.rename(columns=(lambda x : re.sub(r'(.*\[)|(\].*)', "", x)))

Output:

    date    customer    level1    s2            WIP
0   Jan.10  ABC         East[3]   Normal[1]   TBD[WIP]
user_17405770

A couple of notes:

  • re.match only searches for a match at the start of string, use re.search to look for matches anywhere inside a string
  • [^[]*\[([^]]*)\] matches zero or more chars other than [ and then [, zero or more chars other than ] and then ] and it looks good, but it is a pattern that you should use with .str.replace (see below).
  • You get 'NoneType' object has no attribute 'groups' issue because some headers do not match your pattern (see the regex demo above.)

You can use

import pandas as pd
df = pd.DataFrame([['Jan.10','ABC','East[3]','Normal[1]','TBD[WIP]']], columns=['date','customer','region[level1]','priority[s2]','planning[WIP]'])
df.columns = df.columns.str.replace(r'.*\[([^][]*)].*', r'\1', regex=True)

>>> df
     date customer   level1         s2       WIP
0  Jan.10      ABC  East[3]  Normal[1]  TBD[WIP]

The .*\[([^][]*)].* regex matches

  • .* - any zero or more chars other than line break chars as many as possible
  • \[ - a [ char
  • ([^][]*) - Group 1: any zero or more chars other than [ and ]
  • ].* - ] char and the rest of the line.

See the regex demo.

user_38329700
Related