Python - Create a column based on multiple other columns in a dataframe

Question

I want to create a new column that outputs ascending or descending depending on values in other columns

  Index Leg  Map Number
   0     AD  J1   1
   1     AD  J1   2
   2     AD  J1   3
   3     AD  J2   5
   4     AD  J2   3
   4     AF  J1   9
   5     AF  J1   6

So looking at this dataframe, I want to create a new column "updown" that is either ascending or descending depending based on the leg, map and number columns. Basically for every leg and map pairings, look at the number column in order to determine whether the numbers are ascending or descending....which will result in a dataframe like:

  Index Leg  Map Number Updown
   0     AD  J1   1     ascending
   1     AD  J1   2     ascending
   2     AD  J1   3     ascending
   3     AD  J2   5     descending
   4     AD  J2   3     descending
   4     AF  J1   9     descending
   5     AF  J1   6     descending

Any help will be appreciated

"For each leg, if the map is the same, look at the number column to determine the order..." How? What is the logic? — harpan
– harpan, Commented Aug 16, 2019 at 15:02
It is not clear to me how the input maps on the output. You probably should explain for the sample data here why you used ascending/descending. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 16, 2019 at 15:05
How do you determine for the first occurrence of the pairing? — harpan
– harpan, Commented Aug 16, 2019 at 15:07
@harvpan you determine by looking at each pairing in full not just the first occurrence — Alessandrini
– Alessandrini, Commented Aug 16, 2019 at 15:08

anky · Accepted Answer · 2019-08-16 15:21:12Z

1

IIUC, you need:

s=df.groupby(['Leg','Map'])['Number'].transform(lambda x: (x.diff()>0).any())

Or:

s=df.groupby(['Leg','Map'])['Number'].transform(lambda x: x.is_monotonic) #thanks Mark Wang
df['Updown']=np.where(s,'ascending','descending')
print(df)

   Index Leg Map  Number      Updown
0      0  AD  J1       1   ascending
1      1  AD  J1       2   ascending
2      2  AD  J1       3   ascending
3      3  AD  J2       5  descending
4      4  AD  J2       3  descending
5      4  AF  J1       9  descending
6      5  AF  J1       6  descending

edited Aug 16, 2019 at 15:21

answered Aug 16, 2019 at 15:07

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

1 Comment

Mark Wang Over a year ago

Just a quick comment, there's actually a is_monotonic_increasing method with groupby

Collectives™ on Stack Overflow

Python - Create a column based on multiple other columns in a dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related