Get Column Name for specific value in each row in Python Pandas

Question

I have the below dataframe called df:

Id	Stage1	Stage2	Stage3
1	2022-02-01	2020-04-03	2022-06-07
---	------------	------------	-----------
2	2023-06-07	2020-03-01	2020-09-03
---	------------	------------	-----------
3	2023-02-04	2023-06-07	2022-06-07
---	------------	------------	-----------
4	2022-05-08	2023-09-01	2023-09-01

I need to calculate the max date for each ID and its respective Stage. So for Order 1,2,3 the Stages I need are Stage 3, Stage 1 and Stage 2 respectively. I started this process by calculating the max date in each row first with the below code:

df2 = df[['Stage1', 'Stage2', 'Stage3', 'Stage4', 'Stage5']]
lis = list(df2.max(axis=1))

lis variable has the max dates stored for each row. Now, with each max date, I need to get the Stage Name of that row.

The below code calculates the max Stage for the whole df and not row.

new_lis = []
for i in lis:
    new_lis.append(df.columns[df.isin([i]).any()])

How do I fix this? Output I need is "Stage 3", "Stage 1" and "Stage 2" for Order 1,2,3 respectively.

Ynjxsjmh · Accepted Answer · 2022-09-19 18:07:59Z

3

Let's try idxmax(axis=1)

out = (df.filter(like='Stage')
       .apply(pd.to_datetime)
       .idxmax(axis=1))

print(out)

0    Stage3
1    Stage1
2    Stage2
dtype: object

If your stage columns contain NaT for the whole row, you can drop this row

out = (df.filter(like='Stage')
       .apply(pd.to_datetime)
       .dropna(how='all')
       .idxmax(axis=1))

input dataframe

   Id      Stage1      Stage2      Stage3
0   1  2022-02-01  2020-04-03  2022-06-07
1   2  2023-06-07  2020-03-01  2020-09-03
2   3  2023-02-04  2023-06-07  2022-06-07
3   4         NaN         NaN         NaN
4   5         NaT  2023-06-07  2022-06-07

output dataframe, note the index 3 is dropped

0    Stage3
1    Stage1
2    Stage2
4    Stage2
dtype: object

edited Sep 19, 2022 at 18:07

answered Sep 19, 2022 at 17:23

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sriya TR Over a year ago

Thank you so much! For some Id's, I have null values for all Stages. For these rows, the code you provided I am getting this error: ValueError: attempt to get argmax of an empty sequence. How do I fix this?

Ynjxsjmh Over a year ago

@SriyaTR How is your null value like?

Sriya TR Over a year ago

Null values have this value "NaT"

Ynjxsjmh Over a year ago

@SriyaTR I don't think NaT will be the cause of your error. NaT is NaN for datetime. Consider provide the raw line.

Ynjxsjmh Over a year ago

@SriyaTR I understand the error, what do you expect to get for the whole NaT rows?

|

Collectives™ on Stack Overflow

Get Column Name for specific value in each row in Python Pandas

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related