如何在每个组的pandas数据帧中估算一列

Question

全部，

我有四列的数据帧("key1"、"key2"、"data1"one_answers"data2"(。我在数据1中插入了一些nan。现在，我想在nan中填充在groupby(['key1', 'key2'])之后每组中出现次数最多的值。

dt =  pd.DataFrame ({'key1': np.random.choice(['a', 'b'], size=100),
                 'key2': np.random.choice(['c', 'd'], size=100),
                  'data1': np.random.randint(5, size=100),
                  'data2': np.random.randn(100)},
                columns = ['key1', 'key2','data1', 'data2'])
#insert nan 
dt['data1'].ix[[2,6,10]]= None
# group by key1 and key2
group =dt.groupby(['key1', 'key2'])['data1']
group.value_counts(dropna=False)
key1  key2  data1
a     c     1.0       8
            4.0       6
            0.0       4
            2.0       2
            3.0       1
      d     0.0       7
            1.0       6
            4.0       6
            2.0       5
            NaN       3
            3.0       1
b     c     0.0       7
            2.0       7
            1.0       3
            3.0       2
            4.0       2
      d     2.0      11
            1.0      10
            0.0       3
            3.0       3
            4.0       3

在本例中，我想做的是用0.0(组中最频繁的值(key1=a，key2=d((填充data1列中的nan。

非常感谢你的帮助！

Answer 1

使用.transform(lambda y: y.fillna(y.value_counts().idxmax()))

之前

key1  key2  data1
a     c     1.0       6
            3.0       5
            0.0       4
            2.0       3
            4.0       3
            NaN       1
      d     1.0      11
            3.0       9
            0.0       5
            2.0       5
            4.0       5
b     c     4.0       7
            0.0       4
            3.0       4
            2.0       3
            NaN       2
            1.0       1
      d     4.0       6
            1.0       5
            2.0       5
            3.0       4
            0.0       2
Name: data1, dtype: int64

应用.transform(lambda y: y.fillna(y.value_counts().idxmax())) 后

dt['nan_filled'] = dt.groupby(['key1', 'key2'])['data1'].transform(lambda y: y.fillna(y.value_counts().idxmax()))
group = dt.groupby(['key1', 'key2'])['nan_filled']
group.value_counts(dropna=False)

key1  key2  nan_filled
a     c     1.0            7
            3.0            5
            0.0            4
            2.0            3
            4.0            3
      d     1.0           11
            3.0            9
            0.0            5
            2.0            5
            4.0            5
b     c     4.0            9
            0.0            4
            3.0            4
            2.0            3
            1.0            1
      d     4.0            6
            1.0            5
            2.0            5
            3.0            4
            0.0            2
Name: nan_filled, dtype: int64