CODEWITHSUNDEEP
rdataframedplyr
Vector JX
Vector JX

Reputation: 179

categorizing Types in bucket using R

I have a dataframe which looks like:

DF1

ID      Value      Type         Date
II-1    150        Active       2019-01-01 15:34:18
II-1    175        Active       2019-01-01 15:34:18
II-1    165        Active       2019-01-01 15:34:18
II-1    168        Active       2019-01-01 15:34:18
II-2    200        InActive     2019-01-05 17:14:20
II-2    45         InActive     2019-01-05 17:14:20
II-3    34         InActive     2019-02-04 11:04:12
II-4    78         InActive     2019-02-01 12:33:14

I need to convert the above output in the below mentioned format in R to achieve the below mentioned format.

Where, the 1-3 and 3-5 are the bifurcation of count of ID basis on the bucket under which it falls as per the Type.

Example: If ID II-1 coming four times then it will fall in 3-5 bucket, similarly ID II-2 will fall in 1-3 bucket. 

Month       Total      Active  1-3   3-5   InActive   1-3   3-5  Hold  1-3  3-5
Jan-19       6           2      1     1       0         0    0     0    0    0
Feb-19       2           0      0     0       2         2    0     0    0    0

Upvotes: 0

Views: 69

Answers (2)

user8065556
user8065556

Reputation:

If you want the totals in as well, you can find these separately and do a join:

## Libraries
library(tidyverse)
library(lubridate)


## Alter the DF1 table to get months in the right format: DF1_new
DF1_new <- DF1 %>%
  # Create new month column
  mutate(Month = as_factor(str_c(month(Date, label = TRUE), year(Date), sep = "-")),
         Type = as_factor(Type)) %>%
  # Reorder columns
  select(Month, everything())


## Group DF1_new by Month and Type: right
right <- DF1_new %>%
  # Count ID by month and type
  count(Month, Type, ID) %>%
  # Place each in buckets by count
  mutate(Bucket = case_when(n < 4 ~ "1-3", TRUE ~ "4-5")) %>%
  # Combine bucket names
  unite(Type.Bucket, c(Type, Bucket), sep = ".") %>%
  # Count how many IDs fall in each bucket type
  count(Month, Type.Bucket) %>%
  spread(Type.Bucket, n)


## Get month totals and join to month/type data frame
DF1_new %>%
  # Count ID by month
  group_by(Month) %>%
  summarise(Total = n()) %>%
  left_join(right, by = "Month")

Result:

Month       Total    Active.4-5    InActive.1-3
Jan-2019      6        1              1 
Feb-2019      2        NA             2

Upvotes: 1

Jon Spring
Jon Spring

Reputation: 66520

Here's a rough approach, could be tweaked to get the specific columns you want.

library(lubridate); library(tidyverse)
DF1 %>%
  count(Month = floor_date(Date, "month"), Type, ID) %>%
  mutate(bucket = case_when(n < 4 ~ "1-3", TRUE  ~ "4-5")) %>%
  count(Month, Type, bucket) %>%
  unite(column, Type:bucket) %>%
  spread(column, n, fill = 0)

## A tibble: 2 x 3
#  Month               `Active_4-5` `InActive_1-3`
#  <dttm>                     <dbl>          <dbl>
#1 2019-01-01 00:00:00            1              1
#2 2019-02-01 00:00:00            0              2

Data:

DF1 <- structure(list(ID = c("II-1", "II-1", "II-1", "II-1", "II-2", 
"II-2", "II-3", "II-4"), Value = c(150L, 175L, 165L, 168L, 200L, 
45L, 34L, 78L), Type = c("Active", "Active", "Active", "Active", 
"InActive", "InActive", "InActive", "InActive"), Date = structure(c(1546385658, 
1546385658, 1546385658, 1546385658, 1546737260, 1546737260, 1549307052, 
1549053194), class = c("POSIXct", "POSIXt"), tzone = "")), row.names = c(NA, 
-8L), class = "data.frame")

Upvotes: 0

Related Questions