[연구 자동화 #25] 실전 프로젝트: 수천 개의 실험 엑셀 파일 통합 및 이상치 자동 탐지 솔루션

4월 28, 2026

오늘의 프로젝트는 연구실에서 가장 흔하게 발생하는 '엑셀 노가다'를 뿌리 뽑는 것입니다. 매일 생성되는 수십 개의 실험 데이터 파일, 한 달이면 수백 개, 일 년이면 수천 개가 쌓입니다. 이를 하나로 합쳐서 경향성을 보려고 하면 벌써 눈앞이 깜깜해지죠. 단순히 합치는 것을 넘어, 데이터 속에 숨어 분석 결과를 망치는 '이상치(Outlier)'를 자동으로 걸러내는 시스템을 구축해 보겠습니다.

1. 연구자의 고민: "파일이 너무 많아서 엄두가 안 나요"

많은 연구원이 다음과 같은 과정을 반복합니다. 폴더를 열고, 엑셀 파일을 하나씩 열어 데이터를 드래그한 뒤, 마스터 파일에 붙여넣습니다. 이 과정에서 발생하는 오타나 데이터 밀림 현상은 연구의 신뢰도를 떨어뜨립니다. 파이썬을 사용하면 이 과정을 단 몇 초 만에, 실수 없이 끝낼 수 있습니다.

2. 자동 통합 시스템 구축 로직

파일 스캔: 지정된 폴더 내의 모든 엑셀(.xlsx) 파일을 탐색합니다.
데이터 로드: 각 파일을 읽어올 때 파일명(날짜, 실험 조건 등)을 새로운 열로 추가해 출처를 기록합니다.
수직 통합: 읽어온 모든 데이터를 하나의 거대한 데이터프레임으로 병합합니다.
이상치 필터링: 통계적 기법을 활용해 비정상적인 수치를 찾아내고 정제합니다.

3. 실전 코드: 데이터 통합과 출처 기록

import pandas as pd
import os

target_dir = './experiment_data'
all_data = []

for file_name in os.listdir(target_dir):
    if file_name.endswith('.xlsx'):
        file_path = os.path.join(target_dir, file_name)
        
        # 데이터 읽기
        df = pd.read_excel(file_path)
        
        # 파일명에서 날짜나 조건 추출
        df['Source_File'] = file_name
        df['Date'] = file_name.split('_')[0]
        
        all_data.append(df)

# 하나로 합치기
combined_df = pd.concat(all_data, ignore_index=True)

4. 통계적 이상치 감지: Z-score 활용

데이터를 합쳤다면 이제 '가짜 데이터'를 걸러내야 합니다. 여기서는 평균으로부터 표준편차의 3배 이상 떨어진 값을 이상치로 판단하는 Z-score 방식을 적용합니다.

from scipy import stats
import numpy as np

# 'Measurement' 열에서 Z-score 계산
z_scores = np.abs(stats.zscore(combined_df['Measurement']))

# Z-score가 3보다 큰 행(이상치) 제거
cleaned_df = combined_df[z_scores < 3]

print(f"제거된 이상치 개수: {len(combined_df) - len(cleaned_df)}")

5. 기대 효과: 시간 절약 그 이상의 가치

이 시스템을 구축해두면 얻게 되는 이점은 명확합니다. 데이터의 일관성이 확보되어 휴먼 에러가 사라지고, 통계적 근거에 기반해 객관적으로 데이터를 정제할 수 있습니다. 또한 스케줄러와 결합하면 실시간으로 업데이트되는 마스터 파일을 가질 수 있습니다.

기능	사용 도구	기대 결과
파일 자동 검색	os 라이브러리	파일 찾기 수작업 생략
엑셀 데이터 통합	pandas.concat	복사 실수 방지
이상치 제거	scipy.stats	분석 신뢰도 향상

[다음 프로젝트 안내] 26편에서는 이 데이터를 예쁘게 가공해서 웹으로 공유하는 [실전 프로젝트 #3: 스트림릿(Streamlit)을 활용한 논문 데이터 아카이브 제작]을 다룹니다.

여러분의 데이터 정제 노하우는 무엇인가요? 댓글로 자유롭게 들려주세요!

정보설명서