Creating a Unique Sequence of Dates
As I noted in my comment, seq has method for dates, seq.Date:
seq(as.Date('2011-01-01'),as.Date('2011-01-31'),by = 1)
[1] "2011-01-01" "2011-01-02" "2011-01-03" "2011-01-04" "2011-01-05" "2011-01-06" "2011-01-07" "2011-01-08"
[9] "2011-01-09" "2011-01-10" "2011-01-11" "2011-01-12" "2011-01-13" "2011-01-14" "2011-01-15" "2011-01-16"
[17] "2011-01-17" "2011-01-18" "2011-01-19" "2011-01-20" "2011-01-21" "2011-01-22" "2011-01-23" "2011-01-24"
[25] "2011-01-25" "2011-01-26" "2011-01-27" "2011-01-28" "2011-01-29" "2011-01-30" "2011-01-31"
Generate Sequence of Dates and Time for each ID in R
You weren't too far off. Try this:
# I reproduce your data:
library(data.table)
data = data.table::fread(input =
"Patients,temperature
Patient1,37
Patient2,36
Patient3,35.4")
library(dplyr)
Time <- seq (from=as.POSIXct("2018-1-1 01:00"), to=as.POSIXct("2018-3-30 01:00", tz="UTC"), by="hour")
And this should do what you want:
data %>%
group_by(Patients) %>%
do({data.frame("temperature" = c(.data$temperature, rep(NA,length(Time) - nrow(.data))), Time)})
How to create sequences of dates in r
We can use seq on a Date class element (from, to) and specify the by as "1 month" along
out <- format(seq(as.Date("2005-01-01"), as.Date("2018-12-31"),
by = "1 month"), "%Y/%m")
head(out)
#[1] "2005/01" "2005/02" "2005/03" "2005/04" "2005/05" "2005/06"
tail(out)
#[1] "2018/07" "2018/08" "2018/09" "2018/10" "2018/11" "2018/12"
Create sequence of date on every last day of month
If you want last day of month, instead of start from 2018-01-31, try
seq(as.Date("2018-02-01",format="%Y-%m-%d"),by="month",length.out=6) -1
[1] "2018-01-31" "2018-02-28" "2018-03-31" "2018-04-30" "2018-05-31" "2018-06-30"
How to generate a sequence of dates given starting and ending dates using AWK of BASH scripts?
If you have gawk:
#!/usr/bin/gawk -f
{
split($1,s,"/")
split($2,e,"/")
st=mktime(s[3] " " s[1] " " s[2] " 0 0 0")
et=mktime(e[3] " " e[1] " " e[2] " 0 0 0")
for (i=et;i>=st;i-=60*60*24) print strftime("%m/%d/%Y",i)
}
Demonstration:
./daterange.awk inputfile
Output:
07/07/2009
07/06/2009
07/05/2009
07/04/2009
07/03/2009
07/02/2009
03/03/1996
03/02/1996
03/01/1996
02/29/1996
02/28/1996
01/04/2002
01/03/2002
01/02/2002
01/01/2002
12/31/2001
12/30/2001
Edit:
The script above suffers from a naive assumption about the length of days. It's a minor nit, but it could produce unexpected results under some circumstances. At least one other answer here also has that problem. Presumably, the date command with subtracting (or adding) a number of days doesn't have this issue.
Some answers require you to know the number of days in advance.
Here's another method which hopefully addresses those concerns:
while read -r d1 d2
do
t1=$(date -d "$d1 12:00 PM" +%s)
t2=$(date -d "$d2 12:00 PM" +%s)
if ((t2 > t1)) # swap times/dates if needed
then
temp_t=$t1; temp_d=$d1
t1=$t2; d1=$d2
t2=$temp_t; d2=$temp_d
fi
t3=$t1
days=0
while ((t3 > t2))
do
read -r -u 3 d3 t3 3<<< "$(date -d "$d1 12:00 PM - $days days" '+%m/%d/%Y %s')"
((++days))
echo "$d3"
done
done < inputfile
Generate sequence of dates for given frequency as per days of occurence
Working on larger sample, as discussed earlier in comments. Strategy followed -
- As your
daycolumn always start fromMonwhich is not equal tostart_dateso the column matchingweekdayis required. - So Created
dayfield to orderedfactortype so that it can be manipulatedit into integers. - Arranged the dataframe in such a way that your every group starts from that day only. Used modulo division
%%for this - After arranging the task was rather easier. I created seven dates for each weekday end, for each group and each start_date.
- Filtered out rows with
Y/Nas 0 anywhere. - Now you require only top row so used
slice_head()
df <- data.frame(
stringsAsFactors = FALSE,
Group = c("foo","foo","foo",
"foo","foo","foo","foo","foo","foo","foo",
"foo","foo","foo","foo","foo","foo","foo",
"foo","foo","foo","foo","bar","bar","bar",
"bar","bar","bar","bar","bar","bar","bar","bar",
"bar","bar","bar"),
start_date = c("02-06-2021",
"02-06-2021","02-06-2021","02-06-2021","02-06-2021",
"02-06-2021","02-06-2021","04-06-2021",
"04-06-2021","04-06-2021","04-06-2021","04-06-2021",
"04-06-2021","04-06-2021","06-06-2021","06-06-2021",
"06-06-2021","06-06-2021","06-06-2021",
"06-06-2021","06-06-2021","02-06-2021","02-06-2021",
"02-06-2021","02-06-2021","02-06-2021","02-06-2021",
"02-06-2021","05-06-2021","05-06-2021",
"05-06-2021","05-06-2021","05-06-2021","05-06-2021",
"05-06-2021"),
Day = c("Mon","Tue","Wed",
"Thu","Fri","Sat","Sun","Mon","Tue","Wed",
"Thu","Fri","Sat","Sun","Mon","Tue","Wed",
"Thu","Fri","Sat","Sun","Mon","Tue","Wed",
"Thu","Fri","Sat","Sun","Mon","Tue","Wed","Thu",
"Fri","Sat","Sun"),
y_n = c(0L,1L,0L,1L,1L,
1L,0L,0L,1L,0L,1L,1L,1L,0L,0L,1L,0L,1L,
1L,1L,0L,1L,0L,0L,1L,1L,0L,0L,1L,0L,
0L,1L,1L,0L,0L)
)
library(lubridate)
library(tidyverse)
df %>% group_by(Group, start_date) %>%
mutate(Day = factor(Day, levels = Day, ordered = T)) %>%
arrange(Group, (as.numeric(Day) + 7 - wday(dmy(start_date), week_start = 1)) %% 7, .by_group = T) %>%
mutate(next_available_date = dmy(start_date) + 0:6) %>%
filter(y_n !=0) %>%
slice_head()
#> # A tibble: 5 x 5
#> # Groups: Group, start_date [5]
#> Group start_date Day y_n next_available_date
#> <chr> <chr> <ord> <int> <date>
#> 1 bar 02-06-2021 Thu 1 2021-06-03
#> 2 bar 05-06-2021 Mon 1 2021-06-07
#> 3 foo 02-06-2021 Thu 1 2021-06-03
#> 4 foo 04-06-2021 Fri 1 2021-06-04
#> 5 foo 06-06-2021 Tue 1 2021-06-08
On the data provided
df <- data.frame(
stringsAsFactors = FALSE,
Group = c("foo","foo","foo",
"foo","foo","foo","foo","bar","bar","bar",
"bar","bar","bar","bar"),
start_date = c("02-06-2021",
"02-06-2021","02-06-2021","02-06-2021","02-06-2021",
"02-06-2021","02-06-2021","02-06-2021",
"02-06-2021","02-06-2021","02-06-2021","02-06-2021",
"02-06-2021","02-06-2021"),
Day = c("Mon","Tue","Wed",
"Thu","Fri","Sat","Sun","Mon","Tue","Wed",
"Thu","Fri","Sat","Sun"),
y_n = c(0L,1L,0L,1L,1L,
1L,0L,1L,0L,0L,1L,1L,0L,0L)
)
library(lubridate)
library(tidyverse)
df %>% group_by(Group, start_date) %>%
mutate(Day = factor(Day, levels = Day, ordered = T)) %>%
arrange(Group, (as.numeric(Day) + 7 - wday(dmy(start_date), week_start = 1)) %% 7, .by_group = T) %>%
mutate(next_available_date = dmy(start_date) + 0:6) %>%
filter(y_n !=0) %>%
slice_head()
#> # A tibble: 2 x 5
#> # Groups: Group, start_date [2]
#> Group start_date Day y_n next_available_date
#> <chr> <chr> <ord> <int> <date>
#> 1 bar 02-06-2021 Thu 1 2021-06-03
#> 2 foo 02-06-2021 Thu 1 2021-06-03
Created on 2021-06-02 by the reprex package (v2.0.0)
Creating a sequence of dates for each group in Python 3
Let us group the dataframe on RETAILER and use cumcount to create sequential counter per RETAILER then map this counter to MonthBegin offset and add a Timestamp('2000-01-01'):
c = df.groupby('RETAILER').cumcount()
df['DATE'] = pd.Timestamp('2000-01-01') + c.map(pd.offsets.MonthBegin)
RETAILER VOLUME DISP PRICE DATE
0 store1 12 15 10 2000-01-01
1 store1 10 8 17 2000-02-01
2 store1 12 13 12 2000-03-01
3 store2 22 22 30 2000-01-01
4 store2 17 14 22 2000-02-01
5 store2 23 18 18 2000-03-01
6 store3 11 13 10 2000-01-01
7 store3 12 13 13 2000-02-01
8 store3 14 12 11 2000-03-01
generate sequence of dates in SQL for each variable
In PostgreSql you can actually get those dates via a Recursive CTE
Test here on SQL Fiddle.
with RECURSIVE RECIPEDATES (recipe_date, recipe_name) AS
(
select min(recipe_date), recipe_name
from recipes
group by recipe_name
union all
select recipe_date + 1, recipe_name
from RECIPEDATES
where recipe_date <= current_date
)
select recipe_name, recipe_date
from RECIPEDATES
order by recipe_name, recipe_date;
Generating sequence of dates quarterly
The from and to dates in the question are both end-of-quarter dates so we assume that that is the general case you are interested in.
1) Create a sequence of yearqtr objects yq and then convert them to Date class. frac=1 tells it s to use the end of the month. Alternately just use yq since that directly models years with quarters.
library(zoo)
from <- as.Date('1980-12-31')
to <- as.Date('1985-06-30')
yq <- seq(as.yearqtr(from), as.yearqtr(to), by = 1/4)
as.Date(yq, frac = 1)
giving;
[1] "1980-12-31" "1981-03-31" "1981-06-30" "1981-09-30" "1981-12-31"
[6] "1982-03-31" "1982-06-30" "1982-09-30" "1982-12-31" "1983-03-31"
[11] "1983-06-30" "1983-09-30" "1983-12-31" "1984-03-31" "1984-06-30"
[16] "1984-09-30" "1984-12-31" "1985-03-31" "1985-06-30"
2) or without any packages add 1 to from and to so that they are at the beginning of the next month, create the sequence (it has no trouble with first of month sequences) and then subtract 1 from the generated sequence giving the same result as above.
seq(from + 1, to + 1, by = "quarter") - 1
Related Topics
Delete "" from CSV Values and Change Column Names When Writing to a CSV
Create Columns from Factors and Count
How to Generate Distributions Given, Mean, Sd, Skew and Kurtosis in R
R: Assign Variable Labels of Data Frame Columns
Split Character Data into Numbers and Letters
How to Concatenate Factors, Without Them Being Converted to Integer Level
Filter Function in Dplyr Errors: Object 'Name' Not Found
Re-Ordering Bars in R's Barplot()
Remove Duplicate Column Pairs, Sort Rows Based on 2 Columns
Expand Spacing Between Tick Marks on X Axis
Reproducing Lattice Dendrogram Graph with Ggplot2
Is There a Built-In Way to Do a Logarithmic Color Scale in Ggplot2
Euclidean Distance of Two Vectors
Rstudio Not Picking the Encoding I'm Telling It to Use When Reading a File
Error in Model.Frame.Default: Variable Lengths Differ
Get Row and Column Indices of Matches Using 'Which()'