(키움증권 해외선물) 1분봉 데이터 받기 (2) 데이터 한 파일(엑셀)로 합치기 (concat 함수)

목 차
1. 들어가며
2. 사전설명
   1) os.listdir() 모듈
   2) pandas
   3) concat 함수
   4) 파일 합쳐지는 순서
3. 코드 설명
4. 전체 코드
5. 마치며

1. 들어가며

지난 글에서는 영웅문G에서 1분봉 데이터를 다운받는 방법을 알아보았다.

1분봉을 모아야겠다고 마음먹었지만, 하루 8~9시간마다 다운 받는 건 생각보다 어렵다.

 

갑작스러운 저녁 약속, 빠지기 힘든 회식 등으로

일부 데이터가 누락되는 건 어쩔수 없는거 같다.

 

아무리 자동매매를 구현하고 있다고 해도,

최소한의 인간관계 유지를 위해 참석이 필요한 경우가 있기 때문이다.

※ 향후 설명할 내용은 아래와 같다.
 (1) 영웅문G에서 1분봉 데이터 엑셀로 받기
 (2) 데이터 한 파일(엑셀)로 합치기(concat 함수)
 (3) 데이터 합친 후 수정사항

 

생업이 바빠서 1분봉을 다운받아 한 파일로 합치는 것도

생각보다 시간이 많이 걸린다.

 

영웅문에서 제공하는 데이터는 xls 확장자 파일이다.

이유는 알 수 없지만, xls 확장자는 편집이 조금 불편했다.

 

필자도 몸이 피곤할 때는 데이터만 받아두는 경우도 많다.

주말에 한 파일로 합치자고 하는데, 생각보다 귀찮다.

 

그.래.서!

이번 글에서는 파이썬을 통해 폴더 내의 xls 파일들을

xlsx로 합치는 방법을 코드로 생각해 보았다.

(또다시... 귀차니즘이 발동했구나 ㅠㅠ)

---

2. 사전설명

os.listdir(), pandas 모듈(concat 함수)를 활용할 것이다.

 

1) os.listdir() 모듈

• 활용 개념 : os.listdir()은 지정한 경로 내의 모든 파일과 리스트(list)를 반환
• 활용 형태 : os.listdir(경로)

 

2) pandas

• 활용 개념 : 여기서는 데이터를 ExcelWriter을 활용하여 엑셀에 기록한다..
• 활용 형태 : with pd.ExcelWriter(경로, 형태~~ )

 

3) concat 함수

• 활용 개념 : pandas모듈을 활용하여 두개의 데이터프레임을 합치고 싶을 때 사용
• 활용 형태 : pd.concat(데이터프레임)

 

4) 파일 합치는 순서

os 모듈과 pandas 모듈을 각각 활용하여 엑셀(확장자 xls)을 합친다.

사전작업으로 2가지 정도는 해야 한다. -_-+

①바탕화면에 test_concat 이름의 빈 폴더를 만든다. (경로 : C:\Users\User\Desktop\test_concat)

②합치고자 하는 파일을 빈 폴더에 복사해 온다.

 

※ 한 개 파일로 만들때 위에서부터 아래로 읽는다.

    맨 위 파일부터 붙이고, 마지막 파일을 끝에 붙인다.   

  → 임의로 파일을 숫자(1, 2, 3....)로 변경하여, 붙이는 순서를 바꿀 수도 있다.

그림1. 위에서 아래로 데이터를 붙인다.

---

3. 코드 설명

pandas와 os 모듈을 사용한다.

그림2. 여러개의 엑셀(xls) 파일을 한개의 파일(xlsx)로 합친다.

 

1줄 : 파일 경로를 확인(5줄, 6줄)하고, 파일 및 리스트를 반환(3줄, os.listdir)받기 위해 os모듈을 임포트한다.

2줄 : 엑셀 파일(9줄)을 읽고, 데이터프레임 파일을 합치고(13줄), 엑셀에 기록(17줄)하기 위해 pandas모듈을 임포트한다.

 

4줄 : os.listdir 모듈을 활용하여 경로(.../test_concat)에 있는 파일을  minute_candles 변수에 인스턴스 시킨다.

5줄 : 경로(.../test_concat)를 dir_1 변수에 넣는다.

6줄 : 경로(.../test_concat/minute_bong.xlsm)를 dir_2의 변수에 넣는다. 16줄에서 엑셀에 기록할 때 쓰인다.

 

8줄 : for문과 len 함수를 활용하여, 5줄의 경로(.../test_concat)에 있는 파일을 불러온다.

         len 함수는 문자열의 개수를 반환한다.

         필자는 5개의 파일을 활용할 것이므로,

         len(minute_candles)은 5를 반환한다.

         → 즉, for i in range(len(minute_candles))는 for i in range(5)를 의미한다.

 

