Python: How to iterate over rows and apply function to create new columns

Question

variable    best m_value  g_value  e_value
       m    8      3       3       7
       g    5      5       5       9
       e    7      6       4       4
       m    3      7       8       2
       m    6      2       1       1
       e    7      6       4       2

This is a tiny mini version of my actual dataframe and I would like to make calculations per row. For each row I would like to subtract best from the column that starts with the variable value (so for example for the first row I would like to subtract best from m_value, as m is indicated in the variable column). Therefore, I have the following function, that subtracts best from the column that starts with the string in variable:

df_test['dif'] = df_test.apply(lambda row: (df_test.loc[row,'best']) - (df_test[df_test.columns[pd.Series(df_test.columns).str.startswith(df_test.loc[row,'variable'])]]), axis=0)

However I get the following error:

(u'None of [0    m\n1    g\n2    e\n3    m\n4    m\n5    e\n6    g\n7    e\nName: variable, dtype: object] are in the [index]', u'occurred at index variable')

How could I correctly apply the function?

example data:

df_test = pd.DataFrame()
df_test['variable']= ['m', 'g', 'e', 'm', 'm', 'e','g', 'e']
df_test['best'] = [8,5,7,3,6,7,8,9]
df_test['m_value']= [3,5,6,7,2,6,6,9]
df_test['g_value']= [3,5,4,8,1,4,7,2]
df_test['e_value']= [7,9,4,2,1,2,3,4]

Answer 1

You need not resort to row-wise pd.DataFrame.apply where calculations can be vectorised. Instead, you can use optimised methods available in Pandas. In this case, via pd.DataFrame.lookup:

df_test['dif'] = df_test['best'] - \
                 df_test.lookup(df_test.index, df_test.variable+'_value')

print(df_test)

  variable  best  m_value  g_value  e_value  lookup
0        m     8        3        3        7       5
1        g     5        5        5        9       0
2        e     7        6        4        4       3
3        m     3        7        8        2      -4
4        m     6        2        1        1       4
5        e     7        6        4        2       5
6        g     8        6        7        3       1
7        e     9        9        2        4       5