How put value when exist in other rows?

Hi community

I want to correct the coordinates of a database. I have only in some cases a new correction for latitude or longitude but in somes cases both changes.
So, when I graph in `leaflet` I get an error for those blank data.
The idea is that when the blank space is filled with the data from the first rows of the A-D
The Acces column is an identifier

``````dat <- data.frame(Acces=c('A','B','C','D','E',
'A','B','C','D','E'),
latitud=c( -24.12,-26.33,-27.32,-29.48,-27.43,
'',-26.80,-27.3206, '', -27.4334),
longitud=c(-65.40,-58.75,-58.58,-56.48,	-58.82,
-65.4027,-58.9666,'', -56.9833, -58.8337))
``````

Hope obtain this:

Tnks!

This can be accomplished with the `na.locf()` function from the `zoo` package by first converting empty character strings "" to NA. The only caveat is the first row in the group needs to have a non-NA value.

``````library(tidyverse)
library(zoo)

dat %>%
mutate_at(2:3, ~ifelse(. == '', NA, .)) %>%
group_by(Acces) %>%
mutate(latitud = na.locf(latitud),
longitud = na.locf(longitud)
) %>%
ungroup()
#> # A tibble: 10 × 3
#>    Acces latitud  longitud
#>    <chr> <chr>    <chr>
#>  1 A     -24.12   -65.4
#>  2 B     -26.33   -58.75
#>  3 C     -27.32   -58.58
#>  4 D     -29.48   -56.48
#>  5 E     -27.43   -58.82
#>  6 A     -24.12   -65.4027
#>  7 B     -26.8    -58.9666
#>  8 C     -27.3206 -58.58
#>  9 D     -29.48   -56.9833
#> 10 E     -27.4334 -58.8337
``````

Created on 2022-12-09 with reprex v2.0.2.9000

1 Like

Another option assuming they are in the desired order

``````library(dplyr)
library(tidyr)

dat <- data.frame(Acces=c('A','B','C','D','E',
'A','B','C','D','E'),
latitud=c( -24.12,-26.33,-27.32,-29.48,-27.43,
'',-26.80,-27.3206, '', -27.4334),
longitud=c(-65.40,-58.75,-58.58,-56.48,   -58.82,
-65.4027,-58.9666,'', -56.9833, -58.8337))

dat %>%
mutate(across(c(latitud, longitud), na_if, y = "")) %>%
group_by(Acces) %>%
fill(latitud, longitud, .direction = "down") %>%
ungroup()
#> # A tibble: 10 × 3
#>    Acces latitud  longitud
#>    <chr> <chr>    <chr>
#>  1 A     -24.12   -65.4
#>  2 B     -26.33   -58.75
#>  3 C     -27.32   -58.58
#>  4 D     -29.48   -56.48
#>  5 E     -27.43   -58.82
#>  6 A     -24.12   -65.4027
#>  7 B     -26.8    -58.9666
#>  8 C     -27.3206 -58.58
#>  9 D     -29.48   -56.9833
#> 10 E     -27.4334 -58.8337
``````

Created on 2022-12-09 with reprex v2.0.2

1 Like

The both solutions are excellent.
Tnks guys!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.