[SOLVED] Calculate Year wise age in Pandas

Issue

This Content is from Stack Overflow. Question asked by TheSarfaraz

Let’s say I have an Employees Table and yearly survey filled by each person.
I have to transform transactional data into prediction data year wise.

Available Data:

E_IDTestYearDateOfBirth
120101947-01-01
120111947-01-01
120121947-01-01
220101990-01-01
320111999-01-01
420111991-01-01
420121991-01-01
520101989-01-01
520111989-01-01
520121989-01-01
520131989-01-01

DataFrame I need:

E_IDYearAge
1201063
1201164
1201265
2201020
2201121
2201222
3201011
3201112
3201213
4201019
4201120
4201221
5201021
5201122
5201223

In the new df I need all employees, for all 3 years 2010, 2011, 2022 and their relevant ages in the year 2010, 2011, 2022 respectively.

How to achieve this? Since in the transactional data, I have records for some employees for some years and not for other years.



Solution

Since your employer Id E_ID is unique and the date of birth DateOfBirth is also unique you can groupby the employer id and get the date of birth.

For the aggregation functions in the TestYear you include a list with the years you want to extract the age and and in DateOfBirth you can aggregate with a list, since the values of the list are the same (identic date of birth) you get the first entry:

df = df.groupby('E_ID').agg({"TestYear": lambda x: [2010, 2011, 2012], 
                        'DateOfBirth': lambda x: list(x)[0]}).explode("TestYear")

df['DateOfBirth'] = pd.to_datetime(df['DateOfBirth'])

df['Age'] = df['TestYear'] - df['DateOfBirth'].dt.year

output

    TestYear    DateOfBirth     Age
E_ID            
1   2010    1947-01-01  63
1   2011    1947-01-01  64
1   2012    1947-01-01  65
2   2010    1990-01-01  20
2   2011    1990-01-01  21
2   2012    1990-01-01  22
3   2010    1999-01-01  11
3   2011    1999-01-01  12
3   2012    1999-01-01  13
4   2010    1991-01-01  19
4   2011    1991-01-01  20
4   2012    1991-01-01  21
5   2010    1989-01-01  21
5   2011    1989-01-01  22
5   2012    1989-01-01  23


This Question was asked in StackOverflow by TheSarfaraz and Answered by Lucas M. Uriarte It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?