엑셀 별 통합 지표

데이터 통합

Python.xlsx와 Java.xlx의 확장자도 다르고 지표도 다른 상황에서

각 파일을 하나의 시트로 통합해 관리하기 위해 작성했다.

원하는 지표만 추출해 표준화하고 자동으로 집계하여 result.xlsx에 저장한다.

*이 전 글에서는 여러 엑셀 파일들을 하나의 엑셀 파일에 관리하기 위해 여러 시트들을 활용해 관리했었다.

---

예시 데이터

Python.xlsx

Unit	pay	input	special	in	put	people	account
vscode	54651054	46586541	878646	8746846	784613	87646	21546
pycharm	468764	8768454	998754	78944654	684454	65431	54512

Java.xlx

program	cost	revenue	etc	inflow	click
Eclipse	465	8461	761	4654126	46871
Intellij	87765	6554865	9866	8971980	6871654

excel 통합 구조

• platform별로 제각각인 파일들을 표준 지표(광고비·매출·노출·클릭·구매) 기준으로 통합·집계
• result.xlsx(시트 구성)
• monthly : 월별 집계 및 전월 대비 증감
• summary : 전체 광고 지표 요약
• platform : 플랫폼별 상세 지표

---

파일 구성

• data_loader.py → 기준 데이터에서 요약값 로드
• ad_aggregator.py → 플랫폼별 파일 수집·전처리·지표 계산
• report_writer.py → monthly/summary/platform 작성 및 서식 적용

---

전체 코드를 README에 모두 넣으면 길어져 가독성이 떨어지므로

핵심 포인트 중심으로 작성했고, 필요 없는 부분(log, try-except 등)은 생략했다.

전체 코드는 GitHub에 정리했다.

---

이 전 데이터 불러오기 — data_loader.py

현재 기준으로 1년 전 폴더(ex. “24년 09월”)에서 total이 포함된 .xlsx 파일을 읽고

첫 행(0) · 마지막 열(-1)값을 반환한다.

def data_loader():
    today = datetime.now()
    one_month_ago = today - relativedelta(months=12)
    monthly_folder = one_month_ago.strftime("%y년 %m월")
    path = rf"C:\Users\monthly\data\{monthly_folder}"
    file_list = glob.glob(os.path.join(path, "*total*.xlsx"))[0]
    df = pd.read_excel(file_list, sheet_name="summary")
    return df.iloc[0, -1]

• glob : 특정 패턴의 파일을 검색하여 리스트로 반환

계산하기 — ad_aggregator.py

날짜 설정

current = datetime(2025, 10, 1)
last_day = calendar.monthrange(current.year, current.month)[1]

• last_day : 월의 마지막 날짜(28~31)
• 고정비 계산 시 고정단가 × last_day 로 사용

플랫폼별 설정 (platform_settings)

platform마다 열 이름, 패턴명, header 위치, total 유무가 다르기 때문에

이를 dict 구조로 통합 관리했했다.

platform_settings = {
    "Java_Web": {
        "pattern": "excel_market",
        "header": 1,
        "columns": {
            "type": "classification",
            "name": "web",
            "cost": "지출비",
            "revenue": "매출액",
            "impression": "노출수",
            "click": "클릭수",
            "purchase": "전환수",
        },
        "has_total": False,
    },
    "Python_market": {
        "pattern": "excel_place",
        "header": 1,
        "columns": {
            "type": "classification",
            "name": "fixed",
            "cost": "지출비",
            "revenue": "매출액",
            "impression": "뷰",
            "click": "방문자",
            "purchase": "구매건",
        },
        "has_total": True,
    },
}

주요 항목

항목	설명
pattern	파일명 검색 키워드
header	header 위치
columns	컬럼명 매핑
has_total	파일에 total 행 존재 여부
type/name	파일 내부의 세부 구분 기준

💡 Java.xlx에서

• type = program
• name = Eclipse / Intellij

.xls → .xlsx 변환 (pywin32)

