I have a table that looks like the following:

 -------- -------- ----------- ------------ 
| Binary | Status | Inception | New Status |
 -------- -------- ----------- ------------ 
|      1 | Raising|      2016 |            |
|      1 | Raising|      2017 |            |
|      0 | Failed |      2018 |            |
|      1 | Closed |      2021 |            |
|      1 | Raising|      2020 |            |

I have been populating the New Status column as following:

q = df.query('Binary == 1 and Inception <= 2022 - 3 and Status != "Closed" and Status != "Failed"')
my_query_index = q.index
my_query_index
df.iloc[my_query_index, 3] = "Closed"
df

However, I would like to do this in a more dynamic way, using a < in the first case and a <= in the second case. In this case, if todays date happens to be during the first half of the year, I will use the query

q = df.query('Binary == 1 and Inception < 2022 - 3 and Status != "Closed" and Status != "Failed"')
my_query_index = q.index
my_query_index
df.iloc[my_query_index, 3] = "Closed"
df

and if todays date happens to be in the second half of the year, use

q = df.query('Binary == 1 and Inception <= 2022 - 3 and Status != "Closed" and Status != "Failed"')
my_query_index = q.index
my_query_index
df.iloc[my_query_index, 3] = "Closed"
df

I would like to replace Inception (in the code) with today, given that today = datetime.today().strftime('%Y-%m-%d'). I am not sure how to incorporate these two cases into the code and am also not sure how to incorporate it into the query.

CodePudding user response：

You can use str format for the query

today = datetime.today().strftime('%Y-%m-%d')
half_year = '2022-06-01'

if today < half_year:
    query = 'Binary == 1 and Inception < 2022 - 3 and Status != "Closed" and Status != "Failed"'
else:
    query = 'Binary == 1 and Inception <= 2022 - 3 and Status != "Closed" and Status != "Failed"'

then your regular code

q = df.query(query)
my_query_index = q.index
my_query_index
df.iloc[my_query_index, 3] = "Closed"
df