I have a dataframe, df, where I would like to remove the values that come before the underscore '_' and after the underscore '_' , essentially, keeping the middle.

Also keeping the digits at the end and concatenate with the middle part extracted.

Data

col1         col2
a_bu1        dd
a_lap_aa1    d     
a_lap_aa2    d
h_bb_led1    dd

Desired

col1    col2
bu1     dd
lap1    d      
lap2    d
bb1     dd

Doing

re.sub(r'^.*?I', 'I', stri)

However, the entire dataset is not being maintained. I am still researching. Any advice is appreciated.

To remove the values that come before the '_' and after the '_' , essentially, keeping the middle, you can use .str.extract() with regex, as follows:

df['col1'] = df['col1'].str.extract(r'\w*?_([^_]*)(?:_)?')

Result:

print(df)col1 col2
0  bu1   dd
1  lap    d
2  lap    d
3   bb   dd

Edit

To extract also the digits at the end, you can do:

s_df = df['col1'].str.split('_', expand=True) 
s_df[2] = s_df[2].str.extract(r'(\d+)$').fillna('') 
df['col1'] = s_df[1] + s_df[2]

Result:

print(df)col1 col2
0   bu1   dd
1  lap1    d
2  lap2    d
3   bb1   dd