为了加快数据的处理能力,加快统计排名情况,需要从统计好的excel表中进行相关数据排序,并按要求输出。
fenshu.xlsx
空气质量
污染程度
其他
得分
上海
44.5
8.151949
14.46154
67.11349
北京
34
8.095238
15.15873
57.25397
广州
33
10.23364
13.27103
56.50467
成都
38.5
10.2129
15.94406
64.65695
武汉
42.5
13.26796
18.52273
74.29069
南京
50
7.461452
14.63576
72.09721
重庆
50
11.16099
15.21127
76.37226
南昌
48.5
12.3913
17.82609
78.71739
贵阳
49.5
30
20
99.5
#coding:GBK
import xlrd
import xlwt
def read_excel():
workbook = xlrd.open_workbook(r'D:python casefenshu.xlsx')
# print workbook.sheet_names()
sheet_name = workbook.sheet_names()[1]
sheet = workbook.sheet_by_index(1)
sheet = workbook.sheet_by_name('Sheet1')
lie = {}
print "各项排名!"
print '-'*20
print sheet.cell(0,1).value.encode('GBK')+":"
for i in range(1,sheet.nrows):
lie[sheet.cell(i,0).value.encode('GBK')] = sheet.cell(i,1).value
i = i+1
newlie = sorted(lie.iteritems(),key = lambda d:d[1],reverse = True)
for i in range(0,len(newlie)):
print newlie[i][0]+":"+str(newlie[i][1])+"分," ,
i = i+1
print "n"
print sheet.cell(0,2).value.encode('GBK')+":"
for i in range(1,sheet.nrows):
lie[sheet.cell(i,0).value.encode('GBK')] = sheet.cell(i,2).value
i = i+1
newlie = sorted(lie.iteritems(),key = lambda d:d[1],reverse = True)
for i in range(0,len(newlie)):
print newlie[i][0]+":"+"%.1f" %(newlie[i][1])+"分," ,
i = i+1
print "n"
print sheet.cell(0,3).value.encode('GBK')+":"
for i in range(1,sheet.nrows):
lie[sheet.cell(i,0).value.encode('GBK')] = sheet.cell(i,3).value
i = i+1
newlie = sorted(lie.iteritems(),key = lambda d:d[1],reverse = True)
for i in range(0,len(newlie)):
print newlie[i][0]+":"+"%.1f" %(newlie[i][1])+"分," ,
i = i+1
print "n"
print sheet.cell(0,4).value.encode('GBK')+":"
for i in range(1,sheet.nrows):
lie[sheet.cell(i,0).value.encode('GBK')] = sheet.cell(i,4).value
i = i+1
newlie = sorted(lie.iteritems(),key = lambda d:d[1],reverse = True)
for i in range(0,len(newlie)):
print newlie[i][0]+":"+"%.1f" %(newlie[i][1])+"分," ,
i = i+1
if __name__ == '__main__':
read_excel()
输出:
>>> ================================ RESTART ================================
>>>
各项排名!
--------------------
空气质量:
重庆:50.0分, 南京:50.0分, 贵阳:49.5分, 南昌:48.5分, 上海:44.5分, 武汉:42.5分, 成都:38.5分, 北京:34.0分, 广州:33.0分,
污染程度:
贵阳:30.0分, 武汉:13.3分, 南昌:12.4分, 重庆:11.2分, 广州:10.2分, 成都:10.2分, 上海:8.2分, 北京:8.1分, 南京:7.5分,
其他:
贵阳:20.0分, 武汉:18.5分, 南昌:17.8分, 成都:15.9分, 重庆:15.2分, 北京:15.2分, 南京:14.6分, 上海:14.5分, 广州:13.3分,
得分:
贵阳:99.5分, 南昌:78.7分, 重庆:76.4分, 武汉:74.3分, 南京:72.1分, 上海:67.1分, 成都:64.7分, 北京:57.3分, 广州:56.5分,