Split list of lists in various fields of pandas data frame

Question

I have a dataframe like this:

date         message
05/01/2017   field1=aaaa&field2=bbbb&field3=cccc 
05/02/2017   field1=aaaa&field2=bbbb&field3=cccc

I want to transform it like this:

date         field1     field2    field3
05/01/2017   aaaa       bbbb      cccc
05/02/2017   aaaa       bbbb      cccc

But I'm not being able to. This is my code until now:

def split_log_fields(x):
    date = x[0]
    subscription = x[2][x[2].index('=')+1:]
    user_id = x[3][x[3].index('=')+1:]
    status =  x[4][x[4].index('=')+1:]
    return [[date],[subscription],[user_id],[status]]



a = pandas.read_csv(WEBPUSH_SUBSCRIBERS_FILE_NAME,sep='#',header=None)
b= a[1].str.split('&', 2,expand=True)
c = pandas.concat([a,b], axis=1, ignore_index=True)
d = c.apply(split_log_fields,axis=1).to_frame()

The problem is that d is not being separated by the right fields. d is like this now:

field
[[05/01/2017], [aaaa],[bbbb],[cccc]]

Pandas is not splitting the list of lists in different fields.

How can I fix this?

You can have a look at [this answer](http://stackoverflow.com/questions/38384145/pandas-dataframe-splitting-one-column-into-multiple-columns/38384553#38384553). — Psidom, May 03 '17 at 16:56

DYZ · Answer 1 · 2017-05-03T17:02:45.580

Since the messages seem to be coming from HTTP query strings, the most robust way to parse them is to use an HTTP parser. This naturally preserves the key/value pairs:

from urllib.parse import parse_qs
pd.concat([df, df.message.apply(parse_qs).apply(pd.Series)], axis=1)

#         date                              message  field1  field2  field3
#0  05/01/2017  field1=aaaa&field2=bbbb&field3=cccc  [aaaa]  [bbbb]  [cccc]
#1  05/02/2017  field1=aaaa&field2=bbbb&field3=cccc  [aaaa]  [bbbb]  [cccc]

Vaishali · Accepted Answer · 2017-05-03T17:06:43.027

You can use str.extract

df[['f1', 'f2', 'f3']] = df['message'].str.extract('=(\w+)&.*=(\w+)&.*=(\w+)', expand = True)

You get

    date        message                             f1      f2      f3
0   05/01/2017  field1=aaaa&field2=bbbb&field3=cccc aaaa    bbbb    cccc
1   05/02/2017  field1=aaaa&field2=bbbb&field3=cccc aaaa    bbbb    cccc

If you want to drop the original column message,

df.drop('message', axis =1, inplace = True)

EDIT: Using str.split

df[['f1', 'f2', 'f3']] = df['message'].str.split('&', expand = True).replace({'.*=':''}, regex = True)

You get the same result

Split list of lists in various fields of pandas data frame

2 Answers2