Extract multiple substring matching pattern into columns

Question

My pandas dataframe has string like this

A=1;B=3;C=c6
A=2;C=c7;D=8

I want to extract the value in each field into separate columns, and then use the field name as columns like this

A    B    C    D
1    3    c6   NaN
2    NaN  c7   8

I tried split df.str.split('=|;', expand=True) but it splits both the value and field as separated columns

I also tried using df.str.extract(r'=\s*([^\.]*)\s*\;', expand=True) but it only return the first occurrence of the values.

Thank you for your help

jezrael · Accepted Answer · 2020-05-12 05:08:50Z

5

I think here is possible use split in list comprehension - first by ; and then by = and convert it to dictionary, so last is possible use DataFrame constructor:

print (df)
            col
0  A=1;B=3;C=c6
1  A=2;C=c7;D=8

L = [dict([y.split('=') for y in x.split(';')]) for x in df['col']]

df = pd.DataFrame(L)
print (df)
   A    B   C    D
0  1    3  c6  NaN
1  2  NaN  c7    8

Detail:

print (L)
[{'A': '1', 'B': '3', 'C': 'c6'}, {'A': '2', 'C': 'c7', 'D': '8'}]

answered May 12, 2020 at 5:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

r.ook Over a year ago

I don't think it gets any simpler than using black magic. +1

nicholishen · Accepted Answer · 2020-05-12 06:18:15Z

3

Here is the regex way...

df = pd.DataFrame(dict(re.findall(r'(\w+)=(\w+)', x)) for x in df['col'])

edited May 12, 2020 at 6:18

answered May 12, 2020 at 5:38

nicholishen

3,0522 gold badges11 silver badges14 bronze badges

Collectives™ on Stack Overflow

Extract multiple substring matching pattern into columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related