Pandas比较来自两个数据框的两列，如果有匹配，则将第三列的值转换为周数

Question

试图自动生成一个报告，但它超出了我目前的技能水平，如果可能的话，请协助。df1

report = {'Name':["one", "two", "three", "four", "five"],'Week_Num':['','','','',''],"Week_1":['1','1','','',''],"Week_2":['','1','','1',''],"Week_3":['','','','','1'],"Week_4":['','','1','',''],"Week_5":['','','','','']}

df2

data = {'Name': ["one", "two", "four", "five", "two", "three"],
"Dates":["03/01/2022", "09/01/2022", "13/01/2022", "21/01/2022", "01/01/2022","28/01/2022"]}

现在我必须匹配报告['Name']与数据['Name']，如果有匹配获得数据["日期"]将数据["日期"]转换为week_number"今天是第18周"，并增加报告的值。每次匹配时[match, week_number] += 1。预期的输出:

report = {'Name':["one", "two", "three", "four", "five"],'Week_Num':['','','','',''],"Week_1":['1','1','','',''],"Week_2":['','1','','1',''],"Week_3":['','','','','1'],"Week_4":['','','1','',''],"Week_5":['','','','','']}

Answer 1

Try withmerge:

data["Week_Num"] = pd.to_datetime(data["Dates"],format="%d/%m/%Y").dt.strftime("%W").astype(int).add(1)
counts = data[["Name","Week_Num"]].merge(report["Name"], how="right").set_index("Name")
output = pd.get_dummies(counts["Week_Num"]).reindex(range(1,6),axis=1,fill_value=0).groupby(level=0).sum().reindex(report["Name"]).reset_index()
>>> output
Name  1  2  3  4  5
0    one  0  1  0  0  0
1    two  1  1  0  0  0
2  three  0  0  0  0  1
3   four  0  0  1  0  0
4   five  0  0  0  1  0