How to find closest col name based on if row conditions in python dataframe?

Question

All IDs contain ranks from 1-100. Purpose is to understand the flow of ranking during dates.

Please Help!

1.I want to find the first date from the left if value is not 0 but close to 1.

2.I want to find last date for best rank from the right.

3.I want to find the best rank(minimum) in all dates.

Input

import datetime
d = {'ID': ["id1","id2"], '26-01-2021': [0, 15],'01-02-2021': [12, 17],'01-03-2021': [58, 17]}
df = pd.DataFrame(data=d)

ID  26-01-2021  01-02-2021  01-03-2021
id1     0         12          58
id2     15        17          17

Desired_output

ID  26-01-2021  01-02-2021  01-03-2021  first_date_rank last_date_rank  best_latest_date_rank   best_rank
id1   0              12           58    01-02-2021        01-03-2021            01-02-2021      12
id2  15              17           17    26-01-2021        01-03-2021            26-01-2021      15

I tried argmin but it doesn't work

def get_date(row):
    date_range = row[dft.columns[1:]]
    closest_value_key = abs(100 - date_range).argmin()
    closest_date = date_range[closest_value_key]
    column_name = date_range.keys()[closest_value_key]
    return pd.Series((closest_date, column_name))

dft[['best_latest_date_rank', 'best_rank']] = dft.apply(lambda row:get_date(row), axis=1)

Please help!

Answer 1

You can do idx max and idxmin here with some changes:

u = df.set_index("ID").replace(0,np.nan)
first_date_rank = u.idxmin(1)
last_date_rank= u.iloc[:,::-1].idxmax(1)
best_rank = u.min(1)

out = u.assign(first_date_rank=first_date_rank, last_date_rank=last_date_rank,
         best_latest_date_rank=first_date_rank,best_rank=best_rank).reset_index()

---

print(out)

    ID  26-01-2021  01-02-2021  01-03-2021 first_date_rank last_date_rank  \
0  id1         NaN          12          58      01-02-2021     01-03-2021   
1  id2        15.0          17          17      26-01-2021     01-03-2021   

  best_latest_date_rank  best_rank  
0            01-02-2021       12.0  
1            26-01-2021       15.0