[hwp + python 업무자동화] 졸업사정안 자동화 #4 pandas

2023.12.15 - [교무업무자동화] - [hwp + python 업무자동화] 졸업 사정안 자동화 프로그램 #3

나이스 출결상황 데이터 정제, pandas로 xls data 다루기

< 목차 >
1. 나이스 출결상황 xls data의 구조 분석
2. pandas로 출결상황 데이터 정제하기

 

1. 나이스 출결상황 xls data의 구조 분석

4세대 나이스로 전환되면서 대부분의 자료를 PDF, MSWord, PPT, Xls, Hwp파일 등으로 다운로드가 가능해졌다. 

 

먼저 출결정보를 학생부에 전체반영을 한 후에 출결상황을 나이스에서 XLS data로 저장을 한다. 

 

[ 접근 경로 ]
나이스 - 학생부 항목별 조회 - 출결상황(전체학년) - XLS Data로 저장 

 

 

 

나이스에서 제공하는 엑셀파일은 XLS와 XLS data 두 가지 유형이 있다.

XLS 파일은 보고서 양식으로 출력되는데, 셀이 병합되거나, 셀 간격이 변형되기 때문에 출력물로 활용할 경우 적합한 방식이다. 반면 XLS data 파일은 하나의 셀에 하나의 자료가 입력되는 형태로 제공되기 때문에 데이터를 가공처리 할 때 적합한 방식이다.  아래의 두 그림을 비교하면 쉽게 이해할 수 있다. 

XLS 파일 형식

XLs data 형식

 

하지만 XLS data 형태도 데이터베이스의 형태로 행열로 쌓여 있는 것이 아니라 페이지 출력 형태가 그대로 유지되고 있어서 데이터베이스의 형태로 사용하려면 데이터 정제가 반드시 필요하다. 

 

 

2. pandas로 출결상황 데이터 정제하기

pandas는 엑셀데이터를 데이터프레임 만들어 주며, 데이버 분석에 가장 기본적으로 활용되는 라이브러리다. 

 

먼저 pandas와 os를 import한다. pandas는 관행적으로 pd로 import한다. 

import pandas as pd
import os

 

현재 경로를 getcwd()를 통해 변수 path로 저정하고, Hwpcontrol 폴더에 저장된 출결상황.xlsx를 읽어와서 df에 저장한다.

path = os.getcwd()
df = pd.read_excel(path + r"\HwpControl\출결상황.xlsx")

 

데이터프레임을 정제하기 전에 먼저 필요한 정보를 뽑아와야 한다.

 

pandas에서 원하는 데이터에 접근하기 위해서 loc와 iloc를 사용한다. loc는 행이나 컬럼의 label로 접근하고, iloc는 인덱스값으로 접근한다. 보통 iloc가 처리 속도가 더 빠르지만 경우에 따라 label로 접근해서 사용하기도 하고, label이 있어야 데이터프레임에서 데이터값의 유형과 특징을 파악하기 쉽다. 

 

그렇기 때문에 위에서 생성한 df에는 칼럼명이 부여되어 있지 않기 때문에 칼럼명을 먼저 생성하고 데이터를 정제해야 한다. 

 

 

먼저 XLSdata를 열어보면 6행의 A열~P열까지 들어있는 정보가 실제로 데이터프레임을 구성할 값이다. 그리고 A4셀부터 P5셀까지는 표에서 제목줄에 해당하는 값으로 여기에 입력된 값을 칼럼명으로 지정할 것이다. 

 

먼저 입력된 값에 "번VV호"와 같이 불필요한 공백이 있기 때문에 이 값을 제거해 준다. 

칼럼에 입력된 값을 astype(str)로 문자열로 바꾸고, 각 행에 입력된 값을 람다식을 통해 " "을 ""로 치환한 값을 적용하여 반환한다.

#칼럼 공백 제거 함수
def clean_column(row):
    return row.astype(str).apply(lambda x : x.replace(" ", ""))

 

 

다음으로 컬럼명을 업데이트하는 함수이다. 

update_column은 데이터프레임 df와 reference_row 두 값을 입력 받는다.

위에서 정의한 clean_column함수에를 통해 공백이 제거된 값을 new_cols에 저장하고, 이것을 다시 df.columns로 반환한다. 

#칼럼명 업데이트 함수
def update_column(df, reference_row):
    new_cols = clean_column(df.loc[reference_row])
    df.columns = new_cols

 

 

먼저 3행의 값을 참조하여 칼럼명을 업데이트한다. 

