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]"
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}]
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()}.
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