Reputation: 272
R language, count the Friday in a month
I have a data.frame named ll with all the dates between 2014 and 2014. Also I have a column named Flag_new_month which is 1 if it's the first day of a new month and 0 otherwise. Last I have a column named Flag_Friday which is 1 if it's Friday and 0 otherwise.
Here's an example:
Dates Month Weekday Flag_new_month Flag_Friday
2014-01-30 1 Thursday 0 0
2014-01-31 1 Friday 0 1
2014-02-01 2 Saturday 1 0
My goal is to obtain a counter of all the Friday in a month that resets to zero when a new month occurs. I tried to do that with no success using a simple ifelse statement:
count=0
ll$Count_Friday = ifelse(ll$Flag_Friday == 1 && ll$Flag_new_month == 0, count+1,0)
Any idea will be appreciated.
P.S.: First of all thanks for the quick support but maybe I didn't explain properly. My goal is to obtain another column named Count_Friday in my data.frame that pinpoints the number of Friday in that month. The variable Count_Friday increases in value every time finds Flag_Friday == 1 in a month. When Flag_new_month == 1 then the variable Count_Friday resets to zero. For example:
Dates Month Weekday Flag_new_month Flag_Friday Count_Friday
2014-01-15 1 Wednesday 0 0 0
2014-01-16 1 Thursday 0 0 0
2014-01-17 1 Friday 0 1 1
2014-01-18 1 Saturday 0 0 0
2014-01-19 1 Sunday 0 0 0
2014-01-20 1 Monday 0 0 0
2014-01-21 1 Tuesday 0 0 0
2014-01-22 1 Wednesday 0 0 0
2014-01-23 1 Thursday 0 0 0
2014-01-24 1 Friday 0 1 2
2014-01-25 1 Saturday 0 0 0
2014-01-26 1 Sunday 0 0 0
2014-01-27 1 Monday 0 0 0
2014-01-28 1 Tuesday 0 0 0
2014-01-29 1 Wednesday 0 0 0
2014-01-30 1 Thursday 0 0 0
2014-01-31 1 Friday 0 1 3
2014-02-01 2 Saturday 1 0 0
2014-02-02 2 Sunday 0 0 0
2014-02-03 2 Monday 0 0 0
2014-02-04 2 Tuesday 0 0 0
2014-02-05 2 Wednesday 0 0 0
2014-02-06 2 Thursday 0 0 0
2014-02-07 2 Friday 0 1 1
Thanks again for the support and hope to be more clear now.
Upvotes: 2
Views: 358
Answers (3)
Reputation: 52637
I think you're relatively close, but there are a couple of things that are off. First, you're using && instead of &. Only the latter is vectorized (you use the former with if/else, and the latter with ifelse). Additionally, the second condition doesn't work because Flag_new_month isn't always 1 on the Friday. I believe this does what you want:
ll$Count_Friday <-
with(ll, ifelse(Flag_Friday, ave(Flag_Friday, Month, FUN=cumsum), 0))
If you notice I don't use Flag_new_month, instead I use ave to break up the Flag_Friday column by month, and then within each month, do a cumulative sum of the Fridays. The ifelse is just so that I only populate this cumulative sum value for Fridays (otherwise every day following a Friday up to the next Friday would have the same number as Count_Friday).
And here is the result. Interestingly 2014 had 5 Fridays in the first month.
head(ll, 40)
# Dates Month Weekday Flag_new_month Flag_Friday Count_Friday
# 1 2014-01-01 1 Wednesday 1 0 0
# 2 2014-01-02 1 Thursday 0 0 0
# 3 2014-01-03 1 Friday 0 1 1
# 4 2014-01-04 1 Saturday 0 0 0
# 5 2014-01-05 1 Sunday 0 0 0
# 6 2014-01-06 1 Monday 0 0 0
# 7 2014-01-07 1 Tuesday 0 0 0
# 8 2014-01-08 1 Wednesday 0 0 0
# 9 2014-01-09 1 Thursday 0 0 0
# 10 2014-01-10 1 Friday 0 1 2
# 11 2014-01-11 1 Saturday 0 0 0
# 12 2014-01-12 1 Sunday 0 0 0
# 13 2014-01-13 1 Monday 0 0 0
# 14 2014-01-14 1 Tuesday 0 0 0
# 15 2014-01-15 1 Wednesday 0 0 0
# 16 2014-01-16 1 Thursday 0 0 0
# 17 2014-01-17 1 Friday 0 1 3
# 18 2014-01-18 1 Saturday 0 0 0
# 19 2014-01-19 1 Sunday 0 0 0
# 20 2014-01-20 1 Monday 0 0 0
# 21 2014-01-21 1 Tuesday 0 0 0
# 22 2014-01-22 1 Wednesday 0 0 0
# 23 2014-01-23 1 Thursday 0 0 0
# 24 2014-01-24 1 Friday 0 1 4
# 25 2014-01-25 1 Saturday 0 0 0
# 26 2014-01-26 1 Sunday 0 0 0
# 27 2014-01-27 1 Monday 0 0 0
# 28 2014-01-28 1 Tuesday 0 0 0
# 29 2014-01-29 1 Wednesday 0 0 0
# 30 2014-01-30 1 Thursday 0 0 0
# 31 2014-01-31 1 Friday 0 1 5
# 32 2014-02-01 2 Saturday 1 0 0
# 33 2014-02-02 2 Sunday 0 0 0
# 34 2014-02-03 2 Monday 0 0 0
# 35 2014-02-04 2 Tuesday 0 0 0
# 36 2014-02-05 2 Wednesday 0 0 0
# 37 2014-02-06 2 Thursday 0 0 0
# 38 2014-02-07 2 Friday 0 1 1
# 39 2014-02-08 2 Saturday 0 0 0
# 40 2014-02-09 2 Sunday 0 0 0
Here is how I made the data:
ll <- data.frame(Dates=seq(as.Date("2014-01-01"), len=365, by="1 day"))
ll <- transform(
ll,
Month=month(Dates),
Weekday=weekdays(Dates),
Flag_new_month=diff(c(0, month(Dates))),
Flag_Friday=as.integer(weekdays(Dates) == "Friday")
)
Upvotes: 0
Reputation: 54340
A fully vectorized way of doing it:
> df<-data.frame(cbind(c(0,0,0,1,0,0,1,0,0,0), c(1,0,1,0,1,0,1,0,1,0)))
> colnames(df)<-c('Month', 'Friday')
> df
Month Friday
1 0 1
2 0 0
3 0 1
4 1 0
5 0 1
6 0 0
7 1 1
8 0 0
9 0 1
10 0 0
> cumsum(df$Friday)-cummax(ifelse(df$Month==1, c(0,cumsum(df$Friday)), 0))
[1] 1 1 2 0 1 1 1 1 2 2
Upvotes: 1
Reputation: 13280
You can use for example the plyr package for this:
df <- read.table(header = TRUE, text = 'Dates Month Weekday Flag_new_month Flag_Friday
2014-01-30 1 Thursday 0 0
2014-01-31 1 Friday 0 1
2014-02-01 2 Saturday 1 0
2014-02-07 2 Friday 1 1
2014-02-14 2 Friday 1 1')
head(df)
require(plyr)
ddply(df, .(Month), summarise,
n_friday = sum(Flag_Friday))
Or just simply tapply:
tapply(df$Flag_Friday, df$Month, sum)
Upvotes: 1
Related Questions
- How to get every third Friday of every third month in R
- R find last weekday of month
- add new column represent the number of occurrence of weekday within the specific month in R dataframe
- Counting number of days in months in r
- Count the number of Fridays or Mondays in Month in R
- count weekdays between sys.date and each date in column in R
- How to count the number of days in a given month in a given year in R
- Counting days in month in R
- Count days in each month for week overlapping two months
- How to figure third Friday of a month in R