Python pandas로 excel 파일 읽기/쓰기

Python pandas로 excel 파일 읽기/쓰기

 

가끔 데이터를 가지고 뭔가 해보려 하다보면, 원본 데이터가 xlsx 등의 엑셀 파일로 제공되는 경우가 있습니다. 가만 보면 공공데이터포털에서 파일로 제공되는 데이터 등에서 쉽게 볼 수 있습니다. 이러한 xlsx 파일을 파이썬 데이터프레임인 pandas로 읽고, 쓰는 간단한 예제를 보이려고 합니다.

 

Python Pandas로 Excel 파일 읽기

 

마침 기상청에서 다운로드 받은 2017년부터 2019년 서울 기온에 관한 엑셀파일이 위의 그림과 같이 있어서, 아주 단순하게 pandas 데이터프레임으로 읽어보겠습니다.

 

파이썬 pandas로 엑셀파일을 읽는 것은 다음과 같이 read_excel 함수를 이용하면 됩니다. 우선 아무런 옵션없이 파일명만 넣어봤습니다.

import pandas as pd

filename = '서울기온2017-2019.xlsx'

df_excel = pd.read_excel(filename)

print(df_excel)

 

위의 실행 결과가 아래와 같이 나오는데, 별다른 옵션을 넣지 않았다면 첫번째 시트를 읽어옵니다.

      지점 지점명  일시  평균기온(°C)  최저기온(°C)  최고기온(°C)
0     108  서울 2017-01-01       2.7      -1.6       6.9
1     108  서울 2017-01-02       5.0       1.8       9.2
2     108  서울 2017-01-03       2.0      -2.3       7.7
3     108  서울 2017-01-04       3.9       1.0       8.9
4     108  서울 2017-01-05       3.8      -0.1       7.3
  ...  ..        ...       ...       ...       ...
1090  108  서울 2019-12-27      -1.7      -4.6       2.6
1091  108  서울 2019-12-28       1.1      -3.6       6.1
1092  108  서울 2019-12-29       3.8       1.1       6.2
1093  108  서울 2019-12-30       2.7      -5.7       6.8
1094  108  서울 2019-12-31      -7.9     -10.9      -4.5

[1095 rows x 6 columns]

 

pandas가 생각보다 잘 만들어졌다고 느껴지는 것은 별다른 옵션을 주지 않았음에도 위의 컬럼 값들이 108은 numpy.int64로, 서울은 str, 2017-01-01은 Timestamp, 그외 numpy.float64 등으로 알맞게 데이터프레임으로 읽어옵니다.

 

위에서는 read_excel에 파일명만 넣었었는데, 빈번하게 사용될만한 옵션들을 넣어봤습니다.

pandas.read_excel(filename, 
                  sheet_name = '서울', 
                  header = None, 
                  names = ['일시','평균','최저','최고'], 
                  index_col = None, 
                  usecols = "C:F", 
                  dtype = {'일시':str, '평균':float, '최저':float, '최고':float},
                  skiprows = 32,
                  nrows = 28,
                  na_values = 'nan',
                  thousands = ',')    

 

• sheet_name :
  기본 값은 0 입니다. int 0이 첫번째 시트, 1이 두번째 시트입니다. 문자열로 sheet_name = "sheet1" 방법이나, [0,"sheet2"] 처럼 리스트 값이 들어갈 수 있습니다.
• header:
  어느 행(row)에 열(column)의 이름이 있는지를 지정하는 것으로 기본 값은 0으로 첫번째 줄입니다. None으로 하면 첫번째 줄(행,row)부터 바로 데이터로 받아드립니다.
• names:
  header가 None일 경우 열(column)의 이름을 지정해줍니다.
• index_col:
  각 행(row)의 이름이 위치한 열(column)을 지정합니다. 기본 값은 None입니다.
• usecols:
  기본 값은 None으로 모든 열을 다 불러옵니다. "A:E", "A,C,F:H" 등으로 원하는 열을 선택하여 읽어오게 할 수 있습니다.
• dtype:
  각 열의 데이터 타입(type)을 지정할 수 있습니다.
• skiprows:
  엑셀을 읽을 때 첫줄(0)으로 부터 몇 줄을 건너뛸지 지정합니다.
• nrow:
  몇 줄을 읽을지 지정합니다.
• na_values:
  값이 없는 경우 어떤 str 등으로 넣을지 지정합니다.
• thousands:
  돈과 같이 천단위로 쉼표(,)로 구분된 문자를 변환하기 위하여, 천단위의 구분자가 무엇인지 지정합니다.

 

다른 옵션들도 있긴 한데, 저는 그냥 뭐가 되었든 대충 읽어서 데이터프레임으로 만들고 후가공하는 주의라서 위의 옵션들로도 상당 수 엑셀을 읽어오는데 불편함은 없었습니다.

 

 

Python Pandas로 Excel 파일 쓰기

 

위에서 pandas로 excel 파일의 일부 데이터를 추려내어 있었던 것을 다른 시트에 저장해보겠습니다. 우선 아래의 방법은 기존 파일 이름과 다른 파일명으로 저장하시기 바랍니다. (여러분의 데이터는 소중합니다.)

with pd.ExcelWriter("./output.xlsx") as writer:
    df_excel.to_excel(writer,sheet_name="시트1",index_label='id')
    df_excel.to_excel(writer,sheet_name="시트2",index=False)

 

마치 writer라는 이름으로 output.xlsx 파일명을 가진 ExcelWriter객체를 만들어서, 데이터프레임을 시트1, 시트2로 저장했습니다. pandas의 데이터프레임의 to_excel('저장할 파일명', 옵션) 으로도 새로 엑셀파일을 만들 수 있긴 합니다만, ExcelWriter를 이용하여 파일을 쓰는 것을 권합니다. 

 

to_excel() 함수의 옵션을 몇개 살펴보면 다음과 같습니다.

 

• sheet_name:
  시트 이름을 지정합니다.
• na_rep:
  기본 값은 '' 입니다. 비어있는 값을 'NaN' 등으로 설정할 수 있습니다.
• na_rep:
  예를 들어 float_format = "%.2f"로 한다면, 0.1234가 0.12로 표기됩니다.
• header:
  기본 값은 True로 데이터프레임의 열(column)의 이름을 기본적으로 쓰게 됩니다. False값은 열의 이름을 쓰지 않습니다. ['a','b','c']과 같이 문자열이 있는 리스트를 넣어서 열의 이름을 바꿀 수 있습니다.
• index:
  기본 값은 True로, 각 행(row), 즉 첫줄부터 0으로 시작하여 숫자가 매겨집니다.
• startrow:
  기본 값은 0, 시작 행 지정
• startcol:
  기본 값은 0, 시작 열 지정

 

이밖에 to_excel함수에 다양한 옵션이 존재합니다만, 데이터프레임에서 대다수 테이블 정리가 끝나다보니 생각보다 이런저런 옵션을 넣게 되진 않더군요. 개인적으로는 자동으로 붙어나오는 index가 싫어 그것만 False로 바꾸어주는 편입니다.