CODEWITHSUNDEEP
rsplitsubset
user3138373
user3138373

Reputation: 523

subsetting data frame on sum of column

This is a follow up question of my previous question

Considering I have the data frame like this:

g1:1    4
g1:2    5
g1:3    9
g2:1    6
g2:2    2
g3:1    5
g3:2    6
g4:1    4
g4:1    1

I use the following code to split first column on :

tab2 <- read.table("dplyrtest.txt",header=FALSE)
dput(tab2)
structure(list(V1 = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 
8L), .Label = c("g1:1", "g1:2", "g1:3", "g2:1", "g2:2", "g3:1", 
"g3:2", "g4:1"), class = "factor"), V2 = c(4L, 5L, 9L, 6L, 2L, 
5L, 6L, 4L, 1L)), class = "data.frame", row.names = c(NA, -9L
))
tab2 <- data.frame(tab2$V1, do.call(rbind, strsplit(as.character(tab2$V1),split=":")))
str(tab2)

'data.frame':   9 obs. of  3 variables:
 $ tab2.V1: Factor w/ 8 levels "g1:1","g1:2",..: 1 2 3 4 5 6 7 8 8
 $ X1     : Factor w/ 4 levels "g1","g2","g3",..: 1 1 1 2 2 3 3 4 4
 $ X2     : Factor w/ 3 levels "1","2","3": 1 2 3 1 2 1 2 1 1

tab2$X2 <- as.integer(tab2$X2)
str(tab2)

'data.frame':   9 obs. of  3 variables:
 $ tab2.V1: Factor w/ 8 levels "g1:1","g1:2",..: 1 2 3 4 5 6 7 8 8
 $ X1     : Factor w/ 4 levels "g1","g2","g3",..: 1 1 1 2 2 3 3 4 4
 $ X2     : int  1 2 3 1 2 1 2 1 1

colnames(tab2) <- c("gene","name","count")

dput(tab2)
structure(list(gene = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 
8L, 8L), .Label = c("g1:1", "g1:2", "g1:3", "g2:1", "g2:2", "g3:1", 
"g3:2", "g4:1"), class = "factor"), name = structure(c(1L, 1L, 
1L, 2L, 2L, 3L, 3L, 4L, 4L), .Label = c("g1", "g2", "g3", "g4"
), class = "factor"), count = structure(c(1L, 2L, 3L, 1L, 2L, 
1L, 2L, 1L, 1L), .Label = c("1", "2", "3"), class = "factor")), class = "data.frame", row.names = c(NA, 
-9L))

tab2 <- tab2 %>% group_by(name) %>% filter(sum(as.integer(count)) > 10)

This gives a warning, and tab2 has no data in it:

# A tibble: 0 x 3
# Groups:   name [1]
# … with 3 variables: gene <fct>, name <fct>, count <fct>
Warning message:
Factor `name` contains implicit NA, consider using `forcats::fct_explicit_na`

Any help is appreciated??

Upvotes: 2

Views: 88

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388907

The splitting step changes the numbers I believe.

Try doing this instead after reading the file. 

library(tidyverse)
tab2 <- read.table("dplyrtest.txt",header=FALSE)

tab2 %>%
  separate(V1, into = c("Gene", "name")) %>%
  rename_at(3, ~"count") %>%
  group_by(Gene) %>% #OR group_by(name)
  filter(sum(count) > 10)

#  Gene  name  count
#  <chr> <chr> <int>
#1  g1    1       4
#2  g1    2       5
#3  g1    3       9
#4  g3    1       5
#5  g3    2       6

Upvotes: 1

iod
iod

Reputation: 7592

tab2 %>% group_by(name) %>% summarize(sum(count))
# A tibble: 4 x 2
  name  `sum(count)`
  <fct>        <dbl>
1 g1              6.
2 g2              3.
3 g3              3.
4 g4              2.

tab2 %>% group_by(name) %>% filter(sum(as.integer(count)) > 5)
# A tibble: 3 x 3
# Groups:   name [1]
  gene  name  count
  <fct> <fct> <dbl>
1 g1:1  g1       1.
2 g1:2  g1       2.
3 g1:3  g1       3.

The code works fine, none of your groups have a sum higher than 10.

Upvotes: 1

Related Questions