how to return row values of a column such that their corresponding value in another column are the smallest n values, in R

user_16354132 0
Viewed 341

I have df below.

df<-cbind(c("LA", "NY", "Rome"),c(1,1,2),c(0,1,0),c(0,1,2),c(1,3,4))
     [city] [c1] [c2] [c3] [sum]
[1,] "LA"   "1"  "0"  "0"  "1" 
[2,] "NY"   "1"  "1"  "1"  "3" 
[3,] "Rome" "2"  "0"  "2"  "4"

I want to compare the values in sum column to find the two smallest values .( here 1,3 in increasing order)
then return the corresponding values in city column with the same order.
so what I want in out put is : LA, NY

I can get the rownames ( which I do not want, but I do not know how to get LA, NY:

rownames(df$city[order(df$sum, decreasing = F),][1:2])[1:2]
rowname dataframe max r matrix
4 Answers

The dataset in the question is a matrix (cbind returns a matrix by default and matrix can have only a single type. Thus, the whole dataset is converted to character). If we need to extract 'n' elements of first column based on the last column (5th column), extract the 5th column, convert to numeric, use order to get the index of values in ascending order, then with head get the n order index and use that to subset the first column

n <- 2
df[,1][head(order(as.numeric(df[,5])), n)]
[1] "LA" "NY"

as.data.frame(cbind is wrong way as the columns are already getting type changed to character. It is better to use data.frame directly

df1 <- data.frame(col1 = ..., col2 = .., col3 = ...)
user_37322710

By tidyverse style,

(df%>%
  as.data.frame %>%
  arrange(sum) %>%
  pull(city))[1:2]

[1] "LA" "NY"
user_167291750

Assuming a data.frame:

df <- data.frame(city = c("LA", "NY", "Rome"),
                 c1 = c(1,1,2),
                 c2 = c(0,1,0),
                 c3 = c(0,1,2),
                 sum = c(3,1,4))

We could use the %in% operator to extract a logical index of matches, and use that to subset your city variable:

df$city[ df$sum %in% sort(df$sum)[1:2] ]
[1] "LA" "NY"
user_158553900

You can use dplyr's filter, to extract only those rows in which sum is %in% the top (head) two sum values, then pull the city vector

library(dplyr)

df %>% filter(sum %in% head(sum, 2)) %>% pull(city)
[1] "LA" "NY"
user_139723330
Related