如何选择groupby中空值最少的组？

Question

示例：

row_number |id |firstname | middlename | lastname |
0          | 1 | John     | NULL       | Doe      |
1          | 1 | John     | Jacob      | Doe      |
2          | 2 | Alison   | Marie      | Smith    |
3          | 2 | NULL     | Marie      | Smith    |
4          | 2 | Alison   | Marie      | Smith    |

我正在尝试找出如何按id分组，然后为每个groupby抓取空值最少的行，删除任何包含空值最少的额外行就可以了（例如，删除第4行，因为它将第2行与id=2的空值最少的行相关联）

这个例子的答案是第1行和第2行

最好是ansisql，但是我可以翻译其他语言（比如带pandas的python），如果你能想到一种方法的话

编辑： 增加了一行的情况下打破领带。你知道吗

Answer 1

哦，您需要的是null值最少的行。我建议：

select t.*
from (select t.*,
             dense_rank() over (order by (case when firstname is null then 1 else 0 end) + 
                                         (case when middlename is null then 1 else 0 end) + 
                                         (case when lastname is null then 1 else 0 end)
                               ) as seqnum

      from t
     ) t
where seqnum = 1;

这是ANSI标准SQL。你知道吗

Answer 2

如果你想这样做，你可以这样做：

df[df.assign(NC = df.isnull().sum(1)).groupby('id')['NC'].transform(lambda x: x == x.min())]

输出：

   row_number  id firstname middlename lastname
1           1   1      John      Jacob      Doe
2           2   2    Alison      Marie    Smith

对于断绳器：

添加行：

df.loc[4,['row_number','id','firstname','middlename','lastname']] = ['4',2,'Mary','Maxine','Maxwell']

然后使用groupby、transform和idxmin：

df[df.index == df.assign(NC = df.isnull().sum(1)).groupby('id')['NC'].transform('idxmin')]

输出：

  row_number id firstname middlename lastname
1          1  1      John      Jacob      Doe
2          2  2    Alison      Marie    Smith