Python pandas timeseries resample giving unexpected results
Question: The data here is for a bank account with a running balance. I want to resample
the data to only use the end of day balance, so the last value given for a
day. There can be multiple data points for a day, representing multiple
transactions.

    
    
    In [1]: from StringIO import StringIO
    
    In [2]: import pandas as pd
    
    In [3]: import numpy as np
    
    In [4]: print "Pandas version", pd.__version__
    Pandas version 0.12.0
    
    In [5]: print "Numpy version", np.__version__
    Numpy version 1.7.1
    
    In [6]: data_string = StringIO(""""Date","Balance"
       ...: "08/09/2013","1000"
       ...: "08/09/2013","950"
       ...: "08/09/2013","930"
       ...: "08/06/2013","910"
       ...: "08/02/2013","900"
       ...: "08/01/2013","88"
       ...: "08/01/2013","87"
       ...: """)
    
    In [7]: ts = pd.read_csv(data_string, parse_dates=[0], index_col=0)
    
    In [8]: print ts
                Balance
    Date               
    2013-08-09     1000
    2013-08-09      950
    2013-08-09      930
    2013-08-06      910
    2013-08-02      900
    2013-08-01       88
    2013-08-01       87
    

I expect "2013-08-09" to be 1000, but definitely not the 'middle' number 950.

    
    
    In [10]: ts.Balance.resample('D', how='last')
    Out[10]: 
    Date
    2013-08-01     88
    2013-08-02    900
    2013-08-03    NaN
    2013-08-04    NaN
    2013-08-05    NaN
    2013-08-06    910
    2013-08-07    NaN
    2013-08-08    NaN
    2013-08-09    950
    Freq: D, dtype: float64
    

I expect "2013-08-09" to be 930, or "2013-08-01" to be 88.

    
    
    In [12]: ts.Balance.resample('D', how='first')
    Out[12]: 
    Date
    2013-08-01      87
    2013-08-02     900
    2013-08-03     NaN
    2013-08-04     NaN
    2013-08-05     NaN
    2013-08-06     910
    2013-08-07     NaN
    2013-08-08     NaN
    2013-08-09    1000
    Freq: D, dtype: float64
    

Am I missing something here? Does resampling with 'first' and 'last' not work
the way I'm expecting it to?


Answer: To be able to resample your data Pandas first have to sort it. So if you load
your data and sort it by index you get the following thing:

    
    
    >>> pd.read_csv(data_string, parse_dates=[0], index_col=0).sort_index()
                Balance
    Date               
    2013-08-01       87
    2013-08-01       88
    2013-08-02      900
    2013-08-06      910
    2013-08-09     1000
    2013-08-09      930
    2013-08-09      950
    

Which explains why you got the results you got. @Jeff explained why the order
is "arbitrary" and according to your comment the solution is to use
`mergesort` algorithm on the data before the operations...

    
    
    >>> df = pd.read_csv(data_string, parse_dates=[0],
                         index_col=0).sort_index(kind='mergesort')
    >>> df.Balance.resample('D',how='last')
    2013-08-01      88
    2013-08-02     900
    2013-08-03     NaN
    2013-08-04     NaN
    2013-08-05     NaN
    2013-08-06     910
    2013-08-07     NaN
    2013-08-08     NaN
    2013-08-09    1000
    >>> df.Balance.resample('D', how='first')
    2013-08-01     87
    2013-08-02    900
    2013-08-03    NaN
    2013-08-04    NaN
    2013-08-05    NaN
    2013-08-06    910
    2013-08-07    NaN
    2013-08-08    NaN
    2013-08-09    930
    

