26J29h1
"""
[6단계 강화판] FinOps 마스터 엑셀 빌더 - 차트 삽입 + 서식 강화
"""
import pandas as pd
import numpy as np
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import (Font, PatternFill, Alignment, Border, Side,
GradientFill)
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image as XLImage
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.series import SeriesLabel
from openpyxl.chart.layout import Layout
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, CellIsRule, FormulaRule
from openpyxl.styles.differential import DifferentialStyle
MERGED_DIR = Path("/home/claude/data/merged")
PLOT_DIR = Path("/home/claude/data/output/plots")
OUT_DIR = Path("/home/claude/data/output")
OUT_DIR.mkdir(parents=True, exist_ok=True)
# ── Color Palette ──
C = {
"hdr_dark": "1F4E79",
"hdr_mid": "2E75B6",
"hdr_light": "BDD7EE",
"accent": "ED7D31",
"red": "C00000",
"green": "70AD47",
"yellow": "FFC000",
"gray_row": "F2F2F2",
"white": "FFFFFF",
"border": "9DC3E6",
"summary_bg":"EBF3FB",
}
def ft(bold=False, size=10, color="000000", name="Arial"):
return Font(name=name, bold=bold, size=size, color=color)
def fill(hex_color):
return PatternFill("solid", fgColor=hex_color)
def thin_border(all=True, bottom_only=False):
thin = Side(style="thin", color=C["border"])
med = Side(style="medium", color="2E75B6")
if bottom_only:
return Border(bottom=med)
return Border(left=thin, right=thin, top=thin, bottom=thin)
def center(wrap=False):
return Alignment(horizontal="center", vertical="center", wrap_text=wrap)
def left(wrap=False):
return Alignment(horizontal="left", vertical="center", wrap_text=wrap)
def set_col_widths(ws, widths: dict):
for col, w in widths.items():
ws.column_dimensions[col].width = w
def apply_header_row(ws, row_idx, headers, bg=C["hdr_dark"], fg=C["white"], bold=True, size=10):
for col_idx, h in enumerate(headers, 1):
cell = ws.cell(row=row_idx, column=col_idx, value=h)
cell.font = ft(bold=bold, size=size, color=fg)
cell.fill = fill(bg)
cell.alignment = center(wrap=True)
cell.border = thin_border()
def apply_data_rows(ws, df, start_row, num_formats=None, status_col_idx=None,
eff_col_indices=None, zebra=True):
"""
df의 데이터를 ws에 쓰고 서식 적용
num_formats: {col_idx(1-based): fmt_str}
eff_col_indices: utilization % 컬럼 목록 → color scale 조건부 서식
"""
nf = num_formats or {}
for r_offset, row in enumerate(df.itertuples(index=False), 0):
row_num = start_row + r_offset
bg_hex = C["gray_row"] if (r_offset % 2 == 1 and zebra) else C["white"]
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=row_num, column=c_idx, value=val)
cell.font = ft(size=9)
cell.fill = fill(bg_hex)
cell.alignment = left(wrap=False)
cell.border = thin_border()
if c_idx in nf:
cell.number_format = nf[c_idx]
# status 컬럼 색상
if status_col_idx and c_idx == status_col_idx:
v = str(val)
if "OOM" in v:
cell.fill = fill("FFCCCC"); cell.font = ft(bold=True, size=9, color=C["red"])
elif "부족" in v or "Request" in v:
cell.fill = fill("FFF2CC"); cell.font = ft(bold=True, size=9, color="7F6000")
elif "과다" in v:
cell.fill = fill("DDEEFF"); cell.font = ft(bold=True, size=9, color=C["hdr_dark"])
elif "최적" in v:
cell.fill = fill("E2EFDA"); cell.font = ft(bold=True, size=9, color="375623")
return start_row + len(df)
def freeze_and_filter(ws, row=2):
ws.freeze_panes = ws.cell(row=row+1, column=1)
ws.auto_filter.ref = ws.dimensions
def add_section_title(ws, row, col, text, bg=C["hdr_light"], fg=C["hdr_dark"]):
cell = ws.cell(row=row, column=col, value=text)
cell.font = ft(bold=True, size=11, color=fg)
cell.fill = fill(bg)
cell.alignment = left(wrap=False)
cell.border = thin_border(bottom_only=True)
# ─────────────────────────────────────────────
# Sheet 0: 전사 종합 요약
# ─────────────────────────────────────────────
def build_sheet_summary(wb, df_pod, df_ns):
ws = wb.active
ws.title = "0. 전사종합요약"
ws.sheet_view.showGridLines = False
ws.row_dimensions[1].height = 40
# 타이틀 배너
ws.merge_cells("A1:H1")
t = ws["A1"]
t.value = "FinOps Resource Governance Master Report"
t.font = ft(bold=True, size=16, color=C["white"])
t.fill = fill(C["hdr_dark"])
t.alignment = center()
# KPI 카드 (행 3~12)
total_containers = len(df_pod)
oom_cnt = int(df_pod["is_oom_killed"].sum())
no_req_cnt = int((df_pod["has_no_request"] | df_pod["has_no_limit"]).sum())
total_waste_ch = df_pod["cpu_waste_core_hours"].sum()
total_alloc_ch = df_pod["cpu_allocated_core_hours"].sum()
overall_eff = df_pod["cpu_usage_core_hours"].sum() / max(total_alloc_ch, 0.001) * 100
mem_waste_gb = df_pod["mem_waste_gb_hours"].sum()
top30p_limit = max(1, int(total_containers * 0.30))
kpis = [
("총 관측 컨테이너 수", f"{total_containers:,} 개", C["hdr_dark"], C["white"]),
("OOMKilled 발생 컨테이너", f"{oom_cnt:,} 개", C["red"], C["white"]),
("리소스 미설정 위반 컨테이너", f"{no_req_cnt:,} 개", "E26B0A", C["white"]),
("전사 CPU 낭비 총량", f"{total_waste_ch:,.1f} Core-H", C["hdr_mid"], C["white"]),
("전사 CPU 평균 활용률", f"{overall_eff:.1f} %", "375623", C["white"]),
("전사 Memory 낭비 총량", f"{mem_waste_gb:,.1f} GB-H", "6B4F9B", C["white"]),
("최적화 권고 대상 (Top 30%)", f"{top30p_limit:,} 개", C["accent"], C["white"]),
("분석 네임스페이스 수", f"{df_pod['namespace'].nunique()} 개", "1F4E79", C["white"]),
]
ws.row_dimensions[2].height = 8
for i, (label, value, bg, fg) in enumerate(kpis):
row = 3 + i
ws.row_dimensions[row].height = 30
# Label
lc = ws.cell(row=row, column=1, value=label)
lc.font = ft(bold=True, size=10, color=C["hdr_dark"])
lc.fill = fill(C["summary_bg"])
lc.alignment = left()
lc.border = thin_border()
ws.merge_cells(f"A{row}:C{row}")
# Value
vc = ws.cell(row=row, column=4, value=value)
vc.font = ft(bold=True, size=11, color=fg)
vc.fill = fill(bg)
vc.alignment = center()
vc.border = thin_border()
ws.merge_cells(f"D{row}:F{row}")
set_col_widths(ws, {"A":26,"B":14,"C":14,"D":18,"E":18,"F":18,"G":20,"H":20})
# 차트 삽입 (도넛 + 파레토)
row_img = 14
ws.cell(row=row_img-1, column=1, value="[ 거버넌스 현황 분포 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
ws.cell(row=row_img-1, column=5, value="[ 네임스페이스 파레토 분석 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
if (PLOT_DIR / "chart6_status_donut.png").exists():
img = XLImage(str(PLOT_DIR / "chart6_status_donut.png"))
img.width = 420; img.height = 300
ws.add_image(img, f"A{row_img}")
if (PLOT_DIR / "chart5_pareto_ns_waste.png").exists():
img2 = XLImage(str(PLOT_DIR / "chart5_pareto_ns_waste.png"))
img2.width = 560; img2.height = 300
ws.add_image(img2, f"E{row_img}")
# ─────────────────────────────────────────────
# Sheet 1: 파레토 분석 (NS)
# ─────────────────────────────────────────────
def build_sheet_pareto(wb, df_ns):
ws = wb.create_sheet("1. 파레토분석_NS")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:I1")
t = ws["A1"]
t.value = "Namespace별 CPU Waste 파레토 분석 (80/20 Rule)"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"])
t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["Namespace", "실행시간 합계(분)", "컨테이너 수",
"할당 Core-H", "낭비 Core-H", "낭비 비중(%)", "누적 비중(%)", "등급"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
df_disp = df_ns.copy()
df_disp["등급"] = df_disp["waste_cumsum_pct"].apply(
lambda x: "🔴 Critical (Top 20%)" if x <= 20 else
("🟡 High (Top 50%)" if x <= 50 else
("🟢 Medium" if x <= 80 else "⚪ Low")))
col_map = {
"namespace": "Namespace",
"minutes_running_sum": "실행시간 합계(분)",
"container_cnt": "컨테이너 수",
"total_allocated_core_hours": "할당 Core-H",
"total_waste_core_hours": "낭비 Core-H",
"waste_share_pct": "낭비 비중(%)",
"waste_cumsum_pct": "누적 비중(%)",
"등급": "등급",
}
df_out = df_disp[list(col_map.keys())].rename(columns=col_map)
nf = {2:"#,##0", 3:"#,##0", 4:"#,##0.0", 5:"#,##0.0", 6:"0.00%", 7:"0.00%"}
# % 컬럼은 0~1 아니라 이미 % 값이므로 그냥 숫자 포맷
nf = {4:"#,##0.0", 5:"#,##0.0", 6:"0.00", 7:"0.00"}
end_row = apply_data_rows(ws, df_out, start_row=3, num_formats=nf)
# 낭비 비중 데이터바 조건부서식
col_waste = 5
col_letter = get_column_letter(col_waste)
data_range = f"{col_letter}3:{col_letter}{end_row}"
ws.conditional_formatting.add(
data_range,
DataBarRule(start_type="min", end_type="max",
color="2E75B6", showValue=True)
)
set_col_widths(ws, {"A":22,"B":18,"C":14,"D":16,"E":16,"F":12,"G":12,"H":22})
freeze_and_filter(ws)
# 차트 이미지 삽입
if (PLOT_DIR / "chart5_pareto_ns_waste.png").exists():
ws.cell(row=end_row+2, column=1, value="[ 파레토 차트 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
img = XLImage(str(PLOT_DIR / "chart5_pareto_ns_waste.png"))
img.width = 780; img.height = 380
ws.add_image(img, f"A{end_row+3}")
# ─────────────────────────────────────────────
# Sheet 2: CPU Request/Usage 분석
# ─────────────────────────────────────────────
def build_sheet_cpu(wb, df_pod):
ws = wb.create_sheet("2. CPU Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "CPU Resource Efficiency Analysis — Request / Limit / P95 Usage"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"])
t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜","클러스터","네임스페이스","워크로드 타입","Pod","컨테이너",
"CPU Request(Core)","CPU Limit(Core)","CPU P95 사용량(Core)",
"활용률(사용/Request %)","낭비 Core-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
total = len(df_pod)
top30_n = max(1, int(total * 0.30))
df_out = df_pod.sort_values("cpu_waste_core_hours", ascending=False).head(top30_n).copy()
df_out["활용률(사용/Request %)"] = np.where(
df_out["cpu_request_max"] > 0,
(df_out["cpu_usage_p95"] / df_out["cpu_request_max"] * 100).round(1),
0
)
display_cols = ["date","cluster","namespace","workload_type","pod","container",
"cpu_request_max","cpu_limit_max","cpu_usage_p95",
"활용률(사용/Request %)","cpu_waste_core_hours","status"]
df_disp = df_out[display_cols].reset_index(drop=True)
status_col = headers.index("상태") + 1
nf = {7:"0.000", 8:"0.000", 9:"0.000", 10:"0.0", 11:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=status_col)
# 활용률 컬럼 color scale (빨강→노랑→초록)
eff_col = get_column_letter(10)
ws.conditional_formatting.add(
f"{eff_col}3:{eff_col}{end_row}",
ColorScaleRule(start_type="num", start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050")
)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":30,"F":18,
"G":16,"H":16,"I":18,"J":18,"K":14,"L":18})
freeze_and_filter(ws)
# 차트 삽입
if (PLOT_DIR / "chart1_cpu_req_vs_usage_by_workload.png").exists():
ws.cell(row=end_row+2, column=1, value="[ CPU Request vs P95 Usage by Workload ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
img = XLImage(str(PLOT_DIR / "chart1_cpu_req_vs_usage_by_workload.png"))
img.width = 860; img.height = 400
ws.add_image(img, f"A{end_row+3}")
# ─────────────────────────────────────────────
# Sheet 3: Memory Request/Usage 분석
# ─────────────────────────────────────────────
def build_sheet_memory(wb, df_pod):
ws = wb.create_sheet("3. Memory Request_Usage 분석")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "Memory Resource Efficiency Analysis — Request / Limit / P95 Usage"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"])
t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜","클러스터","네임스페이스","워크로드 타입","Pod","컨테이너",
"Mem Request(GB)","Mem Limit(GB)","Mem P95 사용량(GB)",
"활용률(사용/Request %)","낭비 GB-H","상태"]
apply_header_row(ws, 2, headers, bg=C["hdr_mid"])
top30_n = max(1, int(len(df_pod) * 0.30))
df_out = df_pod.sort_values("mem_waste_gb_hours", ascending=False).head(top30_n).copy()
df_out["활용률(사용/Request %)"] = np.where(
df_out["mem_request_max"] > 0,
(df_out["mem_usage_p95"] / df_out["mem_request_max"] * 100).round(1),
0
)
display_cols = ["date","cluster","namespace","workload_type","pod","container",
"mem_request_max","mem_limit_max","mem_usage_p95",
"활용률(사용/Request %)","mem_waste_gb_hours","status"]
df_disp = df_out[display_cols].reset_index(drop=True)
status_col = headers.index("상태") + 1
nf = {7:"0.000", 8:"0.000", 9:"0.000", 10:"0.0", 11:"#,##0.0"}
end_row = apply_data_rows(ws, df_disp, start_row=3, num_formats=nf, status_col_idx=status_col)
eff_col = get_column_letter(10)
ws.conditional_formatting.add(
f"{eff_col}3:{eff_col}{end_row}",
ColorScaleRule(start_type="num", start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050")
)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":30,"F":18,
"G":16,"H":16,"I":18,"J":18,"K":14,"L":18})
freeze_and_filter(ws)
if (PLOT_DIR / "chart2_mem_req_vs_usage_by_workload.png").exists():
ws.cell(row=end_row+2, column=1, value="[ Memory Request vs P95 Usage by Workload ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
img = XLImage(str(PLOT_DIR / "chart2_mem_req_vs_usage_by_workload.png"))
img.width = 860; img.height = 400
ws.add_image(img, f"A{end_row+3}")
# ─────────────────────────────────────────────
# Sheet 4: OOM 장애 및 자원 부족
# ─────────────────────────────────────────────
def build_sheet_oom(wb, df_pod):
ws = wb.create_sheet("4. 자원부족및OOM장애군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:K1")
t = ws["A1"]
t.value = "OOMKilled / CPU Request 부족 컨테이너 명세"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["red"])
t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜","클러스터","네임스페이스","워크로드 타입","Pod","컨테이너",
"상태","CPU Request","CPU P95 사용","Mem Limit(GB)","Mem P95(GB)"]
apply_header_row(ws, 2, headers, bg=C["red"])
df_out = df_pod[(df_pod["cpu_shortage_cores"] > 0) | (df_pod["is_oom_killed"])].sort_values(
["is_oom_killed","cpu_shortage_cores"], ascending=[False,False]
)[["date","cluster","namespace","workload_type","pod","container",
"status","cpu_request_max","cpu_usage_p95","mem_limit_max","mem_usage_p95"]].reset_index(drop=True)
nf = {8:"0.000", 9:"0.000", 10:"0.000", 11:"0.000"}
end_row = apply_data_rows(ws, df_out, start_row=3, num_formats=nf, status_col_idx=7)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":30,"F":18,
"G":18,"H":14,"I":14,"J":14,"K":14})
freeze_and_filter(ws)
# ─────────────────────────────────────────────
# Sheet 5: 리소스 미설정 위반군
# ─────────────────────────────────────────────
def build_sheet_violations(wb, df_pod):
ws = wb.create_sheet("5. 리소스미설정위반군")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:L1")
t = ws["A1"]
t.value = "Resource Request / Limit 미설정 위반 컨테이너 전수 목록"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill("E26B0A")
t.alignment = center()
ws.row_dimensions[1].height = 32
headers = ["날짜","클러스터","네임스페이스","워크로드 타입","Pod","컨테이너",
"Request 미설정","Limit 미설정",
"CPU Request","CPU Limit","Mem Request(GB)","Mem Limit(GB)"]
apply_header_row(ws, 2, headers, bg="E26B0A")
df_out = df_pod[df_pod["has_no_request"] | df_pod["has_no_limit"]].sort_values(
"minutes_running", ascending=False
)[["date","cluster","namespace","workload_type","pod","container",
"has_no_request","has_no_limit",
"cpu_request_max","cpu_limit_max","mem_request_max","mem_limit_max"]].reset_index(drop=True)
df_out["has_no_request"] = df_out["has_no_request"].map({True:"⚠️ 미설정", False:"✅ 설정됨"})
df_out["has_no_limit"] = df_out["has_no_limit"].map({True:"⚠️ 미설정", False:"✅ 설정됨"})
nf = {9:"0.000", 10:"0.000", 11:"0.000", 12:"0.000"}
end_row = apply_data_rows(ws, df_out, start_row=3, num_formats=nf)
set_col_widths(ws, {"A":12,"B":18,"C":20,"D":18,"E":30,"F":18,
"G":14,"H":14,"I":14,"J":14,"K":14,"L":14})
freeze_and_filter(ws)
# ─────────────────────────────────────────────
# Sheet 6: 일별 트렌드 + Heatmap 차트
# ─────────────────────────────────────────────
def build_sheet_trends(wb, df_pod):
ws = wb.create_sheet("6. 일별트렌드_차트")
ws.sheet_view.showGridLines = False
ws.merge_cells("A1:H1")
t = ws["A1"]
t.value = "Daily Resource Waste Trend & CPU Utilization Heatmap"
t.font = ft(bold=True, size=13, color=C["white"])
t.fill = fill(C["hdr_dark"])
t.alignment = center()
ws.row_dimensions[1].height = 32
# 일별 집계 소테이블
df_daily = df_pod.groupby("date").agg(
total_containers=("container","count"),
cpu_alloc_ch=("cpu_allocated_core_hours","sum"),
cpu_usage_ch=("cpu_usage_core_hours","sum"),
cpu_waste_ch=("cpu_waste_core_hours","sum"),
mem_alloc_gbh=("mem_allocated_gb_hours","sum"),
mem_waste_gbh=("mem_waste_gb_hours","sum"),
oom_cnt=("is_oom_killed","sum")
).reset_index()
df_daily["cpu_util_pct"] = (df_daily["cpu_usage_ch"] / df_daily["cpu_alloc_ch"].clip(lower=0.001) * 100).round(1)
headers2 = ["날짜","컨테이너 수","CPU 할당 Core-H","CPU 사용 Core-H",
"CPU 낭비 Core-H","Mem 할당 GB-H","Mem 낭비 GB-H","OOM 발생","CPU 활용률(%)"]
apply_header_row(ws, 3, headers2, bg=C["hdr_mid"])
ws.cell(row=2, column=1, value="[ 일별 집계 요약 ]").font = ft(bold=True, size=11, color=C["hdr_dark"])
nf = {3:"#,##0.0",4:"#,##0.0",5:"#,##0.0",6:"#,##0.0",7:"#,##0.0",8:"#,##0",9:"0.0"}
end_row = apply_data_rows(ws, df_daily, start_row=4, num_formats=nf)
# CPU 활용률 color scale
eff_col = get_column_letter(9)
ws.conditional_formatting.add(
f"{eff_col}4:{eff_col}{end_row}",
ColorScaleRule(start_type="num",start_value=0, start_color="FF0000",
mid_type="num", mid_value=50, mid_color="FFFF00",
end_type="num", end_value=100, end_color="00B050")
)
set_col_widths(ws, {"A":14,"B":14,"C":16,"D":16,"E":16,"F":16,"G":14,"H":12,"I":14})
chart_row = end_row + 3
for chart_key, label in [
("chart3_daily_waste_stack.png", "[ 일별 CPU 낭비 워크로드별 누적 추이 ]"),
("chart4_cpu_efficiency_heatmap.png", "[ CPU 활용률 Heatmap (Namespace x Date) ]"),
]:
ws.cell(row=chart_row, column=1, value=label).font = ft(bold=True, size=11, color=C["hdr_dark"])
if (PLOT_DIR / chart_key).exists():
img = XLImage(str(PLOT_DIR / chart_key))
img.width = 860; img.height = 380
ws.add_image(img, f"A{chart_row+1}")
chart_row += 22
# ─────────────────────────────────────────────
# Main
# ─────────────────────────────────────────────
def main():
df_pod = pd.read_parquet(MERGED_DIR / "enriched_fixed_7d.parquet")
df_ns = pd.read_parquet(MERGED_DIR / "pareto_fixed_ns.parquet")
wb = Workbook()
build_sheet_summary(wb, df_pod, df_ns)
build_sheet_pareto(wb, df_ns)
build_sheet_cpu(wb, df_pod)
build_sheet_memory(wb, df_pod)
build_sheet_oom(wb, df_pod)
build_sheet_violations(wb, df_pod)
build_sheet_trends(wb, df_pod)
out_path = OUT_DIR / "finops_resource_governance_report.xlsx"
wb.save(out_path)
print(f"✅ Excel 저장 완료: {out_path}")
if __name__ == "__main__":
main()
engineer