Subset data by month in R

I am working with a time series of meteorological data and want to extract just the summer months. The data frame looks like this:

FECHA;H_SOLAR;DIR_M;DIR_S;VEL_M;VEL_S;VEL_X;U;V;TEMP_M;HR;BAT;PRECIP;RAD;UVA;UVB;FOG;GRID;
00/01/01;23:50:00;203.5;6.6;2.0;0.5;-99.9;-99.9;-99.9;6.0;-99.9;9.0;-99.9;-99.9;-99.9;-99.9;-99.9;-99.9
00/01/02;23:50:00;235.5;7.5;1.8;0.5;-99.9;-99.9;-99.9;6.1;-99.9;8.9;-99.9;-99.9;-99.9;-99.9;-99.9;-99.9
00/01/03;23:50:00;217.4;6.1;1.4;0.5;-99.9;-99.9;-99.9;7.0;-99.9;8.9;-99.9;-99.9;-99.9;-99.9;-99.9;-99.9
00/01/04;23:50:00;202.5;8.6;1.8;0.5;-99.9;-99.9;-99.9;6.4;-99.9;8.8;-99.9;-99.9;-99.9;-99.9;-99.9;-99.9
00/01/05;23:50:00;198.5;7.1;1.8;0.5;-99.9;-99.9;-99.9;5.4;-99.9;8.8;-99.9;-99.9;-99.9;-99.9;-99.9;-99.9


I have found some examples of time subsetting in R but only between an starting and end date. What I want is to extract all data from a month for all years to create a new data frame to work with. I can create a zoo time series from the data but how do I subset? zoo aggregate?

-

You could use xts::.indexmon. Creating an xts object is similar to creating a zoo object. Assuming xData is your xts object, something like xData[.indexmon(xData) %in% c(5,6,7)] should do what you want.

-

Thank you very much for your help. Let me write the three solutions working for me.

The first one is the answer form Joshua Ulrich:

Read the data, create an xts object and use indexmon

data=read.csv("peira.dat",sep=";",header=T,na.strings="-99.9")
dia=as.Date(data[,1],"%y/%m/%d")
xdata=xts(data[,c("PRECIP")],dia)
mdat=xdata[.indexmon(xdata) %in% c(5,6,7)]


Here is a plot of mdat

The second solution came from the spanish R mailing list and uses lubridate package:

library(lubridate)

f.dat<-parse_date(data$FECHA, c("%y", "%m", "%d"), seps="/") data$m.dat<-month(f.dat, label=F, abbr=F)
dat.gd<-data[data$m.dat>5 & data$m.dat<9,]
dia=as.Date(dat.gd[,1],"%y/%m/%d")
dataz=zoo(dat.gd[,c("PRECIP")],dia)


The plot of dataz:

And the last one also from the spanish mailing list it uses POSIXct to take into account both date and time

tt=as.POSIXct(paste(data$FECHA,data$H_SOLAR), format="%y/%m/%d %H:%M:%S")

datZoo <- zoo(data[,-c(1,2)], tt)

month <- function (x) as.numeric(format(x, "%m"))
veranoIdx <- which(month(tt) %in% 6:8)
veranoZoo <- datZoo[veranoIdx]
veranoZoo

-

You could extract the months out your day/month/year column to a new column called 'month' using substring() e.g.,

mydata$month <- as.numeric( substring( mydata$dayMonthYear, first = 4, last = 5) )


This should create a column that just contains the months.

then subset based on the values of \$month

summer.months <- subset(mydata, month > 5 & month < 9)


or

summer.months <- subset(mydata, month %in% c(5:9) )

-
Does this work with zoo objects? I can't seem to make this work for a "regular" ts object. –  Chase Jul 13 '11 at 12:17
I'm not familiar with zoo objects to say. As a kludge could you coerce the day/month/year column to text? –  Chris Jul 13 '11 at 12:55
Dear Chris, I can't figure out exactly the way to use grep. I've googled for grep syntax but get some errors and then I do not only grep month 05 but also day 05. Could you please post a detailed example. I'll add a new answer with the results I'm getting but I have to wait some hours as previously said in response to Chase. –  pacomet Jul 13 '11 at 14:11
@Pacomet - you can update your question with additional details, there shouldn't be any time restriction on that. –  Chase Jul 13 '11 at 15:12
done, substring() actually might work better here than grep(). Your error with grep() might be because you were too specific. I'm guessing you were passing 05 in the search string, instead of [[numeric]], and not limiting the string to what was between the backslashes. substring() gets around that. –  Chris Jul 13 '11 at 16:15

One option is to use the cycle() function which gives the position in the cycle of each observation. For example:

gnp <- ts(cumsum(1 + round(rnorm(100), 2)),
start = c(1954, 7), frequency = 12)

> cycle(gnp)
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1954                           7   8   9  10  11  12
1955   1   2   3   4   5   6   7   8   9  10  11  12
1956   1   2   3   4   5   6   7   8   9  10  11  12
..............


Then to subset a particular month, use the index of that month:

#Get all data from July
subset(gnp, cycle(gnp) == 7)


I should note that this returns a numeric vector, which may or may not be an issue for you depending on what you want to do from there. I'm curious to see other solutions as well.

-
Dear Chase, I've tried your sugggestion but get stuck on the first point. The ts(cumsum....) command does not give that integer numbers for each month. Later I will post the results as an answer to the question so you can see, the system makes me wait some hours to answer my own questions. Thanks. –  pacomet Jul 13 '11 at 14:08
is your data a ts object, or some other object? In order for cycle to work, your object needs to be a ts object and to have a frequency component associated with it. It isn't clear to me what your data above represents. Can you update your question with the results of dput(yourData)? –  Chase Jul 13 '11 at 14:19
As your answer was the first one, Chase. I can't just start your suggestion. Take a look at the result of ts(cumsum....) It shows such a different result I can't understand gnp <- ts(cumsum(1 + round(rnorm(100), 2)),start = c(1954, 7), frequency = 12)  This gives ** Jan Feb Mar Apr May Jun Jul Aug Sep Oct 1954 -1.55 0.44 1.28 0.32 1955 3.61 4.65 4.40 4.89 5.11 5.93 7.48 7.83 9.53 10.87 1956 15.45 16.22 17.43 20.89 22.89 25.45 26 ** not the integer numbers from your example, why?? –  pacomet Jul 14 '11 at 7:46
@pacomet - ok, i clearly failed at my example. Above, gnp is an arbitrary example of a time series object (actually it comes from the help page for ?ts). When making that time series object, we define a start year and month (1954 and month == 7), and then also specify that the number of observations per unit of time is 12 (monthly data). That time series object now has some "meta-data" associated with it, including the position of each observation which can be accessed by cycle(). Since that information is directly linked to the ts object itself, we can use it for subsetting / indexing –  Chase Jul 14 '11 at 11:34
purposes. This is fundamentally the same concept as @Joshua showed you using xts which defined an index for your time series object. For an illustrative example, try running  cbind(gnp, cycle(gnp), time(gnp)) in your console and viewing what is returned. As a note for future questions - the answers you get will be much more on target if you provide the exact structure of your data with dput(), as the answers will be different depending on the structure of your data, i.e. is it a data.frame? xts object? ts object? matrix? list? vector? –  Chase Jul 14 '11 at 11:36
show 1 more comment