How can I remove string after last underscore in python dataframe?

user_13573521 0
Viewed 2196

I want to remove the all string after last underscore from the dataframe. If I my data in dataframe looks like.

AA_XX,
AAA_BB_XX,
AA_BB_XYX,
AA_A_B_YXX

I would like to get this result

AA,
AAA_BB,
AA_BB,
AA_A_B
pandas dataframe python
3 Answers

You can do this simply using Series.str.split and Series.str.join:

In [2381]: df
Out[2381]: 
         col1
0       AA_XX
1   AAA_BB_XX
2   AA_BB_XYX
3  AA_A_B_YXX

In [2386]: df['col1'] = df['col1'].str.split('_').str[:-1].str.join('_')

In [2387]: df
Out[2387]: 
     col1
0      AA
1  AAA_BB
2   AA_BB
3  AA_A_B
user_58208140 
pd.DataFrame({'col': ['AA_XX', 'AAA_BB_XX', 'AA_BB_XYX', 'AA_A_B_YXX']})['col'].apply(lambda r: '_'.join(r.split('_')[:-1]))

Explaination:

df = pd.DataFrame({'col': ['AA_XX', 'AAA_BB_XX', 'AA_BB_XYX', 'AA_A_B_YXX']})

Creates

    col
0   AA_XX
1   AAA_BB_XX
2   AA_BB_XYX
3   AA_A_B_YXX

Use apply in order to loop through the column you want to edit.
I broke the string at _ and then joined all parts leaving the last part at _

df['col'] = df['col'].apply(lambda r: '_'.join(r.split('_')[:-1]))
print(df)

Results:

    col
0   AA
1   AAA_BB
2   AA_BB
3   AA_A_B

If your dataset contains values like AA (values without underscore).
Change the lambda like this

df = pd.DataFrame({'col': ['AA_XX', 'AAA_BB_XX', 'AA_BB_XYX', 'AA_A_B_YXX', 'AA']})
df['col'] = df['col'].apply(lambda r: '_'.join(r.split('_')[:-1]) if len(r.split('_')) > 1 else r)
print(df)
user_137399650

Here is another way of going about it.

import pandas as pd
data = {'s': ['AA_XX', 'AAA_BB_XX', 'AA_BB_XYX', 'AA_A_B_YXX']}
df = pd.DataFrame(data)

def cond1(s):
    temp_s = s.split('_')
    temp_len = len(temp_s)
    if len(temp_s) == 1:
        return temp_s
    else:
        return temp_s[:len(temp_s)-1]
df['result'] = df['s'].apply(cond1)
user_65348180
Related