Python对表格的数据进行处理然后在Excel中制作柱形图

原创 于 2017-06-13 16:38:57 发布 · 5k 阅读 · 9 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#python #excel #tkinter #字体

本文介绍了一款用于游戏性能分析的工具，该工具通过读取Excel文件中的游戏数据，并使用Python进行统计分析，最后将分析结果以图表形式展示在新的Excel文件中。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf8')

from matplotlib.font_manager import FontProperties
import xlrd
import xlsxwriter
from matplotlib.font_manager import FontProperties
import Tkinter as tk
from Tkinter import *
import xlrd
import xlsxwriter
import numpy as np
import matplotlib as mpl
from datetime import datetime

#字体颜色设置
font = FontProperties(fname=r"C:\\WINDOWS\\Fonts\\simsun.ttc", size=14)
font_size = 10
fig_size = (8, 6)
mpl.rcParams['font.size'] = font_size
mpl.rcParams['figure.figsize'] = fig_size



#提取表格数据
data=xlrd.open_workbook('AutoData.xlsx')
table = data.sheets()[0]
nrows = table.nrows
ncols = table.ncols

DrawCall=[]
for i in range(nrows):
    DrawCall.append(table.row_values(i)[1])
del DrawCall[0]
DrawCall.sort()

Area=[]
for i in range(nrows):
    Area.append(table.row_values(i)[6])
del Area[0]
Area.sort()

ParticleNumber=[]
for i in range(nrows):
    ParticleNumber.append(table.row_values(i)[8])
del ParticleNumber[0]
ParticleNumber.sort()

#建立客户端
window = tk.Tk()
window.title(u'数据分析')
window.geometry('800x200')
window.resizable(0, 0)

l = tk.Label(window,text=u'输入DrawCall值取值区间：',font=('Arial',12),width=30,  height=2)
l.grid(row=0, column=0)
data_01 = StringVar()
e1 = tk.Entry(window,width = 50,textvariable=data_01)
e1.grid(row=0, column=1)


l1 = tk.Label(window,text=u'            输入面数取值区间：',font=('Arial',12),width=30,  height=2)
l1.grid(row=1, column=0)
data_02 = StringVar()
e2 = tk.Entry(window,width = 50,textvariable=data_02)
e2.grid(row=1, column=1)


l1 = tk.Label(window,text=u'    输入粒子数量取值区间：',font=('Arial',12),width=30,  height=2)
l1.grid(row=2, column=0)
data_03 = StringVar()
e3 = tk.Entry(window,width = 50,textvariable=data_03)
e3.grid(row=2, column=1)



