

In [1]:

    
%load_ext cython

Case Study: Slow Pandas dates

Batches of data are collected from field instruments. These instruments capture the date in three separate columns: day, month and year.

Data is processed in Pandas, but currently it is slow to convert the three columns into datetimes.

Example (randomised) data



In [2]:

    
import numpy as np
import pandas as pd

def make_sample_data(size):
    d = dict(
        # Years: 1980 - 2015
        year=np.random.randint(1980, 2016, int(size)),
        # Months 1 - 12
        month=np.random.randint(1, 13, int(size)),
        # Day number: 1 - 28
        day=np.random.randint(1, 28, int(size)),
        )
    return pd.DataFrame(d)

Start with a small dataset



In [3]:

    
df = make_sample_data(5)
df

Goal: make single `datetime` column

Let's see the Python code first:



In [4]:

    
import datetime

def create_datetime_py(year, month, day):
    """ Take year, month, day and return a datetime """
    return datetime.datetime(year, month, day, 0, 0, 0, 0, None)

Use the Python conversion function

Pandas has an apply() method that runs your function on a bunch of columns.

You must provide a function that receives a row, and your function must return a value. All the output values get put into a new Pandas series.



In [5]:

    
# Refer to fields by name! Very cool 👍
df.apply(lambda x : create_datetime_py(
        x['year'], x['month'], x['day']), axis=1)









    Out[5]:





0   1981-12-20
1   2007-01-16
2   1984-07-10
3   2015-03-07
4   2015-07-08
dtype: datetime64[ns]

Note: the type is "datetime64[ns]".

Awkward to type that all out each time. Let's make a convenient function.



In [6]:

    
def make_datetime_py(df):
    return df.apply(lambda x : create_datetime_py(
        x['year'], x['month'], x['day']), axis=1)

Then we can just call it like so:



In [7]:

    
df['new'] = make_datetime_py(df)
df

Problem: this is slow

With lots of data, the conversion to a datetime column takes a very long time! Let's try a bunch of data:



In [8]:

    
df_big = make_sample_data(100000)

%timeit make_datetime_py(df_big)









    



1 loop, best of 3: 2.82 s per loop

What to do?

The first thing is to check whether there is a low-level PXD interface file for the Python datetime object.

Let's use Cython!



In [9]:

    
%%cython -a
# cython: boundscheck = False
# cython: wraparound = False
from cpython.datetime cimport (
    import_datetime, datetime_new, datetime, timedelta)
from pandas import Timestamp

import_datetime()

cpdef convert_arrays_ts(
        long[:] year, long[:] month, long[:] day, 
        long long[:] out):
    """ Result goes into `out`  """
    cdef int i, n = year.shape[0]
    cdef datetime dt
    for i in range(n):
        dt = <datetime>datetime_new(
                year[i], month[i], day[i], 0, 0, 0, 0, None)
        out[i] = Timestamp(dt).value









    Out[9]:









    
    Cython: _cython_magic_715d5a747987f306c0c45b9ee3cef841.pyx
    
    


Generated by Cython 0.25.2

    Yellow lines hint at Python interaction.

    Click on a line that starts with a "+" to see the C code that Cython generated for it.

+01: # cython: boundscheck = False
  __pyx_t_2 = PyDict_New(); if (unlikely(!__pyx_t_2)) __PYX_ERR(0, 1, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_2);
  if (PyDict_SetItem(__pyx_d, __pyx_n_s_test, __pyx_t_2) < 0) __PYX_ERR(0, 1, __pyx_L1_error)
  __Pyx_DECREF(__pyx_t_2); __pyx_t_2 = 0;
 02: # cython: wraparound = False
 03: from cpython.datetime cimport (
 04:     import_datetime, datetime_new, datetime, timedelta)
+05: from pandas import Timestamp
  __pyx_t_1 = PyList_New(1); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 5, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  __Pyx_INCREF(__pyx_n_s_Timestamp);
  __Pyx_GIVEREF(__pyx_n_s_Timestamp);
  PyList_SET_ITEM(__pyx_t_1, 0, __pyx_n_s_Timestamp);
  __pyx_t_2 = __Pyx_Import(__pyx_n_s_pandas, __pyx_t_1, 0); if (unlikely(!__pyx_t_2)) __PYX_ERR(0, 5, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_2);
  __Pyx_DECREF(__pyx_t_1); __pyx_t_1 = 0;
  __pyx_t_1 = __Pyx_ImportFrom(__pyx_t_2, __pyx_n_s_Timestamp); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 5, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  if (PyDict_SetItem(__pyx_d, __pyx_n_s_Timestamp, __pyx_t_1) < 0) __PYX_ERR(0, 5, __pyx_L1_error)
  __Pyx_DECREF(__pyx_t_1); __pyx_t_1 = 0;
  __Pyx_DECREF(__pyx_t_2); __pyx_t_2 = 0;
 06: 
