엑셀 피벗 테이블(Pivot Table), 파이썬 코드 한 줄로 1초 만에 자동화하기

 

수만 줄, 수십만 줄의 원본 데이터(Raw Data)를 팀장님이 한눈에 볼 수 있도록 요약하려면 어떻게 해야 할까요? 엑셀을 조금 다룰 줄 아는 직장인이라면 주저 없이 '피벗 테이블(Pivot Table)'을 켤 것입니다. 십자 모양의 틀 안에 부서명, 날짜, 매출액을 마우스로 끌어다 놓기만 하면 순식간에 멋진 요약 보고서가 완성되니까요.

하지만 엑셀 피벗 테이블에는 치명적인 단점이 있습니다. 매달 새로운 데이터가 추가될 때마다 원본 범위를 다시 잡고 '새로고침'을 눌러야 하며, 마우스로 일일이 필드를 드래그하는 작업은 '자동화'와 거리가 멉니다. 파이썬 판다스(Pandas)에서는 이 번거로운 마우스 드래그 작업을 단 한 줄의 코드로 영구적으로 자동화할 수 있습니다. 오늘은 파이썬으로 피벗 테이블을 1초 만에 뚝딱 만들어내는 공식을 알아보겠습니다.


1. 엑셀의 4구역, 파이썬 코드로 매칭하기

엑셀 피벗 테이블을 띄우면 우측에 4개의 네모 칸(필터, 열, 행, 값)이 나옵니다. 파이썬의 pd.pivot_table() 함수는 이 4개의 칸을 영단어로 그대로 옮겨놓았을 뿐, 원리는 100% 똑같습니다.

  • index : 엑셀의 [행] (예: 부서별로 볼래)
  • columns : 엑셀의 [열] (예: 월별로 나누어서 볼래)
  • values : 엑셀의 [값] (예: 매출액을 볼래)
  • aggfunc : [계산 방식] (예: 더할래? 평균을 낼래?)

이 공식만 머릿속에 넣으면 끝입니다. "부서별(행), 월별(열) 매출액(값)의 합계(계산)를 구해줘!"를 코드로 바꾸면 아래와 같이 됩니다.

pivot_df = pd.pivot_table(df, index='부서명', columns='월', values='매출액', aggfunc='sum')

이 한 줄을 실행하는 순간, 마우스로 낑낑대며 드래그하던 그 요약 표가 파이썬 화면에 즉시 나타납니다.


2. 다중 조건 설정하기 (대분류 > 소분류)

실무 보고서는 단순하지 않습니다. "본부별로 먼저 나누고, 그 안에서 다시 팀별로 나누어서 매출을 보여줘" 같은 다중 조건이 수시로 붙습니다. 엑셀에서는 행 칸에 필드를 두 개 끌어다 놓으면 되죠? 파이썬에서는 대괄호 []를 써서 두 개를 묶어주기만 하면 됩니다.

pd.pivot_table(df, index=['본부명', '팀명'], values='매출액', aggfunc='sum')

마찬가지로 매출액(합계)과 영업이익(평균)을 동시에 보고 싶다면 values=['매출액', '영업이익'], aggfunc=['sum', 'mean'] 처럼 대괄호 안에 여러 개를 나열하면 됩니다. 엑셀보다 훨씬 직관적이고 오류가 날 확률이 제로에 가깝습니다.


3. 클릭 한 번에 '총합계' 추가하기: margins=True

보고서의 생명은 맨 밑, 그리고 맨 오른쪽에 있는 '총합계'입니다. 엑셀에서는 디자인 탭에 들어가서 '총합계 표시'를 마우스로 클릭해서 켜주어야 합니다. 파이썬에서는 아주 우아한 옵션 하나로 끝납니다.

pd.pivot_table(df, index='부서명', values='매출액', aggfunc='sum', margins=True)

끝에 margins=True (여백에 합계를 추가하라) 라는 옵션만 달아주면, 판다스가 알아서 행과 열의 맨 끝에 'All'이라는 이름표를 달고 전체 총합계를 계산해서 붙여줍니다. 이렇게 완성된 피벗 데이터는 11편에서 배운 to_excel()을 통해 그대로 엑셀 파일로 쏙 뽑아내면 팀장님 책상에 올라갈 완벽한 요약 보고서가 됩니다.


데이터 분석, 마우스 대신 코드로 지휘하세요

