I have a string column in my df.

col
a: 1, b: 2, c: 3
b: 1, c: 3, a: 4
c: 2, b: 4, a: 3

I wish to convert this into multiple columns as:

a   b   c
1   2   3
4   1   3
3   4   2

Need help regarding this.

I am trying to convert this into a dict and then sort the dict. Post that, I want to maybe do a pivot table. Not exactly sure if it'll do but any help or better method will be appreciated.

CodePudding user response：

Use nested list comprehension with double split by , and : for list of dictionaries and pass to DataFrame constructor:

df = pd.DataFrame([dict(y.split(': ') for y in x.split(', ')) for x in df['col']],
                  index=df.index)
print (df)
   a  b  c
0  1  2  3
1  4  1  3
2  3  4  2

CodePudding user response：

You can use str.extractall and unstack:

(df['col'].str.extractall('(\w ):\s*([^,] )')
 .set_index(0, append=True).droplevel('match')[1]
 .unstack(0)
)

Output:

   a  b  c
0  1  2  3
1  4  1  3
2  3  4  2