hello, I have the following data manipulation issue. the following is the sample data: value level 4 A 5 A 2 A 10 B 9 B 34 B 100 C 34 C 101 C. I hope to get the following result: value level rank 2 A 1 4 A 2 5 A 3 9 B 1 10 B 2 34 B 3 34 C 1 100 C 2 101 C. 3 as you may see, I need the data sorted and indexed within each level of "level". what do I need to do calculate the rank variable? thanks, -- View this message in context: http://r.789695.n4.nabble.com/how-to-add-row-index-based-a-categorical-column-tp3556126p3556126.html Sent from the R help mailing list archive at Nabble.com.
Hi, This seems to work (although I have this sense that I am missing something, but I cannot put my finger on it). There are undoubtedly other ways: ## data in a form ready for copy and pasting (created using dput() ) dat <- structure(list(value = c(4L, 5L, 2L, 10L, 9L, 34L, 100L, 34L, 101L), level = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L ), .Label = c("A", "B", "C"), class = "factor"), rank = c(2L, 3L, 1L, 2L, 1L, 3L, 2L, 1L, 3L)), row.names = c(NA, -9L), .Names = c("value", "level", "rank"), class = "data.frame") ## add a variable, 'rank' that does what you want dat[order(dat$value), "rank"] <- with(dat[order(dat$value), ], ave(value, level, FUN = seq_along)) HTH, Josh On Fri, May 27, 2011 at 12:12 PM, xin wei <xinwei at stat.psu.edu> wrote:> hello, I have the following data manipulation issue. the following is the > sample data: > > value level > 4 ? ? ? A > 5 ? ? ? A > 2 ? ? ? A > 10 ? ? B > 9 ? ? ? B > 34 ? ? B > 100 ? C > 34 ? ? C > 101 ? ?C. > > I hope to get the following result: > value level ?rank > 2 ? ? ? A ? ? ? 1 > 4 ? ? ? A ? ? ? 2 > 5 ? ? ? A ? ? ? 3 > 9 ? ? ? B ? ? ? 1 > 10 ? ? ?B ? ? ?2 > 34 ? ? ?B ? ? ?3 > 34 ? ? ?C ? ? ?1 > 100 ? ?C ? ? ? 2 > 101 ? ?C. ? ? ?3 > > as you may see, I need the data sorted and indexed within each level of > "level". what do I need to do calculate the rank variable? > > thanks, > > -- > View this message in context: http://r.789695.n4.nabble.com/how-to-add-row-index-based-a-categorical-column-tp3556126p3556126.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at r-project.org mailing list > 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. >-- Joshua Wiley Ph.D. Student, Health Psychology University of California, Los Angeles http://www.joshuawiley.com/
On May 27, 2011, at 3:12 PM, xin wei wrote:> hello, I have the following data manipulation issue. the following > is the > sample data: > > value level > 4 A > 5 A > 2 A > 10 B > 9 B > 34 B > 100 C > 34 C > 101 C. > > I hope to get the following result: > value level rank > 2 A 1 > 4 A 2 > 5 A 3 > 9 B 1 > 10 B 2 > 34 B 3 > 34 C 1 > 100 C 2 > 101 C. 3 >Probably something along these lines: d2 <- d1[order(d1$value, ] d2$rank <- ave(d2$value, d2$level, FUN=order)> as you may see, I need the data sorted and indexed within each level > of > "level". what do I need to do calculate the rank variable? > > thanks, > > -- > View this message in context: http://r.789695.n4.nabble.com/how-to-add-row-index-based-a-categorical-column-tp3556126p3556126.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at r-project.org mailing list > 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.David Winsemius, MD West Hartford, CT
Hi: Another angle. Calling your example data frame df, df <- transform(df, Rank = with(df, ave(value, level, FUN = rank))) df[with(df, order(level, value)), ] value level Rank 3 2 A 1 1 4 A 2 2 5 A 3 5 9 B 1 4 10 B 2 6 34 B 3 9 34 C 1 7 100 C 2 8 101 C 3 Note: If you try to use rank = blah instead of Rank = blah in transform(), an error will be thrown; another reason not to use the names of functions in the base package as variable names. Dennis On Fri, May 27, 2011 at 12:12 PM, xin wei <xinwei at stat.psu.edu> wrote:> hello, I have the following data manipulation issue. the following is the > sample data: > > value level > 4 ? ? ? A > 5 ? ? ? A > 2 ? ? ? A > 10 ? ? B > 9 ? ? ? B > 34 ? ? B > 100 ? C > 34 ? ? C > 101 ? ?C. > > I hope to get the following result: > value level ?rank > 2 ? ? ? A ? ? ? 1 > 4 ? ? ? A ? ? ? 2 > 5 ? ? ? A ? ? ? 3 > 9 ? ? ? B ? ? ? 1 > 10 ? ? ?B ? ? ?2 > 34 ? ? ?B ? ? ?3 > 34 ? ? ?C ? ? ?1 > 100 ? ?C ? ? ? 2 > 101 ? ?C. ? ? ?3 > > as you may see, I need the data sorted and indexed within each level of > "level". what do I need to do calculate the rank variable? > > thanks, > > -- > View this message in context: http://r.789695.n4.nabble.com/how-to-add-row-index-based-a-categorical-column-tp3556126p3556126.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at r-project.org mailing list > 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. >
thank you everyone. how can I not be aware of the existence of ave()? I try the following: tapply(data$value, data$level, rank). However, I have a very difficult time merging the resulting rank variable back to the original data frame. thanks a lot! -- View this message in context: http://r.789695.n4.nabble.com/how-to-add-row-index-based-a-categorical-column-tp3556126p3563627.html Sent from the R help mailing list archive at Nabble.com.