Extract string before certain last character

Question

I have the following Pandas Dataframe:

Name	ID
AT_A	1
AT_B	2
AOS_PAR_F	3
AOS_ROOT_LE_B	4

I want to create a new column which would hold the string from column "Name" which is before the last "_" character and remove it from the original column.

So the desired output would be:

Name	ID	Name_2
A	1	AT
B	2	AT
F	3	AOS_PAR
B	4	AOS_ROOT_LE

Any ideas?

Answer 1

You can use a right split (rsplit):

df[['Name_2', 'Name']] = df['Name'].str.rsplit('_', 1, expand=True)

Variant with a regex:

df[['Name_2', 'Name']] = df['Name'].str.extract(r'(.*)_([^_]+)$')

output:

  Name  ID       Name_2
0    A   1           AT
1    B   2           AT
2    F   3      AOS_PAR
3    B   4  AOS_ROOT_LE