0

I have a data frame that looks something like this:

df =

          AT       BE       DE       FR       NL       from     to
id
10365     2        4        6        12       4        DE       AT
10366     3        6        7        14       7        AT       FR
10370     4        8        8        16       10       BE       FR
10370     5        10       9        18       13       BE       FR
10380     6        12       10       20       16       NL       DE

What I would like to do is get another column with two values from the columns subtracted from each other, if it matches the from to values. I.e. the resulting data frame should be something like:

df_result =

          AT       BE       DE       FR       NL       from     to    result
id
10365     2        4        6        12       4        DE       AT    6-2 = 4
10366     3        6        7        14       7        AT       FR    3-14 = -11
10370     4        8        8        16       10       BE       FR    8-16 = -8
10370     5        10       9        18       13       BE       FR    10-18 = -8
10380     6        12       10       20       16       NL       DE    16-10 = 6

No need for the calculations of course.

Hence, first row with from = DE and to = AT it should subtract the value for AT in that row from the value in DE for the same row etc.

Denver Dang
  • 2,433
  • 3
  • 38
  • 68
  • You need to perform a `lookup` on both columns, then a simple subtraction. See duplicate for the lookup. – mozway Sep 12 '22 at 12:32

1 Answers1

0

Using Lambda

df['result'] = df.apply(lambda x: x[x['from']]-x[x['to']], axis=1)
R. Baraiya
  • 1,490
  • 1
  • 4
  • 17