Pandas dataframe - timeseries overnight returns

Question

EXAMPLE of data sets

I am creating a pandas dataframe with trading data (datetime, time, volume, price). I built the dataframe using several and identical files (each file represents a trading day) then I resample my dataframe using a 5 minutes interval.

I then calculate the return between each interval BUT I do not want to calculate the return from one day to another (i.e. the return between the last datapoint at day t and the first datapoint at day t+1).

    list_=[]
    big_df=pd.DataFrame()

    #read file into pandas
    for file in filelist:

        #create panda dataframe
        df=pd.read_hdf(file)
        #Retrieve time and price
        data= df.filter(['datetime','price'], axis=1)
        data = data.set_index('datetime')

        #Resample dataframe
        data = data.resample('5T').mean().bfill().between_time('04:00', '19:00')

        list_.append(data)

   #concatenate them together
   big_df = pd.concat(list_)

   # compute log returns
   ret_d = pd.DataFrame(100*np.log(big_df['price']).diff(1)*100)

The code above calculate the return for each interval including the return between 2 days. How can I exclude these returns? For instance, I do not want to calculate the return between day 1, 19:00 and day 2, 4:05 (please note that the first datapoint of a day could be anything after 4:05am; for instance 4:35, so we do not have the same number of datapoints each day).

My second problem is that I could not find a way to compute the weighted average mean of the price (using the volume) in my resampling (only the method .mean() is available in the pandas resample function according to the documentation). Is there any way to do this? Thank you.

Example:

In [1]: df = pd.DataFrame([[2017-01-04 18:51:00, 100,10], [2017-01-04 18:53:00, 101.5,50], [2017-01-04 18:58:00, 102.1], [2017-01-05 04:32:00, 102.6, 50], [2017-01-05 04:34:00, 102.7, 10], [2017-01-05 04:38:00, 103, 50]], columns=['datetime', 'price', 'volume'])

After puting 'datetime' as index, removing volume, and computing the weighted average price, the desired result should be the following dataframe:

pd.DataFrame([[2017-01-04 18:55:00, 101.25], [2017-01-04 19:00:00, 102], [2017-01-05 04:35:00, 102.62], [2017-01-05 04:40:00, 103]],['datetime', 'price'])

with: 101.25 = (101,5*50+100*10)/(50+10)

Finally, computing the log-return of the previous dataframe (excluding the return from a day-change), I should get:

[0.00320514*, 0**, 0.00162932***]

with: * log(102/101.25)

** 0 (since it is between 2 days)

*** log(103/102.62)

Answer 1

So I think I have figured out what you want - you are really asking about 2 quite separate things, the log return and the returns over days, but I think I have answered them both here. Your spreadsheet screenshot and the data in your example are inconsistent and not particularly simple to follow as mentioned in the comments, so let me know if this is the answer you expect.

Edited to incorporate comment:

import pandas as pd
import numpy as np

df = pd.DataFrame([
    ["2017-01-04 18:51:00", 100,10], 
    ["2017-01-04 18:53:00", 101.5,50], 
    ["2017-01-04 18:58:00", 102, 10], 
    ["2017-01-05 04:07:00", 101.9, 30], 
    ["2017-01-05 04:32:00", 102.6, 50], 
    ["2017-01-05 04:34:00", 102.7, 10], 
    ["2017-01-05 04:38:00", 103, 50]], columns=['datetime', 'price', 'volume'])

df['datetime'] = pd.to_datetime(df['datetime'])
df = df.set_index('datetime')
df['price_volume'] = df['price'] * df['volume']

df = df.resample("5T", label='right').agg(['sum', 'mean']).between_time('04:00', '19:00')

df['volume_weighted_price'] = df[('price_volume', 'sum')] / df[('volume', 'sum')]
df = df[['volume_weighted_price']]
df.columns = df.columns.droplevel(1)
df = df.groupby([pd.Grouper(level=0, freq='D', label='right')]).ffill()

df['log_return'] = np.log(df['volume_weighted_price']).diff(1)
print(df)

Which gives the resulting dataframe

                     volume_weighted_price  log_return
datetime                                              
2017-01-04 18:55:00             101.250000         NaN
2017-01-04 19:00:00             102.000000    0.007380
2017-01-05 04:00:00                    NaN         NaN
2017-01-05 04:05:00                    NaN         NaN
2017-01-05 04:10:00             101.900000         NaN
2017-01-05 04:15:00             101.900000    0.000000
2017-01-05 04:20:00             101.900000    0.000000
2017-01-05 04:25:00             101.900000    0.000000
2017-01-05 04:30:00             101.900000    0.000000
2017-01-05 04:35:00             102.616667    0.007008
2017-01-05 04:40:00             103.000000    0.003729

I first resample to ensure each 5 minute period exists, and sum and take the mean of all columns for calculating the volume weighted price. After calculating the price and rearranging the columns, I group by day and forward fill the prices. This gives each time period the previous periods price. Finally I calculate the returns.