컬럼정리
병합된 데이터프레임
각각의 엑셀을 읽어서 병합하고 데이터프레임으로 만들었습니다. 이제 본격적으로 전처리를 해야 합니다.
전처리는 원본 데이터를 분석하기 좋게 다듬는 작업입니다. 빠진 값을 채우고, 이상한 값을 고치고, 데이터 형식을 맞추고, 중복된 내용을 정리합니다. 특히 서로 다른 시스템의 데이터를 비교할 때는 형식을 맞추는 게 정말 중요합니다.
import glob
import pandas as pd
# 파일 읽기 및 통합 함수
def readFiles(pattern, header=None):
files = glob.glob(pattern)
data = [pd.read_excel(f, header=header) for f in files]
return pd.concat(data, ignore_index=True)
# 택스 파일 처리
hts = readFiles('*매출전자세금계산서목록*', header=5)
# ERP 파일 처리
erp = readFiles('*부가세수정_매출*.xlsx', header=3)
Python
복사
이것이 지난번에 완성한 코드입니다. 이제 두 데이터프레임을 비교하기 위해서는 컬럼들을 확인해야 합니다.
컬럼출력
각각의 데이터프레임이 가진 컬럼을 비교하기 전에 컬럼을 출력하여 확인해보겠습니다. dataframe.columns를 하면 데이터프레임이 가진 컬럼을 반환해줍니다. [pandas index & column 페이지를 참고]
print('erp컬럼:',erp.columns)
print('hts컬럼:',hts.columns)
Python
복사
유니크 컬럼은 무엇인가?
데이터프레임에서 고유한 식별자(유니크 키) 역할을 할 컬럼이 필요합니다. 세금계산서 승인번호는 홈택스에서 발행되는 고유한 값이므로 이상적인 키가 됩니다. 하지만 회사 ERP의 경우, 전표 기준으로 작성되어 세금계산서 승인번호를 관리하지 않는 경우도 있습니다. 이는 회사마다 다른 상황이므로, 각자 HTS 세금계산서와 ERP 세금계산서를 매핑할 수 있는 적절한 컬럼을 선택해주시기 바랍니다.
유니크 컬럼이 없다면??
각각을 매핑할 수 있는 컬럼이 없다면 대안이 필요합니다. 제가 선택한 대안은 이렇습니다. "공급자사업자등록번호"는 유니크한 회사키가 될 수 있습니다. 그렇다면 사업자등록번호를 기준으로 공급가와 부가세가 같은 것을 순서대로 비교해서 나열하고 일치하지 않는 곳은 비워주는 겁니다.
이렇게 하면 마지막에 불부합자료가 나오겠지요? 꼭 발행일자가 동일하지 않아도 좋습니다. 국세청에서 확인하는 세금계산서 부합자료는 각 과세기간의 세금계산서 발행매수, 총공급가액, 총부가세액입니다. 발행한 측과 발행받은 측이 일치하는지를 체크하는 것이므로 우리는 회사별 발행매수를 맞추는 작업을 하면 됩니다.
필요한 컬럼만 선택
pandas에서는 데이터프레임의 열을 쉽게 선택할 수 있습니다. 이중 리스트로 컬럼을 묶어서 선택하면 됩니다. 이때 필요한 컬럼만 별도의 데이터프레임으로 저장하거나 hts 변수에 다시 할당할 수 있습니다. 저는 재할당하지 않고 대문자로 HTS에 할당하겠습니다. 다른변수에 재할당 할때는 .copy()로 원본을 카피하는것을 명시적으로 넣어주는게 좋습니다. 키가 되는 컬럼은 반드시 포함해야 하고, 불부합 발생 시 회사명과 발행일자 등을 확인하기 위한 컬럼도 함께 선택해야 합니다.
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS
Python
복사
이렇게 별도의 데이터프레임을 만들었습니다. 이는 비교 작업을 시각적으로 더 명확하게 하기 위해 데이터를 줄인 것입니다. 하지만 이 작업은 마지막에 해도 되고, 아예 하지 않고 데이터 원본 그대로를 사용해도 좋습니다.
컬럼명 수정
재선택된 데이터프레임은 나중에 ERP 데이터프레임과 병합할 것입니다. 병합할 때 컬럼이 중복될 수 있으므로, 컬럼명에 구분자를 추가하겠습니다.
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS.columns = 'HTS_' + HTS.columns
HTS
Python
복사
데이터 정리
사업자등록번호 개선
사업자등록번호를 주요 키로 사용할 것입니다. 이 키에서 하이픈("-") 문자를 제거하겠습니다.
hts = readFiles('*매출전자세금계산서목록*', header=5)
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS.columns = 'HTS_' + HTS.columns
HTS['HTS_공급받는자사업자등록번호'] = HTS['HTS_공급받는자사업자등록번호'].str.replace('-', '', regex=False)
HTS
Python
복사
Pandas의 DataFrame에서 문자열(string) 데이터를 다룰 때는 .str 접근자를 사용해야 합니다. 이는 pandas가 내부적으로 문자열 데이터를 효율적으로 처리하기 위한 특별한 방식입니다. DataFrame의 열에 직접 문자열 메서드를 적용할 수 없는 이유는, DataFrame의 열이 여러 가지 데이터 타입을 포함할 수 있기 때문입니다.
.str을 사용함으로써 pandas는 해당 열을 문자열 데이터로 취급하고 문자열 관련 작업을 수행할 수 있게 됩니다. .replace() 메서드는 문자열 내의 특정 패턴을 다른 패턴으로 교체하는 기능을 합니다. 여기서는 사업자등록번호에서 하이픈('-')을 빈 문자열('')로 교체하고 있습니다.
regex=False 파라미터는 정규표현식을 사용하지 않겠다는 의미입니다. 단순히 문자 그대로의 하이픈만 교체하려는 경우에는 정규표현식이 필요하지 않으므로, 이를 False로 설정하여 처리 속도를 향상시킬 수 있습니다.
정규표현식(Regular Expression)은 문자열을 처리하기 위한 특별한 패턴 언어입니다. 예를 들어 '\d+'는 '하나 이상의 숫자'를 의미하고, '[A-Z]'는 '대문자 알파벳'을 의미합니다. 이를 통해 복잡한 문자열 검색, 추출, 치환 작업을 간단하게 수행할 수 있지만, 단순한 문자 치환의 경우에는 일반 문자열 메서드를 사용하는 것이 더 효율적일 수 있습니다.
정렬하기
데이터를 비교하기 전에 먼저 정렬을 하겠습니다. 사업자등록번호를 기준으로 정렬하고, 그 다음으로 공급가액과 세액 순으로 정렬합니다. 이때 동일한 금액이 존재할 수 있음을 유의해야 합니다.
hts = readFiles('*매출전자세금계산서목록*', header=5)
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS.columns = 'HTS_' + HTS.columns
HTS['HTS_공급받는자사업자등록번호'] = HTS['HTS_공급받는자사업자등록번호'].str.replace('-', '', regex=False)
HTS = HTS.sort_values(by = ['HTS_공급받는자사업자등록번호','HTS_공급가액','HTS_세액'],ascending = [True,True,True])
HTS
Python
복사
sort_values는 pandas DataFrame을 정렬하는 메서드입니다. 여기서는 세 가지 컬럼을 기준으로 정렬을 수행했습니다. by 매개변수에는 정렬 기준이 되는 컬럼들의 리스트를 지정했고, ascending 매개변수에는 각 컬럼별 정렬 방향을 지정했습니다.
먼저 'HTS_공급받는자사업자등록번호'를 기준으로 오름차순 정렬을 하고, 동일한 사업자등록번호 내에서는 'HTS_공급가액'을 기준으로 오름차순 정렬을 합니다. 마지막으로 공급가액도 같은 경우에는 'HTS_세액'을 기준으로 오름차순 정렬을 수행합니다.
ascending=[True,True,True]는 모든 컬럼에 대해 오름차순 정렬을 적용한다는 의미입니다. False로 설정하면 해당 컬럼에 대해 내림차순 정렬이 적용됩니다.
홈택스 vs ERP 병합용 키생성
데이터를 바로 병합할 수도 있지만, 홈택스 세금계산서와 ERP 세금계산서를 더 보기 좋게 비교하기 위해서는 병합용 키 컬럼을 먼저 생성하는 것이 좋습니다. 이 키를 사용해 병합한 후에는 키 컬럼을 삭제할 수 있으며, 이렇게 하면 불일치 자료를 더 명확하게 구분할 수 있습니다.
hts = readFiles('*매출전자세금계산서목록*', header=5)
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS.columns = 'HTS_' + HTS.columns
HTS['HTS_공급받는자사업자등록번호'] = HTS['HTS_공급받는자사업자등록번호'].str.replace('-', '', regex=False)
HTS = HTS.sort_values(by = ['HTS_공급받는자사업자등록번호','HTS_공급가액','HTS_세액'],ascending = [True,True,True])
# 신규 컬럼생성
HTS = HTS.assign(
KEY_사업자등록번호=HTS['HTS_공급받는자사업자등록번호'],
KEY_공급가액=HTS['HTS_공급가액'],
KEY_세액=HTS['HTS_세액']
)
# assign과 동일한 결과
# HTS[['KEY_사업자등록번호','KEY_공급가액','KEY_세액']] = HTS[['HTS_공급받는자사업자등록번호','HTS_공급가액','HTS_세액']].copy()
Python
복사
assign() 메서드는 pandas DataFrame에 새로운 컬럼을 추가하는 효율적인 방법입니다. 이 메서드는 기존 DataFrame을 수정하지 않고 새로운 컬럼이 추가된 새로운 DataFrame을 반환합니다.
위 코드에서는 세 개의 새로운 키 컬럼을 생성했습니다:
•
KEY_사업자등록번호: HTS_공급받는자사업자등록번호 컬럼의 값을 복사
•
KEY_공급가액: HTS_공급가액 컬럼의 값을 복사
•
KEY_세액: HTS_세액 컬럼의 값을 복사
이렇게 생성된 KEY_ 컬럼들은 나중에 홈택스와 ERP 데이터를 비교할 때 매칭 키로 사용될 것입니다. assign() 메서드를 사용하면 여러 컬럼을 한 번에 추가할 수 있어 코드가 더 간결해지고 가독성이 향상됩니다.
중복키 체크
마지막으로 한 가지를 더 처리하겠습니다. 키로 사용할 사업자등록번호, 공급가액, 세액이 두 개 이상 존재할 수 있습니다. 이런 경우 병합 시 여러 개의 키가 있으면 정확한 매칭이 어렵습니다. 따라서 중복되는 경우에는 해당 항목을 카운팅하여 추가 키로 활용하겠습니다.
hts = readFiles('*매출전자세금계산서목록*', header=5)
HTS = hts[['작성일자','승인번호','공급받는자사업자등록번호', '상호.1','공급가액', '세액']].copy()
HTS.columns = 'HTS_' + HTS.columns
HTS['HTS_공급받는자사업자등록번호'] = HTS['HTS_공급받는자사업자등록번호'].str.replace('-', '', regex=False)
HTS = HTS.sort_values(by = ['HTS_공급받는자사업자등록번호','HTS_공급가액','HTS_세액'],ascending = [True,True,True])
HTS = HTS.assign(
KEY_사업자등록번호=HTS['HTS_공급받는자사업자등록번호'],
KEY_공급가액=HTS['HTS_공급가액'],
KEY_세액=HTS['HTS_세액']
)
HTS['KEY_중복체크'] = HTS.groupby(['KEY_사업자등록번호', 'KEY_공급가액']).cumcount() + 1
HTS
Python
복사
위 코드에서 groupby()와 cumcount()를 함께 사용하여 중복된 데이터를 처리하고 있습니다:
•
groupby(['KEY_사업자등록번호', 'KEY_공급가액']): 사업자등록번호와 공급가액을 기준으로 데이터를 그룹화합니다.
•
cumcount(): 각 그룹 내에서 행의 순서대로 0부터 시작하는 번호를 매깁니다.
•
+ 1: 0부터가 아닌 1부터 시작하는 번호를 부여하기 위해 1을 더합니다.
예를 들어, 동일한 사업자등록번호와 공급가액을 가진 데이터가 3개 있다면
•
첫 번째 행은 KEY_중복체크 = 1
•
두 번째 행은 KEY_중복체크 = 2
•
세 번째 행은 KEY_중복체크 = 3
이렇게 생성된 KEY_중복체크 컬럼은 나중에 데이터를 매칭할 때 동일한 값을 가진 데이터를 정확하게 구분할 수 있게 해줍니다.
ERP내역 정리
홈택스에서 정리한 컬럼명과 데이터 정리 방식을 ERP에도 동일하게 적용해야 합니다. ERP의 컬럼명은 회사마다 다르기 때문에, 자신의 데이터 컬럼을 참고하여 필요한 항목을 선택하면 됩니다. 단, 사업자등록번호, 공급가, 세액은 비교를 위한 필수 항목이므로 반드시 포함해야 합니다.
erp = readFiles('*부가세수정_매출*.xlsx', header=3)
ERP = erp[['발행일자','사업자등록번호','거래처명','공급가액','부가세금액','부가세유형명','전표번호']].copy()
ERP.columns = 'ERP_' + ERP.columns
ERP['ERP_사업자등록번호'] = ERP['ERP_사업자등록번호'].str.replace('-', '', regex=False)
ERP = ERP.sort_values(by = ['ERP_사업자등록번호','ERP_공급가액','ERP_부가세금액'],ascending = [True,True,True])
ERP = ERP.assign(
KEY_사업자등록번호=ERP['ERP_사업자등록번호'],
KEY_공급가액=ERP['ERP_공급가액'],
KEY_세액=ERP['ERP_부가세금액']
)
ERP['KEY_중복체크'] = ERP.groupby(['KEY_사업자등록번호', 'KEY_공급가액']).cumcount() + 1
Python
복사
코드 리팩토링
이제 지금까지 작성한 코드를 하나로 정리하겠습니다. 반복되는 코드는 함수로 만들고, 매개변수를 활용하여 각 부분을 설정하겠습니다.
리팩토링은 기존 코드의 기능은 유지하면서 코드의 구조와 가독성을 개선하는 작업입니다. 중복된 코드를 함수화하고, 변수명을 더 명확하게 만들며, 코드의 흐름을 더 효율적으로 재구성합니다. 이를 통해 코드의 유지보수성이 높아지고 재사용성이 향상됩니다.
import glob
import pandas as pd
# 파일 읽기 및 통합 함수
def readFiles(pattern, header=None):
files = glob.glob(pattern)
data = [pd.read_excel(f, header=header) for f in files]
return pd.concat(data, ignore_index=True)
def readAndProcessFile(pattern, header, prefix, columns, keyColumns):
df = readFiles(pattern, header=header)
df = df[columns].copy()
df.columns = f'{prefix}_' + df.columns
df[f'{prefix}_{keyColumns[0]}'] = (
df[f'{prefix}_{keyColumns[0]}'].astype(str).str.replace('-', '', regex=False)
)
df = df.sort_values(
by=[
f'{prefix}_{keyColumns[0]}',
f'{prefix}_{keyColumns[1]}',
f'{prefix}_{keyColumns[2]}'
],
ascending=[True, True, True]
)
df = df.assign(
KEY_사업자등록번호=df[f'{prefix}_{keyColumns[0]}'],
KEY_공급가액=df[f'{prefix}_{keyColumns[1]}'],
KEY_세액=df[f'{prefix}_{keyColumns[2]}']
)
df['KEY_중복체크'] = df.groupby(['KEY_사업자등록번호', 'KEY_공급가액']).cumcount() + 1
return df
# 1) HTS 파일 처리
htsColumns = ['작성일자', '승인번호', '공급받는자사업자등록번호', '상호.1', '공급가액', '세액']
htsKeyCols = ['공급받는자사업자등록번호', '공급가액', '세액'] # keyColumns: ['사업자등록번호', '공급가액', '세액']
htsProcessed = readAndProcessFile(
pattern='*매출전자세금계산서목록*', # HTS 파일 패턴 (예시)
header=5, # HTS 파일에서 헤더가 있는 행 (예: 5행)
prefix='hts', # HTS 데이터 접두사
columns=htsColumns, # 위에서 정의한 htsColumns
keyColumns=htsKeyCols # 위에서 정의한 htsKeyCols
)
# 2) ERP 파일 처리
erpColumns = ['발행일자', '사업자등록번호', '거래처명', '공급가액', '부가세금액', '부가세유형명', '전표번호']
erpKeyCols = ['사업자등록번호', '공급가액', '부가세금액'] # ERP 파일에서 세액에 해당하는 컬럼은 '부가세금액'
erpProcessed = readAndProcessFile(
pattern='*부가세수정_매출*.xlsx', # ERP 파일 패턴 (예시)
header=3, # ERP 파일에서 헤더가 있는 행 (예: 3행)
prefix='erp', # ERP 데이터 접두사
columns=erpColumns, # 위에서 정의한 erpColumns
keyColumns=erpKeyCols # '사업자등록번호', '공급가액', '부가세금액'
)
Python
복사
키컬럼으로 병합
앞서 생성한 KEY 컬럼들을 사용하여 HTS와 ERP 데이터프레임을 병합하겠습니다. pandas의 merge 함수를 활용하면 손쉽게 데이터를 병합할 수 있습니다. ERP와 홈택스(HTS) 데이터를 병합하고 있습니다. 병합할 때는 4개의 KEY 컬럼(사업자등록번호, 공급가액, 세액, 중복체크)을 기준으로 합니다. merge는 다른 페이지에서 추가로 다루겠습니다.
mergeResult = pd.merge(
erpProcessed,
htsProcessed,
on=['KEY_사업자등록번호','KEY_공급가액','KEY_세액','KEY_중복체크'], # 4개 컬럼으로 병합
how='outer'
)
mergeResult
Python
복사
불부합 추출
병합된 결과에서 한쪽 시스템에만 데이터가 있는 경우 빈 값으로 표시됩니다. 고유한 키 값을 기준으로 병합했기 때문에, 같은 키 값이 있으면 데이터가 병합되고 없으면 빈 값이 됩니다. ERP와 홈택스 데이터 간의 불일치를 찾기 위해 isnull() 함수를 사용하여 결측값이 있는 항목을 찾아냅니다. 구체적으로, ERP와 홈택스 양쪽의 사업자등록번호 중 빈 값(결측값)이 있는 경우를 추출합니다. 각 조건을 괄호로 묶고 "|"(또는) 연산자를 사용하여 ERP 또는 홈택스 쪽의 사업자등록번호가 비어있는 행을 찾습니다.
mergeResult.loc[(mergeResult['erp_사업자등록번호'].isnull())|(mergeResult['hts_공급받는자사업자등록번호'].isnull())]
Python
복사
불부합 결과 엑셀로 내보내기
불부합으로 추출된 데이터프레임을 엑셀 파일로 내보내겠습니다. 추출된 결과를 final 변수에 저장하고 pandas의 to_excel() 메서드를 사용합니다. to_excel() 함수에 내보낼 파일명을 매개변수로 지정하면 전처리 과정이 완료됩니다.
final = mergeResult.loc[(mergeResult['erp_사업자등록번호'].isnull())|(mergeResult['hts_공급받는자사업자등록번호'].isnull())]
final.to_excel('불부합결과.xlsx')
Python
복사
전체코드
import glob
import pandas as pd
# 파일 읽기 및 통합 함수
def readFiles(pattern, header=None):
files = glob.glob(pattern)
data = [pd.read_excel(f, header=header) for f in files]
return pd.concat(data, ignore_index=True)
def readAndProcessFile(pattern, header, prefix, columns, keyColumns):
df = readFiles(pattern, header=header)
df = df[columns].copy()
df.columns = f'{prefix}_' + df.columns
df[f'{prefix}_{keyColumns[0]}'] = (
df[f'{prefix}_{keyColumns[0]}'].astype(str).str.replace('-', '', regex=False)
)
df = df.sort_values(
by=[
f'{prefix}_{keyColumns[0]}',
f'{prefix}_{keyColumns[1]}',
f'{prefix}_{keyColumns[2]}'
],
ascending=[True, True, True]
)
df = df.assign(
KEY_사업자등록번호=df[f'{prefix}_{keyColumns[0]}'],
KEY_공급가액=df[f'{prefix}_{keyColumns[1]}'],
KEY_세액=df[f'{prefix}_{keyColumns[2]}']
)
df['KEY_중복체크'] = df.groupby(['KEY_사업자등록번호', 'KEY_공급가액']).cumcount() + 1
return df
htsColumns = ['작성일자', '승인번호', '공급받는자사업자등록번호', '상호.1', '공급가액', '세액']
htsKeyCols = ['공급받는자사업자등록번호', '공급가액', '세액'] # keyColumns: ['사업자등록번호', '공급가액', '세액']
htsProcessed = readAndProcessFile(
pattern='*매출전자세금계산서목록*', # HTS 파일 패턴 (예시)
header=5, # HTS 파일에서 헤더가 있는 행 (예: 5행)
prefix='hts', # HTS 데이터 접두사
columns=htsColumns, # 위에서 정의한 htsColumns
keyColumns=htsKeyCols # 위에서 정의한 htsKeyCols
)
# 2) ERP 파일 처리
erpColumns = ['발행일자', '사업자등록번호', '거래처명', '공급가액', '부가세금액', '부가세유형명', '전표번호']
erpKeyCols = ['사업자등록번호', '공급가액', '부가세금액'] # ERP 파일에서 세액에 해당하는 컬럼은 '부가세금액'
erpProcessed = readAndProcessFile(
pattern='*부가세수정_매출*.xlsx', # ERP 파일 패턴 (예시)
header=3, # ERP 파일에서 헤더가 있는 행 (예: 3행)
prefix='erp', # ERP 데이터 접두사
columns=erpColumns, # 위에서 정의한 erpColumns
keyColumns=erpKeyCols # '사업자등록번호', '공급가액', '부가세금액'
)
# 3) 데이터 병합
mergeResult = pd.merge(
erpProcessed,
htsProcessed,
on=['KEY_사업자등록번호','KEY_공급가액','KEY_세액','KEY_중복체크'], # 4개 컬럼으로 병합
how='outer'
)
# 불부합 추출(엑셀내보내기)
final = mergeResult.loc[(mergeResult['erp_사업자등록번호'].isnull())|(mergeResult['hts_공급받는자사업자등록번호'].isnull())]
final.to_excel('불부합결과.xlsx')
Python
복사
추가업무
매출세금계산서의 ERP와 홈택스 간 불부합결과 추출은 부가세 검증업무의 시작입니다. 다음 단계로는
1.
불부합 원인 분석
2.
잘못된 세금계산서 발행 시
•
고객 통보
•
수정세금계산서 발행
•
수정전표 작성
이후 재검증 작업을 통해 불부합 자료가 완전히 해소될 때까지 과정을 반복합니다. 같은 방식으로 매입세금계산서도 검증을 진행합니다.
마무리
지금까지 부가세 신고를 위한 세금계산서 검증 업무를 파이썬으로 더 편리하게 만드는 과정을 진행했습니다. 완전 자동화가 아니라서 아쉽다고요? 이 시스템이 업무에 정착되면 부가세 신고 시의 어려움이 크게 줄어들 것입니다. 다음 단계로는 마우스와 키보드를 자동화하여 세금계산서를 매일 자동으로 가져오는 작업에 도전해 보세요! 매일 자동으로 세금계산서를 수집하고, 이를 ERP와 연계하여 세금계산서 기반의 지출결의 시스템으로 발전시키며, 단계적으로 자동화와 반자동화를 확대해 나가면 업무 효율성을 크게 높일 수 있을 것입니다.
이번 경험을 기반으로 더 다양한 업무 개선의 시작점이 되길 바랍니다. 여러분의 가정에 평안과 소소한 행복이 함께하시길 바랍니다. 감사합니다.
