blueeye-3184
Initial Commit: Clean history without large files
7a6b92c
Raw History Blame Contribute Delete
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()