pandas dataframe to json by-CodePudding

I have this data table in excel: data table

I want to convert it into below format in json:

[
    {
        "name": "A",
        "time": 1000,
        "value": 3
    },
    {
        "name": "B",
        "time": 1000,
        "value": 7
    },
    {
        "name": "C",
        "time": 1000,
        "value": 3
    },
    {
        "name": "A",
        "time": 2000,
        "value": 7
    },
    {
        "name": "B",
        "time": 2000,
        "value": 3
    },
    {
        "name": "C",
        "time": 2000,
        "value": 9
    }
]

I tried multiple formats but none of them works. Any help is appreciated.

CodePudding user response：

Probably not the most compute efficient but this will do the job. First of all read in your excel file to a pandas dataframe:

df = pd.read_excel("<name>.xlsx")

Then use dictionary comprehension and zip:

json_list = [{"time":time, "a":a, "b":b, "c":c} for time, a, b, c in zip(df["Time"], df["A"], df["B"], df["C"])]

CodePudding user response：

Did it with melt

import pandas as pd

data = {
    'time': [1000, 2000, 3000, 4000, 5000],
    'A': [3, 7, 6, 1, 7],
    'B': [7, 3, 6, 6, 5],
    'C': [9, 9, 5, 3, 5]
}

df = pd.DataFrame(data)

long_df = pd.melt(df, id_vars=['time'], var_name='name')
json = long_df.to_json(orient='records')
print(json)