Skip to content

Conditional editing of rows in a data frame

3 messages · Irene Gallego Romero, David Winsemius, Gabor Grothendieck

#
Dear R users,

I have a dataframe (main.table) with ~30,000 rows and 6 columns, of
which here are a few rows:

      id chr window         gene     xp.norm    xp.top
129 1_32   1     32       TAS1R1  1.28882115     FALSE
130 1_32   1     32       ZBTB48  1.28882115     FALSE
131 1_32   1     32       KLHL21  1.28882115     FALSE
132 1_32   1     32        PHF13  1.28882115     FALSE
133 1_33   1     33        PHF13  1.02727430     FALSE
134 1_33   1     33        THAP3  1.02727430     FALSE
135 1_33   1     33      DNAJC11  1.02727430     FALSE
136 1_33   1     33       CAMTA1  1.02727430     FALSE
137 1_34   1     34       CAMTA1  1.40312732      TRUE
138 1_35   1     35       CAMTA1  1.52104538     FALSE
139 1_36   1     36       CAMTA1  1.04853732     FALSE
140 1_37   1     37       CAMTA1  0.64794094     FALSE
141 1_38   1     38       CAMTA1  1.23026086      TRUE
142 1_38   1     38        VAMP3  1.23026086      TRUE
143 1_38   1     38         PER3  1.23026086      TRUE
144 1_39   1     39         PER3  1.18154967      TRUE
145 1_39   1     39         UTS2  1.18154967      TRUE
146 1_39   1     39      TNFRSF9  1.18154967      TRUE
147 1_39   1     39        PARK7  1.18154967      TRUE
148 1_39   1     39       ERRFI1  1.18154967      TRUE
149 1_40   1     40      no_gene  1.79796879     FALSE
150 1_41   1     41      SLC45A1  0.20193560     FALSE

I want to create two new columns, xp.bg and xp.n.top, using the
following criteria:

If gene is the same in consecutive rows, xp.bg is the minimum value of
xp.norm in those rows; if gene is not the same, xp.bg is simply the
value of xp.norm for that row;

Likewise, if there's a run of contiguous xp.top = TRUE values,
xp.n.top is the minimum value in that range, and if xp.top is false or
NA, xp.n.top is NA, or 0 (I don't care).

So, in the above example,
xp.bg for rows 136:141 should be 0.64794094, and is equal to xp.norm
for all other rows,
xp.n.top for row 137 is 1.40312732, 1.18154967 for rows 141:148, and
0/NA for all other rows.

Is there a way to combine indexing and if statements or some such to
accomplish this? I want to it this without using split(main.table,
main.table$gene), because there's about 20,000 unique entries for
gene, and one of the entries, no_gene, is repeated throughout. I
thought briefly of subsetting the rows where xp.top is TRUE, but I
then don't know how to set the range for min, so that it only looks at
what would originally have been consecutive rows, and searching the
help has not proved particularly useful.

Thanks in advance,
Irene Gallego Romero
#
On Jan 28, 2010, at 7:05 AM, Irene Gallego Romero wrote:

            
Assuming that gene values are adjacent in a dataframe named df1, then  
this would work:

df1$xp.bg<- with(df1, ave(xp.norm, gene, FUN=min))
df1$seqgrp <- c(0, diff(df1$xp.top))
df1$seqgrp2 <- cumsum(df1$seqgrp != 0)
df1$xp.n.top <- with(df1, ave(xp.norm, seqgrp2, FUN=min))
is.na(df1$xp.n.top) <- !xp.top

 > df1$xp.bg<- with(df1, ave(xp.norm, gene, FUN=min))
 > df1
       id chr window    gene   xp.norm xp.top seqgrp seqgrp2  
xp.n.top     xp.bg
129 1_32   1     32  TAS1R1 1.2888211  FALSE      0       0       NA  
1.2888211
130 1_32   1     32  ZBTB48 1.2888211  FALSE      0       0       NA  
1.2888211
131 1_32   1     32  KLHL21 1.2888211  FALSE      0       0       NA  
1.2888211
132 1_32   1     32   PHF13 1.2888211  FALSE      0       0       NA  
1.0272743
133 1_33   1     33   PHF13 1.0272743  FALSE      0       0       NA  
1.0272743
134 1_33   1     33   THAP3 1.0272743  FALSE      0       0       NA  
1.0272743
135 1_33   1     33 DNAJC11 1.0272743  FALSE      0       0       NA  
1.0272743
136 1_33   1     33  CAMTA1 1.0272743  FALSE      0       0       NA  
0.6479409
137 1_34   1     34  CAMTA1 1.4031273   TRUE      1       1 1.403127  
0.6479409
138 1_35   1     35  CAMTA1 1.5210454  FALSE     -1       2       NA  
0.6479409
139 1_36   1     36  CAMTA1 1.0485373  FALSE      0       2       NA  
0.6479409
140 1_37   1     37  CAMTA1 0.6479409  FALSE      0       2       NA  
0.6479409
141 1_38   1     38  CAMTA1 1.2302609   TRUE      1       3 1.181550  
0.6479409
142 1_38   1     38   VAMP3 1.2302609   TRUE      0       3 1.181550  
1.2302609
143 1_38   1     38    PER3 1.2302609   TRUE      0       3 1.181550  
1.1815497
144 1_39   1     39    PER3 1.1815497   TRUE      0       3 1.181550  
1.1815497
145 1_39   1     39    UTS2 1.1815497   TRUE      0       3 1.181550  
1.1815497
146 1_39   1     39 TNFRSF9 1.1815497   TRUE      0       3 1.181550  
1.1815497
147 1_39   1     39   PARK7 1.1815497   TRUE      0       3 1.181550  
1.1815497
148 1_39   1     39  ERRFI1 1.1815497   TRUE      0       3 1.181550  
1.1815497
149 1_40   1     40 no_gene 1.7979688  FALSE     -1       4       NA  
1.7979688
150 1_41   1     41 SLC45A1 0.2019356  FALSE      0       4       NA  
0.2019356


And if the adjacent-gene assumption of the first request above were  
not met, then the first portion of this method could be used instead  
to great group indices.
#
If DF is your data frame then:

DF$xp.bg <- ave(DF$xp.norm, DF$gene, FUN = min)

will create a new column such that the entry in each row has the
minimum xp.norm of all rows with the same gene.  ave does  use split
internally but I think it would be worth trying anyways since its only
one short line of code.

See help(ave)
On Thu, Jan 28, 2010 at 7:05 AM, Irene Gallego Romero <ig247 at cam.ac.uk> wrote: