Possible Bug when using diff on datetime objects in a pandas groupby object with multiindex
Question: Consider a dataframe with a column with labels that are used to create groups
and two rows of the same dates:

    
    
    import datetime as dt
    import pandas as pd
    
    dd = [['A','A','A','A','B','B']\
         ,[dt.date(1981,3,6),dt.date(1986,5,1),dt.date(1983,11,8)\
         ,dt.date(1982,6,11),dt.date(1977,2,26),dt.date(1991,9,4)]]
    dd = map(list,zip(*dd))
    DF = pd.DataFrame(dd,columns=['Label','Date'])
    DF['Date2'] = DF['Date'].copy()
    
    print DF
    print type(DF.Date[0])
    print type(DF.Date2[0])
    

This yields:

    
    
      Label        Date       Date2
    0     A  1981-03-06  1981-03-06
    1     A  1986-05-01  1986-05-01
    2     A  1983-11-08  1983-11-08
    3     A  1982-06-11  1982-06-11
    4     B  1977-02-26  1977-02-26
    5     B  1991-09-04  1991-09-04
    <type 'datetime.date'>
    <type 'datetime.date'>
    

Now I can do these:

    
    
    print DF.groupby(['Label']).diff()
    print "======================================"
    print DF.groupby(['Label']).apply(lambda s: s[u'Date'].diff())
    print "======================================"
    print DF.groupby(['Label']).apply(lambda s: s[u'Date2'].diff())
    

Leading to this output:

    
    
                     Date               Date2
    0                 NaN                 NaN
    1  1882 days, 0:00:00  1882 days, 0:00:00
    2  -905 days, 0:00:00  -905 days, 0:00:00
    3  -515 days, 0:00:00  -515 days, 0:00:00
    4                 NaN                 NaN
    5  5303 days, 0:00:00  5303 days, 0:00:00
    ======================================
    Label   
    A      0         NaT
           1   1882 days
           2   -905 days
           3   -515 days
    B      4         NaT
           5   5303 days
    Name: Date, dtype: timedelta64[ns]
    ======================================
    Label   
    A      0         NaT
           1   1882 days
           2   -905 days
           3   -515 days
    B      4         NaT
           5   5303 days
    Name: Date2, dtype: timedelta64[ns]
    

However when I'm doing these:

    
    
    print DF.groupby(['Label','Date']).diff()
    print "======================================"
    print DF.groupby(['Label','Date']).apply(lambda s: s[u'Date2'].diff())
    print "======================================"
    print DF.groupby(['Label','Date'])[u'Date2'].transform(pd.Series.diff)
    

Then the output is broken:

    
    
      Date2
    0   NaN
    1   NaN
    2   NaN
    3   NaN
    4   NaN
    5   NaN
    ======================================
    Label  Date         
    A      1981-03-06  0    NaN
           1982-06-11  3    NaN
           1983-11-08  2    NaN
           1986-05-01  1    NaN
    B      1977-02-26  4    NaN
           1991-09-04  5    NaN
    Name: Date2, dtype: object
    ======================================
    0    NaN
    1    NaN
    2    NaN
    3    NaN
    4    NaN
    5    NaN
    Name: Date2, dtype: object
    

As you can see for some reason the Date2 column is no longer a timedelta64
dtype but just an object type. This happens with every method I have tried and
also when switching the two date columns, so this must be something to do with
the multiindex groupby.

I can't tell if this is expected or unexpected behaviour, that is if it's a
bug or not.

EDIT: Pandas 0.18.1 on Python 2.7.12

EDIT2: Deleted, my mistake.


Answer: I see two problems - first you need dtypes `datetimes` and then your sample
data where output is `NaT` (len of each group was `1`, so `difference` is
`NaT`):

    
    
    import datetime as dt
    import pandas as pd
    
    dd = [['A','A','A','A','B','B']\
         ,[dt.date(1981,3,6),dt.date(1986,5,1),dt.date(1983,11,8)\
         ,dt.date(1982,6,11),dt.date(1977,2,26),dt.date(1991,9,4)]]
    dd = list(zip(*dd))
    DF = pd.DataFrame(dd,columns=['Label','Date'])
    DF['Date2'] = DF['Date'].copy()
    
    print (DF)
      Label        Date       Date2
    0     A  1981-03-06  1981-03-06
    1     A  1986-05-01  1986-05-01
    2     A  1983-11-08  1983-11-08
    3     A  1982-06-11  1982-06-11
    4     B  1977-02-26  1977-02-26
    5     B  1991-09-04  1991-09-04
    
    
    
    print (DF.dtypes)
    Label    object
    Date     object
    Date2    object
    dtype: object
    
    DF['Date'] = pd.to_datetime(DF['Date'])
    DF['Date2'] = pd.to_datetime(DF['Date2'])
    print (DF.dtypes)
    Label            object
    Date     datetime64[ns]
    Date2    datetime64[ns]
    dtype: object
    
    print (DF.groupby(['Label','Date'])['Date2'].diff())
    0   NaT
    1   NaT
    2   NaT
    3   NaT
    4   NaT
    5   NaT
    Name: Date2, dtype: timedelta64[ns]
    

