从长格式数据中筛选compound字段NA数≤2的用户全量数据

我有一份约百万行的长格式数据框，每个username对应7行数据，compound字段的NA数量范围为0-6，数据结构示例如下：

username,Phase,compound,Time,agegroup
000000ceans,6,0.1531,0.85,YA
000000ceans,1,,0,
000000ceans,2,,0.17,
000000ceans,3,,0.34,
000000ceans,4,,0.51,
000000ceans,5,,0.68,
000000ceans,7,,1.02
00000codename,5,0.4588,0.68,YA
00000codename,1,,0,
00000codename,2,,0.17,
00000codename,3,,0.34,
00000codename,4,,0.51,
00000codename,6,,0.85,
00000codename,7,,1.02
0000lailai,6,-0.2023,0.85,YA
0000lailai,1,,0,
0000lailai,2,,0.17,
0000lailai,3,,0.34,
0000lailai,4,,0.51,
0000lailai,5,,0.68,
0000lailai,7,,1.02

需要筛选出所有compound字段NA数量≤2的用户，保留这类用户对应的全部7行数据，输出结果中agegroup、Time字段按对应Phase的原有值保留，预期输出格式示例如下：

username   phase      compound      agegroup   Time
a           1            0.1          YA       assigned according to phase
a           2            0.2          YA       assigned according to phase
a           3            0.3          YA       assigned according to phase
a           4            0.4          YA       assigned according to phase
a           5            0.5          YA       assigned according to phase
a           6            NA           YA       assigned according to phase
a           7            NA           YA       assigned according to phase
b
b
b

核心逻辑：按username分组统计每个用户compound列的NA数量，筛选出符合条件的用户后保留其全部行，其余字段自动保留原有数值无需额外处理。

R实现 #

常规dplyr写法 #

library(dplyr)
result <- df %>%
  group_by(username) %>%
  filter(sum(is.na(compound)) <= 2) %>%
  ungroup()

百万行数据推荐data.table写法（性能更高） #

library(data.table)
setDT(df) # 转换为data.table格式
result <- df[, .SD[sum(is.na(compound)) <= 2], by = username]

可选操作：统一填充用户的agegroup字段 #

如果每个用户仅某一行agegroup有值，需要给该用户所有行补全agegroup，可额外执行：

result <- result %>%
  group_by(username) %>%
  tidyr::fill(agegroup, .direction = "downup") %>%
  ungroup()

Python实现 #

高性能transform写法（适合百万行数据） #

import pandas as pd
# 统计每个用户的compound NA数量
na_count = df.groupby('username')['compound'].transform(lambda x: x.isna().sum())
# 筛选符合条件的行
result = df[na_count <= 2]

简洁filter写法 #

result = df.groupby('username').filter(lambda x: x['compound'].isna().sum() <= 2)

可选操作：统一填充用户的agegroup字段 #

result['agegroup'] = result.groupby('username')['agegroup'].transform(lambda x: x.ffill().bfill())

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