[예제 1] 교육신청서 취합하기

문제

한 회사에서 직원들에게 네 종류의 교육 A, B, C, D 가운데 어느 교육을 듣고 싶은지 교육신청서에 작성해서 제출하도록 했습니다. 이 회사의 직원은 총 1천명이고 이 가운데 일부는 다음과 같은 엑셀 양식의 교육신청서를 작성했습니다.

교육신청서 양식

듣고 싶은 교육에 O 표시를 하고, 듣고 싶지 않은 교육에는 X 표시를 했습니다. 단, 모든 직원은 하나 이상의 교육을 반드시 수강해야 합니다. 

그런데 1천명의 직원 가운데 대부분이 아직 신청서를 제출하지 않았습니다. 그래서 제출한 신청서를 바탕으로 제출한 직원이 듣고 싶은 교육을 취합하고 제출하지 않은 직원을 체크하려 합니다. 여기에 사용하는 양식은 아래와 같습니다.

 

 

데이터 

직원들이 제출한 교육신청서는 아래 압축 파일에 있습니다. 각 신청서의 파일명은 "사번_교육신청서.xlsx"꼴로 되어 있습니다. 

교육신청서.zip

1.44MB

 

이 파일의 압축을 교육신청서 폴더에 풀어야 합니다.

 

제출결과를 취합하는 파일은 아래와 같습니다.

제출결과취합.xlsx

0.03MB

 

 

 

따라서 전체 데이터는 "교육신청서 취합하기 폴더"에 다음과 같이 있습니다.

 

해결책 생각하기

자동화하기 전에 이 작업을 노가다로 한다면 어떤 절차로 하는지 생각해보겠습니다.

먼저, 제출결과취합.xlsx에는 교육신청서를 제출해야 하는 직원들의 사번이 있습니다. 그래서 이 파일의 첫 번째 행부터 하나씩 교육신청서 폴더에 해당 사번으로 시작하는 파일명이 있는지를 기준으로 이 직원이 제출했는지 확인할 것입니다. 만약 제출했다면 제출여부에 O를 표시해야 합니다. 또한, 제출했다면 제출한 교육신청서를 열어서 어느 교육을 수강하는지 정보를 가져올 것입니다.

 

파이썬 코드

이제 주피터노트북을 열고 코드를 작성해보겠습니다. 첫 예제인만큼 매우 친절하게 작성해보겠습니다. 개발 환경 구축은 여기를 참고하시기 바랍니다.

 

먼저 데이터가 있는 경로("교육신청서 취합하기")에 New > Python 3를 클릭하여 ipynb 파일을 생성합니다.

다음으로 "제출결과취합.xlsx" 파일을 불러오겠습니다. 엑셀파일을 다룰 때 사용하는 패키지로는 pandas와 openpyxl 등이 있으며, 색상, 폰트 정렬 등 양식을 유지하려면 openpyxl이 적절하고, 순수하게 값만 분석하는 경우에는 pandas가 더 적절합니다. 여기서는 openpyxl을 사용하겠습니다.

openpyxl 모듈을 불러온 뒤, 이 모듈의 load_workbook 함수를 이용하여 "제출결과취합.xlsx" 파일을 불러와 wb에 저장하겠습니다.

import openpyxl # openpyxl 모듈 불러오기
wb = openpyxl.load_workbook("제출결과취합.xlsx") # 엑셀 파일 불러오기

참고로 wb는 WorkBook의 약어로 사용한 것입니다.

워크북은 여러 시트의 조합이므로 우리가 작업하고자 하는 시트를 지정하겠습니다. 우리가 작업할 시트명은 Sheet1입니다. 

ws = gathering_data["Sheet1"] # 시트 저장

이제 이 시트의 A열(사번)을 순회할 것입니다. 참고로 값은 A2셀부터 A1001셀까지 있습니다. 

ws의 A2셀에 있는 값을 출력해서 확인해보겠습니다.

print(ws["A2"].value)

[실행 결과]

165805

 

이렇게 value를 사용하여 값을 알 수 있습니다.

이제 저 사번으로 시작하는 파일이 교육신청서 폴더에 있는지 확인해보겠습니다. 참고로 한 폴더에 있는 파일 목록은 os 모듈의 listdir 함수를 사용하여 다음과 같이 알아낼 수 있습니다.

import os
os.listdir("교육신청서")

그러면 교육신청서 폴더에 있는 파일을 순회하면서 사번이 파일명에 포함되는지를 다음과 같이 확인하면 됩니다. 

for file_name in os.listdir("교육신청서"): # 교육신청서 폴더에 있는 파일명을 file_name으로 순회
    if str(ws["A2"].value) in file_name:   # 만약 ws["A2"]의 값이 file_name에 속해 있으면
        print(file_name)                   # 파일 이름을 출력

두 번째 줄에서 값을 str로 감싼 이유는 문자인 file_name과 숫자인 ws["A2"].value를 비교하려, 숫자를 문자로 바꾸기 위한 것입니다. 

위 코드를 실행해보니 아무런 것도 출력되지 않았습니다. 즉, A2에 있는 사번이 165805인 직원은 교육신청서를 아직 제출하지 않았습니다. 이 경우에는 아무런 작업을 하지 않고 넘어가야 합니다. 만약 파일이 있었다면, 그 파일을 불러와서 두 번째 행의 값(A2, B2, C2, D2)을 가져오면 될 것입니다.

 

이 내용을 바탕으로 전체 코드를 완성시켜보겠습니다. 설명을 위해 라인 번호를 추가했습니다.

 1 for row in range(2, 1002):
 2     for file_name in os.listdir("교육신청서"):
 3         if str(ws["A" + str(row)].value) in file_name:
 4             ws["B" + str(row)].value = "O"
 5             
 6             # 신청서 열기
 7             app_wb = openpyxl.load_workbook("교육신청서/" + file_name)
 8             app_ws = app_wb["Sheet1"]
 9             
10             # 값 가져오기
11             ws["C" + str(row)].value = app_ws["A2"].value
12             ws["D" + str(row)].value = app_ws["B2"].value
13             ws["E" + str(row)].value = app_ws["C2"].value
14             ws["F" + str(row)].value = app_ws["D2"].value
15             
16 wb.save("제출결과취합_완료.xlsx")

• 라인 1: row가 2, 3, 4, ..., 1001을 순회합니다. range의 맨 뒷 값(1002)까지는 순회하지 않습니다.
• 라인 2 - 3: 교육신청서 폴더에 있는 파일명을 순회하면서 ws의 A + str(row) 셀의 값이 파일 이름에 속해 있는지 검사합니다. row는 숫자이므로 str을 사용하여 문자로 바꾼 뒤, A와 더했습니다. 예를 들어, row가 2라면 A2가 됩니다. 
• 라인 4: 라인 3의 조건을 만족하므로 온 라인이므로, B + str(row) 셀의 값에 O를 기록합니다.
• 라인 6 - 8: file_name에 A + str(row)가 있었으므로, 해당 파일 이름을 갖는 파일을 열어 app_wb에 저장합니다. 또, 여기에 있는 Sheet1을 가져와 app_ws에 저장합니다.
• 라인 11 - 14: app_ws의 A2셀에 있는 값을 C + str(row)셀에, B2셀에 있는 값을 D + str(row)셀에 저장합니다.
• 라인 16: 취합한 결과를 "제출결과취합_완료.xlsx"로 저장합니다. 

전체 소스코드와 결과는 아래에서 다운로드받을 수 있습니다.

솔루션.ipynb

0.02MB

제출결과취합_완료.xlsx

0.02MB