파이썬을 사용하여 Excel 피벗 테이블 다루기

들어가기 전에

피벗 테이블은 데이터를 빠르게 요약, 분석 및 시각화할 수 있도록 하는 Excel의 강력한 기능 중 하나입니다. 피벗 테이블은 원시 데이터를 의미 있는 통찰력으로 변환하는 유연하고 효율적인 방법을 제공합니다. 파이썬을 사용하여 Excel에서 피벗 테이블을 만들고, 업데이트하고, 제거하는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

---

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: Create, Update and Remove Pivot Tables in Excel with Python
• URL: https://medium.com/@alice.yang_10652/create-update-and-remove-pivot-tables-in-excel-with-python-a7db24d02445

---

Excel의 피벗 테이블을 제어하는 파이썬 라이브러리

이 문서에서는 파이썬에서 Excel의 피벗 테이블을 만들고, 업데이트하고, 제거하기 위해 파이썬 Excel 라이브러리를 사용합니다: Python용 Spire.XLS입니다.

파이썬용 Spire.XLS는 파이썬 애플리케이션 내에서 Excel 파일을 만들고, 읽고, 편집하고, 변환하기 위한 다기능의 사용하기 쉬운 라이브러리입니다. 이 라이브러리를 사용하면 XLS, XLSX, XLSB, XLSM 및 ODS와 같은 다양한 스프레드시트 형식으로 쉽게 작업할 수 있습니다. 또한 Excel 파일을 PDF, HTML, CSV, 텍스트, 이미지, XML, SVG, ODS, PostScript 및 XPS와 같은 다른 유형의 파일 형식으로 렌더링할 수도 있습니다.

터미널에서 다음 명령을 실행하여 pypi에서 Python용 Spire.XLS를 쉽게 설치할 수 있습니다.

pip install Spire.Xls

 

설치에 대한 자세한 내용은 [여기]에서 확인할 수 있습니다.

파이썬으로 Excel에서 피벗 테이블 만들기

피벗 테이블은 워크시트의 데이터를 직접 사용하지 않고 데이터의 스냅샷을 저장하는 피벗 캐시에서 가져옵니다. 피벗 테이블에는 행 필드, 열 필드 및 데이터 필드가 포함될 수 있습니다. 피벗 테이블을 만들려면 다음 단계를 따릅니다.

(1) 피벗 캐시를 추가합니다: 워크시트에서 관련 데이터를 캡처하여 보관합니다.

 

(2) 피벗 테이블 만들기: 피벗 캐시에 저장된 데이터를 사용하여 피벗 테이블을 설정합니다.

 

(3) 필드 추가: 행 필드, 열 필드, 데이터 필드 등의 필드를 추가하여 데이터를 정리하고 분석할 수 있습니다.

 

다음은 Python을 사용하여 Excel에서 피벗 테이블을 만드는 방법을 보여주는 예제입니다.

 

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Define the data source range
data_range = sheet.Range["A1:C9"]
# Add a pivot cache from the given data source range
pivot_cache = workbook.PivotCaches.Add(data_range)

# Create a pivot table from the pivot cache and add it to a specific location of the worksheet
pivot_table = sheet.PivotTables.Add("Pivot Table", sheet.Range["E2:H8"], pivot_cache)

# Add a row field to the pivot table
row = pivot_table.PivotFields["Region"]
row.Axis = AxisTypes.Row
pivot_table.Options.RowHeaderCaption = "Region"
# Add a column field to the pivot table
column = pivot_table.PivotFields["Product"]
column.Axis = AxisTypes.Column
pivot_table.Options.ColumnHeaderCaption = "Product"
# Add a data field with the required subtotal function to the pivot table
pivot_table.DataFields.Add(pivot_table.PivotFields["Sales"], "Sum of Sales", SubtotalTypes.Sum)

# Set pivot table style
pivot_table.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium14       

# Set column width
sheet.Range["E2:H8"].ColumnWidth = 12.0