abs_path = os.path.abspath(file_path)
new_path = abs_path + "x"
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(abs_path)
wb.SaveAs(new_path, FileFormat=51)
wb.Close()
excel.Quit()

• 51 = .xlsx 형식

최근 2일 이내 파일 필터링

def get_files_by_pattern(path, pattern):
    today = datetime.date.today()
    yesterday = datetime.timedelta(days=2)
    date = today - yesterday
    matched_files = glob.glob(os.path.join(path, f"*{pattern}*"))
    today_files = [
        f for f in matched_files
        if datetime.date.fromtimestamp(os.path.getmtime(f)) >= date
    ]
    today_files.sort(key=os.path.getmtime, reverse=True)
    return today_files

파일 읽기

def read_excel_file(filepath, header):
    if filepath.lower().endswith(".csv"):
        try:
            return pd.read_csv(filepath, header=header, encoding="utf-8")
        except:
            return pd.read_csv(filepath, sep='\t', encoding='utf-16', header=header)
    elif filepath.lower().endswith(".xls"):
        try:
            return pd.read_excel(filepath, header=header, engine="xlrd")
        except:
            converted = convert_xls_to_xlsx(filepath)
            return pd.read_excel(converted, header=header, engine='openpyxl')
    else:
        return pd.read_excel(filepath, header=header, engine="openpyxl")

지표 계산

• 콤마 제거 후 숫자 변환
• purchase 컬럼이 없는 경우 예외 처리
• 고정비(FIXED_COST * last_day) 반영

FIXED_COST = 1550

if columns["cost"] in df.columns:
    if columns.get("name") and "fixed" in columns["name"]:
        site_cost = FIXED_COST * last_day
    else:
        site_cost = pd.to_numeric(
            df[columns["cost"]].astype(str).str.replace(",", ""), errors="coerce"
        ).sum()

결과 반환

def calculate_values():
    data_loader = loader.data_loader()
    total = {
        "광고비": cost,
        "매출": revenue,
        "노출": impression,
        "클릭": click,
        "구매": purchase,
        "info_data": data_loader
    }
    return {"총합": total, "플랫폼": results}

Excel 저장 — report_writer.py

각 플랫폼의 계산 결과를 엑셀에 저장한다.

지표 생성 (ROAS, CPC 등)

def add_metrics(df):
    df["ROAS"] = (df["매출"] / df["광고비"]).replace([float("inf"), -float("inf")], 0)
    ...
    return df

무한대 값을 0으로 처리

서식 적용

PERCENT_COLS = ["ROAS", "CTR", "CVR"]
COMMA_COLS = ["광고비", "매출", "클릭", "구매", "CPC"]

if col_name in percent_cols:
    cell.number_format = "0.0%"
elif col_name in comma_cols:
    cell.number_format = "#,##0"

플랫폼 그룹화

플랫폼명이 파생된 경우 상위 플랫폼명으로 통합

ex. ABC_DEF, ABC_XYZ → ABC

df_platforms["index"] = df_platforms["index"].str.replace(
    r"ABC_DEF | ABC_XYZ", "ABC", regex=True
)
df_platforms = df_platforms.rename(columns={'index': 'platform'})

monthly 시트

전월 데이터의 마지막 행을 찾아 증감 계산 후 신규 행 추가

def find_last_data_row(ws, start_row=3, col=1):
    row = start_row
    while ws.cell(row=row, column=col).value:
        row += 1
    return row - 1

ws_mon.append([curr_ad, curr_rev, curr_roas, ad_diff, rev_diff, roas_diff])

해당 월 데이터가 있으면 skip, 없으면 작성

summary 및 platform 시트 저장

with pd.ExcelWriter("result.xlsx", mode="a", engine="openpyxl") as writer:
    df_total.to_excel(writer, sheet_name="summary", index=False)
    df_platforms.to_excel(writer, sheet_name="platform", index=False)

# 테이블 및 서식 적용 후 저장
apply_table_and_format(...)
wb.save("result.xlsx")