在合并像ID（主键）这样的公共列上的数据帧时，如何处理在第二个df中针对单个ID多次出现的数据？

Question

所以我有两个dfs

DF1

Superhero ID    Superhero      City
212121          Spiderman      New york
364331          Ironman        New york
678523          Batman         Gotham
432432          Dr Strange     New york
665544          Thor           Asgard
123456          Superman       Metropolis
555555          Nightwing      Gotham
666666          Loki           Asgard

Df2

SID       Mission End date
665544    10/10/2020
665544    03/03/2021
212121    02/02/2021
665544    05/12/2020
212121    15/07/2021
123456    03/06/2021
666666    12/10/2021

我需要创建一个新的df，总结每个城市有多少英雄，他们的任务将在哪个季度完成。我将能够通过Df2中的超级英雄ID或SID（“超级英雄ID”='SID'），将df1中的超级英雄（及其城市）与任务结束日期进行匹配。超级英雄ID在Df1中只出现一次，但在DF2中可以出现多次

最终，我需要计算不同城市的英雄总数（我可以做到，见下文），以及每个季度有多少英雄是免费的

这些是季度的阈值

Quarter 1 – Apr, May, Jun
Quarter 2 – Jul, Aug, Sept
Quarter 3 – Oct, Nov, Dec
Quarter 4 – Jan, Feb, Mar

下面的代码告诉我每个城市有多少英雄：

df_Count = pd.DataFrame(df1.City.value_counts().reset_index())

产生：

 City       Count
New york     3
Gotham       2
Asgard       2
Metropolis   1

我还可以通过以下操作将日期转换为日期时间格式：

#Convert to datetime series
Df2['Mission End date'] = pd.to_datetime('Df2['Mission End date']')

最终我需要一个像这样的新df

     City      Total Count    No. of heroes free in Q3    No. of heroes free in Q4   Free in Q1 2021+
    New york       3               2                           0                           1
    Gotham         2               2                           2                           0
    Asgard         2               1                           2                           0
    Metropolis     1               0                           0                           1 

如果有人能帮我创建合适的宿舍，并能将它们分类到合适的栏目中，我将不胜感激。我还想要一种方法来处理有多个任务结束日期的英雄。我不能忽视它们，我还需要数一数。我想我需要创建一个自定义函数，然后通过apply（）方法和lambda表达式将其应用于每一行。这个问题已经困扰了我一段时间了，所以我非常感谢能得到的所有帮助。非常感谢：）

Answer 1

将数据帧与

 df = df1.merge(df2, left_on='Superhero ID', right_on='SID')

并将日期列转换为pd.datetime格式

df.assign(missing_end_date=lambda x: pd.to_datetime(x['Missing End Date']))

您可以创建两列；一个用于提取新创建的datetime列的季度，另一个用于提取该列的年份

df.assign(quarter_end_date=lambda x: x.missing_end_date.dt.quarter)
  .assign(year_end_date=lambda x: x.missing_end_date.dt.year)

并将它们组合成一列，以Qx，yyyy的格式显示季度

df.assign(quarter_year_end=lambda x: f"Q{int(x.quarter_end_date)}, {int(x.year_end_date)}")

最后，按城市和季度分组，计算超级英雄的数量，并旋转数据帧以获得所需的结果

df.groupby(['City', 'quarter_year_end'])
.count()
.reset_index()
.pivot(index='City', columns='quarter_year_end', values='Superhero')