to_html() formatters does not work for objects with 'datetime64[ns]' type

[shishirsharma]

I am using a to_html() to convert a dataframe into a table. i have various datatypes in Dataframe including float, int, datetime and timedelta.

I am using formatters to control outputs, It seems to be working for int and float but not for datetime.

datetime_formatter = lambda ts: "%s" % ts.strftime("%I:%M %p")
currency_formatter = lambda cash: "%.2f" % cash

[jreback]

pls show a small but minimally reproducible example, along with pd.show_versions()

[haleemur]

I encountered the same problem, and then did some digging. The issue is reproduced with the following example.

import pandas as pd
df = pd.DataFrame({'months': ['2015-01-01', '2015-10-10', '2016-01-01']})
df.months = pd.to_datetime(df.months)
df.to_html(formatters={'months': lambda x: x.strftime('%Y-%m')})

this prints:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>months</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2015-01-01</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2015-10-10</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2016-01-01</td>
    </tr>
  </tbody>
</table>

As you can see, the dates are not formatted using the specified function.

The class pandas.formats.format.Datetime64Formatter is responsible for the formatting, however it does nothing if a formatter is passed to it's init. Browsing through the file, I found a class pandas.formats.format.Datetime64TZFormatter which inherits from Datetime64Formatter, which does respect formatter.

from pandas.formats.format import Datetime64Formatter, Datetime64TZFormatter

Datetime64TZFormatter(df.months, formatter=lambda x: x.strftime('%Y-%m')).get_result()
#prints
['2015-01', '2015-10, '2016-01']

Datetime64Formatter(df.months, formatter=lambda x: x.strftime('%Y-%m')).get_result()
#prints
['2015-01-01', '2015-10-10', '2016-01-01']

I propose to change method Datetime64Formatter._format_strings from

    def _format_strings(self):
        """ we by definition have DO NOT have a TZ """

        values = self.values
        if not isinstance(values, DatetimeIndex):
            values = DatetimeIndex(values)

        fmt_values = format_array_from_datetime(
            values.asi8.ravel(),
            format=_get_format_datetime64_from_values(values,
                                                      self.date_format),
            na_rep=self.nat_rep).reshape(values.shape)
return fmt_values.tolist()

to

    def _format_strings(self):
        """ we by definition have DO NOT have a TZ """

       if self.formatter is not None:
           return [formatter(x) for x in values]

        values = self.values
        if not isinstance(values, DatetimeIndex):
            values = DatetimeIndex(values)

        fmt_values = format_array_from_datetime(
            values.asi8.ravel(),
            format=_get_format_datetime64_from_values(values,
                                                      self.date_format),
            na_rep=self.nat_rep).reshape(values.shape)
return fmt_values.tolist()

Here's the output of pd.show_versions() on my system.

INSTALLED VERSIONS
------------------
commit: None
python: 3.5.1.final.0
python-bits: 64
OS: Darwin
OS-release: 15.3.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_CA.UTF-8

pandas: 0.18.1
nose: None
pip: 8.1.2
setuptools: 23.0.0
Cython: 0.24
numpy: 1.11.0
scipy: 0.17.1
statsmodels: 0.6.1
xarray: None
IPython: 4.2.0
sphinx: None
patsy: 0.4.1
dateutil: 2.5.3
pytz: 2016.4
blosc: None
bottleneck: None
tables: None
numexpr: 2.6.0
matplotlib: 1.5.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.13
pymysql: None
psycopg2: 2.6.1 (dt dec pq3 ext lo64)
jinja2: 2.8
boto: None
pandas_datareader: None