Looking up values from one dataframe in specific row of another dataframe

Question

I'm struggling with a bit of a complex (to me) lookup-type problem.

I have a dataframe df1 that looks like this:

    Grade  Value
0     A     16
1     B     12
2     C      5

And another dataframe (df2) where the values in one of the columns('Grade') from df1 forms the index:

     Tier 1  Tier 2  Tier 3
A      20      17      10
B      16      11       3
C       7       6       2

I've been trying to write a bit of code that for each row in df1, look ups the row corresponding with 'Grade' in df2, finds the smallest value in df2 greater than 'Value', and returns the name of that column.

E.g. for the second row of df1, it looks up the row with index 'B' in df2: 16 is the smallest value greater than 12, so it returns 'Tier 1'. Ideal output would be:

    Grade  Value  Tier
0     A     16    Tier 2 
1     B     12    Tier 1
2     C      5    Tier 2

My novice, downloaded-Python-last-week attempt so far has been as follows, which is throwing up all manner of errors and doesn't even try returning the column name. Sorry also about the micro-ness of the question: any help appreciated!

for i, row in input_df1.iterrows():
    Tier = np.argmin(df1['Value']<df2.loc[row,0:df2.shape[1]])

piRSquared · Accepted Answer · 2017-06-15 20:37:30Z

2

df2.loc[df1.Grade].eq(df1.Value, 0).idxmax(1)

answered Jun 15, 2017 at 20:37

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Collectives™ on Stack Overflow

Looking up values from one dataframe in specific row of another dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related