In R, extract the value of column 1 where subsequent columns are max

apply, max, r

Solution

Convert your data into a "long" format and then use one of the many aggregation functions in R.

Here are two approaches with "data.table".

First, load the packages required.

library(data.table)
library(reshape2)

Option 1: Keep the data long--more flexible to work with later. (I would prefer this option.) From here, you can use `dcast.data.table` if you wanted to collapse this using `paste` or some other approach.

melt(as.data.table(df), id.vars = "Time")[, list(
  Time[value == max(value)]), by = variable]
#    variable  V1
# 1:    Cell1 0.3
# 2:    Cell2 0.4
# 3:    Cell3 0.2
# 4:    Cell3 0.4

Option 2: Store the results as a `list` column. Offers more flexibility to work with the data later than using `paste`, but not many people expect a column to be a `list`.

melt(as.data.table(df), id.vars = "Time")[, list(
  list(Time[value == max(value)])), by = variable]
#    variable      V1
# 1:    Cell1     0.3
# 2:    Cell2     0.4
# 3:    Cell3 0.2,0.4

Problem

I have a data.frame containing Time as the first column, then each subsequent column is the concentration of a transcription factor for individual cells, for example: ``` Time = c(0.1,0.2,0.3,0.4,0.5) Cell1 = c(1,5,10,4,2) Cell2 = c(1,5,4,11,5) Cell3 = c(1,9,5,9,5) df = data.frame(Time,Cell1,Cell2,Cell3) ``` Which gets: ``` Time Cell1 Cell2 Cell3 1 0.1 1 1 1 2 0.2 5 5 9 3 0.3 10 4 5 4 0.4 4 11 9 5 0.5 2 5 5 ``` Now, I'm trying to extract the Time at which each cell has the maximum concentration of transcription factor to output something like: ``` Cell1 0.3 Cell2 0.4 Cell3 0.2,0.4 ``` Apologies if this is simplistic, I'm new to R and have fumbled about forums for an answer for a while now. I can do it by inquiring about each column individually, but I have hundreds of cells and would have to write a script for each one with my current method: ``` cell1_peak=which(df[2]==max(df[2]));cell1_time=df$Time[cell1_peak] ``` Possible way to use the apply function with my current method and compile all cells for easy export?

Original source