Convert dataframe to JSON with 1 common valued column as array field

Question

I am a bit new to Python programming. I have a small requirement where in I need to list down all customers and their amounts for a given fortnight in a JSON format.Currently, I have a dataframe this way -

  FortNight   AmountValue   Customer
  2018-04-29   339632.00    10992     
  2018-04-29   27282.00     10994      
  2018-04-29   26353.00     10995      
  2018-04-29   24797.00     11000  
  2018-04-29   21093.00     10990

Expected -

"Apr-2FN-2018":[           
{"Customer":"10992","AmountValue":"339632.00"},
{"Customer":"10994","AmountValue":"27282.00"},
{"Customer":"10995","AmountValue":"26353.00"},
{"Customer":"11000","AmountValue":"24797.00"},
{"Customer":"10990","AmountValue":"21093.00"}    
]

I tried using

df.(orient = "records", date_format = "iso")

But it retrieves values in the below way-

{"FortNight":"2018-04-29T00:00:00.000Z","AmountValue":21093.8,"Customer":10990}

I tried other ways too, but in vain. Any help is welcome. Thanks in advance!!...

I guess you need some `groupby`. Check this post out https://stackoverflow.com/questions/22219004/grouping-rows-in-list-in-pandas-groupby and tell if it not fits to your case. — vahdet, Jul 05 '18 at 09:36
Thanks vahdet, I checked out the link, but here I require to filter on two columns, I shall check if I can modify that internally as a dict and keep the result in a list. — Sparsha Devapalli, Jul 05 '18 at 09:45
@min2bro - That gives the result as below - dict_values([{"FortNight":"2018-04-29T00:00:00.000Z","AmountValue":21093.8,"Customer":10990}....]) — Sparsha Devapalli, Jul 05 '18 at 09:47
@SparshaDevapalli Got it, you need only first two columns and not the third one — min2bro, Jul 05 '18 at 09:49

user96564 · Accepted Answer · 2018-07-05T10:21:38.377

2

first you need to use set_index for FortNight column and then try this

b = dict(df.set_index('FortNight').groupby(level=0).apply(lambda  x : x.to_json(orient = 'records')))
print(b)
{'2018-04-29': '[{"AmountValue":339632.0,"Customer":10992},{"AmountValue":27282.0,"Customer":10994},{"AmountValue":26353.0,....

edited Jul 05 '18 at 10:21

answered Jul 05 '18 at 10:09

user96564

1,578
5
24
42

1

That's right! I think what is missing is to turn it into dict `dict(df.set_index('FortNight').groupby(level=0).apply(lambda x : x.to_json(orient = 'records')))` – kosnik Jul 05 '18 at 10:18

score 1 · Answer 2 · answered Jul 05 '18 at 09:58

1

Am sure this is not the best (pythonic) way to do it, but I think it will produce the outcome you need

dict(df.groupby('FortNight').apply(lambda x: [{x.columns[1:].values[0] :y[0]} for y in x.values[:,1:]]))

answered Jul 05 '18 at 09:58

kosnik

2,342
10
23

Convert dataframe to JSON with 1 common valued column as array field

2 Answers2