含动态日期列的数据集Unpivot逆透视方案(Spark SQL/Pandas)

针对列名为动态日期格式、无法硬编码列名的逆透视需求，以下方案兼容Palantir Foundry平台，分别提供Spark（PySpark封装动态生成SQL）、Pandas两种实现，全程不需要提前重命名列，直接保留原始日期列名作为维度值，避免后续格式还原问题。

方案1：Spark 实现（Foundry平台原生适配） #

核心思路是先通过正则自动识别所有日期格式的列，排除固定维度列后动态拼接逆透视逻辑，不需要手动维护待处理列名列表，每月新增日期列会被自动识别。

from pyspark.sql import SparkSession
import re

# 替换为你实际的数据集读取逻辑
source_df = spark.read.format("parquet").load("path/to/your/dataset")
source_df.createOrReplaceTempView("source_table")

# 配置不需要逆透视的固定维度列，根据实际业务字段修改，比如ID、指标分类、主体名称这类固定字段
fixed_dim_cols = ["object_id", "metric_type", "department"]
# 配置日期列名匹配正则，可根据实际列名格式调整，当前兼容yyyy-MM、yyyy/MM、yyyyMM、yyyy-MM-dd等常见格式
date_col_pattern = re.compile(r"^\d{4}[-/]?\d{1,2}([-/]\d{1,2})?$")
# 自动筛选所有待逆透视的日期列
date_cols = [col for col in source_df.columns if col not in fixed_dim_cols and date_col_pattern.match(col)]

# 动态拼接逆透视SQL
unpivot_query = f"""
SELECT
    {', '.join(fixed_dim_cols)},
    TO_DATE(period_col, 'yyyy-MM-dd') AS stat_date, -- 日期格式根据实际列名格式调整，直接转日期类型
    metric_value
FROM source_table
UNPIVOT (
    metric_value FOR period_col IN ({', '.join([f'`{col}`' for col in date_cols])})
)
"""

result_df = spark.sql(unpivot_query)

如果需要纯Spark SQL实现，可先通过SHOW COLUMNS IN 你的表名获取全量表字段，过滤出日期列后替换上述SQL中IN子句的列列表即可，逻辑完全一致。

方案2：Pandas 实现 #

逻辑和Spark方案对齐，通过melt函数实现逆透视，自动筛选日期列：

import pandas as pd
import re

# 替换为实际数据读取逻辑
df = pd.read_parquet("path/to/your/local_or_cloud_dataset")

# 固定维度列配置，和Spark方案保持一致即可
fixed_dim_cols = ["object_id", "metric_type", "department"]
# 日期列匹配正则，同Spark方案
date_col_pattern = re.compile(r"^\d{4}[-/]?\d{1,2}([-/]\d{1,2})?$")
date_cols = [col for col in df.columns if col not in fixed_dim_cols and date_col_pattern.match(col)]

# 执行逆透视
result_df = df.melt(
    id_vars=fixed_dim_cols,
    value_vars=date_cols,
    var_name="stat_date",
    value_name="metric_value"
)

# 可选：将日期字段转为标准日期类型
result_df["stat_date"] = pd.to_datetime(result_df["stat_date"], errors="coerce")

注意事项 #

• 两种方案均不需要提前重命名原始日期列，原始列名会直接作为逆透视后的日期维度值，不存在格式还原成本
• 日期列匹配正则可根据实际命名规则调整，比如日期列是2024年1月这类中文格式，修改正则匹配规则即可
• 适配数百行到百万级以上数据量，Spark方案可直接在Foundry的Transform节点运行，无额外依赖
• 每月新增的日期列只要符合预设的命名规则，会被自动纳入逆透视范围，不需要迭代修改代码

内容的提问来源于stack exchange，提问作者Shazib Munshi