import pandas as pd
import numpy as np
import scipy
def excel_one_line_to_list():
X1 = pd.read_excel("xxx.xls", usecols=[0],
names=None) # 读取项目名称列,不要列名
X1 = np.array(X1)
X1 = X1.tolist()
X1 = pd.Series(X1)
X1 = X1.astype('str')
Y1 = pd.read_excel("xxx.xls", usecols=[2],
names=None) # 读取项目名称列,不要列名
Y1 = np.array(Y1)
Y1 = Y1.tolist()
Y1 = pd.Series(Y1)
Y1 = Y1.astype('str')
# 处理数据删除Nan
x1 = X1.dropna()
y1 = Y1.dropna()
n = x1.count()
x1.index = np.arange(n)
y1.index = np.arange(n)
# 分部计算
d = (x1.sort_values().index - y1.sort_values().index) ** 2
dd = d.to_series().sum()
p = 1 - n * dd / (n * (n ** 2 - 1))
# s.corr()函数计算
r = x1.corr(y1, method='spearman')
print(round(r,3), p) # 0.942857142857143 0.9428571428571428
print(scipy.stats.spearmanr(X1, Y1))
if __name__ == '__main__':
excel_one_line_to_list()
