CODEWITHSUNDEEP
r
Xin
Xin

Reputation: 674

Calculating sum of days per category

I have a dataset and I want to calulate the span of days summed per unique Id, and as well the days that spanned during one of the two unique "Actions"(STD, STD Modified). I would also like to display the data as one row per unique Id. How would I go about achieving this? Thanks in advance!

Desired result:

Id      Days on STD     Days on STD Modified     Total
123         228                  0                228
321         85                   42               127

Data:

structure(list(Id = c(123, 123, 321, 321, 321, 321), Action = c("STD", 
"STD", "STD", "STD", "STD Modified", "STD"), IDM.Start = structure(c(1427414400, 
1499299200, 1457740800, 1508371200, 1511913600, 1540166400), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), IDM.End = structure(c(1438214400, 
1508198400, 1458691200, 1511827200, 1515542400, 1543104000), class = c("POSIXct", 
"POSIXt"), tzone = "UTC")), row.names = c(NA, 6L), class = "data.frame")

Upvotes: 0

Views: 26

Answers (1)

Jon Spring
Jon Spring

Reputation: 66490

library(tidyverse); library(lubridate)
df %>%
  mutate(days = (IDM.End - IDM.Start)/ddays(1)) %>%
  count(Id, Action, wt = days, name = "days") %>%
  spread(Action, days, fill = 0) %>%
  mutate(Total = STD + `STD Modified`)

## A tibble: 2 x 4
#     Id   STD `STD Modified` Total
#  <dbl> <dbl>          <dbl> <dbl>
#1   123   228              0   228
#2   321    85             42   127

Upvotes: 1

Related Questions