Split pandas column into multiple columns based on 'key=value' items

Question

I have a dataframe where one column contains several information in a 'key=value' format. There are almost a hundred different 'key=value' that can appear in that column but for simplicity sake I'll use this example with only 4 (_browser, _status, _city, tag)

id  name   properties
0   A      {_browser=Chrome, _status=TRUE, _city=Paris}
1   B      {_browser=null, _status=TRUE, _city=London, tag=XYZ}
2   C      {_status=FALSE, tag=ABC}

How can I convert this splitting the properties string column into multiple columns?

The expected output is:

id  name   _browser    _status    _city    tag
0   A      Chrome      TRUE       Paris       
1   B      null        TRUE       London   XYZ
2   C                  FALSE               ABC

Note: this value can also contain spaces (eg. _city=Rio de Janeiro)

https://stackoverflow.com/questions/14745022/how-to-split-a-dataframe-string-column-into-two-columns — Huzefa Sadikot, Dec 11 '20 at 16:52
https://www.geeksforgeeks.org/split-a-text-column-into-two-columns-in-pandas-dataframe/ — Huzefa Sadikot, Dec 11 '20 at 16:54
@Huzefa in my case I don't know how many values can be in the properties column and they don't follow a strict sequence, the item can appear in any part of the string — eduardoftdo, Dec 11 '20 at 16:56
@eduardoftdo does this answer help? https://stackoverflow.com/questions/58245672/how-to-splitting-column-value-in-dataframe-into-multiple-columns — Huzefa Sadikot, Dec 11 '20 at 17:04

Shubham Sharma · Accepted Answer · 2020-12-11T17:21:52.307

5

Let's use str.findall with regex capture groups to extract key-value pairs from the properties column:

df.join(pd.DataFrame(
    [dict(l) for l in df.pop('properties').str.findall(r'(\w+)=([^,\}]+)')]))

Result:

 id name _browser _status   _city  tag
  0    A   Chrome    TRUE   Paris  NaN
  1    B     null    TRUE  London  XYZ
  2    C      NaN   FALSE     NaN  ABC

edited Dec 11 '20 at 17:21

answered Dec 11 '20 at 17:12

Shubham Sharma

68,127
6
24
53

Thanks Shubham, but how can I change the regex for when I have values with spaces in it? For example _city=Rio de Janeiro – eduardoftdo Dec 11 '20 at 17:16
1

@eduardoftdo in that case i guess you can try `df.pop(properties').str.findall(r'(\w+)=([^,\}]+)')` – Shubham Sharma Dec 11 '20 at 17:20
1

@eduardoftdo Happy coding! – Shubham Sharma Dec 11 '20 at 17:25
Is there a way to do this regex if I have spaces in the key itself? – Asif Iqbal Apr 15 '21 at 12:42
@AsifIqbal Yes you can do that just replace the `regex` pattern with `r'([^=]+)=([^,\}]+)'` – Shubham Sharma Apr 15 '21 at 13:40

Split pandas column into multiple columns based on 'key=value' items

1 Answers1

Linked