Profile picture

[Python] 엑셀 데이터를 키워드로 필터링 및 취합하는 방법

JaehyoJJAng2024년 08월 02일

1. 들어가며

프로젝트를 하다보면 여러 개의 엑셀 파일에서 필요한 데이터를 추출하고, 그 데이터를 하나의 파일로 정리해야 하는 경우가 종종 있다.

특히, 특정 키워드를 기준으로 데이터를 필터링하는 경우에는 수작업으로 하기에는 매우 번거롭다.

이런 작업을 자동화할 수 있도록 파이썬을 사용하여 엑셀 파일에서 데이터를 필터링하고, 취합하는 방법을 기록해보려고 한다.


2. 데이터 구조

각 폴더에는 2020년부터 2024년까지의 엑셀 파일들이 존재한다.

├── 디지털 전환
│   └── 2020.xlsx
├── 순환경제
│   ├── 2020.xlsx
│   ├── 2021.xlsx
│   ├── 2022.xlsx
│   ├── 2023.xlsx
│   └── 2024.xlsx
└── 인구구조 변화
    ├── 2020.xlsx
    ├── 2021.xlsx
    ├── 2022.xlsx
    ├── 2023.xlsx
    └── 2024.xlsx

그리고 각 엑셀 파일에는 아래와 같은 열들이 포함되어 있다.

  • 카테고리
  • 제목
  • 발행기관
  • 출판년도
  • 분류
  • 요약

나는 각 엑셀 파일에서 "보고서명"과 "요약"에 특정 키워드가 포함된 데이터를 필터링해 취합하려고 한다.


3. 데이터 처리

필터링 작업은 pandas 라이브러리를 활용하여 진행하며, openpyxl을 사용해 엑셀 파일을 생성한다.

각 주제별로 설정된 키워드를 기반으로 데이터를 추출한 후, 엑셀 파일을 작성하는 방법을 단계별로 살펴보자.


3-1. 필요한 라이브러리 설치

먼저 파이썬에서 엑셀 데이터를 다루기 위해 필요한 라이브러리를 설치해야 한다.

pip install pandas openpyxl

3-2. 코드 작성

아래의 코드는 각 폴더 안에 있는 엑셀 파일을 읽어와, 주제에 맞는 키워드로 데이터를 필터링한 후 하나의 엑셀 파일로 취합하는 전체 과정이다.

import os
import pandas as pd
from openpyxl import Workbook

# 1. 파일 경로 설정
folder_paths = {
    '순환 경제': './순환 경제',
    '디지털 전환': './디지털 전환',
    '인구구조 변화': './인구구조 변화'
}

# 필터링 키워드 설정 (여러 키워드 추가)
keywords = {
    '순환 경제': ['순환', '경제', '순환 경제'],
    '디지털 전환': ['디지털', '전환', '디지털 전환'],
    '인구구조 변화': ['인구', '구조', '변화', '인구구조', '인구구조 변화']
}

# 새로운 엑셀 파일 생성
output_file = '필터링_데이터_취합.xlsx'
wb = Workbook()

for sheet_name, folder_path in folder_paths.items():
    # 2. 시트 생성
    ws = wb.create_sheet(title=sheet_name)

    # 엑셀 파일 취합용 DataFrame
    combined_df = pd.DataFrame()

    # 해당 폴더의 모든 엑셀 파일 읽기
    for file in os.listdir(folder_path):
        if file.endswith('.xlsx'):
            file_path = os.path.join(folder_path, file)
            
            # 3. 엑셀 파일 읽기
            df = pd.read_excel(file_path)

            # 4. 보고서명과 초록(요약)에서 다중 키워드로 필터링
            keyword_conditions = False
            for keyword in keywords[sheet_name]:
                keyword_conditions |= df['제목'].str.contains(keyword, na=False) | df['요약'].str.contains(keyword, na=False)
            filtered_df = df[keyword_conditions]

            # 5. 필터된 데이터 취합
            combined_df = pd.concat([combined_df, filtered_df])

    # 6. 시트 첫 번째 행에 헤더 추가
    headers = ['카테고리', '보고서명', '발행기관', '출판년도', '표준분류', '초록(요약)']
    ws.append(headers)

    # 7. 취합된 데이터를 시트에 기록
    for r_idx, row in combined_df.iterrows():
        ws.append(row.values.tolist())

# 8. 엑셀 파일 저장
wb.save(output_file)

3.3 코드 설명

전체적인 흐름

  • 폴더 경로 설정: 각 주제에 맞는 폴더를 설정해 폴더 내 엑셀 파일을 읽어올 준비를 한다.
  • 키워드 설정: '순환 경제', '디지털 전환', '인구구조 변화' 각각의 주제에 대해 관련 키워드를 설정한다.
  • 데이터 필터링: '제목'과 '요약'에서 설정된 키워드가 포함된 데이터를 필터링한다.
  • 엑셀 파일 생성: 필터링된 데이터를 주제별로 새로운 엑셀 파일의 각 시트에 기록하고, 마지막에 파일로 저장한다.

코드 설명

for keyword in keywords[sheet_name]:
  • keywords[sheet_name]는 주제에 해당하는 키워드 리스트를 참조함.
    • 예를 들어, sheet_name이 "순환 경제"일 때, keywords['순환 경제']는 ['순환', '경제', '순환 경제']와 같은 리스트가 된다.

df['제목'].str.contains(keyword, na=False)
  • df['보고서명']은 pandas DataFrame에서 '보고서명' 열을 의미함.
  • .str.contains(keyword, na=False)는 '제목'의 각 값에서 keyword가 포함되어 있는지 여부를 확인함.
    • 예를 들어, keyword가 '순환'일 때, 제목 중 '순환'이 포함된 행들은 True로 반환됨.
    • na=False는 결측치(NaN)가 있을 때 오류를 발생시키지 않고 False로 처리하도록 하는 설정임.

df['제목'].str.contains(keyword, na=False) | df['요약'].str.contains(keyword, na=False)
  • | 연산자
    • |는 논리적 OR 연산자 이다. 즉, 제목에 키워드가 포함되거나 요약에 키워드가 포함된 행이 있으면 True로 평가된다.
    • 이 방식으로 제목이나 요약 중 어느 하나라도 키워드가 있으면 그 행을 선택하게 됨.

keyword_conditions |=
  • |= 연산자는 OR 할당 연산자 이다. keyword_conditions 변수에 반복적으로 조건을 추가하면서, 이전 조건과 새로운 조건을 OR 연산한다.
    • 예를 들어 첫 번째 키워드로 '순환'을 필터링한 결과가 True인 행들은 keyword_conditions에 저장된다.
    • 이후 두 번째 키워드로 '경제'를 필터링한 결과를 기존의 keyword_conditions과 OR 연산으로 결합하여 갱신한다.
    • 이렇게 하면 모든 키워드를 조건으로 필터링한 결과가 하나의 변수에 누적된다.
Loading script...