{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# niq_build_beer.py\n",
    "\n",
    "NIQ 맥주 스캐너(MALT, 가정용) → 월별 국산/수입 정돈 + 월별 2재화 AIDS (한치록 완전재현)\n",
    " 입력: 'NIQ) MALT Sales_JAN17-AUG25.xlsx' (3시트, 브랜드×월 Sales Value(MM)·Sales Volume(10LIT))\n",
    " 분류: MANUFACTURER에 'IMP' 포함 → 수입, 그 외 → 국산\n",
    " 산출: 공개데이터/닐슨/beer_niq_dom_imp_monthly.csv + 월별 AIDS 탄력성\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"NIQ 맥주 스캐너(MALT, 가정용) → 월별 국산/수입 정돈 + 월별 2재화 AIDS (한치록 완전재현)\n",
    " 입력: 'NIQ) MALT Sales_JAN17-AUG25.xlsx' (3시트, 브랜드×월 Sales Value(MM)·Sales Volume(10LIT))\n",
    " 분류: MANUFACTURER에 'IMP' 포함 → 수입, 그 외 → 국산\n",
    " 산출: 공개데이터/닐슨/beer_niq_dom_imp_monthly.csv + 월별 AIDS 탄력성\n",
    "\"\"\"\n",
    "import sys,io,json,re\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",
    "import os\n",
    "XL='C:/Temp/주류세/NIQ) MALT Sales_JAN17-AUG25.xlsx'\n",
    "OUT='C:/Temp/주류세/공개데이터/닐슨'; os.makedirs(OUT,exist_ok=True)\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'([A-Z]{3})(\\d{2})',str(lbl).strip())\n",
    "    if not m: return None\n",
    "    return (2000+int(m.group(2)))*100+MON[m.group(1)]\n",
    "\n",
    "wb=openpyxl.load_workbook(XL,read_only=True,data_only=True)\n",
    "recs=[]\n",
    "for ws in wb.worksheets:\n",
    "    rows=list(ws.iter_rows(values_only=True))\n",
    "    metric_row=rows[1]; head_row=rows[2]\n",
    "    colmap={}\n",
    "    for j,(mt,hd) in enumerate(zip(metric_row,head_row)):\n",
    "        y=ym(hd)\n",
    "        if y and mt:\n",
    "            met='value' if 'Value' in str(mt) else ('volume' if 'Volume' in str(mt) else None)\n",
    "            if met: colmap[j]=(met,y)\n",
    "    for r in rows[3:]:\n",
    "        man=r[2]; brand=r[3]\n",
    "        if not man: continue\n",
    "        imp='IMP' in str(man).upper()\n",
    "        for j,(met,y) in colmap.items():\n",
    "            v=r[j]\n",
    "            if v in (None,'NA','',' '): continue\n",
    "            try: v=float(v)\n",
    "            except: continue\n",
    "            recs.append((y,'수입' if imp else '국산',met,v))\n",
    "wb.close()\n",
    "df=pd.DataFrame(recs,columns=['ym','grp','met','v'])\n",
    "agg=df.groupby(['ym','grp','met']).v.sum().unstack('met').reset_index()\n",
    "# 단위: value=백만원(MM), volume=10리터 → 금액(원)=value*1e6, 리터=volume*10\n",
    "agg['sales']=agg['value']*1e6; agg['liters']=agg['volume']*10\n",
    "agg['price']=agg['sales']/agg['liters']   # 원/리터\n",
    "wide=agg.pivot(index='ym',columns='grp',values=['sales','liters','price'])\n",
    "wide.columns=[f'{a}_{b}' for a,b in wide.columns]; wide=wide.reset_index().dropna()\n",
    "wide['X']=wide['sales_국산']+wide['sales_수입']\n",
    "wide['w_imp']=wide['sales_수입']/wide['X']; wide['w_dom']=wide['sales_국산']/wide['X']\n",
    "wide.to_csv(OUT+'/beer_niq_dom_imp_monthly.csv',index=False,encoding='utf-8-sig')\n",
    "print('월별 관측:',len(wide),'기간',int(wide.ym.min()),'~',int(wide.ym.max()))\n",
    "print('수입 지출몫 w_imp: 처음 %.3f / 끝 %.3f / 평균 %.3f'%(wide.w_imp.iloc[0],wide.w_imp.iloc[-1],wide.w_imp.mean()))\n",
    "print('가격(원/L) 국산 평균 %.0f · 수입 평균 %.0f'%(wide['price_국산'].mean(),wide['price_수입'].mean()))\n",
    "\n",
    "# ---------- 월별 2재화 LA-AIDS ----------\n",
    "d=wide.copy()\n",
    "d['lnP']=d.w_imp*np.log(d['price_수입'])+d.w_dom*np.log(d['price_국산'])\n",
    "d['ln_XP']=np.log(d.X)-d.lnP\n",
    "d['ln_rel']=np.log(d['price_수입']/d['price_국산'])\n",
    "d['month']=d.ym%100; d['t']=np.arange(len(d))\n",
    "d['covid']=(d.ym//100).isin([2020,2021]).astype(int)\n",
    "def aids(df,ctrl):\n",
    "    f='w_imp ~ ln_rel + ln_XP'+ctrl\n",
    "    m=smf.ols(f,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=df.w_dom.mean()\n",
    "    return m,dict(gamma=round(g,3),tg=round(m.tvalues['ln_rel'],2),beta=round(b,3),\n",
    "      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",
    "      eta_imp=round(1+b/wi,2), eta_dom=round(1-b/wd,2), wi=round(wi,3), n=int(m.nobs))\n",
    "res={}\n",
    "for lab,ctrl in [('기본(계절)',' + C(month)'),('계절+추세+코로나',' + C(month) + t + covid')]:\n",
    "    m,r=aids(d,ctrl); res[lab]=r\n",
    "    print(f'\\n===== NIQ 월별 맥주 AIDS [{lab}] n={r[\"n\"]} =====')\n",
    "    print(f'  γ={r[\"gamma\"]}(t={r[\"tg\"]}) β={r[\"beta\"]}')\n",
    "    print(f'  자체가격탄력성: 수입 {r[\"e_imp\"]:+} | 국산 {r[\"e_dom\"]:+}')\n",
    "    print(f'  교차: 국산가↑→수입 {r[\"cross_imp_on_dom\"]:+} · 수입가↑→국산 {r[\"cross_dom_on_imp\"]:+}')\n",
    "    print(f'  지출탄력성: 수입 {r[\"eta_imp\"]:+} | 국산 {r[\"eta_dom\"]:+} (수입몫 {r[\"wi\"]})')\n",
    "json.dump(res,open(OUT+'/beer_niq_aids.json','w',encoding='utf-8'),ensure_ascii=False,indent=2)\n",
    "print('\\n[대조] 원논문 수입프리미엄 -4.8~-6.0/국산코어 -1.8~-1.9 · 국세접합 수입 -3.6/국산 -1.1')\n",
    "print('저장: 닐슨/beer_niq_dom_imp_monthly.csv, beer_niq_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
}