Custom json from pandas dataframe-CodePudding

I have a question regarding writing json from a python pandas dataframe.

I am reading the data from a csv file with pandas to a dataframe, add some calculated field and then aggregate the data. The dataframe looks the like this:

     country        city          date     chng      population

0        USA     NewYork    2020-01-01     3.55           20.15

1        USA     NewYork    2021-01-01    -1.58           19.84

2         UK      London    2020-01-01     1.38            9.30

3         UK      London    2021-01-01     1.31            9.42

Now I wanna create a json file, which looks somehow like this:

{

    "metrics": [

        {

            "metric": "data.usa.newyork.change",

            "value": 3.55

        },

        {

            "metric": "data.usa.newyork.population",

            " value ": 20.15

        }

    ],

    "metricTime": "2020-01-01",

    "region": "X"

},

{

    "metrics": [

        {

            "metric": "data.usa.newyork.change",

            "value": -1.58

        },

        {

            "metric": "data.usa.newyork.population",

            " value ": 19.84

        }

    ],

    "metricTime": "2021-01-01",

    "region": "X"

},

{

    "metrics": [

        {

            "metric": "data.uk.london.change",

            "value": 1.38

        },

        {

            "metric": "data.uk.london.population",

            " value ": 9.30

        }

    ],

    "metricTime": "2020-01-01",

    "region": "X"

},

{

    "metrics": [

        {

            "metric": "data.uk.london.change",

            "value": 1.31

        },

        {

            "metric": "data.uk.london.population",

            " value ": 9.41

        }

    ],

    "metricTime": "2021-01-01",

    "region": "X"

},

Does someone can help me?

Thanks a lot! :)

CodePudding user response：

I hope I've understood your question well. You can try:

out = [
    {
        "metrics:": [
            {
                "metric": f'data.{g["country"].iat[0].lower()}.{g["city"].iat[0].lower()}.change',
                "value": g["chng"].iat[0],
            },
            {
                "metric": f'data.{g["country"].iat[0].lower()}.{g["city"].iat[0].lower()}.population',
                "value": g["population"].iat[0],
            },
        ],
        "metricTime": g["date"].iat[0],
        "region": "X",
    }
    for _, g in df.groupby(["country", "city", "date"])
]

print(out)

Prints:

[
    {
        "metrics:": [
            {"metric": "data.uk.london.change", "value": 1.38},
            {"metric": "data.uk.london.population", "value": 9.3},
        ],
        "metricTime": "2020-01-01",
        "region": "X",
    },
    {
        "metrics:": [
            {"metric": "data.uk.london.change", "value": 1.31},
            {"metric": "data.uk.london.population", "value": 9.42},
        ],
        "metricTime": "2021-01-01",
        "region": "X",
    },
    {
        "metrics:": [
            {"metric": "data.usa.newyork.change", "value": 3.55},
            {"metric": "data.usa.newyork.population", "value": 20.15},
        ],
        "metricTime": "2020-01-01",
        "region": "X",
    },
    {
        "metrics:": [
            {"metric": "data.usa.newyork.change", "value": -1.58},
            {"metric": "data.usa.newyork.population", "value": 19.84},
        ],
        "metricTime": "2021-01-01",
        "region": "X",
    },
]