엑셀로 데이터를 정리해서 관리하다 보면 종종 동일한 양식의 두 파일을 비교해야 할 때가 있습니다. 과거에 만들어 놓은 파일 A와 새로 업데이트한 파일 B를 비교하는 경우 말입니다. 이렇게 두 개의 파일을 비교할 때 확인하고 싶은 내용은 보통 세 가지 정도 일 것 같은데요.
1. 새로 생긴 데이터
2. 없어진 데이터
3. 정보가 변경된 데이터
이번 포스팅에서 이런 작업을 파이썬 판다스 라이브러리를 사용해서 처리하는 방법을 다루려 합니다. 약간 난이도가 있어서 판다스 자체가 처음이시라면 조금 어려울지도 모르겠습니다.
예를 들어 다음과 같이 생긴 두 개의 엑셀 파일이 있습니다. 두 파일의 차이점을 찾아서 그 결과를 새로운 엑셀 파일로 만들어주는 코드를 작성하겠습니다.
삭제된 데이터와 새로 추가된 데이터를 찾아내기
두 파일을 비교하기 위해서는, 두 파일 모두 동일한 컬럼 순서를 가지고 있고 개별 행 데이터를 구분할 수 있는 고유한 값을 지닌 컬럼이 존재해야 합니다. 예제 코드에서는 '아이디' 컬럼을 활용하겠습니다.
# pandas 라이브러리를 불러옵니다.
import pandas as pd
# 비교할 엑셀 파일들을 불러옵니다.
df_old = pd.read_excel('data_old.xlsx')
df_new = pd.read_excel('data_new.xlsx')
어떤 파일이 예전에 만들어진 파일이고 어떤 파일이 새로운 파일인지 구분하기 위해 컬럼을 추가합니다.
df_old['ver'] = 'old'
df_new['ver'] = 'new'
set 함수를 이용하면 손쉽게 새로 추가된 데이터와 이전에 있었지만 삭제된 데이터를 발라낼 수 있습니다.
id_dropped = set(df_old['아이디']) - set(df_new['아이디'])
id_added = set(df_new['아이디']) - set(df_old['아이디'])
print('삭제된 아이템: ',id_dropped)
print('추가된 아이템: ',id_added)
df_dropped = df_old[df_old['아이디'].isin(id_dropped)].iloc[:,:-1]
df_added = df_new[df_new['아이디'].isin(id_added)].iloc[:,:-1]
이전에 존재했다가 삭제된 데이터와 새로 추가된 데이터를 찾아내고, 그 결과를 새로운 데이터프레임으로 각각 만들었습니다.
내용이 변경된 데이터 찾기
내용이 바뀐 데이터를 찾는 것은 조금 더 처리가 필요합니다. 주석을 천천히 읽어주세요.
# 두 데이터프레임을 하나로 합칩니다.
df_concatted = pd.concat([df_old, df_new], ignore_index=True)
# 모든 컬럼의 내용이 중복되는 데이터는 삭제합니다.
changes = df_concatted.drop_duplicates(df_concatted.columns[:-1], keep='last')
# 남은 데이터 중 동일한 아이디 값이 두개 이상 존재한다면
# 정보가 변경된 데이터입니다.
duplicated_list = changes[changes['아이디'].duplicated()]['아이디'].to_list()
df_changed = changes[changes['아이디'].isin(duplicated_list)]
이렇게 처리된 데이터프레임은 이전 데이터와 새롭게 업데이트된 데이터를 모두 담고 있습니다.
이렇게 처리된 데이터프레임은 위의 사진처럼 이전 데이터와 새롭게 업데이트된 데이터가 모두 들어있습니다. 다시 두 개의 데이터프레임으로 분리하겠습니다.
df_changed_old = df_changed[df_changed['ver'] == 'old'].iloc[:,:-1]
df_changed_old.sort_values(by='아이디', inplace=True)
df_changed_new = df_changed[df_changed['ver'] == 'new'].iloc[:,:-1]
df_changed_new.sort_values(by='아이디', inplace=True)
반복문으로 두 데이터프레임 내 값을 차례대로 비교해서 그 결과를 정리하겠습니다.
df_info_changed = df_changed_old.copy()
for i in range(len(df_changed_new.index)):
for j in range(len(df_changed_new.columns)):
if (df_changed_new.iloc[i, j] != df_changed_old.iloc[i, j]):
df_info_changed.iloc[i,j] = str(df_changed_old.iloc[i, j]) + " ==> " + str(df_changed_new.iloc[i,j])
비교 결과를 엑셀로 저장하기
두 파일을 비교한 결과를 엑셀로 저장하겠습니다. 이 파일은 세 개의 시트로 구성됩니다. ①내용이 변경된 데이터, ②새롭게 추가된 데이터, ③기존에 있다가 삭제된 데이터
with pd.ExcelWriter('compared_result.xlsx') as writer:
df_info_changed.to_excel(writer, sheet_name='info changed', index=False)
df_added.to_excel(writer, sheet_name='added', index=False)
df_dropped.to_excel(writer, sheet_name='dropped', index=False)
엑셀 파일을 열어보면 아래와 같이 시트별로 잘 정리된 것을 확인할 수 있습니다.
def compare_excel(old_xlsx, new_xlsx, column_name):
import pandas as pd
df_old = pd.read_excel(old_xlsx)
df_new = pd.read_excel(new_xlsx)
# 불러온 데이터의 버전 구분
df_old['ver'] = 'old'
df_new['ver'] = 'new'
id_dropped = set(df_old[column_name]) - set(df_new[column_name])
id_added = set(df_new[column_name]) - set(df_old[column_name])
# 삭제된 데이터
df_dropped = df_old[df_old[column_name].isin(id_dropped)].iloc[:,:-1]
# 추가된 데이터
df_added = df_new[df_new[column_name].isin(id_added)].iloc[:,:-1]
df_concatted = pd.concat([df_old, df_new], ignore_index=True)
changes = df_concatted.drop_duplicates(df_concatted.columns[:-1], keep='last')
duplicated_list = changes[changes[column_name].duplicated()][column_name].to_list()
df_changed = changes[changes[column_name].isin(duplicated_list)]
df_changed_old = df_changed[df_changed['ver'] == 'old'].iloc[:,:-1]
df_changed_old.sort_values(by=column_name, inplace=True)
df_changed_new = df_changed[df_changed['ver'] == 'new'].iloc[:,:-1]
df_changed_new.sort_values(by=column_name, inplace=True)
# 정보가 변경된 데이터 정리
df_info_changed = df_changed_old.copy()
for i in range(len(df_changed_new.index)):
for j in range(len(df_changed_new.columns)):
if (df_changed_new.iloc[i, j] != df_changed_old.iloc[i, j]):
df_info_changed.iloc[i,j] = str(df_changed_old.iloc[i, j]) + " ==> " + str(df_changed_new.iloc[i,j])
# 엑셀 저장
with pd.ExcelWriter('compared_result.xlsx') as writer:
df_info_changed.to_excel(writer, sheet_name='info changed', index=False)
df_added.to_excel(writer, sheet_name='added', index=False)
df_dropped.to_excel(writer, sheet_name='dropped', index=False)
- 이 글은 아나콘다(Anaconda3)가 설치된 주피터 노트북에서 작성되었습니다.