Get column index nr from value in other column

user_13073029 0
Viewed 95

I'm relativly new to python and pandas, so I might not have the full understanding of all possibilities and would appreciate a hint how to solve the following problem:

I have a df like this one:

    Jan  Feb  Mar  Apr  i    j
a   100  200  250  100  1  0.3
b   120  130   90  100  3  0.7
c    10   30   10   20  2 0.25

I want to construct a column which takes the column with the index according to df['i'] and then multiplies the value in the selected column with the value in df['j']. I want to bulid a table like this (df['k'] beeing the column constructed):

    Jan  Feb  Mar  Apr  i    j    k
a   100  200  250  100  1  0.3   60
b   120  130   90  100  3  0.7   70
c    10   30   10   20  2 0.25  2.5

(row a df['k']=200*0.3 (df['Feb']*df['j']), in row b df['k']=100*0.7 (df['Apr']*df['j']) and in row c df['k']=10*0.25(df['Mar']*df['j']))

The value in df['i'] will always be an integer value, so i would love to use the position of a column according to the value in df['i'].

pandas dataframe numpy python
3 Answers

IIUC, DataFrame.rename and then we can use DataFrame.lookup to map. Finally we use Series.mul 

df['k'] = df['j'].mul(df.rename(columns = dict(zip(df.columns,
                                                   range(len(df.columns)))))
                        .lookup(df.index, df['i']))
print(df)

Output

   Jan  Feb  Mar  Apr  i     j     k
a  100  200  250  100  1  0.30  60.0
b  120  130   90  100  3  0.70  70.0
c   10   30   10   20  2  0.25   2.5

Alternative:

df['j'].mul(df.iloc[:,df['i']].lookup(df.index, 
                                      df['i'].map(dict(zip(range(len(df.columns)),
                                                           df.columns)))))
user_118842370

I have a feeling that there must be a better way, but you could use itertuples like this :

list_k = []
for row in df.itertuples():
  month = row[int(row[5]+1)] # Tuple indexing
  j = row[6]
  list_k.append(month * j)

df['k'] = list_k
user_152675090

Another option to the solutions provided : 

   #convert column i to a list
     vals = df.i.tolist()

   #get the number indices for the dataframe
  num_indices = [df.index.get_loc(ind) for ind in df.index]
   # or df.index.get_indexer(df.index)

   #create a pair of the indices and vals
    paired = list(zip(num_indices,vals))

  #calculate column k by multiplying each extract with column j
   df['k'] = [df.iloc[entry] for entry in paired] * df.j

    Jan Feb Mar Apr i   j         k
a   100 200 250 100 1   0.30    60.0
b   120 130 90  100 3   0.70    70.0
c   10  30  10  20  2   0.25    2.5

Update : @ansev is right, loop is unnecessary : 

#get the column labels that correspond with the values in column i:
 col_labels = df.columns[df.i]
 #get the values from each column using pandas' lookup:
 result = df.lookup(df.index, col_labels)
 #multiply the array with column j:
 df['k'] = result * df.j
 #u can compress this in one line, but i believe breaking it down
 #allows for readability : 
 #df = df.assign(k = df.lookup(df.index,df.columns[df.i])*df.j)

    Jan Feb Mar Apr i   j        k
a   100 200 250 100 1   0.30    60.0
b   120 130 90  100 3   0.70    70.0
c   10  30  10  20  2   0.25    2.5
user_71757130
Related