Wan to get group by date and status from the data

January 17, 2022

I have below data

data=[
{"name":"aa"..."created_at":"2022-01-17 07:38:26.403Z","status":"success"},
{"name":"bb"..."created_at":"2021-12-1 07:38:26.403Z","status":"failed"},
{"name":"kk"..."created_at":"2022-01-13 07:38:26.403Z","status":"success"},

{"name":"ll"..."created_at":"2021-12-17 07:38:26.403Z","status":"success"},

]

I want a report like

created_at,success

December,1
Jan,2

I tried below

 d = defaultdict(int)
 for i in kk:
     if(i["status"] == 'success')
     d[i["status"]]+=1

It give only success count but not able to do in month wise.

>Solution :

You can convert your ‘created_at’ column to datetime using pd.to_datetime, filter for your ‘success’ rows, and then use groupby per month with the aggregation of count:

df['created_at'] = pd.to_datetime(df['created_at'])
out = df.loc[df.status.eq('success')].groupby(df.created_at.dt.month).status.count()

prints:

   created_at  success
0           1        2
1          12        1

# as a dict

>>> out.set_index('created_at').to_dict()
{'success': {1: 2, 12: 1}}

Then you can just convert the month numbers to month names as shown here