안녕하세요!
오늘은 파이썬을 활용하여 단, 복수의 엑셀 시트에서 빈 값(이하 결측치)가 있는지 자동으로 확인하는 프로그램을 만들어보겠습니다.
다음과 같은 상황에 있는 분들에게 도움이 되리라 생각합니다.
1. 동일 포맷의 엑셀 파일들 중 결측치가 있는지 확인해야 하는 경우
파이썬을 활용하면 엑셀 파일을 하나씩 클릭하면서 일일이 눈으로 찾는 것보다 정확하고 빠르게 진행할 수 있습니다.
코드 각주가 없는 전체 코드는 하단에 첨부하였습니다.
최종 실행 코드는 하단의 [결과]와 같습니다.
코드도 길지 않고 다양한 분야의 업무에서 자주 사용 될 수 있으니, 천천히 따라해보시길 추천드립니다.
먼저 구현하려는 코드의 진행 순서는 다음과 같습니다.
1) 기본 라이브러리 설치 및 임포트 한다.
2) 폴더 안에 있는 엑셀 파일의 이름을 불러온다.
3) 엑셀에서 행열 표현을 위한 함수를 작성한다.
4) 엑셀 파일을 열고, 결측치가 있는지 확인하고 이를 반복한다.
그럼 예시를 통해 진행 해보겠습니다.
[그림1] 같은 가상의 매출 데이터를 담은 엑셀 파일을 3개가 있는데, 각 엑셀의 형식은 [그림2]와 같습니다.
이때 각 파일에서 결측치가 있는지를 확인하고, 만약 결측치가 있다면([그림2]의 빨간 네모들처럼) 해당 파일명과 셀의 위치를 알고 싶습니다.
1) 기본 라이브러리 설치 및 임포트 한다.
## 1. 기본 세팅
# 1-1 pandas와 numpy 라이브러리를 설치해줍니다. (이미 설치하신 분은 해당 과정 생략)
!pip install pandas
!pip install numpy
# 1-2 pandas, numpy, os 모듈을 불러옵니다.
# 이때 os는 파이썬 설치 시 같이 설치되는 표준 모듈이기 때문에, 따로 설치하지 않아도 괜찮습니다.
import pandas as pd
import os
import numpy as np
2) 폴더 안에 있는 엑셀 파일의 이름을 불러온다.
## 2. 폴더 안에 있는 엑셀 파일 이름 리스트로 만들기
# 2-1 폴더 내 파일명을 모두 가져오는 함수 만들기
# 바꾸려는 폴더의 경로 변수로 설정해주기
path = "C:/Users/Desktop/test"
# 폴더 내 파일들의 이름들을 리스트로 저장하기
files = os.listdir(path)
# 리스트에서 for문을 돌면서, 윈도우 운영체제를 위해 저장된 숨겨진 파일들을 걸러내는 작업입니다.
files = [i for i in files if os.path.isfile(os.path.join(path, i)) and i != "desktop.ini"]
# 리스트를 폴더에서 수정된 순으로 정렬하는 함수입니다.
files.sort(key=lambda x: os.path.getmtime(os.path.join(path, x)))
# 2-2 결과 출력
# print(files)
2)의 과정을 통해 생긴 리스트를 출력해보면 [그림3]과 같이 나옵니다.
3) 엑셀에서 행열 표현을 위한 함수를 작성한다.
## 3. 엑셀식 행열 표현으로 바꿔주기 위한 함수입니다.
# 함수의 내용이 어려우실 수 있습니다. 그냥 아 이런게 있구나 하고 넘어가셔도 괜찮으리라 생각합니다!
def get_excel_column_name(n):
result = ""
# 엑셀에서는 A~Z 이후로 AA, AB, AC와 같이 열이 표현됩니다. 이를 표현하기 위한 반복문입니다.
while n > 0:
# divmod를 통해 n-1을 26으로 나눠줍니다.
# 이때 n-1을 하는 것은 엑셀은 1부터 열을 세지만, 파이썬에서는 0부터이기 때문이고
# 26은 알파벳의 갯수를 의미합니다.
n, remainder = divmod(n-1, 26)
# 해당 숫자(유니코드)를 알파벳으로 변환시켜줍니다.
result = chr(65 + remainder) + result
return result
4) 엑셀 파일을 열고, 결측치가 있는지 확인하고 이를 반복한다.
## 3. 파일 읽기 및 결측치 확인
for name in files:
# for문을 돌면서 파일의 경로를 불러옵니다.
file_path = os.path.join(path, str(name))
# pandas를 통해 엑셀 데이터를 데이터프레임 형태로 읽어옵니다.
df_excel = pd.read_excel(file_path)
# numpy를 통해 엑셀 데이터에 결측치가 있는 것을 튜플의 형태( (3,4)와 같은 형식 )로 저장합니다.
missing_value = np.where(df_excel.isnull())
# 결측치가 있는 값들을 담을 리스트입니다.
missing_value_list = []
# for문과 zip 메서드를 통해 missing_value들을 하나씩 넣습니다.
for row, col in zip(missing_value[0], missing_value[1]):
excel_row = row + 2
# row에 2를 더해주는 것은 데이터 형태에 따름입니다.
# 예시의 데이터에서는 Header가 첫 줄에 있기 때문에 1를 더해주고,
# 파이썬이 수를 0부터 세기 때문에 이에 맞춰주기 위해 1을 또 더해서 총 2를 더해줍니다.
excel_col = get_excel_column_name(col + 1)
# col같은 경우에는 헤더가 없기 때문에 1만 더해줍니다.
missing_value_list.append(f"{excel_col}{excel_row}")
# 더한 값들을 리스트에 append 메서드를 사용해 추가해줍니다.
# f"{}"와 같은 형태를 f-string이라고 합니다.
# 이는 함수의 값을 문자열로 사용하고 싶을 때 사용합니다. 중괄호({}) 안에 함수명을 넣으면 됩니다.
# missing_value_list가 True라면, 이라는 것을 짧게 표현한 것입니다.
# 기본적으로 리스트 안에 원소가 하나라도 있으면 (결측치가 있다면) True, 원소가 없으면 False입니다.
if missing_value_list:
# 엑셀 파일명과 그 위치들을 출력합니다.
print(f"{name}의 엑셀 파일에서 결측치 위치는 다음과 같습니다. {missing_value_list}")
# missing_value_list가 True가 아니라면, 즉 False라면 입니다. (엑셀 파일 내 결측치가 없는 경우를 말합니다.)
else:
# 엑셀 파일명과 결측치가 없음을 출력해줍니다.
print(f"{name}의 엑셀 파일에서 결측치는 없습니다.")
4)까지 코드를 실행하면 최종적으로 얻고 싶은 결측치들을 [그림4]와 같이 출력 받을 수 있습니다.
+) 응용:
4)의 코드를 좀만 수정하면 시트 전체가 아닌, 본인이 원하는 특정 영역(특정 열이나, 행만 파악하고 싶을때)에서의 결측치 여부만 확인할 수 있습니다.
for name in files:
file_path = os.path.join(path, str(name))
df_excel = pd.read_excel(file_path)
# 특정 영역 설정.
subset = df_excel.iloc[3:20, 1:4]
# iloc 메서드를 통해 영역을 특정할 수 있습니다.
# 3:20은 행을 뜻하고, 1:4은 열을 뜻합니다.
# 즉 해당 예시에서는 행은 네 번째부터 20행까지, 열은 두 번째부터 4번째 열까지 가져옵니다.
# (파이썬과 엑셀이 행,열을 세는 것이 다름을 기억하면 이해하실 수 있습니다.)
missing_value = np.where(subset.isnull())
missing_value_list = []
for row, col in zip(missing_value[0], missing_value[1]):
# 이때 주의하셔야 할 점은 특정 영역에 맞춰 row와 col에 각각 값을 더해주어야 한다는 것입니다.
excel_row = row + 5
# row의 경우 네 번째 행부터 시작하니 원래 2에 3를 더해 총 5을 더해줍니다.
excel_col = get_excel_column_name(col + 2)
# col의 경우 네 번째 행부터 시작하니 원래 1에 1를 더해 총 2을 더해줍니다.
missing_value_list.append(f"{excel_col}{excel_row}")
if missing_value_list:
print(f"{name}의 엑셀 파일에서 결측치 위치는 다음과 같습니다. {missing_value_list}")
else:
print(f"{name}의 엑셀 파일에서 결측치는 없습니다.")
해당 응용을 통해 [그림5]와 같이 전체 결측치가 아닌 특정 영역의 결측치만 가져올 수 있습니다.
마지막으로 주석이 없는 전체 코드입니다.
# !pip install pandas
# !pip install numpy
import pandas as pd
import os
import numpy as np
path = "C:/Users/Desktop/test"
files = os.listdir(path)
files = [i for i in files if os.path.isfile(os.path.join(path, i)) and i != "desktop.ini"]
files.sort(key=lambda x: os.path.getmtime(os.path.join(path, x)))
def get_excel_column_name(n):
result = ""
while n > 0:
n, remainder = divmod(n-1, 26)
result = chr(65 + remainder) + result
return result
for name in files:
file_path = os.path.join(path, str(name))
df_excel = pd.read_excel(file_path)
missing_value = np.where(df_excel.isnull())
missing_value_list = []
for row, col in zip(missing_value[0], missing_value[1]):
excel_row = row + 2
excel_col = get_excel_column_name(col + 1)
missing_value_list.append(f"{excel_col}{excel_row}")
if missing_value_list:
print(f"{name}의 엑셀 파일에서 결측치 위치는 다음과 같습니다. {missing_value_list}")
else:
print(f"{name}의 엑셀 파일에서 결측치는 없습니다.")
긴 글 읽어주셔서 감사합니다. 조금이나마 도움이 되셨길 바랍니다!
'Python > 업무 자동화' 카테고리의 다른 글
| [파이썬+워드] 워드 속 이미지 자동 추출하기 (0) | 2024.11.02 |
|---|---|
| [파이썬+엑셀] 파이썬으로 엑셀 데이터를 가져와 파일 이름들을 한 번에 변경하기 (2) | 2024.09.16 |
