CODEWITHSUNDEEP
r
GaryO
GaryO

Reputation: 6338

R - fill in values for all dates

I have a data set with sales by date, where date is not unique and not all dates are represented: my data set has dates (the date of the sale), quantity, and totalprice. This is an irregular time series.

What I'd like is a vector of sales by date, with every date represented exactly once, and quantities and totalprice summed by date, with zeros where there are no sales.

I have part of this now; I can make a sequence containing all dates:

first_date=as.Date(min(dates))
last_date=as.Date(max(dates))
all_dates=seq(first_date, by=1, to=last_date)

And I can aggregate the sales data by sale date:

quantitybydate=aggregate(quantity, by=list(as.Date(dates)), sum)

But not sure what to do next. If this were python I'd loop through one of the dates arrays, setting or getting the related quantity. But this being R I suspect there's a better way.

Upvotes: 1

Views: 1062

Answers (1)

IRTFM
IRTFM

Reputation: 263301

Make a dataframe with the all_dates as a column, then merge with quantitybydate using the by variable columns as the by.y, and all.x=TRUE. Then replace the NA's by 0.

Upvotes: 1

Related Questions