PowerBI矩阵可视化：行转置（按月+1规则调整）

问题场景还原 #

你的数据集结构大致如下（以样例数据为例）：

目标是生成以InitialDate为行、RollingDate为列、Churn为单元格值的矩阵，缺失的组合可补0或空值。

Python Pandas 实现方案 #

1. 构造样例数据

import pandas as pd

# 模拟你的数据集
data = [
    ("2022-07", "2022-07", 0.1),
    ("2022-06", "2022-06", 0.2),
    ("2022-06", "2022-07", 0.15),
    ("2022-05", "2022-05", 0.3),
    ("2022-05", "2022-06", 0.25),
    ("2022-05", "2022-07", 0.2),
]

df = pd.DataFrame(data, columns=["InitialDate", "RollingDate", "Churn"])

2. 生成目标矩阵

使用pivot_table函数，指定行、列、值，并填充缺失值：

# 生成矩阵，缺失值填充为0（可根据需求改为NaN）
churn_matrix = df.pivot_table(
    index="InitialDate",
    columns="RollingDate",
    values="Churn",
    fill_value=0
)

# 按日期排序行和列（可选，确保顺序符合预期）
churn_matrix = churn_matrix.sort_index(ascending=False)
churn_matrix = churn_matrix.reindex(sorted(churn_matrix.columns), axis=1)

3. 输出结果

RollingDate  2022-05  2022-06  2022-07
InitialDate
2022-07          0.0     0.00      0.1
2022-06          0.0     0.20      0.15
2022-05          0.3     0.25      0.2

SQL 实现方案（以PostgreSQL为例） #

1. 创建并插入样例数据

CREATE TABLE churn_data (
    InitialDate DATE,
    RollingDate DATE,
    Churn NUMERIC
);

INSERT INTO churn_data VALUES
('2022-07-01', '2022-07-01', 0.1),
('2022-06-01', '2022-06-01', 0.2),
('2022-06-01', '2022-07-01', 0.15),
('2022-05-01', '2022-05-01', 0.3),
('2022-05-01', '2022-06-01', 0.25),
('2022-05-01', '2022-07-01', 0.2);

2. 使用crosstab生成矩阵

SELECT * FROM crosstab(
    -- 基础查询：按初始日期、滚动日期排序
    'SELECT InitialDate, RollingDate, Churn FROM churn_data ORDER BY 1,2',
    -- 定义列的取值：所有唯一的滚动日期
    'SELECT DISTINCT RollingDate FROM churn_data ORDER BY 1'
) AS ct (
    InitialDate DATE,
    "2022-05-01" NUMERIC,
    "2022-06-01" NUMERIC,
    "2022-07-01" NUMERIC
);

3. 输出结果

initialdate | 2022-05-01 | 2022-06-01 | 2022-07-01
-------------|------------|------------|------------
 2022-05-01  |        0.3 |       0.25 |        0.2
 2022-06-01  |            |        0.2 |       0.15
 2022-07-01  |            |            |        0.1

关键说明 #

直接用RollingDate作为列不符合预期的核心原因是：部分InitialDate没有对应更早的RollingDate记录，导致矩阵中出现缺失的单元格。通过pivot_table（Pandas）或crosstab（SQL）可以自动补全所有行和列的组合，再通过fill_value（Pandas）或默认空值（SQL）处理缺失项，最终得到符合要求的矩阵。

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