Copy content from cell in a row to adjacent right-hand cell in R

user_16870234 0
Viewed 52

I have a data frame that looks like this:

 df <- data.frame(col1=c(NA, NA),
                  col2=c("arroz", "unit"),
                  col3=c(NA, "area"),
                  col4=c("arveja", "unit"),
                  col5=c(NA, "area"),
                  col6=c("frejol", "unit"),
                  col7=c(NA, "area"))

  col1  col2 col3   col4 col5   col6 col7
1   NA arroz <NA> arveja <NA> frejol <NA>
2   NA  unit area   unit area   unit area

As you can see, there are NAs in the first row. I would like to copy the content of the non-NA cells and paste it into the right-hand NA cell, so that I obtain a df like this; the very first column has to stay as NA:

df_output <- data.frame(col1=c(NA, NA),
                         col2=c("arroz", "unit"),
                         col3=c("arroz", "area"),
                         col4=c("arveja", "unit"),
                         col5=c("arveja", "area"),
                         col6=c("frejol", "unit"),
                         col7=c("frejol", "area"))

  col1  col2  col3   col4   col5   col6   col7
1   NA arroz arroz arveja arveja frejol frejol
2   NA  unit  area   unit   area   unit   area

This is a toy example from larger dataset.

Thanks!!

dataframe r string
4 Answers
library(tidyverse)

df <- data.frame(col1=c(NA, NA),
                 col2=c("arroz", "unit"),
                 col3=c(NA, "area"),
                 col4=c("arveja", "unit"),
                 col5=c(NA, "area"),
                 col6=c("frejol", "unit"),
                 col7=c(NA, "area")) %>% 
  as_tibble()


df %>%
  pivot_longer(-col1) %>%  
  fill(value) %>%  
  pivot_wider(names_from = name, values_from = value) %>%  
  unnest()

# A tibble: 2 x 7
  col1  col2  col3  col4   col5   col6   col7  
  <lgl> <chr> <chr> <chr>  <chr>  <chr>  <chr> 
1 NA    arroz arroz arveja arveja frejol frejol
2 NA    unit  area  unit   area   unit   area
user_172133550

A possibility would be to use Reduce to replace the NA values.

df[] <- Reduce(function(x, y) {i <- is.na(y); replace(y, i, x[i])},
               df, accumulate = TRUE)
df
#  col1  col2  col3   col4   col5   col6   col7
#1   NA arroz arroz arveja arveja frejol frejol
#2   NA  unit  area   unit   area   unit   area

Benchmark

bench::mark(check = FALSE
        , "tidyverse" = {
            df %>%
              pivot_longer(-col1) %>%  
              fill(value) %>%  
              pivot_wider(names_from = name, values_from = value) %>%  
              unnest()
            }
        , "zoo" = setNames(data.frame(t(apply(df, 1, na.locf0))), colnames(df))
        , "Reduce" = Reduce(function(x, y) {i <- is.na(y); replace(y, i, x[i])},
                            df, accumulate = TRUE)
          )
#  expression     min  median `itr/sec` mem_alloc `gc/sec` n_itr  n_gc total_time
#  <bch:expr> <bch:t> <bch:t>     <dbl> <bch:byt>    <dbl> <int> <dbl>   <bch:tm>
#1 tidyverse   26.2ms  26.7ms      37.3     104KB     6.99    16     3      429ms
#2 zoo        277.9µs 292.7µs    3383.         0B     8.23  1644     4      486ms
#3 Reduce      33.8µs  36.4µs   26973.       11KB    10.8   9996     4      371ms

Reduce is about 8 times faster than zoo and 700 times faster than tidyverse.

user_104885040

You can apply the na.locf0 on all rows using the following code:

library(zoo)
setNames(data.frame(t(apply(df, 1, na.locf0))), colnames(df))

Output:

  col1  col2  col3   col4   col5   col6   col7
1 <NA> arroz arroz arveja arveja frejol frejol
2 <NA>  unit  area   unit   area   unit   area
user_142827140

You could just lag the the row by one column and assign at former NA indices.

nas <- is.na(df[1, ])
df[1, nas] <- c(NA, unlist(df[1, -ncol(df)]))[nas]
df
#   col1  col2  col3   col4   col5   col6   col7
# 1 <NA> arroz arroz arveja arveja frejol frejol
# 2 <NA>  unit  area   unit   area   unit   area
user_65740380
Related