python处理数据，均值，偏差，占比

###############    data:2021.4.8            ###############################
###############    function:数据分析         ###############################

from openpyxl import load_workbook, Workbook

#文件路径
filepath = r'D:\work\python_work\eswin\song_1\statistic.xlsx'
#打开文件
wb = load_workbook(filepath)

# 通过属性sheetnames获取所有Worksheet的名字
print(wb.sheetnames)
#打开random,statistic
wsheet = wb['random']
#打开statistic
#ws_stic = wb['statistic']
#生成statistic
ws_stic = wb.create_sheet("statistic", 0)
# 获取sheet总行数和总列数
rows = wsheet.max_row
cols = wsheet.max_column

#表格规划
ws_stic.cell(1, 1, "数")
ws_stic.cell(1, 2, "个数")
ws_stic.cell(1, 3, "总和")
ws_stic.cell(1, 4, "均值")
ws_stic.cell(1, 5, "差值")
ws_stic.cell(1, 6, "绝对值")
ws_stic.cell(1, 7, "偏差率(差值绝对值/均值*100%)")   #差值绝对值/均值*100%
ws_stic.cell(1, 8, "数据占比(数据个数/总的数据数*%100)")   #数据个数/总的数据数*100%

#找到不同的数
i = 0
b = wsheet.cell(row=1, column=1).value
ws_stic.cell(2, 1, b)
while i<rows:
    i = i+1
    #轮询数据,random
    num_random = wsheet.cell(row=i, column=1).value
    #目前为止有几个不同的数
    rows_stic = ws_stic.max_row
    j = 0
    cnt_row = 0
    while j<rows_stic:
        j = j+1
        #提取数据
        num_stic = ws_stic.cell(row=j, column=1).value
        if num_random != num_stic:
            cnt_row += 1
    #如果出现不同的数，在末尾添上     
    if cnt_row == rows_stic:
        ws_stic.cell(rows_stic+1, 1, num_random)

#数组排序
j = 2
x1 = [ws_stic.cell(row=2, column=1).value]
while j<rows_stic:
    j = j+1
    x1[len(x1):] = [ws_stic.cell(row=j, column=1).value]
x1.sort() #排序，从小到大排序

#排序后写入excel
i = 1
num_value = 0
while i<rows_stic:
    i = i+1
    num_value = x1[0]
    del x1[0]  ##每写入一个数，就把数组第一个数删除，每次写入到数都是数组第一个数
    ws_stic.cell(i, 1, num_value)




j = 1
while j<rows_stic:
    j = j+1
    #未计数时每个数有0个
    ws_stic.cell(j, 2, 0)


#统计不同数的个数
i = 0
cnt_stic_num = 0
while i<rows:
    i = i+1
    #轮询数据,random
    num_random = wsheet.cell(row=i, column=1).value
    #目前为止有几个不同的数
    rows_stic = ws_stic.max_row
    j = 1
    while j<rows_stic:
        j = j+1
        #轮询statistic
        num_stic = ws_stic.cell(row=j, column=1).value
        cnt_stic_num = ws_stic.cell(row=j, column=2).value
        #相同数计数+1
        if num_random == num_stic:
            cnt_stic_num = cnt_stic_num + 1
            ws_stic.cell(j, 2, cnt_stic_num)     


#求和，与均值
i = 1
sum = 0
while i<rows:
    i = i+1
    #print(wsheet.cell(row=i, column=1).value)
    sum += wsheet.cell(row=i, column=1).value
mean_value = sum/rows
print(sum, i)
print(mean_value)
ws_stic.cell(2, 3, sum)
ws_stic.cell(2, 4, mean_value)


#求差值，绝对值，偏移率，数据个数
j = 1
d_value = 0
offset_radio = 0
sum_num = 0
while j<rows_stic:
    j = j+1
    num_stic = ws_stic.cell(row=j, column=1).value
    sum_num = ws_stic.cell(row=j, column=2).value
    d_value = num_stic-mean_value
    ws_stic.cell(j, 5, d_value)
    ws_stic.cell(j, 8, round(sum_num/rows,4)*100 )
    offset_radio = round(d_value/mean_value, 4)*100
    if d_value<0:
        ws_stic.cell(j, 6, -d_value)
        #offset_radio = -(d_value/mean_value * 100)
        ws_stic.cell(j, 7, -offset_radio)
    else:
        ws_stic.cell(j, 6, d_value)
        ws_stic.cell(j, 7, offset_radio)


wb.save(filepath)