나는 엑셀 대신 파이썬을 사용한다.

나는 엑셀 대신 파이썬을 사용한다.

MS 오피스 중 엑셀(Excel)은 학교, 가정, 사무실, 관공서 등에서 가장 광범위하게 사용하는 Tool 중에 하나라는 것은 어느 누구도 부인할 수 없을 것입니다.

엑셀을 사용하는 이유는 개인마다 다르겠지만 주로 데이터(또는 자료)를 정리, 집계, 분석 등을 위해에 많이 사용하고 있습니다. 엑셀에서 제공하는 함수를 이용하여 수학적 계산을 쉽고 빨리하기 위해서도 엑셀을 사용하고 있으며 때로는 차트 그리기를 통해 데이터를 가시화 하기도 합니다. 단순 반복 작업이나 복잡한 작업을 자동화하기 위해서 매크로, VBA와 같은 기능들도 자주 활용합니다.

저의 개인적인 의견으로 엑셀에서 제공하는 수 없이 많은 기능들 중에 VBA를 제외하고 으뜸으로 생각하는 다섯 가지는 아래와 같습니다.

• 특정 조건만 선택하는 필터 기능
• 데이터를 내림차순 / 오름차순으로 정렬하는 기능
• 원하는 값을 찾기 위한 vlookup / hlookup 기능
• 수학 또는 통계를 계산하기 위해 제공하는 함수 기능
• 데이터를 가시화 하기 위해 차트 그리기 기능

엑셀의 파워풀한 기능은 정말 우리의 다양한 업무 처리를 효과적으로 할 수 있도록 도와주지만 이러한 작업은 누가 해야 할까요?

한 번 생각해보세요

엑셀을 열어서 지속적으로 데이터를 입력하고 마우스로 셀을 클릭하여 선택하고 선택한 셀을 복사하고 붙여넣기 등 때로는 내가 지금 뭐 하고 있지? 라고 생각될 정도로 단순 업무들의 반복 연속입니다.

더구나 엑셀의 여러 파일에서 복수의 시트(sheet)를 오가면 작업해야 하는 업무가 있다면 최악의 상황을 맞이하게 됩니다.

파이썬은 위의 다섯 가지 기능 뿐만 아니라 엑셀에서 제공하는 대부분의 기능들이 구현 가능합니다. 그것도 몇 줄의 코드 작성만으로 가능하며 사용하는 컴퓨터에 엑셀이 설치되어 있을 필요도 없습니다.

오늘 이후부터는 단순 반복되는 엑셀 업무를 파이썬이 대신할 수 있도록 만들어 놓고 그 시간에 커피 한 잔의 여유를 즐기세요.

파이썬으로 엑셀 자동화하기 시작해 봅시다.

시작하기에 앞서 예제로 진행할 데이터 준비가 필요합니다. 한국 거래소에서 KOSPI에 상장 회사 데이터를 가지고 왔습니다.

직접 한국 거래소 사이트 방문하여 다운로드 하거나 아래의 유첨 다운 받아서 사용하셔도 됩니다. 파이썬 코드를 작성할 폴더에 다운 받은 파일을 저장하면 됩니다.

KOSPI상장회사.xlsx

1. 엑셀 파일의 데이터 불러오기

엑셀 파일의 데이터를 불러오기 위해서는 pandas 라이브러리를 먼저 import 하고 read_excel()을 이용하여 엑셀 파일로부터 데이터를 가져오면 됩니다. 함수의 인자로 파일 명을 입력하면 됩니다.

2. 위에서 엑셀에서 가져온 데이터들이 저장되어 있는 kospi_df 을 이용하여 필터 기능을 확인해봅시다.

우리나라 시가총액 1위 기업인 삼성 전자의 업종은 '통신 및 방송 장비 제조업' 입니다. 그러면 업종에서 '통신 및 방송 장비 제조업'만 필터링 하기 위해서는 해당 column(업종)이 찾고자 하는 항목(통신 및 방송 장비)가 같은지 조건문만 추가하면 됩니다.

