Decoding issue when reading in py3 a datetime64 hdf data that was created in py2

[pedroreys]

Code Sample, a copy-pastable example if possible

In Python 2, create the .hdf file with a datetime64[ns] column

#python 2
>>> d = {"data": pd.Timestamp("2020-02-06T18:00")}
>>> df = pd.DataFrame(d, index=[1])
>>> df
                 data
1 2020-02-06 18:00:00
>>> df.dtypes
data    datetime64[ns]
dtype: object
>>> df.to_hdf('py2_data.hdf', 'py2_data')

Reading the hdf in python 2 works just fine

# python2
>>> df = pd.read_hdf('py2_data.hdf', 'py2_data')
>>> df
                 data
1 2020-02-06 18:00:00
>>> df.dtypes
data    datetime64[ns]
dtype: object

Reading the hdf in Python 3 the dtype of the data column is set to int64 instead of datatime64

# python 3
>>> df = pd.read_hdf('py2_data.hdf', 'py2_data')
>>> df
                  data
1  1581012000000000000
>>> df.dtypes
data    int64
dtype: object

Problem description

The reason why the dtype is being loaded as a int64 in python 3 is because pandas is not reading correctly the value_type attribute from the hdf file. The current implementation does not explicitly decodes to "UTF-8" the attribute value when its type is np.bytes_, which causes it to read the dtype as b'datetime64 instead of the correct datetime64 value. That, in turn, causes it to treat the raw value 1581012000000000000 with an inferred dtype of int64 instead of coercing it to a M8[ns] dtype that would give the correct 2020-02-06 18:00:00 datetime value.

This is a similar problem to the one from issue #15725, but that one was when reading the index from the hdf but this one is when reading the data.

The fix for this seems to be really straight forward, you just need to ensure the value of thevalue_type attribute is decoded to UTF-8 when reading it from the hdf file. More specifically, in this line of pytables.py

diff --git a/pytables.py b/pytables.py
index 06c9aa1..9d074d5 100644
--- a/pytables.py
+++ b/pytables.py
@@ -2724,7 +2724,7 @@ class GenericFixed(Fixed):
         if isinstance(node, tables.VLArray):
             ret = node[0][start:stop]
         else:
-            dtype = getattr(attrs, "value_type", None)
+            dtype = _ensure_decoded(getattr(attrs, "value_type", None))
             shape = getattr(attrs, "shape", None)

I can make the change and open a PR, as it seems to be really straight forward, just let me know what you prefer.

Note: this is an issue only when the ".hdf" file is created in python 2 using the fixed format. If it is created using the table format it works as expected.

Thanks!

Expected Output

The expected output is that in python 3 it will parse the dtype of the columns correctly, so in this case it should be:

>>> df = pd.read_hdf('py2_data.hdf', 'py2_data')
>>> df
                 data
1 2020-02-06 18:00:00
>>> df.dtypes
data    datetime64[ns]
dtype: object

Output of pd.show_versions()

``` INSTALLED VERSIONS ------------------ commit : None python : 3.6.10.final.0 python-bits : 64 OS : Darwin OS-release : 18.7.0 machine : x86_64 processor : i386 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 1.0.1
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 45.1.0.post20200127
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : 2.7.1
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : 3.6.1
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : None

</details>

[jreback]

yes this likely straightforward to patch

iirc there is a very similar issue if your would search for it