[python 활용하기#12] Pandas 여러 시트 통합 후 피벗테이블 만들기

[python 활용하기#12] Pandas 여러 시트 통합 후 피벗테이블 만들기

 

안녕하세요. 심심한 코딩쟁이입니다.

 

오늘은 직장인들의 친구 피벗테이블을 파이썬의 Pandas로 다루는 방법을 알아보려합니다.

 

여러 데이터들을 통합해서 피벗테이블로 만드는 작업을 해야할 때가 종종 생기실텐데

 

파이썬을 이용해서 코드를 만들어두었다가 필요에 따라 약간씩 변형해서 사용하시면

 

업무처리에 아주 큰 도움이 되리라 생각됩니다.

 

같이 살펴보시죠.

 

판다스 로고

---

사용할 데이터 살펴보기

 

제일 먼저 우리가 피벗테이블로 만들 데이터의 형태를 살펴봅시다.

 

각자의 상황에 맞게끔 코드를 변형해서 사용해야할 수 있으니 도움이 필요하신분들은 댓글로 남겨주세요.

 

이번 포스팅에서 사용할 데이터의 모습입니다.

 

여러 편의점을 운영하는 사장님이 매장들의 매출을 파악하고자 사용하는 데이터라는 설정입니다.

 

품목	강남점	양재점	역삼점
과자	1,000	2,000	2,000
아이스크림	3,000	1,000	3,000
즉석식품	3,000	3,000	2,000
음료	2,000	2,000	1,000
주류	3,000	1,000	3,000
생활용품	1,000	2,000	3,000
담배	3,000	2,000	1,000

 

제가 사용한 데이터가 필요하시다면 아래에 첨부한 파일을 사용하시면 됩니다.

 

매출.xlsx

0.01MB

 

매출 정보가 달 별로 여러 시트에 저장되어있는 엑셀 파일을 사용해 모든 매출 정보를

 

통합 후 매장 별로 어떤 품목이 얼마나 팔렸는가를 살펴보는 피벗테이블을 만들어 보겠습니다.

 

---

데이터 읽어오기

 

가장 먼저 데이터를 읽어오는 과정을 살펴보시죠.

 

import pandas as pd
import openpyxl as opxl

for name in opxl.load_workbook("매출.xlsx").sheetnames:
    df = pd.read_excel("매출.xlsx", sheet_name=name).set_index("품목")
    print(df)

 

위 코드를 실행해 보면 1월 ~ 6월 시트안에 있는 정보들을 모두 읽어오는 것을 볼 수 있습니다.

 

openpyxl 라이브러리의 함수들을 사용해 존재하는 시트의 이름을 리스트로 받아와서 for 문에 활용하는 방식입니다.

 

---

데이터 이어붙이기

 

우리가 피벗테이블을 만들기 위해서는 우선 언피벗된 데이터가 필요합니다.

 

각 시트별로 언피벗한 데이터들을 판다스의 concat 을 사용해 세로로 이어붙이도록 하겠습니다.

---

시트에 존재하는 데이터를 언피벗하는 코드입니다.

 

import pandas as pd
import openpyxl as opxl

for name in opxl.load_workbook("매출.xlsx").sheetnames:
    df = pd.read_excel("매출.xlsx", sheet_name=name).set_index("품목")
    df = df.stack().reset_index()
    print(df)

# 출력 결과 #####################
       품목 level_1       0
0      과자     강남점  806912
1      과자     양재점  328765
2      과자     역삼점  532119
#################################

# 우리가 원하는 열 이름이 아니므로 수정해야함

---

우리가 사용할 열 이름으로 수정 후 데이터를 세로로 이어 붙여 보겠습니다. 

 

import pandas as pd
import openpyxl as opxl

pdf = pd.DataFrame([])

for name in opxl.load_workbook("매출.xlsx").sheetnames:
    df = pd.read_excel("매출.xlsx", sheet_name=name).set_index("품목")
    df = df.stack().reset_index()
    df.columns = ["품목", "지점", "매출"]
    pdf = pd.concat([pdf, df])

print(pdf)

# 출력 결과 #######################
       품목   지점      매출
0      과자  강남점  849489
1      과자  양재점  434872
2      과자  역삼점  893209
3   아이스크림  강남점  503140
4   아이스크림  양재점  145199
..    ...  ...     ...
16   생활용품  양재점  727788
17   생활용품  역삼점   21158
18     담배  강남점  755223
19     담배  양재점  710404
20     담배  역삼점  762264

[126 rows x 3 columns]
###################################
# 세로로 이어붙여 126줄의 데이터가 생성되었습니다.

---

모든 데이터를 이어붙였으니 이제 피벗테이블을 만들어봅시다.

 

import pandas as pd
import openpyxl as opxl

pdf = pd.DataFrame([])
for name in opxl.load_workbook("매출.xlsx").sheetnames:
    df = pd.read_excel("매출.xlsx", sheet_name=name).set_index("품목")
    df = df.stack().reset_index()
    df.columns = ["품목", "지점", "매출"]
    pdf = pd.concat([pdf, df])
    
pdf = pdf.pivot_table("매출", index="지점", columns="품목", aggfunc="sum") # 집계함수 sum 사용
# 집계함수를 sum으로 사용하는것은 매출의 총합이 필요하기 때문
# pdf = pdf.pivot_table(values="매출", index="지점", columns="품목", aggfunc="sum")
# 위 코드와 같은 동작

pdf.to_clipboard() # 엑셀에 붙여넣고 싶을때 사용

print(pdf)

# 출력 결과 ############################################################################
품목        과자       담배     생활용품    아이스크림       음료       주류     즉석식품
지점
강남점  4076791  3571916  3243847  2968100  3262180  2616684  2570267
양재점  2363647  3262718  3941330  2908451  3709670  4241618  3327248
역삼점  3980930  3651444  2294660  3611182  1635856  2674239  2168921
########################################################################################

# 공백 간격이 안 맞을 뿐 제대로 통합된 결과가 얻어집니다.

 

피벗테이블의 데이터를 엑셀에 바로 붙여넣고 싶다면 to_clipboard를 사용해 붙여넣기로 엑셀에 작성하시면 됩니다.

---

여기까지 파이썬 판다스를 이용한 데이터 통합 후 피벗테이블 만들기에 대한 설명이었습니다.

 

많은 분들에게 도움이 되었으면 좋겠네요.

 

따라해보시다가 제가 설정해놓은 상황과 다른 데이터 형태 때문에 어려움이 있으시다면 댓글로 남겨주세요.

 

성심성의껏 답해드리겠습니다.

 

감사합니다.