Python : Pandas 를 이용한 엑셀 작업 예제

CSV를 읽어 피봇 테이블을 만들고 값에 따라 Color Scale 을 적용하는 업무는 자주 있는 일입니다.

이 블로그에서는 Pandas를 사용하여 위와 같은 복잡한 요건을 처리하는 예제를 작성해 봅니다.

요건 :

CSV 파일에는 아래와 같은 컬럼이 있다고 가정합니다. 특정 페이지에 링크된 리소스 파일들(html, css, js 등)의 이름과 최적화 전후 파일크기와 감소량을 보여줍니다.

PageName, fileName, before, after, gz_before, gz_after, saving, gz_saving

한 페이지에는 여러 리소스들이 존재하고, 여러 페이지에 공통으로 존재하는 파일들도 많기 때문에 리소스 파일이름(fileName) 으로 그룹핑하여 특정 파일에 어떤 페이지들이 얼마만큼 최적화 되어 있는지 나타내고, 최적화 비율이 클수록 빨간색으로 낮을 수록 녹색으로 표시하려고 합니다.

결과 파일의 컬럼은 아래와 같습니다.

fileName, max_before, Page1, Page2, Page3, ... , Page N

max_befor : 기존 파일의 크기

page1 ~ pageN : 각 페이지별 파일 사이즈 감소량

작업 순서 :

아래와 같은 순서로 코드를 작성합니다.

CSV 파일 읽기: 데이터를 pandas로 읽어옵니다.
데이터 처리:
- fileName으로 그룹화하여 before의 최대값을 계산합니다.
- 각 pageName별로 saving 항목의 최대값을 피벗 테이블 형식으로 구성합니다.
Excel 파일 생성:
- 각 페이지 이름 컬럼에 대해 값 크기에 따라 색을 설정합니다. 값이 클수록 붉은색, 작을수록 녹색이 가까운 색상으로 채웁니다.
Excel 파일 저장: openpyxl로 결과를 저장합니다.

파이썬 코드 :

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

# CSV 파일 경로
csv_file_path = "your_file_path.csv"  # 적절한 파일 경로로 변경
output_excel_path = "output_excel_colored.xlsx"

# CSV 파일 읽기
df = pd.read_csv(csv_file_path)

# fileName으로 그룹화하여 before의 최대값 계산
before_max_df = df.groupby("fileName", as_index=False)["before"].max().rename(columns={"before": "max_before"})

# 각 pageName 별 saving 항목의 최대값 계산
saving_max_df = df.pivot_table(index="fileName", columns="pageName", values="saving", aggfunc="max").reset_index()

# 결과 합치기
final_df = pd.merge(before_max_df, saving_max_df, on="fileName")

# Excel 파일 생성
wb = Workbook()
ws = wb.active
ws.title = "Summary"

# DataFrame 데이터를 Excel로 기록
for row in dataframe_to_rows(final_df, index=False, header=True):
    ws.append(row)

# 페이지 컬럼만 선택 (2번째 이후 컬럼)
page_columns = list(final_df.columns[2:])
page_min_max = {col: (final_df[col].min(), final_df[col].max()) for col in page_columns}

# 색상 설정 함수
def get_color(value, min_val, max_val):
    """값의 범위에 따라 색상 계산"""
    if pd.isna(value):  # NaN은 색 적용 안 함
        return "FFFFFF"
    range_val = max_val - min_val
    normalized = (value - min_val) / range_val if range_val else 0.5
    red = int(255 * normalized)
    green = int(255 * (1 - normalized))
    return f"{red:02X}{green:02X}00"

# 색상 적용
for col_idx, col_name in enumerate(page_columns, start=3):  # 3번째 컬럼부터 시작
    min_val, max_val = page_min_max[col_name]
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=col_idx, max_col=col_idx):
        for cell in row:
            color = get_color(cell.value, min_val, max_val)
            cell.fill = PatternFill(start_color=color, end_color=color, fill_type="solid")

# Excel 파일 저장
wb.save(output_excel_path)
print(f"엑셀 파일 저장 완료: {output_excel_path}")

실행 방법:

CSV 파일 경로를 csv_file_path에 입력합니다.
코드를 실행하면 output_excel_colored.xlsx 파일이 생성됩니다.

함께 사는 세상... 그리고 나