Getting non null latest value in python pandas dataframe

Question

I want to get the latest non null value across all the variables. For example, in this data set, we have 3 service dates.

import pandas as pd
df =pd.DataFrame( {'PatientID': [1, 1, 1], 
'Date': ['01/01/2018', '01/15/2018','01/20/2018'],
'Height': ['Null', '178', 'Null'],
'Weight': ['Null', '182', '190'],
'O2 Level': ['95', '99', '92'],
'BPS': ['120', 'Null', 'Null'],
'DPS': ['80', 'Null', 'Null']})

Example Dataset

As an output I need something like this:

df = pd.DataFrame( {'PatientID': [1], 
'Height': ['178'],
'Weight': ['190'],
'O2 Level': ['92'],
'BPS': ['120'],
'DPS': ['80']})

Expected Output

My original data set has thousands of patients and 100+ covariates. Currently I am using a triple loop to achieve this task, which is very inefficient. I am looking for more efficient solutions.

Answer 1

I think you need first remove column Date, replace null to NaNs and then call groupby with last:

d = {'PatientID': [1, 1, 1], 
'Date': ['01/01/2018', '01/15/2018','01/20/2018'],
'Height': ['Null', '178', 'Null'],
'Weight': ['Null', '182', '190'],
'O2 Level': ['95', '99', '92'],
'BPS': ['120', 'Null', 'Null'],
'DPS': ['80', 'Null', 'Null']}
c = ['PatientID','Date','Height','Weight','O2 Level','BPS','DPS']
df = pd.DataFrame(d, columns=c)
print (df)
   PatientID        Date Height Weight O2 Level   BPS   DPS
0          1  01/01/2018   Null   Null       95   120    80
1          1  01/15/2018    178    182       99  Null  Null
2          1  01/20/2018   Null    190       92  Null  Null

---

print (df.drop('Date', 1).replace('Null', np.nan))
   PatientID Height Weight O2 Level  BPS  DPS
0          1    NaN    NaN       95  120   80
1          1    178    182       99  NaN  NaN
2          1    NaN    190       92  NaN  NaN

df = df.drop('Date', 1).replace('Null', np.nan).groupby('PatientID', as_index=False).last()
print (df)
   PatientID Height Weight O2 Level  BPS DPS
0          1    178    190       92  120  80