I have Datframe with Multiindex and I want to transfer it to Json

this is Datframe

This is the dataframe

When I try to convert it to JSON, and I put to_json() on the orient = index, it converts all the keys to tuple

That's why I tried to put data in this format, but it didn't know how to do it correctly

    data = {
        '2022-01-21': {
            'SF1_8-1':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "lability_Auto":0,
                 "lability_Man":67499,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                },
            'S01_8-2':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "Unavailability_Auto":0,
                 "Unavailability_Man":8475,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                },

            'S01_8-3':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "Unavailability_Auto":0,
                 "Unavailability_Man":8475,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                }
        },
        '2022-01-22': {
            '001_P87-1':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "Unavailability_Auto":0,
                 "Unavailability_Man":8475,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                },

            },
            '001_P-2':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "Unavailability_Auto":0,
                 "Unavailability_Man":8475,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                },

            '001_P-3':
                {"Lock_Auto":0,
                 "Lock_Man":0,
                 "Unavailability_Auto":0,
                 "Unavailability_Man":8475,
                 "ANd":40,
                 "ANR_Remove":0,
                 "AveI":11.01470647,
                 "AveLoss":113.8496936
                },
        }

• Question 1: Is there a way to transfer Multi_index Dataframe to Json
• Question 2: How can I put data like this?

CodePudding user response：

Given:

                    Lock_Auto  Lock_Man  lability_Auto  lability_Man  ANd  ANR_Remove       AveI     AveLoss  Unavailability_Auto  Unavailability_Man
2022-01-21 SF1_8-1          0         0            0.0       67499.0   40           0  11.014706  113.849694                  NaN                 NaN
           S01_8-2          0         0            NaN           NaN   40           0  11.014706  113.849694                  0.0              8475.0
           S01_8-3          0         0            NaN           NaN   40           0  11.014706  113.849694                  0.0              8475.0

Doing:

• Output a dictionary instead of json, so that you can make some modifications before converting it to the final json.
  • Here, I construct a new dictionary by adding a key : empty dict from the first value of the tuple. Then, I append values to that dict with the second value of the tuple as the key.

import json

out = df.to_dict('index')
new = {}
for x, y in out.items():
    if x[0] not in new:
        new[x[0]] = {}
    new[x[0]] |= {x[1]:y}

print(json.dumps(new, indent=4))

Output:

{
    "2022-01-21": {
        "SF1_8-1": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "lability_Auto": 0.0,
            "lability_Man": 67499.0,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936,
            "Unavailability_Auto": NaN,
            "Unavailability_Man": NaN
        },
        "S01_8-2": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "lability_Auto": NaN,
            "lability_Man": NaN,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936,
            "Unavailability_Auto": 0.0,
            "Unavailability_Man": 8475.0
        },
        "S01_8-3": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "lability_Auto": NaN,
            "lability_Man": NaN,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936,
            "Unavailability_Auto": 0.0,
            "Unavailability_Man": 8475.0
        }
    }
}

Optional, filter out nans... this may actually be easier at the out step, as that's one less nest level:

new = {i:{j:{m:z for m, z in y.items() if not np.isnan(z)} for j, y in x.items()} for i, x in new.items()}
print(json.dumps(new, indent=4))

Output:

{
    "2022-01-21": {
        "SF1_8-1": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "lability_Auto": 0.0,
            "lability_Man": 67499.0,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936
        },
        "S01_8-2": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936,
            "Unavailability_Auto": 0.0,
            "Unavailability_Man": 8475.0
        },
        "S01_8-3": {
            "Lock_Auto": 0,
            "Lock_Man": 0,
            "ANd": 40,
            "ANR_Remove": 0,
            "AveI": 11.01470647,
            "AveLoss": 113.8496936,
            "Unavailability_Auto": 0.0,
            "Unavailability_Man": 8475.0
        }
    }
}