Convert json dict with lists

atb Published at Dev

ATB

I have pulled JSON data from a url. The result is a dictionary. How can I transform this dictionary so metric is a column, and the time is the index for each value

Thanks in advance

{
    "metricData": {
        "metrics": ["AdrActCnt", "BlkCnt", "BlkSizeByte"],
        "series": [
            {
                "time": "2021-01-28T00:00:00.000Z",
                "values": ["1097896.0", "145.0", "190568423.0"]
            },
            {
                "time": "2021-01-29T00:00:00.000Z",
                "values": ["1208741.0", "152.0", "199725189.0"]
            },
            {
                "time": "2021-01-30T00:00:00.000Z",
                "values": ["1087755.0", "136.0", "177349536.0"]
            }
        ]
    }
}

time------------------------AdrActCnt-----BlkCnt------BlkSizeByte
    
2021-01-28T00:00:00.000Z----1097896.0-----145.0-------190568423.0
2021-01-29T00:00:00.000Z----1208741.0-----152.0-------199725189.0
2021-01-29T00:00:00.000Z----1087755.0-----136.0-------177349536.0

Arty

I expect that you wanted to have final result as pandas dataframe. Next code achieves this. Also I expect that your data is already parsed from JSON to Python dictionary format, if not just do data = json.loads(json_text).

Try it online!

def to_df(data):
    import pandas as pd
    df = pd.DataFrame(
        data = [
            [e['time']] + [(float(e2) if e2 is not None else e2) for e2 in e['values']]
            for e in data['metricData']['series']
        ],
        columns = ['time'] + data['metricData']['metrics'],
    ).set_index('time')
    df.index = pd.to_datetime(df.index)
    return df

data = {
    "metricData": {
        "metrics": ["AdrActCnt", "BlkCnt", "BlkSizeByte"],
        "series": [
            {
                "time": "2021-01-28T00:00:00.000Z",
                "values": ["1097896.0", "145.0", "190568423.0"]
            },
            {
                "time": "2021-01-29T00:00:00.000Z",
                "values": ["1208741.0", "152.0", "199725189.0"]
            },
            {
                "time": "2021-01-30T00:00:00.000Z",
                "values": ["1087755.0", "136.0", "177349536.0"]
            }
        ]
    }
}

print(to_df(data))

Output:

                           AdrActCnt  BlkCnt  BlkSizeByte
time
2021-01-28 00:00:00+00:00  1097896.0   145.0  190568423.0
2021-01-29 00:00:00+00:00  1208741.0   152.0  199725189.0
2021-01-30 00:00:00+00:00  1087755.0   136.0  177349536.0

Collected from the Internet

Please contact [email protected] to delete if infringement.