[Python] 파이썬으로 엑셀 파일(xlsx) 다루기 (읽기,쓰기,수정,추가)

---

 

파이썬으로 데이터 분석 업무를 하다보면 엑셀 파일(csv, xlsx)들을 다룰때가 많습니다. 예전에는 csv 파일 읽고 처리하는 법을 살펴보았는데, csv와 xlsx 파일을 엄연히 다른 파일이기 때문에 서로 다르게 처리를 해주어야 합니다. 이번 시간에는 xlsx 파일에 대해 처리하는 방법에 대해서 살펴봅시다!

 

 

모듈 설치

xlsx 파일을 읽으려면 openpyxl, pandas, xlrd 모듈 등이 있는데, 이 글에서는 pandas를 통한 방법으로 진행하겠습니다. 실제로 pandas가 데이터 분석을 위한 많은 툴을 지원하기 때문에 애초부터 pandas로 접근하는 것을 추천드립니다.

 

먼저 판다스를 설치합니다. 판다스는 결국 내부적으로 openpyxl, xlrd를 사용하기 때문에 함께  설치해줍니다.

pip install xlrd
pip install openpyxl
pip install pandas

 

그럼 이제 필요한 모듈을 모두 설치했습니다.

 

excel 파일 읽기 - read_excel 함수

pandas에서 excel 파일을 읽으려면 read_excel 함수를 사용해야 합니다. 사용법은 아래와 같습니다.

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name)

# 데이터 프레임 출력
print(df)

 

위와 같이 코드를 작성하고 실행하면 아래와 같이 엑셀 파일 내용이 출력된 것을 확인하실 수 있습니다.

 

    이름  나이  지역
0  임꺽정  34  서울
1  홍길동  32  경기

 

참고로 엑셀에는 여러개의 시트가 있습니다. 위 내용은 Excel 시트에서 가장 첫번째로 위치한 시트를 가져옵니다. 그렇다면 시트를 직접 정해서 가져올 수 있으려면 어떻게 해야할까요? 바로 sheet_name 옵션을 사용하면 됩니다.

 

 

엑셀 시트 정해서 가져오기 - sheet_name 옵션 추가

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name='B반')

# 데이터 프레임 출력
print(df)

 

sheet_name으로 직접 데이터를 정해서 가져오도록 해보니 아래와 같이 B반 시트에 해당하는 내용이 출력됩니다.

 

    이름  나이  지역
0  신동엽  53  서울
1  유재석  51  경기
2  강호동  52  강원

참고로 sheet_name은 순서 인덱스를 통해서 가져올 수도 있습니다. 0번이 가장 앞에 있는 시트, 1번은 그 다음 시트를 가져옵니다.

 

 

엑셀 시트 여러개 가져오기 - sheet_name 에 리스트 형태로 인자값 전달

여러 개의 시트를 지정해서 동시에 읽고 싶다면 sheet_name에 리스트 형태로 인자값을 전달하면 됩니다. 리턴 값은 딕셔너리 형태로 오게 되는데, 여기서 key는 전달해준 sheet 이름이고 value는 해당 sheet의 데이터 프레임입니다. 예제를 통해서 살펴봅시다.

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name=['A반', 'B반'])

# A반에 해당하는 데이터 프레임 출력
print(df['A반'])

# B반에 해당하는 데이터 프레임 출력
print(df['B반'])

그럼 아래와 같이 A반, B반 시트가 출력되는 것을 알 수 있습니다.

 

    이름  나이  지역
0  임꺽정  34  서울
1  홍길동  32  경기
    이름  나이  지역
0  신동엽  53  서울
1  유재석  51  경기
2  강호동  52  강원

 

만약 모든 Sheet를 가져오고 싶다면 sheet_name = None 을 통해서 모든 Sheet 를 가져올 수 있고, 위 처럼 시트명(또는 시트인덱스)를 통해서 가져오실 수 있습니다.

 

 

 

excel 파일 수정하기 - 데이터 프레임 조작

앞서 read_excel을 통해서 데이터 프레임을 얻어올 수 있다는 것을 알았습니다. 데이터 프레임이란 Excel 시트의 행과 열의 구조로 이뤄진 데이터 구조인데요. 해당 데이터프레임의 데이터에 직접 접근하여 데이터를 수정할 수 있습니다. 사실 이부분은 Dataframe을 다루기에 pandas 에 대한 기본 문법에 가깝습니다. 일단 여기서는 간략하게 특정 데이터만 수정하는 정도로만 소개하고 자세한 데이터 조작에 대한 내용은 pandas를 좀 더 공부해보시는 것을 추천드립니다.

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name=['A반', 'B반'])

a_class_df = df['A반']

# 1번 행 (홍길동)의 나이를 49로 변경
a_class_df.loc[1, '나이'] = 49

# 데이터 프레임 출력
print(df['A반'])

홍길동의 나이를 49로 변경하였습니다.

    이름  나이  지역
0  임꺽정  34  서울
1  홍길동  49  경기

 

또는 2번째 행에 (심청, 21, 서울) 이라는 데이터를 추가할 수도 있습니다. 

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name=['A반', 'B반'])

a_class_df = df['A반']

a_class_df.loc[2] =["심청", 21, "서울"]

# 데이터 프레임 출력
print(df['A반'])

아래와 같이 심청 데이터가 추가된 것을 확인하실 수 있습니다.

 

    이름  나이  지역
0  임꺽정  34  서울
1  홍길동  32  경기
2   심청  21  서울

 

 

그렇다면 수정된 데이터를 원본 엑셀 파일에 쓰려면 어떻게 해야할까요?

 

 

엑셀 파일에 쓰기 - to_excel, ExcelWriter

조작한 데이터를 엑셀에 다시 쓰려면 to_excel 함수를 사용해서 할 수 있습니다. 사용법은 아래와 같습니다.

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name=['A반', 'B반'])

a_class_df = df['A반']
b_class_df = df['B반']

a_class_df.loc[2] =["심청", 21, "서울"]

with pd.ExcelWriter(file_name) as w:
    a_class_df.to_excel(w, sheet_name='A반')

이렇게 하면 test.xlsx를 변경하게 됩니다. 한번 파일을 살펴봅시다.

 

 

보면 두가지 문제가 있네요. (이런 이유로 인해서 같은 파일을 읽고 쓰는 것은 위험합니다)

 

첫번째로 0, 1, 2 라는 인덱스 값이 원치않게 들어갔다는 것입니다. 이는 to_excel 함수에  index=False 옵션을 주어서 해결할 수 있습니다.

 

두번째는 B반 시트가 사라졌다는 것입니다. B반 시트도 함께 해당 파일에 적어주어야 합니다.

 

import pandas as pd

# 파일명
file_name = 'test.xlsx'

# Daraframe형식으로 엑셀 파일 읽기
df = pd.read_excel(file_name, sheet_name=['A반', 'B반'])

a_class_df = df['A반']
b_class_df = df['B반']

a_class_df.loc[2] =["심청", 21, "서울"]

with pd.ExcelWriter(file_name) as w:
    a_class_df.to_excel(w, sheet_name='A반', index=False)
    b_class_df.to_excel(w, sheet_name='B반', index=False)

 

이렇게 하면 원하는 동작을 수행할 수 있습니다.

 

 

 

---

이상으로 Excel 파일 읽고 수정하고 쓰는방법에 대한 소개를 마칩니다. 잘못된 부분이 있거나 추가하실 내용 있으시면 언제든 커멘트 달아주시면 감사하겠습니다 :)