如何用BigQuery或Python替换DataFrame中无效行的Value为最近有效行值

一、BigQuery实现 #

思路 #

1. 标记有效行（Occurrences ≥ 10）的Value，无效行的Value设为NULL；
2. 用窗口函数LAST_VALUE结合IGNORE NULLS，为每行获取后续最近的有效行Value；
3. 同时用窗口函数获取前面最近的有效行Value，用于处理末尾无后续有效行的场景；
4. 最终优先取后续有效Value，无后续则取前面的有效Value。

代码 #

WITH labeled_data AS (
  -- 标记有效行并添加行号（基于已有排序）
  SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY `Column A`, `Column B`) AS row_num,
    CASE WHEN Occurrences >= 10 THEN Value ELSE NULL END AS valid_value
  FROM
    `你的项目.你的数据集.你的表名`
),
next_valid AS (
  -- 获取每行后续最近的有效Value
  SELECT
    *,
    LAST_VALUE(valid_value IGNORE NULLS) OVER(
      ORDER BY row_num 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS next_valid_value
  FROM labeled_data
),
prev_valid AS (
  -- 获取每行前面最近的有效Value（处理末尾无后续的情况）
  SELECT
    *,
    LAST_VALUE(valid_value IGNORE NULLS) OVER(
      ORDER BY row_num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS prev_valid_value
  FROM labeled_data
)
-- 合并结果，优先用后续有效Value，无则用前面的
SELECT
  `Column A`,
  `Column B`,
  Occurrences,
  COALESCE(next_valid.next_valid_value, prev_valid.prev_valid_value) AS Value
FROM labeled_data
JOIN next_valid USING(row_num)
JOIN prev_valid USING(row_num)
ORDER BY row_num;

二、Python（Pandas）实现 #

思路 #

1. 将无效行（Occurrences < 10）的Value替换为缺失值（pd.NA）；
2. 先用bfill()向后填充，让无效行取最近的后续有效行Value；
3. 再用ffill()向前填充，处理末尾无后续有效行的场景。

代码 #

import pandas as pd

# 构造原始DataFrame
raw_data = {
    'Column A': ['Cell 1', 'Cell 1', 'Cell 1', 'Cell 1', 'Cell 1', 'Cell 2'],
    'Column B': ['Cell 2', 'Cell 3', 'Cell 4', 'Cell 5', 'Cell 6', 'Cell 1'],
    'Occurrences': [1, 2, 10, 1, 12, 1],
    'Value': [0, 0, 5, 1, 4, 7]
}
df = pd.DataFrame(raw_data)

# 替换无效行的Value为缺失值
df['Value'] = df['Value'].where(df['Occurrences'] >= 10, pd.NA)

# 先向后填充，再向前填充处理剩余缺失
df['Value'] = df['Value'].bfill().ffill()

# 输出结果
print(df)

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