I have the following table:
id money date
1 15 2009-02-07
1 30 2009-02-09
1 45 2009-03-04
1 50 2009-03-12
I would like to get the maximum amount of money spent in each fortnight. For example, for the first fortnight of February it would have to be 30 and for March 50. How could I do this in a sql query in R with the sqldf library?
I don't use any database, I have them in .csv files and import them in a dataframe. The problem I would have is that the period of time in which I have to do this covers several years, so I would not know very well how to do it.
I haven't touched SQLDF, so I'm posting this so that it can serve as a guide and/or help. Assuming that the sqldf backend is SQLite, the select you are looking for could be the same or similar to the following
Regarding the operation, we use strftime to separately extract the week of the year with %W and the specific year with %Y.
I propose a solution using
tidyverse
andlubridate
that contemplates that it returns all the fortnights (although there is no data for that fortnight) and also considers fortnights that go from 1-15 and from the 15-last day of the month.I build the example:
Now I generate a dataframe that has all the last days of the fortnight, from the date on which data begins to
df
the date on which it ends, adding a column calleddinero
, with the value0
for all the dates (which I am going to use later):floor_date
withunit = "month"
takes the first day of the month (1
) andceiling_date() - 1
takes the last (can be 28, 29, 30 or 31).Then I do a
full_join
, and considerdinero
ifdf$dinero
it is notNA
and if notdates$dinero
, which I defined as 0 for all cases.There you are ordered by year, month and fortnight 1 or 2 of the month.