9줄 : 여기서 상당히 헤맸다. -_-+

         pandas를 활용하여 엑셀 파일을 읽어온다.(= 데이터를 가져온다)

         다만, 경로를 제대로 설정해 주고, 엔진(engine)은 'xlrd'로 써주어야 한다.

       ① 경로설정을 잘 이해하자.

           ▶ dir_1 + " / " : 5줄 경로의 하위 폴더(" / ")를 뜻하며

           ▶ + mintue_candles[i] : 위 경로에서 첫번째, 두번째....마지막 파일을 뜻한다.

                                                  (for 문으로 돌리면서 파일을 받아온다)

       ② engine = 'xlrd'

           ▶ 이 부분이 약간 눈에 띈다.

                pd.read_excel 구문을 쓰면,

               왠만하면 'openpyxl'을 쓰는데, 여기서는 'xlrd'를 썼다.

           ▶ 이유는??? 키움에서 제공하는 1분봉 엑셀 파일의 확장자가 xls이기 때문이다.

                xls는 xlrd 엔진을 활용해야 파일이 열리고 기록되고, 닫히는 걸 확인했다.

 

             ※ 위의 engine='xlrd' 때문인지는 모르겠지만, 파이참에 아래의 메시지가 뜨는데 그냥 무시했다.

                 파일은 하나로 합쳐지기 때문이다.

                  *** No CODEPAGE record, no encoding_override: will use 'iso-8859-1'

             ※ 위 에러 메시지(?)가 출력되지 않고, 더 좋은 방법이 있으면 댓글로 알려주시면 감사드린다.

 

11줄 : 데이터프레임 형태인 minute_cancels[i]에서 [0 : 601] 줄만 선택한다.

             ※ print(type(minute_cancels[i]))로 찍어보면

                 태형(type)은 < class 'pandas.core.frame.DataFrame' > 로 출력된다.

 

             ※ [0 : 600] 으로 선택할지, [0 : 601]로 선택할지?

 

※ 사용자 판단에 따라 0~600줄로 할지, 0~601줄로 정할지 그 적용은 다를 것이다.

① 필자는 [0 : 601] 줄로 선택하였다.

    ☞ 이유는? 0~600줄에서 0줄은 구분을 나타내고, 1~600은 600개의 데이터를 의미한다.

         0~600줄로 붙이면, 빈 줄 없이 붙어서, 어디서 다음 파일을 붙였는지 알 수 없다.

 

②어디서 붙여야 되는지 알아야 하는 이유?

    ☞ 8~9시간씩 파일을 붙이면, 중복되는 데이터가 있다.

         중복 데이터를 삭제하기 위해서이다.

 

③결론 : [0 : 601]으로 설정하여, 중복 데이터를 쉽게 삭제하자.

 

13줄 : 이 글의 핵심이다.

           8줄~11줄의 for로 받아온 데이터프레임 파일들을

           pd.concat(minute_candles)을 사용하여 한 파일(minute_candles_total)로 합친다.

 

15줄 : 6줄의 경로(dir_2)에 파일이 없으면,

16줄 : 6줄의 경로(dir_2)에 openpyxl 엔진을 활용하여 파일을 만들어라(mode = 'w')

17줄 : 13줄에서 만든 통합된 데이터(minute_candles_total)를 엑셀에 보내라(to_excel)

           (인덱스 없고, 시트명은 sheet1로 설정)

 

19줄~21줄 : 파일이 있으면 더하라(mode='a')

                     19줄~21줄을 활성화시키면 < 그림3 > 에러 발생한다.

                     ☞ 결론 : 19줄~21줄은 주석 처리하자!

그림3. "파일이 있는 경우"로 데이터를 추가하려는 경우, 에러가 발생

 

23줄~24줄 : 중복된 1분봉이 있을 수 있으므로, 리마인드 차원에서 print문을 넣었다.

---

4. 전체 코드

전체코드는 아래와 같다.

파일을 다 합치면, 필터를 걸고 600, 1200, 1800줄 정도에는 노란색으로 표기하자.

 

A열에 필터를 걸고 빈칸만 선택하고,

빈칸에 노란색으로 채색하고 필터를 풀면, 각각의 파일을 어디에서 붙였는지 알 수 있다

☞ 빈칸 노란색으로 설정한 이유는 빈칸 위/아래로 중복 파일을 삭제하자는 취지이다.

 

import pandas as pd
import os

minute_candles = os.listdir('C:/Users/User/Desktop/test_concat')
dir_1 = 'C:/Users/User/Desktop/test_concat'
dir_2 = r'C:/Users/User/Desktop/test_concat/minute_bong.xlsx'

for i in range(len(minute_candles)):
    minute_candles[i] = pd.read_excel(dir_1 + "/" + minute_candles[i], engine='xlrd')

    minute_candles[i] = minute_candles[i][0:601]    # 원래는 0~600이 맞음, 0~601로 한칸 더 설정(600 → 601) 설정한 이유는, 붙인 지점이 어디인지 확인하기 위함

minute_candles_total = pd.concat(minute_candles)

if not os.path.exists(dir_2):
    with pd.ExcelWriter(dir_2, mode='w', engine='openpyxl') as writer:
        minute_candles_total.to_excel(writer, index=False, sheet_name='Sheet1')

# else:
#     with pd.ExcelWriter(dir_2, mode='a', engine='xlrd', if_sheet_exists='overlay') as writer:
#         minute_candles_total.to_excel(writer, header=False, index=False, sheet_name= minute_candles[i])

print("1분봉 엑셀 취합이 완료되었습니다.")
print("중복 1분봉 등 확인하시면 됩니다.")

---

5. 마치며

파이썬을 통해 엑셀 파일(xls)을 한 개의 엑셀 파일(xlsx)로 통합하는 방법을 알아보았다. 주말이나 연휴 등 비교적 여유가 있을 때 파일을 합칠 경우에 유용하게 사용할 수 있었다.

 

파이썬의 concat 함수가 정말 편하다는 생각이 든다. 향후 설명하겠지만, 엑셀 concat 함수도 필자를 정말 편하게 만들어 주었다. 역시 파이썬과 엑셀은 알면 알수록 매력적이다. ^^

 

귀차니즘과 코딩에 대한 권태가 올 때쯤,

한번 정도는 엑셀 파일을 더해보는 것도 좋아 보인다.