I have a .csv file with monthly mean data in two columns:
Date, Mean
200601, 45
200602, 93
200603, 76
..
..
..
2001005, 54
(date format is yyyymm)
I want to calculate average of Jan months of each year i.e.(Jan 2006, Jan 2007..till Jan 2010) and similarly for Feb...till Dec.
I am trying to use aggregate function in R to do so but due to yearmonth date format (not having day), I am getting error. Can anyone please help.
First extract the Month part from the Date, then use aggregate
df$Month <- sub("^\\d{4}", "", df$Date)
aggregate(Mean ~ Month, df, mean)
Month Mean
1 01 39.33333
2 02 34.00000
3 03 33.00000
4 04 50.50000
5 05 27.00000
6 06 52.50000
7 07 40.50000
8 08 55.50000
9 09 64.50000
10 10 65.50000
11 11 65.00000
12 12 25.00000
df <- structure(list(Date = c("200601", "200602", "200603", "200604",
"200605", "200606", "200607", "200608", "200609", "200610", "200611",
"200612", "200701", "200702", "200703", "200704", "200705", "200706",
"200707", "200708", "200709", "200710", "200711", "200712", "200801"
), Mean = c(49L, 65L, 25L, 74L, 18L, 100L, 47L, 24L, 71L, 89L,
37L, 20L, 26L, 3L, 41L, 27L, 36L, 5L, 34L, 87L, 58L, 42L, 93L,
30L, 43L)), class = "data.frame", row.names = c(NA, -25L))