如何提取年份范围并在给定一对日期的情况下生成新行

Question

我有两个数据框，我最终想合并，以便比较不同的领导人姓名拼写之间的差异

我的第一个数据帧如下所示：

year    country_isocode country_name    leader  leader_start_date   leader_end_date
20  1986    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
21  1987    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
22  1988    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
23  1989    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
24  1990    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
25  1991    AFG Afghanistan Mohammed Najibullah 1986-05-04  1992-04-16
26  1992    AFG Afghanistan Burhanuddin Rabbani 1992-06-28  1996-09-27
27  1993    AFG Afghanistan Burhanuddin Rabbani 1992-06-28  1996-09-27
28  1994    AFG Afghanistan Burhanuddin Rabbani 1992-06-28  1996-09-27

而我的第二个看起来像这样：

leader_start_date   leader_end_date LeaderCountryOrIGO  LeaderCountryISO    LeaderTitle LeaderLastName  LeaderFullName
0   1986-05-04  1990-06-28  Afghanistan AFG General Secretary   Najibullah  Mohammad Najibullah
1   1989-02-21  1990-05-07  Afghanistan AFG Prime Minister  Keshtmand   Ali Keshtmand
2   1990-05-07  1992-04-15  Afghanistan AFG Prime Minister  Khaliqyar   Fazal Haq Khaliqyar
3   1992-04-16  1992-04-28  Afghanistan AFG President (Acting)  Hatef   Abdul Rahim Hatef
4   1992-04-28  1992-06-28  Afghanistan AFG President (Acting)  Mojadedi    Sibghatullah Mojadedi
5   1992-06-28  1996-09-27  Afghanistan AFG President   Rabbani Burhanuddin Rabbani

第一个数据框为数据集中的每个国家/地区年份条目提供了单独的行，而第二个数据框为具有任期范围的唯一领导人提供了单独的行。我的目标是重新塑造第二个数据帧的形状，使其与第一个数据帧的形状相似

我想取第二个数据集中的两个日期时间列“leader\u start\u date”、“leader\u end\u date”和“expand”所暗示的年份范围“这些选项用于在该范围内每年创建一组新行，其中包括有关国家和领导人姓名的重复信息。然后，我需要在第二个日期框架中对所有唯一的领导者姓名及其年份范围迭代此解决方案

虽然数据集不是完美的匹配，但让两个数据帧的形状相同将允许我识别许多匹配

Answer 1

使用：

#convert both columns to datetimes
df['leader_start_date'] = pd.to_datetime(df['leader_start_date'])
df['leader_end_date'] = pd.to_datetime(df['leader_end_date'])

#create new column by years
df.insert(0, 'year', df['leader_start_date'].dt.year)

#subtract years for repeating, repalce missing values by actual year
s = df['leader_end_date'].dt.year.fillna(pd.to_datetime('now').year) - df['year']

#if output is previous year by leader_end_date
df = df.loc[df.index.repeat(s)].copy()
#if output match also year in leader_end_date
# df = df.loc[df.index.repeat(s + 1)].copy()

#add counter to column year
df['year'] += df.groupby(level=0).cumcount()

#create default index
df = df.reset_index(drop=True)

---

print (df)
    year leader_start_date leader_end_date LeaderCountryOrIGO  \
0   1986        1986-05-04      1990-06-28        Afghanistan   
1   1987        1986-05-04      1990-06-28        Afghanistan   
2   1988        1986-05-04      1990-06-28        Afghanistan   
3   1989        1986-05-04      1990-06-28        Afghanistan   
4   1989        1989-02-21      1990-05-07        Afghanistan   
5   1990        1990-05-07      1992-04-15        Afghanistan   
6   1991        1990-05-07      1992-04-15        Afghanistan   
7   1992        1992-06-28      1996-09-27        Afghanistan   
8   1993        1992-06-28      1996-09-27        Afghanistan   
9   1994        1992-06-28      1996-09-27        Afghanistan   
10  1995        1992-06-28      1996-09-27        Afghanistan   

   LeaderCountryISO        LeaderTitle LeaderLastName       LeaderFullName  
0               AFG  General Secretary     Najibullah  Mohammad Najibullah  
1               AFG  General Secretary     Najibullah  Mohammad Najibullah  
2               AFG  General Secretary     Najibullah  Mohammad Najibullah  
3               AFG  General Secretary     Najibullah  Mohammad Najibullah  
4               AFG     Prime Minister      Keshtmand        Ali Keshtmand  
5               AFG     Prime Minister      Khaliqyar  Fazal Haq Khaliqyar  
6               AFG     Prime Minister      Khaliqyar  Fazal Haq Khaliqyar  
7               AFG          President        Rabbani  Burhanuddin Rabbani  
8               AFG          President        Rabbani  Burhanuddin Rabbani  
9               AFG          President        Rabbani  Burhanuddin Rabbani  
10              AFG          President        Rabbani  Burhanuddin Rabbani