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