엑셀 7만 라인 분석 도전기

Andrew2025-03-11 09:30

최근에 아내와 ChatGPT 계정을 공유하기 시작했다.

AI에 관심이 1도 없던 아내가 ChatGPT를 왠일일까? 프로젝트 파일을 만들어 사용하라고 알려준 뒤 나중에 들어가서 뭐했는지 봤는데, 아주 평범하게 엑셀 분석을 하고 계시더라. 그래서 잘 되느냐 물어봤더니 원하는 대로 잘 안 된길래 분석 대상 파일을 줘보라고 했다.

$엑셀 7만 라인 분석 도전기 image 1$

아내에겐 토큰이 어쩌고, 청킹이 어쩌고 말하면 싸움이 나니 그냥 파일을 달라고 했다. 그리고 그렇게 삽질은 시작되었다.

1. 구글 스프레드 시트 활용 분석

AppScrpt를 활용하면 구글 스프레드 시트에서도 분석이 가능하다. 실제로 Claude를 활용해 코드도 짰지만 실제 활용은 하지 않았다. 왜냐하면,

구글 스프레드 시트에서 7만 라인을 입력하면 굉장히 무거워진다.
7만 라인을 한번에 입력으로 넣을 수 없기에 분할 처리해야 하는데 번거롭다.

2. Claude 제안 코드 활용 1차

대화 내용을 분석하는 코드이기에 Claude API를 활용하는 코드를 제안해 달라고 했다.

엑셀에서 A열에는 대화가 있어. 이 대화를 읽고, (특정) 내용을 찾아서 Clude API나 ChatGPT API를 활용해 해당 내용을 파란색으로 강조 표시할 수 있을가? 셀이 7만 행이 넘기 때문에 대규모 정보 처리를 해야 돼.

import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill, Font
import anthropic
import time
import os
from tqdm import tqdm

# 설정
ANTHROPIC_API_KEY = "YOUR_ANTHROPIC_API_KEY"  # Claude API 키를 입력하세요
INPUT_FILE = "XXXXX 대화.xlsx"  # 분석할 엑셀 파일명
OUTPUT_FILE = "XXXXX 대화_분석결과.xlsx"  # 결과를 저장할 파일명
BATCH_SIZE = 10  # 한 번에 처리할 행 수 (API 비용 최적화)
SLEEP_TIME = 0.5  # API 요청 간 대기 시간 (초)
# CONVERSATION_COLUMN = ""  # 대화 내용이 있는 열 이름 (비워두면 첫 번째 열 사용)

# API 클라이언트 초기화
client = anthropic.Anthropic(api_key=ANTHROPIC_API_KEY)

def analyze_conversation(text):
    """
    텍스트에서 XXXXX 관련 내용을 찾는 함수
    반환값: (관련 내용이 있는지 여부, 강조할 텍스트 목록)
    """
    if pd.isna(text) or not isinstance(text, str):
        return False, []
        
    try:
        response = client.messages.create(
            model="claude-3-sonnet-20240229",
            max_tokens=1000,
            temperature=0,
            system="당신은 텍스트 분석 전문가입니다. 주어진 텍스트에서 'XXXXX'는 내용이 있는지 분석하고, 정확히 해당되는 부분만 추출해주세요. JSON 형식으로 다음과 같이 응답하세요: {\"contains_logistics_margin\": true|false, \"relevant_texts\": [\"관련 텍스트1\", \"관련 텍스트2\"]}",
            messages=[
                {"role": "user", "content": f"다음 텍스트를 분석해주세요: {text}"}
            ]
        )
        
        # API 응답에서 JSON 추출 (실제 구현 시 JSON 파싱 로직 필요)
        import json
        import re
        
        content = response.content[0].text
        # JSON 부분만 추출
        json_match = re.search(r'\{.*\}', content, re.DOTALL)
        if json_match:
            result = json.loads(json_match.group(0))
            return result.get("contains_logistics_margin", False), result.get("relevant_texts", [])
        
        return False, []
    except Exception as e:
        print(f"API 오류: {e}")
        return False, []

