python - Groupby，转换 - 恢复到原始表

Question

我有以下数据框:

data={"Date":["8/4/2020","8/4/2020","8/4/2020","8/4/2020","8/4/2020"],
      "Weight":[35,40,30,50,50],
      "Subportfolio":["Portfolio1","Portfolio2","Portfolio3","Portfolio4","Portfolio5"],
      "Portfolio Code":[1,1,1,2,2]}
df=pd.DataFrame(data)

表格看起来是这样的:

    Date      Weight      Subportfolio  Portfolio Code
0   8/4/2020    35        Portfolio1          1
1   8/4/2020    40        Portfolio2          1
2   8/4/2020    30        Portfolio3          1
3   8/4/2020    50        Portfolio4          2
4   8/4/2020    50        Portfolio5          2

如果您观察到 Portfolio Code 等于 1 的权重总和大于 100，那么我想重新缩放它们并最终拥有相同的数据框。重新缩放由

df.groupby(by="Portfolio Code").apply(lambda x: x["Weight"]/x["Weight"].sum()*100)

结果输出是:

Portfolio Code   
1               0    33.333333
                1    38.095238
                2    28.571429
2               3    50.000000
                4    50.000000

投资组合代码 1 的权重已经重新调整并总计为 100。但是我不知道如何回到原来的 table 。我想在最后:

    Date      Weight            Subportfolio    Portfolio Code
0   8/4/2020    33.333333         Portfolio1          1
1   8/4/2020    38.095238         Portfolio2          1
2   8/4/2020    28.571429         Portfolio3          1
3   8/4/2020    50                Portfolio4          2
4   8/4/2020    50                Portfolio5          2

我读到有关在 apply 的地方使用 transform 但不断出现错误并且不知道如何解决这个问题。

Answer 1

在你想将 groupby 结果重新分配回原始数据帧的情况下，这通常意味着你想要 groupby().transform:

df['Weight'] = df.groupby(by="Portfolio Code")['Weight'].transform(lambda x: x/x.sum())

或者稍微好一些:

df['Weight'] = df['Weight']*100/df.groupby(by="Portfolio Code")['Weight'].transform('sum')

输出:

       Date     Weight Subportfolio  Portfolio Code
0  8/4/2020  33.333333   Portfolio1               1
1  8/4/2020  38.095238   Portfolio2               1
2  8/4/2020  28.571429   Portfolio3               1
3  8/4/2020  50.000000   Portfolio4               2
4  8/4/2020  50.000000   Portfolio5               2