import os import pandas as pd from openpyxl.styles import Font, Alignment from openpyxl.utils import get_column_letter def collect_combined_scores(evaluated_dir): """ evaluated_dir 내의 모든 combined_scores.xlsx 파일을 검색하여 리스트로 반환합니다. """ combined_scores_files = [] for root, dirs, files in os.walk(evaluated_dir): for file in files: if file == 'combined_scores.xlsx': combined_scores_files.append(os.path.join(root, file)) return combined_scores_files def get_categories_from_file(file_path): """ 주어진 엑셀 파일에서 카테고리(인덱스)를 추출합니다. """ try: df = pd.read_excel(file_path, index_col=0) return df.index.tolist() except Exception as e: print(f"Error reading categories from {file_path}: {e}") return [] def extract_model_name(file_path): """ 파일 경로에서 모델 이름을 추출합니다. 예: /path/to/model1/combined_scores.xlsx -> model1 """ return os.path.basename(os.path.dirname(file_path)) def drop_empty_string_columns(sheet_df): """ 각 컬럼의 문자열 길이를 기준으로 모든 값의 길이가 0인 컬럼을 삭제합니다. """ for column in sheet_df.columns: # 각 셀의 문자열 길이를 측정 (NaN은 무시) string_lengths = sheet_df[column].dropna().astype(str).apply(len) # 모든 값의 문자열 길이가 0인 컬럼 삭제 if string_lengths.sum() == 0: sheet_df.drop(columns=[column], inplace=True) return sheet_df def create_score_sheets(combined_scores_files, categories, score_types): """ 각 스코어 유형별로 데이터프레임을 생성하여 딕셔너리에 저장합니다. """ # 초기화: 각 스코어 유형에 대해 빈 데이터프레임 생성 score_sheets = {score_type: pd.DataFrame(columns=categories) for score_type in score_types} for file in combined_scores_files: model_name = extract_model_name(file) try: df = pd.read_excel(file, index_col=0) # 첫 번째 열을 인덱스로 설정 (카테고리) except Exception as e: print(f"Error reading {file}: {e}") continue for score_type in score_types: if score_type in df.columns: # 시트별 데이터프레임에 모델 이름을 인덱스로 추가하고 스코어를 행으로 추가 # 존재하지 않는 카테고리는 NaN으로 채워짐 score_sheets[score_type].loc[model_name] = df.loc[categories, score_type] else: print(f"Warning: '{score_type}' not found in {file}") # 각 시트에서 문자열 길이를 기준으로 빈 컬럼 삭제 및 'Score Average' 열 추가 for score_type, sheet_df in score_sheets.items(): # 1. 문자열 길이 기준으로 빈 컬럼 삭제 drop_empty_string_columns(sheet_df) # 2. 기존 'Score Average' 열이 있을 경우 삭제 if 'Score Average' in sheet_df.columns: sheet_df.drop(columns=['Score Average'], inplace=True) # 3. 각 모델별 평균값 계산 sheet_df['Score Average'] = sheet_df.mean(axis=1, skipna=True) # 데이터프레임을 업데이트 score_sheets[score_type] = sheet_df return score_sheets def save_to_excel(score_sheets, output_file): """ 딕셔너리에 저장된 데이터프레임을 각 시트로 저장하여 엑셀 파일로 저장합니다. """ with pd.ExcelWriter(output_file, engine='openpyxl') as writer: # 새로운 시트를 위한 데이터프레임 초기화 average_scores_df = pd.DataFrame() for sheet_name, df in score_sheets.items(): df.to_excel(writer, sheet_name=sheet_name) # 스타일 적용 workbook = writer.book worksheet = writer.sheets[sheet_name] # 헤더 스타일 header_font = Font(bold=True) for col_num, col in enumerate(df.columns, 1): cell = worksheet.cell(row=1, column=col_num + 1) cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # 인덱스 스타일 for row_num in range(2, len(df) + 2): cell = worksheet.cell(row=row_num, column=1) cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # 열 너비 자동 조정 for col_num, col in enumerate(df.columns, 1): max_length = max(df[col].astype(str).map(len).max(), len(col)) + 2 worksheet.column_dimensions[get_column_letter(col_num + 1)].width = max_length # 인덱스 열 너비 조정 max_length = max(df.index.astype(str).map(len).max(), len(df.index.name) if df.index.name else 0) + 2 worksheet.column_dimensions[get_column_letter(1)].width = max_length # 새로운 시트를 위한 평균 계산 if 'Score Average' in df.columns: average_scores_df[sheet_name] = df['Score Average'] # 모델별 평균 계산 후 새로운 시트에 저장 if not average_scores_df.empty: average_scores_df['Overall Average'] = average_scores_df.mean(axis=1, skipna=True) average_scores_df.to_excel(writer, sheet_name='Model Average Scores') # 스타일 적용 avg_worksheet = writer.sheets['Model Average Scores'] # 헤더 스타일 for col_num, col in enumerate(average_scores_df.columns, 1): cell = avg_worksheet.cell(row=1, column=col_num + 1) cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # 인덱스 스타일 for row_num in range(2, len(average_scores_df) + 2): cell = avg_worksheet.cell(row=row_num, column=1) cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') # 열 너비 자동 조정 for col_num, col in enumerate(average_scores_df.columns, 1): max_length = max(average_scores_df[col].astype(str).map(len).max(), len(col)) + 2 avg_worksheet.column_dimensions[get_column_letter(col_num + 1)].width = max_length # 인덱스 열 너비 조정 max_length = max(average_scores_df.index.astype(str).map(len).max(), len(average_scores_df.index.name) if average_scores_df.index.name else 0) + 2 avg_worksheet.column_dimensions[get_column_letter(1)].width = max_length print(f"모든 스코어가 '{output_file}' 파일에 저장되었습니다.") def main(): evaluated_dir = 'evaluated' # 'evaluated' 디렉토리 경로 output_file = 'merged_scores.xlsx' # 출력 엑셀 파일 이름 # 1. 'evaluated' 디렉토리 내 모든 combined_scores.xlsx 파일 검색 combined_scores_files = collect_combined_scores(evaluated_dir) if not combined_scores_files: print("No 'combined_scores.xlsx' files found in the 'evaluated' directory.") return print(f"Found {len(combined_scores_files)} 'combined_scores.xlsx' files.") # 2. 첫 번째 파일에서 카테고리 추출 categories = get_categories_from_file(combined_scores_files[0]) if not categories: print("Failed to extract categories from the first file.") return # 3. 스코어 유형 정의 score_types = [ 'cot_1_shot_single_score', 'cot_1_shot_multi_score', '1_shot_single_score', '1_shot_multi_score', 'default_single_score', 'default_multi_score', 'lotte_single_turn' ] # 4. 데이터 수집 및 시트 생성 score_sheets = create_score_sheets(combined_scores_files, categories, score_types) # 5. 엑셀 파일로 저장 save_to_excel(score_sheets, output_file) if __name__ == "__main__": main()