CODEWITHSUNDEEP
pythonpandas
user11357465
user11357465

Reputation:

Index dataframe based on row and col info in other dataframe

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

Upvotes: 1

Views: 53

Answers (1)

yatu
yatu

Reputation: 88236

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

Upvotes: 2

Related Questions