How to sort, group, and aggregate values in a list of nested dictionaries?

Hesoyam 0
Viewed 38

given the list of dictionaries below, I want to do the following things:

1: Sort the following data by key (top level)'name'
2: Sort the by the nested key "name" under key "items"
3: Group values under items by aggregation interval for example "1d"
4: Get again the min max and avg result from step number 3\

Atm, I resolve this by iter down to the values and group them with pandas, aggregate again min max and avg from result. This way feels really tricky, and the performance is not given.

Can someone help me out?

[
    {
        '_id': 2,
        'name': 'b',
        'device': 'b',
        'items': [
            {
                'item_id': 'item_id_2', 'name': 'item_2', 'unit': 'b/s',
                'values': [
                    {'time': datetime.datetime(2022, 9, 5, 15, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 16, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 17, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 18, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 19, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 20, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                ]
            }
        ]
    },
    {
        '_id': 1,
        'name': 'a',
        'device': 'a',
        'items': [
            {
                'item_id': 'item_id_1', 'name': 'item_1', 'unit': 'b/s',
                'values': [
                    {'time': datetime.datetime(2022, 9, 5, 15, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 16, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 17, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 18, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 19, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                    {'time': datetime.datetime(2022, 9, 5, 20, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                ]
            }
        ]
    }
]

As for the result, I would expect something like this:

[
    {
        '_id': 1,
        'name': 'a',
        'device': 'a',
        'items': [
            {
                'item_id': 'item_id_1', 'name': 'item_1', 'unit': 'b/s',
                'values': [
                    {'time': datetime.datetime(2022, 9, 5, 0, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                ]
            }
        ]
    },
    {
        '_id': 1,
        'name': 'b',
        'device': 'b',
        'items': [
            {
                'item_id': 'item_id_2', 'name': 'item_2', 'unit': 'b/s',
                'values': [
                    {'time': datetime.datetime(2022, 9, 5, 0, 0), 'min': 0.0, 'max': 1.0, 'avg': 0.5},
                ]
            }
        ]
    }
]
aggregation dataframe pandas python
1 Answers

With the initial list of dicts that you provided and that I choose to call data, here is one way to do it:

df = pd.DataFrame(data)

# First, sort values
df = df.assign(temp=df["items"].apply(lambda x: x[0]["name"])).pipe(
    lambda df_: df_.sort_values(by="temp").drop(columns="temp").reset_index(drop=True)
)

# Get aggregated as new column 'temp'
dfs = df["items"].apply(lambda x: pd.DataFrame(x[0].pop("values", None)))
df["temp"] = pd.Series(
    [
        {
            k: v[0]
            for k, v in df.set_index("time")
            .resample("D")
            .mean()
            .reset_index()
            .to_dict(orient="list")
            .items()
        }
        for df in dfs
    ]
)
df["items"] = df["items"].apply(lambda x: x[0])

# Merge intermediate dictionaries
df["items"] = df.apply(lambda x: x["items"] | {"values": [x["temp"]]}, axis=1)
df = df.drop(columns="temp")

And so:

print(df.to_json(orient="records"))
# Output
[
    {
        "_id": 1,
        "name": "a",
        "device": "a",
        "items": {
            "item_id": "item_id_1",
            "name": "item_1",
            "unit": "b\\/s",
            "values": [{"time": 1662336000000, "min": 0.0, "max": 1.0, "avg": 0.5}],
        },
    },
    {
        "_id": 2,
        "name": "b",
        "device": "b",
        "items": {
            "item_id": "item_id_2",
            "name": "item_2",
            "unit": "b\\/s",
            "values": [{"time": 1662336000000, "min": 0.0, "max": 1.0, "avg": 0.5}],
        },
    },
]
userCDbL0
Related