Smarter formatting of timedelta and datetime columns
State: closed

Closes #3401 
Closes #5708
Closes #5912


Labels:

Comments:
@jreback What do you think shouold be done about the `__unicode__` method (https://github.com/pydata/pandas/blob/master/pandas/tseries/index.py#L529) ? Should that also omit the time string if the time is not set? Should it check each element in the array to determine when to do this? Or only the first and last?

@cancan101 for DataTimeIndex, I think you should leave it unless:

you determine for the entire series if their are time elements or not (using the quick test and it doesn't affect perf) - this should actually become a method of the DateTimeIndex (as a cached property)

then you could change unicode

@jreback I can see what can be done about unicode. Either way, cleaning up that method seems less important. 

yep....no biggie either way

Should just be a matter of using code similar to https://github.com/pydata/pandas/pull/5701/files#diff-23878beaf55672cdc92c119f79fe492aR1797 and making sure to cache the call to `_is_dates_only`

yep....just try not to repeat the code

pls rebase and squash a bit

also pls add a release notes (and in this case a whatsnew entry - in v0.13.1.txt, giving a short demo)

@jreback Done, let me know what you think.

@jreback Anything else to get this merged?

@jorisvandenbossche @jtratner @y-p any comments?

good for me

pls rebase...can you run the vbenchs just to confirm nothing reallly changes with this? (don't think it will but just to check)

These stick out over a few runs of vbench:

```
frame_to_csv_mixed                           | 219.4360 | 208.9397 |   1.0502 |
packers_write_csv                            | 779.2506 | 442.6716 |   1.7603 |
```

Assuming you are benching this against master; the `frame_to_csv_mixed` has not changed much from master (e.g. the 1.0502 ratio), so it does NOT stick out and doesn't matter. The fact that it takes 200ms is irrelevant.

However, the other test `packers_write_csv` DOES look like a problem; its writing a datetime index. That is not a good increase. Pls investigate what is taking the extra time, e.g. use `%prun` and see if it can be remedied.

What is the easiest way to run one of the vbench Benchmarks individually in  IPython so that I can benchmark?

just copy the code and run it
it's 2 lines

This function is expensive:

```
def _format_datetime64(x, tz=None, nat_rep='NaT'):
    if isnull(x):
        return nat_rep

    stamp = lib.Timestamp(x, tz=tz)
    if stamp.tzinfo is not None:
        return str(stamp)
    return stamp._repr_base
```

Changing the isnull to checknull gives a nice speed boost.

I think u need to do something like is in tseries/index/format_native_types

where it's not a function but rather the entire array is set at once 

this should be a common function anyhow (same for timedelta as well)

The newest commit brings the time to:

```
packers_write_csv                            | 591.3297 | 434.2360 |   1.3618 |
```

I remember from working with @jreback  on optimizing to_csv that stringfying numerics
accounted for a large chunk of the runtime. If there were faster routines available that could
make a large difference re #3186

Okay. I think I have tracked the performance regression to the following:

```
In [77]: ts = pd.Timestamp("2013-1-1")

In [78]: %timeit str(ts)
1000000 loops, best of 3: 1.06 µs per loop

In [79]: %timeit ts._repr_base
100000 loops, best of 3: 5.2 µs per loop
```

even tho:

```
In [80]: str(ts)
Out[80]: '2013-01-01 00:00:00'

In [81]: ts._repr_base
Out[81]: '2013-01-01 00:00:00'

In [83]: str(pd.Timestamp("2013-1-1 12:00:00"))
Out[83]: '2013-01-01 12:00:00'

In [84]: pd.Timestamp("2013-1-1 12:00:00")._repr_base
Out[84]: '2013-01-01 12:00:00'
```

@cancan01 you need to change these 2 a vectorized method like I indicated above (and what to_csv now does in to_native_types)

it's not the stringifying per se but the fact that u r checking nan every time in a python loop
u can check it once in a vectorized way and it will be much faster

@jreback (still spelling my name wrong...)
It actually is not the nan check. I tried removing it completely and it does not show a large reduction in time. The evaluation of the `_repr_base` is quite expensive. 

Also I uncovered #5912 in my tinkering.

For example If I strip it down to:

```
def _format_datetime64(x, tz=None, nat_rep='NaT'):
#     if x is None or lib.checknull(x):
#         return nat_rep
# 
#     if tz is not None or not isinstance(x, lib.Timestamp):
#         x = lib.Timestamp(x, tz=tz)

    if x.tzinfo is not None:
        return str(x)
    else:
        return x._repr_base
```

the time per 50,000 calls goes from 0.310s to 0.280s

Whereas this:

```
def _format_datetime64(x, tz=None, nat_rep='NaT'):
    if x is None or lib.checknull(x):
        return nat_rep

    if tz is not None or not isinstance(x, lib.Timestamp):
        x = lib.Timestamp(x, tz=tz)

    if x.tzinfo is not None:
        return str(x)
    else:
        return str(x)
#         return x._repr_base
```

takes 0.119s

@jreback Because of #5912, the current code is actually broken:

```
        # tz formatter or time formatter
         zero_time = time(0, 0)
         if date_format is None:
             for d in data:
                 if d.time() != zero_time or d.tzinfo is not None:
                     return [u('%s') % x for x in data]
```

leads to:

```
In [48]: [unicode('%s') % x for x in [pd.Timestamp(200)]]
Out[48]: [u'1970-01-01 00:00:00']
```

Even for formatting jsut the date, using the native format appears superior:

```
In [49]: ts = pd.Timestamp("2013-1-1")

In [60]: %timeit '%d-%.2d-%.2d' % (ts.year, ts.month, ts.day)
100000 loops, best of 3: 2.3 µs per loop

In [56]: %timeit ts.strftime("%Y-%m-%d")
100000 loops, best of 3: 2.08 µs per loop
```

Performance looks good with the newest change (all results > 3%):

```
groupby_multi_size                           |  23.0029 |  22.2677 |   1.0330 |
datetime_index_intersection                  |   0.2843 |   0.2750 |   1.0338 |
groupby_last                                 |   3.9919 |   3.8571 |   1.0350 |
frame_mult_no_ne                             |   5.3106 |   5.1310 |   1.0350 |
frame_multi_and_no_ne                        |  85.9957 |  82.9343 |   1.0369 |
frame_iteritems_cached                       |   0.4876 |   0.4700 |   1.0375 |
packers_write_pack                           |   3.4560 |   3.3247 |   1.0395 |
timeseries_to_datetime_YYYYMMDD              |   7.8693 |   7.5637 |   1.0404 |
dti_reset_index_tz                           |   9.1580 |   8.7987 |   1.0408 |
frame_assign_timeseries_index                |   0.6383 |   0.6119 |   1.0431 |
frame_ctor_list_of_dict                      |  67.9533 |  65.0856 |   1.0441 |
frame_get_numeric_data                       |   0.1024 |   0.0980 |   1.0446 |
plot_timeseries_period                       |  67.0340 |  64.1300 |   1.0453 |
eval_frame_mult_one_thread                   |  11.6833 |  11.1721 |   1.0458 |
series_align_left_monotonic                  |  11.8790 |  11.3533 |   1.0463 |
replace_fillna                               |   1.6336 |   1.5583 |   1.0483 |
reindex_fillna_backfill                      |   0.4443 |   0.4230 |   1.0505 |
packers_read_pickle                          |   0.6233 |   0.5923 |   1.0523 |
frame_drop_duplicates_na                     |  14.5866 |  13.8346 |   1.0544 |
frame_constructor_ndarray                    |   0.0503 |   0.0477 |   1.0550 |
stat_ops_frame_mean_int_axis_1               |   0.7436 |   0.7047 |   1.0553 |
indexing_dataframe_boolean_rows              |   0.3553 |   0.3363 |   1.0565 |
replace_replacena                            |   1.1587 |   1.0910 |   1.0621 |
frame_multi_and_st                           |  37.9917 |  35.7176 |   1.0637 |
frame_ctor_nested_dict_int64                 |  83.4666 |  78.0357 |   1.0696 |
frame_html_repr_trunc_si                     |  31.3597 |  29.2840 |   1.0709 |
timestamp_ops_diff2                          |  18.5770 |  17.2790 |   1.0751 |
series_timestamp_compare                     |   2.2450 |   2.0747 |   1.0821 |
series_constructor_ndarray                   |   0.0160 |   0.0146 |   1.0924 |
frame_to_csv_mixed                           | 226.2426 | 206.5704 |   1.0952 |
lib_fast_zip                                 |   8.4523 |   7.7160 |   1.0954 |
frame_mult_st                                |   5.8163 |   5.2950 |   1.0985 |
series_ctor_from_dict                        |   2.4934 |   2.2697 |   1.0986 |
stat_ops_frame_mean_int_axis_0               |   0.6413 |   0.5833 |   1.0995 |
frame_from_series                            |   0.1020 |   0.0927 |   1.1003 |
frame_mult                                   |   6.0443 |   5.4270 |   1.1138 |
reindex_fillna_pad                           |   0.4726 |   0.4203 |   1.1244 |
frame_add                                    |   6.1897 |   5.4307 |   1.1398 |
frame_boolean_row_select                     |   0.3163 |   0.2677 |   1.1817 |
frame_dropna_axis0_all                       |  66.0930 |  55.6227 |   1.1882 |
```