(파이썬) pandas와 os모듈을 활용하여 데이터를 엑셀로 보내기

1. 들어가며

키움증권에서 수신받은 데이터를 엑셀 파일에 저장해야 할 때가 있다.
주가의 흐름, 패턴 등을 파악할 때 엑셀은 발군의 기능을 보인다.

이 글에서는 pandas와 os모듈을 활용하여 데이터를 엑셀로 저장하는 방법을 알아볼 것이다.
→ 나중에는 1분봉을 엑셀의 각 시트에 저장하는 내용을 소개할 예정이다.

• pandas 모듈 : 데이터를 행과 열 형태로 정리해 주는 모듈 (pandas 1.4.2 버전 이상으로 업데이트 필요)
• os 모듈 : 파이썬에 내장된 모듈이며, 윈도우 운영체제에서 제공되는 기능을 파이썬에서 활용하도록 함

---

2. 코드 구현

아래는 데이터프레임 형태로 저장된 3가지 데이터(4줄~6줄)이다.

< 그림1. pandas와 os모듈을 활용하여 엑셀에 저장하기 >

1줄 : 데이터프레임 형태(4줄~6줄)의 자료 활용을 위해 pandas 모듈을 임포트함
2줄 : 윈도의 바탕화면의 경로(12줄)를 활용하기 위해 os 모듈을 임포트함

4줄~6줄 : 딕셔너리 형태로 날짜, 코드번호, 가격을 데이터프레임 형태로 나타냄

8줄~10줄 : 딕셔너리 형태(4줄~6줄)을 df1 ~ df3까지의 변수에 담는다. (4~6줄 코드가 길다)
12줄 : 이 글의 핵심이다. 엑셀의 경로를 정해준다.

14줄 : dir경로에 파일이 있으면, 아래 실행
15줄 : pandas에서 제공하는 ExcelWriter을 활용하여 12줄의 dir 경로에 있는 파일에 대해,
파일이 있으면 추가(append, "a")하고, openpyxl을 활용하고,
시트가 존재하면 해당 시트에 추가하여 데이터를 기록하라
16줄~18줄 : 8줄의 df1을 엑셀로 보내고(to_excel) 써라.
header : False로 설정하여 출력하지 말라는 뜻이다.
(header은 4줄의 data, code, price 이다)
index : False로 설정하여 출력하지 말라는 뜻이다.
(index은 그림1에는 없지만,
데이터프레임의 맨 왼쪽들에 나타나는 0,1,2,3 등이다)
sheet_name : 시트명은 df1_종목으로 임의로 정함
startrow : 받아온 데이터의 출력을 시작하는 줄을 정한다.
df1_종목 시트의 가장 아래 줄(max_row)에 쓴다.
→ 즉 startrow = writer.sheet['df1_종목'].max_row는
df1_종목 시트에 데이터가 있으면,
기존에 있는 데이터의 바로 밑에 쓴다는 것이다.
* 엑셀의 "ctrl + 방향키↓"와 유사하다.
※ 17줄~18줄은 16줄과 유사하며, 설명이 반복되므로 생략한다.

20줄 : 14줄의 if문과 상응하는 else이며, dir 경로에 데이터가 없으면,
21줄 : ExcelWriter을 활용하여, dir 경로에 엑셀 파일을 만들고(mode = 'w')
pandas에서 제공하는 openpyxl 엔진을 활용해서 기록하라.
22줄~24줄 : 8줄의 df1을 시트명이 "df1_종목" 시트에 넣어라.
df1의 데이터프레임 자료를 index는 출력하지 말고,
시트명이 df1_종목의 시트에 넣어라.
※ 23줄~24줄은 22줄과 유사하며, 설명이 반복되므로 생략한다.

26줄 : 24줄까지 실행을 위해 shift+f10을 누르면,
파이참의 실행창에 아무것도 안떠서, 임의로 넣었다.

< 그림2. 위의 메시지와 함께 바탕화면에 엑셀이 생성된다 >

 

※ 아래와 같이 바탕화면에 "minute_data" 파일이 생기고,
엑셀 시트가 각각 생겨서 데이터가 저장된다.

< 그림3. 바탕화면에 생성된 엑셀 파일 >

---

3. 전체 코드

import pandas as pd
import os

minute_data_code1 = {'data': ['2022-09-29'], 'code': ['005930'], 'price': [1500]}
minute_data_code2 = {'data': ['2022-09-29', '2022-09-30'], 'code': ['005930', '000660'], 'price': [1600, 3200]}
minute_data_code3 = {'data': ['2022-09-30', '2022-10-01', '2022-10-02'], 'code': ['005930', '000660', '066570'], 'price': [1800, 3700, 5700]} # 066570 LG전자

df1 = pd.DataFrame(minute_data_code1)
df2 = pd.DataFrame(minute_data_code2)
df3 = pd.DataFrame(minute_data_code3)

dir = r'C:\Users\User\Desktop\minute_data.xlsx'  # 경로 설정

if os.path.exists(dir):
    with pd.ExcelWriter(dir, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
        df1.to_excel(writer, header=False, index=False, sheet_name='df1_종목', startrow = writer.sheets['df1_종목'].max_row)
        df2.to_excel(writer, header=False, index=False, sheet_name='df2_종목', startrow = writer.sheets['df2_종목'].max_row)
        df3.to_excel(writer, header=False, index=False, sheet_name='df3_종목', startrow = writer.sheets['df3_종목'].max_row)

else:
    with pd.ExcelWriter(dir, mode='w', engine='openpyxl') as writer:
        df1.to_excel(writer, index=False, sheet_name='df1_종목')
        df2.to_excel(writer, index=False, sheet_name='df2_종목')
        df3.to_excel(writer, index=False, sheet_name='df3_종목')

print("\n""pandas모듈과 os모듈을 활용하여 데이터를 엑셀로 보냅니다.")

---

4. 주의사항

위의 코드는 2번 이상 실행해 보고, 엑셀을 확인하는 게 좋다.(shif + f10, 2번 누름)
한번 실행하고 엑셀을 열어서 데이터가 제대로 들어갔는지 확인할 것이다.
엑셀을 열고 실행을 누르면, 아래의 에러가 발생한다.
번거롭더라도, 한번 실행하고 데이터를 확인한 후 엑셀을 닫고, 또 실행해야 한다. ^^;

< 그림4. [Errno 13] Permission denied 오류 화면 >

---

5. 마치며

위 자료는 pandas와 os를 이용하여
키움증권 OpenAPI를 통해 1분봉을 받아올 때 활용할 것이다.

for문과 함께 사용하면, "종목코드"가 시트명으로 만들어서,
종목별로 각 시트에 저장할 수 있다.(to_excel)

시트별로 데이터를 저장하는 방법은
향후 1분봉을 엑셀에 저장할 때 설명하겠다.