[Python] Python(파이썬) 데이터 분석을 위한 Excel(엑셀) 활용 (1)

파이썬 엑셀 라이브러리

라이브러리	설명
openpyxl	https://openpyxl.readthedocs.io/ Excel 2010 read/write를 지원하며 Pandas의 read_excel 함수에서 내부적으로 사용한다.
pyexcel	https://pypi.org/project/pyexcel/ xls, xlsx, xlsm, ods, csv 등의 파일들에 대해 하나의 API로 접근을 지원한다.
xlsxwriter	https://xlsxwriter.readthedocs.io/ xlsx 포맷에 대한 생성을 풍성하게 지원한다.
xlrd	https://pypi.org/project/xlrd/ Excel spreadsheets에서 데이터를 추출한다.
xlwt	https://pypi.org/project/xlwt/ Excel 97/2000/XP/2003에 대한 read/write를 지원한다.
xlwings	https://pypi.org/project/xlwings/ Excel 프로그램 자동화 라이브러리로서 유일하게 Excel 프로그램에 의존적이다.

 

xlwings 라이브러리

• Excel 프로그램에 대한 자동화 라이브러리
• Excel의 장점과 파이썬의 장점을 결합하여 강력한 기능으로 사용 가능
• 다른 Excel 라이브러리들은 Excel 프로그램과의 연동이 아니라 파일 포맷을 지원하는 형태이기 때문에 
• Excel 실행 후에 파이썬을 사용하여 값의 read/write 등을 지원
• Excel의 매크로를 파이썬으로 구현 가능하도록 지원(VBA 대체 가능)
• Windows/MAC 지원
• Anaconda Python을 설치하면 포함되어 있음
  1. pip install xlwings 또는 conda install xlwings

Excel을 단독으로 사용하기 보다는 파이썬을 결합하여 수많은 라이브러리들을 연동한다면 작업 효율성을 크게 향상시킬 수 있다.

 

import xlwings as xw

xw.__version__

xlwings를 import하여 version을 위의 예제 코드와 같이 확인할 수 있다. xlwings를 전부 사용하기엔 길기 때문에 xw로 명명한다.

 

data = [
    ['apple', 'samsung', 'lg'],
    ['100', '200', '300'],
    ['140', '280', '420'],
]

xw.view(data)

예제 코드와 같이 2차원 데이터를 생성한 후에, view 함수를 실행하면 Excel 프로그램이 실행되며 내용을 확인할 수 있다. 데이터의 내용이 커도 Excel이 허용하는 범위 내에서는 모두 입력 가능하다.

 

import numpy as np

data = np.random.rand(3, 5) #3행 5열의 랜덤 데이터 생성
xw.view(data)

예제 코드와 같이 Numpy 라이브러리를 import한 후에, 임의의 데이터를 생성하고 'xw.view'를 사용하면 또 새로운 Excel이 실행되는 것을 확인할 수 있다. 이때 새로운 Excel을 실행하지 않고 기존의 Excel을 사용하기 위해서는 아래와 같이 사용하면 된다.

 

xw.sheets.active # 현재 활성화 된 sheets에 접근

xw.sheets.active를 사용하면 현재 활성화 된 sheets에 접근 가능하다. 따라서 이를 활용한다면 새로운 Excel을 실행하지 않고 기존의 활성화된 Excel을 사용 가능하다.

 

import numpy as np

data = np.random.rand(3, 5) #3행 5열의 랜덤 데이터 생성
xw.view(data, xw.sheets.active)

xw.sheets.active를 xw.view의 두 번째 전달 인자에 넣으면 기존에 열어놨던 '통합 문서1.xlsx'에 임의의 데이터를 생성한 결과들이 적용된 것을 확인할 수 있다. xw.view의 기능은 Excel 워크 시트의 전체 내용을 한 번에 바꾸는 것이다.

 

import pandas as pd

url = 'https://finance.naver.com/marketindex/exchangeList.nhn' # 네이버 증권 환율 정보 테이블
pd.read_html(url)