LG전자, 대동전자, 대유플러스, 삼성전자, 휴니드테크놀러지스 총 5개의 회사가 필터링 되는 것을 확인할 수 있습니다.

참고로 전체 업종 리스트를 보고 싶다면 unique()라는 함수를 이용하여 kospi_df['업종'].unique() 을 실행하면 업종 column에 있는 중복된 항목은 제외한 전체 리스트를 보여줍니다.

복수의 조건 필터도 가능합니다. 위에서 찾은 조건에 추가로 업종이 '음식점업'인 조건을 추가해 보겠습니다.

각각의 조건을 괄호'( )'로 구분하고 OR 조건은 '|' 를, AND 조건은 '&' 를 사용하여 찾고자 하는 두 개 이상의 조건을 아래와 같이 입력하면 됩니다.

실행해 보면 신세계푸드라는 회사 하나가 추가된 것을 확인할 수 있습니다.

3. 다음은 엑셀의 내림차순, 오름차순 정렬을 파이썬으로 대신하여 봅시다.

'종목코드' 열을 기준으로 오름차준 정렬을 해보겠습니다.

sort.values() 라는 함수를 사용하면 되는데, 함수의 인자 중 by에는 정렬의 기준 열('종목코드')을, ascending에는 오름차순이면 True, 내림차순이면 False를 입력하면 됩니다.

종목 코드가 20에서 부터 900140까지 오름 차순으로 정렬된 것을 확인할 수 있습니다.

4. 다음은 엑셀의 다른 파일에서 원하는 값을 찾아오는 VLOOKUP 기능을 파이썬으로 구현해 봅시다.

엑셀의 버전에 따라 조금은 다르겠지만 다른 파일에서 원하는 값을 찾아오는 작업은 생각처럼 쉬운 작업이 아닙니다.

위에서 사용하던 파일에는 시가총액이 없습니다. 종목별로 시가 총액을 다른 파일에서 가져와서 위에서 사용하던 파일에 추가해봅시다.

시작하기 전에 종목별 시가 총액이 있는 파일을 한국 거래소에서 가져 오겠습니다.

그리고 기존에 사용하던 파일도 열이 너무 많아 보기 불편해서 몇 개를 삭제하여 다시 첨부합니다.

KOSPI시가총액.xlsx

KOSPI상장회사_1.xlsx

VLOOKUP은 항상 기준이 되는 열(column)이 필요합니다. 이것을 파이썬에서는 set_index()함수를 이용할 수 있습니다. set_index() 함수의 파라미터로 기준으로 하고 싶은 열의 이름을 아래와 같이 입력하면 됩니다.

여기서 inplace=True는 결과를 변경하도록 하는 것입니다.

   kospi_df.set_index('기업명', inplace=True)

다운로드 받은 파일을 동일 폴더에 저장 후 'KOSPI상상회사_1' 파일과 'KOSPI시가총액' 파일을 위에서 하였던 방법과 동일한 방법으로 파일에서 불러오기 합니다. 'KOSPI시가총액' 파일은 조금 응용하여 불러오기 하면서 기준이 되는 열('종목명'으로 되어 있음)을 미리 지정하였습니다. 결과는 price_df 에 저장합니다

그리고 기존 kospi_df 에 '기업명'을 기준 열로 변경하고 kospi_df에 '시가총액' 이라는 열을 만들어 VLOOKUP의 결과를 저장합니다.

시행해보면 아래와 같이 기업 명에 일치하는 시가 총액이 자동으로 추가 되었습니다.

오늘 포스팅이 많이 길어졌네요.

파이썬으로 피벗과 차트 그리기, 함수 사용하기 등은 다음 포스팅에 이어서 올리겠습니다.

파이썬을 이제 입문하셨다면 Pandas가 익숙하지 않아서 이상해 보일 수 있겠지만 단지 몇 줄의 코드 만으로 엑셀의 작업을 대신할 수 있다는 것을 정렬, 필터, VLOOKUP 등의 대표적인 예로 설명 드렸습니다.

궁금한 점 있으면 댓글 주세요~