■ DataFrame 클래스에서 피벗 데이터 생성시 합계/소계부터 표시하는 방법을 보여준다.
▶ main.py
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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") |
▶ requirements.txt
|
1 2 3 4 5 6 7 8 |
numpy==2.1.2 pandas==2.2.3 python-dateutil==2.9.0.post0 pytz==2024.2 six==1.16.0 tzdata==2024.2 |