[Python] 엑셀 파일 간 데이터 비교 (여러 시트일 때)

엑셀 파일 자동화: 파이썬으로 여러 시트 간 데이터 비교 및 필터링

엑셀 작업을 자동화하려는 경우, 특히 여러 파일과 시트를 다루며 특정 조건에 따라 데이터를 필터링해야 할 때, 파이썬을 활용하면 효율적이고 반복 가능한 작업을 수행할 수 있습니다. 이번 글에서는 다음과 같은 상황을 가정하고, 이를 해결하기 위한 파이썬 코드를 단계별로 설명합니다.

상황 설명

• A 파일: 단일 시트로 구성된 엑셀 파일입니다.
• B 파일: 여러 시트로 구성된 엑셀 파일입니다.
• 목표: B 파일의 특정 컬럼 값이 A 파일에 존재하는 경우만 A 파일에 남겨 새로운 엑셀 파일로 저장합니다.

파이썬 코드 설명

필요한 라이브러리

pip install pandas openpyxl

전체 코드

import pandas as pd

# 파일 경로 설정
a_file_path = 'a_file.xlsx'
b_file_path = 'b_file.xlsx'

# A 파일과 B 파일 불러오기
a_df = pd.read_excel(a_file_path)  # A 파일은 단일 시트
b_sheets = pd.ExcelFile(b_file_path).sheet_names  # B 파일의 모든 시트 이름 가져오기

# 비교할 컬럼 이름 설정
a_col_name = 'A_컬럼명'  # A 파일에서 비교할 컬럼 이름
b_col_name = 'B_컬럼명'  # B 파일에서 비교할 컬럼 이름

# B 파일의 모든 시트를 반복하며 특정 컬럼 값 수집
b_col_values = set()
for sheet_name in b_sheets:
    b_df = pd.read_excel(b_file_path, sheet_name=sheet_name)  # B 파일의 각 시트 읽기
    
    if b_col_name in b_df.columns:
        b_col_values.update(b_df[b_col_name].dropna().unique())  # 값 수집
    else:
        print(f"'{sheet_name}' 시트에 '{b_col_name}' 컬럼이 없습니다. 건너뜁니다.")

# A 파일의 특정 컬럼 값이 B 파일의 값에 포함된 경우만 필터링
filtered_a_df = a_df[a_df[a_col_name].isin(b_col_values)]

# 필터링된 A 파일을 새로운 엑셀 파일로 저장
output_file = 'filtered_a_file.xlsx'
filtered_a_df.to_excel(output_file, index=False)

print(f"필터링된 A 파일이 '{output_file}'에 저장되었습니다.")

코드 설명

1. pandas를 사용하여 A 파일과 B 파일을 읽어옵니다.
2. B 파일의 모든 시트를 반복하면서 특정 컬럼 값을 수집합니다.
3. A 파일의 특정 컬럼 값이 B 파일의 값에 포함된 경우만 필터링합니다.
4. 필터링된 결과를 새로운 엑셀 파일로 저장합니다.

포인트

• 중복 제거 및 NaN 제외: dropna().unique()를 사용하여 중복된 값과 NaN 값을 제거한 고유한 값만 집합에 저장합니다.
• 빠른 조회를 위한 집합 사용: B 파일의 특정 컬럼 값을 set에 저장하여 A 파일과의 비교 속도를 향상시켰습니다.
• 반복 가능한 작업 자동화: 여러 시트를 처리해야 할 때 반복문을 사용하여 자동으로 비교 작업을 수행할 수 있습니다.

확장 가능성

이 코드는 엑셀 데이터 비교 및 필터링 작업의 기본 형태로, 상황에 따라 다양한 확장이 가능합니다.

• B 파일의 특정 컬럼 값이 A 파일에 존재하지 않는 경우만 필터링할 수 있습니다.
• 결과를 하나의 시트에 통합하여 저장하거나, 개별 시트로 저장할 수 있습니다.
• 대용량 데이터 처리 시 메모리 효율성을 고려한 개선이 가능합니다.

결론

엑셀 파일 간 데이터 비교 및 필터링은 수작업으로 하면 시간이 많이 소요되지만, 파이썬의 pandas와 openpyxl 라이브러리를 활용하면 이를 자동화하여 생산성을 높일 수 있습니다.