I changed data in `Date2`:

    
    
    import datetime as dt
    import pandas as pd
    
    dd = [['A','A','A','A','B','B']\
         ,[dt.date(1981,3,6),dt.date(1981,3,6),dt.date(1983,11,8)\
         ,dt.date(1983,11,8),dt.date(1977,2,26),dt.date(1991,9,4)]\
         ,[dt.date(1981,3,6),dt.date(1986,5,1),dt.date(1983,11,8)\
             ,dt.date(1982,6,11),dt.date(1977,2,26),dt.date(1991,9,4)]]
    dd = list(zip(*dd))
    DF = pd.DataFrame(dd,columns=['Label','Date', 'Date2'])
    
    DF['Date'] = pd.to_datetime(DF['Date'])
    DF['Date2'] = pd.to_datetime(DF['Date2'])
    print (DF)
      Label       Date      Date2
    0     A 1981-03-06 1981-03-06
    1     A 1981-03-06 1986-05-01
    2     A 1983-11-08 1983-11-08
    3     A 1983-11-08 1982-06-11
    4     B 1977-02-26 1977-02-26
    5     B 1991-09-04 1991-09-04
    
    print (DF.dtypes)
    Label            object
    Date     datetime64[ns]
    Date2    datetime64[ns]
    dtype: object
    
    
    
    print (DF.groupby(['Label','Date'])['Date2'].diff())
    0         NaT
    1   1882 days
    2         NaT
    3   -515 days
    4         NaT
    5         NaT
    Name: Date2, dtype: timedelta64[ns]
    
    print (DF.groupby(['Label','Date']).diff())
          Date2
    0       NaT
    1 1882 days
    2       NaT
    3 -515 days
    4       NaT
    5       NaT
    Label  Date  
    print (DF.groupby(['Label','Date']).apply(lambda s: s[u'Date2'].diff()))
    A      1981-03-06  0         NaT
                       1   1882 days
           1983-11-08  2         NaT
                       3   -515 days
    B      1977-02-26  4         NaT
           1991-09-04  5         NaT
    Name: Date2, dtype: timedelta64[ns]
    print (DF.groupby(['Label','Date'])[u'Date2'].transform(pd.Series.diff))
    0          NaT
    1   1975-02-26
    2          NaT
    3   1968-08-04
    4          NaT
    5          NaT
    Name: Date2, dtype: datetime64[ns]
    

If remove converting [`to_datetime`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.to_datetime.html), output is `NaN` and with
groups with numbers `NaT`:

    
    
    import datetime as dt
    import pandas as pd
    
    dd = [['A','A','A','A','B','B']\
         ,[dt.date(1981,3,6),dt.date(1981,3,6),dt.date(1983,11,8)\
         ,dt.date(1983,11,8),dt.date(1977,2,26),dt.date(1991,9,4)]\
         ,[dt.date(1981,3,6),dt.date(1986,5,1),dt.date(1983,11,8)\
             ,dt.date(1982,6,11),dt.date(1977,2,26),dt.date(1991,9,4)]]
    dd = list(zip(*dd))
    DF = pd.DataFrame(dd,columns=['Label','Date', 'Date2'])
    print (DF)
      Label        Date       Date2
    0     A  1981-03-06  1981-03-06
    1     A  1981-03-06  1986-05-01
    2     A  1983-11-08  1983-11-08
    3     A  1983-11-08  1982-06-11
    4     B  1977-02-26  1977-02-26
    5     B  1991-09-04  1991-09-04
    
    print (DF.dtypes)
    Label    object
    Date     object
    Date2    object
    dtype: object
    
    
    
    print (DF.groupby(['Label','Date'])['Date2'].diff())
    0                   NaT
    1    1882 days, 0:00:00
    2                   NaT
    3    -515 days, 0:00:00
    4                   NaN
    5                   NaN
    Name: Date2, dtype: object
    
    print (DF.groupby(['Label','Date']).diff())
                    Date2
    0                 NaN
    1  1882 days, 0:00:00
    2                 NaN
    3  -515 days, 0:00:00
    4                 NaN
    5                 NaN
    print (DF.groupby(['Label','Date']).apply(lambda s: s[u'Date2'].diff()))
    Label  Date         
    A      1981-03-06  0                   NaT
                       1    1882 days, 0:00:00
           1983-11-08  2                   NaT
                       3    -515 days, 0:00:00
    B      1977-02-26  4                   NaN
           1991-09-04  5                   NaN
    Name: Date2, dtype: object
    print (DF.groupby(['Label','Date'])[u'Date2'].transform(pd.Series.diff))
    0                  None
    1    162604800000000000
    2                  None
    3    -44496000000000000
    4                   NaN
    5                   NaN
    Name: Date2, dtype: object
    

