[已解决]从一个excel列数据中分批提取进行计算，并将计算结果储存成一个新的表格的例子求助

incrediblejack

之前我发了一个带有傅里叶变换的例子，（见帖子：https://fishc.com.cn/thread-171039-1-1.html）
我感觉可能是傅里叶变换对整个运行过程有影响，于是想写个更简单的例子，把傅里叶变换的内容先去掉，只从原始数据列中分批取出部分数据，分别计算所取出当前批次的平均值、最大值、最小值、方差、标准差，并把这五个结果当做特征，组成一张新的表   （表的格式是：每一批数据的对应五个特征写成一行，取了几批数据，就有几行特征数据。）
但是我自己写的代码，结果却只有一行。运行结果里面很乱。我多次尝试也改不成正确的。劳烦各位大神指点迷津，告诉我该怎么改呀。谢谢啦！

数据集：https://pan.baidu.com/s/1U4gADju6QtH2ZRFDLEb3-g 提取码 fgcu

我写的简版的代码附上：

#本例为不使用傅里叶变换的情况下，只用一些简单的方法来提取特征，然后存入并形成新的数据集的例子。
#自己写个尝试一下。20200603
import numpy as np
import matplotlib.pyplot as plt
import os
os.environ['TF_CPP_MIN_LOG_LEVEL'] = '2'
import xlrd
import xlwt
import typing

workbook = xlwt.Workbook(encoding='utf-8')
booksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)

train1 = xlrd.open_workbook('shuangtu_13_14.xlsx')  #读取原始数据文件
table1 = train1.sheets()[0]    #提第一页sheet
len_train = 2003              #设置取用数据长度（可更改）
a = [0 for i in range(len_train)]      #中间变量，列表a
for i in range(len_train):             #1000次
    a[i]=table1.row_values(i)[1]       #将第1列的内容，逐行读取，存入a列表


#全局变量
changdu = 250
start_i = 1
# f_1 = 0
# f_2 = 0
# f_3 = 0
# f_4 = 0
# f_5 = 0
def calculateMean(x):
    #xf_i = []
    xf = []
    xs = x[start_i:start_i + changdu]
    for p in xs:
        xf.append(p*p*3)   #注意这里不要写成 xf = xf.apppend(p*3)

    return (x, xs, xf)

#特征1：计算平均数
def function_f1(input):
    sum_mid_f1_v1 = 0  #当前值
    sum_mid_f1_v2 = 0  #加和值
    #f_1 : typing.Optional[function_f1.Response] = None
    f_1 = None
    for i in range(len(dangqianxuanqu)):
        f_1 = 0
        sum_mid_f1_v1 = input[i]
        sum_mid_f1_v2 += sum_mid_f1_v1
        f_1 = sum_mid_f1_v2 / len(dangqianxuanqu)
    return f_1

#特征2：计算最大值
def function_f2(input):
    sum_mid_f2_v1 = 0  #当前值
    sum_mid_f2_v2 = []
    f_2 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f2_v1 = input[i]
        sum_mid_f2_v2.append(sum_mid_f2_v1)
        f_2 = max(sum_mid_f2_v2)
    return f_2

#特征3：计算最小值
def function_f3(input):
    sum_mid_f3_v1 = 0
    sum_mid_f3_v2 = []
    f_3 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f3_v1 = input[i]
        sum_mid_f3_v2.append(sum_mid_f3_v1)
        f_3 = min(sum_mid_f3_v2)
    return f_3

#特征4：计算方差
def function_f4(input):
    sum_mid_f4_v1 = 0
    sum_mid_f4_v2 = 0
    f_4 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f4_v1 = (function_f1(input) - input[i])**2
        sum_mid_f4_v2 += sum_mid_f4_v1
        f_4 = sum_mid_f4_v2 / len(dangqianxuanqu)
    return f_4

