CODEWITHSUNDEEP
r
user3281487
user3281487

Reputation: 97

How do I add a column to data frame with a letter representing a date range in another column

I have a large environmental dataset where I want to add a column and then assign a letter to the rows in the new column A, B, C, ... L based on a date range in another column. My code so far is

prec <- read.csv("precipitation.csv") #read file
prec_DI <- subset(prec, Location=="Dauphin Island") #subset one location

This creates an output like such:

enter image description here

prec_DI$Date <- as.Date(strptime(prec_DI$Date, format="%Y%m%d")) #convert date column to R format
df.DI<-data.frame(sumPrec=tapply(prec_DI$Prec, factor(prec_DI$Date), sum)) #sum all values for each date
df.DI$Date<-row.names(df.DI) #add a row names column
DI10 <- subset(df.DI, Date>="2010-05-17"& Date<="2010-10-31") #subset data based on a date range

Now the output looks like this: enter image description here

I don't care about the row names being the same as the Date column, I can easily change that. I tried to add a new empty column by using

DI10$Period <- DI10

but this created: enter image description here

Instead, I want to create a new column where date range 2010-05-17 - 2010-06-03 is assigned "A", range 2010-03-04 - 2010-03-16 is assigned "B", and so on. I would greatly appreciate any help on this, I'm sure it's a straightforward question but I am just starting to learn.

As requested I am adding this output

structure(list(sumPrec = structure(c(0.4, 1.6, 1.9, 1.3, 1.4, 1.7), .Dim = 6L,
.Dimnames = list(c("2010-05-18", "2010-05-26", "2010-05-29", "2010-05-30",
"2010-05-31", "2010-06-01"))), Date = c("2010-05-18", "2010-05-26", 
"2010-05-29", "2010-05-30", "2010-05-31", "2010-06-01" )), 
.Names = c("sumPrec", "Date"), row.names = c("2010-05-18", "2010-05-26", 
"2010-05-29", "2010-05-30", "2010-05-31", "2010-06-01" ), class = "data.frame")

Upvotes: 0

Views: 892

Answers (1)

Weihuang Wong
Weihuang Wong

Reputation: 13118

There are a couple of ways to do it. First, convert your date column to a POSIXct class object, representing a calendar date:

DI10$Date <- as.POSIXct(as.Date(DI10$Date))

Then, you can either use the cut.POSIXt() function

DI10$Period <- cut.POSIXt(
  DI10$Date, 
  breaks = as.POSIXct(as.Date(c("1970-01-01", "2010-05-17", "2010-05-29", "9999-12-31"))),
  labels = c("C", "A", "B"))

which cuts your date range using the breaks provided (where the first and last date represent the outer bounds); or you can make a dataframe with start/end dates and labels, e.g.

dates <- data.frame(Start = c("2010-05-17", "2010-03-04"),
                    End =   c("2010-05-28", "2010-03-16"),
                    Label = c("A", "C"),
                    stringsAsFactors = FALSE)
dates$Start <- as.POSIXct(as.Date(dates$Start))
dates$End <- as.POSIXct(as.Date(dates$End))

and then use sapply to iterate through your dates to do the Period assignment

DI10$Period <- sapply(DI10$Date, function(x) {
  out <- dates$Label[x >= dates$Start & x <= dates$End]
  if (length(out) == 0) "B" else out
})

Output:

DI10
#            sumPrec                Date Period
# 2010-05-18     0.4 2010-05-18 08:00:00      A
# 2010-05-26     1.6 2010-05-26 08:00:00      A
# 2010-05-29     1.9 2010-05-29 08:00:00      B
# 2010-05-30     1.3 2010-05-30 08:00:00      B
# 2010-05-31     1.4 2010-05-31 08:00:00      B
# 2010-06-01     1.7 2010-06-01 08:00:00      B

Upvotes: 1

Related Questions