Pandas: Matching index of one dataframe to the column of other dataframe

Question

I have two data frames named df1 & df2:

df1

df2

SITE_NUMBER is set as the index of df1. Now I want to return the 'FACILITY' column of df1 if the values of 'S_STATION' of df2 matches with the index value of df1. I wrote a function for this purpose:

def return_facility():
   return df1[df1['SITE_NUMBER'] == pd.to_numeric(df2['S_STATION'])]['FACILITY']

However, the function is not working because compiler does not recognize df1['SITE_NUMBER']. Could anyone point out where I am making mistake?

SITE_NUMBER is the index of df1, not a column. You're referencing it like a column. — itzy
– itzy, Commented Oct 17, 2019 at 1:39
@vb_rises didn't work. Throw an error message "ValueError: index must be monotonic increasing or decreasing" — user2293224
– user2293224, Commented Oct 17, 2019 at 1:47
Better solution is to use pd.merge(df1, df2, left_index=True, right_on='S_STATION') and then extract desired columns. — vb_rises
– vb_rises, Commented Oct 17, 2019 at 1:56

Andy L. · Accepted Answer · 2019-10-17 02:04:12Z

0

You need isin. Use it on df1.index

def return_facility():
   return df1.loc[df1.index.isin(pd.to_numeric(df2['S_STATION'])), 'FACILITY']

answered Oct 17, 2019 at 2:04

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Pandas: Matching index of one dataframe to the column of other dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related