Calculate Stock-CodePudding

Is it possible calculated stock using R? The formula is stock purchase-sold. In this case first stock (row1) is 0, rg first result stockB1= 12 - 3 = 9 the second (row1) 9 0-5=4.

    df=read.table(text="
AB1 XB1 AB2 XB2 AB3 XB3
12  3   0   5   3   7
11  35  1   7   2   8
0   10  5   16  5   3",h=T)

stock = read.table(text="
AB1 XB1 STB1 AB2 XB2 STB2 AB3 XB3 STB3
12   3   9    5   3   11   3   7   7
11   35  24   1   7   18   2   8   12  
11   10  1    5   16 -10   5   3   -2",h=T)

Where STB is my request, I not need the total but the partial for each. It can be also in another dataframe.

CodePudding user response：

I'm not sure what form of output you're looking for, but here's an approach that assumes each PB column is a purchase and each SB column is a sale.

My first step is to track the original row for later. Then I reshape the data long, splitting the column into two components after the 2nd character, the first component being PB/SB, the second component being the number of trade. I count PB as increases and SB as reductions, and take the cumulative total for each transaction.

library(tidyverse)
df %>% 
  mutate(row = row_number()) %>%
  pivot_longer(-row, names_to = c("type", "num"), names_sep = 2) %>%
  mutate(net = value * if_else(type == "SB", -1, 1)) %>%
  group_by(row) %>%
  mutate(cuml = cumsum(net)) %>%
  ungroup()

## A tibble: 18 × 6
#     row type  num   value   net  cuml
#   <int> <chr> <chr> <int> <dbl> <dbl>
# 1     1 PB    1        12    12    12
# 2     1 SB    1         3    -3     9
# 3     1 PB    2         0     0     9
# 4     1 SB    2         5    -5     4
# 5     1 PB    3         3     3     7
# 6     1 SB    3         7    -7     0
# 7     2 PB    1        11    11    11
# 8     2 SB    1        35   -35   -24
# 9     2 PB    2         1     1   -23
#10     2 SB    2         7    -7   -30
#11     2 PB    3         2     2   -28
#12     2 SB    3         8    -8   -36
#13     3 PB    1        11    11    11
#14     3 SB    1        10   -10     1
#15     3 PB    2         5     5     6
#16     3 SB    2        16   -16   -10
#17     3 PB    3         5     5    -5
#18     3 SB    3         3    -3    -8

This is almost certainly not the final format you want, but we could use this to create a few outputs.

For instance, we might add

... %>%
  select(row, num, cuml) %>%
  pivot_wider(names_from = num, names_prefix = "trades_", values_from = cuml)

to get something like the original, but just showing the total stock after each pair of trades:

# A tibble: 3 × 4
    row trades_1 trades_2 trades_3
  <int>    <dbl>    <dbl>    <dbl>
1     1        9        4        0
2     2      -24      -30      -36
3     3        1      -10       -8

CodePudding user response：

library(tidyverse)
df=read.table(text="
PB1 SB1 PB2 SB2 PB3 SB3
12   3  0   5   3   7
11  35  1   7   2   8
11   10  5   16  5   3",h=T)
df %>% 
  rowwise() %>% 
  mutate(total = sum(c_across(everything())*c(1,-1)))
#> # A tibble: 3 × 7
#> # Rowwise: 
#>     PB1   SB1   PB2   SB2   PB3   SB3 total
#>   <int> <int> <int> <int> <int> <int> <dbl>
#> 1    12     3     0     5     3     7     0
#> 2    11    35     1     7     2     8   -36
#> 3    11    10     5    16     5     3    -8