python - How to assign a value if in between values using Pandas

Question

my df_created:

created_date
------------    
2020-01-01     
2020-01-21     
2020-01-15     
2020-01-01     
2020-01-26     
2020-01-30     

Notice that the above created_date is in random date order

I have another df_ipsum

id      date_start     date_end
---     -----------    ----------
100     2020-01-01     2020-01-09    
101     2020-01-10     2020-01-19  
102     2020-01-20     2020-01-29  
103     2020-01-30     2020-02-09  
104     2020-02-10     2020-02-19  

What I want to do is assign the value of id from df_ipsum if the value of created_date on df_created Data Frame is between date_start and date_end so the new df_created looks like this:

created_date    id_2
------------    ----    
2020-01-01      100
2020-01-21      102
2020-01-15      101
2020-01-01      100
2020-01-26      102
2020-01-30      103

What did I do?

# using conditional expression: a if cond else b
df_created['id_2']  =  df_ipsum['id'] if (~df_created['created_date'].between(df_ipsum['date_start'],
df_ipsum['date_end'])) else False

when I do that I get this error:

ValueError: Can only compare identically-labeled Series objects

however, my dtypes are identical, which is datetime64[ns] for all df_created['created_date'], df_ipsum['date_start'], and df_ipsum['date_end']

Could someone please help me solve this?

question from:https://stackoverflow.com/questions/65648462/how-to-assign-a-value-if-in-between-values-using-pandas

Answer 1

You can use pd.cut:

df_created['id'] = pd.cut(df_created['created_date'], 
       bins=[df_ipsum['date_start'].iloc[0]] + list(df_ipsum['date_end']),
       include_lowest=True,
       labels=df_ipsum['id'])

Output:

  created_date   id
0   2020-01-01  100
1   2020-01-21  102
2   2020-01-15  101
3   2020-01-01  100
4   2020-01-26  102
5   2020-01-30  103