在pandas中创建多级数据帧掩码

Question

我有以下数据框

          Style     Land Code      Assessment_per_living
  0       Cape       1010              94.933749
  1       Cape       1010              95.235000
  2       Cape       1010             109.222000
  3   Colonial       1010              64.175109
  4   Colonial       1010              77.270968
  5       Cape       1011              80.856481
  6       Cape       1011              58.060029
  7   Colonial       1011              45.572414
  8   Colonial       1011              67.856000
  9   Colonial       1011              79.564000
  10     Ranch       1011              52.769231
  11     Ranch       1011              57.810434
  12     Ranch       1011              52.472952
  13  Colonial       1012             107.820248
  14  Colonial       1012              84.827050
  15  Colonial       1012              63.626108
  16  Colonial       1012              51.931994
  17     Ranch       1012             109.641711
  18     Ranch       1012              88.243000
  19     Ranch       1012              74.783000

我完成了下面的groupby

n_hood_cost_count= df.groupby(‘土地代码’，‘风格’)‘评估_每_生活’..mean()

这产生了下面的数据帧

Land Code  Style   
 1010       Cape        99.796916
          Colonial      70.723038
  1011      Cape        69.458255
          Colonial      64.330805
           Ranch        54.350872
  1012    Colonial      77.051350
            Ranch       90.889237

我正在尝试添加一个新列，它将遵循多级groupby，但在Assessment_per_living列中返回大于平均值的单个项目。因此，例如，数据帧中的第2行将是1(传递掩码)，而第0行和第1行不是。我希望返回所有groupby集合的结果(1010/Cape、1010/Colonial、1011/Cape、1011/Colonial、1010/Ranch等)。并将其保存在新的数据帧中。有人能教我怎么做吗？

Answer 1

您可以将transform用于means，长度与df相同，请与gt进行比较，最后使用boolean indexing

means = df.groupby(['Land Code','Style'])['Assessment_per_living'].transform('mean')
print (means)
0     99.796916
1     99.796916
2     99.796916
3     70.723039
4     70.723039
5     69.458255
6     69.458255
7     64.330805
8     64.330805
9     64.330805
10    54.350872
11    54.350872
12    54.350872
13    77.051350
14    77.051350
15    77.051350
16    77.051350
17    90.889237
18    90.889237
19    90.889237
Name: Assessment_per_living, dtype: float64

mask = df['Assessment_per_living'].gt(means)
df = df[mask]
print (df)
       Style  Land Code  Assessment_per_living
2       Cape       1010             109.222000
4   Colonial       1010              77.270968
5       Cape       1011              80.856481
8   Colonial       1011              67.856000
9   Colonial       1011              79.564000
11     Ranch       1011              57.810434
13  Colonial       1012             107.820248
14  Colonial       1012              84.827050
17     Ranch       1012             109.641711

如果只需要前2列，则为唯一对添加loc和drop_duplicates：

mask = df['Assessment_per_living'].gt(means)
df = df.loc[mask, ['Style','Land Code']].drop_duplicates()
print (df)
       Style  Land Code
2       Cape       1010
4   Colonial       1010
5       Cape       1011
8   Colonial       1011
11     Ranch       1011
13  Colonial       1012
17     Ranch       1012