pandas를 사용하여 서로 다른 엑셀(Excel) 파일을 통해 결과를 출력하는 방법

들어가며

이번에는 서로 다른 엑셀 파일의 데이터를 가공하여 새로운 엑셀 파일을 생성하는 방법에 대해서 정리해보았다. 본 글에서 사용한 두 개의 엑셀 파일은 다음과 같다.

• 파일명 : cables.xlsx
• 설명 : 케이블의 시스템 코드, 시작기기, 종료기기 정보가 담긴 엑셀 파일

순번	케이블	시스템 코드	시작기기	종료기기
1	cable-01	system-01-open:closed	equipment-01	equipment-06
2	cable-02	system-01-open:closed	equipment-02	equipment-05
3	cable-03	system-01-open:closed	equipment-03	equipment-07
4	cable-04	system-02-open:closed	equipment-04	equipment-08
5	cable-05	system-02-open:closed	equipment-05	equipment-13
6	cable-06	system-02-open:closed	equipment-12	equipment-09
7	cable-07	system-02-open:closed	equipment-17	equipment-11
8	cable-08	system-03-open:closed	equipment-10	equipment-34
9	cable-09	system-03-open:closed	equipment-15	equipment-21
10	cable-10	system-03-open:closed	equipment-22	equipment-20

• 파일명 : raceways.xlsx
• 설명 : 레이스웨이의 케이블, 격실, 참조도면 정보가 담긴 엑셀 파일

순번	레이스웨이	케이블	격실	참고도면
1	raceway-21	cable-01	room-a-02	dwg-10592
2	raceway-15	cable-01	room-a-01	dwg-10435
3	raceway-34	cable-02	room-b-03	dwg-10462
4	raceway-15	cable-02	room-a-01	dwg-10435
5	raceway-21	cable-03	room-a-02	dwg-10592
6	raceway-27	cable-03	room-a-02	dwg-10593
7	raceway-12	cable-04	room-a-01	dwg-10435
8	raceway-34	cable-04	room-b-03	dwg-10462
9	raceway-26	cable-05	room-a-02	dwg-10593
10	raceway-30	cable-05	room-b-03	dwg-10462
11	raceway-08	cable-05	room-a-01	dwg-10257
12	raceway-21	cable-06	room-a-02	dwg-10592
13	raceway-26	cable-06	room-a-02	dwg-10593
14	raceway-37	cable-07	room-b-03	dwg-10462
15	raceway-61	cable-07	room-b-06	dwg-10612
16	raceway-15	cable-08	room-a-01	dwg-10435
17	raceway-12	cable-08	room-a-01	dwg-10435
18	raceway-52	cable-09	room-a-05	dwg-10576
19	raceway-31	cable-09	room-b-03	dwg-10462
20	raceway-62	cable-10	room-a-06	dwg-10612

엑셀 파일 불러오기

두 개의 서로 다른 엑셀 파일의 데이터를 가공하여 생성한 새로운 데이터가 담긴 엑셀 파일의 정보는 다음과 같다.

• 파일명 : systems.xlsx
• 설명 : 시스템 코드의 케이블, 레이스웨이, 격실, 참조도면, 시작기기, 종료기기 정보가 담긴 엑셀 파일

서로 다른 두 개의 엑셀 파일을 불러오면 다음과 같은 코드로 나타낼 수 있다.

import pandas as pd
import os

dir_path = 'C:/Users/CHOEWY/Desktop'                    # 엑셀 파일이 저장된 폴더의 경로

cables = pd.read_excel(dir_path + "/cables.xlsx")        # 첫 번째 엑셀 파일
raceways = pd.read_excel(dir_path + "/raceways.xlsx")    # 두 번째 엑셀 파일

데이터 가공

새로운 데이터는 시스템 코드 열을 기준으로 나머지 두 개의 엑셀 파일에 담긴 모든 데이터를 나열한 형태이다. 즉, 시스템 코드가 데이터의 기준이 될 것이므로 첫 번째 엑셀 파일에 담긴 데이터에서 시스템 코드만 불러오고, 중복을 제거하면 다음과 같은 코드로 나타낼 수 있다.

