BUG:ValueError: Invalid frequency with date

[lygujiaxin]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

---

Code Sample

import pandas as pd

df = pd.read_hdf('metr-la.h5')
print(df)

metr-la.h5
https://drive.google.com/open?id=10FOTa6HXPqX8Pf5WRoRwcFnW9BrNZEIX
It's index is DataTime type, data is float64 type.

Output

Traceback (most recent call last):
  File "C:\Test.py", line 5, in <module>
    print(pandas_df)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\frame.py", line 751, in __repr__
    show_dimensions=show_dimensions,
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\frame.py", line 881, in to_string
    line_width=line_width,
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\io\formats\format.py", line 630, in __init__
    self._chk_truncate()
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\io\formats\format.py", line 716, in _chk_truncate
    frame = concat((frame.iloc[:row_num, :], frame.iloc[-row_num:, :]))
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 284, in concat
    sort=sort,
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 454, in __init__
    self.new_axes = self._get_new_axes()
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 521, in _get_new_axes
    for i in range(ndim)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 521, in <listcomp>
    for i in range(ndim)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 574, in _get_concat_axis
    concat_axis = _concat_indexes(indexes)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\reshape\concat.py", line 592, in _concat_indexes
    return indexes[0].append(indexes[1:])
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\indexes\base.py", line 4153, in append
    return self._concat(to_concat, name)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\indexes\base.py", line 4161, in _concat
    result = _concat.concat_compat(to_concat)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\dtypes\concat.py", line 162, in concat_compat
    return concat_datetime(to_concat, axis=axis, typs=typs)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\dtypes\concat.py", line 392, in concat_datetime
    result = type(to_concat[0])._concat_same_type(to_concat, axis=axis)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\arrays\datetimelike.py", line 695, in _concat_same_type
    if all(pair[0][-1] + obj.freq == pair[1][0] for pair in pairs):
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\arrays\datetimelike.py", line 695, in <genexpr>
    if all(pair[0][-1] + obj.freq == pair[1][0] for pair in pairs):
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\arrays\datetimelike.py", line 540, in __getitem__
    return self._box_func(result)
  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64\lib\site-packages\pandas\core\arrays\datetimes.py", line 476, in <lambda>
    return lambda x: Timestamp(x, freq=self.freq, tz=self.tz)
  File "pandas\_libs\tslibs\timestamps.pyx", line 1083, in pandas._libs.tslibs.timestamps.Timestamp.__new__
  File "pandas\_libs\tslibs\offsets.pyx", line 3580, in pandas._libs.tslibs.offsets.to_offset
ValueError: Invalid frequency: b"ccopy_reg\n_reconstructor\np1\n(cpandas.tseries.offsets\nMinute\np2\nc__builtin__\nobject\np3\nNtRp4\n(dp5\nS'normalize'\np6\nI00\nsS'_offset'\np7\ncdatetime\ntimedelta\np8\n(I1\nI0\nI0\ntRp9\nsS'_use_relativedelta'\np10\nI00\nsS'kwds'\np11\n(dp12\nsS'n'\nI5\nsb."

Problem description

Pandas seems to work incorrect with hdf5 file created by old version pandas.
The file work well with pandas 0.19.2. Now, many method will raise ValueError: Invalid frequency.

I am not sure what causes this error, but I found that the DataFrame's index is DatetimeArray type instead of DatetimeIndex type which may be the reason.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : f2ca0a2
python : 3.7.8.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.18362
machine : AMD64
processor : Intel64 Family 6 Model 42 Stepping 7, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 1.1.1
numpy : 1.19.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.2.2
setuptools : 47.1.0
Cython : None
pytest : 5.4.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.5.0
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.2
IPython : 7.13.0
pandas_datareader: None
bs4 : 4.9.0
bottleneck : None
fsspec : 0.7.2
fastparquet : None
gcsfs : None
matplotlib : 3.2.1
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.3
pandas_gbq : None
pyarrow : 0.17.0
pytables : None
pyxlsb : None
s3fs : 0.2.2
scipy : 1.4.1
sqlalchemy : None
tables : 3.6.1
tabulate : 0.8.7
xarray : 0.15.1
xlrd : 1.2.0
xlwt : None
numba : 0.48.0

[bashtage]

Can you provide a copy, pastable and runnable example? See https://matthewrocklin.com/blog/work/2018/02/28/minimal-bug-reports for how to write a helpful bug report.

Also, how was the h5 data created?

[rsheftel]

I get a similar error when upgrading from pandas v1.0.5 to v1.1.1. I have example hdf5 files that cause the error, but I cannot find out how to upload them here.

I have an hdf5 file that was saved with a DataFrame with a DateTimeIndex as an hdf5 with the following:

to_hdf(filename, key='data', mode='w', format='fixed', complib='blosc:blosclz', complevel=1)

That data was saved in a version of pandas < 1.0 (I do not remember which version).

Loading in v1.0.5 works fine

Loading in v1.1.1 with read_hdf() works, but when trying to access the index with DataFrame.index results in the following:

