How to pass a dataframe to a specific JSON structure

Juan Almada 0
Viewed 40

my question is the following:

I have this dataframe in pandas:

AVG_SCORE ASD_SCORE QWE_AVG OPO_SCORE HAHA_SCORE
-0.487 -0.560 -0.171 -0.581 0.063
388545 388545 388545 388545 388545

and I want to get a JSON to pass to a POST request from an external endpoint.

The endopint only contemplates the following body, then what I want is to be able to obtain from the dataframe the desired JSON format. Can someone help me? I'm using python 3.9 and pandas:

JSON FORMAT:

{
    "label_1": "description_harcode_here_1",
    "description_harcode_here_2" : [
        {
            "field": "AVG_SCORE",
            "avg": -0.487,
            "count": 388545
        },
        {
            "field": "ASD_SCORE",
            "avg": -0.560,
            "count": 388545
        },
        {
            "field": "QWE_AVG",
            "avg": -0.171,
            "count": 388545
        },
        {
            "field": "OPO_SCORE",
            "avg":  -0.581,
            "count": 388545
        },
        {
            "field": "HAHA_SCORE",
            "avg":  0.063,
            "count": 388545
        }
    ]
}

Thank

pandas json python
2 Answers

Try:

out = {
    "label_1": "description_harcode_here_1",
    "description_harcode_here_2": [],
}
for c in df:
    out["description_harcode_here_2"].append(
        {"field": c, **dict(zip(("avg", "count"), df[c].values))}
    )

print(out)

Prints:

{
    "label_1": "description_harcode_here_1",
    "description_harcode_here_2": [
        {"field": "AVG_SCORE", "avg": -0.487, "count": 388545.0},
        {"field": "ASD_SCORE", "avg": -0.56, "count": 388545.0},
        {"field": "QWE_AVG", "avg": -0.171, "count": 388545.0},
        {"field": "OPO_SCORE", "avg": -0.581, "count": 388545.0},
        {"field": "HAHA_SCORE", "avg": 0.063, "count": 388545.0},
    ],
}
Andrej Kesely0

Try this:

import pandas as pd
import json

# Your df
df = pd.DataFrame(
    [[-0.487, -0.560, -0.171, -0.581, 0.063] ,
     [388545, 388545, 388545, 388545, 388545] ],
     columns = ["AVG_SCORE", "ASD_SCORE", "QWE_AVG", "OPO_SCORE", "HAHA_SCORE"]
)

# add  column names as first row 
df.loc[2]=df.loc[0]
df.loc[0] = list(df.columns.values)


# add index names for columns
df.index = ['field', 'avg', 'count']


# Make description_harcode_here_2 list
result = df.to_dict()
description_harcode_here_2 = [ val for val in result.values()]

# Final json.

final_json = {  "label_1": "description_harcode_here_1",
                           "description_harcode_here_2" : description_harcode_here_2 }

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

Prints:

{
    "label_1": "description_harcode_here_1",
    "description_harcode_here_2": [
        {
            "field": "AVG_SCORE",
            "avg": 388545.0,
            "count": -0.487
        },
        {
            "field": "ASD_SCORE",
            "avg": 388545.0,
            "count": -0.56
        },
        {
            "field": "QWE_AVG",
            "avg": 388545.0,
            "count": -0.171
        },
        {
            "field": "OPO_SCORE",
            "avg": 388545.0,
            "count": -0.581
        },
        {
            "field": "HAHA_SCORE",
            "avg": 388545.0,
            "count": 0.063
        }
    ]
}
RobertG0
Related