+07: import_datetime()
  __pyx_f_7cpython_8datetime_import_datetime();
 08: 
+09: cpdef convert_arrays_ts(
static PyObject *__pyx_pw_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_1convert_arrays_ts(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds); /*proto*/
static PyObject *__pyx_f_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_convert_arrays_ts(__Pyx_memviewslice __pyx_v_year, __Pyx_memviewslice __pyx_v_month, __Pyx_memviewslice __pyx_v_day, __Pyx_memviewslice __pyx_v_out, CYTHON_UNUSED int __pyx_skip_dispatch) {
  int __pyx_v_i;
  int __pyx_v_n;
  PyDateTime_DateTime *__pyx_v_dt = 0;
  PyObject *__pyx_r = NULL;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_ts", 0);
/* … */
  /* function exit code */
  __pyx_r = Py_None; __Pyx_INCREF(Py_None);
  goto __pyx_L0;
  __pyx_L1_error:;
  __Pyx_XDECREF(__pyx_t_6);
  __Pyx_XDECREF(__pyx_t_7);
  __Pyx_XDECREF(__pyx_t_8);
  __Pyx_XDECREF(__pyx_t_9);
  __Pyx_AddTraceback("_cython_magic_715d5a747987f306c0c45b9ee3cef841.convert_arrays_ts", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __pyx_r = 0;
  __pyx_L0:;
  __Pyx_XDECREF((PyObject *)__pyx_v_dt);
  __Pyx_XGIVEREF(__pyx_r);
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}

/* Python wrapper */
static PyObject *__pyx_pw_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_1convert_arrays_ts(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds); /*proto*/
static char __pyx_doc_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_convert_arrays_ts[] = " Result goes into `out`  ";
static PyObject *__pyx_pw_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_1convert_arrays_ts(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds) {
  __Pyx_memviewslice __pyx_v_year = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_month = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_day = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_out = { 0, 0, { 0 }, { 0 }, { 0 } };
  PyObject *__pyx_r = 0;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_ts (wrapper)", 0);
  {
    static PyObject **__pyx_pyargnames[] = {&__pyx_n_s_year,&__pyx_n_s_month,&__pyx_n_s_day,&__pyx_n_s_out,0};
    PyObject* values[4] = {0,0,0,0};
    if (unlikely(__pyx_kwds)) {
      Py_ssize_t kw_args;
      const Py_ssize_t pos_args = PyTuple_GET_SIZE(__pyx_args);
      switch (pos_args) {
        case  4: values[3] = PyTuple_GET_ITEM(__pyx_args, 3);
        case  3: values[2] = PyTuple_GET_ITEM(__pyx_args, 2);
        case  2: values[1] = PyTuple_GET_ITEM(__pyx_args, 1);
        case  1: values[0] = PyTuple_GET_ITEM(__pyx_args, 0);
        case  0: break;
        default: goto __pyx_L5_argtuple_error;
      }
      kw_args = PyDict_Size(__pyx_kwds);
      switch (pos_args) {
        case  0:
        if (likely((values[0] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_year)) != 0)) kw_args--;
        else goto __pyx_L5_argtuple_error;
        case  1:
        if (likely((values[1] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_month)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_ts", 1, 4, 4, 1); __PYX_ERR(0, 9, __pyx_L3_error)
        }
        case  2:
        if (likely((values[2] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_day)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_ts", 1, 4, 4, 2); __PYX_ERR(0, 9, __pyx_L3_error)
        }
        case  3:
        if (likely((values[3] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_out)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_ts", 1, 4, 4, 3); __PYX_ERR(0, 9, __pyx_L3_error)
        }
      }
      if (unlikely(kw_args > 0)) {
        if (unlikely(__Pyx_ParseOptionalKeywords(__pyx_kwds, __pyx_pyargnames, 0, values, pos_args, "convert_arrays_ts") < 0)) __PYX_ERR(0, 9, __pyx_L3_error)
      }
    } else if (PyTuple_GET_SIZE(__pyx_args) != 4) {
      goto __pyx_L5_argtuple_error;
    } else {
      values[0] = PyTuple_GET_ITEM(__pyx_args, 0);
      values[1] = PyTuple_GET_ITEM(__pyx_args, 1);
      values[2] = PyTuple_GET_ITEM(__pyx_args, 2);
      values[3] = PyTuple_GET_ITEM(__pyx_args, 3);
    }
    __pyx_v_year = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[0]); if (unlikely(!__pyx_v_year.memview)) __PYX_ERR(0, 10, __pyx_L3_error)
    __pyx_v_month = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[1]); if (unlikely(!__pyx_v_month.memview)) __PYX_ERR(0, 10, __pyx_L3_error)
    __pyx_v_day = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[2]); if (unlikely(!__pyx_v_day.memview)) __PYX_ERR(0, 10, __pyx_L3_error)
    __pyx_v_out = __Pyx_PyObject_to_MemoryviewSlice_ds_PY_LONG_LONG(values[3]); if (unlikely(!__pyx_v_out.memview)) __PYX_ERR(0, 11, __pyx_L3_error)
  }
  goto __pyx_L4_argument_unpacking_done;
  __pyx_L5_argtuple_error:;
  __Pyx_RaiseArgtupleInvalid("convert_arrays_ts", 1, 4, 4, PyTuple_GET_SIZE(__pyx_args)); __PYX_ERR(0, 9, __pyx_L3_error)
  __pyx_L3_error:;
  __Pyx_AddTraceback("_cython_magic_715d5a747987f306c0c45b9ee3cef841.convert_arrays_ts", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __Pyx_RefNannyFinishContext();
  return NULL;
  __pyx_L4_argument_unpacking_done:;
  __pyx_r = __pyx_pf_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_convert_arrays_ts(__pyx_self, __pyx_v_year, __pyx_v_month, __pyx_v_day, __pyx_v_out);

  /* function exit code */
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}

static PyObject *__pyx_pf_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_convert_arrays_ts(CYTHON_UNUSED PyObject *__pyx_self, __Pyx_memviewslice __pyx_v_year, __Pyx_memviewslice __pyx_v_month, __Pyx_memviewslice __pyx_v_day, __Pyx_memviewslice __pyx_v_out) {
  PyObject *__pyx_r = NULL;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_ts", 0);
  __Pyx_XDECREF(__pyx_r);
  if (unlikely(!__pyx_v_year.memview)) { __Pyx_RaiseUnboundLocalError("year"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_month.memview)) { __Pyx_RaiseUnboundLocalError("month"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_day.memview)) { __Pyx_RaiseUnboundLocalError("day"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_out.memview)) { __Pyx_RaiseUnboundLocalError("out"); __PYX_ERR(0, 9, __pyx_L1_error) }
  __pyx_t_1 = __pyx_f_46_cython_magic_715d5a747987f306c0c45b9ee3cef841_convert_arrays_ts(__pyx_v_year, __pyx_v_month, __pyx_v_day, __pyx_v_out, 0); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 9, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  __pyx_r = __pyx_t_1;
  __pyx_t_1 = 0;
  goto __pyx_L0;

  /* function exit code */
  __pyx_L1_error:;
  __Pyx_XDECREF(__pyx_t_1);
  __Pyx_AddTraceback("_cython_magic_715d5a747987f306c0c45b9ee3cef841.convert_arrays_ts", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __pyx_r = NULL;
  __pyx_L0:;
  __PYX_XDEC_MEMVIEW(&__pyx_v_year, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_month, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_day, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_out, 1);
  __Pyx_XGIVEREF(__pyx_r);
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}
 10:         long[:] year, long[:] month, long[:] day,
 11:         long long[:] out):
 12:     """ Result goes into `out`  """
+13:     cdef int i, n = year.shape[0]
  __pyx_v_n = (__pyx_v_year.shape[0]);
 14:     cdef datetime dt
+15:     for i in range(n):
  __pyx_t_1 = __pyx_v_n;
  for (__pyx_t_2 = 0; __pyx_t_2 < __pyx_t_1; __pyx_t_2+=1) {
    __pyx_v_i = __pyx_t_2;
+16:         dt = <datetime>datetime_new(
    __pyx_t_6 = __pyx_f_7cpython_8datetime_datetime_new((*((long *) ( /* dim=0 */ (__pyx_v_year.data + __pyx_t_3 * __pyx_v_year.strides[0]) ))), (*((long *) ( /* dim=0 */ (__pyx_v_month.data + __pyx_t_4 * __pyx_v_month.strides[0]) ))), (*((long *) ( /* dim=0 */ (__pyx_v_day.data + __pyx_t_5 * __pyx_v_day.strides[0]) ))), 0, 0, 0, 0, Py_None); if (unlikely(!__pyx_t_6)) __PYX_ERR(0, 16, __pyx_L1_error)
    __Pyx_GOTREF(__pyx_t_6);
    __pyx_t_7 = __pyx_t_6;
    __Pyx_INCREF(__pyx_t_7);
    __Pyx_DECREF(__pyx_t_6); __pyx_t_6 = 0;
    __Pyx_XDECREF_SET(__pyx_v_dt, ((PyDateTime_DateTime *)__pyx_t_7));
    __pyx_t_7 = 0;
+17:                 year[i], month[i], day[i], 0, 0, 0, 0, None)
    __pyx_t_3 = __pyx_v_i;
    __pyx_t_4 = __pyx_v_i;
    __pyx_t_5 = __pyx_v_i;
+18:         out[i] = Timestamp(dt).value
    __pyx_t_6 = __Pyx_GetModuleGlobalName(__pyx_n_s_Timestamp); if (unlikely(!__pyx_t_6)) __PYX_ERR(0, 18, __pyx_L1_error)
    __Pyx_GOTREF(__pyx_t_6);
    __pyx_t_8 = NULL;
    if (CYTHON_UNPACK_METHODS && unlikely(PyMethod_Check(__pyx_t_6))) {
      __pyx_t_8 = PyMethod_GET_SELF(__pyx_t_6);
      if (likely(__pyx_t_8)) {
        PyObject* function = PyMethod_GET_FUNCTION(__pyx_t_6);
        __Pyx_INCREF(__pyx_t_8);
        __Pyx_INCREF(function);
        __Pyx_DECREF_SET(__pyx_t_6, function);
      }
    }
    if (!__pyx_t_8) {
      __pyx_t_7 = __Pyx_PyObject_CallOneArg(__pyx_t_6, ((PyObject *)__pyx_v_dt)); if (unlikely(!__pyx_t_7)) __PYX_ERR(0, 18, __pyx_L1_error)
      __Pyx_GOTREF(__pyx_t_7);
    } else {
      #if CYTHON_FAST_PYCALL
      if (PyFunction_Check(__pyx_t_6)) {
        PyObject *__pyx_temp[2] = {__pyx_t_8, ((PyObject *)__pyx_v_dt)};
        __pyx_t_7 = __Pyx_PyFunction_FastCall(__pyx_t_6, __pyx_temp+1-1, 1+1); if (unlikely(!__pyx_t_7)) __PYX_ERR(0, 18, __pyx_L1_error)
        __Pyx_XDECREF(__pyx_t_8); __pyx_t_8 = 0;
        __Pyx_GOTREF(__pyx_t_7);
      } else
      #endif
      #if CYTHON_FAST_PYCCALL
      if (__Pyx_PyFastCFunction_Check(__pyx_t_6)) {
        PyObject *__pyx_temp[2] = {__pyx_t_8, ((PyObject *)__pyx_v_dt)};
        __pyx_t_7 = __Pyx_PyCFunction_FastCall(__pyx_t_6, __pyx_temp+1-1, 1+1); if (unlikely(!__pyx_t_7)) __PYX_ERR(0, 18, __pyx_L1_error)
        __Pyx_XDECREF(__pyx_t_8); __pyx_t_8 = 0;
        __Pyx_GOTREF(__pyx_t_7);
      } else
      #endif
      {
        __pyx_t_9 = PyTuple_New(1+1); if (unlikely(!__pyx_t_9)) __PYX_ERR(0, 18, __pyx_L1_error)
        __Pyx_GOTREF(__pyx_t_9);
        __Pyx_GIVEREF(__pyx_t_8); PyTuple_SET_ITEM(__pyx_t_9, 0, __pyx_t_8); __pyx_t_8 = NULL;
        __Pyx_INCREF(((PyObject *)__pyx_v_dt));
        __Pyx_GIVEREF(((PyObject *)__pyx_v_dt));
        PyTuple_SET_ITEM(__pyx_t_9, 0+1, ((PyObject *)__pyx_v_dt));
        __pyx_t_7 = __Pyx_PyObject_Call(__pyx_t_6, __pyx_t_9, NULL); if (unlikely(!__pyx_t_7)) __PYX_ERR(0, 18, __pyx_L1_error)
        __Pyx_GOTREF(__pyx_t_7);
        __Pyx_DECREF(__pyx_t_9); __pyx_t_9 = 0;
      }
    }
    __Pyx_DECREF(__pyx_t_6); __pyx_t_6 = 0;
    __pyx_t_6 = __Pyx_PyObject_GetAttrStr(__pyx_t_7, __pyx_n_s_value); if (unlikely(!__pyx_t_6)) __PYX_ERR(0, 18, __pyx_L1_error)
    __Pyx_GOTREF(__pyx_t_6);
    __Pyx_DECREF(__pyx_t_7); __pyx_t_7 = 0;
    __pyx_t_10 = __Pyx_PyInt_As_PY_LONG_LONG(__pyx_t_6); if (unlikely((__pyx_t_10 == (PY_LONG_LONG)-1) && PyErr_Occurred())) __PYX_ERR(0, 18, __pyx_L1_error)
    __Pyx_DECREF(__pyx_t_6); __pyx_t_6 = 0;
    __pyx_t_11 = __pyx_v_i;
    *((PY_LONG_LONG *) ( /* dim=0 */ (__pyx_v_out.data + __pyx_t_11 * __pyx_v_out.strides[0]) )) = __pyx_t_10;
  }

Utility function for applying our conversion



In [10]:

    
def make_datetime_cy(df, method):
    s = pd.Series(np.zeros(len(df), dtype='datetime64[ns]'))
    method(df['year'].values, df['month'].values, df['day'].values,
               s.values.view('int64')) 
    return s



In [11]:

    
# Test it out
make_datetime_cy(df, convert_arrays_ts)









    Out[11]:





0   1981-12-20
1   2007-01-16
2   1984-07-10
3   2015-03-07
4   2015-07-08
dtype: datetime64[ns]

Speed Test



In [12]:

    
df_big = make_sample_data(100000)

%timeit make_datetime_py(df_big)
%timeit make_datetime_cy(df_big, convert_arrays_ts)









    



1 loop, best of 3: 2.85 s per loop
10 loops, best of 3: 79.8 ms per loop

Check annotation

Eliminate the Python overhead



In [13]:

    
%%cython -a
# cython: boundscheck = False
# cython: wraparound = False
from cpython.datetime cimport (
    import_datetime, datetime_new, datetime, timedelta,
    timedelta_seconds, timedelta_days)

import_datetime()  # <-- Pretty important

cpdef convert_arrays_dt(long[:] year, long[:] month, long[:] day, 
        long long[:] out):
    """ Result goes into `out`  """
    cdef int i, n = year.shape[0]
    cdef datetime dt, epoch = datetime_new(1970, 1, 1, 0, 0, 0, 0, None)
    cdef timedelta td
    cdef long seconds
    for i in range(n):
        dt = <datetime>datetime_new(
                year[i], month[i], day[i], 0, 0, 0, 0, None)
        td = <timedelta>(dt - epoch)
        seconds = timedelta_days(td) * 86400 # Seconds per day
        out[i] = seconds * 1000000000  # Nanoseconds, remember?









    Out[13]:









    
    Cython: _cython_magic_27ec1a37df64cdaca0906d0c247336a4.pyx
    
    


Generated by Cython 0.25.2

    Yellow lines hint at Python interaction.

    Click on a line that starts with a "+" to see the C code that Cython generated for it.

+01: # cython: boundscheck = False
  __pyx_t_1 = PyDict_New(); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 1, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  if (PyDict_SetItem(__pyx_d, __pyx_n_s_test, __pyx_t_1) < 0) __PYX_ERR(0, 1, __pyx_L1_error)
  __Pyx_DECREF(__pyx_t_1); __pyx_t_1 = 0;
 02: # cython: wraparound = False
 03: from cpython.datetime cimport (
 04:     import_datetime, datetime_new, datetime, timedelta,
 05:     timedelta_seconds, timedelta_days)
 06: 
+07: import_datetime()  # <-- Pretty important
  __pyx_f_7cpython_8datetime_import_datetime();
 08: 
+09: cpdef convert_arrays_dt(long[:] year, long[:] month, long[:] day,
static PyObject *__pyx_pw_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_1convert_arrays_dt(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds); /*proto*/
static PyObject *__pyx_f_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_convert_arrays_dt(__Pyx_memviewslice __pyx_v_year, __Pyx_memviewslice __pyx_v_month, __Pyx_memviewslice __pyx_v_day, __Pyx_memviewslice __pyx_v_out, CYTHON_UNUSED int __pyx_skip_dispatch) {
  int __pyx_v_i;
  int __pyx_v_n;
  PyDateTime_DateTime *__pyx_v_dt = 0;
  PyDateTime_DateTime *__pyx_v_epoch = 0;
  PyDateTime_Delta *__pyx_v_td = 0;
  long __pyx_v_seconds;
  PyObject *__pyx_r = NULL;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_dt", 0);
/* … */
  /* function exit code */
  __pyx_r = Py_None; __Pyx_INCREF(Py_None);
  goto __pyx_L0;
  __pyx_L1_error:;
  __Pyx_XDECREF(__pyx_t_1);
  __Pyx_XDECREF(__pyx_t_7);
  __Pyx_AddTraceback("_cython_magic_27ec1a37df64cdaca0906d0c247336a4.convert_arrays_dt", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __pyx_r = 0;
  __pyx_L0:;
  __Pyx_XDECREF((PyObject *)__pyx_v_dt);
  __Pyx_XDECREF((PyObject *)__pyx_v_epoch);
  __Pyx_XDECREF((PyObject *)__pyx_v_td);
  __Pyx_XGIVEREF(__pyx_r);
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}

/* Python wrapper */
static PyObject *__pyx_pw_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_1convert_arrays_dt(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds); /*proto*/
static char __pyx_doc_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_convert_arrays_dt[] = " Result goes into `out`  ";
static PyObject *__pyx_pw_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_1convert_arrays_dt(PyObject *__pyx_self, PyObject *__pyx_args, PyObject *__pyx_kwds) {
  __Pyx_memviewslice __pyx_v_year = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_month = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_day = { 0, 0, { 0 }, { 0 }, { 0 } };
  __Pyx_memviewslice __pyx_v_out = { 0, 0, { 0 }, { 0 }, { 0 } };
  PyObject *__pyx_r = 0;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_dt (wrapper)", 0);
  {
    static PyObject **__pyx_pyargnames[] = {&__pyx_n_s_year,&__pyx_n_s_month,&__pyx_n_s_day,&__pyx_n_s_out,0};
    PyObject* values[4] = {0,0,0,0};
    if (unlikely(__pyx_kwds)) {
      Py_ssize_t kw_args;
      const Py_ssize_t pos_args = PyTuple_GET_SIZE(__pyx_args);
      switch (pos_args) {
        case  4: values[3] = PyTuple_GET_ITEM(__pyx_args, 3);
        case  3: values[2] = PyTuple_GET_ITEM(__pyx_args, 2);
        case  2: values[1] = PyTuple_GET_ITEM(__pyx_args, 1);
        case  1: values[0] = PyTuple_GET_ITEM(__pyx_args, 0);
        case  0: break;
        default: goto __pyx_L5_argtuple_error;
      }
      kw_args = PyDict_Size(__pyx_kwds);
      switch (pos_args) {
        case  0:
        if (likely((values[0] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_year)) != 0)) kw_args--;
        else goto __pyx_L5_argtuple_error;
        case  1:
        if (likely((values[1] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_month)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_dt", 1, 4, 4, 1); __PYX_ERR(0, 9, __pyx_L3_error)
        }
        case  2:
        if (likely((values[2] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_day)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_dt", 1, 4, 4, 2); __PYX_ERR(0, 9, __pyx_L3_error)
        }
        case  3:
        if (likely((values[3] = PyDict_GetItem(__pyx_kwds, __pyx_n_s_out)) != 0)) kw_args--;
        else {
          __Pyx_RaiseArgtupleInvalid("convert_arrays_dt", 1, 4, 4, 3); __PYX_ERR(0, 9, __pyx_L3_error)
        }
      }
      if (unlikely(kw_args > 0)) {
        if (unlikely(__Pyx_ParseOptionalKeywords(__pyx_kwds, __pyx_pyargnames, 0, values, pos_args, "convert_arrays_dt") < 0)) __PYX_ERR(0, 9, __pyx_L3_error)
      }
    } else if (PyTuple_GET_SIZE(__pyx_args) != 4) {
      goto __pyx_L5_argtuple_error;
    } else {
      values[0] = PyTuple_GET_ITEM(__pyx_args, 0);
      values[1] = PyTuple_GET_ITEM(__pyx_args, 1);
      values[2] = PyTuple_GET_ITEM(__pyx_args, 2);
      values[3] = PyTuple_GET_ITEM(__pyx_args, 3);
    }
    __pyx_v_year = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[0]); if (unlikely(!__pyx_v_year.memview)) __PYX_ERR(0, 9, __pyx_L3_error)
    __pyx_v_month = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[1]); if (unlikely(!__pyx_v_month.memview)) __PYX_ERR(0, 9, __pyx_L3_error)
    __pyx_v_day = __Pyx_PyObject_to_MemoryviewSlice_ds_long(values[2]); if (unlikely(!__pyx_v_day.memview)) __PYX_ERR(0, 9, __pyx_L3_error)
    __pyx_v_out = __Pyx_PyObject_to_MemoryviewSlice_ds_PY_LONG_LONG(values[3]); if (unlikely(!__pyx_v_out.memview)) __PYX_ERR(0, 10, __pyx_L3_error)
  }
  goto __pyx_L4_argument_unpacking_done;
  __pyx_L5_argtuple_error:;
  __Pyx_RaiseArgtupleInvalid("convert_arrays_dt", 1, 4, 4, PyTuple_GET_SIZE(__pyx_args)); __PYX_ERR(0, 9, __pyx_L3_error)
  __pyx_L3_error:;
  __Pyx_AddTraceback("_cython_magic_27ec1a37df64cdaca0906d0c247336a4.convert_arrays_dt", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __Pyx_RefNannyFinishContext();
  return NULL;
  __pyx_L4_argument_unpacking_done:;
  __pyx_r = __pyx_pf_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_convert_arrays_dt(__pyx_self, __pyx_v_year, __pyx_v_month, __pyx_v_day, __pyx_v_out);

  /* function exit code */
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}

static PyObject *__pyx_pf_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_convert_arrays_dt(CYTHON_UNUSED PyObject *__pyx_self, __Pyx_memviewslice __pyx_v_year, __Pyx_memviewslice __pyx_v_month, __Pyx_memviewslice __pyx_v_day, __Pyx_memviewslice __pyx_v_out) {
  PyObject *__pyx_r = NULL;
  __Pyx_RefNannyDeclarations
  __Pyx_RefNannySetupContext("convert_arrays_dt", 0);
  __Pyx_XDECREF(__pyx_r);
  if (unlikely(!__pyx_v_year.memview)) { __Pyx_RaiseUnboundLocalError("year"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_month.memview)) { __Pyx_RaiseUnboundLocalError("month"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_day.memview)) { __Pyx_RaiseUnboundLocalError("day"); __PYX_ERR(0, 9, __pyx_L1_error) }
  if (unlikely(!__pyx_v_out.memview)) { __Pyx_RaiseUnboundLocalError("out"); __PYX_ERR(0, 9, __pyx_L1_error) }
  __pyx_t_1 = __pyx_f_46_cython_magic_27ec1a37df64cdaca0906d0c247336a4_convert_arrays_dt(__pyx_v_year, __pyx_v_month, __pyx_v_day, __pyx_v_out, 0); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 9, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  __pyx_r = __pyx_t_1;
  __pyx_t_1 = 0;
  goto __pyx_L0;

  /* function exit code */
  __pyx_L1_error:;
  __Pyx_XDECREF(__pyx_t_1);
  __Pyx_AddTraceback("_cython_magic_27ec1a37df64cdaca0906d0c247336a4.convert_arrays_dt", __pyx_clineno, __pyx_lineno, __pyx_filename);
  __pyx_r = NULL;
  __pyx_L0:;
  __PYX_XDEC_MEMVIEW(&__pyx_v_year, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_month, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_day, 1);
  __PYX_XDEC_MEMVIEW(&__pyx_v_out, 1);
  __Pyx_XGIVEREF(__pyx_r);
  __Pyx_RefNannyFinishContext();
  return __pyx_r;
}
 10:         long long[:] out):
 11:     """ Result goes into `out`  """
+12:     cdef int i, n = year.shape[0]
  __pyx_v_n = (__pyx_v_year.shape[0]);
+13:     cdef datetime dt, epoch = datetime_new(1970, 1, 1, 0, 0, 0, 0, None)
  __pyx_t_1 = __pyx_f_7cpython_8datetime_datetime_new(0x7B2, 1, 1, 0, 0, 0, 0, Py_None); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 13, __pyx_L1_error)
  __Pyx_GOTREF(__pyx_t_1);
  if (!(likely(((__pyx_t_1) == Py_None) || likely(__Pyx_TypeTest(__pyx_t_1, __pyx_ptype_7cpython_8datetime_datetime))))) __PYX_ERR(0, 13, __pyx_L1_error)
  __pyx_v_epoch = ((PyDateTime_DateTime *)__pyx_t_1);
  __pyx_t_1 = 0;
 14:     cdef timedelta td
 15:     cdef long seconds
+16:     for i in range(n):
  __pyx_t_2 = __pyx_v_n;
  for (__pyx_t_3 = 0; __pyx_t_3 < __pyx_t_2; __pyx_t_3+=1) {
    __pyx_v_i = __pyx_t_3;
+17:         dt = <datetime>datetime_new(
    __pyx_t_1 = __pyx_f_7cpython_8datetime_datetime_new((*((long *) ( /* dim=0 */ (__pyx_v_year.data + __pyx_t_4 * __pyx_v_year.strides[0]) ))), (*((long *) ( /* dim=0 */ (__pyx_v_month.data + __pyx_t_5 * __pyx_v_month.strides[0]) ))), (*((long *) ( /* dim=0 */ (__pyx_v_day.data + __pyx_t_6 * __pyx_v_day.strides[0]) ))), 0, 0, 0, 0, Py_None); if (unlikely(!__pyx_t_1)) __PYX_ERR(0, 17, __pyx_L1_error)
    __Pyx_GOTREF(__pyx_t_1);
    __pyx_t_7 = __pyx_t_1;
    __Pyx_INCREF(__pyx_t_7);
    __Pyx_DECREF(__pyx_t_1); __pyx_t_1 = 0;
    __Pyx_XDECREF_SET(__pyx_v_dt, ((PyDateTime_DateTime *)__pyx_t_7));
    __pyx_t_7 = 0;
+18:                 year[i], month[i], day[i], 0, 0, 0, 0, None)
    __pyx_t_4 = __pyx_v_i;
    __pyx_t_5 = __pyx_v_i;
    __pyx_t_6 = __pyx_v_i;
+19:         td = <timedelta>(dt - epoch)
    __pyx_t_7 = PyNumber_Subtract(((PyObject *)__pyx_v_dt), ((PyObject *)__pyx_v_epoch)); if (unlikely(!__pyx_t_7)) __PYX_ERR(0, 19, __pyx_L1_error)
    __Pyx_GOTREF(__pyx_t_7);
    __pyx_t_1 = __pyx_t_7;
    __Pyx_INCREF(__pyx_t_1);
    __Pyx_DECREF(__pyx_t_7); __pyx_t_7 = 0;
    __Pyx_XDECREF_SET(__pyx_v_td, ((PyDateTime_Delta *)__pyx_t_1));
    __pyx_t_1 = 0;
+20:         seconds = timedelta_days(td) * 86400 # Seconds per day
    __pyx_v_seconds = (__pyx_f_7cpython_8datetime_timedelta_days(((PyObject *)__pyx_v_td)) * 0x15180);
+21:         out[i] = seconds * 1000000000  # Nanoseconds, remember?
    __pyx_t_8 = __pyx_v_i;
    *((PY_LONG_LONG *) ( /* dim=0 */ (__pyx_v_out.data + __pyx_t_8 * __pyx_v_out.strides[0]) )) = (__pyx_v_seconds * 0x3B9ACA00);
  }

Test it out



In [14]:

    
make_datetime_cy(df, convert_arrays_dt)









    Out[14]:





0   1981-12-20
1   2007-01-16
2   1984-07-10
3   2015-03-07
4   2015-07-08
dtype: datetime64[ns]

Speed Test



In [15]:

    
df_big = make_sample_data(100000)

%timeit make_datetime_py(df_big)
%timeit make_datetime_cy(df_big, convert_arrays_ts)
%timeit make_datetime_cy(df_big, convert_arrays_dt)









    



1 loop, best of 3: 2.85 s per loop
10 loops, best of 3: 80.3 ms per loop
100 loops, best of 3: 6.34 ms per loop

Speed improvement:

~450 X !!!

real Python datetime and timedelta objects
still single-threaded



In [ ]:

	day	month	year	new
0	20	12	1981	1981-12-20
1	16	1	2007	2007-01-16
2	10	7	1984	1984-07-10
3	7	3	2015	2015-03-07
4	8	7	2015	2015-07-08

Case Study: Slow Pandas dates

Example (randomised) data

Start with a small dataset

Goal: make single datetime column

Use the Python conversion function

Problem: this is slow

What to do?

Let's use Cython!

Utility function for applying our conversion

Speed Test

Eliminate the Python overhead

Test it out

Speed Test

Speed improvement:

Goal: make single `datetime` column