How can I subtract specific values with different observations in the same Dataframe-CodePudding

So I have this dataframe with credit and debit values. It tells me that I bought a stock X for 5 and sold it for 6. How can I translate this subtration into a code? I just want to group the same stocks that were bought and subtract them from the amount that them was sold.

I tried the following, but I couldn't come up with a way to subtract the sale from the purchase. Thanks in advance for any help, tips or advice.

tiker <- c("stock1", "stock2", "stock3",
           "stock2", "stock1", "stock3",
           "stock3", "stock1", "stock1")

boughtSold <- c("sold", "bought", "sold",
               "sold", "sold", "bought",
               "bought", "sold", "bought")

price <- c(12, 14, 5,
           7, 14, 9,
           32, 21, 9)

stocks_df <- data_frame(tiker, boughtSold, price)

df1 <- stocks_df %>% 
  group_by(tiker, boughtSold) %>% 
  summarise(sum(price))

In this scenario it will show that stock1 was bought for 9 and sold for 47, how can I create a third line with profit or loss?

CodePudding user response：

You can do this:

stocks_df %>% 
  group_by(tiker) %>% 
  summarize(profit = sum(price[boughtSold == "sold"]) - sum(price[boughtSold=="bought"]))

Output:

  tiker  profit
  <chr>   <dbl>
1 stock1     38
2 stock2     -7
3 stock3    -36

I'm not sure what you final output should look like. One alternative options is something like this:

stocks_df %>% 
  group_by(tiker, boughtSold) %>% 
  summarise(price = sum(price), .groups = "drop") %>% 
  pivot_wider(id_cols = tiker,names_from = boughtSold, values_from = price) %>% 
  mutate(profit = sold - bought )

Output:

  tiker  bought  sold profit
  <chr>   <dbl> <dbl>  <dbl>
1 stock1      9    47     38
2 stock2     14     7     -7
3 stock3     41     5    -36