How to split one string column with regular format to multiple columns in Pandas

Question

If I have a dataframe like this:

id  s             scene
1   a   kitchen: 0.297, living: 0.515, degree: A
2   b   kitchen: 0.401, study: 0.005, degree: A
3   c   study: 0.913, degree: B
4   d   living: 0.515, degree: B
5   e   others: 0.1, degree: C

How can get a new dataframe as follows with Pandas.

So far, I have tried df[['id', 's', 'kitchen', 'living', 'study', 'others', 'degree']] = df['scene'].str.split(',', expand=True):

id   s   kitchen living  study   others    degree
1    a    0.297   0.515   0       0           A 
2    b    0.401   0       0.005   0           A
3    c    0       0       0.913   0           B
4    d    0       0.515   0       0           B
5    e    0       0       0       0.1         C

Zero · Accepted Answer · 2018-09-03 12:05:21Z

2

You can

In [763]: dff = pd.DataFrame(
              dict(y.split(': ') for y in x.split(', ')) for x in df.scene).fillna(0)

In [764]: dff
Out[764]:
  degree kitchen living others  study
0      A   0.297  0.515      0      0
1      A   0.401      0      0  0.005
2      B       0      0      0  0.913
3      B       0  0.515      0      0
4      C       0      0    0.1      0

then join

In [766]: df.join(dff)
Out[766]:
   id  s                                     scene degree kitchen living  \
0   1  a  kitchen: 0.297, living: 0.515, degree: A      A   0.297  0.515
1   2  b   kitchen: 0.401, study: 0.005, degree: A      A   0.401      0
2   3  c                   study: 0.913, degree: B      B       0      0
3   4  d                  living: 0.515, degree: B      B       0  0.515
4   5  e                    others: 0.1, degree: C      C       0      0

  others  study
0      0      0
1      0  0.005
2      0  0.913
3      0      0
4    0.1      0

answered Sep 3, 2018 at 12:05

Zero

77.4k22 gold badges154 silver badges154 bronze badges

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

1 Comment

ah bon Over a year ago

Thanks. What if in the column of scene has NULL value? It returns: AttributeError: 'float' object has no attribute 'split' when i join dff to df.

Collectives™ on Stack Overflow

How to split one string column with regular format to multiple columns in Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related