{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# niq_soju.py\n",
    "\n",
    "NIQ 소주 스캐너(가정용) → 월별 총소주 가격·물량 → H2 탄력성 + H1 2024 기준판매비율 전가\n",
    " 컬럼(행9): Markets,Periods,TOTAL SOJU,BRAND,SUBBRAND,SIZE,PERCENTAGE(도수),PACKAGE,VARIANTA,ITEM,\n",
    "            MANUFACTURER,Sales Value,Sales(10.8LIT),Sales Units,Sales(10L),Val/L\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"NIQ 소주 스캐너(가정용) → 월별 총소주 가격·물량 → H2 탄력성 + H1 2024 기준판매비율 전가\n",
    " 컬럼(행9): Markets,Periods,TOTAL SOJU,BRAND,SUBBRAND,SIZE,PERCENTAGE(도수),PACKAGE,VARIANTA,ITEM,\n",
    "            MANUFACTURER,Sales Value,Sales(10.8LIT),Sales Units,Sales(10L),Val/L\n",
    "\"\"\"\n",
    "import sys,io,re,json\n",
    "sys.stdout=io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')\n",
    "import numpy as np, pandas as pd, openpyxl, statsmodels.formula.api as smf\n",
    "OUT='C:/Temp/주류세/공개데이터/닐슨'\n",
    "MON={m:i+1 for i,m in enumerate(['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'])}\n",
    "def ym(lbl):\n",
    "    m=re.match(r'\\s*([A-Za-z]{3})\\s*(\\d{2})',str(lbl))\n",
    "    return (2000+int(m.group(2)))*100+MON[m.group(1).upper()] if m else None\n",
    "wb=openpyxl.load_workbook('20260908 Soju raw.xlsx',read_only=True,data_only=True)\n",
    "ws=wb.worksheets[0]; it=ws.iter_rows(min_row=9,values_only=True); hdr=list(next(it))\n",
    "idx={h:i for i,h in enumerate(hdr)}\n",
    "def col(*names):\n",
    "    for n in names:\n",
    "        for h,i in idx.items():\n",
    "            if h and n.lower() in str(h).lower(): return i\n",
    "    return None\n",
    "cP,cVal=idx.get('Periods'),col('Sales Value'); c10L=col('Sales (10L)','Sales(10L)'); cVL=col('Val/L'); cPct=col('PERCENTAGE'); cBr=col('BRAND')\n",
    "rows=[]\n",
    "for r in it:\n",
    "    y=ym(r[cP]) if r[cP] else None\n",
    "    if not y: continue\n",
    "    try: val=float(r[cVal]); lit=float(r[c10L])*10\n",
    "    except: continue\n",
    "    if lit<=0: continue\n",
    "    rows.append((y,val,lit,r[cPct],r[cBr]))\n",
    "wb.close()\n",
    "d=pd.DataFrame(rows,columns=['ym','val','lit','pct','brand'])\n",
    "print('소주 스캐너: 행',len(d),'기간',int(d.ym.min()),'~',int(d.ym.max()),'월수',d.ym.nunique())\n",
    "# 월별 총소주\n",
    "m=d.groupby('ym').agg(val=('val','sum'),lit=('lit','sum')).reset_index()\n",
    "m['price']=m.val/m.lit; m['q']=m.lit\n",
    "m['ln_q']=np.log(m.q); m['ln_p']=np.log(m.price); m['month']=m.ym%100\n",
    "m['t']=np.arange(len(m)); m['post2024']=(m.ym>=202401).astype(int)\n",
    "print('가격(원/L) 범위 %.0f~%.0f, 평균 %.0f'%(m.price.min(),m.price.max(),m.price.mean()))\n",
    "# H2 탄력성\n",
    "r1=smf.ols('ln_q ~ ln_p + C(month)',data=m).fit(cov_type='HAC',cov_kwds={'maxlags':6})\n",
    "r2=smf.ols('ln_q ~ ln_p + C(month) + t',data=m).fit(cov_type='HAC',cov_kwds={'maxlags':6})\n",
    "print('\\n[H2 소주 스캐너 탄력성]')\n",
    "print('  계절통제 ε=%.2f (t=%.2f)'%(r1.params['ln_p'],r1.tvalues['ln_p']))\n",
    "print('  계절+추세 ε=%.2f (t=%.2f)'%(r2.params['ln_p'],r2.tvalues['ln_p']))\n",
    "# H1 2024 기준판매비율: 가격 수준 전후\n",
    "pre=m[(m.ym>=202301)&(m.ym<=202312)].price.mean(); post=m[(m.ym>=202401)&(m.ym<=202412)].price.mean()\n",
    "print('\\n[H1 2024 기준판매비율 전가(소주 소매가, 원/L)]')\n",
    "print('  2023평균 %.0f → 2024평균 %.0f (%.1f%%)'%(pre,post,(post/pre-1)*100))\n",
    "# 도수 밴드별 가격(참고)\n",
    "m.to_csv(OUT+'/soju_niq_monthly.csv',index=False,encoding='utf-8-sig')\n",
    "json.dump(dict(n_month=int(m.ym.nunique()),period=f'{int(m.ym.min())}-{int(m.ym.max())}',\n",
    "   elast_season=round(float(r1.params['ln_p']),3),elast_trend=round(float(r2.params['ln_p']),3),\n",
    "   price2023=round(pre,1),price2024=round(post,1),chg_pct=round((post/pre-1)*100,2)),\n",
    "   open(OUT+'/soju_niq.json','w',encoding='utf-8'),ensure_ascii=False,indent=2)\n",
    "print('\\n저장: soju_niq_monthly.csv, soju_niq.json')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}