Convert pd.Grouper to human-readable format on plt.plot

Question

I'm using Pandas and Matplotlib to plot some data from an SQL database.

Here are my steps:

• fetch the data from the DB into a pd.DataFrame
• group them using a Grouper('MS')
• aggregate to count how many items are there in each group
• draw the chart

df = df.groupby(Grouper(key='published_at', freq='MS'))['id'].count()
ax = df.plot.bar(position=0.5, width=0.4, label="Items")

This is what my plot looks like :

I'd like to show the months as "2019-04", so "Y-M", but I can't figure out how to do it.

As I'm totally new to Python, any help would be greatly appreciated. Thank you !

Answer 1

The following works with you sample data, but may fail with a lot of dates:

tmp_df = df.resample('MS',on='published_at').id.count()
plt.figure(figsize=(10,6))
plt.bar(tmp_df.index.strftime("%Y-%m"), tmp_df)
plt.show()

Output:

Answer 2

Seems you just want to reformat the datetime column.

Also looks like you have already converted this column in the proper format, if not start from line 2, otherwise start from line 5.

# Convert to datetime
df['published_at'] = pd.to_datetime(df['published_at'])

# You can start from here, if you have already converted your column
df['published_at_YM'] = df['DOB'].dt.strftime('%Y-%m')

df = df.groupby(Grouper(key='published_at_YM', freq='MS'))['id'].count()

ax = df.plot.bar(position=0.5, width=0.4, label="Items")