def ViewMapping():
    Jian=[]
    #DrawCall数据处理
    arr_num = np.array(DrawCall)
    qujian = e1.get().lstrip()

    qujianLB = qujian.split(",")
    qujianLB.pop()

    qujianLB1 = []
    for i in range(len(qujianLB)):
        qujianLB1.append(int((qujianLB[i].split("-"))[1]))
    Jian.append(len(qujianLB1))

    numbersum = {}
    for i in range(len(qujianLB1)):
        if i < len(qujianLB1) - 1:
            numbersum[i] = (qujianLB1[i], qujianLB1[i + 1])

    numbersumes = []
    numbersumes.append(((arr_num >= 0) & (arr_num <= qujianLB1[0])).sum())
    for i in range(len(qujianLB1) - 1):
        A = ((arr_num >= numbersum[i][0]) & (arr_num <= numbersum[i][1])).sum()
        B = ((arr_num >= numbersum[i][0]) & (arr_num <= numbersum[i][0])).sum()
        numbersumes.append(A - B)

    E = 0
    for i in range(len(numbersumes)):
        E = E + numbersumes[i]
    numbersumes.append(len(DrawCall) - E)

    baifenbi = []
    for i in range(len(numbersumes)):
        baifenbi.append(str(format(float(numbersumes[i]) / float(len(DrawCall)) * 100, '.2f')) + '%')

    qujianLB.append(u'合计')
    numbersumes.append(len(DrawCall))
    baifenbi.append(u'100%')
    qujianLB.pop()
    numbersumes.pop()

    DrawCallData = {}
    DrawCallData['0'] = [u'最多DrawCall数量', u"数量", u'占比']
    for i in range(len(numbersumes)):
        DrawCallData[str(i + 1)] = [ qujian.split(",")[i],numbersumes[i], str(format(float(numbersumes[i]) / float(len(DrawCall)) * 100, '.2f')) + '%']
    DrawCallData[str(len(numbersumes) + 1)] = [u'合计', len(DrawCall), u'100%']


    #Areas数据处理
    arr_numA = np.array(Area)
    qujianA = e2.get().lstrip()
    qujianLBA = qujianA.split(",")
    qujianLBA.pop()

    qujianLB1A = []
    for i in range(len(qujianLBA)):
        qujianLB1A.append(int((qujianLBA[i].split("-"))[1]))
    Jian.append(len(qujianLB1A))

    numbersumA = {}
    for i in range(len(qujianLB1A)):
        if i < len(qujianLB1A) - 1:
            numbersumA[i] = (qujianLB1A[i], qujianLB1A[i + 1])

    numbersumesA = []
    numbersumesA.append(((arr_numA >= 0) & (arr_numA <= qujianLB1A[0])).sum())
    for i in range(len(qujianLB1A) - 1):
        AA = ((arr_numA >= numbersumA[i][0]) & (arr_numA <= numbersumA[i][1])).sum()
        BA = ((arr_numA >= numbersumA[i][0]) & (arr_numA <= numbersumA[i][0])).sum()
        numbersumesA.append(AA - BA)

    EA = 0
    for i in range(len(numbersumesA)):
        EA = EA + numbersumesA[i]
    numbersumesA.append(len(Area) - EA)

    baifenbiA = []
    for i in range(len(numbersumesA)):
        baifenbiA.append(str(format(float(numbersumesA[i]) / float(len(Area)) * 100, '.2f')) + '%')

    qujianLBA.append(u'合计')
    numbersumesA.append(len(Area))
    baifenbiA.append(u'100%')
    qujianLBA.pop()
    numbersumesA.pop()

    AreaData = {}
    AreaData['0'] = [u'面积分布', u"数量", u'占比']
    for i in range(len(numbersumesA)):
        AreaData[str(i + 1)] = [qujianA.split(",")[i],numbersumesA[i], str(format(float(numbersumesA[i]) / float(len(Area)) * 100, '.2f')) + '%']
        AreaData[str(len(numbersumesA) + 1)] = [u'合计', len(Area), u'100%']


    #ParticleNumber数据处理
    arr_numB = np.array(ParticleNumber)
    qujianAB = e3.get().lstrip()
    qujianLBAB = qujianAB.split(",")
    qujianLBAB.pop()

    qujianLB1AB = []
    for i in range(len(qujianLBAB)):
        qujianLB1AB.append(int((qujianLBAB[i].split("-"))[1]))
    Jian.append(len(qujianLB1AB))

    numbersumAB = {}
    for i in range(len(qujianLB1AB)):
        if i < len(qujianLB1AB) - 1:
            numbersumAB[i] = (qujianLB1AB[i], qujianLB1AB[i + 1])

    numbersumesAB = []
    numbersumesAB.append(((arr_numB >= 0) & (arr_numB <= qujianLB1AB[0])).sum())
    for i in range(len(qujianLB1AB) - 1):
        AAB = ((arr_numB >= numbersumAB[i][0]) & (arr_numB <= numbersumAB[i][1])).sum()
        BAB = ((arr_numB >= numbersumAB[i][0]) & (arr_numB <= numbersumAB[i][0])).sum()
        numbersumesAB.append(AAB - BAB)

    EAB = 0
    for i in range(len(numbersumesAB)):
        EAB = EAB + numbersumesAB[i]
    numbersumesAB.append(len(ParticleNumber) - EAB)

    baifenbiAB = []
    for i in range(len(numbersumesAB)):
        baifenbiAB.append(str(format(float(numbersumesAB[i]) / float(len(ParticleNumber)) * 100, '.2f')) + '%')

    qujianLBAB.append(u'合计')
    numbersumesAB.append(len(ParticleNumber))
    baifenbiAB.append(u'100%')
    qujianLBAB.pop()
    numbersumesAB.pop()

    ParticleNumberData={}
    ParticleNumberData['0']=[u'粒子数分布', u"数量", u'占比']
    for i in range(len(numbersumesAB)):
        ParticleNumberData[str(i+1)] = [qujianAB .split(",")[i],numbersumesAB[i],str(format(float(numbersumesAB[i]) / float(len(ParticleNumber)) * 100, '.2f')) + '%']
    ParticleNumberData[str(len(numbersumesAB)+1)] = [u'合计',len(ParticleNumber),u'100%']


    filename=datetime.now().strftime('%Y-%m-%d %H:%M:%S').split()[0]+datetime.now().strftime('%Y-%m-%d %H:%M:%S').split()[1].split(":")[0]+datetime.now().strftime('%Y-%m-%d %H:%M:%S').split()[1].split(":")[1]+datetime.now().strftime('%Y-%m-%d %H:%M:%S').split()[1].split(":")[2]+'autodata'+'.xlsx'
    book = xlsxwriter.Workbook(filename)
    sheet = book.add_worksheet()

    ldata = []
    num1 = [a for a in DrawCallData]
    num = map(int, num1)
    num.sort()

    for x in num:
        t = [int(x)]
        for a in DrawCallData[str(x)]:
            t.append(a)
        ldata.append(t)
    for i, p in enumerate(ldata):
        for j, q in enumerate(p):
            sheet.write(i, j, q)
    for i in  range(Jian[0]+3):
        sheet.write(i,0,' ')

    # 创建一个图表，类型是column
    chart1 = book.add_chart({'type': 'column'})
    chart1.add_series({
        'name': '=Sheet1!$B$1',
        'categories': ('=Sheet1!$B$2:$B$' + str(len(num)-1)),
        'values': ('=Sheet1!$C$2:$C$' + str(len(num)-1)),
        'points': [
            {'fill': {'color': '#00CD00'}},
            {'fill': {'color': '#008B00'}},
            {'fill': {'color': '#1E90FF'}},
            {'fill': {'color': '#0000FF'}},
            {'fill': {'color': '#D15FEE'}},
            {'fill': {'color': '#EE30A7'}},
            {'fill': {'color': '#EE00EE'}},
            {'fill': {'color': '#C71585'}},
            {'fill': {'color': '#FF4500'}},
            {'fill': {'color': '#FF0000'}}
        ],
    })
    # 添加图表标题和标签
    chart1.set_title({'name': u'最多DrawCall数量分布'})
    chart1.set_y_axis({'name': u'个数之和'})
    # 设置图表风格
    chart1.set_style(3)
    chart1.set_size({'width': 400, 'height': 400})
    # 插入图表
    sheet.insert_chart('A' + str(max(Jian) + 6), chart1)


    ldata = []
    num1 = [a for a in AreaData]
    num = map(int, num1)
    num.sort()

    for x in num:
        t = [int(x)]
        for a in AreaData[str(x)]:
            t.append(a)
        ldata.append(t)
    for i, p in enumerate(ldata):
        for j, q in enumerate(p):
            sheet.write(i, j+9, q)
    for i in range(Jian[1] + 3):
        sheet.write(i, 9, ' ')


     # 创建一个图表，类型是column
    chart1 = book.add_chart({'type': 'column'})

    chart1.add_series({
        'name': '=Sheet1!$B$1',
        'categories': ('=Sheet1!$K$2:$k$' + str(len(num)-1)),
        'values': ('=Sheet1!$L$2:$L$' + str(len(num)-1)),
        'points': [
            {'fill': {'color': '#00CD00'}},
            {'fill': {'color': '#008B00'}},
            {'fill': {'color': '#1E90FF'}},
            {'fill': {'color': '#0000FF'}},
            {'fill': {'color': '#D15FEE'}},
            {'fill': {'color': '#EE30A7'}},
            {'fill': {'color': '#EE00EE'}},
            {'fill': {'color': '#C71585'}},
            {'fill': {'color': '#FF4500'}},
            {'fill': {'color': '#FF0000'}}
        ],
    })
    # 添加图表标题和标签
    chart1.set_title({'name': u'面积分布'})
    chart1.set_y_axis({'name': u'个数之和'})
    # 设置图表风格
    chart1.set_style(3)
    chart1.set_size({'width': 400, 'height': 400})
    # 插入图表（带偏移）
    sheet.insert_chart('I' + str(max(Jian) + 6), chart1)


    ldata = []
    num1 = [a for a in ParticleNumberData]
    num = map(int, num1)
    num.sort()

    for x in num:
        t = [int(x)]
        for a in ParticleNumberData[str(x)]:
            t.append(a)
        ldata.append(t)
    for i, p in enumerate(ldata):
        for j, q in enumerate(p):
            sheet.write(i, j+17, q)
    for i in range(Jian[1] + 3):
        sheet.write(i , 17, ' ')

    # 创建一个图表，类型是column
    chart1 = book.add_chart({'type': 'column'})
    chart1.add_series({
        'name': '=Sheet1!$B$1',
        'categories': ('=Sheet1!$S$2:$S$' + str(len(num)-1)),
        'values': ('=Sheet1!$T$2:$T$' + str(len(num)-1)),
        'points': [
            {'fill': {'color': '#00CD00'}},
            {'fill': {'color': '#008B00'}},
            {'fill': {'color': '#1E90FF'}},
            {'fill': {'color': '#0000FF'}},
            {'fill': {'color': '#D15FEE'}},
            {'fill': {'color': '#EE30A7'}},
            {'fill': {'color': '#EE00EE'}},
            {'fill': {'color': '#C71585'}},
            {'fill': {'color': '#FF4500'}},
            {'fill': {'color': '#FF0000'}}
        ],
    })
    # 添加图表标题和标签
    chart1.set_title({'name': u'粒子数分布'})
    chart1.set_y_axis({'name': u'个数之和'})
    # 设置图表风格
    chart1.set_style(3)
    chart1.set_size({'width': 400, 'height': 400})
    # 插入图表（带偏移）
    sheet.insert_chart('R' + str(max(Jian) + 6), chart1)
    book.close()


b = tk.Button(window,text=u'查看数据总图',width=15,height=2, command = ViewMapping)
b.grid(row=1, column=4)
window.mainloop()   #循环
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Bug来袭～

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值