R:: Dplyr: How to get each Monday in a specific date range + count it

vie2bgd 0
Viewed 84

I have data on when an ATP Tennis tournament took place with two columns in the following format:

Tournament Date Range
Australian Open 20.01.2020 - 02.02.2020

Now the goal is to predict the participation but solely for each Monday, if the date range contains a Monday of course! Also since once you lose you are out in Tennis, I am assuming that the participation in the second week is higher since then only the good players are left in the tournament. Which is why we would need to know if it is week one or two in the tournament. Hence for the above example we would need something like this at the end:

| Tournament | Date | Number of week

|Australian Open |20.01.2022| 1 |

|Australien Open |27.01.2022| 2 |

I know that I can count in dplyr but how would you get "only Monday" that is compatible with Dplyr? Essentially in SQL "where DAYOFWEEK(Column) = 2)".

I guess first one would need to separate the date range into two columns? Search function didn't yield nothing covering such a specific problem, hence it could help someone in future. Cheers vie2bgd ############################################# #############################################

Edit: after working day and night I almost have it but am missing on the last step ... also sorry but it's my first post, no need to ghost me or give me immediate minus points thanks @NirGraham at least giving me some hints, much appreciated and will try to implement it but technically I shared data up there in line with an instruction here how to do it, simply separating by | simply forgot some point "... "... here is what I did so far:

#first I separated the initial range into 2 columns
tennis.orf.2020.2 = tennis.orf.2020 %>% separate(Datum, c("Start", "End"), sep = " - ")

x=tennis.orf.2020.2 %>%
  mutate(across(c(Start, End), as.Date, "%d.%m.%y")) %>% 
  transmute(Tournament, date = map2(Start, End, seq, by = 'day')) %>% 
  unnest(c(date)) %>% 
  filter(wday(date) == 2) %>%
  count(Tournament,date)
Tournament Date Number of week
Australian Open 2020-01-20 1
Australian Open 2020-01-27 1

This should be the result:

Tournament Date Number of week
Australian Open 2020-01-20 1
Australian Open 2020-01-27 2

If I group by tournament I lose a row :( ################################################

EDIT2: Nevermind got it finally although it makes zero sense,

hopefully this will help somebody out there and save at least somebody valuable time

x%>%
  arrange(date) %>%
  group_by(Tournament) %>%
  mutate(dummy = 1) %>%
  mutate(times = cumsum(dummy)) %>%
  select(-dummy)
dplyr r
1 Answers

You can use row_number() helper function for this:

x%>%
  arrange(date) %>%
  group_by(Tournament) %>%
  mutate(times = row_number())

This is a more concise equivalent of your code with the cumsum(dummy).

Gregor Thomas0
Related