使用PowerQuery按规则向下填充非空值完成数据清洗

问题描述 #

我正在寻找当前数据集的高效清洗方法，确信存在便捷的实现方案但暂未梳理出正确的实现逻辑。
我已尝试使用Fill Down（向下填充）功能，但目前无法实现排除非所属分组的数值、仅将分组标识填充到对应日期行的需求，无法得到预期结果。

以下为现有单列表数据及期望实现的两列输出效果：

My Data in one column   What i want in two columns

APA  -->   22           null              null
Je 06/01/2022           APA  -->   22     Je 06/01/2022
Ve 07/01/2022           APA  -->   22     Ve 07/01/2022
Lu 07/02/2022           APA  -->   22     Lu 07/02/2022
Ma 08/02/2022           APA  -->   22     Ma 08/02/2022
null                    null              null
AR  -->   6             null              null
Ma 04/01/2022           AR  -->   6       Ma 04/01/2022
Ve 21/01/2022           AR  -->   6       Ve 21/01/2022
Sa 22/01/2022           AR  -->   6       Sa 22/01/2022
Me 23/02/2022           AR  -->   6       Me 23/02/2022
Lu 21/03/2022           AR  -->   6       Lu 21/03/2022
Ma 22/03/2022           AR  -->   6       Ma 22/03/2022
null                    null              null
AS  -->   545           null              null
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Sa 01/01/2022           AS  -->   545     Sa 01/01/2022
Di 02/01/2022           AS  -->   545     Di 02/01/2022

通用实现逻辑 #

不需要复杂功能，按照行类型判断+条件填充的逻辑即可实现，适配Excel、Power Query、Python、SQL等所有常用数据处理工具：

• 先逐行给原始数据打标签，分为三类：
  • 分组标识行：内容包含-->符号，是每个分组的开头
  • 日期行：内容为星期缩写+DD/MM/YYYY格式日期，是分组下的有效数据行
  • 空分隔行：值为null，是两个分组之间的分隔
• 初始化临时变量存储当前生效的分组值，初始值为null
• 逐行遍历生成两列结果：
  • 遇到分组标识行：更新临时变量为当前分组值，当前输出行两列均填null
  • 遇到空分隔行：将临时变量重置为null，当前输出行两列均填null
  • 遇到日期行：第一列填当前临时变量存储的分组值，第二列填当前行的日期内容

Pandas 快速实现代码 #

import pandas as pd
import numpy as np

# 读取原始单列数据，列名设为raw_data
df = pd.read_excel("你的数据文件路径.xlsx", names=["raw_data"])

# 标记行类型
df["is_group"] = df["raw_data"].str.contains("-->", na=False)
df["is_blank"] = df["raw_data"].isna()

# 生成分组列：遇到分组行赋值，向下填充，遇到空行重置为空
df["group"] = np.where(df["is_group"], df["raw_data"], np.nan).ffill()
df.loc[df["is_blank"], "group"] = np.nan

# 生成日期列：仅非分组、非空行保留原始日期值，其余为空
df["date"] = np.where((~df["is_group"]) & (~df["is_blank"]), df["raw_data"], np.nan)

# 提取最终结果
final_result = df[["group", "date"]]

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