1

I have the following data:

dataset

library(tidyverse)
df <- tribble(~SwapID, ~SwapLegID, ~LegPayerID, ~ CurrencyID, ~NotionalAmount,
              'AB-D-0001', 1, 'AB',  'CAD',  250000000,
              'AB-D-0001', 2, 'BMO', 'CAD',  250000000,
              'AB-D-0002', 1, 'AB',  'CAD',  250000000,
              'AB-D-0002', 2, 'RBC', 'CAD',  250000000,
              'AB-D-0004', 1, 'AB',  'CAD',  250000000,
              'AB-D-0004', 2, 'TD',  'USD',  250000000,
              'AB-D-0005', 1, 'BMO', 'USD',  416666668,
              'AB-D-0005', 2, 'AB',  'CAD',  548750002)

I need to find a way to divide "NotionalAmount" with each other for each respective "SwapID" and "SwapLegID".

Therefore, for "SwapID" AB-D-0005, I want to divide 548750002 by 416666668 to get 1.317 and assign this data element a new column.

FXrateSwap<-SwapLegs%>%

arrange(SwapID,SwapLegID)%>%

select(SwapID,SwapLegID,LegPayerID,CurrencyID,NotionalAmount)
Tony Ladson
  • 3,539
  • 1
  • 23
  • 30
Kal Souleiman
  • 11
  • 1
  • 1
    Can you provide a reproducible example ? You can use `dput(data)` or any means [here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – cbo Jun 25 '19 at 20:29
  • Will the data always have two per *SwapID* and *SwapLegID* pairings? – Parfait Jun 25 '19 at 20:41
  • Is SwapID always : ``SAME-SAME`` and SwapLegID : ``1-2`` ? Edit : kinda like Parfait asked. – Gainz Jun 25 '19 at 20:43
  • 1
    [You should not post code or sample data as an image because...](https://meta.stackoverflow.com/a/285557/1422451) – Parfait Jun 25 '19 at 20:43
  • Also, since the currency isn't always the same, wouldn't doing CAD / USD change the ratio meaning? Because sometimes you will get CAD/CAD but CAD/USD and USD/CAD will happen too. – Gainz Jun 25 '19 at 20:49
  • I ran dput(data) and it generated a large output with 1,374 rows of data, class is defined as a data.frame. I was thinking of creating an if statement that identifies each swap and assigns the proper cells for the calculation. Thoughts? – Kal Souleiman Jun 25 '19 at 20:57
  • As for the SwapID, each SwapID will always have two pairings, hence two SwapLegIDs – Kal Souleiman Jun 25 '19 at 20:58

2 Answers2

1

Though you should consider the above concerns, technically this is what I think you'd want.

want = FXrateSwap %>% 
   group_by(SwapID) %>% 
   summarize(ratio_want = sum(NotionalAmount[SwapLegID==1])/sum(NotionalAmount)
spazznolo
  • 747
  • 3
  • 9
0

Try this

library(tidyverse)

df %>% 
  group_by(SwapID) %>% 
  mutate(ratio = NotionalAmount[SwapLegID == 2]/NotionalAmount[SwapLegID == 1])
Tony Ladson
  • 3,539
  • 1
  • 23
  • 30