Extract String Data From DataFrame Column To Separate Columns

Question

I have a data frame column that looks like this:

df['out_column']

0         Out 0: 0.024 Out 1: 0.005 
1         Out 0: 0.024 Out 1: 0.009 
2         Out 0: 0.024 Out 1: 0.009 
3         Out 0: 0.024 Out 1: 0.01 
4         Out 0: 0.024 Out 1: 0.011           
5         Out 0: 0.017 Out 1: 0.018 
6         Out 0: 0.146 Out 1: 0.081 
7         Out 0: 0.001 Out 1: 0.002 
8         Out 0: 0.022 Out 1: 0.009 
9         Out 0: 0.012 Out 1: 0.008

How can I separate the data on this column to create to other column with the values in front of Out 0: and Out 1:? The expected outcome is:

          col1   col2
0         0.024  0.005 
1         0.024  0.009 
2         0.024  0.009 
3         0.024  0.01 
4         0.024  0.011           
5         0.017  0.018 
6         0.146  0.081 
7         0.001  0.002 
8         0.022  0.009 
9         0.012  0.008

Nk03 · Accepted Answer · 2021-06-02 20:13:44Z

2

You can use extractall:

df[['col1', 'col2']] = df['out_column'].str.extractall(
    pat=r'.*?\s+\d+:\s+([\d.]+)').unstack(-1)

OUTPUT:

                   out_column   col1   col2
0  Out 0: 0.024 Out 1: 0.005   0.024  0.005
1  Out 0: 0.024 Out 1: 0.009   0.024  0.009
2  Out 0: 0.024 Out 1: 0.009   0.024  0.009
3   Out 0: 0.024 Out 1: 0.01   0.024   0.01
4   Out 0: 0.024 Out 1: 0.011  0.024  0.011
5  Out 0: 0.017 Out 1: 0.018   0.017  0.018
6  Out 0: 0.146 Out 1: 0.081   0.146  0.081
7  Out 0: 0.001 Out 1: 0.002   0.001  0.002
8  Out 0: 0.022 Out 1: 0.009   0.022  0.009
9   Out 0: 0.012 Out 1: 0.008  0.012  0.008

NOTE: Use df = df.drop('out_columns', 1) if requried.

answered Jun 2, 2021 at 20:13

Nk03

15k2 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben.T · Accepted Answer · 2021-06-02 20:17:47Z

1

with a regular pattern with Out 1 and Out 2, you can do str.extract and looking for anything with . several time *

print(df['out_column'].str.extract('Out 0: (.*) Out 1: (.*)'))
       0      1
0  0.024  0.005
1  0.024  0.009
2  0.024  0.009
3  0.024   0.01

answered Jun 2, 2021 at 20:17

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Collectives™ on Stack Overflow

Extract String Data From DataFrame Column To Separate 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