{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# niq_segment_aids.py\n",
    "\n",
    "NIQ 맥주 세그먼트·용기별 AIDS — 수입프리미엄 정밀재현 (Rolling 3yr, 가정용)\n",
    " 세그먼트: 국산코어(국산&CORE/VALUE) · 수입프리미엄(수입&P/SP) · 기타(국산프리미엄+수입CORE+)\n",
    " (A) 3재화 AIDS(전체) (B) 캔 한정 2재화(용기 통제: 수입프리미엄CAN vs 국산코어CAN)\n",
    " 스캐너 Val/L는 프로모션 변동 포함 → 상대가격 식별력 제고.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"NIQ 맥주 세그먼트·용기별 AIDS — 수입프리미엄 정밀재현 (Rolling 3yr, 가정용)\n",
    " 세그먼트: 국산코어(국산&CORE/VALUE) · 수입프리미엄(수입&P/SP) · 기타(국산프리미엄+수입CORE+)\n",
    " (A) 3재화 AIDS(전체) (B) 캔 한정 2재화(용기 통제: 수입프리미엄CAN vs 국산코어CAN)\n",
    " 스캐너 Val/L는 프로모션 변동 포함 → 상대가격 식별력 제고.\n",
    "\"\"\"\n",
    "import sys,io,re,json,os\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(l):\n",
    "    m=re.match(r'\\s*([A-Za-z]{3})\\s*(\\d{2})',str(l)); return (2000+int(m.group(2)))*100+MON[m.group(1).upper()] if m else None\n",
    "DOM_EXACT={'HITE','KLOUD','KELLY','MAX','FITZ','HANMAC','JEJU','MALPYO','KOMPYO','KABREW','PLATINUM','STOUT','OB LAGER','THE PREMIER OB','DRY FINISH D','GOMPYO'}\n",
    "DOM_PREFIX=('CASS','CAFRI','TERRA','FILITE','FILGOOD','HITE','KLOUD','DRY FINISH','OB ','HAND','SEVEN','THE BOOTH','GORILLA','GOLDEN LAGER','QUEX')\n",
    "def origin(b):\n",
    "    b=str(b).upper().strip(); return '국산' if (b in DOM_EXACT or b.startswith(DOM_PREFIX)) else '수입'\n",
    "def seg(o,t):\n",
    "    if o=='수입' and t in ('P/SP',): return '수입프리미엄'\n",
    "    if o=='국산' and t in ('CORE','VALUE'): return '국산코어'\n",
    "    return '기타'\n",
    "wb=openpyxl.load_workbook('20260902_Aug26 Rolling 3-Year.xlsx',read_only=True,data_only=True)\n",
    "ws=wb.worksheets[0]; it=ws.iter_rows(min_row=9,values_only=True); H={h:i for i,h in enumerate(next(it))}\n",
    "rows=[]\n",
    "for r in it:\n",
    "    if r[H['SEGMENT']]!='BEER': continue\n",
    "    y=ym(r[H['Periods']])\n",
    "    if not y: continue\n",
    "    try: val=float(r[H['Sales Value']]); lit=float(r[H['Sales (10LIT)']])*10\n",
    "    except: continue\n",
    "    if lit<=0: continue\n",
    "    o=origin(r[H['BRAND']]); rows.append((y,seg(o,r[H['PRICETIER']]),str(r[H['PACKAGE']]),val,lit))\n",
    "wb.close()\n",
    "d=pd.DataFrame(rows,columns=['ym','seg','pack','val','lit'])\n",
    "print('기간',int(d.ym.min()),'~',int(d.ym.max()),'월수',d.ym.nunique())\n",
    "\n",
    "def build(df,segs):\n",
    "    a=df[df.seg.isin(segs)].groupby(['ym','seg']).agg(val=('val','sum'),lit=('lit','sum')).reset_index()\n",
    "    a['p']=a.val/a.lit\n",
    "    P=a.pivot(index='ym',columns='seg',values='p'); V=a.pivot(index='ym',columns='seg',values='val')\n",
    "    w=V.div(V.sum(1),axis=0); X=V.sum(1)\n",
    "    return P.dropna(),w.dropna(),X\n",
    "\n",
    "def aids3(P,w,X,focus,num,third):\n",
    "    df=pd.DataFrame(index=P.index)\n",
    "    df['w_f']=w[focus]; df['w_n']=w[num]; df['w_t']=w[third]\n",
    "    df['lnP']=sum(w[c]*np.log(P[c]) for c in P.columns)\n",
    "    df['ln_XP']=np.log(X)-df.lnP\n",
    "    df['r_f']=np.log(P[focus]/P[num]); df['r_t']=np.log(P[third]/P[num])\n",
    "    df['month']=df.index%100\n",
    "    m=smf.ols('w_f ~ r_f + r_t + ln_XP + C(month)',data=df).fit(cov_type='HAC',cov_kwds={'maxlags':6})\n",
    "    g_ff=m.params['r_f']; b=m.params['ln_XP']; wf=df.w_f.mean()\n",
    "    e_ff=-1+(g_ff-b*wf)/wf; eta=1+b/wf\n",
    "    return dict(own=round(e_ff,2),tg=round(m.tvalues['r_f'],2),gamma=round(g_ff,3),beta=round(b,3),eta=round(eta,2),w=round(wf,3),n=int(m.nobs))\n",
    "\n",
    "def aids2(P,w,X,imp,dom):\n",
    "    df=pd.DataFrame(index=P.index); df['w_imp']=w[imp]\n",
    "    df['lnP']=w[imp]*np.log(P[imp])+w[dom]*np.log(P[dom]); df['ln_XP']=np.log(X)-df.lnP\n",
    "    df['ln_rel']=np.log(P[imp]/P[dom]); df['month']=df.index%100\n",
    "    m=smf.ols('w_imp ~ ln_rel + ln_XP + C(month)',data=df).fit(cov_type='HAC',cov_kwds={'maxlags':6})\n",
    "    g=m.params['ln_rel']; b=m.params['ln_XP']; wi=df.w_imp.mean(); wd=1-wi\n",
    "    return dict(e_imp=round(-1+(g-b*wi)/wi,2),e_dom=round(-1+(g+b*wd)/wd,2),\n",
    "      cross_imp_on_dom=round((-g+b*wi)/wd,2),cross_dom_on_imp=round((-g-b*wd)/wi,2),\n",
    "      tg=round(m.tvalues['ln_rel'],2),gamma=round(g,3),wi=round(wi,3),n=int(m.nobs))\n",
    "\n",
    "print('\\n===== (A) 3재화 AIDS: 수입프리미엄 자체탄력성 =====')\n",
    "P,w,X=build(d,['수입프리미엄','국산코어','기타'])\n",
    "rA=aids3(P,w,X,'수입프리미엄','국산코어','기타')\n",
    "print('  수입프리미엄: 자체 %+.2f (γ t=%.2f) 지출 %+.2f (몫 %.3f) n=%d'%(rA['own'],rA['tg'],rA['eta'],rA['w'],rA['n']))\n",
    "rC=aids3(P,w,X,'국산코어','수입프리미엄','기타')\n",
    "print('  국산코어:    자체 %+.2f (γ t=%.2f) 지출 %+.2f (몫 %.3f)'%(rC['own'],rC['tg'],rC['eta'],rC['w']))\n",
    "\n",
    "print('\\n===== (B) 캔 한정 2재화 AIDS (용기 통제): 수입프리미엄 vs 국산코어 =====')\n",
    "dc=d[d.pack=='CAN']\n",
    "Pc,wc,Xc=build(dc,['수입프리미엄','국산코어'])\n",
    "rB=aids2(Pc,wc,Xc,'수입프리미엄','국산코어')\n",
    "print('  자체가격: 수입프리미엄 %+.2f | 국산코어 %+.2f (γ t=%.2f, 수입몫 %.3f, n=%d)'%(rB['e_imp'],rB['e_dom'],rB['tg'],rB['wi'],rB['n']))\n",
    "print('  교차: 국산코어가↑→수입프리미엄 %+.2f · 수입프리미엄가↑→국산코어 %+.2f'%(rB['cross_imp_on_dom'],rB['cross_dom_on_imp']))\n",
    "\n",
    "json.dump({'three_good':{'수입프리미엄':rA,'국산코어':rC},'can_only_two_good':rB},\n",
    "          open(OUT+'/beer_niq_segment_aids.json','w',encoding='utf-8'),ensure_ascii=False,indent=2)\n",
    "print('\\n[원논문] 수입프리미엄 -4.8~-6.0 / 국산코어 -1.8~-1.9')\n",
    "print('저장: beer_niq_segment_aids.json')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}