replace missing data in multiple column by different specific value

Question

I have a huge data frame with several missing value that I need to replace as follow:

Cycle A	Cycle B	Cycle C
na	na	na
na	na	na
na	na	na
-1	na	0
-1	-2	0
na	-2	na
na	na	na
na	na	1
0	-1	1
0	-1	na
na	na	na
na	na	na
na	0	2
1	0	2
1	na	na
na	na	na

For each column I need to replace the NA's by the next number that appears, to have something like that:

Cycle A	Cycle B	Cycle C
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
0	-2	1
0	-1	1
0	-1	1
0	-1	1
0	-1	2
1	0	2
1	0	2
1	0	2
1	0	2
1	1	3
2	1	3

Any idea how to do that? Thank you.

Can you clarify what the general rule would be - it looks like NAs are replaced with the first non-missing value underneath. But what about the NAs at the end of the column - how do we know that the last NA in the first column is meant to be replaced with "2", for example? — Andrea M
– Andrea M, Commented Apr 21, 2022 at 8:20
Try tidyr::fill(df, starts_with("Cycle"), .direction = "up") — Darren Tsai
– Darren Tsai, Commented Apr 21, 2022 at 8:24
the rule would be, after the last non-missing value, implement the value by "+1" — Virginie
– Virginie, Commented Apr 21, 2022 at 8:27

benson23 · Accepted Answer · 2022-04-21 08:32:58Z

2

Assume you want to perform replacement in all columns that starts_with Cycle.

The first fill function replaces NA with the next row values. The mutate function replaces NA in the last row to be last non-NA value + 1.

library(tidyverse)

df %>% 
  fill(starts_with("Cycle"), .direction = "up") %>% 
  mutate(across(starts_with("Cycle"), ~ replace_na(.x, last(.x[!is.na(.x)]) + 1)))

   CycleA CycleB CycleC
1      -1     -2      0
2      -1     -2      0
3      -1     -2      0
4      -1     -2      0
5      -1     -2      0
6       0     -2      1
7       0     -1      1
8       0     -1      1
9       0     -1      1
10      0     -1      2
11      1      0      2
12      1      0      2
13      1      0      2
14      1      0      2
15      1      1      3
16      2      1      3

edited Apr 21, 2022 at 8:32

answered Apr 21, 2022 at 8:24

benson23

19.6k9 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Virginie Over a year ago

I have one problem with that (my fault I did not mention it) in between each column I have other columns (character and numeric), so I get an error message while tryin this code: Error in mutate(): ! Problem while computing ..1 = across(everything(), ~replace_na(.x, last(.x[!is.na(.x)]) + 1)). Caused by error in across(): ! Problem while computing column NOTH. Caused by error in last(NOTH[!is.na(NOTH)]) + 1: ! non-numeric argument to binary operator Is there a way to run that only for the columns that starts with "Cycle" for instance?

benson23 Over a year ago

@Virginie See my update. This assumes you want to perform replacement in all columns that starts with Cycle

benson23 Over a year ago

@Virginie You can click on the little tick mark on the left hand side of your favourite answer to accept it :)

jay.sf · Accepted Answer · 2022-04-21 08:34:50Z

First, convert "na" to NA and type.covert for numeric values.

dat <- replace(dat, dat== 'na', NA) |> type.convert(as.is=TRUE)

Next, I might be wrong, are you looking for this underlying structure?

f <- \(x) {
  rp <- cumsum(c(0, diff(!is.na(x))) > 0) + min(x, na.rm=TRUE)
  nas <- is.na(x)
  x[nas] <- rp[nas]
  x
}

cols <- c("CycleA", "CycleB", "CycleC")  ## select columns

dat[cols] <- lapply(dat[cols], f)
dat
#    CycleA CycleB CycleC
# 1      -1     -2      0
# 2      -1     -2      0
# 3      -1     -2      0
# 4      -1     -2      0
# 5      -1     -2      0
# 6       0     -2      1
# 7       0     -1      1
# 8       0     -1      1
# 9       0     -1      1
# 10      0     -1      2
# 11      1      0      2
# 12      1      0      2
# 13      1      0      2
# 14      1      0      2
# 15      1      1      3
# 16      2      1      3

Data:

dat <- structure(list(CycleA = c("na", "na", "na", "-1", "-1", "na", 
"na", "na", "0", "0", "na", "na", "na", "1", "1", "na"), CycleB = c("na", 
"na", "na", "na", "-2", "-2", "na", "na", "-1", "-1", "na", "na", 
"0", "0", "na", "na"), CycleC = c("na", "na", "na", "0", "0", 
"na", "na", "1", "1", "na", "na", "na", "2", "2", "na", "na")), class = "data.frame", row.names = c(NA, 
-16L))

Collectives™ on Stack Overflow

replace missing data in multiple column by different specific value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Cycle A	Cycle B	Cycle C
na	na	na
na	na	na
na	na	na
-1	na	0
-1	-2	0
na	-2	na
na	na	na
na	na	1
0	-1	1
0	-1	na
na	na	na
na	na	na
na	0	2
1	0	2
1	na	na
na	na	na

Cycle A	Cycle B	Cycle C
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
0	-2	1
0	-1	1
0	-1	1
0	-1	1
0	-1	2
1	0	2
1	0	2
1	0	2
1	0	2
1	1	3
2	1	3

Cycle A	Cycle B	Cycle C
na	na	na
na	na	na
na	na	na
-1	na	0
-1	-2	0
na	-2	na
na	na	na
na	na	1
0	-1	1
0	-1	na
na	na	na
na	na	na
na	0	2
1	0	2
1	na	na
na	na	na

Cycle A	Cycle B	Cycle C
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
0	-2	1
0	-1	1
0	-1	1
0	-1	1
0	-1	2
1	0	2
1	0	2
1	0	2
1	0	2
1	1	3
2	1	3

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Cycle A	Cycle B	Cycle C
na	na	na
na	na	na
na	na	na
-1	na	0
-1	-2	0
na	-2	na
na	na	na
na	na	1
0	-1	1
0	-1	na
na	na	na
na	na	na
na	0	2
1	0	2
1	na	na
na	na	na

Cycle A	Cycle B	Cycle C
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
-1	-2	0
0	-2	1
0	-1	1
0	-1	1
0	-1	1
0	-1	2
1	0	2
1	0	2
1	0	2
1	0	2
1	1	3
2	1	3