Problem when applying a function on a pandas dataframe column

Question

I have these two dataframes :

df = pd.DataFrame({'Points' : ['A','B','C','D','E'],'ColY' : [1,2,3,4,5]})
df
    Points  ColY
0       A      1
1       B      2
2       C      3
3       D      4
4       E      5

df2 = pd.DataFrame({'Points' : ['A','D'],'ColX' : [2,9]})
df2
    Points  ColX
0       A      2
1       D      9

And these two functions :

# equivalent of the Excel vlookup function applied to a dataframe
def vlookup(df,ref,col_ref,col_goal):
    return pd.DataFrame(df[df.apply(lambda x: ref == x[col_ref],axis=1)][col_goal]).iloc[0,0]

# if x is in column Points of df2, return what is in column ColX in the same row
def update_if_belong_to_df2(x):
    if x in df2['Points']:
        return vlookup(df2,x,'Points','ColX')
    return x

I would like to apply the function update_if_belong_to_df2 to the column ColY of df. I tried the following but it doesn't work :

df['ColY'] = df['ColY'].apply(lambda x : update_if_belong_to_df2(x))

I would like to get :

df
    Points  ColY
0       A      2
1       B      2
2       C      3
3       D      9
4       E      5

Could you please help me to understand why ? Thanks

BENY · Accepted Answer · 2019-12-02 15:21:16Z

3

I will do merge

df=df.merge(df2,how='left')
df.ColX=df.ColX.fillna(df.ColY)
df
  Points  ColY  ColX
0      A     1   2.0
1      B     2   2.0
2      C     3   3.0
3      D     4   9.0
4      E     5   5.0

answered Dec 2, 2019 at 15:21

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

Ewdlam Over a year ago

Thanks for this solution !

Quang Hoang · Accepted Answer · 2019-12-02 15:12:30Z

2

IIUC, your problem is easier with map and fillna:

df['ColY'] = (df['Points'].map(df2.set_index('Points')['ColX'])
                   .fillna(df['ColY'])
              )

Output:

  Points  ColY
0      A   2.0
1      B   2.0
2      C   3.0
3      D   9.0
4      E   5.0

answered Dec 2, 2019 at 15:12

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

Ewdlam Over a year ago

Thanks for your help ! Your approach is indeed much better than what I thought

mcsoini · Accepted Answer · 2019-12-02 15:13:48Z

2

Use pandas update instead:

df = pd.DataFrame({'Points' : ['A','B','C','D','E'],'ColY' : [1,2,3,4,5]})
df2 = pd.DataFrame({'Points' : ['A','D'],'ColX' : [2,9]})

df = df.set_index('Points')
df.update(df2.set_index('Points').rename(columns={'ColX': 'ColY'}))

df.reset_index()

  Points  ColY
0      A   2.0
1      B   2.0
2      C   3.0
3      D   9.0
4      E   5.0

answered Dec 2, 2019 at 15:13

mcsoini

6,7922 gold badges21 silver badges43 bronze badges

Collectives™ on Stack Overflow

Problem when applying a function on a pandas dataframe column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related