python - 根据文本将值从一个数据帧平均分配到另一个数据帧

Question

我有两个如下所示的数据框

import pandas as pd 
import numpy as np

random = np.random.RandomState(seed=123)

columns = ['Assessment Type', 'Weight %']

assessment = ['Mid-Term', 'Quiz', 'Final Exam', 'Assignment']

wight = [30, 20, 35, 15]

df1 = pd.DataFrame(np.column_stack([assessment, wight]),
                           columns=columns)

df1['Weight %'] = df1['Weight %'].apply(int)

df2 = pd.DataFrame(random.randint(0, 100, size=(4, 8)), columns=list('ABCDEFGH'))

df2.iloc[0, :] = ['', 'Mid-Term', 'Quiz', 'Mid-Term', 'Quiz', '', 'Final Exam', '']
df2.iloc[1, :] = ['' for i in range(df2.columns.size)]

df1 =

  Assessment Type  Weight %
0        Mid-Term        30
1            Quiz        20
2      Final Exam        35
3      Assignment        15

df2 =

    A         B     C         D     E   F           G   H
0      Mid-Term  Quiz  Mid-Term  Quiz      Final Exam
1
2  78        36    96        80    68  49          55  67
3   2        84    39        66    84  47          61  48

我想在 df2 中的相同成绩评估类型之间平均分配 df1 的成绩权重

上述情况所需的输出如下。

    A         B     C         D     E   F           G   H
0      Mid-Term  Quiz  Mid-Term  Quiz      Final Exam
1            15    10        15    10              35
2  78        36    96        80    68  49          55  67
3   2        84    39        66    84  47          61  48

有人可以帮助在 pandas 中实现这一目标吗？

感谢和问候

Answer 1

试试这个:

type_counts = df2.loc[0].value_counts().drop('').rename("type_counts")
distributor = df1.set_index("Assessment Type").join(type_counts)
distributor["alocated weight"] = distributor["Weight %"].div(distributor["type_counts"])
df2.loc[1] = df2.loc[0].map(distributor["alocated weight"]).fillna('')
print(df2)

结果:

    A         B     C         D     E   F           G   H
0      Mid-Term  Quiz  Mid-Term  Quiz      Final Exam    
1            15    10        15    10              35    
2  78        36    96        80    68  49          55  67
3   2        84    39        66    84  47          61  48