CODEWITHSUNDEEP
rcountif
Uncle Mutou
Uncle Mutou

Reputation: 1

How to do countif in R based on dates

I have this data in my excel files, and it has so much data to count if I do it in Excel. I want to count how many days in 1 month have a value of more than 50.

enter image description here

I'd like to turn it into something like :

enter image description here

Could someone help me to solve this?

Upvotes: 0

Views: 102

Answers (2)

akrun
akrun

Reputation: 887501

Another option is count with as.yearmon from zoo - filter the rows where 'Value' is greater than 50, then use count after converting to yearmon class with as.yearmon

library(dplyr)
library(zoo)
df %>% 
   filter(Value > 50) %>%
   count(month_year = as.yearmon(Date))

-ouptut

  month_year n
1   Jan 2010 3
2   Feb 2010 1

data

df <- structure(list(Date = structure(c(14610, 14611, 14612, 14618, 
14618, 14624, 14641), class = "Date"), Value = c(27, 35, 78, 
88, 57, 48, 99)), class = "data.frame", row.names = c(NA, -7L
))

Upvotes: 2

Martin Gal
Martin Gal

Reputation: 16988

Suppose your data is given by

df <- data.frame(Date = as.Date(c("1/1/2010", "1/2/2010", "1/3/2010", "1/9/2010", "1/9/2010", "1/15/2010", "2/1/2010"), "%m/%d/%Y"),
                 Value = c(27, 35, 78, 88, 57, 48, 99))

To count your specific values you could use

library(dplyr)

df %>%
  group_by(month_year = format(Date, "%m-%y")) %>%
  summarise(count = sum(Value > 50))

which returns

# A tibble: 2 x 2
  month_year count
  <chr>      <int>
1 01-10          3
2 02-10          1

Note: Your Date column has to contain dates (as in as.Date).

Upvotes: 1

Related Questions