我有一個資料集，其中包含來自現場觀察員的死鳥記錄。

Death.Date Observer Species Bird.ID
1 03/08/2021       DA      MF FC10682
2 15/08/2021       AG      MF FC10698
3 12/01/2022       DA      MF FC20957
4 09/02/2022       DA      MF FC10708

我想從中生成一個資料集，其中包含唯一Bird.ID / Month的數量，以便我可以從中生成一個圖表。（“獨特”是因為有些人有時會犯錯誤并兩次進入鳥）。

在這種情況下，輸出將是：

Month Number of dead 
08/2021 2
01/2022 1
02/2022 1

這個想法是按月使用該distinct函式（知道該值采用日期格式dd/mm/yyyy）。

uj5u.com熱心網友回復：

1. 如果您的日期列是字符型別，請先轉換為日期型別dmy

2. 將格式更改為月份和年份

3. group_by和summarize

library(dplyr)
library(lubridate) # in case your Date is in character format

df %>% 
  mutate(Death.Date = dmy(Death.Date)) %>% # you may not need this line
  mutate(Month = format(as.Date(Death.Date), "%m/%Y")) %>%
  group_by(Month) %>% 
  summarise(`Number of dead`=n())

 Month   `Number of dead`
  <chr>              <int>
1 01/2022                1
2 02/2022                1
3 08/2021                2

uj5u.com熱心網友回復：

為了完整起見，這可以在不使用aggregate任何附加包的情況下實作：

df <- data.frame(
  Death.Date = c("3/8/2021", "15/08/2021", "12/1/2022", "9/2/2022"),
  Observer = c("DA", "AG", "DA", "DA"),
  Species = c("MF", "MF", "MF", "MF"),
  Bird.ID = c("FC10682", "FC10698", "FC20957", "FC10708")
)


aggregate.data.frame(
  x = df["Bird.ID"],
  by = list(death_month = format(as.Date(df$Death.Date, "%d/%m/%Y"), "%m/%Y")),
  FUN = function(x) {length(unique(x))}
)

筆記

• 匿名函式function(x) {length(unique(x))提供唯一值的計數
• format(as.Date(df$Death.Date, "%d/%m/%Y"), "%m/%Y"))call 確保提供了月/年字串

uj5u.com熱心網友回復：

data.table 解決方案

library(data.table)
library(lubridate)

# Reproductible example with a duplicated bird
deadbirds <- data.table::data.table(Death.Date = c("03/08/2021", "15/08/2021", "12/01/2022", "09/02/2022", "03/08/2021"),
                                    Observer   = c("DA", "AG", "DA", "DA", "DA"),
                                    Species    = c("MF", "MF", "MF" , "MF", "MF"),
                                    Bird.ID    = c("FC10682", "FC10698", "FC20957", "FC10708", "FC10682"))

# Clean dataset = option 1 : delete all duplicated row
deadbirds <- base::unique(deadbirds)

# Clean dataset = option 2 : keep only the first line by bird (can be useful when there is duplicated data with differents values in useless columns)
deadbirds <- deadbirds[
  j = .SD[1],
  by = c("Bird.ID")
]

# Death.Date as date
deadbirds <- deadbirds[
  j = Death.Date := lubridate::dmy(Death.Date)
]

# Create month.Death.Date
deadbirds <- deadbirds[
  j = month.Death.Date := base::paste0(lubridate::month(Death.Date),
                                       "/",
                                       lubridate::year(Death.Date))
]

# Count by month
deadbirds <- deadbirds[
  j  = `Number of dead` := .N,
  by = month.Death.Date]

uj5u.com熱心網友回復：

tidyverse一個可能的解決方案，基于lubridate和zoo::as.yearmon：

library(tidyverse)
library(lubridate)
library(zoo)

df <- data.frame(
  Death.Date = c("3/8/2021", "15/08/2021", "12/1/2022", "9/2/2022"),
  Observer = c("DA", "AG", "DA", "DA"),
  Species = c("MF", "MF", "MF", "MF"),
  Bird.ID = c("FC10682", "FC10698", "FC20957", "FC10708")
)

df %>% 
  group_by(date = as.yearmon(dmy(Death.Date))) %>% 
  summarise(nDead = n_distinct(Bird.ID), .groups = "drop")

#> # A tibble: 3 x 2
#>   date      nDead
#>   <yearmon> <int>
#> 1 Aug 2021      2
#> 2 Jan 2022      1
#> 3 Feb 2022      1

uj5u.com熱心網友回復：

你可以使用：

as.data.frame(table(format(as.Date(df$Death.Date,'%d/%m/%Y'), '%m/%Y')))

#       Var1 Freq
# 1 01/2022    1
# 2 02/2022    1
# 3 08/2021    2

資料：

df <- data.frame(
  Death.Date = c("3/8/2021", "15/08/2021", "12/1/2022", "9/2/2022"),
  Observer = c("DA", "AG", "DA", "DA"),
  Species = c("MF", "MF", "MF", "MF"),
  Bird.ID = c("FC10682", "FC10698", "FC20957", "FC10708")
)

標籤：r 日期 数数 清楚的

上一篇：如何在PostgreSQL中選擇上個月的范圍

下一篇：如何選擇按開始和結束日期加入的所有連續行