I need to realize FIFO algorithm

I have a data frame like:

items = data.frame(
  Items = c('Item A', 'Item B','Item A','Item A','Item A','Item B','Item B','Item A','Item C'),
  Quantity = c(5,100,4,7,10,50,30,1,1000),
  BuySell = c('B','B','B','S','B','S','S','S','B'),
  Price = c(100,50,110,130,90,45,60,120,5)
)

items$Value = items$Quantity * items$Price

I need to group items by name and calculate the amount and value of balances using the FIFO method. When we sell an item, we sell first by date (table grouped by date of operation). And we need to calculate the value of items which weren't sold.

I need to get the result of movement items (buy & sell) with their value by FIFO (first in, first out)

In my example results have to be:

Item A:

 Quantity: 11 Value 1000 
Item B:
 Quantity: 20 Value 1000
Item C:
 Quantity: 1000 Value 5000

Solution

Another option using dplyr and tidyr is

library(dplyr)
library(tidyr)

items <- items %>% 
  group_by(Items) %>% 
  mutate(index = 1:n()) %>% 
  spread(BuySell, Quantity, fill = 0) %>% 
  arrange(Items, index) %>% 
  mutate(TotalStock = cumsum(B) - cumsum(S), 
         Sold = case_when(B == 0 ~ 0, # Nothing bought - cannot be sold
                          cumsum(B) < sum(S) ~ B, # Total items bought is smaller than total item sold - everything is sold
                          sum(S) < (cumsum(B) - B) ~ 0, # Total sum is smaller than total amount bought excluding the current buy - nothing sold
                          TRUE ~ B - (cumsum(B) - sum(S))), 
         InStock = B - Sold)

This gives the following data.frame

items
# A tibble: 9 x 9
# Groups:   Items [3]
#   Items  Price Value index     B     S TotalStock  Sold InStock
#   <fct>  <dbl> <dbl> <int> <dbl> <dbl>      <dbl> <dbl>   <dbl>
# 1 Item A   100   500     1     5     0          5     5       0
# 2 Item A   110   440     2     4     0          9     3       1
# 3 Item A   130   910     3     0     7          2     0       0
# 4 Item A    90   900     4    10     0         12     0      10
# 5 Item A   120   120     5     0     1         11     0       0
# 6 Item B    50  5000     1   100     0        100    80      20
# 7 Item B    45  2250     2     0    50         50     0       0
# 8 Item B    60  1800     3     0    30         20     0       0
# 9 Item C     5  5000     1  1000     0       1000     0    1000

and this can be summarized as

items %>% 
  summarize(Value = sum(InStock * Price), 
            TotalStock = sum(InStock))

# A tibble: 3 x 3
#   Items  Value TotalStock
#   <fct>  <dbl>      <dbl>
# 1 Item A  1010         11
# 2 Item B  1000         20
# 3 Item C  5000       1000