피벗 테이블을 코드로 짠다는 것이 처음에는 어색하게 느껴질 수 있습니다. 하지만 한 번만 세팅해 두면, 내일 수십만 줄의 새로운 데이터가 들어와도 코드 실행 버튼 클릭 한 번에 1초 만에 요약 보고서가 튀어나오는 마법을 경험하시게 될 겁니다. 이것이 바로 파이썬으로 퇴근 시간을 앞당기는 진짜 무기입니다.

▶ 핵심 요약

  • 엑셀 피벗 테이블의 마우스 드래그 작업은 파이썬 판다스의 pd.pivot_table() 함수 단 한 줄로 완벽하게 대체할 수 있습니다.
  • index(행), columns(열), values(값), aggfunc(계산방식) 네 가지 옵션에 원하는 열 이름을 적어주는 것이 기본 공식입니다.
  • 다차원 분석이 필요할 때는 대괄호 []를 사용하고, 총합계가 필요할 때는 margins=True 옵션을 추가하여 실무 보고서 포맷을 단숨에 완성합니다.

▶ 다음 편 예고

피벗 테이블로 숫자를 깔끔하게 요약했으니, 이제 팀장님의 눈을 사로잡을 '시각화' 작업이 필요합니다. 다음 14편에서는 '엑셀 차트 필요 없다! 파이썬으로 1초 만에 꺾은선, 막대그래프 그리고 엑셀에 삽입하기'에 대해 다루겠습니다.

여러분은 엑셀에서 피벗 테이블을 돌리다가 데이터가 너무 많아서 메모리 부족으로 엑셀이 강제 종료된 뼈아픈 경험이 있으신가요? 엑셀 피벗 테이블의 가장 짜증 났던 단점을 댓글로 공유해 주세요!

댓글

댓글 쓰기

이 블로그의 인기 게시물

수만 줄의 데이터, 엑셀이 멈출 때 파이썬으로 가볍게 필터링하는 요령

용량이 100MB가 넘어가는 데이터 파일, 혹은 수십만 줄이 쌓여있는 로우 데이터(Raw Data)를 엑셀로 열어본 경험이 있으신가요? 파일을 더블클릭하는 순간 마우스 커서가 뱅글뱅글 돌기 시작하고, 화면은 하얗게 변하며 제목 표시줄에는 '응답 없음'이라는 공포의 단어가 뜹니다. 어찌어찌 파일이 열렸다고 해도, '데이터 필터' 버튼을 누르고 특정 부서나 특정 날짜의 체크박스를 클릭하는 데만 또 수 분의 시간이 걸립니다. 바쁜 업무 시간에 이런 로딩을 기다리며 커피만 홀짝이는 것은 너무나도 큰 시간 낭비입니다. 엑셀은 눈에 보이는 격자무늬(UI)를 화면에 띄우는 데 컴퓨터의 메모리(RAM)를 엄청나게 소모하기 때문에 대용량 처리에 쥐약입니다. 반면, 파이썬은 눈에 보이는 화려한 껍데기 없이 오직 백그라운드에서 연산만 수행하므로 수십만 줄의 데이터도 단 몇 초면 가볍게 처리할 수 있습니다. 오늘은 엑셀에서 우리를 가장 지치게 했던 '조건 필터링' 작업을 파이썬 판다스(Pandas)로 어떻게 순식간에 끝낼 수 있는지, 그 직관적이고 강력한 방법을 알아보겠습니다. 1. 엑셀의 필터 버튼을 대체하는 '조건 마스크' 원리 엑셀에서는 깔때기 모양의 필터 버튼을 눌러서 원하는 항목에 체크 표시를 합니다. 파이썬에서는 이 체크 표시 대신 '조건문'을 사용합니다. 원리는 아주 간단합니다. 파이썬에게 "이 조건이 맞는 줄(행)은 True, 틀린 줄은 False로 표시해 줘"라고 명령한 뒤, True로 표시된 데이터만 쏙 뽑아오는 방식입니다. 이것을 실무에서는 '마스킹(Masking)'이라고 부릅니다. 만약 우리가 불러온 데이터(df)에서 '부서명' 열의 값이 '영업팀'인 데이터만 걸러내고 싶다면 어떻게 해야 할까요? 주피터 노트북에 df[df['부서명'] == '영업팀'] 이라고 적어주기만 하면 끝납니다. 바깥쪽의 대괄...
자세한 내용 보기

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

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

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

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