Need a way to return value from many columns and rows [duplicate]

Solution 1:

You can use melt() followed by merge() to do this.

Code Example:

df2_reformated = df2.melt(id_vars=['Qty'], var_name='Name', value_name='Rate')
df3 = df1.merge(df2_reformated)

Output:

  Name  Qty  Rate
0    A    1   1.0
1    B    3   1.3
2    C    5   2.2
3    A    6   2.0
4    D    1   1.4

This works by converting the df2 variable from 'wide' to 'long format. For example, the first few rows of the dataframe, after melting, would look like this:

    Qty Name  Rate
0     1    A   1.0
1     2    A   1.2
2     3    A   1.4

Once you have it in this format, you can use merge() to combine it with df1. merge() works by looking at the columns in common between two dataframes, and combining rows which match in those columns.

Solution 2:

Another way:

(i) create a MultiIndex from df1: idx

(ii) set_index of df2 to Qty and unstack. This creates a MultiIndex Series. Using idx filter the relevant rows:

idx = df1.set_index(['Name','Qty']).index
out = df2.set_index('Qty').unstack().loc[idx].reset_index().rename(columns={0:'Rate'})

Output:

  Name  Qty  Rate
0    A    1   1.0
1    B    3   1.3
2    C    5   2.2
3    A    6   2.0
4    D    1   1.4