007

原始文件为 Python 代码，本文是转换后的 Markdown 文件。

import xlrd
import json
from numpy import *

SRC_FILE = 'data/country_stable.xlsx'
DES_FILE = 'data/country_stable_rank.json'

xlsx = xlrd.open_workbook(SRC_FILE)

# # 查看所有 sheet 列表
# print('All sheets: %s' % xlsx.sheet_names())

# 获得第一张 sheet
sheet1 = xlsx.sheets()[0]    
# 获取表的名称
# sheet1_name = sheet1.name    
# 获取表的行数和列数
max_row = sheet1.nrows
max_col = sheet1.ncols  

# 获取某个单元格数据，左上角为(0,0)
# cell23 = sheet1.row(2)[3].value 

inner_average = {}
outer_average = {}
for i in range(3, max_row):
    # 获取第 i 行数据，列数据同理，换成 col 即可
    row_value = sheet1.row_values(i)
    inner_average[ row_value[0] ] = mean(row_value[1:13])
    outer_average[ row_value[0] ] = mean(row_value[13:16])

# 排序后得到结果
inner_average =  sorted( inner_average.items(), key = lambda kv:(kv[1]) ) 
outer_average =  sorted( outer_average.items(), key = lambda kv:(kv[1]), reverse=True ) 

# 之后都是为了写入文件，把两个列表数据合并起来
result = {}
current_value = current_index = 0
for i, val in enumerate(inner_average):
    result[val[0]] = {}
    if current_value == val[1]:
        result[val[0]]['inner_rank'] = current_index
    else:
        result[val[0]]['inner_rank'] = i + 1
        current_index = i + 1
        current_value = val[1]

    result[val[0]]['inner_value'] = val[1]

current_value = current_index = 0
for i, val in enumerate(outer_average):
    if current_value == val[1]:
        result[val[0]]['outer_rank'] = current_index
    else:
        result[val[0]]['outer_rank'] = i + 1
        current_index = i + 1
        current_value = val[1]

    result[val[0]]['outer_value'] = val[1]

# with open('inner_average.txt', 'w') as f:
#     # f.write('国家内部抗毁性\n')
#     for kv in inner_average:
#         f.write(kv[0] + ' ' + str(kv[1]) + '\n')

# with open('outer_average.txt', 'w') as f:
#     # f.write('国家外部抗毁性\n')
#     for kv in outer_average:
#         f.write(kv[0] + ' ' + str(kv[1]) + '\n')

for key in result:
    result[key]['country'] = key

with open(DES_FILE, 'w') as f:
    json.dump(obj=list(result.values()), fp=f, indent=2)