[직장인이 알아두면 좋은 파이썬] openpyxl로 pandas 데이터 프레임 만들기

직장인들이 가장 많이 사용하는 프로그램은 엑셀일 것입니다. 사실 파이썬과 같은 프로그래밍 언어를 모르더라고 엑셀만 잘 쓰면 일하시는데 큰 문제는 없을 것입니다. 하지만 매일 반복되는 작업을 자동화 하기위해 파이썬을 사용하면 정말 평소에 20분 걸리는 일도 5분만에 처리가 가능합니다. 저 역시 파이썬과 sql을 사용해서 매일 반복적으로 하는 일을 자동화 하여 평소 15분 걸리는 일을 1분 안에 처리하고 있습니다.  

 

이처럼 매일 반복되는 일은 자동화를 통해 업무 효율을 극대화 할수 있습니다. 엑셀을 잘 쓰시는 분이시라면 매크로를 사용할 수도 있지만 파이썬을 배우는 여러모로 활용하기 좋기 때문에 파이썬을 하면 공부해보시는 것을 추천드립니다. 

 

그래서 이번에는 파이썬으로 엑셀을 자동화하는데 필요한 라이브러리인 openpyxl로 판다스 데이터 프레임을 만드는 과정을 공부해보고자 합니다. 

---

 openpyxl로 pandas 데이터 프레임 만들기

우선 엑셀로 주로 하는 자료분석 기능 자동화를 위해서 저는 판다스를 사용하고 이것을 다시 엑셀로 바꾸기 위해 openpyxl을 사용하고 있습니다.  

 

필요한 라이브러리 import

import pandas as pd
from openpyxl import Workbook
from itertools import islice

판다스와 openpyxl 그리고 조건을 걸어 반복을 위한 툴인 islice를 임포트 합니다. 

workbook과 샘플 데이터 입력

wb = Workbook()

ws3 = wb.create_sheet(title="Data")
ws3.cell(1,1,value="이름")
ws3.cell(1,2,value="나이") 
ws3.cell(1,3,value="취미") 
ws3.cell(2,1,value="홍길동")
ws3.cell(2,2,value="20") 
ws3.cell(2,3,value="카메라") 
ws3.cell(3,1,value="둘리")
ws3.cell(3,2,value="100") 
ws3.cell(3,3,value="마술") 
ws3.cell(4,1,value="하니")
ws3.cell(4,2,value="13") 
ws3.cell(4,3,value="달리기")

wb  = Workbook()은 openpyxl 사용시 기본적으로 만들어야 하는 시작점입니다. 이것을 실행하는 것은 엑셀프로그램을 실행한다고 생각하시면 될거 같습니다. 

 

그 다음은 ws(work sheet)를 생성하는데 wb.active로 가능하지만 저는 ws3 = wb.create_sheet(title="Data")를 사용하여 새로은 시트를 생성하여 그곳에 데이터를 입력하겠습니다. 

 

데이터를 입력하는 방법은 여러가지가 있지만 저는 ws3.cell(1,1,value="이름") 이런 방법으로 예제 데이터를 입력했습니다. 자동화를 한다면 반복문을 사용한다는 쉽게 데이터를 입력할수 있습니다. 

---

openpyxl을  판다스 데이터 프레임으로 변환 

판다스 데이터 프레임으로 변환을 위해 아래의 명령어를 사용할 예정입니다. 각각의 data, idx, cols은 리스트 형태로 입력해면 판다스 데이터 프레임이 손쉽게 만들어 집니다. 

pd.DataFrame(data=data, index=idx, columns=cols)

완성된 데이터 프레임 형태 

values로 한 행씩 데이터 가져오기

data = ws3.values
for rows in ws3.values:
    print(rows)
        
// 출력결과    
('이름', '나이', '취미')
('홍길동', '20', '카메라')
('둘리', '100', '마술')
('하니', '13', '달리기')

ws3.values는 제가 입력한 데이터를 튜플 형태의 iterator를 생성해주는 generator입니다. 그래서 반복문을 돌리면 제가 입력한 데이터가 튜플 형태로 나옵니다. 아마 그냥 data를 프린트하면  generator라고 밖에 표시 되지 않지만 list로 바꾸면 제가 입력한 정보가 리스트 형태로 보일 것입니다. 

인덱스와 컬럼을 리스트로 만들기

리스트로 만든 data를 가지고 인덱스와 컬럼 그리고 데이터를 분리하여 데이터 프레임으로 바꾸어 보겠습니다. 

// 원래 데이터
('이름', '나이', '취미')
('홍길동', '20', '카메라')
('둘리', '100', '마술')
('하니', '13', '달리기')

//컬럼
cols = next(data)[1:]
//출력
('나이', '취미')

//데이터를 리스트로 바꾸기 
data = list(data)
//출력
[('홍길동', '20', '카메라'), ('둘리', '100', '마술'), ('하니', '13', '달리기')]

// 인덱스
idx = [r[0] for r in data]
// 출력
['홍길동', '둘리', '하니']

// 데이터 
data = [islice(r, 1,None) for r in data]
// 출력
[<itertools.islice at 0x10f268950>,
 <itertools.islice at 0x10f268720>,
 <itertools.islice at 0x11e407770>]

컬럼에 넣을 리스트 만들기

cols = next(data)[1:]의 next는 반복되는 이터레이터를 하나씩 꺼내는 함수입니다. 그래서 첫번째 행인 이름, 나이, 취미를 꺼내 [1:]을 사용하여 1번 이후의 값인 나이와 취미를 cols에 저장하는 부분입니다. 그리고 next함수를 사용 후에는 2번째 행인 홍길동 부터 출력되는 것을 보실 수 있습니다. 

 

인덱스에 넣을 리스트 만들기

리스트 컴프리헨션을 이용해 홍길동, 둘리, 하니를 리스트로 바꾸고 인덱스에 넣겠습니다.  

 

데이터로 넣을 리스트만들기

역시 리스트 컴프리헨션을 이용해 데이터로 들어갈 리스트를 만들겠습니다. data = [islice(r, 1,None) for r in data] 여기서 islice(r, 1,None) 이 부분은 조건에 따라 슬라이스 해주는 함수입니다. 

그래서 r 부분에는 슬라이스할 데이터, 두번째 1은 스타트 부분, 세번째는 슬라이스할 갯수를 입력합니다. 

 

결국 islice(r, 1,None)은 r[1:]과 같은 결과입니다.  

 

정리하면 r은 ('홍길동', '20', '카메라')이고 1은 홍길동 다음은 '20'부터 슬라이스 해라이고 None은 끝까지 해라입니다. 그래서 (20, 홍길동)이 리스트에 저장이 되는 것입니다. 

 

데이터 프레임 만들기

이제 옆에 이 명령어를 사용할 준비가 끝났습니다. df = pd.DataFrame(data=data, index=idx, columns=cols)를 사용하여 데이터 프레임을 만드시면 됩니다. 

 

 마치며

이번 시간의 과정은 간단하면서도 몬가 복잡해 보일수 있습니다. 하지만 쉽게 생가하면 데이터 프레임을 만들기 위해서는 데이터와 인덱스 컬럼이 리스트 형태로 입력을 하면 되고 기존의 데이터를 어떻게 리스트 형태로 바꾸는지에 집중하시면 될거 같습니다. 

 

 

---

 참고 자료

https://openpyxl.readthedocs.io/en/stable/pandas.html

Working with Pandas and NumPy — openpyxl 3.0.10 documentation