How to get data in a column whose name is stored in another column in python

Question

• I have a dataframe:

  import pandas as pd
  data = {'Alpha': [1, 2, 3, 4, 5], 'Beta':[6, 7 ,8, 9, 10],
  'Delta': [11, 12, 13, 14, 15], 'Gamma': [16, 17, 18, 19, 20]}
  df = pd.DataFrame(data, index = ['2010', '2011', '2012', '2013', '2014'])
  df
  

2. It also has a Select column containing other column names.

   df['Select'] = ['Beta', 'Gamma', 'Gamma', 'Delta', 'Alpha']
   df
   

3. I need to add a column, such as Select, where the data will be retrieved from columns whose names are stored in the Select column, as shown below:

   df['Choice'] = [0,0,0,0,0]
   row = df['Select'].values
   for i in range(len(row)):
       df['Choice'][i] = df[row[i]][i]
   df
   

4. I do this using a loop, but maybe there is an even more elegant way? Thank!

Answer 1

we can use DataFrame.lookup () :

In [49]: df['new'] = df.lookup(df.index, df.Select)

In [50]: df
Out[50]:
      Alpha  Beta  Delta  Gamma Select  Choice  new
2010      1     6     11     16   Beta       6    6
2011      2     7     12     17  Gamma      17   17
2012      3     8     13     18  Gamma      18   18
2013      4     9     14     19  Delta      14   14
2014      5    10     15     20  Alpha       5    5