swap last 2 string of columns seperated by delimiter

Question

I hava a dataframe. I want to swap last 2 string of columns seperated by "_" if the 2nd last string is "pi"

Dataframe has columns such as:

     abc_rte        abc_rte_log     abc_rte_log_pi1     abc_rte_pi1_log     xyz_pnct_pi2_log

Desired column names:

      abc_rte       abc_rte_log     abc_rte_log_pi1     abc_rte_log_pi1     xyz_pnct_log_pi2    

What i tried so far:

        for i in range(0, len(df.columns)):
          if str(df.columns[i].split('_')[-2] == 'pi':
            df.columns[i].split('_')[-2] = str(df.columns[i].split('_')[-1])

Answer 1

Index.str.replace

df.columns = df.columns.str.replace(r'(pi\d*)_([^_]+)$', r'\2_\1')

---

>>> df.columns

Index(['abc_rte', 'abc_rte_log', 'abc_rte_log_pi1', 'abc_rte_log_pi1',
       'xyz_pnct_log_pi2'],
      dtype='object')

Regex details:

• (pi\d*) : First capturing group
  • pi : Matches the characters pi literally
  • \d* : Matches a digit between zero or more times
• _ : Matches the character _
• ([^_]+) : Second capturing group
  • [^_]+ : Matches any character not present in the list [_] one or more times
• $ : Asserts position at the end of line

See the online regex demo

Answer 2

mapping = {col:col for col in df.columns}
for colname in df.columns:
    splits = colname.rsplit("_",2)
    if splits[-2] == 'pi':
        newname = "_".join((splits[0], splits[-1], splits[-2]))
        mapping[colname] = newname

df.rename(columns=mapping, inplace=True)