Pandas로 열이 뒤죽박죽인 엑셀, csv 합치기

결론부터 보실 분은 여기로 >> https://github.com/yc-song/dbmerge

0. 들어가며

아래와 같은 별개의 엑셀 파일/시트를 하나로 합친다고 합시다. 행의 순서가 통일되어 있어 별로 어려운 일이 아닐 것 같습니다.

1.xlsx
이름	나이
라이언	22
포로리	33
도라에몽	19
아라찌	24

2.xlsx
이름	나이
비둘기	35
어피치	49
김강철	11

하지만 다음과 같이 행이 같지만 순서가 뒤죽박죽인 엑셀 파일이 추가된 상황이라면 어떨까요?

3.xlsx
나이	이름
43	김구구
32	이구구
29	박구구
11	구구구

심지어 행의 항목도 이상한게 추가된 엑셀 시트까지 합쳐야 한다면?

4.csv
나이	이름	성별
29	구구콘	M
32	슈퍼콘	F
29	손흥민	F
15	슈퍼콘	M

해외 문서까지 합쳐져서 Name열의 내용을 이름 열 밑으로 합쳐야 하는 상황이라면?

5.xlsx
Name	Age	Gender
Nabi	34	M
Bobettau	29	F
周星馳	50	M
孫正義	62	M

블로그 포스팅을 위해 만들어낸 작위적인 상황 같지만; 실제로 회사에서 이와 유사한 상황에 대처해야 했습니다.

하지만 우리에게는 ~~노가다를 견뎌낼 수 있는 강인한 체력~~ 컴퓨터가 있으니 찬찬히 살펴보도록 합시다.

1. 위의 1,2,3 파일을 하나로 합치는 경우

우선 다음과 같은 엑셀 파일을 생성했습니다.

위의 테이블과 같은 내용의 파일입니다. 이름, 나이 열이 뒤죽박죽이었던 위 파일들을 아래와 같이 합치고자 합니다.

소스코드 및 설명

위의 1,2,3 엑셀 파일을 프로젝트 밑의 rawdata폴더에 저장했습니다. 코드는 다음과 같습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

import pandas as pd
import glob
import xlrd
import openpyxl
 
#우선 모든 자료들을 raw data folder에 저장
all_data = pd.DataFrame()
all_data1 = pd.DataFrame()
for f in glob.glob("rawdata/*.xlsx"): #rawdata 디렉토리에 있는 xlsx파일들 불러옴
    df=pd.read_excel(f, sheet_name=None)
    all_data1 = pd.concat(df, ignore_index=True)
    all_data=all_data.append(all_data1,ignore_index=True)
    print(all_data)
all_data.to_excel('합본.xlsx',encoding='utf-8-sig') #저장
#엑셀로 저장하는게 오래 걸릴 때에는, csv로 저장할 수도 있습니다.
#all_data.to_csv('합본.csv',encoding='utf-8-sig') 

cs

위의 과정을 도식화하면 다음과 같이 정리할 수 있습니다.

만약 주어진 파일 중에 csv가 있다면, 아래와 같이 csv용 for문을 추가할 수 있습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

import pandas as pd
import glob
import xlrd
import openpyxl
 
#우선 모든 자료들을 raw data folder에 저장
all_data = pd.DataFrame()
all_data1 = pd.DataFrame()
for f in glob.glob("rawdata/*.xlsx"): #rawdata 디렉토리에 있는 xlsx파일들 불러옴
    df=pd.read_excel(f, sheet_name=None)
    all_data1 = pd.concat(df, ignore_index=True)
    all_data=all_data.append(all_data1,ignore_index=True)
#######추가######
for f in glob.glob("rawdata/*.csv"): #csv 불러옴
    df=pd.read_csv(f,index_col=None, header=0, encoding='UTF8')
    all_data1 = pd.concat([df], ignore_index=True)
    all_data=all_data.append(all_data1,ignore_index=True)
#################
all_data.to_excel('합본.xlsx',encoding='utf-8-sig') #저장
#엑셀로 저장하는게 오래 걸릴 때에는, csv로 저장할 수도 있습니다.
# all_data.to_csv('합본.csv',encoding='utf-8-sig') 

cs

2. 위의 상황에 덧붙여, 네번째 파일 (성별 column 존재)이 추가된 경우

일부러 파일 포맷을 csv로 해보았습니다.

4.csv

0.00MB

위의 코드를 그대로 쓰면 됩니다. 결과는 다음과 같습니다.

1,2,3번 엑셀 파일에는 성별 정보가 없어서, 해당 행에는 성별 열을 비워둔 것을 확인할 수 있습니다.

3. 마지막으로, 열의 이름이 다른 다섯번째 파일이 추가된 경우 (최종 코드)

5.xlsx

0.01MB

Name을 이름 행에, Age를 나이 행에, Gender를 성별 행에 합치는 경우입니다. df의 type은 dictionary이기 때문에, Key를 일일이 바꿔주는 방식으로 해결할 수 있습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

import pandas as pd
import glob
import xlrd
import openpyxl
 
#우선 모든 자료들을 raw data folder에 저장
all_data = pd.DataFrame()
all_data1 = pd.DataFrame()
for f in glob.glob("rawdata/*.xlsx"): #rawdata 디렉토리에 있는 xlsx파일들 불러옴
    df=pd.read_excel(f, sheet_name=None)
    all_data1 = pd.concat(df, ignore_index=True)
######추가#####
    all_data1.rename(columns={"Gender":"성별","Age":"나이","Name":"이름"},inplace=True) #추가된 부분
##############
    all_data=all_data.append(all_data1,ignore_index=True)
 
for f in glob.glob("rawdata/*.csv"): #csv 불러옴
    df=pd.read_csv(f,index_col=None, header=0, encoding='UTF8')
    all_data1 = pd.concat([df], ignore_index=True)
    all_data1.rename(columns={"Gender":"성별","Age":"나이","Name":"이름"},inplace=True)
    all_data=all_data.append(all_data1,ignore_index=True)
 
all_data.to_excel('합본.xlsx',encoding='utf-8-sig') #저장
# all_data.to_csv('합본.csv',encoding='utf-8-sig') #csv로 저장
Colored by Color Scripter

cs