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

7장. 판다스를 사용한 엑셀 파일 조작¶

7.1 케이스 스터디: 엑셀 보고서¶

대리점 별 / 월 별 전체 매출의 엑셀 보고서를 만들어 보자.

In [ ]:

import pandas as pd
df = pd.read_excel("../sales_data/new/January.xlsx")
df.info()

• sales_report_pandas.py에 스크립트를 작성

7.2 판다스로 엑셀 파일 읽고 쓰기¶

7.2.1 read_excel 함수와 ExcelFile 클래스¶

In [ ]:

# sheet_name, skiprows, usecols 인자로 판다스가 읽어들일 셀 범위를 지정
df = pd.read_excel("../xl/stores.xlsx",
                   sheet_name="2019", skiprows=1, usecols="B:F")
df

In [ ]:

df.info()

In [ ]:

# Flagship의 경우 object type이 아니라 bool이어야 한다.
def fix_missing(x):
    return False if x in ["", "MISSING"] else x

In [ ]:

df = pd.read_excel("../xl/stores.xlsx",
                   sheet_name="2019", skiprows=1, usecols="B:F",
                   converters={"Flagship": fix_missing})
df

In [ ]:

df.info()

In [ ]:

# 모든 시트를 읽어 들이려면 sheet_name=None
# sheet_name에 리스트로 전달하면 시트 이름을 키, 데이터프레임을 값으로 갖는 딕셔너리를 반환
sheets = pd.read_excel("../xl/stores.xlsx",
                       sheet_name=["2019", "2020"], skiprows=1,
                       usecols=["Store", "Employees"])

# 딕셔너리로 접근하기
sheets["2019"].head(2)

In [ ]:

# 원본 엑셀 파일에 열 헤더가 없으면 header=None을 전달하고, names 인자로 헤더를 지정
# sheet_name은 인덱스도 받는다.
df = pd.read_excel("../xl/stores.xlsx", sheet_name=0,
                   skiprows=2, skipfooter=3,
                   usecols="B:C, F", header=None,
                   names=["Branch", "Employee_Count", "Is_Flagship"])
df

In [ ]:

# NaN 값을 지정할 때: na_values, keep_default_na 인자를 사용
# na_values에 NaN으로 만들고자 하는 값, 
# keep_default_na 인자로 파이썬의 표준 NaN 처리 방식을 그대로 사용할지 여부 결정
df = pd.read_excel("../xl/stores.xlsx", sheet_name="2019",
                   skiprows=1, usecols="B, C, F", skipfooter=2,
                   na_values="MISSING", keep_default_na=False)
df

엑셀 파일은 pd.ExcelFile 클래스로도 열 수 있다.
주로 구형 xls 형식에서 시트 여러 개를 읽을 때 사용
판다스가 파일 전체를 시트 마다 여러 번 읽을 일이 없어서 더 빠르고, context manager 역할도 겸하므로 사용이 쉽다.

In [ ]:

with pd.ExcelFile("../xl/stores.xls") as f:
    df1 = pd.read_excel(f, "2019", skiprows=1, usecols="B:F", nrows=2)
    df2 = pd.read_excel(f, "2020", skiprows=1, usecols="B:F", nrows=2)

df1

In [ ]:

stores = pd.ExcelFile("../xl/stores.xlsx")
stores.sheet_names

url을 통해 엑셀을 다운로드해서 읽어들일 수 있다.
Refer pandas < 1.3에서 xlsb 파일을 읽기 위해 engine="pyxlsb"를 명시해야 한다.

Refer read_excel 함수의 전체 인자 리스트 Link

7.2.2 to_excel 메서드와 ExcelWriter 클래스¶

to_excel() - 데이터프레임을 어느 시트의 어느 셀에 쓸 지 지정,
인덱스와 열 헤더를 포함할 지 여부,
np.nan, np.inf의 처리 방법 등을 설정할 수 있다.

ExcelWriter 클래스 - 데이터프레임이 여러 개일 때

In [ ]:

import numpy as np
import datetime as dt

data = [[dt.datetime(2020, 1, 1, 10, 13), 2.222, 1, True],
        [dt.datetime(2020, 1, 2), np.nan, 2, False],
        [dt.datetime(2020, 1, 2), np.inf, 3, True]]

df = pd.DataFrame(data=data,
                  columns=["Dates", "Floats", "Integers", "Booleans"])
df.index.name = "index"
df

In [ ]:

df.to_excel("written_with_pandas.xlsx", sheet_name="output",
            startrow=1, startcol=1, index=True, header=True,
            na_rep="<NA>", inf_rep="<INF>")

In [ ]:

with pd.ExcelWriter("written_with_pandas2.xlsx") as writer:
    df.to_excel(writer, sheet_name="Sheet1", startrow=1, startcol=1)
    df.to_excel(writer, sheet_name="Sheet1", startrow=10, startcol=1)
    df.to_excel(writer, sheet_name="Sheet2")

7.3 판다스의 한계¶

• 파일로 작성할 때 제목이나 차트를 넣을 수 없다.
• 엑셀의 헤더와 인덱스 기본 형식을 바꿀 수 없다.
• 판다스는 오류 셀을 모두 NaN으로 변환하므로 스프레드시트의 오류를 검색할 수 없다.
• 아주 큰 엑셀 파일을 다룰 때는 추가 설정이 필요하다.

7.4 요약¶