[엑셀이 편해지는 파이썬] 8장. 판다스를 사용한 엑셀 파일 조작 (211~242p)

8장. 리더와 라이터 패키지를 이용한 엑셀 파일 조작¶

8.1 리더와 라이터 패키지¶

8.1.1 언제 어떤 패키지를 사용할 지 선택하기¶

• OpenPyXL은 읽기, 쓰기, 편집이 모두 가능하지만, XlsxWriter는 쓰기만 가능
• OpenPyXL은 VBA 매크로를 포함한 엑셀 파일을 더 쉽게 만들 수 있다.
• XlsxWriter는 문서화가 더 잘 되어 있다.
• XlsxWriter는 OpenPyXL보다 빠른 편이지만, 작성하는 워크북의 크기에 따라 그리 큰 장점이 아닐 수 있다.

read_excel, to_excel 함수, ExcelFile이나 ExcelWriter 클래스에서 engine인자에 패키지 이름을 소문자로 전달

8.1.2 excel.py 모듈¶

• 여러 패키지가 있으므로 패키지가 전환되어 문법이 다른 경우에도 에러 없이 사용
• 반환하는 데이터 타입이 각 패키지 마다 다를 수 있는데 이를 일관성있게 출력 되도록 도움
• 자주 하는 작업을 쉽게 자동화할 수 있는 함수가 없어서 반복문을 만들어야할 가능성이 높은데 이를 쉽게 만들 수 있도록 도움

8.1.3 OpenPyXL¶

OpenPyXL로 읽기¶

In [ ]:

import pandas as pd
import openpyxl
import excel
import datetime as dt

# 셀 값을 읽도록 워크북을 연다.
# 데이터를 모두 읽으면 파일은 자동으로 닫힌다.
book = openpyxl.load_workbook("../xl/stores.xlsx", data_only=True)

# 이름 또는 0으로 시작하는 인덱스로 워크시트 객체를 가져오기
# sheet = book["2019"]  # 이름으로 워크시트 객체 가져오기
sheet = book.worksheets[0]  # 인덱스로 가져오기

# 시트 이름 리스트를 가져오기
book.sheetnames

Out[ ]:

['2019', '2020', '2019-2020']

In [ ]:

# 시트 객체를 순회
# OpenPyXL은 name 대신 title을 사용
for i in book.worksheets:
    print(i.title)

2019
2020
2019-2020

In [ ]:

# 크기, 즉 시트의 사용된 영역을 가져온다.
sheet.max_row, sheet.max_column

Out[ ]:

(8, 6)

In [ ]:

# A1 표기법과 1에서 시작하는 셀 인덱스를 써서 셀 값을 얻기
sheet["B6"].value
sheet.cell(row=6, column=2).value

Out[ ]:

'Boston'

In [ ]:

# excel 모듈을 써서 셀 값 범위를 읽기
data = excel.read(book["2019"], (2, 2), (8, 6))
data[:2]

Out[ ]:

[['Store', 'Employees', 'Manager', 'Since', 'Flagship'],
 ['New York', 10, 'Sarah', datetime.datetime(2018, 7, 20, 0, 0), False]]

OpenPyXL로 쓰기¶

In [ ]:

# 차트 등을 만들 openpyxl 클래스들 부르기
import openpyxl
from openpyxl.drawing.image import Image
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, colors
from openpyxl.styles.borders import Border, Side
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.fills import PatternFill
import excel

In [ ]:

# SNIPPET & Cookbook
# Instantiate a workbook
book = openpyxl.Workbook()

# Get the first sheet and give it a name
sheet = book.active
sheet.title = "Sheet1"

# Writing individual cells using A1 notation
# and cell indices (1-based)
sheet["A1"].value = "Hello 1"
sheet.cell(row=2, column=1, value="Hello 2")

# Formatting: fill color, alignment, border and font
font_format = Font(color="FF0000", bold=True)
thin = Side(border_style="thin", color="FF0000")
sheet["A3"].value = "Hello 3"
sheet["A3"].font = font_format
sheet["A3"].border = Border(top=thin, left=thin,
                            right=thin, bottom=thin)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A3"].fill = PatternFill(fgColor="FFFF00", fill_type="solid")

# Number formatting (using Excel's formatting strings)
sheet["A4"].value = 3.3333
sheet["A4"].number_format = "0.00"

# Date formatting (using Excel's formatting strings)
sheet["A5"].value = dt.date(2016, 10, 13)
sheet["A5"].number_format = "mm/dd/yy"

# Formula: you must use the English name of the formula
# with commas as delimiters
sheet["A6"].value = "=SUM(A4, 2)"

# Image
sheet.add_image(Image("images/python.png"), "C1")

# Two-dimensional list (we're using our excel module)
data = [[None, "North", "South"],
        ["Last Year", 2, 5],
        ["This Year", 3, 6]]
excel.write(sheet, data, "A10")

# Chart
chart = BarChart()
chart.type = "col"
chart.title = "Sales Per Region"
chart.x_axis.title = "Regions"
chart.y_axis.title = "Sales"
chart_data = Reference(sheet, min_row=11, min_col=1,
                       max_row=12, max_col=3)