codes = cables["시스템 코드"]         # "시스템 코드" 열 선택
codes = codes.drop_duplicates()        # 중복제거
codes = systems.tolist()            # 리스트로 변환

새롭게 추출할 데이터를 리스트로 저장하기 위해 빈 리스트 변수인 systems를 먼저 선언하고, 새롭게 저장하고자 하는 열을 columns에 저장한다. 이어서 반복문을 수행하며 각 반복 수행 시 필터 연산을 통해 cables에서 가져올 수 있는 데이터를 가져오도록 한다. 이를 나타낸 코드는 다음과 같다.

systems = []
columns = ["시스템 코드", "케이블", "레이스웨이", "격실", "참초도면", "시작기기", "종료기기"] 

for code in codes:
    select = ["케이블", "시작기기", "종료기기"]     # 케이블 정보에서 가져올 열 정보
    exist = cables["시스템 코드"] == code         # 해당 시스템 코드 정보를 찾는 필터 연산
    system_cables = cables[exist]                # 선택한 시스템 코드에 해당하는 모든 정보 선택
    system_cables = system_cables[select]        # 해당 열의 정보만 선택

이중 반복문 형태로 다시 반복문을 수행하여 필터 연산을 통해 선별한 케이블에 해당하는 레이스웨이 데이터를 선별하도록 한다. 이를 나타낸 코드는 다음과 같다.

# ... (생략) ...

for code in codes:

    # ... (중략) ...

    for (cable, room, dwg) in system_cables.values:
        select = ["레이스웨이", "격실", "참고도면"]     # 레이스웨이 정보에서 가져올 열 정보
        exist = raceways["케이블"] == cable          # 해당 케이블 정보를 찾는 필터 연산
        system_raceways = raceways[exist]            # 선택한 케이블에 해당하는 모든 정보 선택
        system_raceways = system_raceways[select]    # 해당 열의 정보만 선택

삼중 반복문 형태로 다시 반복문을 수행하여 선별한 각각의 데이터를 새로운 데이터에 저장하도록 한다. 이를 나타낸 코드는 다음과 같다.

# ... (생략) ...

for code in codes:

    # ... (중략) ...

    for (cable, start, end) in system_cables.values:

        # ... (중략) ...

        for (raceway, room, dwg) in system_raceways.values:
            system = [code, cable, raceway, room, dwg, start, end]    # 데이터 행 생성
            systems.append(system)                                    # 데이터 행 추가

이 단계에서 systems를 출력해보면 그 결과는 다음과 같다.

[
    ["system-01-open:closed", "cable-01", "raceway-21", "room-a-02", "dwg-10592", "equipment-01", "equipment-06"],
    ["system-01-open:closed", "cable-01", "raceway-15", "room-a-01", "dwg-10592", "equipment-01", "equipment-06"],
    // ...
    ["system-03-open:closed", "cable-10", "raceway-62", "room-a-06", "dwg-10612", "equipment-22", "equipment-20"]
]

엑셀 파일로 저장

위에서 새롭게 생성한 systems를 엑셀 파일로 저장하여야 한다. 이를 나타낸 코드는 다음과 같다.

# ... (생략) ...

systems = pd.DataFrame(data=systems, columns=columns)
systems.to_excel(dir_path + "/systems.xlsx", sheet_name="시스템 코드", index=False)

os.startfile(dir_path + "/systems.xlsx")

마치며

위 예시의 로직과 코드는 실제 내가 직장에서 60,000행이 넘는 데이터를 관리할 때 사용한 코드이다. 기존에는 이러한 작업을 수작업으로 했는데, 인적 오류, 인력 자원 낭비 등의 문제가 발생하였다. 특히, 대부분 이와 같은 단순한 업무는 주로 내가 담당하여 처리하였는데, 단순 반복 업무만 하다보니 매너리즘에 빠져 새로운 방법을 알아보던 찰나에 Python을 접하게 되었고, 프로그래밍의 매력에 빠지게 된 또 다른 계기가 아닐까 싶다. 직장 생활을 하며 혼자 Python을 배우기 까지는 꽤 오랜 시간이 걸렸지만, 한 번 배워놓은 기술을 계속 활용할 수 있다는 점에서 충분히 시간 투자할 만한 가치가 있다고 생각한다.