[업무 자동화] openpyxl로 루틴 업무 5분 컷하기

갑자기 웬 자동화?

매주 화요일, 수요일마다 각각 30분씩 품을 들이는 루틴한 업무가 있다.
30분 정도면 끝이 나는 간단한 업무지만 귀찮기도 하고, 무엇보다 실수가 날까봐 두세번 체크 + 전전긍긍하는 것이 짜증나서 아예 자동화를 해버리기로 했다.

openpyxl 만만세!

엑셀 파일을 작성하는 것이 업무의 큰 골자이므로, 파이썬과 엑셀을 연동시켜주는 openpyxl 패키지를 사용하였다.
openpyxl 홈페이지에 들어가보니, 단순 파일 작성 외에도 피벗 테이블 수정이나, 인쇄 설정 세팅 기능까지 제공하는 것으로 보인다.

파이썬 패키지는 무도짤 같다... 없는게 없고 찾아보면 다 있다... ㄷ ㄷ

셀에 데이터 입력하기

첫번째 업무는 유튜브 채널에 업로드되는 콘텐츠별로 조회수 상위 10개 영상들을 추리는 일이다.
먼저, 원본 데이터의 파일명을 인자값으로 받아 데이터프레임을 import한 후 적절하게 가공한다.

# 파일 입력받기
filename = input('[코너별 조회수] 파일명 : ')

# 기본 양식 파일 불러오기
file_path = "C:/Users/****/Documents/코너별 조회수_양식.xlsx"  # 엑셀 파일 경로
sheet_name = 'Sheet2'
load_wb = load_workbook(file_path, data_only=True)
load_ws = load_wb[sheet_name]

load_ws['A1'].value = str(int(filename[9:11])) + '월 ' + str(int(filename[12:14])) + \
    '일~' + str(int(filename[20:22])) + '월 ' + str(int(filename[23:25])-1) + '일 발생한 콘텐츠의 조회수입니다.'

이중 for문을 이용하여 데이터를 삽입하고자 하는 행과 열에 데이터들을 입력해주었다. 12 * idx + 4 가 데이터를 삽입할 행의 index이고, 두번째 for문의 1이 데이터를 삽입할 열의 index에 해당한다.

왜 12냐면 조회수 top10 10행 + 코너제목 1행 + 여백 1행해서 12다!!

program_title = ['콘텐츠1', '콘텐츠2', '콘텐츠3', '콘텐츠4', '콘텐츠5', '콘텐츠6', '콘텐츠7', '콘텐츠8', '콘텐츠9', '콘텐츠10']

for idx, title in enumerate(program_title) :
    temp = df_total[df_total['동영상 제목'].str.contains(title)][:10][['동영상 제목', '동영상 게시 시간', '조회수']]
    for r_idx, row in enumerate(dataframe_to_rows(temp, index=False, header=False), 12 * idx + 4):
        for c_idx, value in enumerate(row, 1):
            load_ws.cell(row=r_idx, column=c_idx, value=value)
            
load_wb.save(file_path)

셀에 음영 처리하기

두번째 업무는 매주 조회수가 50만 이상인 영상을 추리고, 그 전주와 비교했을 때 새로 50만 조회수에 진입한 영상에 음영처리를 하는 매우 간단한 업무다.

# 엑셀 파일에 입력
file_path = "C:/Users/****/Documents/유튜브 50만 이상 영상_양식.xlsx"  # 엑셀 파일 경로
sheet_name = 'Sheet1'
load_wb = load_workbook(file_path, data_only=True)
load_ws = load_wb[sheet_name]

# dataframe_to_rows 함수를 사용해서 dataframe의 temp 데이터를 행 단위로 반복 (행 인덱스를 5부터 시작)
for r_idx, row in enumerate(dataframe_to_rows(temp, index=False, header=False), 5):
    # 현재 행(row)을 열 단위로 반복
    for c_idx, value in enumerate(row, 1):
        load_ws.cell(row=r_idx, column=c_idx, value=value)
        
load_wb.save(file_path)

전주의 조회수 50만 이상 영상 리스트와 비교했을때 새로 진입한 영상 목록을 new로 정의하고, new 리스트에 있는 영상과 해당 셀의 영상이 동일하면 지정된 음영색으로 셀 채우기를 해주는 코드를 작성하였다.

new = list(temp[~(temp['동영상 제목'].isin(df_old['제목']))]['동영상 제목'])

# 음영색 스타일 설정
from openpyxl.styles import PatternFill
fill = PatternFill(start_color='D9D9D9', end_color='D9D9D9', fill_type='solid')  # 노란색 배경색

# 각 셀을 순회하면서 음영색 적용
for row in load_ws.iter_rows():
    for cell in row:
        if cell.value in new:
            cell.fill = fill

그리고 두 파일 모두 'A1'셀에 데이터 수집 기간이 표시되어야 해서, 인자값으로 받은 파일명을 이용해서 날짜 표시까지 적절히 해주었다.

 load_ws['A1'].value = '*2023년 제작한 영상 중 유튜브 조회수 50만 이상 영상. (' + str(int(filename[20:22])) + '월 '  + str(int(filename[23:25])-1) + '일 기준)\n*음영이 된 셀은 새로 진입한 아이템'

마침내 편해졌나?

코드를 완성 후 실행시켜보니 1초도 안되어, 엑셀 파일 두 개가 뚝딱 만들어졌다...ㅎㅎ
유튜브 스튜디오에서 데이터를 다운 받는 시간까지 합하면 5분도 채 걸리지 않을 것으로 보인다.

왜인지 허탈하기도 하고 셀프 일자리 제거를 한 느낌이지만, 효율충(忠)으로서 아주 만족스러운 업무 경험이었다! 부장님껜 비밀이다 하하!