renaming the column names in dataframe using some parts of original column name

Question

I have this huge data frame with very long column names

import pandas as pd


df = pd.DataFrame({'mynumber': [11, 20, 25], 
               'Raja_trial1:gill234_pit_type_id@rng': [4, 5, 42],
               'Raja_trial1:Perm_king_que@pmcx[x1]': [0, 2, 3],
               'Dun_mere_fer45:Hisota_gul_har23@cyu[9]': [4, 5, 42],
               'Pit_chb1:reet_kaam_nix@opdx[x1]': [2, 1, 1],
               })

and I would like to rename some of the columns like below dataframe.

outputdf = pd.DataFrame({'mynumber': [11, 20, 25], 
               'trial1:type_id': [4, 5, 42],
               'trial1:king_que': [0, 2, 3],
               'fere45:gul_har23': [4, 5, 42],
               'chb1:kaam_nix': [2, 1, 1],
               })

Answer 1

You can achieve this with a single regex:

df.columns = df.columns.str.replace(r'.*?([^_]+:).+?([^_]+_[^_]+)@.*',
                                    r'\1\2', regex=True)

output:

   mynumber  trial1:type_id  trial1:king_que  fer45:gul_har23  chb1:kaam_nix
0        11               4                0                4              2
1        20               5                2                5              1
2        25              42                3               42              1

To understand ho this works, you can check the regex demo.