user9292 user9292 - 1 year ago 82
R Question

three min values for each column and more

The data I have contain pair-wise distance between different locations (x,y,z) and (a,b,c,d,e,f,g,h,i,j). See below:

set.seed(123)
x <- rnorm(10, 15,1)
y <- rnorm(10, 7,0.1)
z <- rnorm(10, 3,0.01)

distdat <- data.frame(x,y,z)

rownames(distdat) <- c("a", "b", "c", "d", "e", "f", "g", "h", "i", "j")


I need to create another data that include: 1) the column name, row name of the min, and the minimum three values for each column. So in total, the new data will contain
three column and nine rows. Here is the first rows:

col_name <- c("x", "x", "x")
row_name <- c("h", "g", "a")
min_val <- c(14.21208, 14.88804, 14.98797)

newdat <- data.frame(col_name, row_name, min_val)


Similarly, we need to repeat this for column y and z.

Answer Source

How about this:

set.seed(123)
x <- rnorm(10, 15,1)
y <- rnorm(10, 7,0.1)
z <- rnorm(10, 3,0.01)

distdat <- data.frame(x,y,z)

rownames(distdat) <- c("a", "b", "c", "d", "e", "f", "g", "h", "i", "j")

# find indices of smallest values
idx <- sapply(distdat, order)[1:3, ]

# put everything in a data.frame 
data.frame(col_name = rep(colnames(distdat), each = 3),
           row_name = row.names(distdat)[c(idx)],
           min_val = distdat[cbind(c(idx), rep(1:3, each = 3))]
)

Also, with the given seed I could not replicate your example, let me know if I missed something.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download