与Pandas一起获得一个新的专栏（其他元素的共识）

Question

我需要一些帮助使用熊猫数据帧。 数据框如下：

group   col1    col2    name
1       dog     40      canidae
1       dog     40      canidae
1       dog     40      canidae
1       dog     40      canidae
1       dog     40  
1       dog     40      canidae
1       dog     40      canidae
2       frog    85      dendrobatidae
2       frog    89      leptodactylidae
2       frog    89      leptodactylidae
2       frog    82      leptodactylidae
2       frog    89 
2       frog    81 
2       frog    89      dendrobatidae
3       horse   87      equidae1
3       donkey  76      equidae2
3       zebra   67      equidae3
4       bird    54      psittacidae
4       bird    56  
4       bird    34  
5       bear    67    
5       bear    54

我想得到的是添加一个列“consudence\u name”来获得：

group col1   col2 name              consensus_name
1     dog    40   canidae           canidae
1     dog    40   canidae           canidae
1     dog    40                     canidae
1     dog    40   canidae           canidae
1     dog    40   canidae           canidae
2     frog   85   dendrobatidae     leptodactylidae
2     frog   89   leptodactylidae   leptodactylidae
2     frog   89   leptodactylidae   leptodactylidae
2     frog   82   leptodactylidae   leptodactylidae
2     frog   89                     leptodactylidae
2     frog   81                     leptodactylidae
2     frog   89   dendrobatidae     leptodactylidae
3     horse  87   equidae1          equidae3
3     donkey 76   equidae2          equidae3
3     zebra  67   equidae3          equidae3
4     bird   54   psittacidae       psittacidae
4     bird   56                     psittacidae
4     bird   34                     psittacidae
5     bear   67                     NA
5     bear   54                     NA

为了得到每个组的新列，我得到了组中最具代表性的名称。你知道吗

• 对于group1，有4行名为'canidae'，一行没有，因此对于每一行，我将'canidae'写在consensus_name列中

• 对于group2，有2行名为'dendrobatidae'，2行没有，3行名为'leptodactylidae'，因此对于每一行，我都在consensus_name列中写入leptodactylidae'。

• 对于group3有3行具有不同的名称，因此由于没有一致性，我得到的名称是最低的col2编号，因此我在consensus_name列中写入'equidae3'。

• 对于组4，只有一行有信息，所以它是group4的一致名称，所以我在consensus_name列中写psittacidae。

• 对于group5没有任何信息，只需在consensus_name列中写入NA即可。

有没有人想到要和熊猫一起做？感谢您的帮助：）

---

anky的输出=

    group    col1  col2             name   consensus_name
0       1     dog    40          canidae          canidae
1       1     dog    40          canidae          canidae
2       1     dog    40          canidae          canidae
3       1     dog    40          canidae          canidae
4       1     dog    40              NaN          canidae
5       1     dog    40          canidae          canidae
6       1     dog    40          canidae          canidae
7       2    frog    85    dendrobatidae    dendrobatidae
8       2    frog    89  leptodactylidae  leptodactylidae
9       2    frog    89  leptodactylidae  leptodactylidae
10      2    frog    82  leptodactylidae  leptodactylidae
11      2    frog    89              NaN  leptodactylidae
12      2    frog    81              NaN  leptodactylidae
13      2    frog    89    dendrobatidae    dendrobatidae
14      3   horse    87         equidae1         equidae1
15      3  donkey    76         equidae2         equidae2
16      3   zebra    67         equidae3         equidae3
17      4    bird    54      psittacidae      psittacidae
18      4    bird    56              NaN      psittacidae
19      4    bird    34              NaN      psittacidae
20      5    bear    67              NaN              NaN
21      5    bear    54              NaN              NaN

Answer 1

使用pandas.DataFrame.Groupby.Series.transform并将max函数传递给它：

#First fillna with empty string
df.name.fillna('', inplace=True)

df['consensus_name'] = df.groupby('group').name.transform('max')

print(df)
    group    col1  col2             name   consensus_name
