Rui Barradas
2022-Aug-25 07:22 UTC
[R] Getting minimum value of a column according a factor column of a dataframe
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 at 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 >>> >>> >>> >> > >
javad bayat
2022-Aug-25 07:53 UTC
[R] Getting minimum value of a column according a factor column of a dataframe
Dear Rui; Thank you very much. Both of your codes worked correctly. Now I can see the whole row's value. But I found a problem in the results. When I run your codes, the results are shown in a sorted table. I do not know why the results have been sorted according to the "Code" column, smallest to largest. Is there any way to get the results like their order in the first data frame (bilan2)? I used your codes as follow:> bilan3 <- lapply(split(bilan2, bilan2$Code), \(x) x[which.min(x$Q),]) > bilan3 = data.frame(do.call(rbind, bilan3))Sincerely On Thu, Aug 25, 2022 at 11:52 AM Rui Barradas <ruipbarradas at sapo.pt> wrote:> 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 at 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 > >>> > >>> > >>> > >> > > > > >-- Best Regards Javad Bayat M.Sc. Environment Engineering Alternative Mail: bayat194 at yahoo.com [[alternative HTML version deleted]]
Bert Gunter
2022-Aug-25 17:26 UTC
[R] Getting minimum value of a column according a factor column of a dataframe
A slightly slicker solution making use of the handy by() function to avoid the lapply(split...) construction.> do.call(rbind,by(df1, df1$Code, \(x)x[which.min(x$Q),]))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 This of course ignores the issue of tied minima that Tim Ebert brought up. That would require a bit more finagling in the anonymous function code instead of which.min() . Cheers, Bert On Thu, Aug 25, 2022 at 12:22 AM Rui Barradas <ruipbarradas at sapo.pt> wrote:> > 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 at 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 > >>> > >>> > >>> > >> > > > > > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.