current_data.index
Out[22]: Traceback (most recent call last):
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\IPython\core\formatters.py", line 224, in catch_format_error
    r = method(self, *args, **kwargs)
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\IPython\core\formatters.py", line 702, in __call__
    printer.pretty(obj)
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\IPython\lib\pretty.py", line 394, in pretty
    return _repr_pprint(obj, self, cycle)
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\IPython\lib\pretty.py", line 700, in _repr_pprint
    output = repr(obj)
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\core\indexes\base.py", line 847, in __repr__
    data = self._format_data()
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\core\indexes\base.py", line 891, in _format_data
    return format_object_summary(
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\io\formats\printing.py", line 379, in format_object_summary
    first = formatter(obj[0])
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\core\indexes\extension.py", line 215, in __getitem__
    result = self._data[key]
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\core\arrays\datetimelike.py", line 540, in __getitem__
    return self._box_func(result)
  File "C:\Users\rshef\.conda\envs\dev\lib\site-packages\pandas\core\arrays\datetimes.py", line 476, in <lambda>
    return lambda x: Timestamp(x, freq=self.freq, tz=self.tz)
  File "pandas\_libs\tslibs\timestamps.pyx", line 1083, in pandas._libs.tslibs.timestamps.Timestamp.__new__
  File "pandas\_libs\tslibs\offsets.pyx", line 3580, in pandas._libs.tslibs.offsets.to_offset
ValueError: Invalid frequency: b'ccopy_reg\n_reconstructor\np0\n(cpandas.tseries.offsets\nDay\np1\nc__builtin__\nobject\np2\nNtp3\nRp4\n(dp5\nVn\np6\nI1\nsVnormalize\np7\nI00\nsV_cache\np8\n(dp9\nsb.'

[TomAugspurger]

Does anyone have a copy-pastable example creating the data in memory and then writing to HDF5?

[rsheftel]

This code will create the error. I ran it in python 3.8.5 with two virtual environments for pandas 0.25 and pandas 1.1.1

I chose pandas 0.25 because I knew that would cause the problem, I am not saying that it would not happen in other versions. The error only started with pandas 1.1.1, it works fine in pandas 1.0.5

### make the hdf5 file with pandas 0.25

import pandas as pd

pd.__version__

test_data = pd.DataFrame({'open': [1, 2, 3, 4, 5], 'close': [6, 7, 8, 9, 10]},
                         index=pd.date_range('2010-06-01 17:00', periods=5, freq='1D', tz='America/New_York'),
                         columns=['open', 'close'])
                         
filename = "test_out.h5"

print('writing hdf5 file')
test_data.to_hdf(filename, key='data', mode='w', format='fixed', complib='blosc:blosclz', complevel=1)

### load the hdf5 with pandas 1.1.1

import pandas as pd

pd.__version__

print ('reading hdf5 file')

read_data = pd.read_hdf(filename)

print(read_data)

# this line wil cause the error
print(read_data.index)

[bashtage]

The problem is that freq is stored as a pickly in the h5 form 0.25, but is an object in master.

Using the 0.25 h5 file:

In [26]: f.get_node("/data/axis1").attrs
Out[26]:
/data/axis1._v_attrs (AttributeSet), 9 attributes:
   [CLASS := 'CARRAY',
    TITLE := '',
    VERSION := '1.1',
    freq := b'ccopy_reg\n_reconstructor\np0\n(cpandas.tseries.offsets\nDay\np1\nc__builtin__\nobject\np2\nNtp3\nRp4\n(dp5\nVn\np6\nI1\nsVnormalize\np7\nI00\nsV_cache\np8\n(dp9\nsb.',
    index_class := 'datetime',
    kind := 'datetime64',
    name := None,
    transposed := True,
    tz := 'America/New_York']

Using master

In [30]: f.get_node("/data/axis1").attrs
Out[30]:
/data/axis1._v_attrs (AttributeSet), 9 attributes:
   [CLASS := 'CARRAY',
    TITLE := '',
    VERSION := '1.1',
    freq := <Day>,
    index_class := 'datetime',
    kind := 'datetime64',
    name := None,
    transposed := True,
    tz := 'America/New_York']

[daviskirk]

For anyone who runs into this before it's fixed: As a workaround you can manually assign the index freq that is broken

print(df)  # assuming this is the pandas 1.1 loaded data it will raise the ValueError
df.index.freq='1d'  # Manually assign the index frequency
df.index.freq = df.index.inferred_freq  # might also work if it's inferrable
print(df)  # will now work

[Pyxtraker]

For anyone who runs into this before it's fixed: As a workaround you can manually assign the index freq that is broken

print(df)  # assuming this is the pandas 1.1 loaded data it will raise the ValueError
df.index.freq='1d'  # Manually assign the index frequency
df.index.freq = df.index.inferred_freq  # might also work if it's inferrable
print(df)  # will now work

In my case manual or inferred assignment did not work, however this worked :

new_index = pd.bdate_range(
    np.datetime64(my_df.index.values.min()),
    np.datetime64(my_df.index.values.max()))
my_df = my_df.reindex(db)```

[joha1]

I also have this issue with some rather old hdf files (2+ years, made with python 2.7 and who knows what pandas version).
As a quick fix,

df.index.freq = df.index.inferred_freq # might also work if it's inferrable

works for me with a regular (monthly) index.