0       1     dog    40          canidae          canidae
1       1     dog    40          canidae          canidae
2       1     dog    40          canidae          canidae
3       1     dog    40          canidae          canidae
4       1     dog    40                           canidae
5       1     dog    40          canidae          canidae
6       1     dog    40          canidae          canidae
7       2    frog    85    dendrobatidae  leptodactylidae
8       2    frog    89  leptodactylidae  leptodactylidae
9       2    frog    89  leptodactylidae  leptodactylidae
10      2    frog    82  leptodactylidae  leptodactylidae
11      2    frog    89                   leptodactylidae
12      2    frog    81                   leptodactylidae
13      2    frog    89    dendrobatidae  leptodactylidae
14      3   horse    87         equidae1         equidae3
15      3  donkey    76         equidae2         equidae3
16      3   zebra    67         equidae3         equidae3
17      4    bird    54      psittacidae      psittacidae
18      4    bird    56                       psittacidae
19      4    bird    34                       psittacidae
20      5    bear    67                                  
21      5    bear    54                                  

编辑指出后一般不适用：

df['name'] = df.groupby('group').name.ffill()

df_group = df.groupby('group').name.apply(lambda x: pd.Series.mode(x, dropna=False)).reset_index()
df_group = df_group[df_group.level_1 == df_group.groupby('group').level_1.transform('max')]
df_group.rename({'name':'consensus_name'},axis=1, inplace=True)

df_final = pd.merge(df, df_group, on='group')

print(df_final)
    group    col1  col2             name  level_1   consensus_name
0       1     dog    40          canidae        0          canidae
1       1     dog    40          canidae        0          canidae
2       1     dog    40          canidae        0          canidae
3       1     dog    40          canidae        0          canidae
4       1     dog    40          canidae        0          canidae
5       1     dog    40          canidae        0          canidae
6       1     dog    40          canidae        0          canidae
7       2    frog    85    dendrobatidae        0  leptodactylidae
8       2    frog    89  leptodactylidae        0  leptodactylidae
9       2    frog    89  leptodactylidae        0  leptodactylidae
10      2    frog    82  leptodactylidae        0  leptodactylidae
11      2    frog    89  leptodactylidae        0  leptodactylidae
12      2    frog    81  leptodactylidae        0  leptodactylidae
13      2    frog    89    dendrobatidae        0  leptodactylidae
14      3   horse    87         equidae1        2         equidae3
15      3  donkey    76         equidae2        2         equidae3
16      3   zebra    67         equidae3        2         equidae3
17      4    bird    54      psittacidae        0      psittacidae
18      4    bird    56      psittacidae        0      psittacidae
19      4    bird    34      psittacidae        0      psittacidae
20      5    bear    67              NaN        0              NaN
21      5    bear    54              NaN        0              NaN

Answer 2

您需要定义自己的函数。确保用NaN替换空字符串，这样就不会考虑它们。transform在基于多个列的计算中会变得很棘手，因此应该使用groupby并将结果映射回原始列。你知道吗

import numpy as np

def my_mode(gp):
    s = gp['name'].value_counts()
    s = s[s.eq(s.max())]

    if len(s) == 0:      # If all missing
        return np.NaN
    elif len(s) == 1:    # If there is a mode without ties
        return s.index[0]
    else:                # If ties, use the one with min col2 
        return gp.loc[gp['name'].isin(s.index)].sort_values('col2')['name'].iloc[0]

---

df['name'] = df['name'].replace({'': np.NaN})
df['consensus_name'] = df['group'].map(df.groupby('group').apply(my_mode))

输出：

    group    col1  col2             name   consensus_name
0       1     dog    40          canidae          canidae
1       1     dog    40          canidae          canidae
2       1     dog    40          canidae          canidae
3       1     dog    40          canidae          canidae
4       1     dog    40              NaN          canidae
5       1     dog    40          canidae          canidae
6       1     dog    40          canidae          canidae
7       2    frog    85    dendrobatidae  leptodactylidae
8       2    frog    89  leptodactylidae  leptodactylidae
9       2    frog    89  leptodactylidae  leptodactylidae
10      2    frog    82  leptodactylidae  leptodactylidae
11      2    frog    89              NaN  leptodactylidae
12      2    frog    81              NaN  leptodactylidae
13      2    frog    89    dendrobatidae  leptodactylidae
14      3   horse    87         equidae1         equidae3
15      3  donkey    76         equidae2         equidae3
16      3   zebra    67         equidae3         equidae3
17      4    bird    54      psittacidae      psittacidae
18      4    bird    56              NaN      psittacidae
19      4    bird    34              NaN      psittacidae
20      5    bear    67              NaN              NaN
21      5    bear    54              NaN              NaN   

---

我们唯一没有明确定义的边情况是，当模态值和的最小值col2有联系时会发生什么。目前，在这些情况下，它将选择索引最低的名称（数据帧中最先出现的名称）。你知道吗