I have data that looks like the following:
> head(z, 10)
date year long lat
1 01/18/2017 2017 -92.48474 29.76465
2 01/22/2017 2017 -93.11126 29.83961
3 12/28/2013 2013 -91.30789 29.41938
4 01/08/2014 2014 -93.09949 29.80632
5 01/03/2014 2014 -90.55703 29.44535
6 12/31/2013 2013 -90.39836 29.57244
7 2013 -93.56322 30.30028
8 11/24/2013 2013 -93.45932 29.78530
9 11/19/1994 1994 -93.58333 29.75000
10 11/15/2013 2013 -89.16171 29.45222
There are multiple entries on some days, whereas some entries do not have a date. Those without a date I'm not interested in. What I want to know is how many records there are for each date and to insert missing days, when none records were created, so there is a record for each day of the year for each year whether data was recorded or not, such as:
> head(z2)
m_d y_2017 y_2016 y_2015 y_2014 y_2013
1 01-02 16 15 0 29 9
2 01-03 0 38 25 10 3
3 01-04 13 20 14 5 7
4 01-05 19 0 3 0 16
5 01-06 34 25 29 33 24
6 01-07 3 10 5 34 7
Using the aggregate function I have been able to figure out how many records there were for each day.
> #create a value for the aggregate function to sum
z$count<-rep(1, length(z$year))
m<-aggregate(count ~ date, data = z, sum)
> head(m)
date count
1 308
2 01/01/1980 1
3 01/01/1985 1
4 01/01/1995 1
5 01/01/1996 2
6 01/01/1997 1
I have no idea how to go from this table, which is the information I need, into the format that I want in a resourceful manner. I could manually subset by year and merge the data from each year with a complete set of months/days for that year, then create a new df using all the different years, but this seems overly cumbersome and repetitive since I have data going back to 1980. Anyone know of an efficient way of reorganizing this data into the above format?
You can easily create a reference dataframe with all dates from 1980 to present:
df$date <- as.Date(df$date, format = "%m/%d/%Y")
all_dates <- seq(from = as.Date("1980-01-01"), to = as.Date("2018-05-02"), by = 'days'))
ref_dates = data.frame(date = all_dates)
df <- merge(df, ref_dates, all.y = TRUE)
df$date <- substring(df$date, 6,10) # remove year from date column
df_table <- table(df$date, df$year) # cross tab
final_df <- as.data.frame.matrix(df_table) # convert into dataframe if you like