파이썬 판다스 데이터 분석 : pivot, 엑셀 입력, 출력
바이낸스 비트코인 백테스팅 데이터를 엑셀에 정리했습니다. 이 엑셀 파일을 파이썬 판다스 read_excel() 함수로 불러온 후, 데이터프레임에 저장하고 나면, 그때부터는 파이썬 데이터 분석의 단계로 넘어갈 수 있습니다. 이번 포스팅에서는 엑셀, 파이썬 판다스 데이터프레임, pivot table로 변환, 그리고 다시 엑셀 파일로 저장하는 일련의 과정에 대해 알아보겠습니다.
글의 순서
파이썬 판다스로 엑셀 파일 원하는 부분 골라 읽기
판다스 데이터프레임 pivot table
파이썬 판다스 데이터프레임을 엑셀 파일로 저장
파이썬 코드 : 엑셀 파일 읽고, pivot_table로 가공한 후 엑셀 파일로 저장
파이썬 판다스로 엑셀 파일 원하는 부분 골라 읽기
지난 포스팅 파이썬 판다스로 엑셀 파일의 원하는 부분 골라 읽기라는 포스팅에서 엑셀 파일을 파이썬 판다스 데이터프레임으로 읽어왔었습니다.
여기서 가져온 데이터는 역시나 바이낸스 비트코인 변동성 돌파전략 백 테스팅 결과라는 포스팅의 결과로 만들었던 엑셀 파일입니다. 각 시간 간격 별로 K 값을 변화시켜가며 도출된 수익률을 아래 그림의 엑셀파일처럼 한 칸씩 채워가며 만들었습니다.
위 그림의 파일을 아래 링크를 통해 다운로드 받을 수 있습니다.
변동성 돌파전략 백테스팅 결과 다운로드
판다스 데이터프레임 pivot table
판다스 데이터프레임은 엑셀 표와 같은 형태입니다. 가로, 세로로 여러 개의 칸이 있고 이 칸에 데이터가 채워져 있다고 보시면 되겠습니다. 가로 칸을 행, 세로 칸을 열이라고 합니다.
피봇(pivot)은 행과 열을 회전시켜, 표의 구성을 바꾸는 작업입니다. 엑셀 파일이 파이썬 판다스 데이터프레임으로 변환되어 있는 상태입니다. 첫 번째 열은 ‘시간간격’, 두 번째 열은 ‘K’값, 세 번째 열은 ‘수익’입니다.
이를 파이썬 판다스 pivot_table() 함수를 이용하여 아래와 같은 형태의 표로 만드는 것이 목표입니다. 즉, K 값을 인덱스로, 각 열을 시간 간격으로 나타내는 것입니다. pivot_table() 함수의 인자로는 values, index, columns가 사용됩니다.
이를 pivot이라는 데이터프레임 변수에 담아둔 후 출력해보면, column이 숫자와 문자의 오름차순으로 정리된 것을 볼 수 있습니다. 판다스 데이터프레임에서 열을 바꿔주기 위해서는 리스트 안에 리스트를 넣은 후, 우리가 원하는 대로 열을 지정해주면 됩니다.
|
1 |
pd.pivot_table(df, values='수익', index=['K'], columns=['시간간격'] ) |
파이썬 판다스 데이터프레임을 엑셀 파일로 저장
파이썬 판다스로 엑셀 파일의 원하는 부분 골라 읽은 후, 판다스에서 pivot table을 이용해서 우리가 원하는 형태로 데이터를 가공하였습니다. 이제는 이렇게 가공한 데이터를 다시 엑셀파일로 저장해 보겠습니다.
(1) 하나의 엑셀 sheet로만 저장 : to_excel()
df는 pandas.DataFrame을 저장하고 있는 변수입니다. 이 변수 뒤에 점을 찍고 to_escel이라는 함수를 쓰면 엑셀파일로 저장해 줍니다. 이 함수의 매개변수로는 파일이름과 index 등이 있습니다. index는 index를 파일에 포함시킬지를 설정하는 것인데, 기본값이 포함시키는 것입니다. 인덱스를 포함하고 싶지 않다면, index=False로 넘겨줍니다.
(2) 둘 이상의 엑셀 시트로 저장 : pd.ExcelWriter()
엑셀파일 안에는 여러 개의 sheet를 쓸 수 있습니다. 하나의 파일에 다양한 형태의 데이터를 담을 수 있다는 것이 엑셀 파일의 장점입니다. 이 장점을 잘 살리기 위해서 2개 이상의 sheet데이터를 저장한다면 to_escel() 함수 대신, pd.ExcelWriter()를 사용합니다. 실습코드에서는with문 사용해서 파일을 열고난 후 닫는 것을 신경 쓰지 않도록 하였습니다.
컴퓨터 프로그램이 가볍게 잘 돌아가게 하기 위해서는 자원을 가져오고, 사용한 후, 반드시 반납해 줘야 합니다. 물론 파이썬 프로그래밍에서도 획득 ? 사용 ? 반납의 절차를 그대로 적용합니다. 자원의 반납이 가장 중요한 부분인데, 이 부분을 놓칠 때가 많습니다. with문은 이 반납의 절차를 자동으로 해결해 줍니다. 실습코드에서는 파일을 열어서, 쓰는 것 까지만 수행하는 것처럼 보입니다만, with문을 사용했으므로 파이썬 프로그램이 알아서 닫아 줍니다.
파이썬 코드 : 엑셀 파일 읽고, pivot_table로 가공한 후 엑셀 파일로 저장
실습코드01 구성
(1) 파이썬으로 엑셀파일을 불러온 후 판다스 데이터프레임 변수 df에 저장
(2) 판다스 pivot_table 함수를 이용하여 데이터 형태 변환한 후 데이터프레임 변수 pivot에 저장
(3) pivot 변수의 열 순서 변경
(4) pivot 변수를 엑셀 파일로 저장
(5) df와 pivot 변수를 엑셀 파일의 각각의 sheet에 저장 : with문 활용
참고로, 엑셀 파일을 불러오고, 쓸 때 절대경로를 간편하게 써 주기 위해 file_path라는 변수를 만들었습니다. 여기에 ‘+’를 사용해서 파일명까지 붙이면 파일의 위치까지 간단하게 만들 수 있습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
import pandas as pd import numpy as np #01) Excel 파일에서 원하는 부분만 불러온 후 Pandas DataFrame에 저장 # position = 'C:/_python/pandas/btc_price.xlsx' file_path = 'C:/_python/pandas/' df = pd.read_excel(io=file_path +'back_testing_result.xlsx', sheet_name='백테스팅결과', usecols='A:C', # index_col = 0, skiprows=0, skipfooter=4) print('\n', '#01') print(df) #02) 판다스 pivot_table 함수를 이용하여 데이터 형태 변환한 후 데이터프레임 변수 pivot에 저장 pivot = pd.pivot_table(df, values='수익', index=['K'], columns=['시간간격'] ) print('\n', '#02') print(type(pivot)) print(pivot) #02-1) 데이터프레임 변수 pivot의 index 확인 print('\n', '#02-1') print(pivot.index) #02-2) 데이터프레임 변수 pivot의 column 확인 print('\n', '#02-2') print(pivot.columns) #03) 데이터프레임 변수 pivot의 열(column) 순서 변경 pivot = pivot[['1m', '15m', '30m', '1h', '4h', '1d', '1w']] print('\n', '#03') print(pivot.columns) #04) 데이터프레임 변수 pivot을 엑셀파일로 저장 pivot.to_excel(file_path +'pivoted.xlsx') print('\n', '#04 : 엑셀파일 생성 : 1개의 sheet') print('파일경로와 이름: ', file_path +'pivoted.xlsx') #05) df와 pivot 변수를 엑셀 파일 각각의 sheet에 저장 with pd.ExcelWriter(file_path +'original+pivoted.xlsx') as writer: df.to_excel(writer, sheet_name='original') pivot.to_excel(writer, sheet_name='pivoted') print('\n', '#05 : 엑셀파일 생성: 2개의 sheet') print('파일경로와 이름: ', file_path +'original+pivoted.xlsx') |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
#01 시간간격 K 수익 0 1m 0.1 3605.59800 1 1m 0.2 4476.26600 2 1m 0.3 2443.42200 3 1m 0.4 278.57000 4 1m 0.5 357.74000 .. ... ... ... 58 1w 0.5 3187.74499 59 1w 0.6 2709.58190 60 1w 0.7 3303.86699 61 1w 0.8 3241.03790 62 1w 0.9 3178.20800 [63 rows x 3 columns] #02 <class 'pandas.core.frame.DataFrame'> 시간간격 15m 1d 1h 1m 1w 30m 4h K 0.1 3053.64599 4844.4599 3908.72000 3605.5980 5276.99199 3198.67390 4173.67899 0.2 1927.95390 3770.1699 2487.91399 4476.2660 4497.53300 2123.42999 2611.38990 0.3 1317.21890 2345.3650 1529.26500 2443.4220 3724.67590 2223.53000 3160.59190 0.4 1195.83900 2573.1800 1119.90990 278.5700 3665.90799 2381.90399 2541.85590 0.5 1205.58490 1850.9899 1435.26490 357.7400 3187.74499 1620.96000 1699.06490 0.6 679.19790 1496.3499 1362.11800 -881.9690 2709.58190 1238.20590 1711.95199 0.7 -473.79390 2018.7100 765.16190 -309.3059 3303.86699 838.91990 1670.67600 0.8 -193.54000 1341.1939 1105.15400 -1108.7859 3241.03790 309.71400 1585.95999 0.9 430.95290 663.6769 1621.23200 -610.2040 3178.20800 650.04899 1011.91200 #02-1 Float64Index([0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9], dtype='float64', name='K') #02-2 Index(['15m', '1d', '1h', '1m', '1w', '30m', '4h'], dtype='object', name='시간간격') #03 Index(['1m', '15m', '30m', '1h', '4h', '1d', '1w'], dtype='object', name='시간간격') #04 : 엑셀파일 생성 : 1개의 sheet 파일경로와 이름: C:/_python/pandas/pivoted.xlsx #05 : 엑셀파일 생성: 2개의 sheet 파일경로와 이름: C:/_python/pandas/original+pivoted.xlsx |
마치며 …
엑셒 파일을 파이썬 판다스 read_excel() 함수로 불러온 후, 데이터프레임에 저장하고 나면, 그때부터는 파이썬 데이터 분석의 단계로 넘어갈 수 있습니다. 이번 포스팅에서는 엑셀로 불러온 원본 데이터를 판다스 데이터프레임에 저장하고, pivot 함수를 이용해서 좀 더 이해하기 쉬운 형태로 바꾼 후, 다시 엑셀파일로 저장하는 방법을 알아보았습니다. 하나의 엑셀 sheet로 구성된 엑셀파일뿐만 아니라. 여러 개의 sheet를 가진 엑셀 파일로 저장하는 방법과 파이썬 with 문의 활용방법도 이 포스팅에서 강조하고 싶은 부분입니다. to_excel()과 pd.ExcelWriter() 함수를 기억해 주시기 바랍니다.
함께 참고하면 더 좋은 글 :
1. 바이낸스 코인거래소 API Key로 계좌에 접속하는 파이썬 프로그래밍
2. 파이썬 바이낸스 API로 시계열 데이터를 가져오는 파이썬 프로그래밍
3. 바이낸스 API 보안을 위한 환경 변수 설정 : 윈도우 10
4. 바이낸스 API 보안을 위한 리눅스 환경 변수 설정
5. 파이썬 바이낸스 API 시계열 데이터분석. 판다스 시간 처리
6. 바이낸스 비트코인 투자 백 테스팅. 파이썬 코인 투자 연습
7. 바이낸스 비트코인 변동성 돌파전략 백 테스팅 결과
8. 시계열 데이터 분석 : pandas CSV 파일 저장, 읽기
9. 디스코드 메신저로 만드는 알림 봇 : 파이썬 함수 예제
10. 파이썬 프로그래밍 시작
참고자료
[1] Python Tutorial Read Excel with Python Pandas
[2] Python Tutorial Write Excel with Python Pandas