# 1. 观察表数据发现有些数据需要预处理：
# 1）将成绩表中 男1000米跑，评分表中 男1000米跑成绩，女800米跑成绩：str -> float，才能进行分组计算；
# 2）评分表中 男引体成绩 有空值，需转换为前后两项的平均值；
# 3）所有数值类型的值，都要转换为float类型的值。
# 2. 通过数据分箱计算所有学生体测分数。

#coding=utf8

import math
import numpy as np
import pandas as pd

MAX = 1000000000
MIN = 0

def time2float(x): 
	temp = str(x).split("'")
	res = float(temp[0])
	if len(temp) > 1:
		if temp[1][-1] == '"':
			temp[1] = temp[1][:-1]
		res += float(temp[1]) / 60.0
	return np.round(res, 2)

def format_score(s):
	for i, item in enumerate(s):
		if math.isnan(item):
			s.iat[i] = (s[i - 1] + s[i + 1]) / 2.0
	return s

def calculate_score(df, score_std, start, end):
	columns = df.columns.tolist()
	for item in columns[start:end]:
		#print(i, item)
		if '跑' in item:
			data = [MIN]
			labels = score_std[item]['分数']
			right = True
		else:
			data = [MAX]
			labels = score_std[item]['分数'].sort_values()
			right = False
		result = pd.concat([pd.Series(data = data), score_std[item]['成绩']], axis = 0).sort_values()
		score = pd.cut(df[item], bins = result, labels = labels, right = right)
		df.insert(df.columns.tolist().index(item) + 1, item + '成绩', score)
	df.replace(np.nan, 0, inplace = True)
	print(df.head(n = 10))

if __name__ == '__main__':
	# 数据加载
	score_male = pd.read_excel('./data/18级高一体测成绩汇总.xls')
	score_female = pd.read_excel('./data/18级高一体测成绩汇总.xls', sheet_name = 1)
	score_std = pd.read_excel('./data/体侧成绩评分表.xls',header = [0,1])

	# 数据预处理
	score_male['男1000米跑'] = score_male['男1000米跑'].apply(time2float)
	score_male.iloc[:, 3:] = score_male.iloc[:, 3:].astype(float).round(2)
	score_female.iloc[:, 3:] = score_female.iloc[:, 3:].astype(float).round(2)	
	score_std.loc[:, ('男1000米跑', '成绩')] = score_std['男1000米跑']['成绩'].apply(time2float)
	score_std.loc[:, ('女800米跑', '成绩')] =  score_std['女800米跑']['成绩'].apply(time2float)
	score_std.loc[:, ('男引体', '成绩')] = format_score(score_std['男引体']['成绩'])
	#print(score_std)

	calculate_score(score_male, score_std, 2, -3)
	calculate_score(score_female, score_std, 2, -3)