import pandas as pd
import numpy as np
np.random.seed(0)
category1List = ["수익", "비용"]
category2Dictionary = {"수익" : ["영업수익", "영업외수익"], "비용" : ["영업비용", "영업외비용"]}
category3Dictionary = {
"영업수익" : ["제품매출", "용역매출" ],
"영업외수익" : ["이자수익", "배당금수익"],
"영업비용" : ["인건비" , "재료비" ],
"영업외비용" : ["이자비용", "기부금" ]
}
sourceList = []
def generateAccountCode(대분류, 중분류, 소분류):
return f"{대분류[:1]}{중분류[:1]}{소분류[:1]}"
for category1 in category1List:
for category2 in category2Dictionary[category1]:
for category3 in category3Dictionary[category2]:
accountCode = generateAccountCode(category1, category2, category3)
for month in range(1, 13):
targetAmount = np.random.randint(10000, 100000)
actualAmount = np.random.randint(8000 , 120000)
sourceList.append([category1, category2, category3, accountCode, f"2023-{month:02d}", targetAmount, actualAmount])
sourceDataFrame = pd.DataFrame(sourceList, columns = ["대분류", "중분류", "소분류", "계정코드", "해당월", "목표금액", "실적금액"])
pivotDataFrame = pd.pivot_table(
sourceDataFrame,
values = ["목표금액", "실적금액"],
index = ["대분류", "중분류", "소분류"],
columns = ["해당월"],
aggfunc = "sum",
fill_value = 0,
margins = True,
margins_name = "합계"
)
reorderedPivotDataFrame = pivotDataFrame.reorder_levels([1, 0], axis = 1).sort_index(axis = 1)
def addSubtotal(sourceDataFrame):
levelCount = sourceDataFrame.index.nlevels
copyDataFrame = sourceDataFrame.copy()
for i in range(levelCount - 1, 0, -1):
groupbyDataFrame = sourceDataFrame.groupby(level = list(range(i))).sum()
for indexTuple in groupbyDataFrame.index:
if isinstance(indexTuple, tuple):
newIndexTuple = indexTuple + ('소계',) * (levelCount - len(indexTuple))
else:
newIndexTuple = (indexTuple,) + ('소계',) * (levelCount - 1)
copyDataFrame.loc[newIndexTuple] = groupbyDataFrame.loc[indexTuple]
return copyDataFrame.sort_index()
# 소계를 추가한다.
resultDataFrame = addSubtotal(reorderedPivotDataFrame)
# 결과를 출력한다.
print(resultDataFrame)
# CSV 파일로 저장한다.
resultDataFrame.to_csv("hierarchical_aggregation.csv")
