Pandas - Extracting value from column

Question

I have a Dataframe in the below format. I am trying to break this into different rows for each key value pair.

id, data
101, [{'field': 'type1', 'newValue': '2020-01-16T12:35:50Z', 'oldValue': None}, 
      {'field': 'status', 'newValue': 'Started', 'oldValue': None}]

Expected output:

id, field, newValue, oldValue
101, type1, 2020-01-16T12:35:50Z, None
101, status, Started, None

score 3 · Answer 1 · answered Dec 03 '20 at 17:27

Let's explode the dataframe on data then create a new dataframe from exploded data column and finally use join :

out = df.explode('data').reset_index(drop=True)
out = out.join(pd.DataFrame(out.pop('data').tolist()))

print(out)

    id   field              newValue oldValue
0  101   type1  2020-01-16T12:35:50Z     None
1  101  status               Started     None

score 2 · Accepted Answer · answered Dec 03 '20 at 17:19

You can do this:

In [4432]: df = pd.DataFrame({'id':[101], 'data':[[{'field': 'type1', 'newValue': '2020-01-16T12:35:50Z', 'oldValue': None}, {'field': 'status', 'newValue': 'Started', 'oldValue': None}]]})

In [4438]: df1 = df.explode('data')['data'].apply(pd.Series)

In [4440]: df = pd.concat([df.id, df1], axis=1)

In [4441]: df
Out[4441]: 
    id   field              newValue oldValue
0  101   type1  2020-01-16T12:35:50Z     None
0  101  status               Started     None

Pandas - Extracting value from column

2 Answers2