Get the Values from another column pandas-CodePudding

I have a dataframe like this:

  A     B     C    D   E
 a.1   b.1   c.1   1   nan
 a.2   b.2   c.2   2   nan
 a.3   b.3   c.3   1   nan

Column D has the value which signifies the column index of the row values for Column E. So row D1=1 means here E1 = B1-> means b.1. My output should look like below:

  A     B     C    D   E
 a.1   b.1   c.1   1   b.1
 a.2   b.2   c.2   2   c.2
 a.3   b.3   c.3   1   b.3

How can this be achieved efficiently in pandas ?

CodePudding user response：

You could use numpy advanced indexing:

df['E'] = df[['B','C']].to_numpy()[df.index, df['D'].factorize()[0]]

@piRSquared has a now deleted answer that works even better:

df['E'] = df.to_numpy()[df.index, df.D]

Output:

     A    B    C  D    E
0  a.1  b.1  c.1  1  b.1
1  a.2  b.2  c.2  2  c.2
2  a.3  b.3  c.3  1  b.3

CodePudding user response：

df.apply(lambda x: x.iloc[x.D], axis=1)
# 0    b.1
# 1    c.2
# 2    b.3
# dtype: object

Also check

df.assign(E = df.apply(lambda x: x.iloc[x.D], axis=1))