Pandas의 read_html을 사용하여 네이버 증권 환율 정보의 테이블을 가져올 수 있다. 그런데 dataframe 형식이 아님을 확인할 수 있고, 위 내용을 dataframe화 시켜주어야 한다. 그리고 아래 예제 코드와 같이 활용해본다.

 

import pandas as pd

url = 'https://finance.naver.com/marketindex/exchangeList.nhn' # 네이버 증권 환율 정보 테이블
df_list = pd.read_html(url)
df = df_list[len(df_list) - 1]

print(df.shape)
df.head()

행과 열의 갯수를 출력하고 환율 정보 테이블을 dataframe화 시킨 결과이다. 그런데 상단을 보면 column명이 정리가 되지 않은 상태로 사용되고 있음을 확인할 수 있다.

 

df.columns = [
    '통화명',
    '매매기준율',
    '현찰 - 사실 때',
    '현찰 - 파실 때',
    '송금 - 보내실 때',
    '송금 - 받으실 때',
    '미화환산율',
]
df.head()

column명을 정리하는 방법은 다양하지만, 예제 코드처럼 직접 지정하여 정리할 수도 있다. column 개수를 확인한 후에 지정을 하면 위와 같이 잘 정리된 결과를 확인할 수 있다.

 

df = df.set_index('통화명')
df.head()

index를 0부터 1씩 증가하도록 지정되어 있는 것들을 새롭게 지정할 수도 있다. set_index를 사용하게 되면 위와 같이 통화명을 기준으로 인덱스가 지정된 것을 확인할 수가 있는데, 이 결과는 기존의 dataframe을 변경한 것이 아니라 새로운 dataframe을 생성한 것이다. 따라서 head를 사용하기 위해선 다시 df에 저장해주어야 한다.

 

xw.view(df, xw.sheets.active)

df에 저장된 결과를 Excel에 반영하기 위해서는 xw.view를 사용한다. 예제 코드를 실행하면 파이썬을 통해 웹 사이트에서 크롤링한 내용을 Excel에 한 번에 손쉽게 업데이트 한 결과를 확인할 수 있다. 이 방법을 사용하지 않으면, 매 번 별도의 Excel 파일을 생성하고 작업해야하는 불편함이 있다.

만약 Excel의 시트가 여러 개가 있다고 하면 Excel에서 업데이트 하고 싶은 sheets를 활성화하고 예제 코드를 사용하면 해당 시트가 업데이트 된다.

 

# xw.Range('A1') # 하나의 cell을 지정
xw.Range('A1:C3').value

세 번재 시트를 업데이트 한 후에 예제 코드를 사용해본다. 사용된 Range는 Excel 범위를 지정할 수 있다. 범위를 지정한 후에 value를 사용하면 지정된 범위의 값을 읽어올 수 있다. 실제 예제 코드 결과를 보면 Excel의 (A1:C3) 범위 값을 2차원 리스트 형식으로 읽어온 것을 확인할 수 있다.

 

xw.Range('A1').expand()

하지만 Excel의 범위를 매번 계산하기 힘들다. 시작은 상단의 좌표를 지정하고, 범위 확장을 위한 expand를 사용해보면 연속된 데이터 값을 가진 범위들을 자동으로 선택해준다.

 

xw.Range('A1:A4').expand('right') # table, right, down

expand안의 인자는 'table'이 default로 사용되며 'right' 혹은 'down'을 통해 범위를 선택할 수도 있다. 예제 코드와 같이 (A1:A4)까지의 범위를 지정하고 expand에 'right'를 인자로 전달하게 되면 (A1:G4)까지 범위가 자동으로 지정됨을 확인할 수 있다.

 

data = xw.Range('A1:A4').expand().value
data

보통은 'table' 인자를 많이 쓰기 때문에 생략을 많이 한다. 따라서 expand 사용 후 value를 통해 범위 값을 파이썬의 참조 변수로 쉽게 읽어올 수 있다. 그리고 읽어온 참조 변수를 사용해서 데이터를 다양하게 정제 가능하다.