ipyvega doesn't handle date values in pandas

[falconair]

Some database queries return python date objects in pandas frames call pd.read_sql. Pandas generally knows how to deal with this, for example, when displaying a table, it formats the date correctly.

However, if this frame is passed to ipyvega VegaLite(spec, df ) VegaLite throws an error complaining that it doesn't understand the datetime object. I think ipyvega should be able to understand the python datetime object.

[jakevdp]

Is this still an issue? Do you have example code that reproduces the error? Thanks!

[domoritz]

Please reopen if this is still an issue.

[jpmccu]

This is still an issue. Please reopen:

import datetime
import pandas as pd
from vega import VegaLite

test_data = pd.DataFrame([[datetime.date(year=2019,month=1,day=i),i] for i in range(1,10)], 
                         columns=['date','value'])
VegaLite({
  "$schema": "https://vega.github.io/schema/vega-lite/v3.json",
  "mark": "line",
  "encoding": {
    "x": {"field": "date", "type": "temporal"},
    "y": {"field": "value", "type": "quantitative"},
  }
}, test_data)

results in:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~/causal/venv/lib/python3.6/site-packages/IPython/core/formatters.py in __call__(self, obj, include, exclude)
    968 
    969             if method is not None:
--> 970                 return method(include=include, exclude=exclude)
    971             return None
    972         else:

~/causal/venv/lib/python3.6/site-packages/vega/base.py in _repr_mimebundle_(self, include, exclude)
     39         id = uuid.uuid4()
     40         return (
---> 41             {'application/javascript': self._generate_js(id)},
     42             {'jupyter-vega': '#{0}'.format(id)},
     43         )

~/causal/venv/lib/python3.6/site-packages/vega/base.py in _generate_js(self, id, **kwds)
     29         payload = template.format(
     30             id=id,
---> 31             spec=json.dumps(self.spec, **kwds),
     32             opt=json.dumps(self.opt, **kwds),
     33             type=self.render_type

/usr/lib/python3.6/json/__init__.py in dumps(obj, skipkeys, ensure_ascii, check_circular, allow_nan, cls, indent, separators, default, sort_keys, **kw)
    229         cls is None and indent is None and separators is None and
    230         default is None and not sort_keys and not kw):
--> 231         return _default_encoder.encode(obj)
    232     if cls is None:
    233         cls = JSONEncoder

/usr/lib/python3.6/json/encoder.py in encode(self, o)
    197         # exceptions aren't as detailed.  The list call should be roughly
    198         # equivalent to the PySequence_Fast that ''.join() would do.
--> 199         chunks = self.iterencode(o, _one_shot=True)
    200         if not isinstance(chunks, (list, tuple)):
    201             chunks = list(chunks)

/usr/lib/python3.6/json/encoder.py in iterencode(self, o, _one_shot)
    255                 self.key_separator, self.item_separator, self.sort_keys,
    256                 self.skipkeys, _one_shot)
--> 257         return _iterencode(o, 0)
    258 
    259 def _make_iterencode(markers, _default, _encoder, _indent, _floatstr,

/usr/lib/python3.6/json/encoder.py in default(self, o)
    178         """
    179         raise TypeError("Object of type '%s' is not JSON serializable" %
--> 180                         o.__class__.__name__)
    181 
    182     def encode(self, o):

TypeError: Object of type 'date' is not JSON serializable

[domoritz]

Thanks for the example. Can you send a PR to fix this?

[jpmccu]

Not to argue, but this seems like a bug, not an enhancement, since date(), datetime() and time() objects are pretty common in pandas frames.

I wouldn't know the best way to render dates for consumption by Vega, so I'm not going to try at a PR for this. You probably want to use to_json(orient="index") on the frame, but that returns integer values that javascript is going to need to parse.

test_data.to_json(orient="index")

produces:

{
  "0":{"date":1546300800000,"value":1},
  "1":{"date":1546387200000,"value":2},
  "2":{"date":1546473600000,"value":3},
  "3":{"date":1546560000000,"value":4},
  "4":{"date":1546646400000,"value":5},
  "5":{"date":1546732800000,"value":6},
  "6":{"date":1546819200000,"value":7},
  "7":{"date":1546905600000,"value":8},
  "8":{"date":1546992000000,"value":9}
}

[domoritz]

I think the title of this issue is wrong. We already support datetime, but not date. See

ipyvega/vega/utils.py

Line 69 in eab0c42

elif str(dtype).startswith('datetime'):

[domoritz]

Vega can read dates as standard strings (as supported by browsers) and timestamps.