Issue with matching dates between dataframes in python

Question

I am trying to write a code that will tell me when an animal ate bamboo at least 1 out of the 3 days before a fecal sample was collected from them. I have written the following loop to iterate row by row in a list of fecal samples and do the following:

Put the animal group and date of sample collection into their own variables
Calculate the dates of 1 day, 2 days, and 3 days before sample collection and put them in variables.
Go to a new dataframe called 'bamboodays' that lists the dates that each animal group ate bamboo and see if there are any rows that match the group and previous three days before sample collection.
If the group ate bamboo any of the three days before sample collection, 1 is added to a counter. (So if the animal ate bamboo 2 out of the three days, the counter will be 2).

The issue I am having is that the variables fecalsamples['dayone'], fecalsamples['day two'], and fecalsamples['daythree'] are all coming back as NaN when they should be storing TRUE or FALSE. After some troubleshooting it seems like there is an issue with dates and matching the bamboo eating dates to the 'days before sample collection' dates. I tried to correct for a type() difference using multiple methods, two of which are shown in the code, but nothing seems to be working. If anyone has any insights I would really appreciate it!

Thank you!

fecalsamples['bamboocount'] = 0

#iterate through fecalsamples dataframe
for index, row in fecalsamples.iterrows(): 
    
    #create group and date variables from the fecalsamples DF
    group = fecalsamples['group']
    date = fecalsamples['collectdate']
    #Creates three variables, for the previous 3 days from the sample collection date, indexed by fecalsamples sample number
        #use datetime to subtract 1 day 
    prev_date = date - timedelta(days=1) 
        #Put into correct format and convert to string 
    prev_date = prev_date[index].strftime("%Y-%m-%d %H:%M:%S")
        #only put the y-m-d into variable 
    prev_date = str(prev_date).split(" ")[0]
        #place this variable in the dataframe, as a check to make sure it is working 
    fecalsamples.iloc[index, 15] = prev_date
        #repeat with two days before sample collection 
    prev2_date = date - timedelta(days=2)
    prev2_date = prev2_date[index].strftime("%Y-%m-%d %H:%M:%S")
    prev2_date = str(prev2_date).split(" ")[0]
    fecalsamples.iloc[index, 16] = prev2_date
        #repeat with three days before sample collection 
    prev3_date = date - timedelta(days=3)
    prev3_date = prev3_date[index].strftime("%Y-%m-%d %H:%M:%S")
    prev3_date = str(prev3_date).split(" ")[0]
    fecalsamples.iloc[index, 17] = prev3_date
    #Check if there are any rows in bamboo dataframe that match the date and group, if so put TRUE in variable 
    fecalsamples['dayone'] = (bamboodays[(((bamboodays['DATE'].to_string()) == prev_date) & (bamboodays['GROUP'] == str(group)))]).any()
    fecalsamples['daytwo'] = (bamboodays[(((bamboodays['DATE']) == prev2_date) & (bamboodays['GROUP'] == str(group)))]).any()
    fecalsamples['daythree'] = (bamboodays[(((bamboodays['DATE']) == prev3_date) & (bamboodays['GROUP'] == str(group)))]).any()
    
    #If the animal ate bamboo on any of the three days before sample collection, add one to bamboo count 
    
    if fecalsamples['dayone'] [index] == "TRUE": 
        
        fecalsamples.iloc[index, 11] += 1
       
    if fecalsamples['daytwo'] [index] == "TRUE": 
        
        fecalsamples.iloc[index, 11] += 1
        
    if fecalsamples['daythree'] [index] == "TRUE": 
        
        fecalsamples.iloc[index, 11] += 1

#bamboodays initial data 
    GROUP   DATE
0   UGE 2011-01-04
1   TIT 2011-01-05
2   UGE 2011-01-05
3   UGE 2011-01-06
4   BWE 2011-01-07
... ... ...
1994    NTA 2016-12-28
1995    TIT 2016-12-28
1996    NTA 2016-12-29
1997    NTA 2016-12-30
1998    NTA 2016-12-31

#fecalsamples initial data 

number  sampleno    group   animal  sex   baddate   collectime  ageatsample sampleweight    missingbox  collectdate bamboocount date1   date2   date3   datecheck   datecheck2  datecheck3
0   1   1   INS TAR 1.0 4/14/2011   13:32   11.868583   0.48    NaN 2011-04-14  0                       
1   2   2   INS SHA 1.0 4/14/2011   13:40   30.532513   0.48    NaN 2011-04-14  0                       
2   3   3   INS TAY 1.0 4/14/2011   13:46   8.498289    0.49    NaN 2011-04-14  0                       
3   4   4   PAB MFU 0.0 4/6/2011    10:23   2.420260    0.49    NaN 2011-04-06  0                       
4   5   5   INS UMW 1.0 4/13/2011   11:10   27.865845   0.51    NaN 2011-04-13  0                       
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
14698   14699   14711   IYA IYA 0.0 1/15/2018   12:20   17.095140   0.52    NaN 2018-01-15  0                       
14699   14700   14712   NTA TBK 0.0 1/15/2018   14:02   19.624914   0.51    NaN 2018-01-15  0                       
14700   14701   14713   NTA KBZ 0.0 1/15/2018   12:08   12.533881   0.50    NaN 2018-01-15  0                       
14701   14702   14714   ISA KEZ 1.0 1/17/2018   11:12   8.832307    0.52    NaN 2018-01-17  0                       
14702   14703   14715   MAF PAS 1.0 1/19/2018   13:47   26.803558   0.52    NaN 2018-01-19  0

Issue with matching dates between dataframes in python

0 Answers