Converting pandas dataframe to nested dictionary/json format

Question

I am trying to convert the following pandas dataframe(python) into a nested dictionary format. Input data pandas dataframe:

statetraffic   |state    | act       | traffic| reward | header      | time |   id

  stateacttraf |     1   |    1      | 12      |  22     |   str1    |   1572340221000 | 34022100
  stateacttraf |     1   |    2      | 87      |  30     |   str1    |   1572340221000 | 34022100
  stateacttraf |     1   |    3      | 1       |  48     |   str1    |   1572340221000 | 34022100
  stateacttraf |     2   |    1      | 10      |  13     |   str1    |   1572340221000 | 34022100
  stateacttraf |     2   |    2      | 80      |  27     |   str1    |   1572340221000 | 34022100
  stateacttraf |     2   |    3      | 10      |  60     |   str1    |   1572340221000 | 34022100

Have tried the following code but did not work:

1)final_op = input_df.to_dict(orient='records') -> does not provide the answer       
2)from jsonmerge import merge; 
message = {'statetraffic': 'stateacttraf'}; 
message1 = {'time': time.time()}; 
result = merge(final_op, message, message2) -> Neither does this provide the answer either

Some form of nested dictionary is needed

Expecting dictionary/json output like this:

{

{  "statetraffic":"stateacttraf",
   "time":1572340221000,
   "str1":{ 
      "id":34022100,
      "state":1,
      "act":1,
      "trafficSplit":12,
      "reward":22
   }
{ 
   "statetraffic":"stateacttraf",
   "time":1572340221000,
   "str1":{ 
      "id":34022100,
      "state":1,
      "act":2,
      "trafficSplit":87,
      "reward":30
   }
{ 
   "statetraffic":"stateacttraf",
   "time":1572340221000,
   "str1":{ 
      "id":34022100,
      "state":1,
      "act":3,
      "trafficSplit":1,
      "reward":48
   }
{  "statetraffic":"stateacttraf",
   "time":1572340221000,
   "str1":{ 
      "id":34022100,
      "state":2,
      "act":1,
      "trafficSplit":10,
      "reward":13
   }
}

Desperately need the output in this format. So any help will be appreciated.

1)final_op = input_df.to_dict(orient='records') -> does not provide the answer 2) from jsonmerge import merge; message = {'statetraffic': 'stateacttraf'}; message1 = {'time': time.time()}; result = merge(final_op, message, message2) -> Neither does this provide the answer. Some form of nested dictionary is needed but not able to get the code for it. — Datta
– Datta, Commented Oct 29, 2019 at 16:07
Have made edits in the question itself to show the code already tried. — Datta
– Datta, Commented Oct 30, 2019 at 5:42

Rajith Thennakoon · Accepted Answer · 2019-10-30 07:03:46Z

1

Try this,assume your dataframe as df

main_dict = df.to_dict()
uprow= ["statetraffic","time","header"]
drow = ["id","state" ,"act" ,"traffic","reward"]

datalist = []
for c in range(df.shape[0]):
    subd = {}
    for k,v in main_dict.items():
        subd[k] = v[c]

    subd_ = subd.copy()
    tmp = subd.get("header")

    subd[tmp] = 0
    for i in uprow: del subd_[i]
    subd[tmp]=subd_
    for i in drow: del subd[i] 
    del subd["header"]
    datalist.append(subd)

print(datalist)

answered Oct 30, 2019 at 7:03

Rajith Thennakoon

4,1402 gold badges16 silver badges25 bronze badges

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

2 Comments

Datta Over a year ago

Rajith - Thanks a lot for this implementation, this worked for me

Datta Over a year ago

Rajith - Sure done and Thanks a lot for the solution :)

Collectives™ on Stack Overflow

Converting pandas dataframe to nested dictionary/json format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related