Download model-server/01.data/merge_csv_to_sql.py from apple-retail-study/Apple-Retail-Sales-Strategy: direct link, hf CLI and curl.
- Browser
- Download file 6.86 kB
-
https://huggingface.co/spaces/apple-retail-study/Apple-Retail-Sales-Strategy/resolve/main/model-server/01.data/merge_csv_to_sql.py
- Command line
-
hf download hf://spaces/apple-retail-study/Apple-Retail-Sales-Strategy/model-server/01.data/merge_csv_to_sql.py
-
curl -L -o merge_csv_to_sql.py https://huggingface.co/spaces/apple-retail-study/Apple-Retail-Sales-Strategy/resolve/main/model-server/01.data/merge_csv_to_sql.py
6.86 kB
| """ | |
| data_02_inventory_final.csv ์ Apple_Retail_Sales_Dataset_Modified SQL ์คํค๋ง ๊ฒฐํฉ. | |
| - CSV ๋ก๋ ํ sale_id ๊ธฐ์ค ์ค๋ณต ์ ๊ฑฐ | |
| - store_stock_quantity = quantity * (0.95 ~ 1.30) ๋๋ค ์์ฑ | |
| - 10๊ฐ SQL ํ์ผ๋ก ๋ถํ ์ถ๋ ฅ (01: CREATE+INSERT, 02~10: INSERT) | |
| """ | |
| from pathlib import Path | |
| import re | |
| import numpy as np | |
| import pandas as pd | |
| SCRIPT_DIR = Path(__file__).resolve().parent | |
| MODEL_SERVER = SCRIPT_DIR.parent | |
| CSV_PATH = MODEL_SERVER / "00.old" / "data_02_inventory_final.csv" | |
| NUM_PARTS = 10 | |
| OUTPUT_NAME = "Apple_Retail_Sales_Dataset_Modified" | |
| # SQL ํ ์ด๋ธ ์ปฌ๋ผ ์์ (INSERT์ฉ) โ ์ฌ๊ณ ์ ์ฉ ์ปฌ๋ผ ํฌํจ | |
| SQL_COLUMNS = [ | |
| "sale_id", "sale_date", "store_id", "product_id", "quantity", | |
| "product_name", "category_id", "launch_date", "price", "category_name", | |
| "store_name", "city", "country", "total_sales", "store_stock_quantity", | |
| "inventory", "frozen_money", "safety_stock", "status", | |
| ] | |
| # CSV ์ปฌ๋ผ๋ช -> SQL ์ปฌ๋ผ๋ช ๋งคํ | |
| COLUMN_MAP = { | |
| "Product_Name": "product_name", | |
| "Launch_Date": "launch_date", | |
| "Store_Name": "store_name", | |
| "City": "city", | |
| "Country": "country", | |
| "Inventory": "inventory", | |
| "Frozen_Money": "frozen_money", | |
| "Safety_Stock": "safety_stock", | |
| "Status": "status", | |
| } | |
| def _sql_escape(s): | |
| """PostgreSQL: single quote -> double single quote.""" | |
| if s is None or (isinstance(s, float) and pd.isna(s)): | |
| return "" | |
| s = str(s).strip() | |
| return s.replace("'", "''") | |
| def _row_to_sql_value(row): | |
| """ํ ํ์ SQL VALUES ํ ํญ๋ชฉ ๋ฌธ์์ด๋ก.""" | |
| parts = [] | |
| for col in SQL_COLUMNS: | |
| v = row.get(col) | |
| if col in ("quantity", "store_stock_quantity", "inventory", "safety_stock"): | |
| parts.append(str(int(v)) if pd.notna(v) and str(v).strip() != "" else "0") | |
| elif col in ("price", "total_sales", "frozen_money"): | |
| if pd.isna(v) or str(v).strip() == "": | |
| parts.append("0") | |
| else: | |
| try: | |
| parts.append(str(round(float(v), 2))) | |
| except (TypeError, ValueError): | |
| parts.append("0") | |
| elif col in ("sale_date", "launch_date"): | |
| if pd.isna(v) or str(v).strip() == "": | |
| parts.append("NULL") | |
| else: | |
| s = str(v).strip()[:10] | |
| if re.match(r"\d{4}-\d{2}-\d{2}", s): | |
| parts.append(f"'{s}'") | |
| else: | |
| parts.append("NULL") | |
| elif col == "status": | |
| parts.append(f"'{_sql_escape(v)}'" if pd.notna(v) and str(v).strip() else "'Normal'") | |
| else: | |
| parts.append(f"'{_sql_escape(v)}'") | |
| return "(" + ", ".join(parts) + ")" | |
| def main(): | |
| if not CSV_PATH.exists(): | |
| print(f"CSV ์์: {CSV_PATH}") | |
| return | |
| print(f"CSV ๋ก๋: {CSV_PATH}") | |
| df = pd.read_csv(CSV_PATH, low_memory=False) | |
| # ์ปฌ๋ผ ์ ๊ทํ: ๋๋ฌธ์ ์ปฌ๋ผ๋ช -> ์๋ฌธ์ | |
| rename = {} | |
| for c in list(df.columns): | |
| if c in COLUMN_MAP: | |
| rename[c] = COLUMN_MAP[c] | |
| df = df.rename(columns=rename) | |
| # ํ์ํ ์ปฌ๋ผ (์ฌ๊ณ ์ ์ฉ ํฌํจ; store_stock_quantity๋ ์๋์์ ์์ฑ) | |
| required = [c for c in SQL_COLUMNS if c != "store_stock_quantity"] | |
| missing = [c for c in required if c not in df.columns] | |
| if missing: | |
| print(f"ํ์ ์ปฌ๋ผ ์์: {missing}") | |
| return | |
| df = df[required].copy() | |
| # sale_id ๊ธฐ์ค ์ค๋ณต ์ ๊ฑฐ (์ฒซ ํ ์ ์ง) | |
| before = len(df) | |
| df = df.drop_duplicates(subset=["sale_id"], keep="first") | |
| after = len(df) | |
| print(f"์ค๋ณต ์ ๊ฑฐ: {before} -> {after} ํ") | |
| # ์ซ์/๋ ์ง ์ปฌ๋ผ ์ ๊ทํ | |
| df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce").fillna(0).astype(int) | |
| df["total_sales"] = pd.to_numeric(df["total_sales"], errors="coerce").fillna(0) | |
| df["price"] = pd.to_numeric(df["price"], errors="coerce").fillna(0) | |
| if "inventory" in df.columns: | |
| df["inventory"] = pd.to_numeric(df["inventory"], errors="coerce").fillna(0).astype(int) | |
| if "frozen_money" in df.columns: | |
| df["frozen_money"] = pd.to_numeric(df["frozen_money"], errors="coerce").fillna(0) | |
| if "safety_stock" in df.columns: | |
| df["safety_stock"] = pd.to_numeric(df["safety_stock"], errors="coerce").fillna(0).astype(int) | |
| if "status" not in df.columns: | |
| df["status"] = "Normal" | |
| # store_stock_quantity: CSV์ ์์ผ๋ฉด quantity * (0.95 ~ 1.30) ๋๋ค ์์ฑ | |
| if "store_stock_quantity" not in df.columns: | |
| rng = np.random.default_rng(42) | |
| q = df["quantity"].values | |
| ratio = 0.95 + rng.random(len(df)) * 0.35 | |
| df["store_stock_quantity"] = np.clip(np.round(q * ratio), 1, None).astype(int) | |
| else: | |
| df["store_stock_quantity"] = pd.to_numeric(df["store_stock_quantity"], errors="coerce").fillna(0).astype(int) | |
| n = len(df) | |
| chunk_size = (n + NUM_PARTS - 1) // NUM_PARTS | |
| rows_per_part = [min(chunk_size, n - i * chunk_size) for i in range(NUM_PARTS)] | |
| rows_per_part = [x for x in rows_per_part if x > 0] | |
| create_table_sql = """-- PostgreSQL table for Apple retail sales data (from data_02_inventory_final.csv + Modified) | |
| DROP TABLE IF EXISTS sales_data CASCADE; | |
| CREATE TABLE sales_data ( | |
| sale_id VARCHAR(50), | |
| sale_date DATE, | |
| store_id VARCHAR(20), | |
| product_id VARCHAR(20), | |
| quantity INTEGER, | |
| product_name VARCHAR(255), | |
| category_id VARCHAR(20), | |
| launch_date DATE, | |
| price DECIMAL(12,2), | |
| category_name VARCHAR(100), | |
| store_name VARCHAR(255), | |
| city VARCHAR(100), | |
| country VARCHAR(100), | |
| total_sales DECIMAL(12,2), | |
| store_stock_quantity INTEGER, | |
| inventory INTEGER, | |
| frozen_money DECIMAL(12,2), | |
| safety_stock INTEGER, | |
| status VARCHAR(50) | |
| ); | |
| """ | |
| insert_header = "INSERT INTO sales_data (" + ", ".join(SQL_COLUMNS) + ") VALUES\n" | |
| start = 0 | |
| for part in range(1, NUM_PARTS + 1): | |
| take = rows_per_part[part - 1] if part <= len(rows_per_part) else 0 | |
| if take <= 0: | |
| break | |
| chunk = df.iloc[start : start + take] | |
| start += take | |
| path = SCRIPT_DIR / f"{OUTPUT_NAME}_{part:02d}.sql" | |
| if part == 1: | |
| content = create_table_sql | |
| content += f"\n-- Part 1/10: rows 1 to {len(chunk)} (total: {len(chunk)} rows)\n" | |
| else: | |
| content = f"-- Part {part}/10: rows {start - take + 1} to {start} (total: {len(chunk)} rows)\n" | |
| content += insert_header | |
| values = [] | |
| for _, row in chunk.iterrows(): | |
| values.append(_row_to_sql_value(row)) | |
| content += ",\n".join(values) | |
| content += "\n;\n" | |
| path.write_text(content, encoding="utf-8") | |
| print(f" ์์ฑ: {path.name} ({len(chunk)} rows)") | |
| print("์๋ฃ: 10๊ฐ SQL ํ์ผ ์์ฑ.") | |
| if __name__ == "__main__": | |
| main() | |