가공 끝난 데이터, 파이썬으로 엑셀 시트(Sheet) 여러 개로 쪼개서 저장하기

파이썬으로 수만 줄의 데이터를 불러와 조건에 맞게 필터링하고 빈칸까지 깔끔하게 청소했습니다. 이제 이 완벽한 데이터를 엑셀로 저장해서 팀장님께 보고할 일만 남았습니다. 그런데 팀장님의 지시사항이 하나 더 떨어집니다. "이번 달 전체 실적 데이터, 부서별로 엑셀 탭(시트) 나눠서 하나의 파일로 정리해 주세요."

엑셀만 다룰 줄 알던 시절이라면 어떻게 했을까요? '영업팀'으로 필터링해서 복사하고, 아래쪽의 + 버튼을 눌러 새 시트를 만든 뒤 붙여넣고, 시트 이름을 '영업팀'으로 바꿉니다. 그리고 이 짓을 인사팀, 마케팅팀, 재무팀... 부서 개수만큼 무한 반복합니다. 부서가 20개라면 20번을 반복해야 하죠. 하지만 파이썬에서는 판다스의 숨겨진 무기 하나만 꺼내면 클릭 한 번에 20개의 시트가 알아서 척척 만들어집니다.


1. to_excel()의 치명적 한계

2편에서 우리는 데이터를 엑셀로 내보낼 때 df.to_excel('결과.xlsx') 라는 코드를 쓴다고 배웠습니다. 그렇다면 영업팀 데이터(df_sales)와 인사팀 데이터(df_hr)를 각각 저장하려면 이렇게 쓰면 될까요?

df_sales.to_excel('결과.xlsx', sheet_name='영업팀') df_hr.to_excel('결과.xlsx', sheet_name='인사팀')

이 코드를 실행하고 엑셀 파일을 열어보면, 안타깝게도 '인사팀' 시트 딱 하나만 남아있을 것입니다. to_excel() 함수는 기본적으로 파일을 **'새로 덮어쓰기'**하기 때문입니다. 영업팀 시트를 기껏 만들어놨더니, 그 다음 줄에서 인사팀 데이터가 기존 파일을 싹 밀어버리고 혼자 자리를 차지한 것이죠.


2. 엑셀의 바인더 역할을 하는 마법사: pd.ExcelWriter

여러 개의 시트를 하나의 파일에 차곡차곡 모으려면, 파이썬에게 "이 엑셀 파일 아직 닫지 말고 열어둬! 내가 시트를 계속 추가할 거야!"라고 알려주는 특별한 도구가 필요합니다. 그것이 바로 pd.ExcelWriter 입니다. 일종의 '엑셀 바인더'라고 생각하시면 이해하기 쉽습니다.

사용법은 아주 직관적입니다. 먼저 바인더를 하나 만듭니다. writer = pd.ExcelWriter('부서별_최종실적.xlsx')

그리고 아까 실패했던 코드에서 저장 위치를 파일 이름 대신 이 '바인더(writer)'로 바꿔줍니다. df_sales.to_excel(writer, sheet_name='영업팀') df_hr.to_excel(writer, sheet_name='인사팀')

마지막으로 바인더를 꽉 닫아줍니다. 이 닫아주는 과정이 없으면 파일이 저장되지 않으니 절대 잊으시면 안 됩니다! writer.close()

이렇게 단 4줄의 코드만 적어주면, 하나의 파일 안에 영업팀 시트와 인사팀 시트가 다소곳이 나뉘어 들어간 완벽한 보고서가 탄생합니다.


3. 실무자 필수 팁: index=False의 비밀

파이썬으로 엑셀을 저장할 때 초보자들이 100% 겪는 짜증 나는 현상이 하나 있습니다. 엑셀 파일을 열어보면 A열에 0, 1, 2, 3... 같은 쓸데없는 일련번호가 잔뜩 적혀있는 것입니다. 파이썬이 데이터를 다룰 때 내부적으로 쓰던 행 번호(Index)를 눈치 없이 엑셀까지 그대로 끌고 온 탓입니다.

이 불필요한 번호표를 빼고 깔끔하게 알맹이 데이터만 저장하려면, 코드를 쓸 때 반드시 index=False 라는 마법의 옵션을 껴주어야 합니다.

df_sales.to_excel(writer, sheet_name='영업팀', index=False)

이 옵션은 판다스를 쓰는 실무자라면 숨 쉬듯 자연스럽게 입력해야 하는 아주 중요한 습관입니다.


반복문(for)과 결합하면 진정한 자동화의 완성