chart_categories = Reference(sheet, min_row=10, min_col=2,
                             max_row=10, max_col=3)
# from_rows interprets the data in the same way
# as if you would add a chart manually in Excel
chart.add_data(chart_data, titles_from_data=True, from_rows=True)
chart.set_categories(chart_categories)
sheet.add_chart(chart, "A15")

# Saving the workbook creates the file on disk
book.save("openpyxl.xlsx")

In [ ]:

# SNIPPET: template으로 저장하기
book = openpyxl.Workbook()
sheet = book.active
sheet["A1"].value = "This is a template"
book.template = True
book.save("template.xltx")

OpenPyXL로 편집하기¶

진정한 의미에서 엑셀 파일을 편집할 수 있는 리더/라이터 패키지는 없다.

Refer 더 다양한 함수들과 인자들은 공식 문서 참조 https://oreil.ly/7qfYL

8.1.4 XlsxWriter¶

인덱스가 0으로 시작한다. - OpenPyXL은 1부터 시작

8.1.5 pyxlsb¶

이진 엑셀 파일인 xlsb 확장자를 읽어야 한다면 선택의 여지가 없다.
날짜 형식의 셀을 인식하지 못하므로 직접 datetime 객체로 바꿔줘야 한다.
엔진 (ex: engine=pyxlsb)을 명시적으로 지정해야 한다.

8.1.6 xlrd, xlwt, 엑셀 유틸¶

구형 xls 형식을 다룬다.
xlrd - 읽기, xlwt - 쓰기, xlutils - 편집하기

xlrd로 읽기¶

xlwt로 쓰기¶

xlutils로 편집하기¶

엄밀히 말해 편집 동작을 하는 것은 아니고, xlrd로 서식을 포함해 읽어 들이고, xlwt로 파일을 기록하면서 그 사이에 일어난 변화를 반영

8.2 고급 주제¶

8.2.1 큰 엑셀 파일 다루기¶

OpenPyXL로 쓰기¶

1) lxml 패키지 2) Workbook 인스턴스를 시작할 때 메모리 소모량을 줄이는 write_only=True 플래그 3) append() 메서드로 한 줄씩 써야 한다 - 이것이 제일 문제 (개별 셀에 쓸 수 없다.)

XlsxWriter로 쓰기¶

앞과 같이 메모리 소모량을 줄이는 옵션: constant_memory
또한 행을 순차적으로 기록해야 하는 것도 같음
옵션을 전달하는 구조만 조금 다름

• Workbook 인스턴스를 시작할 때 options={"contant_memory": True} 형태로 전달

xlrd로 읽기¶

큰 xls 파일을 읽을 때 필요한 시트만 읽을 수 있다.

In [ ]:

# SNIPPET: xlrd로 큰 xls 파일 읽기
import xlrd

with xlrd.open_workbook("../xl/stores.xls", on_demand=True) as book:
    sheet = book.sheet_by_index(0)

In [ ]:

# SNIPPET: xlrd와 pandas 같이 사용하기
import pandas as pd

with xlrd.open_workbook("../xl/stores.xls", on_demand=True) as book:
    with pd.ExcelFile(book, engine="xlrd") as f:
        df = pd.read_excel(f, sheet_name=0)

OpenPyXL로 읽기¶

• read_only=True
• 작업이 끝나면 파일을 직접 닫아야 함 (book.close())
• 다른 워크북에 대한 참조가 들어 있다면 keep_links=False 인자 전달
• 참조가 없으면 아예 전달하지 마라

병렬로 시트 읽기¶

multiprocessing 패키지를 이용하여 시트 여러 개를 병렬로 읽을 수 있다.

Refer %%time 매직 커맨드로 주피터 노트북의 한 셀의 수행 시간/경과 시간 등을 측정할 수 있다.
Refer 거대한 시트 한 개를 읽어야 할 때 시트 읽기를 병렬로 처리하는 판다스 애드온이 있다. - 모딘

8.2.2 데이터프레임 서식 개선¶

• 데이터프레임 보고서에 제목을 추가
• 데이터프레임의 인덱스와 헤더에 서식 적용 - OpenPyXL/XlsxWriter 버전 모두 가능하다.
• 데이터프레임의 데이터 부분에 서식 적용 - OpenPyXL은 각 셀에 적용 가능하지만, XlsxWriter는 행/열 단위로만 가능하다.
• Refer: (실험중) Style 프로퍼티 (df.style.applymap)- 판다스의 데이터프레임 스타일 Link
• Refer: 날짜와 시간 객체는 스타일 속성을 사용하지 않아도 서식을 지정할 수 있음
• Refer: 기타 리더와 라이터 패키지 - pyexcel, PyExcelerate, pylightxl, styleframe, oletools

8.2.3 다시 살펴보는 케이스 스터디: 엑셀 보고서¶

8.3 요약¶

개별 리더와 라이터 패키지에 너무 집중하기 보다는 우선 판다스로 할 수 있는 만큼은 해 보고 판다스가 지원하지 않는 기능이 필요할 때에만 개별 페키지를 사용하는 방식이 합리적이다.