#特征5：计算标准差
def function_f5(input):
    sum_mid_f5_v1 = 0
    sum_mid_f5_v2 = 0
    sum_mid_f5_v3 = 0
    f_5 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f5_v1 = (function_f1(input) - input[i])**2
        sum_mid_f5_v2 += sum_mid_f5_v1
        sum_mid_f5_v3 = sum_mid_f5_v2 / len(dangqianxuanqu)
        f_5 = (sum_mid_f5_v3)**0.5
    return f_5




for j in range(len_train // changdu):
    for er in range(len_train // changdu):
        calC = calculateMean(a)
        yuanshishuju = calC[0]  #原始未经过处理的数据
        dangqianxuanqu = calC[1]  #选出的一段长度为changdu的原始数据
        dangqianshuzhi = calC[2]  #changdu对应的计算值  3*原始值*当前原始值
        # print('dangqianshuzhi:')
        # print(dangqianshuzhi)
        # print(' ')
        # print(len(dangqianshuzhi))

        ff1 = function_f1(dangqianshuzhi)
        # print('ff1:')
        # print(ff1)
        # print(' ')
        ff2 = function_f2(dangqianshuzhi)
        # print('ff2:')
        # print(ff2)
        # print(' ')
        ff3 = function_f3(dangqianshuzhi)
        # print('ff3:')
        # print(ff3)
        # print(' ')
        ff4 = function_f4(dangqianshuzhi)
        ff5 = function_f5(dangqianshuzhi)

        rowdata = [ff1, ff2, ff3, ff4, ff5]
        # print('rowdata:')
        # print(rowdata)
        # print(' ')

        for i in range(len(rowdata)):
            booksheet.write(j,i, rowdata[i])

            print('rowdata:',j, i)
            print(rowdata, j)
            print(' ')

            workbook.save('20200603.xls')

        start_i += changdu

#workbook.save('20200603.xls')

最佳答案

月排行榜 / 总排行榜

yifenyu

2020-6-4 19:16:00

# 本例为不使用傅里叶变换的情况下，只用一些简单的方法来提取特征，然后存入并形成新的数据集的例子。
# 自己写个尝试一下。20200603
import typing
import xlwt
import xlrd
import numpy as np
# import matplotlib.pyplot as plt
import os
os.environ['TF_CPP_MIN_LOG_LEVEL'] = '2'



train1 = xlrd.open_workbook('shuangtu_13_14.xlsx')  # 读取原始数据文件
table1 = train1.sheets()[0]  # 提第一页sheet
workbook = xlwt.Workbook(encoding='utf-8')
booksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
len_train = 2003  # 设置取用数据长度（可更改）
# a = [0 for i in range(len_train)]  # 中间变量，列表a
# a = []
# for i in range(len_train):  # 1000次
#     a[i] = table1.row_values(i)[1]  # 将第1列的内容，逐行读取，存入a列表
a = [i[1].value for i in table1.get_rows()]


# 全局变量
changdu = 250
start_i = 1
# f_1 = 0
# f_2 = 0
# f_3 = 0
# f_4 = 0
# f_5 = 0


def calculateMean(x, start_i):
    #xf_i = []
    xf = []
    print(start_i)
    xs = x[start_i:start_i + changdu]
    for p in xs:
        xf.append(p*p*3)  # 注意这里不要写成 xf = xf.apppend(p*3)

    return (x, xs, xf)

# 特征1：计算平均数


def function_f1(input):
    sum_mid_f1_v1 = 0  # 当前值
    sum_mid_f1_v2 = 0  # 加和值
    # f_1 : typing.Optional[function_f1.Response] = None
    f_1 = None
    for i in range(len(dangqianxuanqu)):
        f_1 = 0
        sum_mid_f1_v1 = input[i]
        sum_mid_f1_v2 += sum_mid_f1_v1
    f_1 = sum_mid_f1_v2 / len(dangqianxuanqu)
    return f_1

# 特征2：计算最大值


def function_f2(input):
    sum_mid_f2_v1 = 0  # 当前值
    sum_mid_f2_v2 = []
    f_2 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f2_v1 = input[i]
        sum_mid_f2_v2.append(sum_mid_f2_v1)
    f_2 = max(sum_mid_f2_v2)
    return f_2

# 特征3：计算最小值


def function_f3(input):
    sum_mid_f3_v1 = 0
    sum_mid_f3_v2 = []
    f_3 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f3_v1 = input[i]
        sum_mid_f3_v2.append(sum_mid_f3_v1)
    f_3 = min(sum_mid_f3_v2)
    return f_3

# 特征4：计算方差


def function_f4(input):
    sum_mid_f4_v1 = 0
    sum_mid_f4_v2 = 0
    f_4 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f4_v1 = (function_f1(input) - input[i])**2
        sum_mid_f4_v2 += sum_mid_f4_v1
    f_4 = sum_mid_f4_v2 / len(dangqianxuanqu)
    return f_4

# 特征5：计算标准差


def function_f5(input):
    sum_mid_f5_v1 = 0
    sum_mid_f5_v2 = 0
    sum_mid_f5_v3 = 0
    f_5 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f5_v1 = (function_f1(input) - input[i])**2
        sum_mid_f5_v2 += sum_mid_f5_v1
    sum_mid_f5_v3 = sum_mid_f5_v2 / len(dangqianxuanqu)
    f_5 = (sum_mid_f5_v3)**0.5
    return f_5


for j in range(len(a) // changdu):
    # for er in range(len_train // changdu):
    calC = calculateMean(a, j*changdu)
    yuanshishuju = calC[0]  # 原始未经过处理的数据
    dangqianxuanqu = calC[1]  # 选出的一段长度为changdu的原始数据
    dangqianshuzhi = calC[2]  # changdu对应的计算值  3*原始值*当前原始值
    # print('dangqianshuzhi:')
    # print(dangqianshuzhi)
    # print(' ')
    # print(len(dangqianshuzhi))

    ff1 = function_f1(dangqianshuzhi)
    # print('ff1:')
    # print(ff1)
    # print(' ')
    ff2 = function_f2(dangqianshuzhi)
    # print('ff2:')
    # print(ff2)
    # print(' ')
    ff3 = function_f3(dangqianshuzhi)
    # print('ff3:')
    # print(ff3)
    # print(' ')
    ff4 = function_f4(dangqianshuzhi)
    ff5 = function_f5(dangqianshuzhi)

    rowdata = [ff1, ff2, ff3, ff4, ff5]
    # print('rowdata:')
    # print(rowdata)
    # print(' ')

    for i in range(len(rowdata)):
        booksheet.write(j, i, rowdata[i])

#     print('rowdata:', j, i)
#     print(rowdata, j)
#     print(' ')

    # workbook.save('20200603.xls')

    # start_i += changdu

workbook.save('20200603.xls')

跳转到最佳答案楼层

jinlovelive

xlwt快忘光了，你先试着把循环里的workbook.save('20200603.xls')删了

yifenyu

# 本例为不使用傅里叶变换的情况下，只用一些简单的方法来提取特征，然后存入并形成新的数据集的例子。
# 自己写个尝试一下。20200603
import typing
import xlwt
import xlrd
import numpy as np
# import matplotlib.pyplot as plt
import os
os.environ['TF_CPP_MIN_LOG_LEVEL'] = '2'



train1 = xlrd.open_workbook('shuangtu_13_14.xlsx')  # 读取原始数据文件
table1 = train1.sheets()[0]  # 提第一页sheet
workbook = xlwt.Workbook(encoding='utf-8')
booksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
len_train = 2003  # 设置取用数据长度（可更改）
# a = [0 for i in range(len_train)]  # 中间变量，列表a
# a = []
# for i in range(len_train):  # 1000次
#     a[i] = table1.row_values(i)[1]  # 将第1列的内容，逐行读取，存入a列表
a = [i[1].value for i in table1.get_rows()]


# 全局变量
changdu = 250
start_i = 1
# f_1 = 0
# f_2 = 0
# f_3 = 0
# f_4 = 0
# f_5 = 0


def calculateMean(x, start_i):
    #xf_i = []
    xf = []
    print(start_i)
    xs = x[start_i:start_i + changdu]
    for p in xs:
        xf.append(p*p*3)  # 注意这里不要写成 xf = xf.apppend(p*3)

    return (x, xs, xf)

# 特征1：计算平均数


def function_f1(input):
    sum_mid_f1_v1 = 0  # 当前值
    sum_mid_f1_v2 = 0  # 加和值
    # f_1 : typing.Optional[function_f1.Response] = None
    f_1 = None
    for i in range(len(dangqianxuanqu)):
        f_1 = 0
        sum_mid_f1_v1 = input[i]
        sum_mid_f1_v2 += sum_mid_f1_v1
    f_1 = sum_mid_f1_v2 / len(dangqianxuanqu)
    return f_1

# 特征2：计算最大值


def function_f2(input):
    sum_mid_f2_v1 = 0  # 当前值
    sum_mid_f2_v2 = []
    f_2 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f2_v1 = input[i]
        sum_mid_f2_v2.append(sum_mid_f2_v1)
    f_2 = max(sum_mid_f2_v2)
    return f_2

# 特征3：计算最小值


def function_f3(input):
    sum_mid_f3_v1 = 0
    sum_mid_f3_v2 = []
    f_3 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f3_v1 = input[i]
        sum_mid_f3_v2.append(sum_mid_f3_v1)
    f_3 = min(sum_mid_f3_v2)
    return f_3

# 特征4：计算方差


def function_f4(input):
    sum_mid_f4_v1 = 0
    sum_mid_f4_v2 = 0
    f_4 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f4_v1 = (function_f1(input) - input[i])**2
        sum_mid_f4_v2 += sum_mid_f4_v1
    f_4 = sum_mid_f4_v2 / len(dangqianxuanqu)
    return f_4

# 特征5：计算标准差


def function_f5(input):
    sum_mid_f5_v1 = 0
    sum_mid_f5_v2 = 0
    sum_mid_f5_v3 = 0
    f_5 = None
    for i in range(len(dangqianxuanqu)):
        sum_mid_f5_v1 = (function_f1(input) - input[i])**2
        sum_mid_f5_v2 += sum_mid_f5_v1
    sum_mid_f5_v3 = sum_mid_f5_v2 / len(dangqianxuanqu)
    f_5 = (sum_mid_f5_v3)**0.5
    return f_5


for j in range(len(a) // changdu):
    # for er in range(len_train // changdu):
    calC = calculateMean(a, j*changdu)
    yuanshishuju = calC[0]  # 原始未经过处理的数据
    dangqianxuanqu = calC[1]  # 选出的一段长度为changdu的原始数据
    dangqianshuzhi = calC[2]  # changdu对应的计算值  3*原始值*当前原始值
    # print('dangqianshuzhi:')
    # print(dangqianshuzhi)
    # print(' ')
    # print(len(dangqianshuzhi))

    ff1 = function_f1(dangqianshuzhi)
    # print('ff1:')
    # print(ff1)
    # print(' ')
    ff2 = function_f2(dangqianshuzhi)
    # print('ff2:')
    # print(ff2)
    # print(' ')
    ff3 = function_f3(dangqianshuzhi)
    # print('ff3:')
    # print(ff3)
    # print(' ')
    ff4 = function_f4(dangqianshuzhi)
    ff5 = function_f5(dangqianshuzhi)

    rowdata = [ff1, ff2, ff3, ff4, ff5]
    # print('rowdata:')
    # print(rowdata)
    # print(' ')

    for i in range(len(rowdata)):
        booksheet.write(j, i, rowdata[i])

#     print('rowdata:', j, i)
#     print(rowdata, j)
#     print(' ')

    # workbook.save('20200603.xls')

    # start_i += changdu

workbook.save('20200603.xls')

incrediblejack

yifenyu 发表于 2020-6-4 19:16
# 本例为不使用傅里叶变换的情况下，只用一些简单的方法来提取特征，然后存入并形成新的数据集的例子。
#  ...

非常感谢！！！