update_column(df, 2)로 하면 2행을 참조하여 공백을 제거하고 컬럼명을 수정한다. 여기서 reference_row가 2인 이유는 엑셀을 pandas로 읽어오면 첫 행은 칼럼명으로 인식하고 엑셀의 2행부터 0번째, 3행은 1번째, 4행은 2번째 값으로 인식한다. 칼럼명으로 사용할 값이 4행부터 입력되거 있기 때문에 reference_row가 2가 된다.

#3행의 값을 참조하여 칼럼명 업데이트
update_column(df, 2)

 

 

그런데 문제는 엑셀에서 4행과 5행 두 행에 걸쳐 제목으로 사용할 값이 입력되어 있기 때문에 4행과 5행의 문자열을 조합하여 질병결석, 미인정결석, 기타결석과 같은 문자열을 생성해야 한다. 

 

 

attend = df. columns.values[start_i] 

문자열 조합을 위해서 먼저 현재 3행의 값으로 지정한 컬럼명을 attend 변수에 담는다. 

 

 for i in range(start_i, end_i + 1): #4~6범위, 마지막 end값은 포함하지 않으므로 +1

for문을 통해 "결석", "지각", "조퇴", "결과"가 입력된 start_i을 지정하면 end_i까지 문자열을 가져올 4행의 범위를 지정한다. 이때 끝나는 범위는 포함되지 않으므로 1을 더해야 한다. 

 

df.columns.values[i] = df.iloc[reference_row, i] + attend #4행의 셀값 + attend

i번째인 칼럼명은 reference_row 행의 i번째 값에 attend로 읽어온 결석, 지각, 조퇴, 결과의 값을 합하여 columns.value로 입력한다.

 

#기존 칼럼영을 4행을 참조하여 업데이트하는 함수
def update_column_attend(df, reference_row, start_i, end_i):
    attend = df.columns.values[start_i] #결석
    for i in range(start_i, end_i + 1): #4~6범위, 마지막 end값은 포함하지 않으므로 +1
        df.columns.values[i] = df.iloc[reference_row, i] + attend #4행의 셀값 + attend

update_column_attend(df, 3, 4, 6)
update_column_attend(df, 3, 7, 9)
update_column_attend(df, 3, 10, 12)
update_column_attend(df, 3, 13, 15)

 

 

칼럼명 변경이 완료되었으면 불필요한 행과 열을 삭제하기 전에 학급 정보와 담임교사명을 변수에 담아둔다. 이 정보는 양식에서 반과 교사명을 채울 때 사용한다. 

 

info_data 리스트에서 첫 번째 항목은 df.iloc[1,0]은 출결상황 엑셀파일의 a3셀에 해당하는 내용으로 학급명에 해당한다.

 

두 번째 항목은 df.iloc[1, 20]은 u3에 입력된 "사용자명 : OOO"에 해당한다. 여기에서 이름에 해당하는 값만 추출하기 위해서 split(':')을 통해 쌍점을 기준으로 분할하고, 두 번째에 해당하는 값을 가져온다. 그리고 strip()을 통해 좌우 공백을 제거하면 "OOO"에 해당하는 값만 가져올 수 있다. 

# 필요한 기초정보를 리스트에 저장, 학급정보, 교사명
info_data = [df.iloc[1,0], df.iloc[1, 20].split(':')[1].strip()]  #strip() 좌우공백 제거

 

 

필요한 값을 리스트에 담아두었으면 이제 불필요한 열과 행을 삭제해서 데이터프레임을 정제한다. 

df.drop 으로 18열~21열까지 삭제한다. axis=1 은 열방향을 의미한다.

df.drop으로 3행까지 삭제한다. 

#불필요한 열 삭제(18열~21열)
df = df.drop(df.columns[18:21], axis=1)
#불필요한 행 삭제(1행~3행)
df = df.drop(range(4))

 

 

다음은 페이지 형태로 분할되어 있는 데이터를 정제해야 한다. 

 

데이터의 전체 행을 살펴보면 중간에 나이스 시스템의 페이지 정보가 삽입되어 있다. 이 부분을 정제하기 위해서는 실제 정보가 입력된 열을 중심으로 불필요한 행을 제거할 수 있는 특징을 찾아야 한다. 

 

위의 자료 상에서 페이지 하단의 쪽 정보, 학교명, 표제목 등의 정보를 제거해야 한다. C열에는 학년 정보가 입력되어 있는데, C열의 학년 정보가 아닌 부분과 제거해야 할 정보가 일치한다. 

 

C열을 기준으로 입력된 값을 숫자로 변환(to_numeric) 한다. errors='coerce'는 숫자로 바꿀 수 없는 데이터를 만나면 해당 값을 강제로 NaN으로 처리하고 nonta 메서드를 통해 값이 입력된 것들만 반환한다. 