EDIT:

If length of group is `1` and it means has one row, then
[`diff`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.diff.html) return `NaT`:

    
    
    import pandas as pd
    import numpy as np
    import io
    
    import datetime as dt
    import pandas as pd
    
    dd = [['A','A','A','A','B','B']\
         ,[dt.date(1981,3,6),dt.date(1981,3,6),dt.date(1983,11,8)\
         ,dt.date(1983,11,8),dt.date(1977,2,26),dt.date(1991,9,4)]\
         ,[dt.date(1981,3,6),dt.date(1986,5,1),dt.date(1983,11,8)\
         ,dt.date(1982,6,11),dt.date(1977,2,26),dt.date(1991,9,4)]]
    
    dd = list(zip(*dd))
    DF = pd.DataFrame(dd,columns=['Label','Date', 'Date2'])
    DF['Date'] = pd.to_datetime(DF['Date'])
    DF['Date2'] = pd.to_datetime(DF['Date2'])
    print (DF)
      Label       Date      Date2
    0     A 1981-03-06 1981-03-06
    1     A 1981-03-06 1986-05-01
    2     A 1983-11-08 1983-11-08
    3     A 1983-11-08 1982-06-11
    4     B 1977-02-26 1977-02-26
    5     B 1991-09-04 1991-09-04
    
    
    
    for i, g in DF.groupby(['Label','Date']):
        print (g)
        print ('diff: ')
        print (g[['Date', 'Date2']].diff())
        print ('------------')
    
    0     A 1981-03-06 1981-03-06
    1     A 1981-03-06 1986-05-01
    diff: 
        Date     Date2
    0    NaT       NaT
    1 0 days 1882 days
    ------------
      Label       Date      Date2
    2     A 1983-11-08 1983-11-08
    3     A 1983-11-08 1982-06-11
    diff: 
        Date     Date2
    2    NaT       NaT
    3 0 days -515 days
    ------------
      Label       Date      Date2
    4     B 1977-02-26 1977-02-26
    diff: 
       Date  Date2
    4   NaT    NaT
    ------------
      Label       Date      Date2
    5     B 1991-09-04 1991-09-04
    diff: 
       Date  Date2
    5   NaT    NaT
    ------------
    print ('*************************')    
    
    
    
    for i, g in DF.groupby(['Label','Date2']):
        print (g)
        print ('diff2: ')
        print (g[['Date', 'Date2']].diff())
        print ('------------')
    
      Label       Date      Date2
    0     A 1981-03-06 1981-03-06
    diff2: 
       Date  Date2
    0   NaT    NaT
    ------------
      Label       Date      Date2
    3     A 1983-11-08 1982-06-11
    diff2: 
       Date  Date2
    3   NaT    NaT
    ------------
      Label       Date      Date2
    2     A 1983-11-08 1983-11-08
    diff2: 
       Date  Date2
    2   NaT    NaT
    ------------
      Label       Date      Date2
    1     A 1981-03-06 1986-05-01
    diff2: 
       Date  Date2
    1   NaT    NaT
    ------------
      Label       Date      Date2
    4     B 1977-02-26 1977-02-26
    diff2: 
       Date  Date2
    4   NaT    NaT
    ------------
      Label       Date      Date2
    5     B 1991-09-04 1991-09-04
    diff2: 
       Date  Date2
    5   NaT    NaT
    ------------
    

