[FIXED] Show Average Per Month

Issue

I have the following dataset:

I am trying to create a graph using matplotlib that shows the average number of task by Month & Year.

I was able to complete the calculations for average and was converted the date column to show only the Month and Year. However, when I go to plot, only the year appears.

avg1 = df.groupby(pd.PeriodIndex(df['Date'], freq="M"))['Task Count'].mean().to_frame().reset_index()

avg1 = avg.rename(columns = {"Task Count":'Avg # of Task'})

Plotting:

avg.plot(x="Date", y="Avg # of Tasks",figsize=(15,8))

How do I show the month and year in the graph?

Solution

The data you have provided is very small (3 rows with same date). So, I couldn't create a month-wise plot. So, this is the data I used.

>> df
    Date    Task Count
0   2022-06-25 04:00:00 700
1   2022-06-25 05:00:00 1100
2   2022-06-25 06:00:00 800
3   2022-06-25 07:00:00 1200
4   2022-06-25 08:00:00 700
5   2022-06-25 09:00:00 1400
6   2022-07-25 10:00:00 1600
7   2022-07-03 11:00:00 397
8   2022-07-03 12:00:00 100
9   2022-07-03 13:00:00 400
10  2022-08-03 14:00:00 500
11  2022-08-03 15:00:00 400
12  2022-08-03 16:00:00 300
13  2022-08-03 17:00:00 500
14  2022-08-03 18:00:00 300
15  2022-08-03 19:00:00 400 

You have used avg/avg1 and Task/Tasks alternatively. So, I just used avg and Task instead. This is the updated code

avg = df.groupby(pd.PeriodIndex(df['Date'], freq="M"))['Task Count'].mean().to_frame().reset_index()
avg = avg.rename(columns = {"Task Count":'Avg # of Task'})
avg.plot(x="Date", y="Avg # of Task",figsize=(5,5))

...which gives below line plot. You can see both month and year. Hope this is what you are looking for.

Also, you can use the below code to create a bar chart, which I think might be more relevant in your case. There are many many other graphs you can try out as well...

df=pd.read_excel('myinput.xlsx', 'Sheet19')
avg = df.groupby(pd.PeriodIndex(df['Date'], freq="M"))['Task Count'].mean().to_frame().reset_index()
avg = avg.rename(columns = {"Task Count":'Avg # of Task'})
plt=avg.plot(kind='bar',x="Date", y="Avg # of Task",figsize=(5,5))

Answered By - Redox