I am not entirely sure what type of problem this but here it is.
I have 2 data frames (Baskets and Stocks Unwind). My problem requires me to use the Stocks unwind table to locate the matching stock in the basket table and remove the exact number of units from the baskets that are available. I then need the output in a table. The way I need to do this by order of date. Let me use APPL as an example.
I need to remove 100 units of AAPL. I need to choose from my inventory of baskets where to remove AAPL from my first priority is to take from baskets with the earliest date. After the looping procedure I would get 2 tables. A remainder table with all the stock from the Unwind table that were unable to be removed and Basket Table indicating which baskets to unwind from and in which order. In AAPL example my remainder stock table would have 10 units of APPL in it and my basket table would print all three instances of AAPL as it is 90(10+50+30)<100. Note that I cannot Unwind more stock than I have in the Unwind table.
I am quite certain of problem like this exists I am just not sure the name of this type of problem or the correct tags. Any help would be appreciated
The basket table is as follows
BSKT | STOCK | Nominal| Date
-------------------------------
A | AAPL | 10 | 15/05/17
A | V | 25 | 15/05/17
B | MFC | 5 | 15/05/17
B | GOOG | 30 | 15/05/17
C | AAPL | 30 | 17/05/17
D | AAPL | 50 | 18/05/17
The stocks to unwind table is below
STOCK | Units
--------------
AAPL | 100
V | 15
GOOG | 20
MFC | 5
The resulting tables would be as follows
Basket to unwind
BSKT | STOCK | Nominal| Date
-------------------------------
A | AAPL | 10 | 15/05/17
A | V | 15 | 15/05/17
B | MFC | 5 | 15/05/17
B | GOOG | 20 | 15/05/17
C | AAPL | 30 | 17/05/17
D | AAPL | 50 | 18/05/17
Remaining to unwind Table
STOCK | Units
--------------
AAPL | 10
V | 0
GOOG | 0
MFC | 0
Here's one way to do that with dplyr:
basket <- read.table(text="BSKT STOCK Units Date
A AAPL 10 15/05/17
A V 25 15/05/17
B MFC 5 15/05/17
B GOOG 30 15/05/17
C AAPL 30 17/05/17
D AAPL 50 18/05/17",header=TRUE,stringsAsFactors=FALSE)
unwind <- read.table(text="STOCK Units
AAPL 100
V 15
GOOG 20
MFC 5",header=TRUE,stringsAsFactors=FALSE)
library(dplyr)
updated_basket <- left_join(unwind,basket,by="STOCK") %>%
group_by(STOCK) %>%
rowwise %>%
mutate(to_trade=ifelse(Units.x<=Units.y,
Units.x,
min(Units.y,Units.x-cumsum(Units.y)))) %>%
ungroup %>%
group_by(STOCK) %>%
mutate(left_to_trade=Units.x-cumsum(to_trade))
STOCK Units.x BSKT Units.y Date to_trade left_to_trade
<chr> <int> <chr> <int> <chr> <int> <int>
1 AAPL 100 A 10 15/05/17 10 90
2 AAPL 100 C 30 17/05/17 30 60
3 AAPL 100 D 50 18/05/17 50 10
4 V 15 A 25 15/05/17 15 0
5 GOOG 20 B 30 15/05/17 20 0
6 MFC 5 B 5 15/05/17 5 0
updated_unwind <- updated_basket %>%
ungroup %>%
group_by(STOCK) %>%
summarise(Units=max(Units.x)-sum(to_trade))
STOCK Units
<chr> <int>
1 AAPL 10
2 GOOG 0
3 MFC 0
4 V 0