I want columns counting for events per day including dates without events. This is an example of my data although my real dataset has more than 100 ID's
dt <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), date = c("1/01/2000", "2/01/2000", "2/01/2000",
"5/01/2000", "5/01/2000", "5/01/2000", "6/01/2000", "2/01/2000", "3/01/2000",
"3/01/2000", "4/01/2000", "4/01/2000", "4/01/2000", "4/01/2000",
"5/01/2000", "9/01/2000")), .Names = c("id", "date"),
row.names = c(NA, -16L), class = "data.frame")
What I want is:
date count 1 count 2
1/01/2000 0 0
2/01/2000 2 1
3/01/2000 0 2
4/01/2000 0 4
5/01/2000 3 1
6/01/2000 1 0
7/01/2000 0 0
8/01/2000 0 0
9/01/2000 0 1
My real data will be dates from 1/01/2000 to 31/12/2000. I want all ID's to have all these dates, even if there were zero events during certain days.
CodePudding user response：
Here is an approach using data.table
library(data.table)
setDT(dt)[,`:=`(date=as.Date(date, "%Y-%m-%d"),id=paste0("count",id))]
dcast(
dt[SJ(date=seq(min(date), max(date),1)), on=.(date)],
date~id,fun.aggregate = length,
)[,`NA`:=NULL]
Output:
date count1 count2
1: 2020-01-01 1 0
2: 2020-01-02 2 1
3: 2020-01-03 0 2
4: 2020-01-04 0 4
5: 2020-01-05 3 1
6: 2020-01-06 1 0
7: 2020-01-07 0 0
8: 2020-01-08 0 0
9: 2020-01-09 0 1
If you know your dates, as you indicate in the post, you can use those directly:
library(data.table)
setDT(dt)[,`:=`(date=as.Date(date, "%Y-%m-%d"), id=paste0("count",id))]
result = dcast(
dt[SJ(date=seq(as.Date("2020-01-01"), as.Date("2020-12-31"),1)), on=.(date)],
date~id,fun.aggregate = length,
)[,`NA`:=NULL]
Output:
date count1 count2
1: 2020-01-01 1 0
2: 2020-01-02 2 1
3: 2020-01-03 0 2
4: 2020-01-04 0 4
5: 2020-01-05 3 1
---
362: 2020-12-27 0 0
363: 2020-12-28 0 0
364: 2020-12-29 0 0
365: 2020-12-30 0 0
366: 2020-12-31 0 0
Input:
dt = structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L), date = c("2020-01-01", "2020-01-02", "2020-01-02",
"2020-01-05", "2020-01-05", "2020-01-05", "2020-01-06", "2020-01-02", "2020-01-03",
"2020-01-03", "2020-01-04", "2020-01-04", "2020-01-04", "2020-01-04", "2020-01-05",
"2020-01-09")), row.names = c(NA, -16L), class = "data.frame")
CodePudding user response：
We may use complete and then reshape to 'wide' with pivot_wider. The OP showed the example data with format for 'date' as month/day/year. If the original data is in format year-month-day then change the mdy(date) to ymd(date)
library(lubridate)
library(tidyr)
library(dplyr)
library(stringr)
dt %>%
mutate(date = mdy(date), count = 1) %>%
group_by(id = str_c('count', id)) %>%
complete(date = seq(min(.$date, na.rm = TRUE),
max(.$date, na.rm = TRUE), by = 'month'),
fill = list(count = 0)) %>%
ungroup %>%
pivot_wider(names_from = id, values_from =count,
values_fn = sum, values_fill = 0)
-output
# A tibble: 9 × 3
date count1 count2
<date> <dbl> <dbl>
1 2000-01-01 1 0
2 2000-02-01 2 1
3 2000-03-01 0 2
4 2000-04-01 0 4
5 2000-05-01 3 1
6 2000-06-01 1 0
7 2000-07-01 0 0
8 2000-08-01 0 0
9 2000-09-01 0 1
CodePudding user response：
Here is a base R option using table seq factor
with(
transform(
dt,
date = as.Date(date, "%d/%m/%Y")
),
table(
factor(date,
levels = as.character(seq(min(date), max(date), 1))
),
id
)
)
which gives
id
1 2
2000-01-01 1 0
2000-01-02 2 1
2000-01-03 0 2
2000-01-04 0 4
2000-01-05 3 1
2000-01-06 1 0
2000-01-07 0 0
2000-01-08 0 0
2000-01-09 0 1
Or, we can step further with reshape as.data.frame if we want data.frame output
reshape(
as.data.frame(
with(
transform(
dt,
date = as.Date(date, "%d/%m/%Y")
),
table(
factor(date,
levels = as.character(seq(min(date), max(date), 1))
),
id
)
)
),
idvar = "Var1",
timevar = "id",
direction = "wide"
)
which gives
Var1 Freq.1 Freq.2
1 2000-01-01 1 0
2 2000-01-02 2 1
3 2000-01-03 0 2
4 2000-01-04 0 4
5 2000-01-05 3 1
6 2000-01-06 1 0
7 2000-01-07 0 0
8 2000-01-08 0 0
9 2000-01-09 0 1