CODEWITHSUNDEEP
pythonpandasdataframematplotlibplot
B.W.
B.W.

Reputation: 92

Plotting both date and hour with a counter

I have the following data :

    Unnamed: 0  PanelistID
Date                                       
2020-05-31 17:25:13           0     5555161
2020-05-31 17:26:13           1     5555161
2020-05-31 17:27:13           2     5555161
2020-05-31 17:27:49           3     5555161
2020-05-31 17:28:49           4     5555161
...                         ...         ...
2020-06-01 11:22:39        1029     5555161
2020-06-01 11:23:39        1030     5555161
2020-06-01 11:24:39        1031     5555161
2020-06-01 11:25:39        1032     5555161
2020-06-01 11:26:39        1033     5555161

I want to plot it by hour/date and show the counter of each hour.

So far, I've been able to plot this way :

df1 = df.groupby([df.index.hour, 'PanelistID'])['PanelistID'].count().unstack().plot(kind='bar')

enter image description here

My goal is to merge the records to create this form:

Date                 Amount
2020-05-31 17        60
2020-05-31 18        58
2020-05-31 19        60
...            ... 
2020-06-01 11        42

such that every hour I will sum all the records that I have for that specific hour (and date!) and plot it by the hour and date.

currently, if I have the following records :

2020-05-31 17:27:49           3     5555161
2020-05-31 17:28:49           4     5555161
...                         ...         ...
2020-06-01 17:22:39        1000     5555161
2020-06-01 17:23:39        1001     5555161

The value of "17" on the x-axis will show the data from both 31/5 and 6/1.

Thanks!

Upvotes: 1

Views: 44

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

df.index.hour drops the dates and gives you the hour only. I think you want .floor:

 df1 = df.groupby([df.index.floor('H'), 'PanelistID'])['PanelistID'].count().unstack().plot(kind='bar')

Also, if you only have one PanelistID, you don't need to group by PanelistID:

 df1 = df.groupby(df.index.floor('H'))['PanelistID'].count().plot(kind='bar')

If you have several PanelistID, you can use value_counts:

df1 = df.groupby(df.index.floor('H')['PanelistID'].value_counts().unstack().plot.bar()

Upvotes: 1

Related Questions