안녕하세요!
오늘은 파이썬을 활용하여 MS Excel 안에 있는 특정 데이터들을 삭제하는 코드를 소개하고자 합니다.
이 코드는 예전에 인턴 생활을 할 때 실제 업무를 위해 쓰였던 코드이기 때문에,
다소 지엽적인 목적으로만 쓰였습니다.
하지만 당시 저와 같은 상황에 계신 분이라면 도움이 되실 수 있으리라 생각하고,
다음과 같은 상황에 계신 분들께 도움이 되리라 생각합니다.
1. 하나의 수천 행 이상의 데이터를 담고 있는 엑셀 시트에서 특정 데이터를 삭제하고 싶은 경우
제가 당시 처했던 배경을 설명드리자면 다음과 같습니다.
회사 내 업무 중 매번 대규모 엑셀 파일의 데이터를 추가 및 삭제하는 업무가 있었습니다.
이때 각 파일이 20MB를 초과할 정도로 일반적인 엑셀 파일 용량에 비해 상당히 컸습니다.
이러한 상황에서 해당 파일에서 간단한 조건만 걸어 데이터를 편집하려 해도, 한 번 입력에 10분 이상 PC를 이용하지 못하면서 기다려야 하는 불편이 발생하였습니다.
또한 PC가 감당하지 못해 엑셀 프로그램이 다운되는 경우도 종종 발생했습니다.
해당 상황에서 파이썬을 통해 빠르고 안전하게 데이터를 편집하고 싶었습니다.
각주가 없는 전체 코드는 제일 하단에 첨부하였습니다.
구현하려는 코드 순서는 다음과 같습니다.
1) 필요한 라이브러리를 불러옵니다.
2) 파일이 존재하는 경로로 위치를 설정합니다
3) 엑셀 파일을 불러옵니다.
4) 데이터가 잘 불러와졌는지 확인합니다.
5) 원하는 조건을 걸고 데이터를 삭제합니다.
6) 실제로 데이터가 삭제되었는지 확인합니다.
7) 해당 데이터를 새로운 엑셀 파일로 저장합니다.
단계가 많아 보이지만 전체가 아주 짧은 코드니 쉽게 따라하실 수 있으리라고 생각합니다!
우선 가상의 데이터가 [사진1]과 같다고 하고, '교육여부'(D열)가 '미교육' 상태인 것을 삭제하는 것을 목표로 진행해보겠습니다.
1) 필요한 라이브러리를 불러옵니다.
# 필요한 라이브러리를 불러옵니다.
import pandas as pd
import os
import time
from collections import Counter
2) 파일이 존재하는 경로로 위치를 설정합니다
# 파일이 존재하는 경로로 위치를 설정합니다.
os.getcwd()
os.chdir('C:/Users/USER/Desktop')
3) 엑셀 파일을 불러옵니다.
# 엑셀 파일을 불러옵니다.
# 출력되는 초는 해당 파일 불러오는데 걸린 시간입니다.
start = time.time()
df_data = pd.read_excel('파일명.xlsx')
end = time.time()
print(f"{end - start:.5f} sec")
컴퓨터 사앙별로, 엑셀 파일 크기별로 차이가 있겠지만, [사진2]와 같이 출력 시간은 대부분 1초가 채 걸리지 않을 것입니다.
4) 데이터가 잘 불러와졌는지 확인합니다.
# 데이터 형태 확인
print(type(df_data))
# 잘 불러와졌는지 확인합니다.
print(df_data.head(10))
이때 4)의 결과는 [사진3] 같이 나옵니다.
상단의 <class 'pandas.core.frame.DataFrame'>은 데이터 형태를 Pandas 형태로 잘 불러왔는지를 보여주는 출력 내용입니다.
5) 원하는 조건을 걸고 데이터를 삭제합니다.
# 교육여부 열.drop(교육여부에서 미교육인 데이터를 전부 삭제합니다.
df_data.drop(df_data[df_data['교육여부'] == '미교육' ].index, inplace=True)
6) 실제로 데이터가 삭제되었는지 확인합니다.
# 다시 출력해보며, 잘 삭제되었는지 확인합니다.
print(df_data.head(10))
# 또한 다음과 같이 Counter 메서드를 통해 정확하게 파악할 수 있습니다.
(교육여부 열에서 미교육 데이터가 몇 개인지 확인하는 함수)
print(Counter(df_data['교육여부'])['미교육'])
6)의 실행 결과는 [사진4] 와 같습니다.
('교육여부'가 '미교육'인 데이터 행 전체를 삭제 된 것을 볼 수 있고,
마지막 줄의 출력 결과 0을 통해 '교육여부'가 '미교육'인 데이터가 0개임을 알 수 있다.)
7) 해당 데이터를 새로운 엑셀 파일로 저장합니다.
# 해당 데이터프레임을 다시 엑셀로 저장합니다.
# 이때 원파일과는 이름을 다르게 설정해주셔야 합니다.
# 출력되는 초는 해당 파일 저장하는데 걸린 시간입니다.
start = time.time()
df_data.to_excel('수정된 파일명.xlsx')
end = time.time()
print(f"{end - start:.5f} sec")
7)까지의 결과를 바탕으로 새로 만들어진 엑셀은 [사진5]와 같은 모습을 보입니다.
결과를 보시면 알 수 있지만, 행의 개수를 보여주는 새로운 열이 생기기도 했고(A열), 특정 데이터가 보이지 않기도 하며(C열), 또한 전반적인 양식이 바뀐 것을 볼 수 있습니다.
이렇듯 파이썬을 통한 엑셀 데이터 수정 및 삭제는 주로 엑셀의 양식면에서 한계점이 있으므로,
기존 파일의 양식을 복사하는 과정이 추가로 필요로 하니 주의하시길 바랍니다.
마지막으로 주석이 없는 전체 코드입니다.
import pandas as pd
import os
import time
from collections import Counter
os.getcwd()
os.chdir('D:/바탕화면')
start = time.time()
df_data = pd.read_excel('파일명.xlsx')
end = time.time()
print(f"{end - start:.5f} sec")
print(type(df_data))
print(df_data.head(10))
df_data.drop(df_data[df_data['교육여부'] == '미교육' ].index, inplace=True)
start = time.time()
df_data.to_excel('수정된 파일명.xlsx')
end = time.time()
print(f"{end - start:.5f} sec")
긴 글 읽어주셔서 감사합니다. 조금이나마 도움이 되셨길 바랍니다!