def highlight_text_in_excel():
    """
    엑셀 파일을 읽고 XXXXX 관련 내용을 찾아 강조 표시하는 함수
    """
    print(f"파일 '{INPUT_FILE}'을 로딩 중...")
    try:
        # 엑셀 파일 로드
        df = pd.read_excel(INPUT_FILE)
        
        # 데이터 확인
        print(f"파일 열 목록: {df.columns.tolist()}")
        
        # 대화 내용이 있는 열 찾기
        # 첫 번째 열을 사용하거나 사용자가 지정할 수 있음
        conversation_col = df.columns[0]  # 첫 번째 열 사용
        print(f"대화 내용 열로 '{conversation_col}' 사용")
        
        # 결과를 저장할 새 열 추가
        df['XXXXX_언급여부'] = False
        df['XXXXX_관련내용'] = ''
        
        total_rows = len(df)
        print(f"총 {total_rows}개 행을 분석합니다...")
        
        # 배치 단위로 처리
        for i in tqdm(range(0, total_rows, BATCH_SIZE)):
            batch = df.iloc[i:i+BATCH_SIZE]
            
            for idx, row in batch.iterrows():
                text = row[conversation_col]  # 식별된 대화 내용 열 사용
                if pd.isna(text):
                    continue
                    
                contains_margin, relevant_texts = analyze_conversation(text)
                
                # 분석 결과 저장
                df.at[idx, 'XXXXX_언급여부'] = contains_margin
                if relevant_texts:
                    df.at[idx, 'XXXXX_관련내용'] = ' | '.join(relevant_texts)
                
            # API 요청 제한을 위한 대기
            time.sleep(SLEEP_TIME)
        
        # 예외 처리를 위한 디버깅 정보 출력
        print(f"DataFrame에서 처리 완료된 행 수: {len(df)}")
    
        # 분석 결과 저장
        df.to_excel(OUTPUT_FILE, index=False)
        print(f"분석 결과가 '{OUTPUT_FILE}'에 저장되었습니다.")
        
        # 강조 표시 적용
        apply_highlighting(OUTPUT_FILE)
        
    except Exception as e:
        import traceback
        print(f"오류 발생: {e}")
        print(f"상세 오류 정보: {traceback.format_exc()}")

def apply_highlighting(file_path):
    """
    엑셀 파일에서 관련 내용을 파란색으로 강조 표시하는 함수
    """
    print("강조 표시 적용 중...")
    
    try:
        # openpyxl로 파일 열기
        wb = openpyxl.load_workbook(file_path)
        ws = wb.active
        
        # 파란색 배경 스타일 정의
        blue_fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid')
        blue_font = Font(color='0000FF')
        
        # XXXXX_언급여부 열 인덱스 찾기
        header_row = 1
        headers = [cell.value for cell in ws[header_row]]
        print(f"헤더 목록: {headers}")
        
        try:
            mention_col_idx = headers.index('XXXXX_언급여부') + 1
            content_col_idx = headers.index('XXXXX_관련내용') + 1
            conversation_col_idx = 1  # 첫 번째 열 (대화 내용)
            
            # 각 행 처리
            for row_idx, row in enumerate(ws.iter_rows(min_row=2), start=2):
                if row[mention_col_idx-1].value == True:
                    # 대화 내용 열에 파란색 글씨 적용
                    row[conversation_col_idx-1].font = blue_font
                    
                    # 관련 내용이 있는 경우 배경색 강조
                    content_cell = row[content_col_idx-1]
                    if content_cell.value:
                        content_cell.fill = blue_fill
        except ValueError as e:
            print(f"열 인덱스 찾기 오류: {e} - 열 이름이 정확한지 확인하세요.")
    except Exception as e:
        import traceback
        print(f"강조 표시 적용 중 오류 발생: {e}")
        print(f"상세 오류 정보: {traceback.format_exc()}")
    
    # 변경사항 저장
    wb.save(file_path)
    print(f"강조 표시가 적용된 파일이 저장되었습니다: {file_path}")

if __name__ == "__main__":
    print("XXXXX 대화 분석 시작...")
    highlight_text_in_excel()
    print("분석 완료!")

결론적으로 이 시도는 절반의 성공을 거두었다. 코드는 잘 실행되지만, 40시간쯤 걸리더라.