#불필요한 행 삭제('학년'열 기준, 학년, 공백 삭제)
df = df[pd.to_numeric(df['학년'], errors='coerce').notna()]
# to_numeric은 학년 열을 숫자로 변환
# errors는 숫자로 변환하지 못할 경우 NaN으로 처리
#notna()는 NaN이 아닌 값만 선택하는 함수

 

여기까지 실행하면 불필요한 행과 열 정보가 모두 삭제되고 DB의 형태를 갖추게 된다. 

 

다음으로 행 삭제로 인한 불규칙적인 인덱스를 reset_index로 재부여한다. drop=true는 이전의 인덱스 열을 제거하고 새로 부여한 인덱스를 기준으로 재정렬한다. 

#인덱스 재부여(66*17)
df = df.reset_index(drop=True)

 

 

마지막으로 학생의 번호와 이름 열이 1학년에 해당하는 데이터 영역에만 입력되어 있기 때문에 2, 3학년 영역에도 같은 값을 넣어줘야 한다. 

 

 

 

 

이 작업을 위해 먼저 for 반복문을 만들어주고, 반복은 전체 데이터의 수 len(df) 까지 반복하게 한다. 

for i in range(len(df)):

 

학년정보를 grade변수에 넣어주고

grade = df.iloc[i,2]

 

학년 정보가 2(학년) 또는 3(학년) 일때 1학년 행을 참조하여 데이터를 넣어주도록 한다. 

#상위행을 참조하여 하위행의 값 채우기
for i in range(len(df)):
    grade = df.iloc[i,2]
    if grade == 2:   #2학년일 경우 현재행의 상위행 복사
        df.iloc[i, 0] = df.iloc[i-1, 0]
        df.iloc[i, 1] = df.iloc[i-1, 1]
    if grade == 3:   #3학년일 경우 현재행의 상위행 복사
        df.iloc[i, 0] = df.iloc[i-2, 0]
        df.iloc[i, 1] = df.iloc[i-2, 1]

 

 

<전체 코드>

import pandas as pd
import os
import warnings

#오류 경고 무시 / 기본스타일이 없음
warnings.filterwarnings(action='ignore')

path = os.getcwd()

df = pd.read_excel(path + r"\HwpControl\attendance.xlsx")

####칼럼명 변경
#3행 값을 모두 출력
#공백을 제거, lambda함수로 모두 제거
#df.apply(func)은 df의 각 열에 함수func를 적용

#칼럼 공백 제거 함수
def clean_column(row):
    return row.astype(str).apply(lambda x : x.replace(" ", ""))

#칼럼명 업데이트 함수
def update_column(df, reference_row):
    new_cols = clean_column(df.loc[reference_row])
    df.columns = new_cols

#3행의 값을 참조하여 칼럼명 업데이트
update_column(df, 2)

#기존 칼럼영을 4행을 참조하여 업데이트하는 함수
def update_column_attend(df, reference_row, start_i, end_i):
    attend = df.columns.values[start_i] #결석
    for i in range(start_i, end_i + 1): #4~6범위, 마지막 end값은 포함하지 않으므로 +1
        df.columns.values[i] = df.iloc[reference_row, i] + attend #4행의 셀값 + attend

update_column_attend(df, 3, 4, 6)
update_column_attend(df, 3, 7, 9)
update_column_attend(df, 3, 10, 12)
update_column_attend(df, 3, 13, 15)

# 필요한 기초정보를 리스트에 저장, 학급정보, 교사명
info_data = [df.iloc[1,0], df.iloc[1, 20].split(':')[1].strip()]  #strip() 좌우공백 제거

#불필요한 열 삭제(18열~21열)
df = df.drop(df.columns[18:21], axis=1)
#불필요한 행 삭제(1행~3행)
df = df.drop(range(4))

#불필요한 행 삭제('학년'열 기준, 학년, 공백 삭제)
df = df[pd.to_numeric(df['학년'], errors='coerce').notna()]
# to_numeric은 학년 열을 숫자로 변환
# errors는 숫자로 변환하지 못할 경우 NaN으로 처리
#notna()는 NaN이 아닌 값만 선택하는 함수

#인덱스 재부여(66*17)
df = df.reset_index(drop=True)

#상위행을 참조하여 하위행의 값 채우기
for i in range(len(df)):
    grade = df.iloc[i,2]
    if grade == 2:   #2학년일 경우 현재행의 상위행 복사
        df.iloc[i, 0] = df.iloc[i-1, 0]
        df.iloc[i, 1] = df.iloc[i-1, 1]
    if grade == 3:   #3학년일 경우 현재행의 상위행 복사
        df.iloc[i, 0] = df.iloc[i-2, 0]
        df.iloc[i, 1] = df.iloc[i-2, 1]