如何用Pandas按column1分组统计column3首次出现字符串前的NaN数？

原始DataFrame #

column1 | column2    | column3 |
   1    | 2023-02-21 |   NaN   |
   1    | 2023-02-22 |   NaN   |
   1    | 2023-02-23 |    8    |
   1    | 2023-02-24 |   NaN   |
   1    | 2023-02-24 |   NaN   |
   1    | 2023-02-24 |   NaN   |
   1    | 2023-02-24 |   NaN   |
   1    | 2023-02-24 |   10    |
   2    | 2023-02-25 |   NaN   |
   2    | 2023-02-26 |    9    |

期望结果DataFrame #

column1 | column2    | column3 | result
   1    | 2023-02-21 |   NaN   |   3
   1    | 2023-02-22 |   NaN   |   3 
   1    | 2023-02-23 |    8    |   3
   1    | 2023-02-24 |   NaN   |   3
   1    | 2023-02-24 |   NaN   |   3
   1    | 2023-02-24 |   NaN   |   3
   1    | 2023-02-24 |   NaN   |   3 
   1    | 2023-02-24 |   10    |   3
   2    | 2023-02-23 |   NaN   |   2
   2    | 2023-02-24 |    9    |   2

需求说明 #

按column1分组，统计每组中column3首次出现非NaN值之前的NaN数量，将结果统一填充到该组所有行的result列。

实现方案 #

方法一：简洁高效版 #

通过标记首次非NaN值的位置区间，统计区间内的行数：

import pandas as pd
import numpy as np

# 构造原始数据（实际使用时可替换为你的数据源）
df = pd.DataFrame({
    'column1': [1,1,1,1,1,1,1,1,2,2],
    'column2': ['2023-02-21','2023-02-22','2023-02-23','2023-02-24','2023-02-24','2023-02-24','2023-02-24','2023-02-24','2023-02-25','2023-02-26'],
    'column3': [np.nan, np.nan, 8, np.nan, np.nan, np.nan, np.nan, 10, np.nan, 9]
})

# 标记每组中是否已出现过非NaN值：0表示首次非NaN之前，1及以上表示之后
df['temp_flag'] = df.groupby('column1')['column3'].transform(lambda x: ~x.isna()).cumsum()

# 统计每组中temp_flag为0的行数，即首次非NaN前的NaN数量
result_counts = df[df['temp_flag'] == 0].groupby('column1').size()

# 将统计结果映射到result列
df['result'] = df['column1'].map(result_counts)

# 清理临时列（可选）
df.drop('temp_flag', axis=1, inplace=True)

print(df)

方法二：直接定位首次非NaN索引 #

通过找到每组第一个非NaN值的索引，计算其与组起始索引的差值：

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'column1': [1,1,1,1,1,1,1,1,2,2],
    'column2': ['2023-02-21','2023-02-22','2023-02-23','2023-02-24','2023-02-24','2023-02-24','2023-02-24','2023-02-24','2023-02-25','2023-02-26'],
    'column3': [np.nan, np.nan, 8, np.nan, np.nan, np.nan, np.nan, 10, np.nan, 9]
})

# 获取每组column3第一个非NaN值的索引
first_non_nan_indices = df.groupby('column1')['column3'].apply(lambda x: x.first_valid_index())

# 计算每组首次非NaN前的NaN数量
count_map = {}
for group_id, idx in first_non_nan_indices.items():
    group_rows = df[df['column1'] == group_id]
    # 索引差值即为首次非NaN前的行数（也就是NaN数量）
    count_map[group_id] = idx - group_rows.index[0]

# 填充result列
df['result'] = df['column1'].map(count_map)

print(df)

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