Calculate age based on three dates

Question

I am trying to calculate the age of horses. All horses share the same birthday regardless of the date they were born - the 1st of August. For example, if a horse was born on 31st of July, it would still be classified as 1 yo despite only being one day old.

I have a dataframe with three columns: FoalDate (the day horse was born), MeetingDate (the day of race meeting), and Age. I am trying to calculate the horses age on a particular meeting date. In the example below, the horse would be a 2 yo because the 1st of August has only passed twice since its foal date.

Example:

Foal Date     MeetingDate   Age
20/11/2014    30/04/2017    2

I am trying to write code that would replace the 'Age' column (which is currently filled with 0s) with the correct answers.

score -1 · Answer 1 · answered Dec 14 '22 at 04:47

We can use the datetime library when using python.

Initially get the year from the Foal Date and then create a another datetime object for the Birth Date using that particular year. To make it more efficient we can consider an if else statement to check if the month >= august in order to decide on whether to consider that year or the previous year.

Then by finding the difference between the Birth Date and Meet Date the age can be found in days.

import datetime;

foalDate = datetime.datetime(2014,3,20)
foalMonth = foalDate.month
if foalMonth >= 8:
    foalYear = foalDate.year 
else:
    foalYear = foalDate.year - 1

birthDate = datetime.datetime(foalYear,8,1)
print(f'birthDate : {birthDate}')
meetDate = datetime.datetime(2017,4,30)
print(f'meetDate : {meetDate}')
age = meetDate - birthDate

print(f'Age is {(age.days)//365}')

You can run and check the code: https://www.online-python.com/GfIezdmbNr

Hope I answered your question.

score -1 · Accepted Answer · answered Dec 14 '22 at 04:52

Example

data = {'Foal Date': {0: '20/11/2014', 1: '31/07/2015', 2: '31/07/2015', 3: '31/08/2015'},
        'MeetingDate': {0: '30/04/2017', 1: '30/04/2017', 2: '01/08/2017', 3: '01/11/2015'}}
df = pd.DataFrame(data)

df

    Foal Date   MeetingDate
0   20/11/2014  30/04/2017
1   31/07/2015  30/04/2017
2   31/07/2015  01/08/2017
3   31/08/2015  01/11/2015

Code

# make dtype to datetime
df1 = df.apply(pd.to_datetime, format='%d/%m/%Y')

s1 = df1['Foal Date'].dt.month < 8
s2 = df1['MeetingDate'].dt.month >= 8
s_age = df1['MeetingDate'].dt.year.sub(df1['Foal Date'].dt.year).sub(1).add(s1).add(s2)

s_age

0    2
1    2
2    3
3    0
dtype: int64

make s_age to age column

df.assign(Age=s_age)

result:

    Foal Date   MeetingDate Age
0   2014-11-20  2017-04-30  2
1   2015-07-31  2017-04-30  2
2   2015-07-31  2017-08-01  3
3   2015-08-31  2015-11-01  0

Calculate age based on three dates

2 Answers2