DatetimeIndex formatting when plotting with pandas

I'm trying to plot data from pandas that has been grouped by months and years. However I struggle to format the resulting plot's x-axis. It seems I can only change the right-most xtick label, and not the others. Also, when changing the format, the date gets reset. When converting the DatetimeIndex to strings and replacing the xticklabels there is only the last one updated. Somehow pandas does some shenanigangs in matplotlib I don't understand.

I like how there is only every 3rd month labeled, but it should also contain the year with each label. How can I achieve this?

With the base formatting I get

And with the formatter replacement I get this weird thing:

Code to generate these plots (python 3.9.13, pandas 1.4.4 and matplotlib 3.5.2):

from matplotlib import pyplot as plt
import pandas as pd
import numpy as np

# generate DataFrame
df = pd.DataFrame(data = {
    "value": np.random.rand(1000),
    "day": np.random.randint(1, 28+1, 1000),
    "month": np.random.randint(1, 12+1, 1000),
    "year": np.random.randint(2005, 2010+1, 1000)
})
df["date"] = pd.to_datetime(df[['year', 'month', 'day']])

# group the data by month (and also year)
monthly = df.groupby(pd.Grouper(key="date", freq="M")).sum()

# plot the data
ax = monthly["2008-02":"2009-03"].value.plot.line()

# optional different formatter I've tried
import matplotlib.dates as mdates
from matplotlib.dates import DateFormatter
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
date_form = DateFormatter("%m-%Y")
ax.xaxis.set_major_formatter(date_form)

try this:

from matplotlib import pyplot as plt
import pandas as pd
import numpy as np
import matplotlib.dates as mdates

# generate DataFrame
df = pd.DataFrame(data={
    "value": np.random.rand(1000),
    "day": np.random.randint(1, 28+1, 1000),
    "month": np.random.randint(1, 12+1, 1000),
    "year": np.random.randint(2005, 2010+1, 1000)
})
df["date"] = pd.to_datetime(df[['year', 'month', 'day']])

# group the data by month (and also year)
monthly = df.groupby(pd.Grouper(key="date", freq="M")).sum()

# plot the data
fig, ax = plt.subplots()
ax.plot(monthly.index, monthly["value"])

# format x-axis ticks
ax.xaxis.set_major_locator(mdates.MonthLocator(interval=3))  # Show every 3rd month
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))  # Format as abbreviated month and year

# set minor locator for months
ax.xaxis.set_minor_locator(mdates.MonthLocator())

# rotate the x-axis tick labels for better readability (optional)
plt.xticks(rotation=45)

# display the plot
plt.show()

• The issue is the values in the dataframe index are pandas._libs.tslibs.timestamps.Timestamp type.
  • The issue can be resolved by changing the type to datetime.date, which can be done by selecting only the .date component, since the .time component is irrelevant.
  • pandas.DataFrame.plot can still be used to plot the dataframe.
• Some methods, such as pd.Grouper, and selecting an index range with monthly["2008-02":"2009-03"], require the .Timestamp type to work, so don't change the type until other formatting and manipulations are complete.

import matplotlib.dates as mdates

# sum the value data by month (and also year)
monthly = df.groupby(pd.Grouper(key="date", freq="M")).value.sum()

# extract the date component
monthly.index = monthly.index.date

# plot a line
ax = monthly.plot(rot=45)

# format x-axis ticks
ax.xaxis.set_major_locator(mdates.MonthLocator(interval=3))  # Show every 3rd month
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))  # Format as abbreviated month and year

# set minor locator for months
ax.xaxis.set_minor_locator(mdates.MonthLocator())

# sum the value data by month (and also year)
monthly = df.groupby(pd.Grouper(key="date", freq="M")).value.sum()

# selected range
sel = monthly.loc["2008-02-28":"2009-03-31"]

# extract the date component
sel.index = sel.index.date

# plot a line
ax = sel.plot(rot=45)

# format x-axis ticks
ax.xaxis.set_major_locator(mdates.MonthLocator(interval=3))  # Show every 3rd month
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))  # Format as abbreviated month and year

# set minor locator for months
ax.xaxis.set_minor_locator(mdates.MonthLocator())