Replace NA in row with value in adjacent row "ROW" not column

user_16396496 0
Viewed 234

Raw data:

    V1 V2
1   c1  a
2   c2  b
3 <NA>  c
4 <NA>  d
5   c3  e
6 <NA>  f
7   c4  g

Reproducible Sample Data

V1 = c('c1','c2',NA,NA,'c3',NA,'c4')
V2 = c('a','b','c','d','e','f','g')

data.frame(V1,V2)

Expected output

  V1_after V2_after
1       c1        a
2       c2    b c d
3       c3      e f
4       c4        g

V1_after <- c('c1','c2','c3','c4')
V2_after <- c('a',paste('b','c','d'),paste('e','f'),'g')

data.frame(V1_after,V2_after)

This is sample data. In Real data, Rows where NA in V1 is not regular

It is too difficult to me

na dataframe r
3 Answers

You could make use of zoo::na.locf for this. It takes the most recent non-NA value and fill all NA values on the way:

library(dplyr)
library(zoo)

df %>%
  mutate(V1 = zoo::na.locf(V1)) %>%
  group_by(V1) %>%
  summarise(V2 = paste0(V2, collapse = " "))

# A tibble: 4 x 2
  V1    V2   
  <chr> <chr>
1 c1    a    
2 c2    b c d
3 c3    e f  
4 c4    g
user_143145200

A base R option using na.omit + cumsum + aggregate

aggregate(
  V2 ~ .,
  transform(
    df,
    V1 = na.omit(V1)[cumsum(!is.na(V1))]
  ), c
)

gives

  V1      V2
1 c1       a
2 c2 b, c, d
3 c3    e, f
4 c4       g
user_121587570

You can fill the NA with the previous non-NA values and summarise the data.

library(dplyr)
library(tidyr)

df %>%
  fill(V1) %>%
  group_by(V1) %>%
  summarise(V2 = paste(V2, collapse = ' '))

#   V1    V2   
#  <chr> <chr>
#1 c1    a    
#2 c2    b c d
#3 c3    e f  
#4 c4    g
user_39629140
Related