[FIXED] Transform data from dataframe to json use pandas

Issue

How transform data from dataframe

pd.DataFrame(
    [
        ['2021-12-14 12:00:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:15:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:15:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:30:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:45:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 13:00:00','subgroup_1','group_1','Subgroup 1'],
        ['2021-12-14 12:30:00','subgroup_3','group_2','Subgroup 3'],
        ['2021-12-14 12:45:00','subgroup_3','group_2','Subgroup 3'],
        ['2021-12-14 13:00:00','subgroup_3','group_2','Subgroup 3'],
    ], columns=['timestamp','subgroup','group','name']
)

to json

use pandas. Please, help me.

Solution

My solution :

df["timestamp"] = pd.to_datetime(df.timestamp)

out = {}

for k, df_group in df.groupby("group"):
    out[k] = []
    for _, df_subgroup in df_group.groupby("subgroup"):
        name = df_subgroup["name"].values[0]  # Assuming `name` is unique in this group
        count = len(df_subgroup)
        timegroup = [
            {"index": k, "value": v}
            for k, v in df_subgroup.groupby("timestamp")
            .name.count()
            .to_dict()
            .items()
        ]

        # Complete with missing timestamps
        for ts in df.timestamp.unique():
            if ts not in [t["index"] for t in timegroup]:
                timegroup.append({"index": pd.Timestamp(ts), "value": 0})

        # Sort by timestamp
        timegroup = sorted(timegroup, key=lambda x: x["index"])
        
        # Convert timestamp into strings
        for t in timegroup:
            t["index"] = t["index"].strftime("%Y-%m-%d %H:%M:%S")
  
        out[k].append({"name": name, "count": count, "timegroup": timegroup})

Result in out :

{
   "group_1": [
      {
         "name": "Subgroup 1",
         "count": 6,
         "timegroup": [
            {
               "index": "2021-12-14 12:00:00",
               "value": 1
            },
            {
               "index": "2021-12-14 12:15:00",
               "value": 2
            },
            {
               "index": "2021-12-14 12:30:00",
               "value": 1
            },
            {
               "index": "2021-12-14 12:45:00",
               "value": 1
            },
            {
               "index": "2021-12-14 13:00:00",
               "value": 1
            }
         ]
      }
   ],
   "group_2": [
      {
         "name": "Subgroup 3",
         "count": 3,
         "timegroup": [
            {
               "index": "2021-12-14 12:00:00",
               "value": 0
            },
            {
               "index": "2021-12-14 12:15:00",
               "value": 0
            },
            {
               "index": "2021-12-14 12:30:00",
               "value": 1
            },
            {
               "index": "2021-12-14 12:45:00",
               "value": 1
            },
            {
               "index": "2021-12-14 13:00:00",
               "value": 1
            }
         ]
      }
   ]
}

Answered By - thomask