【Python学习】用Python处理计算城市的排行榜

计算城市排行版

 表格附件

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# 读Excel表格
import xlrd
book = xlrd.open_workbook(r'E:\109-业余学习内容\13-python学习\vscdoe学习\函数\city_data.xls') # 双击打开Excel文件(要用绝对路径？)
sheet = book.sheet_by_index(0) # 点击打开需要编辑的数据sheet

main_data_list = []

for row in range(3,sheet.nrows):
    temp_dict={} # 临时的字典
    # print(sheet.row_values(row)) #读取整行的数据

    temp_dict['城市']=sheet.row_values(row)[0]
    temp_dict['2019GDP']=sheet.row_values(row)[1]
    temp_dict['2018总人口']=sheet.row_values(row)[2]
    temp_dict['2018平均房价']=sheet.row_values(row)[3]
    temp_dict['2018平均工资']=sheet.row_values(row)[4]
    temp_dict['地铁总里程数']=sheet.row_values(row)[5]
    temp_dict['高校数量']=sheet.row_values(row)[6]
    temp_dict['三甲医院数量']=sheet.row_values(row)[7]
    temp_dict['2018小学生数量']=sheet.row_values(row)[8]

    main_data_list.append(temp_dict)
# print(main_data_list)

len(main_data_list)
# print(len(main_data_list))

# 添加原榜单得分的字段
for i_list in range(len(main_data_list)):
    main_data_list[i_list]['原榜单得分']=len(main_data_list)-i_list  # 得分刚好等于 列表长度-下标

# print('城市原榜单得分：(由高到低)')
# for j_list in range(len(main_data_list)):
#     print(main_data_list[j_list]['城市'],main_data_list[j_list]['原榜单得分'])
# print('_'* 60)
# print(main_data_list)

#  计算每个城市的收入房价比，精确到小数点2位，添加新的字段
for i in range(len(main_data_list)):
    # format的直接使用和占位的使用？
    main_data_list[i]['收入房价比'] = format(main_data_list[i]['2018平均工资']/12/main_data_list[i]['2018平均房价'],'.2f')

# 更新数据
print(main_data_list)

# 开始逻辑判断：得分

# 定义从字典中根据‘k’取‘k’对应的值
def get_values(dic,k):
    return dic.get(k)

# 做一个高复用的判断函数，支持升序和降序
def desc_cities(cites_list,kw='2019GDP'):
    '''
    cites_list:用于排序的表格
    kw：判断取值所用的关键字
    '''
    if kw!='2018平均房价':
        #---------------------先排序-----------------------#
        cities_by_key=sorted(cites_list,key=lambda x :get_values(x,kw),reverse=True)

        #---------------------再打分-----------------------#
        for i_key in range(len(cities_by_key)):
            cities_by_key[i_key][kw+'得分']= len(cities_by_key)-i_key
        
        # 打印每个城市所选key的得分
        print(f'城市{kw}得分（由高到低）：') # 这个提示信息是什么意思？占位符的使用？
        for j_key in range(len(cities_by_key)):
            print(cities_by_key[j_key]['城市'],cities_by_key[j_key][kw+'得分'])
        print('-'*80)
    else:  # 2018房价评分
        cities_by_key=sorted(cites_list,key=lambda x:get_values(x,kw))
        for i_house in range(len(cities_by_key)):
            cities_by_key[i_house][kw+'得分']=i_house + 1
        
        # 打印每个城市"2018平均房价"的得分
        print(f'城市{kw}得分（由低到高）：') # 这个提示信息是什么意思？占位符的使用？
        for j_house in range(len(cities_by_key)):
            print(cities_by_key[j_house]['城市'],cities_by_key[j_house][kw+'得分'])
        print('-'*80)

    return cities_by_key   

# ---批量调用函数-----#
desc_cities(main_data_list,'2019GDP')
desc_cities(main_data_list,'2018总人口')
desc_cities(main_data_list,'2018平均房价')
desc_cities(main_data_list,'2018平均工资')
desc_cities(main_data_list,'地铁总里程数')
desc_cities(main_data_list,'高校数量')
desc_cities(main_data_list,'三甲医院数量')
cities_by_key = desc_cities(main_data_list,'2018小学生数量')

# 计算城市总得分
for i_city in range(len(cities_by_key)):
    cities_by_key[i_city]['城市总得分'] = format(cities_by_key[i_city]['2019GDP']+cities_by_key[i_city]['2018总人口']+cities_by_key[i_city]['2018平均房价']+cities_by_key[i_city]['2018平均工资']+cities_by_key[i_city]['地铁总里程数']+cities_by_key[i_city]['高校数量']+cities_by_key[i_city]['三甲医院数量']+cities_by_key[i_city]['2018小学生数量'],'.2f') 

#  打印cities_by_key
print('打印cities_by_key')
print(cities_by_key)
print('-'*80)

# 打印原榜单的得分
print('打印原榜单的得分')
for j_city in range(len(cities_by_key)):
    print(cities_by_key[j_city]['城市'],cities_by_key[j_city]['原榜单得分'])
print('-'*80)

# 城市总得分排序
cities_by_score = sorted(cities_by_key,key=lambda x:get_values(x,'城市总得分'),reverse=True)

# 打印总得分
print('打印总得分')
for g_city in range(len(cities_by_score)):
    print(cities_by_score[g_city]['城市'],cities_by_score[g_city]['城市总得分'])
print('-'*80)