Início / coding / Perguntas / 79555634
Accepted
shrimp
Asked: 2025-04-04 22:50:44 +0800 CST

Como posso anexar meu conjunto de dados com base no registro de data e hora?

  • 772

Tenho dois conjuntos de dados, um que chamarei de mestre e o outro de suplementar. Ambos têm observações em intervalos de 10 minutos, mas há lacunas de tempo no mestre que desejo preencher com o suplementar. Normalmente, eu usaria apenas um left_join, mas o suplementar também tem carimbos de data/hora sobrepostos do mestre. Quero apenas transferir os carimbos de data/hora exclusivos do suplementar para o mestre. Código de exemplo abaixo:

TIMESTAMP <- c("2019-04-27 17:30:00", "2019-04-27 17:40:00", "2019-04-27 17:50:00", "2019-04-27 18:10:00", "2019-04-27 18:30:00", "2019-04-27 18:40:00")
dates<-c("2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27")
Tower <- c("TW2", "TW2", "TW2", "TW2", "TW2", "TW2")
Camera <- c("C5N","C5N","C5N","C5N","C5N","C5N")
Cow <-c(0, 1, 0, 0, 0, 5)

Master<- data.frame(TIMESTAMP, dates, Tower, Camera, Cow)

TIMESTAMP <- c("2019-04-27 17:30:00", "2019-04-27 17:40:00", "2019-04-27 18:00:00", "2019-04-27 18:10:00", "2019-04-27 18:20:00","2019-04-27 18:30:00")
dates<-c("2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27", "2019-04-27")
Tower <- c("TW2", "TW2", "TW2", "TW2", "TW2", "TW2")
Camera <- c("C4N","C4N","C4N","C4N","C4N","C4N")
Cow <-c(0, 1, 4, 0, 2, 0)

Supplemental<- data.frame(TIMESTAMP, dates, Tower, Camera, Cow)

Tentei usar anti_join no pacote dplyr, mas o dataframe resultante tinha apenas 2 observações, então algo deve estar errado.

require(dplyr)
Complete<-Master%>%
  anti_join(Supplemental, by=join_by(TIMESTAMP==TIMESTAMP))

Qualquer ajuda é bem-vinda!

2 respostas

  1. Aparentemente, você quer preencher a câmera 5 com valores da câmera 4. Como 5 parece ser uma referência, podemos adicionar conteúdo numérico sort_bynegativo , por exemplo.grep

    merge(Master, Supplemental, all=TRUE) |> 
  sort_by(~list(TIMESTAMP, -as.numeric(gsub('\\D', '', Camera)))) |> 
  subset(!duplicated(TIMESTAMP))
#              TIMESTAMP      dates Tower Camera Cow
# 2  2019-04-27 17:30:00 2019-04-27   TW2    C5N   0
# 4  2019-04-27 17:40:00 2019-04-27   TW2    C5N   1
# 5  2019-04-27 17:50:00 2019-04-27   TW2    C5N   0
# 6  2019-04-27 18:00:00 2019-04-27   TW2    C4N   4
# 8  2019-04-27 18:10:00 2019-04-27   TW2    C5N   0
# 9  2019-04-27 18:20:00 2019-04-27   TW2    C4N   2
# 11 2019-04-27 18:30:00 2019-04-27   TW2    C5N   0
# 12 2019-04-27 18:40:00 2019-04-27   TW2    C5N   5
    • 0
  2. Best Answer

    anti_join()é uma junção de filtragem -- "pegue-me linhas de x que não tenham uma correspondência em y ", ela só pode retornar um subconjunto de x , nunca mais linhas do que no primeiro quadro. Você pode alternar anti_join()a ordem dos argumentos para obter linhas de Supplementalque não tenham uma correspondência TIMESTAMPem Mastere anexar esse subconjunto a a Master:

    bind_rows(
  Master,
  anti_join(Supplemental, Master, by = join_by(TIMESTAMP))
) |> 
arrange(TIMESTAMP)
#>             TIMESTAMP      dates Tower Camera Cow
#> 1 2019-04-27 17:30:00 2019-04-27   TW2    C5N   0
#> 2 2019-04-27 17:40:00 2019-04-27   TW2    C5N   1
#> 3 2019-04-27 17:50:00 2019-04-27   TW2    C5N   0
#> 4 2019-04-27 18:00:00 2019-04-27   TW2    C4N   4
#> 5 2019-04-27 18:10:00 2019-04-27   TW2    C5N   0
#> 6 2019-04-27 18:20:00 2019-04-27   TW2    C4N   2
#> 7 2019-04-27 18:30:00 2019-04-27   TW2    C5N   0
#> 8 2019-04-27 18:40:00 2019-04-27   TW2    C5N   5

    Ou simplesmente use o subconjunto base R:

    
`%nin%` <- Negate(`%in%`)
Complete <- rbind(Master, Supplemental[Supplemental$TIMESTAMP %nin% Master$TIMESTAMP,])
Complete
#>              TIMESTAMP      dates Tower Camera Cow
#> 1  2019-04-27 17:30:00 2019-04-27   TW2    C5N   0
#> 2  2019-04-27 17:40:00 2019-04-27   TW2    C5N   1
#> 3  2019-04-27 17:50:00 2019-04-27   TW2    C5N   0
#> 4  2019-04-27 18:10:00 2019-04-27   TW2    C5N   0
#> 5  2019-04-27 18:30:00 2019-04-27   TW2    C5N   0
#> 6  2019-04-27 18:40:00 2019-04-27   TW2    C5N   5
#> 31 2019-04-27 18:00:00 2019-04-27   TW2    C4N   4
#> 51 2019-04-27 18:20:00 2019-04-27   TW2    C4N   2

Complete[order(Complete$TIMESTAMP),]
#>              TIMESTAMP      dates Tower Camera Cow
#> 1  2019-04-27 17:30:00 2019-04-27   TW2    C5N   0
#> 2  2019-04-27 17:40:00 2019-04-27   TW2    C5N   1
#> 3  2019-04-27 17:50:00 2019-04-27   TW2    C5N   0
#> 31 2019-04-27 18:00:00 2019-04-27   TW2    C4N   4
#> 4  2019-04-27 18:10:00 2019-04-27   TW2    C5N   0
#> 51 2019-04-27 18:20:00 2019-04-27   TW2    C4N   2
#> 5  2019-04-27 18:30:00 2019-04-27   TW2    C5N   0
#> 6  2019-04-27 18:40:00 2019-04-27   TW2    C5N   5

    Criado em 2025-04-04 com reprex v2.1.1

    • 0

relate perguntas