[R] Getting minimum value of a column according a factor column of a dataframe

[Rui Barradas]

Hello,

OK, what about


res <- lapply(split(df1, df1$Code), \(x) x[which.min(x$Q),])
do.call(rbind, res)
#         Code  Y  M  D     Q    N    O
#  41003 41003 81  1 19 0.160 7.17 2.50
#  41005 41005 79  8 17 0.210 5.50 7.20
#  41009 41009 79  2 21 0.218 5.56 4.04
#  41017 41017 79 10 20 0.240 5.30 7.10


A dplyr solution.



suppressPackageStartupMessages(library(dplyr))

df1 %>%
   group_by(Code) %>%
   slice_min(Q) %>%
   slice_head(n = 1)
#  # A tibble: 4 × 7
#  # Groups:   Code [4]
#    Code      Y     M     D     Q     N     O
#    <fct> <int> <int> <int> <dbl> <dbl> <dbl>
#  1 41003    81     1    19 0.16   7.17  2.5
#  2 41005    79     8    17 0.21   5.5   7.2
#  3 41009    79     2    21 0.218  5.56  4.04
#  4 41017    79    10    20 0.24   5.3   7.1



Hope this helps,

Rui Barradas


Às 05:56 de 25/08/2022, javad bayat escreveu:
> Dear all,
> Many thanks for your suggested methods and codes, but unfortunately they
> did not give the desired results.
> All the codes you have provided are correct but they did not represent the
> whole row which is related to the minimum of "Q".
> The code must result in 4 rows, with the minimum value of "Q" and other
> column values, as below:
> 
>         Code
> 
>                Y
> 
>                M
> 
>                 D
> 
>             Q
> 
>              N
> 
>               O
> 
> 41003
> 
> 81
> 
> 1
> 
> 19
> 
> 0.16
> 
> 7.17
> 
> 2.5
> 
> 41005
> 
> 79
> 
> 8
> 
> 17
> 
> 0.21
> 
> 5.5
> 
> 7.2
> 
> 41009
> 
> 79
> 
> 2
> 
> 21
> 
> 0.218
> 
> 5.56
> 
> 4.04
> 41017 79 10 20 0.24 5.3 7.1
> 
> 
> 
> 
> 
> 
> Sincerely
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 41017 79 10 20 0.24 5.3 7.1
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> On Wed, Aug 24, 2022 at 9:24 PM Rui Barradas <ruipbarradas using sapo.pt> wrote:
> 
>> Hello,
>>
>> Here are two options, the 1st outputs a vector, the 2nd a data.frame.
>>
>>
>> x<-'41003 81 1 19 0.16 7.17 2.5
>> 41003 77 9 22 0.197 6.8 2.2
>> 41003 79 7 28 0.21 4.7 6.2
>> 41005 79 8 17 0.21 5.5 7.2
>> 41005 80 10 30 0.21 6.84 2.6
>> 41005 80 12 20 0.21 6.84 2.4
>> 41005 79 6 14 0.217 5.61 3.55
>> 41009 79 2 21 0.218 5.56 4.04
>> 41009 79 5 27 0.218 6.4 3.12
>> 41009 80 11 29 0.22 6.84 2.8
>> 41009 78 5 28 0.232 6 3.2
>> 41009 81 8 20 0.233 6.39 1.6
>> 41009 79 9 30 0.24 5.6 7.5
>> 41017 79 10 20 0.24 5.3 7.1
>> 41017 80 7 30 0.24 6.73 2.6'
>> df1 <- read.table(textConnection(x))
>> names(df1) <- scan(what = character(),
>>                      text = 'Code Y M D Q N O')
>> df1$Code <- factor(df1$Code)
>>
>> # 1st option
>> with(df1, tapply(Q, Code, min))
>> #  41003 41005 41009 41017
>> #  0.160 0.210 0.218 0.240
>>
>> # 2nd option
>> aggregate(Q ~ Code, df1, min)
>> #     Code     Q
>> #  1 41003 0.160
>> #  2 41005 0.210
>> #  3 41009 0.218
>> #  4 41017 0.240
>>
>>
>> Hope this helps,
>>
>> Rui Barradas
>>
>> Às 08:44 de 24/08/2022, javad bayat escreveu:
>>> Dear all;
>>> I am trying to get the minimum value of a column based on a factor column
>>> of the same data frame. My data frame is like the below:
>>>          Code               Y               M                D
>>>    Q
>>>        N              O
>>> 41003 81 1 19 0.16 7.17 2.5
>>> 41003 77 9 22 0.197 6.8 2.2
>>> 41003 79 7 28 0.21 4.7 6.2
>>> 41005 79 8 17 0.21 5.5 7.2
>>> 41005 80 10 30 0.21 6.84 2.6
>>> 41005 80 12 20 0.21 6.84 2.4
>>> 41005 79 6 14 0.217 5.61 3.55
>>> 41009 79 2 21 0.218 5.56 4.04
>>> 41009 79 5 27 0.218 6.4 3.12
>>> 41009 80 11 29 0.22 6.84 2.8
>>> 41009 78 5 28 0.232 6 3.2
>>> 41009 81 8 20 0.233 6.39 1.6
>>> 41009 79 9 30 0.24 5.6 7.5
>>> 41017 79 10 20 0.24 5.3 7.1
>>> 41017 80 7 30 0.24 6.73 2.6
>>>
>>> I want to get the minimum value of the "Q" column with the whole row
>>> values, according to the "Code"  column  which is a factor. Overall it
>> will
>>> give me 4 rows, with the value of "Q". Below is a code that I used but it
>>> did not give me what I wanted.
>>>
>>>> x[which(x$Q == min(x$Q)),]
>>>
>>> Sincerely
>>>
>>>
>>>
>>
> 
>