Extract string column in multiple columns

Question

I have the next df:

Match                       Count
Asren_Blue_Coached           879
Asren_Blue_NotCoached        721
Asren_RED_Coached            6567
Asren_RED_NotCoached         764
Asren_White_Coached          762
Asren_White_NotCoached       431
Asren_Yellow_Coached         375
Asren_Yellow_NotCoached      412
Hrew_Blue_Coached            689
Hrew_Blue_NotCoached         634
...

I want to add three new columns,

One column for each word before the underscore in column 'Match'

So, the expected output looks like this:

Match                       NewCol1   NewCol2    NewCol3      Count    
Asren_Blue_Coached           Asren      Blue     Coached       879      
Asren_Blue_NotCoached        Asren      Blue     NotCoached    721
Asren_RED_Coached            Asren      RED      Coached       6567
Asren_RED_NotCoached         Asren      RED      NotCoached    764
Asren_White_Coached          Asren      White    Coached       762
Asren_White_NotCoached       Asren      White    NotCoached    431
Asren_Yellow_Coached         Asren      Yellow   Coached       375
Asren_Yellow_NotCoached      Asren      Yellow   NotCoached    412
Hrew_Blue_Coached            Hrew       Blue     Coached       689
Hrew_Blue_NotCoached         Hrew       Blue     NotCoached    634
...

Do you know how can I do it?

I'm working with pandas in Jupyter

sophocles · Accepted Answer · 2020-12-17 17:20:17Z

You can use str.split, and assign the return to 3 columns named are you wish, and specify the delimiter to be "_"

See below:

df[['NewCol1', 'NewCol2','NewCol3']] = df['Match'].str.split('_', expand=True)

which prints your desired output:

print(df)
                     Match  Count NewCol1 NewCol2     NewCol3
0       Asren_Blue_Coached    879   Asren    Blue     Coached
1    Asren_Blue_NotCoached    721   Asren    Blue  NotCoached
2        Asren_RED_Coached   6567   Asren     RED     Coached
3     Asren_RED_NotCoached    764   Asren     RED  NotCoached
4      Asren_White_Coached    762   Asren   White     Coached
5   Asren_White_NotCoached    431   Asren   White  NotCoached
6     Asren_Yellow_Coached    375   Asren  Yellow     Coached
7  Asren_Yellow_NotCoached    412   Asren  Yellow  NotCoached
8        Hrew_Blue_Coached    689    Hrew    Blue     Coached
9     Hrew_Blue_NotCoached    634    Hrew    Blue  NotCoached

noah · Accepted Answer · 2020-12-17 17:17:06Z

0

You can use the Series.str.split() to split a column on a delimiter. Then just mark expand=True to get new columns instead of a list.

df["Match"].str.split("_", expand=True)

answered Dec 17, 2020 at 17:17

noah

2,79615 silver badges29 bronze badges

Collectives™ on Stack Overflow

Extract string column in multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related