python 写的对于单个表格做时间滞后性质的分析

python 写的对于单个表格做时间滞后性质的分析

1、注意

1）单个表格需要是两列数据（x，y）
2）不能有标题行
3）代码实现了x对y序列的时间滞后分析，改一下顺序便可以做y对x
4）本人在另外一篇博客中有批量读取excel文件的介绍批量读取excel文件

2、时间滞后的计算原理

1）参考以下文献
[1]韦振锋,任志远,张翀.气候因子与植被的时滞相关分析——以广西为例[J].生态环境学报,2013,22(11):1757-1762.
2）calculater函数便是将时间滞后原理编码实现

import xlrd
import xlwt
import math

def read_excel_xls(path):
    workbook = xlrd.open_workbook(path)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[0])
    fatherList = list()
    for i in range(0,worksheet.nrows):
        List1 = list(worksheet.row_values(i))
        fatherList.append(List1)
    print("成功打开表格！")
    print("--------------------------------------------------------")
    return fatherList

def calculate(ListX,ListY,n,k_range):
    DictResults = {}
    for k in range(0,k_range + 1):
        print("当k值取"+ k + "时")
        #y的平均值
        sum_Ytk = 0
        for t in range(1,n-k+1):
            sum_Ytk = sum_Ytk + ListY[t+k-1]
        average_Ytk = (1/(n-k)) * sum_Ytk
        print("Y的平均值值是：" + average_Ytk)

        #x的平均值
        sum_Xt = 0
        for t in range(1,n-k+1):
            sum_Xt = sum_Xt + ListX[t-1]
        average_Xt = (1/(n-k)) * sum_Xt

        #theta_Yk
        sum_Ytk_Ytk = 0
        for i in range(1,n-k+1):
            sum_Ytk_Ytk = sum_Ytk_Ytk + math.pow((ListY[i+k-1] - average_Ytk),2)
        theta_yk = math.sqrt((1/(n-k)) * sum_Ytk_Ytk)

        #theta_x
        sum_xk_xk = 0
        for i in range(1,n-k+1):
            sum_xk_xk = sum_xk_xk + math.pow((ListX[i-1] - average_Xt),2)
        theta_x = math.sqrt((1/(n-k)) * sum_xk_xk)

        #Ck
        sum_xy = 0
        for i in range(1,n-k+1):
            sum_xy = sum_xy + (ListX[i-1] - average_Xt) * (ListY[i+k-1] - average_Ytk)
        Ck = (1/(n-k)) * sum_xy

        #Pk
        Pk = Ck / (theta_x * theta_yk)
        DictResults[k] = Pk
    print("计算完成")
    print("--------------------------------------------------------")
    return DictResults

def wirte_excel_xls(path,sheet_name,dict):
    row = 0
    col = 0
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(sheet_name)
    for key in dict.keys():
        sheet.write(row, col, key)
        sheet.write(row, col + 1, dict[key])
        row += 1
    workbook.save(path)
    print("写入数据成功")


print("----------------Start----------------------------------")
xls_file_name = "Your excel file" #数据输入准备
fatherList = read_excel_xls(xls_file_name)
List1 = fatherList[0]
print("列表1的数值是：" + List1)
List2 = fatherList[1]
print("列表2的数值是：" + List2)
n = len(List1)
print("时间序列的长度值是：" + n)
k_range = int(n/4)
print("K值的范围值是：" + k_range)

DictResults = calculate(List1,List2,n,k_range)                    #数据计算
print("输出结果")
print(DictResults)

output_file_path = ""
wirte_excel_xls(output_file_path,"result",DictResults)
print("-----------------End-----------------------------------")