흩어진 수십 개의 엑셀 파일, 클릭 한 번에 하나의 시트로 병합하기

월말이나 연말이 되면 각 부서나 지점에서 취합해 보내온 수십, 수백 개의 엑셀 파일을 하나의 마스터 파일로 합치는 이른바 '취합 업무'가 시작됩니다. 파일 하나를 열고, 드래그해서 복사하고, 마스터 파일 맨 밑줄을 찾아 붙여넣고, 다시 창을 닫고... 이 짓을 50번쯤 반복하다 보면 손목도 저릿하고, 중간에 실수로 누군가의 데이터를 빼먹거나 덮어쓰는 끔찍한 대참사가 발생하기도 하죠. 취합 업무는 직장인의 에너지를 가장 무의미하게 갉아먹는 최악의 단순 반복 작업입니다.

하지만 파이썬의 세계에서는 폴더 안에 있는 100개의 엑셀 파일을 단 3초 만에 하나의 완벽한 시트로 합쳐버릴 수 있습니다. 내가 마우스를 100번 클릭해야 할 일을 컴퓨터에게 대신 시키는 것, 이것이 진짜 코딩을 배우는 목적입니다. 오늘은 파이썬 엑셀 자동화의 꽃이라고 불리는 '다중 파일 병합'의 3단계 핵심 원리를 아주 쉽게 풀어드리겠습니다.

1. 흩어진 파일 이름 싹쓸이하기: glob 라이브러리

가장 먼저 해야 할 일은 컴퓨터에게 "특정 폴더 안에 있는 엑셀 파일들의 이름과 위치를 전부 다 조사해 와!"라고 명령하는 것입니다. 이때 파이썬에 기본적으로 내장된 glob이라는 아주 편리한 도구를 사용합니다.

코드 맨 윗줄에 import glob이라고 적어 도구를 꺼냅니다. 그리고 glob.glob(r'C:\취합폴더\*.xlsx')라고 적어주면 마법이 시작됩니다. 여기서 별표(*)는 "이름이 뭐든 상관없이"라는 뜻을 가진 만능 치트키입니다. 즉, 저 코드는 "취합폴더 안에 있는 파일 중 끝이 .xlsx로 끝나는 모든 파일의 명단을 가져와라"라는 뜻이 됩니다. 이 명단을 파이썬은 리스트(List)라는 이름의 깔끔한 목록표로 정리해서 우리가 쓰기 좋게 쥐여줍니다.

2. 수십 개의 파일을 알아서 열고 복사하기: for 반복문의 힘

명단을 확보했으니 이제 파일들을 하나씩 열어서 데이터를 복사해 올 차례입니다. 50개의 파일을 열기 위해 코드를 50줄 적는다면 자동화가 아니겠죠? 이때 사용하는 것이 파이썬의 핵심 문법인 for 반복문입니다.

빈 상자(빈 리스트)를 하나 만들어 두고, for 문을 사용해 "명단에 있는 파일을 하나씩 순서대로 꺼내서 판다스(pd.read_excel)로 읽은 다음, 아까 만든 빈 상자에 차곡차곡 던져 넣어라"라고 명령을 짭니다. 코드가 실행되는 순간, 컴퓨터는 인간이 눈으로 쫓아갈 수 없는 속도로 수십 개의 파일을 열고 데이터를 긁어와 하나의 거대한 상자 안에 모두 모아둡니다. 사람이 했다면 한 시간 내내 모니터를 노려보며 마우스 휠을 굴려야 했을 작업이 단 1초 만에 끝나는 순간입니다.

3. 레고 블록처럼 하나의 시트로 합치기: pd.concat()

이제 상자 안에 수십 개의 엑셀 데이터 덩어리들이 모였습니다. 마지막으로 할 일은 이 덩어리들을 위아래로 길게 이어 붙이는 것입니다. 이때 판다스의 pd.concat() 함수를 사용합니다. 'concat'은 연결하다(concatenate)라는 뜻의 영어 단어에서 따온 강력한 접착제입니다.

pd.concat(데이터상자)라고만 쳐도 데이터들이 레고 블록 조립되듯 위아래로 찰칵찰칵 달라붙습니다. 여기서 실무자들이 가장 많이 놓치는 아주 중요한 팁이 하나 있습니다. 괄호 안에 반드시 ignore_index=True라는 옵션을 적어주셔야 합니다. 만약 이 옵션을 빼먹으면 1번 파일의 1~10번 데이터, 2번 파일의 1~15번 데이터가 합쳐졌을 때, 번호표가 1,2..10,1,2..15 처럼 뒤죽박죽 섞이게 됩니다. ignore_index=True는 "기존에 있던 번호표는 다 무시하고, 1번부터 끝번까지 새롭게 순서를 쫙 매겨줘"라는 의미입니다. 이 옵션 하나가 여러분의 보고서 퀄리티를 결정합니다.

반복되는 야근의 고리를 끊어내세요

방금 배운 이 3단계(파일 목록 가져오기 -> 반복문으로 읽기 -> concat으로 붙이기) 코드는 한 번만 잘 짜서 저장해 두면 평생 써먹을 수 있는 직장인의 궁극기입니다. 매달 말일, 취합 때문에 야근하던 지난날들과 이제는 영원히 작별하시기 바랍니다.

▶ 핵심 요약

• glob 라이브러리의 * 기호를 활용하면 특정 폴더 안에 있는 수많은 엑셀 파일의 명단을 한 번에 가져올 수 있다.

• for 반복문을 사용해 명단에 있는 파일들을 순식간에 읽어 들여 하나의 리스트 상자 안에 모아둔다.

• 모인 데이터 덩어리들을 pd.concat() 함수로 이어 붙이며, 이때 ignore_index=True 옵션을 써야 행 번호가 꼬이지 않고 깔끔하게 재정렬된다.

위아래로 데이터를 이어 붙이는 법을 알았으니, 이번엔 양옆으로 데이터를 붙여볼 차례입니다. 엑셀의 VLOOKUP 함수를 쓰다가 컴퓨터가 멈춘 경험이 있으시다면 필수 시청! 다음 6편에서는 'VLOOKUP보다 빠르고 정확한 파이썬 merge() 함수 활용 실전 사례'에 대해 다루겠습니다.

직장 생활을 하시면서 가장 많은 개수의 파일을 취합해 본 경험은 언제였나요? 수동으로 취합하면서 겪었던 가장 억울하거나 힘들었던 에피소드가 있다면 편하게 남겨주세요!