添加具有标准化选定值的列

Question

我在this问题中发现了一些问题，所以我想更进一步。我有以下数据帧df：

H,Nu,City,L,C
0.965392,15,Madrid,es,es
0.920614,15,Madrid,it,es
0.726219,16,Madrid,tn,es
0.739119,17,Madrid,fr,es
0.789923,55,Dublin,mt,en
0.699239,57,Dublin,en,en
0.890462,68,Dublin,ar,en
0.746863,68,Dublin,pt,en
0.789923,55,Milano,it,it
0.699239,57,Milano,es,it
0.890462,68,Milano,ar,it
0.746863,68,Milano,pt,it

我想添加一个名为Hm的列，定义如下：

Hm = H/(H,City - where L==C)

到目前为止，我找到了正确的值/城市：

gp = df.groupby('City')
mask = gp.apply(lambda x: x['L'] == x['C'])
lookup = df.loc[mask[mask].reset_index(level=0).index]

获取：

     HmCity  Nu    City   L   C
5  0.699239  57  Dublin  en  en
0  0.965392  15  Madrid  es  es
8  0.789923  55  Milano  it  it

H值现在是规范化的正确值。我现在如何添加一个新的列Hm，以便在lookup的相应位置缩放新的数据帧？例如：

H,Nu,City,L,C,Hm
0.965392,15,Madrid,es,es,1,0
0.920614,15,Madrid,it,es,**
0.726219,16,Madrid,tn,es,**
0.739119,17,Madrid,fr,es,**
0.789923,55,Dublin,mt,en,**
0.699239,57,Dublin,en,en,1,0
0.890462,68,Dublin,ar,en,**
0.746863,68,Dublin,pt,en,**
0.789923,55,Milano,it,it,1,0
0.699239,57,Milano,es,it,**
0.890462,68,Milano,ar,it,**
0.746863,68,Milano,pt,it,**

我想避免merge，因为这会导致我的行为不正确。你知道吗

编辑：

澄清一下：我们可以添加一个新列Hm，其中包含每个城市对应的HmCity值，如下所示：

H,Nu,City,L,C,HmCity
0.965392,15,Madrid,es,es,0.965392
0.920614,15,Madrid,it,es,0.965392
0.726219,16,Madrid,tn,es,0.965392
0.739119,17,Madrid,fr,es,0.965392
0.789923,55,Dublin,mt,en,0.699239
0.699239,57,Dublin,en,en,0.699239
0.890462,68,Dublin,ar,en,0.699239
0.746863,68,Dublin,pt,en,0.699239
0.789923,55,Milano,it,it,0.789923
0.699239,57,Milano,es,it,0.789923
0.890462,68,Milano,ar,it,0.789923
0.746863,68,Milano,pt,it,0.789923

Answer 1

根据您修改后的问题，以下内容适用于您的特定数据集：

import pandas as pd

df = pd.DataFrame(
    data=[[0.965392, 15, "Madrid", "es", "es"],
          [0.920614, 15, "Madrid", "it", "es"],
          [0.726219, 16, "Madrid", "tn", "es"],
          [0.739119, 17, "Madrid", "fr", "es"],
          [0.789923, 55, "Dublin", "mt", "en"],
          [0.699239, 57, "Dublin", "en", "en"],
          [0.890462, 68, "Dublin", "ar", "en"],
          [0.746863, 68, "Dublin", "pt", "en"],
          [0.789923, 55, "Milano", "it", "it"],
          [0.699239, 57, "Milano", "es", "it"],
          [0.890462, 68, "Milano", "ar", "it"],
          [0.746863, 68, "Milano", "pt", "it"]],
    columns=["H", "Nu", "City", "L", "C"])


def func(x):
    x['Hm'] = x.loc[x['L'] == x['C'], "H"].values[0]
    return x


print(df.groupby(["City"]).apply(func))

这将产生以下输出。你知道吗

           H  Nu    City   L   C        Hm
0   0.965392  15  Madrid  es  es  0.965392
1   0.920614  15  Madrid  it  es  0.965392
2   0.726219  16  Madrid  tn  es  0.965392
3   0.739119  17  Madrid  fr  es  0.965392
4   0.789923  55  Dublin  mt  en  0.699239
5   0.699239  57  Dublin  en  en  0.699239
6   0.890462  68  Dublin  ar  en  0.699239
7   0.746863  68  Dublin  pt  en  0.699239
8   0.789923  55  Milano  it  it  0.789923
9   0.699239  57  Milano  es  it  0.789923
10  0.890462  68  Milano  ar  it  0.789923
11  0.746863  68  Milano  pt  it  0.789923

也许还有更好的方法。如果我能想出一个答案，我会更新答案的。你知道吗