[파이썬/Python] 파이썬으로 폴더 내 엑셀파일 한번에 통합하기

안녕하세요

오늘은 파이썬으로 폴더 내 엑셀파일을 한번에 통합해보겠습니다.

최종 코드는 맨 아래에 넣어두었습니다 ! 

 

엑셀로도 가능하지만, 여러 가지 방법을 알려드리고자 파이썬으로 하는 걸 알려드리겠습니다.

코드 없이 쉽게 폴더 내 파일 병합하고 싶다하시는 분은 엑셀 파워쿼리를 쓰는 방법을 참고해주세요 ! (엄청 쉽습니다)

2022.05.09 - [엑셀(Excel)] - [파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

[파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

 

엑셀 VBA를 통해서 폴더 내 파일 병합하는 코드도 업로드 하였습니다.

2022.11.22 - [엑셀(Excel)] - [엑셀/VBA] 폴더 내 엑셀파일 한번에 통합하기(엑셀파일병합, 자동화기본)

[엑셀/VBA] 폴더 내 엑셀파일 한번에 통합하기(엑셀파일병합, 자동화기본)

 

---

1. 상황

 - 동일한 형태의 엑셀파일이 월별로 존재

 - 파일을 하나씩 열어서 복사 붙여넣기해서 통합된 형태로 관리하고 싶음 

 - 매출현황_2022.06 파일을 열어보면 '이름', '매출현황' 데이터가 5행씩 존재

한 폴더 내에 엑셀파일이 월별로 되어 있음

 - 현재 예시는 6개월 정도치만 5행으로 만들었지만 실무에서는 100개 파일을 10만행씩 있다고 가정하면 될 것 같습니다.

 - 어차피 코드는 동일합니다.

 

---

2. 라이브러리 로드

import pandas as pd # 엑셀 파일 로드위함
import os # 파일경로 지정
import re # 정규표현식 사용

 - pandas, os, re 라이브러리를 로드 합니다. 

---

3. 파일리스트 로드

file_format = ".xlsx" # 파일형태 적으면 됩니다. csv, txt, xlsx, png 등
file_list = [f"{file}" for file in os.listdir() if file_format in file]
file_list

 

 - List Comprehension 방법으로 for 구문을 한 줄로 작성하였습니다. 

 - for 반복문을 비교해보겠습니다.

 

for 반복문 list comprehension으로 작성하였을 경우

 

- os.listdir()의 경우, 현재 경로에 있는 파일 전부를 출력해달라는 얘기입니다. 

- If 문의 경우, file 이름 중 ".xlsx"가 있는 파일만 출력해달라는 말입니다.

- 출력결과 입니다.

파일리스트 로드

 - 최종적으로 .xlsx가 마지막에 있는 파일 6개만 잘 나왔습니다. 

---

4. 단순하게 파일 합치기 ! 

df = pd.DataFrame([]) # 빈 데이터 프레임 만들기

for file in file_list : 
    temp = pd.read_excel(file)
    df = pd.concat([df, temp])
    
df.shape

 - 데이터 합치기 위해서는 무조건 빈 데이터프레임을 하나 만들어야 합니다.

 - pd.read_excel로 파일을 하나씩 읽어옵니다.

 - concat 함수를 통해서 파일을 합쳐줍니다.

---

5. 단순하게  파일 합치기 결과

파일합친 결과

 - 파일은 성공적으로 잘 합쳐졌습니다.

 - 다만 파일 이름에 월별로 적어두었기 때문에 이 데이터가 몇월의 데이터인지 알기가 어렵습니다.

---

6. 엑셀 파일로 저장하기

 - 엑셀 파일로 저장하는 방법은 매우 매우 간단합니다.

df.to_excel('매출통합파일.xlsx', index = False)

 - index = False라고 지정해줘야 새로운 index가 생성되지 않습니다.

 - csv파일의 경우에는 df.to_csv("파일이름".csv, index = False)와 같은 형태로 작성해주시면 됩니다. 

---

7. [추가사항] 파일이름의 연월 데이터를 가지고 오기

 - 이를 위해, 먼저 정규표현식을 아주 살짝 알아보겠습니다.  

 - 정규표현식은 별도로 책 한 권을 공부해야할만큼 양이 방대하다고 합니다.

 - 현재 파일이름인 "매출현황_2022.06.xlsx" 을 "202206"처럼 변경하고 싶습니다.

import re

re.sub("[^0-9]", '', '매출현황_2022.06.xlsx')

  - re.sub(변경하고 싶은 규칙, 변경하고 싶은 모습, 변경해야할 대상) 으로 봐주시면 됩니다.

 - "[^0-9]" : [0-9]의 경우 0부터 9까지 모든 숫자를 뜻합니다. 그 앞에 ^가 붙으면 반대를 뜻합니다. 따라서 숫자가 아닌 모든 것이란 뜻입니다.

 - 뒤에 '' 은 없애줘 라는 뜻입니다.

 - '매출현황_2022.06.xlsx' 에서 숫자가 아닌 모든 부분을 제거한다면 "202206"처럼 나타날 것입니다.

정규표현식_예시

그럼 이제 반복문 사이에 위 식을 넣어주기만 하면 됩니다. 

다시 반복문으로 돌아가겠습니다. 

 

df = pd.DataFrame([]) # 빈 데이터 프레임 만들기

for file in file_list : 
    dates = re.sub("[^0-9]", '', file)
    temp = pd.read_excel(file)
    temp['Date'] = dates
    df = pd.concat([df, temp])
    
df.shape

 

dates 라는 변수를 만들어서 파일이름을 "202206"과 같은 형태로 만들어주고 데이터를 삽입합니다.

 

[결과화면]

- 이제는 Date라는 열이 생겼고, 각 파일의 이름으로 연월 데이터를 구할 수 있습니다. 

 

8. [추가사항] '통합본'의 경우 다른 경로에 저장 추천

- 통합본을 기존 경로에 그대로 저장하는 경우에는 처음에 통합할 때는 문제가 없지만, 앞으로 지속적으로 통합본을 만들 때는 문제가 될 수 있습니다.

 

통합본 파일이 불필요하게 중복 통합되는 결과 발생

 

- 코드 등으로 통합본과 그렇지 않은 것을 구분할 수도 있습니다만, 별도로 관리하는 것이 훨씬 편합니다.

- 그렇지 않다면 통합본 파일명이 동일하다는 가정하에 통합본을 삭제 후 반복문을 돌리는 방법도 있습니다. 

- 다만 삭제하게 된다면 복구가 어려우니 편집한 파일은 다른 폴더에 넣어주는 게 좋습니다. 

 

# 경로에 결과파일 있을 경우 삭제
if os.path.isfile(f'{file_name}.xlsx'):
    os.remove(f'{file_name}.xlsx')

 - file_name 부분에 "매출통합파일" 이라는 부분을 넣게되면 매출현황을 반복문 돌리기 전에 알아서 "매출통합파일.xlsx"를 지우고 반복문을 돌리게 됩니다. 

---

9. [최종] 전체코드 통합

import pandas as pd # 엑셀 파일 로드위함
import os # 파일경로 지정
import re # 정규표현식 사용

file_format = ".xlsx"
file_name = "매출통합파일"

if not os.path.isfile(f'{file_name}.xlsx'):
    file_list = [f"{file}" for file in os.listdir() if file_format in file]
    print(file_list)
    #파일 통합하기
    df = pd.DataFrame([])

    for file in file_list :
        dates = re.sub("[^0-9]", '', file)
        temp = pd.read_excel(file)
        temp['Date'] = dates
        df = pd.concat([df, temp])

    #파일 엑셀파일로 저장
    df.to_excel(f'{file_name}.xlsx', index = False)
else :
    print("이미 파일이 존재하여 삭제하고 새로 통합하였습니다")
    os.remove(f'{file_name}.xlsx')
    
    file_list = [f"{file}" for file in os.listdir() if file_format in file]
    #파일 통합하기
    df = pd.DataFrame([])
    
    for file in file_list :
        dates = re.sub("[^0-9]", '', file)
        temp = pd.read_excel(file)
        temp['Date'] = dates
        df = pd.concat([df, temp])

    #파일 엑셀파일로 저장
    df.to_excel(f'{file_name}.xlsx', index = False)

이로써 폴더 내에 있는 엑셀 파일 통합하기 완료하였습니다.

이럴꺼면 노가다 복붙하겠다라고 생각이 문득문득 들긴 합니다.. ^^

 

아무쪼록 업무자동화를 통해 엑셀 노가다 탈출하시길 바랍니다.

감사합니다.