Convert two different dataframes to one json file using pandas

Question

My first dataframe df_gammask looks like that:

    distance breakEvenDistance  min max
0   2.1178  2.0934  NaN         0.000955
1   2.0309  2.1473  0.000955    0.001041
2   1.9801  1.7794  0.001041    0.001124
3   1.9282  2.1473  0.001124    0.001199
4   1.8518  1.5885  0.001199    0.001259
5   1.8518  1.5151  0.001259    0.001319

And my second df_gammabid:

distance    breakEvenDistance   min max
0   1.9999  1.9329  NaN         0.001034
1   1.9251  2.0670  0.001034    0.001118
2   1.8802  1.6758  0.001118    0.001193
3   1.8802  1.5956  0.001193    0.001252
4   1.7542  1.5181  0.001252    0.001317
5   1.7542  1.4541  0.001317    0.001374

What I would need is to have a json file like that one:

{
  "buy": [
    {
      "distance": 0.6278,
      "breakEvenDistance": 0.6261,
      "max": 0.0031920626236615754
    },
    {
      "distance": 0.6224,
      "breakEvenDistance": 0.6199,
      "min": 0.0031920626236615754,
      "max": 0.003223405873670448
    },
    {
      "distance": 0.6202,
      "breakEvenDistance": 0.6142,
      "min": 0.003223405873670448,
      "max": 0.003253791039488344
    },
    {
      "distance": 0.6174,
      "breakEvenDistance": 0.6081,
      "min": 0.003253791039488344,
      "max": 0.003285709011703031}],


"sell": [
    {
      "distance": 0.8012,
      "breakEvenDistance": 0.8005,
      "max": 0.0024962095663052064
    },
    {
      "distance": 0.7996,
      "breakEvenDistance": 0.7939,
      "min": 0.0024962095663052064,
      "max": 0.002516799325547373
    },
    {
      "distance": 0.794,
      "breakEvenDistance": 0.7877,
      "min": 0.002516799325547373,
      "max": 0.0025370182220432014
    },
    {
      "distance": 0.7927,
      "breakEvenDistance": 0.7807,
      "min": 0.0025370182220432014,
      "max": 0.0025605480833123294
    }]

I know there is the function pd.DataFrame.to_json but it works for one dataframe, any clue on how to do it with 2 dataframes and in the above format? do I have to merge them? The buy side is the df_gammask and the sell side is the dg_gammabid! thanks

jezrael · Accepted Answer · 2019-03-12 11:25:30Z

5

Use DataFrame.to_dict in nested dictionary comprehension for remove missing values, then create dictionary and convert to json:

import json
L1 = [{k: v for k, v in x.items() if pd.notnull(v)} for x in df_gammask.to_dict('r')]
L2 = [{k: v for k, v in x.items() if pd.notnull(v)} for x in df_gammabid.to_dict('r')]

with open('file.json', 'w') as file:
    json.dump({ "buy": L1, "sell": L2}, file)

edited Mar 12, 2019 at 11:25

answered Mar 12, 2019 at 10:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Viktor.w Over a year ago

without the Nan, is what I was looking for in fact, thanks! But what is the format of the variable j then? How can I write this value on a file locally? Thanks

Amir · Accepted Answer · 2019-03-12 11:07:10Z

4

Firs convert your dataframes to dictionaries:

dict_gammask = df_gammask.to_dict()
dict_gammabid = df_gammabid.to_dict()

Then you put them in another dictionary, in the structure you want:

result_dict = {'buy': dict_gammabid, 'sell': dict_gammask}

Then you can convert that to json:

import json
json_result = json.dumps(result_dict)

Or to save it to a file:

with open('data.json', 'w') as outfile:
    json.dump(result_dict, outfile)

edited Mar 12, 2019 at 11:07

answered Mar 12, 2019 at 10:25

Amir

1,9153 gold badges22 silver badges42 bronze badges

3 Comments

Viktor.w Over a year ago

Amir , thanks but how can I save the json_result on a json file localy? thanks

Amir Over a year ago

@Viktor.w import json with open('data.json', 'w') as outfile: json.dump(data, outfile)

Dudelstein Over a year ago

Using this code, the final JSON format is different from OP's requirement (using pandas 1.4.2).

Collectives™ on Stack Overflow

Convert two different dataframes to one json file using pandas

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related