Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

use pandas to pick latest value from time based columns

I have a DataFrame like as shown below

ID_1,time_1,time_2       
1,21,0
1,31,5
1,0,0
1,21,100
1,21,21
2,202,0
2,310,
2,0,0
2,201,
2,210,
2,178,190

I would like to fetch the latest value which is from time_2 column.

However, whenever time_2 column has zero or empty, I would like to pick the value from time_1 column.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

If both time_1 and time_2 are zero, then we put just 0.

I was trying something like below

tdf['latest_value'] = tdf['time_2']
tdf['time_2'] = np.where((tdf['time_2']==0 | tdf['time_2'].isna()==True),tdf['time_1'],tdf['time_2'])

I expect my output to be like as shown below

enter image description here

>Solution :

Replace 0 values to missing values with replace missing values by another column:

tdf['latest_value'] = tdf['time_2'].replace(0, np.nan).fillna(tdf['time_1'])
print (tdf)
    ID_1  time_1  time_2  latest_value
0      1      21     0.0          21.0
1      1      31     5.0           5.0
2      1       0     0.0           0.0
3      1      21   100.0         100.0
4      1      21    21.0          21.0
5      2     202     0.0         202.0
6      2     310     NaN         310.0
7      2       0     0.0           0.0
8      2     201     NaN         201.0
9      2     210     NaN         210.0
10     2     178   190.0         190.0

Or if possible many columns first replace, forward filling missing values and select last column with replace missing values to 0:

c = ['time_1', 'time_2']
tdf['latest_value'] = tdf[c].replace(0, np.nan).ffill(axis=1).iloc[:, -1].fillna(0)
print (tdf)
    ID_1  time_1  time_2  latest_value
0      1      21     0.0          21.0
1      1      31     5.0           5.0
2      1       0     0.0           0.0
3      1      21   100.0         100.0
4      1      21    21.0          21.0
5      2     202     0.0         202.0
6      2     310     NaN         310.0
7      2       0     0.0           0.0
8      2     201     NaN         201.0
9      2     210     NaN         210.0
10     2     178   190.0         190.0
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading