Pandas DataFrame Filling missing values in a column
try doing:
def get_age(s):
present = s.age.notna().idxmax()
diff = s.loc[[present]].eval('age - Year').iat[0]
s['age'] = diff + s.Year
return s
df.groupby(['ID']).apply(get_age)
I think instead of trying to fill the values, find the year of birth instead.
df["age"] = df["Year"] - (df["Year"]-df["age"]).mean()
Or general solution with more than 1 id:
s = df.loc[df["age"].notnull()].groupby("ID").first()
df["age"] = df["Year"]-df["ID"].map(s["Year"]-s["age"])
print (df)
ID Year age
0 280165 1991 12.0
1 280165 1992 13.0
2 280165 1993 14.0
3 280165 1994 15.0
4 280165 1995 16.0
5 280165 1996 17.0
6 280165 1997 18.0
7 280165 1998 19.0
8 280165 1999 20.0
9 280165 2000 21.0
10 280165 2001 22.0
11 280165 2002 23.0
12 280165 2003 24.0
13 280165 2004 25.0
14 280165 2005 26.0
15 280165 2006 27.0
16 280165 2007 28.0
17 280165 2008 29.0
18 280165 2010 31.0
19 280165 2011 32.0
20 280165 2012 33.0
21 280165 2013 34.0
22 280165 2014 35.0
23 280165 2015 36.0
24 280165 2016 37.0
25 280165 2017 38.0