pandas - Comparing data across dates at different times

Question

I have a sequence of data in datetime dataFrame and need to compare the data from one date and time to the prior day's another time. In the example below, I would, for example need to calculate percentage change from 2016-11-09 09:30:00 to 2016-11-10 21:30:00 (essentially from t @ 9:30 to next_date in the table @ 21:30.) Unfortunately a simple lag function might not work, since the pattern of the records are unpredictable and there might not be the same number of records between the desired rows.

dates = pd.date_range('2016-11-09 09:30:00',periods=10, freq='12H')
df =pd.DataFrame(np.random.randn(10,4)*100,index=dates,columns=list('ABCD'))

                              A|           B|           C|           D
-----------------------------------------------------------------------
2016-11-09 09:30:00|   74.409062|    3.635309|   17.603051|    6.743699

2016-11-09 21:30:00|   25.707464|  133.592600| -176.460798|  236.354740

2016-11-10 09:30:00|  -13.035709|  -82.974810|  106.204290|  -31.382023

2016-11-10 21:30:00| -120.712954|   -2.636682|   16.839875|  -12.177463

2016-11-11 09:30:00| -195.382169| -102.214945|   84.151532| -130.732630

....

Is there an easy way to do this comparison or would I need to run a for loop?

Answer 1

I think the best approach might be to split the two set of records at the two different times into two DataFrames, align the dates, get rid of the times and compare them across the two DataFrames.