ever-flow/visualization_modules

1import pandas as pd2import numpy as np3import streamlit as st4from pathlib import Path5 6from config import PERIODS7from data_utils import convert_to_usd, create_multiple_classification_data8 9# 2. Data Load & Pre‑processing10# ─────────────────────────────────────────────────────────────────────────────11 12@st.cache_data(show_spinner=True)13def load_raw_data() -> pd.DataFrame:14    """Load Excel source & basic cleaning"""15    file_path = Path(__file__).with_name("heatmap_data_with_SE_v2.xlsx")16    df = pd.read_excel(file_path, sheet_name='Sheet1')17    # Keep rows where at least one EMSEC column is non‑null18    emsec_cols = [f'EMSEC{i}' for i in range(1, 6)]19    df = df[df[emsec_cols].notna().any(axis=1)].copy()20 21    # Fallback Company column22    if 'Company' not in df.columns:23        df['Company'] = df['ticker']24 25    # Map market → country26    market_country_map = {27        'KOSPI': '한국', 'KOSDAQ': '한국', 'KOSDAQ GLOBAL': '한국',28        'NASDAQ': '미국', 'NYSE': '미국',29        'Prime (Domestic Stocks)': '일본', 'Standard (Domestic Stocks)': '일본',30        'Prime (Foreign Stocks)': '일본'31    }32    df['Country'] = df['market'].map(market_country_map).fillna('Unclassified')33 34    # Normalize market label35    df['Market'] = df['market'].replace({'KOSDAQ GLOBAL': 'KOSDAQ'})36    return df37 38 39def calculate_financial_metrics_with_currency_conversion(df: pd.DataFrame) -> pd.DataFrame:40    """Replicates the standalone v3.6 financial‑metric pipeline."""41    df = df.copy()42 43    def safe_divide(num, den):44        return num / den.replace(0, np.nan)45 46    # — absolute USD columns ---------------------------------------------------47    abs_cols = ['Market Cap (2024-12-31)', 'Enterprise Value (FQ0)']48    periods = PERIODS49    for p in periods:50        abs_cols.extend([51            f'Revenue ({p})', f'EBIT ({p})', f'Net Income ({p})',52            f'Total Assets ({p})', f'Equity ({p})', f'Total Liabilities ({p})',53            f'Net Debt ({p})', f'Depreciation ({p})', f'Dividends ({p})',54            f'Net Income After Minority ({p})'55        ])56    for col in abs_cols:57        if col in df.columns:58            df[f'{col}_USD'] = df.apply(lambda r: convert_to_usd(r[col], r['Country']), axis=1)59 60    # — derived metrics --------------------------------------------------------61    for p in periods:62        mc = 'Market Cap (2024-12-31)_USD'63        ebit_usd = f'EBIT ({p})_USD'64        dep_usd = f'Depreciation ({p})_USD'65        nd_usd = f'Net Debt ({p})_USD'66        ev_usd = 'Enterprise Value (FQ0)_USD'67 68        if ebit_usd in df.columns and dep_usd in df.columns:69            df[f'EBITDA ({p})_USD'] = df[ebit_usd] + df[dep_usd]70 71        if mc in df.columns and nd_usd in df.columns:72            df[ev_usd] = df.get(ev_usd, np.nan)73            mask = df[ev_usd].isna()74            df.loc[mask, ev_usd] = df.loc[mask, mc] + df.loc[mask, nd_usd].fillna(0)75 76        ni_usd = f'Net Income ({p})_USD'77        eq_usd = f'Equity ({p})_USD'78        ebitda_usd = f'EBITDA ({p})_USD'79        rev_usd = f'Revenue ({p})_USD'80 81        if mc in df.columns and ni_usd in df.columns:82            df[f'PER ({p})'] = safe_divide(df[mc], df[ni_usd])83        if mc in df.columns and eq_usd in df.columns:84            df[f'PBR ({p})'] = safe_divide(df[mc], df[eq_usd])85        if ev_usd in df.columns and ebitda_usd in df.columns:86            df[f'EV_EBITDA ({p})'] = safe_divide(df[ev_usd], df[ebitda_usd])87        if mc in df.columns and rev_usd in df.columns:88            df[f'시가총액/매출액 ({p})'] = safe_divide(df[mc], df[rev_usd])89        if mc in df.columns and ebit_usd in df.columns:90            df[f'시가총액/영업이익 ({p})'] = safe_divide(df[mc], df[ebit_usd])91 92        # — ratios (local currency OK) ----------------------------------------93        ni = f'Net Income ({p})'94        ni_after = f'Net Income After Minority ({p})'95        ni = ni_after if ni_after in df.columns else ni96        assets = f'Total Assets ({p})'97        liab = f'Total Liabilities ({p})'98 99        if ni in df.columns and eq_usd.replace('_USD', '') in df.columns:100            df[f'ROE ({p})'] = safe_divide(df[ni], df[eq_usd.replace('_USD', '')])101        if ebit_usd.replace('_USD', '') in df.columns and rev_usd.replace('_USD', '') in df.columns:102            df[f'영업이익률 ({p})'] = safe_divide(df[ebit_usd.replace('_USD', '')], df[rev_usd.replace('_USD', '')])103        if ebit_usd in df.columns and rev_usd in df.columns and dep_usd.replace('_USD', '') in df.columns:104            df[f'EBITDA/Sales ({p})'] = safe_divide(df[ebit_usd.replace('_USD', '')] + df[dep_usd.replace('_USD', '')], df[rev_usd.replace('_USD', '')])105        if ni in df.columns and assets.replace('_USD', '') in df.columns:106            df[f'총자산이익률 ({p})'] = safe_divide(df[ni], df[assets.replace('_USD', '')])107        if rev_usd.replace('_USD', '') in df.columns and assets.replace('_USD', '') in df.columns:108            df[f'자산회전율 ({p})'] = safe_divide(df[rev_usd.replace('_USD', '')], df[assets.replace('_USD', '')])109        if eq_usd.replace('_USD', '') in df.columns and assets.replace('_USD', '') in df.columns:110            df[f'자기자본비율 ({p})'] = safe_divide(df[eq_usd.replace('_USD', '')], df[assets.replace('_USD', '')])111        if liab.replace('_USD', '') in df.columns and eq_usd.replace('_USD', '') in df.columns:112            df[f'부채비율 ({p})'] = safe_divide(df[liab.replace('_USD', '')], df[eq_usd.replace('_USD', '')])113 114    return df115 116 117def prepare_streamlit_data(df: pd.DataFrame) -> pd.DataFrame:118    """Convert wide → long for multi‑year access in Streamlit."""119    rows = []120    for yr in PERIODS:121        tmp = df.copy()122        tmp['Year'] = yr123        mapping = {124            f'PER ({yr})': 'PER',125            f'PBR ({yr})': 'PBR',126            f'EV_EBITDA ({yr})': 'EV_EBITDA',127            f'ROE ({yr})': 'ROE',128            f'영업이익률 ({yr})': '영업이익률',129            f'EBITDA/Sales ({yr})': 'EBITDA/Sales',130            f'총자산이익률 ({yr})': '총자산이익률',131            f'자산회전율 ({yr})': '자산회전율',132            f'자기자본비율 ({yr})': '자기자본비율',133            f'부채비율 ({yr})': '부채비율',134            f'시가총액/매출액 ({yr})': '시가총액/매출액',135            f'시가총액/영업이익 ({yr})': '시가총액/영업이익',136            f'Net Income ({yr})_USD': 'Net_Income',137            f'EBITDA ({yr})_USD': 'EBITDA',138            f'Revenue ({yr})_USD': 'Sales',139            f'Total Assets ({yr})_USD': 'Assets',140            f'Equity ({yr})_USD': 'Book'141        }142        for old, new in mapping.items():143            if old in tmp.columns:144                tmp[new] = tmp[old]145        rows.append(tmp)146    return pd.concat(rows, ignore_index=True)147 148 149@st.cache_data(show_spinner=True)150def load_processed_data() -> pd.DataFrame:151    raw = load_raw_data()152    metrics = calculate_financial_metrics_with_currency_conversion(raw)153 154    # Identify companies with any missing financials ------------------------155    non_financial_keywords = ['EMSEC', 'EMTEC', 'ticker', 'market', 'Country', 'Market', 'name', 'Company']156    fin_cols = [c for c in metrics.columns if not any(k in c for k in non_financial_keywords)]157    company_missing = metrics.groupby('ticker')[fin_cols].apply(lambda x: x.isnull().values.any())158    metrics['has_missing_financials'] = metrics['ticker'].map(company_missing)159 160    expanded = create_multiple_classification_data(metrics)161    return prepare_streamlit_data(expanded)162 163 164