만약 우리 회사의 부서가 50개라면 코드를 50줄 써야 할까요? 아닙니다. 5편에서 배웠던 for 반복문과 결합하면 단 5줄의 코드로 50개의 시트를 순식간에 쪼개서 만들 수 있습니다. 엑셀 화면을 띄울 필요도 없이, 엔터 키 한 번이면 수십 개의 탭이 예쁘게 정리된 마스터 파일이 완성됩니다. 오늘 배운 ExcelWriter를 통해 퇴근 시간을 1시간 더 앞당겨 보시기 바랍니다.

▶ 핵심 요약

  • 단순한 to_excel() 함수는 기존 파일을 덮어쓰기 때문에 여러 개의 시트를 저장할 수 없다.

  • pd.ExcelWriter를 사용해 가상의 엑셀 바인더를 열어두면, 하나의 파일에 sheet_name을 지정하여 여러 시트를 차곡차곡 추가할 수 있다.

  • 시트를 추가한 후에는 반드시 writer.close()를 실행해야 파일이 최종적으로 저장된다.

  • 엑셀 저장 시 A열에 불필요한 번호표가 생기는 것을 막으려면 index=False 옵션을 반드시 추가해야 한다.


지금까지는 아주 반듯하고 예쁜 표만 다루었습니다. 하지만 현실의 엑셀 파일은 맨 위에 쓸데없는 결재란이 있거나, 중간에 빈 줄이 섞여 있는 경우가 많죠. 다음 12편에서는 '불필요한 윗줄과 빈 줄을 건너뛰고 알맹이 데이터만 쏙 빼오는 실전 불러오기 기술'에 대해 다루겠습니다.

여러분은 엑셀 파일 하나에 시트(Sheet)를 최대 몇 개까지 쪼개서 만들어 보셨나요? 시트가 너무 많아서 로딩이 엄청나게 오래 걸렸던 경험이 있다면 댓글로 공유해 주세요!

댓글

댓글 쓰기

이 블로그의 인기 게시물

엑셀 셀 병합 데이터, 파이썬으로 불러올 때 엉망으로 깨지는 현상 완벽 해결법

실무에서 다루는 엑셀 보고서들을 보면 십중팔구 '셀 병합'이 되어 있습니다. 부서명, 날짜, 카테고리 등을 보기 좋게 묶어두는 것은 사람의 눈에는 참 친절한 방식입니다. 하지만 파이썬으로 자동화를 시작한 직장인들에게 이 예쁘게 병합된 셀은 그야말로 재앙의 시작입니다. 지난번 배운 대로 판다스(Pandas)를 이용해 기분 좋게 엑셀을 불러왔는데, 화면에 나타난 데이터가 중간중간 'NaN'이라는 정체불명의 글자로 도배되어 엉망진창으로 깨져 있던 경험, 다들 있으실 겁니다. 저 역시 처음 이 현상을 마주했을 때 코드가 완전히 망가진 줄 알고 식은땀을 흘렸습니다. 결국 파이썬 창을 닫고 다시 엑셀을 열어 병합을 다 풀고 일일이 드래그해서 빈칸을 채워 넣는 '수동 노가다'를 한 적도 있죠. 하지만 원리만 알면 파이썬에서 단 한 줄의 코드로 이 문제를 깔끔하게 해결할 수 있습니다. 오늘은 파이썬이 병합된 셀을 어떻게 인식하는지 그 원리를 이해하고, 마법의 함수를 통해 깨진 데이터를 원래대로 복원하는 방법을 알아보겠습니다. 1. 파이썬은 왜 병합된 셀을 'NaN'으로 뱉어낼까? 사람의 눈에는 3개의 행이 하나로 합쳐진 거대한 셀 안에 '영업팀'이라는 글자가 꽉 차 있는 것처럼 보입니다. 하지만 컴퓨터(엑셀 시스템 내부)가 데이터를 기억하는 방식은 다릅니다. 셀을 병합하면 오직 가장 위쪽(또는 왼쪽)의 첫 번째 셀에만 '영업팀'이라는 실제 데이터가 남고, 나머지 아래쪽 두 개의 셀은 텅 빈 공간으로 처리됩니다. 그저 시각적으로만 하나인 것처럼 껍데기를 씌워 둔 것입니다. 파이썬의 판다스는 사람처럼 예쁜 껍데기를 보지 않고 엑셀의 뼈대(Raw Data)를 아주 정직하게 읽어옵니다. 그래서 첫 번째 행에는 '영업팀'을 정상적으로 가져오지만, 나머지 두 개의 빈칸은 "여긴 데이터가 없는데요?"라며 결측치를 의미하는 'NaN(Not a Number)'...
자세한 내용 보기

날짜와 시간 데이터 포맷이 제각각일 때 파이썬으로 일괄 통일하는 비법

