Index dataframe based on row and col info in other dataframe

Question

I want to try and select the correct index row and column based on information from another dataframe. I have a list of the correct index numbers ['index_nums'] in dataframe (df_one) and on the same rows is their names under ['names'], 'John','Sam','Peter', etc. These names are column headers in another dataframe (df_two). I want to say, from df_one, if 'index_nums' equals the index row in df_two, AND the 'names' equals the column header in df_two. Extract this particular value in a new column in df_one.

df_one:

    ['Index_nums']['Names']
           3        Sam
           4        John
           5        Steve

df_two:

     ['John']  ['Sam']   ['Steve']
 0     23.5      45.4      52.2
 1     33.3      9.9       10.2
 3     22.2      9.0       7.3
 4     12.5      15.5      15.4
 5     24.4      8.2       4.4
 6     15.5      25.1      1.9

Expected Output:

df_one:

 ['Index_nums'] ['Names'] ['new_col']
        3         Sam       9.0
        4         John      12.5
        5         Steve     4.4

yatu · Accepted Answer · 2019-05-08 13:35:52Z

2

You can use DataFrame.lookup, directly unpacking the columns of df_one, so that they are fed as arguments for lookup, which expects a sequence of row_labels and of col_labels respectively:

df_one['new_col'] = df_two.lookup(*df_one.values.T)

    Index_nums  Names  new_col
0           3    Sam      9.0
1           4   John     12.5
2           5  Steve      4.4

edited May 8, 2019 at 13:35

answered May 8, 2019 at 13:04

yatu

88.7k12 gold badges93 silver badges148 bronze badges

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

4 Comments

user11357465 Over a year ago

Thanks. Didn't even know about .lookup!

Erfan Over a year ago

Great answer, whats the function of the * in lookup?

yatu Over a year ago

Thanks @erfan. It is unpacking the two columns in df_one, so that they are fed as arguments of the lookup function, which expects a sequence of indices and one of columns respectively. Added some more details

Erfan Over a year ago

Btw not sure why, but I get different output when I run the same code for new_col

Collectives™ on Stack Overflow

Index dataframe based on row and col info in other dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related