Create a new data frame column based on another data frame column values

user_9168000 0
Viewed 70

I have two data frames df1 and df2

     no  plan  current flag
0  abc1   249       30  Y/U
1  abc2   249       30    N
2  abc3   249       30  Y/D

and

      plan  offer
0     149     20
1     249     30
2     349     40

I want to put an extra column in df1 such that if df1['flag'] == 'Y/U' it will search the next higher number in df2['offer'] comparing df1['current']. Similarly, the same rule applies for a lower number, where the flag is Y/D. (Keep it as usual if the flag is N)

Expected output:

     no  plan  current flag   Pos
0  abc1   249       30  Y/U   40
1  abc2   249       30    N   30
2  abc3   249       30  Y/D   20

I tried to do it using apply.

df1['pos'] = (df1.apply(lambda x: next((z for (y, z) in zip(df2['plan'], df2['offer'])
                                            if y > x['plan'] if z > x['current']), None), axis=1))

But it is giving the result considering every cases 'Y/U'.

pandas dataframe python
1 Answers

Without using plan you can achieve the desired result like this. You can just use a list.

offers = df2['offer'].sort_values().tolist()

def assign_pos(row, offers):
    index = offers.index(row['current'])
    if row['flag'] == "N":
        row['pos'] = row['current']
    elif row['flag'] == 'Y/U':
        row['pos'] = offers[index + 1]
    elif row['flag'] == 'Y/D':
        row['pos'] = offers[index - 1]
    
    return row

df1 = df1.apply(assign_pos, args=[offers], axis=1)
user_109761820
Related