将 groupby 中第一行的 NaN 值替换为包含特定值的下一行的值 - Python

Question

我有一个如下所示的 DataFrame，

email              month      level
[email protected]    jan        EE2 
[email protected]    jan        nan
[email protected]    mar        MG1 
[email protected]   jan        nan
[email protected]    jan        nan
[email protected]    jun        EE3
[email protected]   jan        nan
[email protected]   apr        PT 
[email protected]   jul        MG1
[email protected]   aug        MG1
[email protected]   sep        MG2 
[email protected]    sep        MG3

我计划执行 groupby 来选择每个组的第一行和最后一行。

但在此之前，我想将其中每个员工的第一行替换为“nan”，仅当下一行包含“EE”或“MG”时，

我正在考虑创建一个新列称为 level_new ，

email              month      level     level_new
[email protected]    jan        EE2       EE2
[email protected]    jan        nan       EE3
[email protected]    mar        MG1       MG1
[email protected]   jan        nan       MG1
[email protected]    jan        nan       nan
[email protected]    jun        EE3       EE3
[email protected]   jan        nan       MG1
[email protected]   apr        PT        PT
[email protected]   jul        MG1       MG1
[email protected]   aug        MG1       MG1
[email protected]   oct        MG2       MG2  
[email protected]    sep        MG3       MG3

这样我就可以实现以下 groupby

email              month      level     level_new
[email protected]    jan        EE2       EE2
[email protected]    mar        MG1       MG1
[email protected]    jan        nan       EE3
[email protected]    sep        MG3       MG3
[email protected]   jan        nan       MG1
[email protected]   oct        MG2       MG2  
[email protected]    jan        nan       nan
[email protected]   jan        nan       MG1
[email protected]   jul        MG1       MG1

到目前为止，我只能根据分组依据选择第一行和最后一行，但这仍然会选择每个员工第一行的 nan 值。

#get the first and last row of each group
#".nth[-1]" retrieves the last row
#".nth[0]" retrieves the first row
df2 = df.groupby('email', as_index=False).nth([0,-1]) 

Answer 1

我们可以使用 where 将“MG”或“EE”以外的值替换为 NaN；然后 groupby + bfill + fillna 在“level”列中填充 NaN 值，下一个值是“MG”或“EE” ” 对于每个“电子邮件”。

然后使用groupby+应用一个lambda，以列表的形式获取每个“电子邮件”的第一个和最后一个值的索引+分解列表+drop_duplicates （如果某些电子邮件仅出现一次）创建一个掩码，该掩码为每个“电子邮件”的第一个和最后一个值返回 True，否则返回 False。然后使用这个掩码来过滤相关结果：

df['level_new'] = df['level'].fillna(df['level'].where(df['level'].str.contains('MG|EE')).groupby(df['email']).bfill())
out = df.loc[df.groupby('email')['level_new'].apply(lambda x: [x.index.min(), x.index.max()]).explode().drop_duplicates()]

输出：

               email month level level_new
4    [email protected]   jan   NaN       NaN
6   [email protected]   jan   NaN       MG1
8   [email protected]   jul   MG1       MG1
0    [email protected]   jan   EE2       EE2
2    [email protected]   mar   MG1       MG1
1    [email protected]   jan   NaN       EE3
11   [email protected]   sep   MG3       MG3
3   [email protected]   jan   NaN       MG1
10  [email protected]   sep   MG2       MG2

Answer 2

定义以下函数来处理组：

def procGrp(grp):
    if grp.index.size == 1:    # single row only
        return grp
    if pd.isnull(grp.iat[0,2]):
        nxtLev = grp.iat[1,2]  # next "level"
        if ('EE' in nxtLev) or ('MG' in nxtLev):
            grp.iat[0,2] = nxtLev  # set in 1-st row
    # Return first and last row from this group
    return grp.loc[[grp.index[0], grp.index[-1]]]

然后按电子邮件对 DataFrame 进行分组并应用此函数：

result = df.groupby('email').apply(procGrp)

对于您的数据样本，结果为：

               email month level
4    [email protected]   jan   NaN
6   [email protected]   jan   MG1
8   [email protected]   jul   MG1
0    [email protected]   jan   EE2
2    [email protected]   mar   MG1
1    [email protected]   jan   EE3
11   [email protected]   sep   MG3
3   [email protected]   jan   NaN
10  [email protected]   sep   MG2

如您所见：

• 行为 [email protected] 也仍然 NaN，因为该组仅包含
  单行，
• [电子邮件受保护]的行 仍然有 NaN，因为下一行有 level
  =='PT'。

您甚至不需要创建任何额外的列。