How to extract a range of years and generate new rows given a pair of dates-CodePudding

I have two data frames that I would eventually like to merge in order to compare differences between alternative spellings of leader names.

My first dataframe looks something like the following:

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

While my second looks like this:

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

The first data frame has individual rows for every country-year entry in the dataset while the second data frame has a single row for a unique leader with their range of years in office. My goal is to re-shape the second data frame so that it is comparable to the shape of the first.

I would like to take the year range implied by the two datetime columns "leader_start_date, "leader_end_date" in the second data set and "expand" these to create sets of new rows for every year in that range which include duplicate information regarding country and leader name. I would then need to iterate this solution for all unique leader names and their year ranges in the second date frame.

While the data sets aren't a perfect match, getting both data frames in the same shape will allow me to identify a number of matches.

CodePudding user response：

Use:

#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