If - Then With Dates

[Hockey_Rob]

All,

I am very new to python and I am struggling to create conditional formulas with dates. I have two excel spreadsheets that I have read in using Pandas. Each of the Data Frames contain a Start Date Column and an End Date Column. Pandas read the date columns in correctly as datetime64[ns].

Data Frame One: read in as fiscal_periods (Column B: FP Start Date, Column C: FP End Date)
Data Frame Two: loan_ data (Column B: Start Date, Column C: End Date)

I need the code to take the Start Date from loan_data and evaluate it agains the FP Start Date from fiscal_periods and return the greater of the two values and create a column in the loan_data data frame called P1 Start and populate it with the value of the if/then statement.

here is what I tried and it won't work:

### This Program Calculates the In-Period Loans and In-Period Loan days ###

### Import Modules ###
import pandas as pd
import numpy as np

### Read in excel files ###
fiscal_periods = pd.read_excel('fiscal_periods.xlsx')
loan_data = pd.read_excel('TMPro Loan Program.xlsx')

#fiscal_periods.index
#print(loan_data)


### Calculate Loan Days by Loans ###
import datetime

loan_data['Total Loan Days'] = (loan_data['End Date'] - loan_data['Start Date']) / pd.Timedelta("1 Days")

### Determine Start Day for Loan Period Days Calc ###
np.logical_and

### Determine Loan in Period(s) ###

loan_data['FY22_P1'] = (loan_data['Start Date'] < fiscal_periods.loc[0,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[0,'FP Start'])
#loan_data['FY22_P2'] = (loan_data['Start Date'] < fiscal_periods.loc[1,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[1,'FP Start'])
#loan_data['FY22_P3'] = (loan_data['Start Date'] < fiscal_periods.loc[2,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[2,'FP Start'])
#loan_data['FY22_P4'] = (loan_data['Start Date'] < fiscal_periods.loc[3,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[3,'FP Start'])
#loan_data['FY22_P5'] = (loan_data['Start Date'] < fiscal_periods.loc[4,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[4,'FP Start'])
#loan_data['FY22_P6'] = (loan_data['Start Date'] < fiscal_periods.loc[5,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[5,'FP Start'])
#loan_data['FY22_P7'] = (loan_data['Start Date'] < fiscal_periods.loc[6,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[6,'FP Start'])
#loan_data['FY22_P8'] = (loan_data['Start Date'] < fiscal_periods.loc[7,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[7,'FP Start'])
#loan_data['FY22_P9'] = (loan_data['Start Date'] < fiscal_periods.loc[8,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[8,'FP Start'])
#loan_data['FY22_P10'] = (loan_data['Start Date'] < fiscal_periods.loc[9,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[9,'FP Start'])
#loan_data['FY22_P11'] = (loan_data['Start Date'] < fiscal_periods.loc[10,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[10,'FP Start'])
#loan_data['FY22_P12'] = (loan_data['Start Date'] < fiscal_periods.loc[11,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[11,'FP Start'])
#loan_data['FY23_P1'] = (loan_data['Start Date'] < fiscal_periods.loc[12,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[12,'FP Start'])
#loan_data['FY23_P2'] = (loan_data['Start Date'] < fiscal_periods.loc[13,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[13,'FP Start'])
#loan_data['FY23_P3'] = (loan_data['Start Date'] < fiscal_periods.loc[14,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[14,'FP Start'])


##loan_data.to_excel('Loan Data Updated.xlsx')

### Date Comparison Practice ###

if loan_data['Start Date'] > fiscal_periods.loc[0,'FP Start']:
    loan_data['P1 Start'] = loan_data['Start Date']
else:
    loan_data['P1 Start'] = fiscal_periods.loc[0, 'FP Start']

I get the following error:

ValueError                                Traceback (most recent call last)
Input In [59], in <cell line: 46>()
     25 loan_data['FY22_P1'] = (loan_data['Start Date'] < fiscal_periods.loc[0,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[0,'FP Start'])
     26 #loan_data['FY22_P2'] = (loan_data['Start Date'] < fiscal_periods.loc[1,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[1,'FP Start'])
     27 #loan_data['FY22_P3'] = (loan_data['Start Date'] < fiscal_periods.loc[2,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[2,'FP Start'])
     28 #loan_data['FY22_P4'] = (loan_data['Start Date'] < fiscal_periods.loc[3,'FP End']) & (loan_data['End Date'] > fiscal_periods.loc[3,'FP Start'])
   (...)
     43 
     44 ### Date Comparison Practice ###
---> 46 if loan_data['Start Date'] > fiscal_periods.loc[0,'FP Start']:
     47     loan_data['P1 Start'] = loan_data['Start Date']
     48 else:

File ~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/generic.py:1527, in NDFrame.__nonzero__(self)
   1525 @final
   1526 def __nonzero__(self):
-> 1527     raise ValueError(
   1528         f"The truth value of a {type(self).__name__} is ambiguous. "
   1529         "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
   1530     )

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Any suggestions on how this type of date formula can be done?

Thanks