当在ID (主键)这样的公共列上合并Dataframes时，如何处理单个ID在第二个df中多次出现的数据？

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，总结多少英雄在每个城市和在哪个季度将完成他们的任务。我将能够匹配df1中的超级英雄(和他们的城市)，通过他们的超级英雄ID或Df2中的SID (‘超级英雄Id’=‘SID’)来完成任务。超级英雄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')