# Save the resultant workbook to a file
workbook.SaveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬에서 Excel 피벗 테이블의 데이터 소스 변경하기

분석에 추가 데이터 필드 또는 데이터 집합을 포함해야 하는 경우가 있습니다. 데이터 원본을 변경하면 피벗 테이블에 더 포괄적인 정보를 추가할 수 있습니다.

다음은 Python을 사용하여 Excel에서 피벗 테이블의 데이터 원본을 변경하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotTable.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
# Get the first pivot table in the worksheet
pivot_table = sheet.PivotTables[0]

# Define the new data source range of the pivot table
data_range = sheet.Range["B1:C9"]

# Change the data source range of the pivot table
pivot_table.ChangeDataSource(data_range)
# Refresh the pivot table
pivot_table.Cache.IsRefreshOnLoad = True

# Save the resultant workbook to a file
workbook.SaveToFile("ChangeDataSourceOfPivotTable.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

파이썬에서 Excel 피벗 테이블 필드에 숫자 서식 설정하기

숫자 서식 코드를 통해 피벗 테이블의 데이터에 숫자, 통화, 회계, 날짜, 백분율, 과학, 분수, 텍스트 등 다양한 유형의 숫자 서식을 적용할 수 있습니다.

다음은 Python을 사용하여 Excel 피벗 테이블의 특정 피벗 필드 데이터에 숫자 서식을 설정하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotTable.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
# Get the first pivot table in the worksheet
pivot_table = sheet.PivotTables[0]

# Set currency number format for the pivot field "Sales"
pivot_table.PivotFields["Sales"].NumberFormat = "\"$\"#,##0.00"

# Save the resultant workbook to a file
workbook.SaveToFile("SetNumberFormatForPivotField.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel 피벗 테이블에서 데이터 정렬하기

정렬을 사용하면 특정 정보를 더 빨리 찾을 수 있습니다. 예를 들어 가장 낮은 판매량이나 가장 빠른 날짜를 찾고 있는 경우 피벗 테이블을 정렬하면 해당 데이터가 맨 위에 표시됩니다.

다음은 Python을 사용하여 Excel의 피벗 테이블에서 데이터를 정렬하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotTable.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
# Get the first pivot table in the worksheet
pivot_table = sheet.PivotTables[0]

# Sort data of the pivot field "Region" in descending order
pivot_table.PivotFields["Region"].SortType = PivotFieldSortType.Descending

# Save the resultant workbook to a file
workbook.SaveToFile("SortDataInPivotField.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel 피벗 테이블의 데이터 소스 추출하기

데이터 원본을 추출하면 피벗 테이블의 요약 뒤에 있는 원시 데이터를 검토할 수 있습니다. 요약되는 데이터가 정확하고 완전한지 확인하려는 경우에 유용합니다.

다음은 Python에서 Excel의 피벗 테이블의 데이터 소스를 추출하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotTable.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
# Get the first pivot table in the worksheet
pivot_table = sheet.PivotTables[0]

# Get the cell range
data_range = pivot_table.Location  

# Iterate through the rows
for i in range(len(data_range.Rows)):

    # Iterate through the columns
    for j in range(len(data_range.Rows[i].Columns)):

        # print value of the current cell
        print(data_range[i + 1, j + 1].Value + "  ", end='')
        
    print("")

workbook.Dispose()

 

파이썬에서 Excel의 피벗 테이블 제거하기

피벗 테이블의 인덱스나 이름을 사용하여 피벗 테이블을 제거할 수 있을 뿐만 아니라 워크시트에서 모든 피벗 테이블을 제거할 수도 있습니다.

다음은 Python을 사용하여 Excel 워크시트에서 피벗 테이블을 제거하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotTable.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Remove a specific pivot table by its index
sheet.PivotTables.RemoveAt(0)

# Remove a specific pivot table by its name
# sheet.PivotTables.Remove("Pivot Table")

# Remove all pivot tables from the worksheet
# sheet.PivotTables.Clear()

# Save the resultant workbook to a file
workbook.SaveToFile("RemovePivotTable.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.

---