File size: 6,257 Bytes
43493ab 4432760 43493ab 4432760 cec5f40 4432760 cec5f40 4432760 43493ab 4432760 cec5f40 4432760 cec5f40 4432760 43493ab 4432760 43493ab 4432760 43493ab 4432760 43493ab 4432760 43493ab | 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | import gradio as gr
import pandas as pd
import re
import io
import openpyxl
from openpyxl import Workbook
import tempfile
def remove_special_characters(text: str):
return re.sub(r"[^\w\sㄱ-힣]", "", text)
def extract_keywords(text_list, top_n=3):
combined_text = " ".join(text_list)
combined_text = remove_special_characters(combined_text)
words = combined_text.split()
freq = {}
for w in words:
freq[w] = freq.get(w, 0) + 1
sorted_words = sorted(freq.items(), key=lambda x: x[1], reverse=True)
keywords = [w[0] for w in sorted_words[:top_n]]
return ", ".join(keywords)
def process_excel(file):
# -----------------------------
# (기존) 엑셀 읽기 & 기본 처리
# -----------------------------
df = pd.read_excel(file.name, header=None)
df.columns = ["고객ID", "리뷰날짜", "리뷰내용", "리뷰점수"] + list(df.columns[4:])
df["리뷰내용"] = df["리뷰내용"].astype(str).apply(remove_special_characters)
df_use = df[["고객ID", "리뷰날짜", "리뷰내용", "리뷰점수"]]
all_ids = df_use["고객ID"].dropna().unique()
combined_id = ", ".join(map(str, all_ids))
all_dates = df_use["리뷰날짜"].dropna().unique()
combined_dates = ", ".join(map(str, sorted(all_dates)))
keywords = extract_keywords(df_use["리뷰내용"].tolist())
df_use["리뷰점수"] = pd.to_numeric(df_use["리뷰점수"], errors='coerce')
score_mean = df_use["리뷰점수"].mean()
# -----------------------------
# (기존) 새 Workbook에 "기존데이터" 시트
# -----------------------------
wb = Workbook()
ws = wb.active
ws.title = "기존데이터"
ws["A1"] = "고객ID"
ws["B1"] = "리뷰날짜"
ws["C1"] = "리뷰내용"
ws["D1"] = "리뷰점수"
for i, row in enumerate(df_use.values, start=2):
ws.cell(row=i, column=1, value=row[0])
ws.cell(row=i, column=2, value=row[1])
ws.cell(row=i, column=3, value=row[2])
ws.cell(row=i, column=4, value=row[3])
ws["A8"] = combined_id
ws["B8"] = combined_dates
ws["C8"] = keywords
ws["D8"] = score_mean
# ----------------------------------------
# (추가) A1~K1, A2~K21까지 추출
# ----------------------------------------
df_raw = pd.read_excel(file.name, header=None)
# A1~K1 (row=0, col=0~10)
row1 = df_raw.iloc[0, 0:11].fillna('').tolist()
# A2~K21 (row=1~20, col=0~10)
rows2to21 = df_raw.iloc[1:21, 0:11].fillna('')
# H열(=col index=7) 기준 내림차순 정렬
sorted_rows = rows2to21.sort_values(by=7, ascending=False)
# ----------------------------------------------------
# (추가) "추가데이터" 시트
# ----------------------------------------------------
ws_add = wb.create_sheet("추가데이터")
# A1~K1
for col_idx, val in enumerate(row1, start=1):
ws_add.cell(row=1, column=col_idx, value=val)
# A2~K21 (원본 순서)
for row_idx, row_data in enumerate(rows2to21.values, start=2):
for col_idx, val in enumerate(row_data, start=1):
ws_add.cell(row=row_idx, column=col_idx, value=val)
# ----------------------------------------------------
# (추가) "정렬데이터" 시트 (H열 기준 내림차순)
# ----------------------------------------------------
ws_sorted = wb.create_sheet("정렬데이터")
# A1~K1
for col_idx, val in enumerate(row1, start=1):
ws_sorted.cell(row=1, column=col_idx, value=val)
# 정렬된 A2~K21
for row_offset, row_data in enumerate(sorted_rows.values, start=2):
for col_idx, val in enumerate(row_data, start=1):
ws_sorted.cell(row=row_offset, column=col_idx, value=val)
# ------------------------------------------------------------
# (핵심 수정 포인트)
# 최종 "다운로드" 되는 엑셀 파일에는 "정렬데이터"만 포함시키기
# ------------------------------------------------------------
# 지금까지 만든 wb에는 "기존데이터", "추가데이터", "정렬데이터"가 들어있지만
# 최종 다운로드 파일에는 "정렬데이터" 시트만 들어있도록 새 wb_final을 생성
wb_final = Workbook()
ws_final = wb_final.active
ws_final.title = "정렬데이터"
# 헤더(A1~K1)
for col_idx, val in enumerate(row1, start=1):
ws_final.cell(row=1, column=col_idx, value=val)
# 정렬된 A2~K21
for row_offset, row_data in enumerate(sorted_rows.values, start=2):
for col_idx, val in enumerate(row_data, start=1):
ws_final.cell(row=row_offset, column=col_idx, value=val)
# ----------------------------------------------------
# (기존) 미리보기 DataFrame
# ----------------------------------------------------
# 여기서는 정렬된 결과만 미리보기 예시
df_preview_added = pd.DataFrame(sorted_rows.values, columns=[f"Col_{c}" for c in range(1,12)])
# ----------------------------------------------------
# (수정) wb_final을 최종 파일로 저장
# ----------------------------------------------------
output = io.BytesIO()
wb_final.save(output) # 수정 지점: wb -> wb_final
output.seek(0)
with tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp:
tmp.write(output.getvalue())
tmp_path = tmp.name
# Gradio로 반환
return df_preview_added, tmp_path
def create_app():
with gr.Blocks() as demo:
gr.Markdown("## 엑셀 파일 업로드 데모")
file_input = gr.File(label="엑셀 파일 업로드", file_types=[".xlsx"])
generate_button = gr.Button("데이터 추출 및 결과 생성")
output_df = gr.DataFrame(label="처리된 결과 미리보기 (H열 기준 내림차순)")
download_file = gr.File(label="결과 엑셀 다운로드")
def on_click(file):
df_preview, tmp_path = process_excel(file)
return df_preview, tmp_path
generate_button.click(
fn=on_click,
inputs=file_input,
outputs=[output_df, download_file]
)
return demo
if __name__ == "__main__":
app = create_app()
app.launch()
|