Mutate based on a minimum value in a column

General
1

Hello everyone!

I am working with a dataset that looks similar to this:

DataSet = data.frame(Item = c('A', 'B', 'C', "A", 'B', 'C', 'A', 'B', 'C'), 
                     Period = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
                     Produced = c(67,78,50,99,50,95,72, 75, 64),
                     Sold = c(56,52,45,88,33,91,47, 68, 42)) %>% 
  mutate(Surplus = (Produced - Sold))

So the resulting dataframe will be:

Item Period Produced Sold Surplus
A 1 67 56 11
B 1 78 52 26
C 1 50 45 5
A 2 99 88 11
B 2 50 33 17
C 2 95 91 4
A 3 72 47 25
B 3 75 68 7
C 3 64 42 22

That is, a company is producing and selling three items (A, B, and C) over three periods (Periods 1 - 3) .

From this, I wanna create another column called "MinSurplus" showing the minimum amount of surplus in each period (I reckon that I'll have to use a mutate function?). More specifically, considering that the smallest surplus amounts in period 1, 2, and 3 are 5, 4, and 7, respectively, I wanna create something like:

Item Period Produced Sold Surplus MinSurplus
A 1 67 56 11 5
B 1 78 52 26 5
C 1 50 45 5 5
A 2 99 88 11 4
B 2 50 33 17 4
C 2 95 91 4 4
A 3 72 47 25 7
B 3 75 68 7 7
C 3 64 42 22 7

But I am not sure how to approach this. Does anybody know how to do this? Just a note, the actual dataset I am using contains dozens more items and a few more periods. So the actual dataset is a lot bigger and slightly more complex than the example shown above. That being said, it would be great if your solution could be as general as possible. That is, it should not be too "rigid" that it can only be used to address this specific example. Again, I reckon that I'll have to use a mutate function, but if you know more efficient way to tackle this, please let me know!

Thank you!

2

Try this:

library(tidyverse)

DataSet %>% 
  group_by(Period) %>% 
  mutate(MinSurplus = min(Surplus)) %>% 
  ungroup()

# A tibble: 9 × 6
  Item  Period Produced  Sold Surplus MinSurplus
  <chr>  <dbl>    <dbl> <dbl>   <dbl>      <dbl>
1 A          1       67    56      11          5
2 B          1       78    52      26          5
3 C          1       50    45       5          5
4 A          2       99    88      11          4
5 B          2       50    33      17          4
6 C          2       95    91       4          4
7 A          3       72    47      25          7
8 B          3       75    68       7          7
9 C          3       64    42      22          7
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.