词频统计
统计五个excel数据的姓名,然后出现一次名字加德育分,规则如下:
import pandas as pd
from collections import Counter
file_paths = ['1.xlsx', '2.xlsx', '3.xlsx', '4.xlsx', '5.xlsx']
merged_data = pd.DataFrame(columns=['姓名', '学号'])
for file_path in file_paths:
df = pd.read_excel(file_path)
merged_data = pd.concat([merged_data, df], ignore_index=True)
merged_data.to_excel('6.xlsx', index=False)
df = pd.read_excel('6.xlsx')
name_counts = Counter(df['姓名'])
score_dict = {}
for name, count in name_counts.items():
if count >= 5:
score = 2.5 + 0.5 * (count - 5)
elif count >= 4:
score = 2.5
elif count >= 2:
score = 1.5
else:
score = 0.5
score_dict[name] = score
unique_names = set(df['姓名'])
sorted_scores = sorted([(name, score_dict[name], df[df['姓名'] == name]['学号'].values[0]) for name in unique_names], key=lambda x: x[1])
result = pd.DataFrame(columns=['姓名', '学号', '德育分'])
for i, (name, score, student_id) in enumerate(sorted_scores):
row = {'姓名': name, '学号': student_id, '德育分': score}
result.loc[i] = row
result.to_excel('德育分统计.xlsx', index=False)