#!/usr/bin/env python
# coding: utf-8

# In[ ]:


'''
将df.price.shift(1)改为df_shift
'''


# In[1]:


import pandas as pd
import numpy as np


# In[2]:


import os, sys
path = "D:\大三下\数据分析\data-analysis-master"
retval = os.getcwd()
os.chdir( path )
retval = os.getcwd()
print(retval)


# In[3]:


os.chdir("D:\大三下\数据分析\data-analysis-master")


# In[4]:


os.listdir()


# In[5]:


bond= pd.read_csv('bond_intraday_trade.csv')


# In[6]:


data_use = bond[['cusip_id','trd_exctn_dt','trd_exctn_tm','rptd_pr']]
data_use.columns=['id','date','hour','price']


# In[7]:


data_use.head()


# In[8]:


data_use.info()
print('-'*20)
data_use['price'].describe()


# In[9]:


data_use2 = data_use.drop(index=(data_use.loc[(data_use['price']==0)].index))


# In[10]:


data_use2['hour_adj'] = data_use2['hour'].str.replace(':','').astype('int')


# In[11]:


data_use2['date'] = pd.to_datetime(data_use2['date'],format='%Y%m%d')


# In[29]:


low_trade_times = 0
positive_cov = 0
def roll_liquidity1(df):
    # if cov> 0, return np.nan
    global low_trade_times, positive_cov
    # first： 时间相同，取价格mean
    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)
    # only calculate when size > 3
    if temp.shape[0]>3:
        df_price = df.price.shift(1)
        rate = (df.price - df.price.shift(1))/df_price
        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na        
        if c < 0:
            return np.sqrt(-c)*2
        else:
            positive_cov += 1
            return np.nan
    low_trade_times += 1
    return [np.nan]*4


def roll_liquidity2(df):
    # if cov> 0, return 0
    #global low_trade_times, positive_cov
    # first： 时间相同，取价格mean
    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)
    # only calculate when size > 3
    if temp.shape[0]>3:
        df_price = df.price.shift(1)
        rate = (df.price - df_price)/df_price
        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na
        if c < 0:
            return np.sqrt(-c)*2
        else:
            #positive_cov += 1
            return 0
    #low_trade_times += 1
    return np.nan   # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要



def roll_liquidity3(df):
    # if cov> 0, take the square root without applying the negative sign and treat the result as a negative spread.
    #global low_trade_times, positive_cov
    # first： 时间相同，取价格mean
    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)
    # only calculate when size > 3
    if temp.shape[0]>3:
        df_price = df.price.shift(1)
        rate = (df.price - df_price)/df_price
        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na
        if c < 0:
            return np.sqrt(-c)*2
        else:
            #positive_cov += 1
            return -np.sqrt(c)*2
    #low_trade_times += 1
    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要


def roll_liquidity4(df):
    # if cov> 0, treat positive covariances as if they are negative, resulting in a positive Roll spread estimate
    #global low_trade_times, positive_cov
    # first： 时间相同，取价格mean
    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)
    # only calculate when size > 3
    if temp.shape[0]>3:
        df_price = df.price.shift(1)
        rate = (df.price - df_price)/df_price
        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na
        if c < 0:
            return np.sqrt(-c)*2
        else:
            #positive_cov += 1
            return np.sqrt(c)*2
    #low_trade_times += 1
    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要


# In[30]:


low_trade_times = 0
positive_cov = 0
roll_daily = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity1),columns = ['roll_liquidity_method1'])
low_trade_times, positive_cov


# In[31]:


roll_daily2 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity2),columns = ['roll_liquidity_method2'])
roll_daily3 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity3),columns = ['roll_liquidity_method3'])
roll_daily4 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity4),columns = ['roll_liquidity_method4'])


# In[32]:


result = pd.concat([roll_daily,roll_daily2,roll_daily3,roll_daily4],axis = 1)
result.reset_index(inplace=True)
result.rename(columns={'id':'cusip_id'},inplace = True)
result.to_csv('roll_daily_liquidity_measure_all.csv', index = False)


# In[33]:


result


# In[ ]: