How to Match multiple columns with given single column name and get its value in new column?

user_14391779 0
Viewed 25

I have previously asked a question How to Match multiple columns with given single column and get its name in new column?

But now i want to get data from column name :

here is demo data:

df = pd.DataFrame({"mtc": [1, 2, 3, 4],
                     "C1": [0.5, 2, 3.5, 5],
                    "C2": [1, 3, 2.2, 5],
                    "C3": [1, 3, 2.9, 5],
                    "C4": [4.1,6, 6, 6]})

d=df[['C1', 'C2', 'C3', 'C4']]
c=(d.div(df.mtc,axis=0)-1)
m = c.ge(-0.15) & c.le(0.15)
df['Near'] = m.idxmax(1).mask(~m.any(1))

df>>
   mtc  C1  C2  C3  C4  Near
0   1   0.5 1.0 1.0 4.1 C2
1   2   2.0 3.0 3.0 6.0 C1
2   3   3.5 2.2 2.9 6.0 C3
3   4   5.0 5.0 5.0 6.0 NaN

Now i want the real values from corresponding columns by looking at column Near as below:

    mtc C1  C2  C3  C4  Near    Realvalues
0   1   0.5 1.0 1.0 4.1 C2      1.0
1   2   2.0 3.0 3.0 6.0 C1      2.0
2   3   3.5 2.2 2.9 6.0 C3      2.9
3   4   5.0 5.0 5.0 6.0 NaN     NaN

so far i got it using df.lookup(df.index, df.Near) but it fails at Nan also lookup is deprecated

pandas python
1 Answers

Try with , notice we used to have lookup, but pandas will no longer support this function sad ..

df['value'] = df.values[df.index,df.columns.get_indexer(df.Near)]
df
Out[27]: 
   mtc   C1   C2   C3   C4 Near value
0    1  0.5  1.0  1.0  4.1   C2     1
1    2  2.0  3.0  3.0  6.0   C1     2
2    3  3.5  2.2  2.9  6.0   C3   2.9
3    4  5.0  5.0  5.0  6.0  NaN   NaN
user_79645270
Related