CODEWITHSUNDEEP
rdataframesubset
Kevin
Kevin

Reputation: 89

Subset data frame based on column values

I have a data frame consisting of the fluorescence read out of multiple cells tracked over time, for example:

Number=c(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4)
Fluorescence=c(9,10,20,30,8,11,21,31,6,12,22,32,7,13,23,33)
df = data.frame(Number, Fluorescence)

Which gets:

   Number Fluorescence
1       1            9
2       2           10
3       3           20
4       4           30
5       1            8
6       2           11
7       3           21
8       4           31
9       1            6
10      2           12
11      3           22
12      4           32
13      1            7
14      2           13
15      3           23
16      4           33

Number pertains to the cell number. What I want is to collate the fluorescence readout based on the cell number. The data.frame here has it counting 1-4, whereas really I want something like this:

   Number Fluorescence
1       1            9
2       1            8
3       1            6
4       1            7
5       2           10
6       2           11
7       2           12
8       2           13
9       3           20
10      3           21
11      3           22
12      3           23
13      4           30
14      4           31
15      4           32
16      4           33

Or even more ideal would be having columns based on Number, then respective cell fluorescence:

        1    2    3    4
1       9    10   20   30
2       8    11   21   31
3       6    12   22   32
4       7    13   23   33

I've used the which function to extract them one at a time:

Cell1=df[which(df[,1]==1),2]

But this would require me to write a line for each cell (of which there are hundreds).

Thank you for any help with this! Apologies that I'm still a bit of an R noob.

Upvotes: 0

Views: 1423

Answers (1)

Albert Zhao
Albert Zhao

Reputation: 106

How about this:

library(tidyr);library(data.table)
number <- c(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4)
fl <- c(9,10,20,30,8,11,21,31,6,12,22,32,7,13,23,33)
df <- data.table(number,fl)
df[, index:=1:.N, keyby=number]
df
    number fl index
 1:      1  9     1
 2:      1  8     2
 3:      1  6     3
 4:      1  7     4
 5:      2 10     1
 6:      2 11     2
 7:      2 12     3
 8:      2 13     4
 9:      3 20     1
10:      3 21     2
11:      3 22     3
12:      3 23     4
13:      4 30     1
14:      4 31     2
15:      4 32     3
16:      4 33     4

The index is added for the unique identifier in spread function from tidyr. Look this post for more information. 

spread(df,number,fl)
   index 1  2  3  4
1:     1 9 10 20 30
2:     2 8 11 21 31
3:     3 6 12 22 32
4:     4 7 13 23 33

Upvotes: 1

Related Questions