Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Add column with row number count of grouped column values while excluding NA's

I am trying to add a column to a data frame with a row number by groups. I do not want to count NA’s. I have grouped by Day and want to add a column counting the row numbers by group without NA’s. I am getting a data frame that counts the NA’s first and then the number values.

Code so far:

df <- data.frame (Day  = c("1", "1", "1", "1", "1", "2", "2", "2", "2", "2", "3", "3", "3", "3", "3"),
                  test1 = c("1.2", NA, "3.2", NA, "7.2", "1.2", "2.5", "0.98", NA, "1.6","1.2", NA, "4.1", "9.2", "0.78")
)

df <- df %>%
  group_by(Day) %>%
  mutate(row_num = row_number(!is.na(test1)))
print(df)

# A tibble: 15 x 3
# Groups:   Day [3]
   Day   test1 row_num
   <chr> <chr>       <int>
 1 1     1.2             3
 2 1     NA              1
 3 1     3.2             4
 4 1     NA              2
 5 1     7.2             5
 6 2     1.2             2
 7 2     2.5             3
 8 2     0.98            4
 9 2     NA              1
10 2     1.6             5
11 3     1.2             2
12 3     NA              1
13 3     4.1             3
14 3     9.2             4
15 3     0.78            5

It should be:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Day   test1 row_num
   <chr> <chr>       <int>
 1 1     1.2             1
 2 1     NA              
 3 1     3.2             2
 4 1     NA              
 5 1     7.2             3
 6 2     1.2             4
 7 2     2.5             5
 8 2     0.98            6
 9 2     NA              
10 2     1.6             1
11 3     1.2             2
12 3     NA              
13 3     4.1             3
14 3     9.2             4
15 3     0.78            5

>Solution :

We may use replace

library(dplyr)
df %>% 
  group_by(Day) %>%
   mutate(row_num =  replace(test1, !is.na(test1),
       seq_along(test1[!is.na(test1)]))) %>%
   ungroup

Or with data.table

library(data.table)
setDT(df)[!is.na(test1), row_num := seq_len(.N), by = Day]
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading