Convert pandas.DataFrame to list of dictionaries in Python

Question

I have a dictionary which is converted from a dataframe as below :

a = d.to_json(orient='index')

Dictionary :

{"0":{"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},"1":{"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"}}

What I need is it be in a list, so essentially a list of dictionary. So i just add a [] because that is the format to be used in the rest of the code.

input_dict = [a]

input_dict :

['
{"0":{"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},"1":{"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"}}
']

I need to get the single quotes removed just after the [ and just before the ]. Also, have the PKID values in form of list.

How can this be achieved ?

Expected Output :

[ {"yr":2017,"PKID":[58306, 57011],"Subject":"ABC","ID":"T001"},"1":{"yr":2018,"PKID":[1234,54321],"Subject":"XYZ","ID":"T002"} ]

NOTE : The PKID column has multiple integer values which have to come as a lift of integers. a string is not acceptable. so we need like "PKID":[58306, 57011] and not "PKID":"[58306, 57011]"

"What I need is it be in a list, so essentially a list of dictionary" : sorry but this is not enough to warrant the right answer - please post the exact result you expect. — bruno desthuilliers
– bruno desthuilliers, Commented Feb 28, 2018 at 11:20
Thanks for highlighting it Bruno, I have now added expected results. — Shankar Pandey
– Shankar Pandey, Commented Feb 28, 2018 at 11:47

Norrius · Accepted Answer · 2018-02-28 11:25:26Z

30

pandas.DataFrame.to_json returns a string (JSON string), not a dictionary. Try to_dict instead:

>>> df
   col1  col2
0     1     3
1     2     4
>>> [df.to_dict(orient='index')]
[{0: {'col1': 1, 'col2': 3}, 1: {'col1': 2, 'col2': 4}}]
>>> df.to_dict(orient='records')
[{'col1': 1, 'col2': 3}, {'col1': 2, 'col2': 4}]

edited Feb 28, 2018 at 11:25

answered Feb 28, 2018 at 11:01

Norrius

7,9795 gold badges46 silver badges52 bronze badges

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

4 Comments

Shankar Pandey Over a year ago

Thanks, is there also a way to NOT have index values in and in the same format ? like just : [' {"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},{"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"} ']

Norrius Over a year ago

@ShankarPandey I added another example

Shankar Pandey Over a year ago

Thanks. Is there a way to also make one column values as a LIST. so if col2 had 2 integers seperated by comma, how would we make that an example below : [{'col1': 1, 'col2': [3, 4] }, {'col1': 2, 'col2': [5,6] }]

Norrius Over a year ago

@ShankarPandey Just iterate through the list and transform your values: d['PKID'] = list(map(int, d['PKID'].split(',')))

jpp · Accepted Answer · 2018-02-28 11:25:09Z

3

Here is one way:

from collections import OrderedDict

d = {"0":{"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},"1":{"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"}}

list(OrderedDict(sorted(d.items())).values())

# [{'ID': 'T001', 'PKID': '58306, 57011', 'Subject': 'ABC', 'yr': 2017},
#  {'ID': 'T002', 'PKID': '1234,54321', 'Subject': 'XYZ', 'yr': 2018}]

Note the ordered dictionary is ordered by text string keys, as supplied. You may wish to convert these to integers first before any processing via d = {int(k): v for k, v in d.items()}.

edited Feb 28, 2018 at 11:25

answered Feb 28, 2018 at 10:58

jpp

166k37 gold badges301 silver badges363 bronze badges

3 Comments

bruno desthuilliers Over a year ago

dicts are unordered, so this won't preserve the (eventual) ordering implied by the keys... Might or not be an issue for the the OP...

bruno desthuilliers Over a year ago

Close but not quite there - here the keys are strings so you'll get lexical ordering (ie : sorted(["1", "2", "10", "11"]) => ['1', '10', '11', '2']). You want to convert keys to ints before IMHO ;)

bruno desthuilliers Over a year ago

As I originally mentionned : this "Might or not be an issue for the the OP" - but we actually don't know since the OP didn't post the exact expected output (and obviously everyone interpreted it differently) ;) I only wanted to make clear that your first solution would eventually loose ordering and that the second would use lexical ordering instead of numerical ordering, that's all.

Ryan · Accepted Answer · 2018-02-28 10:58:23Z

0

You are converting your dictionary to json which is a string. Then you wrap your resulting string a list. So, naturally, the result is a string inside of a list.

Try instead: [d] where d is your raw dictionary (not converted json

answered Feb 28, 2018 at 10:58

Ryan

6889 silver badges26 bronze badges

Comments

Rakesh · Accepted Answer · 2018-02-28 10:59:20Z

0

You can use a list comprehension

Ex:

d = {"0":{"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},"1":{"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"}}
print [{k: v} for k, v in d.items()]

Output:

[{'1': {'PKID': '1234,54321', 'yr': 2018, 'ID': 'T002', 'Subject': 'XYZ'}}, {'0': {'PKID': '58306, 57011', 'yr': 2017, 'ID': 'T001', 'Subject': 'ABC'}}]

answered Feb 28, 2018 at 10:59

Rakesh

82.9k17 gold badges86 silver badges122 bronze badges

Comments

RoadRunner · Accepted Answer · 2018-02-28 11:29:40Z

0

What about something like this:

from operator import itemgetter

d = {"0":{"yr":2017,"PKID":"58306, 57011","Subject":"ABC","ID":"T001"},"1":
    {"yr":2018,"PKID":"1234,54321","Subject":"XYZ","ID":"T002"}}

sorted_d = sorted(d.items(), key=lambda x: int(x[0]))

print(list(map(itemgetter(1), sorted_d)))

Which Outputs:

[{'yr': 2017, 'PKID': '58306, 57011', 'Subject': 'ABC', 'ID': 'T001'}, 
 {'yr': 2018, 'PKID': '1234,54321', 'Subject': 'XYZ', 'ID': 'T002'}]

edited Feb 28, 2018 at 11:29

answered Feb 28, 2018 at 11:21

RoadRunner

26.4k6 gold badges46 silver badges77 bronze badges

Collectives™ on Stack Overflow

Convert pandas.DataFrame to list of dictionaries in Python

5 Answers 5

4 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related