python 3.x - Append a column from one df to another based on the date column on both dfs - pandas

Question

I have two dfs as shown below.

df1:

Date                t_factor     
2020-02-01             5             
2020-02-02             23              
2020-02-03             14           
2020-02-04             23
2020-02-05             23  
2020-02-06             23          
2020-02-07             30            
2020-02-08             29            
2020-02-09             100
2020-02-10             38
2020-02-11             38               
2020-02-12             38                    
2020-02-13             70           
2020-02-14             70 
2020-02-15             38               
2020-02-16             38                    
2020-02-17             70           
2020-02-18             70 
2020-02-19             38               
2020-02-20             38                    
2020-02-21             70           
2020-02-22             70 
2020-02-23             38               
2020-02-24             38                    
2020-02-25             70           
2020-02-26             70 
2020-02-27             70 

df2:

From                to                   plan          score
2020-02-03          2020-02-05           start         20
2020-02-07          2020-02-08           foundation    25
2020-02-10          2020-02-12           learn         10
2020-02-14          2020-02-16           practice      20
2020-02-15          2020-02-21           exam          30
2020-02-20          2020-02-23           test          10

From the above I would like to append the plan column to df1 based on the From and to date value in df2 and Date value in df1.

Expected output:

output_df

Date                t_factor        plan
2020-02-01             5            NaN
2020-02-02             23           NaN   
2020-02-03             14           start          
2020-02-04             23           start
2020-02-05             23           start  
2020-02-06             23           NaN
2020-02-07             30           foundation               
2020-02-08             29           foundation        
2020-02-09             100          NaN
2020-02-10             38           learn
2020-02-11             38           learn              
2020-02-12             38           learn                   
2020-02-13             70           NaN
2020-02-14             70           practice
2020-02-15             38           NaN              
2020-02-16             38           NaN                    
2020-02-17             70           exam      
2020-02-18             70           exam
2020-02-19             38           exam   
2020-02-20             38           NaN                 
2020-02-21             70           NaN         
2020-02-22             70           test
2020-02-23             38           test             
2020-02-24             38           NaN        
2020-02-25             70           NaN
2020-02-26             70           NaN
2020-02-27             70           NaN

Note:

If there is any overlapping date, then keep plan as NaN for that date.

Example:

2020-02-14 to 2020-02-16 plan is practice.

And 2020-02-15 to 2020-02-21 plan is exam.

So there is overlap is on 2020-02-15 and 2020-02-16.

Hence plan should be NaN for that date range.

I would like to implement function shown below.

def (df1, df2)
    return output_df

Answer 1

Using pd.to_datetime convert the date like columns to pandas datetime series:

df1['Date'] = pd.to_datetime(df1['Date'])
df2[['From', 'to']] = df2[['From', 'to']].apply(pd.to_datetime)

Create a pd.IntervalIndex from the columns From and to of df2, then use Series.map on the column Date of df1 to map it to column plan from df2 (after setting the idx):

idx = pd.IntervalIndex.from_arrays(df2['From'], df2['to'], closed='both')
df1['plan'] = df1['Date'].map(df2.set_index(idx)['plan'])

Result:

         Date  t_factor        plan
0  2020-02-01         5         NaN
1  2020-02-02        23         NaN
2  2020-02-03        14       start
3  2020-02-04        23       start
4  2020-02-05        23       start
5  2020-02-06        23         NaN
6  2020-02-07        30  foundation
7  2020-02-08        29  foundation
8  2020-02-09       100         NaN
9  2020-02-10        38       learn
10 2020-02-11        38       learn
11 2020-02-12        38       learn
12 2020-02-13        70         NaN
13 2020-02-14        70    practice
14 2020-02-15        38    practice
15 2020-02-16        38    practice
16 2020-02-17        70        exam
17 2020-02-18        70        exam
18 2020-02-19        38         NaN
19 2020-02-20        38        test
20 2020-02-21        70        test
21 2020-02-22        70        test
22 2020-02-23        38        test
23 2020-02-24        38         NaN
24 2020-02-25        70         NaN
25 2020-02-26        70         NaN
26 2020-02-27        70         NaN