Pandas dataframe transformation: adding columns from dictionary k-v pairs

Question

I would like to transform a DataFrame looking like this:

         dictionary
0  {'b': 2, 'a': 1}
1  {'c': 4, 'b': 3}

from

import pandas
df = pandas.DataFrame()
df['dictionary'] = [{'a':1,'b':2},{'b': 3,'c':4}]

onto a DataFrame looking like this:

         dictionary    a  b    c
0  {'b': 2, 'a': 1}  1.0  2  NaN
1  {'c': 4, 'b': 3}  NaN  3  4.0

where (of course) the order of the columns does not matter.

How can I do this without explicitly looping through the dictionaries or the rows ?

score 4 · Answer 1 · answered Jan 22 '17 at 16:55

4

A vectorized approach by converting the given series to it's list representation and then performing concatenation column-wise:

pd.concat([df['dictionary'], pd.DataFrame(df['dictionary'].values.tolist())], axis=1)

answered Jan 22 '17 at 16:55

Nickil Maveli

29,155
8
82
85

score 3 · Answer 2 · edited Jan 23 '17 at 06:53

3

You can apply a pd.Series transformation and concat the two:

pd.concat([df, df['dictionary'].apply(pd.Series)], axis=1)
Out: 
         dictionary    a    b    c
0  {'b': 2, 'a': 1}  1.0  2.0  NaN
1  {'b': 3, 'c': 4}  NaN  3.0  4.0

Or you could use join

In [4]: df.join(df.dictionary.apply(pd.Series))
Out[4]:
           dictionary    a    b    c
0  {u'a': 1, u'b': 2}  1.0  2.0  NaN
1  {u'c': 4, u'b': 3}  NaN  3.0  4.0

edited Jan 23 '17 at 06:53

Zero

74,117
18
147
154

answered Jan 22 '17 at 16:54

ayhan

70,170
20
182
203

2

Neat, `df.join(df.dictionary.apply(pd.Series))` would work too. – Zero Jan 22 '17 at 16:58
@JohnGalt Thank you. That looks much cleaner. – ayhan Jan 22 '17 at 17:00
@JohnGalt Thanks! Looks like your solution is the cleanest, would you make it an answer? – Cedric H. Jan 22 '17 at 21:02
I think, the base for the solution came from ayhan's idea. Happy to add it to his answer. – Zero Jan 23 '17 at 04:58
@JohnGalt I actually didn't add it thinking you might want to add another answer. Feel free to edit this answer or add another one. – ayhan Jan 23 '17 at 06:27

Pandas dataframe transformation: adding columns from dictionary k-v pairs

2 Answers2

Linked