Transform column of dictionaries into a single pandas DataFrame

user_7805905 0
Viewed 34

I have a dataframe that looks similiar to this:

                     data
0                    [{'v': 10, 'n': 'metric2'}]
27   [{'v': 20, 'n': 'metric1'}, {'v': 56, 'n': 'metric3'}]
51                   [{'v': 20, 'n': 'metric3'}]
89                   [{'v': 10, 'n': 'metric2'}]

I'd like to transform it into something like this:

    metric1 metric2 metric3
0   NaN     10     NaN
27  20      NaN    56
51  NaN     NaN    20
89  NaN     10     NaN

I was wondering if this is possible?

pandas dataframe dictionary python
2 Answers

There is a bad performance solution with multiple apply, if you have a relative big data , you should using the method provided by cs95

s.apply(pd.Series).stack().apply(pd.Series).set_index('n',append=True).v.unstack('n').sum(level=0)
Out[86]: 
n   metric1  metric2  metric3
0       0.0     10.0      0.0
27     20.0      0.0     56.0
51      0.0      0.0     20.0
89      0.0     10.0      0.0
user_79645270

Here's a pivot based solution with some degree of flattening.

from itertools import chain

df2 = pd.DataFrame(chain.from_iterable(df['data']))
df2.insert(0, 'idx', df.index.repeat(df['data'].str.len()))

df2.pivot(*df2)

n    metric1  metric2  metric3
idx                           
0        NaN     10.0      NaN
27      20.0      NaN     56.0
51       NaN      NaN     20.0
89       NaN     10.0      NaN

In a single line, this operation can be specified with,

(pd.DataFrame(chain.from_iterable(df['data']))
   .assign(idx=df.index.repeat(df['data'].str.len()))
   .pivot('idx', 'n', 'v'))

n    metric1  metric2  metric3
idx                           
0        NaN     10.0      NaN
27      20.0      NaN     56.0
51       NaN      NaN     20.0
89       NaN     10.0      NaN
user_49090870
Related