Python Pandas dataframe to JSON file

warem 0
Viewed 27

My dataframe is:

import pandas as pd
from tabulate import tabulate

data0 = {'dir':[0,'','',90,'','','']}
data1 = {'dist':['0 to 1h','1h to 2h','2h to 3h','0 to 1h','1h to 2h','2h to 3h','> 3h']}
data2 = {'max':[-0.271, -0.17 , -0.034, -0.322, -0.208, -0.057, 0.018]}
data3 = {'min':[-0.441, -0.339, -0.203, -0.491, -0.378, -0.227, -0.151]}
df0 = pd.DataFrame(data0)
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df3 = pd.DataFrame(data3)
pressure = []
pressure.append(df0)
pressure.append(df1)
pressure.append(df2)
pressure.append(df3)
df = pd.concat(pressure, axis=1)
print(tabulate(df, headers='keys', showindex=False))

It is like this:

enter image description here

I want the dataframe to be exported to a JSON file in the way below. I am sorry that I don't know how to express it in English. So, how to do it? Thank you.

{
  "0": [
    {
      "max": {
        "0 to 1h": -0.271
      },
      "min": {
        "0 to 1h": -0.441
      }
    },
    {
      "max": {
        "1h to 2h": -0.17
      },
      "min": {
        "1h to 2h": -0.339
      }
    },
    {
      "max": {
        "2h to 3h": -0.034
      },
      "min": {
        "2h to 3h": -0.203
      }
    }
  ],
  "90": [
    {
      "max": {
        "0 to 1h": -0.322
      },
      "min": {
        "0 to 1h": -0.491
      }
    },
    {
      "max": {
        "1h to 2h": -0.208
      },
      "min": {
        "1h to 2h": -0.378
      }
    },
    {
      "max": {
        "2h to 3h": -0.057
      },
      "min": {
        "2h to 3h": -0.227
      }
    },
    {
      "max": {
        " > 3h ": 0.018
      },
      "min": {
        " > 3h ": -0.151
      }
    }
  ]
}
dataframe pandas json
1 Answers

Here is one way o do it:

# Put values in dicts
df["items"] = df.apply(
    lambda x: {"max": {x["dist"]: x["max"]}, "min": {x["dist"]: x["min"]}}, axis=1
)

# Add missing values
df["dir"] = df["dir"].replace("", pd.NA).fillna(method="ffill")

# Make a dict of target values
data = {
    key: value["items"]
    for key, value in df[["dir", "items"]]
    .groupby("dir")
    .agg(list)
    .to_dict("index")
    .items()
}

with open("file.json", "w") as f:
    json.dump(data, f)

In file.json:

{
    "0": [
        {
            "max": {
                "0 to 1h": -0.271
            },
            "min": {
                "0 to 1h": -0.441
            }
        },
        {
            "max": {
                "1h to 2h": -0.17
            },
            "min": {
                "1h to 2h": -0.339
            }
        },
        {
            "max": {
                "2h to 3h": -0.034
            },
            "min": {
                "2h to 3h": -0.203
            }
        }
    ],
    "90": [
        {
            "max": {
                "0 to 1h": -0.322
            },
            "min": {
                "0 to 1h": -0.491
            }
        },
        {
            "max": {
                "1h to 2h": -0.208
            },
            "min": {
                "1h to 2h": -0.378
            }
        },
        {
            "max": {
                "2h to 3h": -0.057
            },
            "min": {
                "2h to 3h": -0.227
            }
        },
        {
            "max": {
                "> 3h": 0.018
            },
            "min": {
                "> 3h": -0.151
            }
        }
    ]
}
userCDbL0
Related