DateFormatter('%Y') for x-axis labels gives strange output for a specific date value

[davehemming]

import numpy as np
import pandas as pd
import matplotlib.dates as md
import matplotlib.pyplot as plt

data = {
    'date': [pd.Timestamp(2014, 9, 28), pd.Timestamp(2014, 10, 5), pd.Timestamp(2014, 10, 12)],
    'val': [0.37,0.44,0.44]
}
df = pd.DataFrame(data=data)
ax = df.plot(x='date', y='val')
ax.xaxis.set_major_formatter(md.DateFormatter('%Y'))

Problem description

When I run the above code the plot generated should have x axis tick labels showing the year e.g. '2014', '2014', '2014', etc. Instead it has the labels '0007', '0007', '0007'.

If I increment the last date from '2014-10-12' to '2014-10-13' in the 'data' variable by one day (or really change it to anything else) like this:

data = {
    'date': [pd.Timestamp(2014, 9, 28), pd.Timestamp(2014, 10, 5), pd.Timestamp(2014, 10, 13)],
    'val': [0.37,0.44,0.44]
}

Then rerun it, it works as expected.

Expected Output

x label on generated plot figure should be '2014', '2014', '2014', ...

What else have you tried?

I ran the same data using only numpy and matplotlib to rule that out as a source of the unexpected output and I was given the expected x-axis label formatting with '2014', '2014', '2014', etc. Here is my code for that;

data = np.array([
    (np.datetime64('2014-09-28'), 0.37),
    (np.datetime64('2014-10-05'), 0.44),
    (np.datetime64('2014-10-12'), 0.44)], 
    dtype=[('date', 'datetime64[D]'), ('val', float)
])

data = data.view(np.recarray)
fig, ax = plt.subplots()
ax.plot(data.date, data.val)
ax.xaxis.set_major_formatter(md.DateFormatter('%Y'))
plt.show()

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.8.final.0
python-bits: 64
OS: Darwin
OS-release: 18.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_AU.UTF-8
LOCALE: en_AU.UTF-8

pandas: 0.24.2
pytest: None
pip: 19.0.3
setuptools: 41.0.0
Cython: None
numpy: 1.16.2
scipy: 1.2.1
pyarrow: None
xarray: 0.11.3
IPython: 7.4.0
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: 4.3.3
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[gfyoung]

cc @TomAugspurger

[jlplazar]

I might be wrong, but this looks like a bug in matplotlib's DateFormatter and the way the ticks are created and formatted (https://matplotlib.org/_modules/matplotlib/dates.html#DateFormatter -> https://matplotlib.org/_modules/matplotlib/dates.html#num2date).

I was able to reproduce the bug with 3, 4 and 5 data points having exactly 7 days of separation.

[jlplazar]

I found the wrongly functioning code uses PeriodConverter. The get_datevalue function call is returning "date.asfreq(freq).ordinal", which is an integer and I'm not familiar with its purpose, but seems to be causing the format errors at a later point. The cases that work use DatetimeConverter (instead of PeriodConverter -- which applies for the evenly-separated timestamps).

[TomAugspurger]

The difference in behavior likely comes down to the inferred frequency for the two datasets. The first is W-SUN

In [26]: pd.DatetimeIndex(df.date).inferred_freq
Out[26]: 'W-SUN'

In [27]: pd.DatetimeIndex(df2.date).inferred_freq

This leads to pandas using different scales for the values of the x axis.

In [30]: ax.get_xticks()
Out[30]: array([2335, 2336, 2337])

In [31]: ax2.get_xticks()
Out[31]:
array([735505., 735507., 735509., 735511., 735513., 735515., 735517.,
       735519.])

Pandas knows how to format those (with our custom formatter) matplotlib doesn't.

Improvements here would be welcome, but I'd like to see the scope of this issue be more narrowly defined.

[CharlesAydin]

Hi there, I was wondering if there was ever any resolution to this. It's easy enough to directly call the matplotlib API, but it'd be more convenient if we didn't have to. :) Thanks!

[vpozdnyakov]

The same problem. Topic was opened at 1 May 2019 (not 1 May 0007, lol)

[miraculixx]

With just a few hours of trial & error :-) I finally found an easy workaround thanks to https://stackoverflow.com/a/57129081/890242.

import pandas as pd
pd.plotting.plot_params = {'x_compat': True}
(...)

Then the plot shows as it is supposed to.

The x_compat option is briefly described in the Pandas Visualization guide.