【最新OFDI】2003-2024 中国对外直接投资流量/存量全量数据库：分190+国与19大行业面板数据包

import pandas as pd
import numpy as np
from typing import Dict

# 国家中文名称到 ISO3 标准代码映射表
CUSTOM_COUNTRY_MAP: Dict[str, str] = {
    "中国香港": "HKG", "中国澳门": "MAC", "中国台湾": "TWN",
    "开曼群岛": "CYM", "英属维尔京群岛": "VGB", "埃斯瓦蒂尼": "SWZ",
    "俄罗斯": "RUS", "韩国": "KOR", "美国": "USA", "新加坡": "SGP"
}

def transform_ofdi_excel(file_path: str, sheet_name: str, metric_type: str, dimension: str) -> pd.DataFrame:
    """
    自动将 OFDI 宽表转为标准长表，并补齐 ISO 国别代码与美元换算
    """
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    entity_col = df.columns[0]
    year_cols = [c for c in df.columns if str(c).isdigit() or (isinstance(c, int) and 2000 <= c <= 2030)]

    # 宽表逆透视为长表
    melted = df.melt(
        id_vars=[entity_col],
        value_vars=year_cols,
        var_name="year",
        value_name="amount_usd_10k"
    )

    melted.rename(columns={entity_col: "entity_name"}, inplace=True)
    melted["year"] = melted["year"].astype(int)
    melted["metric_type"] = metric_type
    melted["dimension_type"] = dimension

    # 缺失值与数值清洗
    melted["is_official_missing"] = melted["amount_usd_10k"].isna() | (melted["amount_usd_10k"] == "--")
    melted["amount_usd_10k"] = pd.to_numeric(
        melted["amount_usd_10k"].replace(["--", "-", ""], np.nan),
        errors="coerce"
    )
    # 换算标准美元（USD）
    melted["amount_usd"] = melted["amount_usd_10k"] * 10000.0

    if dimension == "COUNTRY":
        melted["iso3_code"] = melted["entity_name"].apply(lambda n: CUSTOM_COUNTRY_MAP.get(str(n).strip(), "OTHER"))

    return melted

if __name__ == "__main__":
    df = transform_ofdi_excel("china_ofdi_2003_2024.xlsx", "流量_分国家地区", "FLOW", "COUNTRY")
    print(f"✅ OFDI 国别流量清洗成功，生成长表记录数: {len(df)}")

-- OFDI 多维时序事实表
CREATE TABLE fact_china_ofdi (
    id BIGSERIAL PRIMARY KEY,
    year INT NOT NULL,
    dimension_type VARCHAR(16) NOT NULL,  -- 'COUNTRY' 或 'INDUSTRY'
    entity_code VARCHAR(16) NOT NULL,    -- ISO3 编码或行业门类代码
    entity_name_zh VARCHAR(128) NOT NULL,

    flow_usd_10k NUMERIC(15, 4),         -- 投资流量（万美元）
    stock_usd_10k NUMERIC(15, 4),        -- 投资存量（万美元）
    flow_usd NUMERIC(18, 2),             -- 投资流量（美元）
    stock_usd NUMERIC(18, 2),            -- 投资存量（美元）

    is_flow_missing BOOLEAN DEFAULT FALSE,
    is_stock_missing BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT uq_ofdi_record UNIQUE (year, dimension_type, entity_code)
);

CREATE INDEX idx_ofdi_year_dim ON fact_china_ofdi (year, dimension_type);
CREATE INDEX idx_ofdi_entity_year ON fact_china_ofdi (entity_code, year DESC);