파이썬을 이용한 엑셀 데이터 병합

1. 블룸버그나 로이터가 없을 때

 

이런 상황에서도 커피 가격은 강세를 유지.

 

분석을 위해 ICE 거래소의 "Coffee Certified Stock" 데이터가 필요하다. 

 

블룸버그나 로이터 있었으면 검색 몇번으로 다운받을 수 있을텐데..

 

어쩔 수 없이 사이트에서 수집을 해야지..

 

https://www.theice.com/marketdata/reports/42

ICE Report Center - Data

 

 

2. 인터넷에 코드는 많지만

 

분명 인터넷에 코드는 많은데 항상 그렇듯 고려할 게 많다. 

 

파이썬에서 가장 많이 사용하는 엑셀 관련 모듈은 openpyxl 

 

그런데 얘는 xls를 못 읽어

 

그런데, ICE 커피 재고 엑셀 파일 형식은 xls....

 

xls을 xlsx로 일괄변환 할 수 있다면 좋겠는데 

 

https://convertio.co/kr/xls-xlsx/

XLS (EXCEL) XLSX (EXCEL) 변환 (온라인 무료) — Convertio

위 사이트에서 가능하긴 한데. 파일이 많을 때는 어떻게 해야하나..

 

어쩔 수 없이 파이썬에서 xls를 다룰 있는 xlrd 모듈을 설치.

 

Anoconda Prompt에서 pip install xlrd 입력해서 설치.

 

 

 

3. 날짜만 다른 동일 엑셀 파일 병합

 

기본적인 내용은 아래 사이트를 참조. 

 

https://m.blog.naver.com/PostView.nhn?blogId=jangsam24&logNo=221521900032&proxyReferer=https%3A%2F%2Fwww.google.com%2F

파이썬 판다스로 엑셀 내용 복사/통합하기

문제는 시트 모든 내용을 가져올 수 없으니까 필요한 행과 열만 가져오자.

 

좋은 코드는 아니고 우선은 동작하는데 만족.. 

import pandas as pd
import numpy as np
import os
import glob
from openpyxl import load_workbook  
 
location = os.getcwd()
excels = glob.glob('C:\\pytest\\coffeestock\\2018\\coffee_cert_stock_*.xls')
 
exl_list = []
for excel in excels:
    exl_list.append(excel)
    
print(exl_list)
 
if len(exl_list) != 0:
    df1 = pd.read_excel(exl_list[0])
    cnt = df1[['ICE Futures U.S.','Unnamed: 9']].loc[1:17].shape[0]
    df1 = df1[['ICE Futures U.S.','Unnamed: 9']].loc[1:17]
    df1.loc[cnt + 1] = np.nan
    
    for item in exl_list[1:]:
        df2 = pd.read_excel(item)
        cnt = df2[['ICE Futures U.S.','Unnamed: 9']].loc[1:17].shape[0]
        df2 = df2[['ICE Futures U.S.','Unnamed: 9']].loc[1:17]
        df2.loc[cnt + 1] = np.nan
 
        df_total = pd.concat([df1, df2], axis = 1)
        df1 = df_total
        
        df_total.to_excel('C:\\pytest\\coffeestock\\2018\\coffee_stock.xlsx', index = False, sheet_name = 'total')
        
else:
    print('통합파일 없어요')

 

 

 

4. 파일저장 형식을 xlsx로 하기 위해서는 openpyxl 모듈을 불러와야

 

df_total.to_excel('C:\\pytest\\coffeestock\\2018\\coffee_stock.xls', index = False, sheet_name = 'total')

 

엑셀 파일이 많을 경우 위 형태로 xls로 파일형식을 저장하면 

 

ValueError: column index (256) not an int in range(256) 

 

위와 같은 에러를 뱉어낸다.

 

xls 형식은 열을 256개만 지원하기 때문에..

 

생각해보면 엑셀도 잘 못하기 때문에 맨날 헤매는 듯.

 

첨부 파일로 연습해보자.

 

coffee_cert_stock_20180102.xls

0.03MB

coffee_cert_stock_20180103.xls

0.03MB

coffee_cert_stock_20180104.xls

0.03MB

coffee_cert_stock_20180105.xls

0.03MB

 

5. 삽질을 한참하고 보니 xls를 xlsx로 변환하는 라이브러리가 있다 ㅠ

 

http://blog.daum.net/jinsilban

진실반-풍경소리(정용만)

 

https://stackoverflow.com/questions/9918646/how-to-convert-xls-to-xlsx

how to convert xls to xlsx