"2023.10.01", "23-10-01", "2023년 10월 1일", "10/01/2023"... 여러 부서에서 취합한 엑셀 파일을 열어보면 날짜를 입력하는 방식이 사람마다 그야말로 천차만별입니다. 엑셀에서 이 데이터를 날짜순으로 정렬하거나 월별 매출을 구하려고 피벗 테이블을 돌리면, 컴퓨터는 이를 날짜가 아닌 '단순한 글자(텍스트)'로 인식해서 가나다순으로 엉뚱하게 정렬해 버립니다. 결국 직장인들은 '셀 서식'에 들어가서 날짜 형식으로 억지로 바꿔보거나, 텍스트 나누기를 하고, 심지어 일일이 타자를 다시 치는 엄청난 시간 낭비를 겪게 됩니다. 하지만 파이썬에서는 제아무리 엉망으로 입력된 날짜라도 단 한 줄의 마법 같은 코드로 컴퓨터가 인식할 수 있는 '진짜 날짜'로 완벽하게 통일할 수 있습니다. 오늘은 시계열 데이터 분석의 뼈대가 되는 날짜 데이터 포맷팅 비법을 알아보겠습니다. 1. 엉망진창 날짜를 하나로 통일하는 마법: pd.to_datetime() 파이썬 판다스(Pandas)에는 사람이 쓴 다양한 형태의 날짜 글자를 컴퓨터용 표준 날짜 형식(YYYY-MM-DD)으로 강제 변환해 주는 아주 강력한 함수가 있습니다. 바로 to_datetime() 입니다. 불러온 데이터(df)에 '작성일'이라는 열이 있다고 가정해 봅시다. 주피터 노트북에 df['작성일'] = pd.to_datetime(df['작성일']) 이라고만 입력하고 실행해 보세요. 놀랍게도 점(.)이 찍혀있든, 슬래시(/)가 있든, 한글로 '년월일'이 적혀있든 판다스가 알아서 눈치껏 해석하여 2023-10-01 이라는 깔끔하고 통일된 규격으로 싹 바꿔줍니다. 이제 파이썬은 이 열을 단순한 글자가 아닌 '시간의 흐름'으로 완벽하게 인식하게 됩니다. 2. 컴퓨터도 헷갈리는 날짜 구출하기 (format과 errors...
자세한 내용 보기

수식이 걸린 엑셀 데이터, 파이썬으로 불렀더니 NaN이 뜨는 이유와 해결법

실무에서 사용하는 엑셀 파일은 단순한 숫자의 나열이 아닙니다. 단가와 수량을 곱하는 간단한 수식부터, 복잡한 조건부 합계(SUMIFS)까지 다양한 수식들이 거미줄처럼 얽혀있죠. 파이썬 자동화를 처음 배우시는 분들은 이 수식이 걸린 엑셀 파일을 판다스로 호기롭게 불러왔다가, 숫자가 있어야 할 자리에 'NaN(빈칸)'이 가득한 것을 보고 멘붕에 빠지곤 합니다. "엑셀을 열면 분명히 숫자가 잘 보이는데, 왜 파이썬은 빈칸이라고 할까요?" 이것은 파이썬에 에러가 난 것이 아니라, 엑셀과 파이썬이 '수식'을 대하는 근본적인 방식이 다르기 때문입니다. 오늘은 이 치명적인 오류의 원인을 파헤치고, 진정한 의미의 '데이터 자동화'를 위해 수식을 어떻게 다뤄야 하는지 그 해답을 제시해 드립니다. 1. 파이썬은 엑셀의 수식을 '계산'하지 못합니다 가장 먼저 머릿속에 박아두어야 할 핵심 개념입니다. 판다스(Pandas)는 엑셀 프로그램이 아닙니다. 따라서 SUM , VLOOKUP 같은 엑셀 고유의 함수 엔진을 가지고 있지 않습니다. 우리가 pd.read_excel() 로 파일을 불러올 때, 파이썬은 엑셀 파일의 껍데기를 살짝 들춰보고 '가장 마지막에 엑셀이 계산해 둔 결과값(캐시 데이터)'만을 사진 찍듯이 찰칵 찍어옵니다. 즉, 파이썬 안에서 수량 데이터를 10에서 20으로 바꾼다고 해서, 총합계 수식이 자동으로 업데이트되어 바뀌지 않는다는 뜻입니다. 파이썬이 가져온 것은 살아 숨 쉬는 수식이 아니라 '박제된 숫자 텍스트'일 뿐입니다. 2. ERP에서 다운받은 파일이 무조건 NaN을 뱉는 이유 그런데 왜 숫자가 안 나오고 NaN이 뜨는 걸까요? 보통 회사 사내 시스템(ERP 등)에서 다운로드한 엑셀 파일에서 이런 일이 잦습니다. 시스템이 엑셀 파일을 만들어낼 때는 컴퓨터의 리소스를 아끼기 위해 수식(예: =A1*B1 )만 텍스트로 적어놓고, 실제 결과값은 계산하지 않은 채(...
자세한 내용 보기