아래와 같은 테이블을 구축하기 위해 <저번 글>에서 고령인구비율(%) 전처리를 진행했습니다. 이번에는 예산집행률을 계산하기 위해 세입세출 결산자료를 전처리하는 과정을 다루도록 하겠습니다.
세입세출 데이터 다운로드
이번에 사용할 데이터는 지방재정365에서 제공하는 ‘성질별 단체별 세입(세출)결산’입니다. ‘성질별 단체별 세입(세출)결산’은 지방재정365 재정 데이터셋에 있습니다. 고령인구비율처럼 저와 동일한 데이터를 사용하고 싶으신 분이 계시다면, 아래 링크에서 다운로드할 수 있습니다.
지방재정365도 여러 형식과 형태로 데이터를 제공하고 있습니다. 저는 Time Series 유형으로 데이터를 다운로드 했습니다. 데이터를 열어보면 아래처럼 생겼습니다.
다운받은 데이터 전처리
세입세출 통합 데이터프레임 생성
세입세출 결산자료를 불러와서, 세입결산과 세출결산 DataFrame을 생성합니다.
세입결산 통합 데이터프레임 생성
이때 위 형태의 데이터를 원하는 형태로 변환하기 위해서는 (1) 여러 파일을 하나로 합치는 작업, (2) 2줄로 구성된 컬럼을 처리하는 작업, (3) 세목을 없애고 자치단체별 세입을 계산하는 작업, (4) wide 형태의 데이터를 long 형태로 변환하는 작업이 필요합니다.
"""
세입결산자료를 읽어서 세입을 합산하는 함수입니다.
DataFrame: 세입결산자료의 세입을 합산한 결과
1. 세입결산 엑셀파일을 하나의 DataFrame으로 합칩니다.
2. groupby를 사용하여 자치단체별 '세입'을 계산합니다.
3. 세입이 0인 자치단체와 값이 없는 자치단체를 제거합니다.
"""
# 세입결산 엑셀파일 경로를 읽어서 리스트로 저장
file_paths = glob.glob(
"rawdata/지방재정365-재정_데이터셋/성질별 단체별 세입결산_*.xlsx"
)
# stack()을 사용하기 위한 column index를 지정
idx = [
("No", "Unnamed: 0_level_1"),
("지역명", "Unnamed: 1_level_1"),
("자치단체코드", "Unnamed: 2_level_1"),
("자치단체명", "Unnamed: 3_level_1"),
("세목코드", "Unnamed: 4_level_1"),
("세목명", "Unnamed: 5_level_1"),
]
# 세입결산을 저장할 빈 DataFrame을 생성
foo = pd.DataFrame()
# 세입결산 엑셀파일을 하나의 DataFrame으로 합침
for file_path in file_paths:
# Read the Excel file, set the column index, stack the data, and reset the index
df = pd.read_excel(file_path, header=[0, 1]).set_index(idx).stack(future_stack=True).reset_index()
foo = pd.concat([foo, df])
# 컬럼명을 변경
foo.columns = [
"No",
"지역명",
"자치단체코드",
"자치단체명",
"세목코드",
"세목명",
"연도",
"value",
]
# groupby를 사용하여 자치단체별 '세입'을 계산
bar = (
foo.groupby(["지역명", "자치단체명", "연도"])["value"]
.sum()
.reset_index()
)
# Drop the rows where 'value' is 0
bar = bar[bar.value != 0]
# Drop the rows where 'value' is NaN
bar = bar.dropna(subset=["value"])
# bar를 세입결산으로 저장
세입결산 = bar.copy()
Code language: Python (python)세입결산 엑셀파일 경로를 읽어서 리스트로 저장
제가 다운로드 받은 ‘성질별 단체별 세입결산’ 파일은 총 5개입니다. 5개 정도는 각각 불러와서 처리해도 괜찮은 수 입니다. 하지만 나중을 위해서 리스트로 불러와서 for문으로 처리하도록 하겠습니다. glob.glob와 *을 이용한 정규식 등을 사용하면, ‘성질별 단체별 세입결산’ 파일을 리스트로 만들 수 있습니다.
file_paths = glob.glob(
"rawdata/지방재정365-재정_데이터셋/성질별 단체별 세입결산_*.xlsx"
)
Code language: Python (python)
stack()을 사용하기 위한 column index를 지정
엑셀 파일을 데이터프레임으로 읽어올 때, pd.read_excel(file_path, header=[0, 1])처럼 header=[0, 1]옵션을 추가해서 맨 위 2줄을 컬럼으로 불러와야 합니다. 엑셀 파일이 셀 병합으로 맨 위 2줄을 컬럼으로 사용하고 있기 때문입니다.
stack() 명령어를 이용해서 wide 타입 데이터를 long 타입으로 변형하기 위해서는 index를 지정해줘야 합니다. 이때, 인덱스는 No~세입금액이 됩니다. 멀티컬럼(컬럼이 2줄)인 경우 아래와 같이 인덱스를 지정해줍니다.
idx = [
("No", "Unnamed: 0_level_1"),
("지역명", "Unnamed: 1_level_1"),
("자치단체코드", "Unnamed: 2_level_1"),
("자치단체명", "Unnamed: 3_level_1"),
("세목코드", "Unnamed: 4_level_1"),
("세목명", "Unnamed: 5_level_1"),
]
Code language: Python (python)세입결산 엑셀파일을 하나의 DataFrame으로 만들기
위처럼 생긴 세입결산 엑셀파일을 하나의 DataFrame으로 만들겠습니다. (1) 엑셀 파일을 데이터프레임으로 불러오고, (2) idx를 인덱스로 지정해주고, (3) stack()으로 long타입 데이터로 변환합니다. (4) reset_index()로 인덱스를 다시 숫자로 초기화하고, (5) pd.concat()명령어로 합쳐줍니다. 이 모든 과정을 for문을 이용해서 각각의 엑셀 파일에 반복해서 적용합니다.
처음 foo라는 빈 데이터프레임을 생성하는 이유는, foo라는 빈 데이터프레임에 위 작업을 거쳐 변환된 데이터프레임을 합쳐서 세입결산 통합 데이터프레임을 생성할 것이기 때문입니다.
# 세입결산을 저장할 빈 DataFrame을 생성
foo = pd.DataFrame()
# 세입결산 엑셀파일을 하나의 DataFrame으로 합침
for file_path in file_paths:
# Read the Excel file, set the column index, stack the data, and reset the index
df = pd.read_excel(file_path, header=[0, 1]).set_index(idx).stack(future_stack=True).reset_index()
foo = pd.concat([foo, df])
Code language: PHP (php)컬럼명 변경
위 작업을 수행하면 아래처럼 컬럼은 한 줄이지만, 보기 어려운 이름의 컬럼이 생성됩니다. 그래서 보기 편하게 컬럼명을 수정합니다.
# 컬럼명을 변경
foo.columns = [
"No",
"지역명",
"자치단체코드",
"자치단체명",
"세목코드",
"세목명",
"연도",
"value",
]
Code language: PHP (php)groupby를 사용하여 자치단체별 ‘세입’을 계산
위 데이터프레임을 보면, 세목별로 구분된 세입금액을 보여주고 있습니다. 더 자세한 정보지만, 저에게 필요한 자료는 자치단체별 세입금액입니다. groupby 명령어를 통해서 자치단체별 세입금액을 계산하도록 하겠습니다. 지역과 자치단체를 조합하면 중복이 없으므로, 여기서는 자치단체코드는 제거하도록 하겠습니다.
# groupby를 사용하여 자치단체별 '세입'을 계산
bar = (
foo.groupby(["지역명", "자치단체명", "연도"])["value"]
.sum()
.reset_index()
)
Code language: PHP (php)마지막으로 불필요한 값(0이나 결측치)을 제거해주고, 세입결산 데이터프레임을 생성합니다. 세입결산 = bar로 세입결산 데이터프레임을 생성할 수도 있습니다. 하지만, 파이썬 복사 방법에 대해서 잘 모른다면 copy()를 습관적으로 붙이도록 합니다.
# Drop the rows where 'value' is 0
bar = bar[bar.value != 0]
# Drop the rows where 'value' is NaN
bar = bar.dropna(subset=["value"])
# bar를 세입결산으로 저장
세입결산 = bar.copy()
Code language: PHP (php)세출결산 통합 데이터프레임 생성
세출결산 통합 데이터프레임 생성도 세입결산과 완전히 동일합니다. 파일명만 수정하고, 마지막에 저장하는 데이터프레임 이름을 세입결산에서 세출결산으로 변경한 차이 밖에는 없습니다. 데이터 형태가 완전히 동일하고, 데이터프레임을 처리하는 과정에서 세입이나 세출과 같이 데이터 종류마다 수정이 필요한 명칭을 사용하지 않아서 그렇습니다.
"""
세출결산자료를 읽어서 세출을 합산하는 함수입니다.
위 세입결산 함수와 동일한 방법으로 세출결산을 계산합니다.
변경된 점은 file_paths만 다릅니다. (세입결산 > 세출결산)
"""
# 세출결산 엑셀파일 경로를 읽어서 리스트로 저장
file_paths = glob.glob(
"rawdata/지방재정365-재정_데이터셋/성질별 단체별 세출결산_*.xlsx"
)
# stack()을 사용하기 위한 column index를 지정
idx = [
("No", "Unnamed: 0_level_1"),
("지역명", "Unnamed: 1_level_1"),
("자치단체코드", "Unnamed: 2_level_1"),
("자치단체명", "Unnamed: 3_level_1"),
("세목코드", "Unnamed: 4_level_1"),
("세목명", "Unnamed: 5_level_1"),
]
# 세출결산을 저장할 빈 DataFrame을 생성
foo = pd.DataFrame()
# 세출결산 엑셀파일을 하나의 DataFrame으로 합침
for file_path in file_paths:
# Read the Excel file, set the column index, stack the data, and reset the index
df = pd.read_excel(file_path, header=[0, 1]).set_index(idx).stack(future_stack=True).reset_index()
foo = pd.concat([foo, df])
# 컬럼명을 변경
foo.columns = [
"No",
"지역명",
"자치단체코드",
"자치단체명",
"세목코드",
"세목명",
"연도",
"value",
]
# groupby를 사용하여 자치단체별 '세출'을 계산
bar = (
foo.groupby(["지역명", "자치단체명", "연도"])["value"]
.sum()
.reset_index()
)
# Drop the rows where 'value' is 0
bar = bar[bar.value != 0]
# Drop the rows where 'value' is NaN
bar = bar.dropna(subset=["value"])
# bar를 세출결산으로 저장
세출결산 = bar.copy()
Code language: PHP (php)세입세출 통합 데이터프레임 생성(심화 – 함수 사용)
같은 작업을 def(), 함수로 만들어서 사용하면 필요할 때 반복해서 쓸 수 있습니다. 조금 더 깔끔하고 효율적으로 코딩을 하고 싶으시면 공부해보세요.
"""
위 코드(세입결산과 세출결산)를 함수로 만들어서 사용하면 아래와 같습니다.
함수로 만들어서 사용하면, 코드를 간결하게 만들 수 있습니다.
2번 입력한 코드를 1번으로 줄일 수 있습니다.
위 2개의 코드와 아래의 코드는 동일한 결과를 출력합니다.
"""
def calculate_expenses(file_paths):
"""def는 함수를 정의할 때 사용하는 키워드입니다.
file_paths를 입력으로 받아서 아래 함수를 수행합니다.
file_paths에 세입결산 엑셀파일 경로를 입력하면, 세입결산을 계산합니다.
file_paths에 세출결산 엑셀파일 경로를 입력하면, 세출결산을 계산합니다.
"""
# stack()을 사용하기 위한 column index를 지정
idx = [
("No", "Unnamed: 0_level_1"),
("지역명", "Unnamed: 1_level_1"),
("자치단체코드", "Unnamed: 2_level_1"),
("자치단체명", "Unnamed: 3_level_1"),
("세목코드", "Unnamed: 4_level_1"),
("세목명", "Unnamed: 5_level_1"),
]
# 세출결산을 저장할 빈 DataFrame을 생성
foo = pd.DataFrame()
# 세출결산 엑셀파일을 하나의 DataFrame으로 합침
for file_path in file_paths:
# Read the Excel file, set the column index, stack the data, and reset the index
df = (
pd.read_excel(file_path, header=[0, 1])
.set_index(idx)
.stack(future_stack=True)
.reset_index()
)
foo = pd.concat([foo, df])
# 컬럼명을 변경
foo.columns = [
"No",
"지역명",
"자치단체코드",
"자치단체명",
"세목코드",
"세목명",
"연도",
"value",
]
# groupby를 사용하여 자치단체별 '세출'을 계산
bar = foo.groupby(["지역명", "자치단체명", "연도"])["value"].sum().reset_index()
# Drop the rows where 'value' is 0
bar = bar[bar.value != 0]
# Drop the rows where 'value' is NaN
bar = bar.dropna(subset=["value"])
return bar
# ---------------------------------------------------------------------
# 세입결산을 계산
file_paths = glob.glob( # 세입결산 엑셀파일 경로를 읽어서 리스트로 저장
"rawdata/지방재정365-재정_데이터셋/성질별 단체별 세입결산_*.xlsx"
)
# calculate_expenses 함수를 사용하면, bar를 생성합니다.
# bar를 세입결산으로 저장합니다.
세입결산 = calculate_expenses(file_paths)
# ---------------------------------------------------------------------
# 세출결산을 계산
file_paths = glob.glob( # 세출결산 엑셀파일 경로를 읽어서 리스트로 저장
"rawdata/지방재정365-재정_데이터셋/성질별 단체별 세출결산_*.xlsx"
)
# calculate_expenses 함수를 사용하면, bar를 생성합니다.
# bar를 세출결산으로 저장합니다.
세출결산 = calculate_expenses(file_paths)
Code language: PHP (php)광역지자체 및 본청 세입세출 산출
광역지자체 및 본청 세입세출을 산출해서, 기초지자체 세입세출 데이터 옆에 붙이려고 합니다. 위에서 제시한 테이블과 조금 다르지만, 광역(혹은 본청) 지자체 세입세출 자료가 통계분석에서 통제변수로 쓰일 수 있어서요.
이 부분은 길어져서 다음에 이어서 작성하도록 하겠습니다.
댓글 남기기