[Pandas] 29. 데이터 프레임을 칼럼 폭 조절, 첫 행 고정, 필터 추가하여 엑셀 저장하기 (feat. ExcelWriter)

---

안녕하세요~ 꽁냥이에요. Pandas에서는 ExcelWriter를 이용하여 데이터 프레임을 단순히 엑셀로 저장할 뿐 아니라 여러 가지 옵션을 추가한 상태로 저장할 수 있어요. 이번 포스팅에서는 ExcelWriter을 이용하여 Pandas 데이터 프레임을 1) 칼럼 이름에 따라 엑셀 칼럼 폭을 조정하고 2) 첫 행을 고정시키며 3) 필터를 추가한 상태로 엑셀(Excel)로 저장하는 방법을 알아보겠습니다.

---

   ExcelWriter을 이용한 엑셀 파일 저장

ExcelWriter는 첫 번째 인자로 엑셀 파일을 저장할 경로를 받습니다. 그리고 engine 인자는 엑셀 전용 모듈을 선택하는데 'openpyxl' 또는 'xlsxwriter'를 주로 사용합니다. 꽁냥이는 'xlsxwriter'를 주로 사용합니다. 왜냐하면 'xlsxwriter'의 문서가 굉장히 잘 정리되어 있기 때문입니다.

 

이제 코드를 통해 사용법을 알아보겠습니다. 아래 코드는 데이터 프레임을 생성하고 칼럼 이름에 맞게 엑셀 칼럼 폭을 조정하고 필터를 추가하고 첫 행을 고정시켜서 엑셀 파일로 저장합니다.

 

import pandas as pd

## 데이터 프레임 생성
data = {
    'STUDNET_NAME':['Park', 'Na', 'Yun', 'Moon'],
    'STUDNET_ID':[2001, 2002, 2003, 2004],
    'MATH_SCORE':[100, 70, 100, 8]
}
df = pd.DataFrame(data)

def get_width(test_str):
    import string
    import re
    letter_to_width = {
        'lower' : 0.95, ## 영어 소문자 하나 당 칼럼 폭
        'upper' : 1.18, ## 영어 대문자 하나 당 칼럼 폭
        'digit' : 1, ## 숫자 하나 당 칼럼 폭
        'korea' : 1.85, ## 한국어 하나 당 칼럼 폭
        'other' : 0.95 ## 나머지
    }
    width = 0
    for c in test_str:
        if c in string.ascii_lowercase:
            width += letter_to_width['lower']
        elif c in string.ascii_uppercase:
            width += letter_to_width['upper']
        elif c in string.digits:
            width += letter_to_width['digit']
        elif re.match(r'[ㄱ-힣]', c):
            width += letter_to_width['korea']
        else:
            width += letter_to_width['other']
    return width

file_path = 'test_excel_file.xlsx'
with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    
    ws = writer.sheets['Sheet1']
    ## 칼럼 폭 조절
    for i, col in enumerate(df.columns):
        width = get_width(col)
        ws.set_column(i, i, width+1)
        
    ws.autofilter(0, 0, df.shape[0]-1, df.shape[1]-1) ## 필터 추가
    ws.freeze_panes(1, 0) ## 첫 행 고정

 

line 11~33

문자열에 맞게 칼럼 폭을 반환하는 함수입니다. 실제로 꽁냥이가 실험을 통해서 얻은 글자당 최적 폭을 적용했습니다. 예를 들어 영어 대문자는 한 글자당 1.18의 폭을 갖게 했습니다.

 

line 36

with 문을 사용하여 ExcelWriter를 불러옵니다. 이때 첫 번째 인자는 엑셀 파일 저장 경로, engine 인자에는 'xlsxwriter'로 설정했습니다.

 

line 41~43

이때 set_column을 이용하여 문자열에 맞게 엑셀 칼럼 폭을 지정합니다. set_column 사용법은 여기를 참고해주세요.

 

line 45~46

데이터 필터를 추가하고 (line 45) 첫 행을 고정합니다(line 46). autofilter 사용법은 여기, freeze_panes 사용법은 여기를 참고해주세요.

 

위 코드를 실행하고 엑셀 파일을 실행하면 다음과 같이 여러 설정들이 적용된 것을 알 수 있습니다.

 

---

이번 포스팅은 꽁냥이가 엑셀을 저장할 때 일일이 필터 추가하고 칼럼 폭 조절하기 귀찮고 거기에 첫 행 고정하는 게 너무 번거로워서 이를 자동으로 해결하는 방법을 찾아보다가 알게 되었습니다. XlsxWriter 모듈과 Pandas는 연계가 잘되어 있어서 여기에 가보시면 여러 좋은 기능들이 있으므로 이를 응용하면 생산성 높은 데이터 분석을 하실 수 있을거예요.

 

다음에도 좋은 주제로 찾아뵐 것을 약속드리며 이상 포스팅 마치겠습니다. 지금까지 꽁냥이의 글 읽어주셔서 감사합니다.

 

- 참고 자료 -

https://xlsxwriter.readthedocs.io/pandas_examples.html#pandas-examples

---