앞으로 업무자동화 컨텐츠는 내가 업무하다가 불편한 부분, 주변 지인들의 요청사항, 아니면 위처럼 유저들이 지식인에 문의한 기능들을 파이썬과 같은 툴로 구현하여 포스팅해보려 한다. 위 네이버 질문은 최근에 발견한 글이다. 지식인 답변에는 엑셀 VBA 고수가 답변하였으나 나는 파이썬으로 해당 기능을 구현해볼 것이다.
먼저 질문과 같은 가상의 예시를 만들어보도록 하자. 질문자는 한 시트의 내용들을 자동으로 분류하여 여러 시트로 data를 나누려고 한다. 예를들면 아래와 같이 블로그명이 "투손플레이스"인 데이터만 분류해서 다른 시트로 이동시키려면 어떻게 해야할까??
일반적으로 아래 이미지처럼 할 것이다. 필터를 걸고 오름차순 정렬을 하고 "투손플레이스"인 부분만 복사해서 다른 시트에 붙여넣기하는 방법이다.
위에는 "투손플레이스", "코딩유치원", "고양이미로"라는 3가지 블로그명 구분만 있다. 하지만 지식인 질문처럼 실무에서는 제품명같이 무수한 데이터 종류가 있을 수 있다. 위와 같이 직접 복사, 붙여넣기 하는 작업은 매우 번거로운 작업이 된다.
그래서 아래와 3가지 함수로 파이썬 프로그램 코드를 작성해보려고 했다. 위와 같은 엑셀 양식에서 사용자는 열번호만 설정하면 해당 열번호에 대한 데이터를 자동으로 분류한다.
| 함수명 | 설명 |
| categoryList | 특정 엑셀 파일의 원하는 열을 선택하여 해당 열의 데이터를 종류별로 카테고리화 하는 함수 (해당 열에 어떤 데이터가 있는지 중복을 제거하고 리스트로 받아옴) |
| categoryData | 위 종류별 카테고리 데이터를 받아와서 카테고리별 데이터를 분류한다. (카테고리별 데이터를 행별로 리스트로 저장.) |
| writeExcel | categoryData에서 저장 된 데이터를 해당 엑셀파일에 시트를 카테고리별로 생성, 데이터 분류 입력 |
1. 모듈 Import
이번 프로그램은 openpyxl 1개 모듈만 import 하면 된다. 원본 엑셀 파일에 접근해야 하기 때문이다.
import openpyxl as op
2. 함수 작성
함수는 위 컨셉에서 설명하였지만 크게 3가지이다.(categoryList, categoryData, writeExcel)
1) def categoryList(col)
-. cateogryList함수는 열번호 col을 입력받아서 그 열의 data를 중복없이 받아온다.
-. 위와 같은 예시에서 결과값은 ["고양이미로", "코딩유치원", "투손플레이스"]이 된다.
-. 코드 중 del temp_list[0]은 제목행을 제거하는 부분으로 제목행이 없다면 생략해도 된다.
<코드>
def categoryList(col : int)-> list:
temp_list = [r[col].value for r in ws] #리스트 컴프리헨션 : 열번호 입력받아서 데이터 리스트화
del temp_list[0] #제목행 제거
temp_set = set(temp_list) #중복을 제거하기 위해 list를 set으로 변환
name_list = list(temp_set) #다시 set을 리스트로 변환
return name_list #위 리스트를 리턴
위 함수만을 실행시켜 리턴 결과인 name_list를 출력시켜 보면 아래와 같다. (col=1)
<결과>
['고양이미로', '코딩유치원', '투손플레이스']
2) def categoryData(col, name)
-. col : 1)에서 다뤘던 열번호이다. 동일하게 입력되어야 한다.
-. name : 1)에서 리턴됐던 name_list 중 문자열 데이터 1개이다. 즉, 리스트의 1개요소를 매개변수로 받는다.
-. categoryData는 name에 따라 각 행의 데이터를 리스트로 저장한다.
<코드>
def categoryData(col, name):
total_list=[] #temp_list를 전부 담을 토탈 리스트(결과 리턴값)
for r in ws.rows: # 엑셀 시트에서 data가 있는 행을 반복
temp_list=[] #한 줄의 데이터를 임시로 담을 리스트 초기화
cell_num = len(r) #가로로 입력된 해당 행의 셀 개수 받아오기(셀 개수가 각각 다를 수 있기 때문에)
if r[col].value == name: #해당 열의 값 = 분류이름이 같은경우에만 아래 실행
for n in range(0,cell_num):
temp_list.append(r[n].value) #셀 개수만큼 임시 리스트에 담는다.
if temp_list != []: #임시리스트가 비어있지 않다면 토탈 리스트에 해당 임시 리스트 추가
total_list.append(temp_list)
return total_list
코드가 복잡해보이지만 1)에서 각 리스트의 name과 일치하는 행의 데이터를 리스트로 저장하는 부분이다. 아래 결과를 보자. 결과는 name = "투손플레이스"일 때의 total_list 리턴값이다.
<total_list 결과1 - "투손플레이스">
[[1, '투손플레이스', '열정'], [6, '투손플레이스', '행복'], [7, '투손플레이스', '행운'], [14, '투손플레이스', '로또당첨'], [15, '투손플레이스', '외국어 능력'], [20, '투손플레이스', '아파트'], [21, '투손플레이스', '차'], [28, '투손플레이스', '결단력'], [30, '투손플레이스', '로또당첨']]
결과에서 보면 알겠지만 "투손플레이스"에 해당되는 data 행들이 모두 리스트로 저장되고(temp_list) 또 이 리스트를 새로운 리스트(total_list)에 담아 출력됐다. 즉, 리스트 안의 리스트 요소가 있는 것이다. 마찬가지로 "고양이미로", "코딩유치원"일 때도 아래처럼 확인할 수 있다.
<total_list 결과2 - "코딩유치원">
[[2, '코딩유치원', '조회수'], [3, '코딩유치원', '열정'], [5, '코딩유치원', '주식대박'], [8, '코딩유치원', '
조회수'], [9, '코딩유치원', '열정'], [11, '코딩유치원', '코딩 실력'], [13, '코딩유치원', '신축 아파트'], [16, '코딩유치원', '조회수'], [17, '코딩유치원', '열정'], [19, '코딩유치원', '주식대박'], [22, '코딩유치원', '
조회수'], [23, '코딩유치원', '열정'], [25, '코딩유치원', '코딩 실력'], [27, '코딩유치원', '신축 아파트'], [29, '코딩유치원', '신축 아파트']]
<total_list 결과3 - "고양이미로">
['고양이미로', '코딩유치원', '투손플레이스']
[[4, '고양이미로', '열정'], [10, '고양이미로', '조회수'], [12, '고양이미로', '테슬라 모델 Y'], [18, '고양이
미로', '열정'], [24, '고양이미로', '조회수'], [26, '고양이미로', '테슬라 모델 Y']]
3) def writeExcel(name, total_list)
-. name : categoryList 함수에서 리턴됐던 name_list 중 문자열 데이터 1개이다. 즉, 리스트의 1개요소를 매개변수로
받는다. 2)에서 다뤘던 name과 동일하다.
-. total_list : 2)에서 리턴된 결과 리스트이다.
-. writeExcel은 함수명 그대로 엑셀에 각 카테고리 분류에 따른 시트를 새로 생성하고 데이터를 실제 입력한다.
<코드>
#카테고리별 시트를 생성하여 데이터를 모두 입력한다.
def writeExcel(name, total_list):
sht = wb.create_sheet(name) #각 name에 따라 시트를 새로 생성하고 sht로 선언
i=1 #각 시트 행에 접근하기 위한 인덱스
for data in total_list: #total_list 반복
data_length = len(data) #total_list 안의 각 list 길이를 구한다.(각 행 데이터 개수가 다를 수 잇기 때문)
for n in range(0, data_length):
sht.cell(row=i, column=n+1).value = data[n] #각 data를 시트에 입력
i=i+1 #행을 바꾸기 위한 인덱스 숫자 증가
위 함수에서 name = "투손플레이스", total_list는 투손플레이스에 따른 data list가 입력되었다고 해보자. 아래처럼 "투손플레이스" 시트가 자동생성되고 각 행 데이터가 분류된다.
3. 실행 및 결과
위 함수들을 실행하기 위한 코드는 아래와 같다. 주의해야 할 사항은 열번호 col이다. 열번호의 경우 1번이 아닌 0번부터 시작한다. 즉, 엑셀 시트의 'A'열은 col=0이다.
<코드>
path = r"./Project/category/data/rawdata.xlsx" # 원본 엑셀파일 경로
wb = op.load_workbook(path) #openpyxl workbook 객체 생성
ws = wb.active #활성화시트 ws로 정의
col = 1 #열번호(엑셀의 A열이 0번이다. 1은 B열을 의미)
category = categoryList(col) #catagoryList 함수 호출
for name in category: #위 catgory 결과 List를 반복
tota_llist = categoryData(col, name) #categoryData 함수 호출
writeExcel(name, total_list) #writeExcel 호출
wb.save("분류결과파일.xlsx") #결과파일 저장
<실행 과정 및 결과>
위 실행 결과파일을 확인해보면 알겠지만 분류별로 시트가 자동생성되었고 데이터가 각 시트별로 분류되었다. 위 예시는 3가지 분류뿐이지만 수많은 데이터를 반복적으로 분류가 필요한 실무에서 사용한다면 굉장히 유용할 것으로 생각된다.
4. 전체 코드 및 파일
전체 코드 및 .py 파일을 하단을 참고!!
<전체 코드>
import openpyxl as op
#지정 한 열의 분류를 중복없이 받아오기
def categoryList(col : int)-> list:
temp_list = [r[col].value for r in ws] #리스트 컴프리헨션 : 열번호 입력받아서 데이터 리스트화
del temp_list[0] #제목행 제거
temp_set = set(temp_list) #중복을 제거하기 위해 list를 set으로 변환
name_list = list(temp_set) #다시 set을 리스트로 변환
print(name_list)
return name_list #위 리스트를 리턴
#col은 동일해야 한다.(1개의 이름만 받아옴)
def categoryData(col, name):
total_list=[] #temp_list를 전부 담을 토탈 리스트(결과 리턴값)
for r in ws.rows: # 엑셀 시트에서 data가 있는 행을 반복
temp_list=[] #한 줄의 데이터를 임시로 담을 리스트 초기화
cell_num = len(r) #가로로 입력된 해당 행의 셀 개수 받아오기(셀 개수가 각각 다를 수 있기 때문에)
if r[col].value == name: #해당 열의 값 = 분류이름이 같은경우에만 아래 실행
for n in range(0,cell_num):
temp_list.append(r[n].value) #셀 개수만큼 임시 리스트에 담는다.
if temp_list != []: #임시리스트가 비어있지 않다면 토탈 리스트에 해당 임시 리스트 추가
total_list.append(temp_list)
print(total_list)
return total_list
#카테고리별 시트를 생성하여 데이터를 모두 입력한다.
def writeExcel(name, total_list):
sht = wb.create_sheet(name) #각 name에 따라 시트를 새로 생성하고 sht로 선언
i=1 #각 시트 행에 접근하기 위한 인덱스
for data in total_list: #total_list 반복
data_length = len(data) #total_list 안의 각 list 길이를 구한다.(각 행 데이터 개수가 다를 수 잇기 때문)
for n in range(0, data_length):
sht.cell(row=i, column=n+1).value = data[n] #각 data를 시트에 입력
i=i+1 #행을 바꾸기 위한 인덱스 숫자 증가
##실행부
path = r"./Project/category/data/rawdata.xlsx" # 원본 엑셀파일 경로
wb = op.load_workbook(path) #openpyxl workbook 객체 생성
ws = wb.active #활성화시트 ws로 정의
col = 1 #열번호(엑셀의 A열이 0번이다. 1은 B열을 의미)
category = categoryList(col) #catagoryList 함수 호출
for name in category: #위 catgory 결과 List를 반복
total_llist = categoryData(col, name) #categoryData 함수 호출
writeExcel(name, total_list) #writeExcel 호출
wb.save("분류결과파일.xlsx") #결과파일 저장
'코딩 > 업무자동화' 카테고리의 다른 글
| [파이썬/업무자동화] PyQt5 콤보박스 응용 - 엑셀 시트 리스트를 콤보박스화하기 (0) | 2021.07.07 |
|---|---|
| [파이썬/업무자동화] 파일명 규칙 따라 폴더 자동 분류하기 (8) | 2021.07.05 |
| [파이썬/업무자동화] 여러 엑셀 파일의 모든 시트 하이퍼링크 걸기 (1) | 2021.06.23 |
| [파이썬/업무자동화] 엑셀 파일 시트별 pdf 저장/변환하기 (18) | 2021.06.21 |
| [파이썬/업무자동화] 여러 폴더 안에 있는 파일명 한번에 바꾸기 2편 (5) | 2021.06.16 |
