Q:

R update values based on event

I recently posted this question already related to my Mysql database which I am using locally on my laptop. Since I did not find a solution to the problem in Mysql and others did not seem to find one either I would like to post it again but now related to R. I use the database with the "RMysql" package. Here is the problem:
I have a database table that contain data of different farming pratices during several years on several fields. Farmers entered there information based on the dates when a certain activity was done. A simplified example would look like this, where ID is an auto-increment value.
ID|Field|Date      |Activity  |
 1|A    |2012/08/01|Tillage   |
 2|A    |2012/08/24|Seeding   |
 3|A    |2013/03/05|Spraying  |
 4|A    |2013/03/05|Fertilizer|
 5|A    |2013/07/25|Harvest   |
 6|B    |2012/09/01|Tillage   |
 7|B    |2012/09/05|Seeding   |
 8|B    |2013/04/05|Spraying  |
 9|B    |2013/07/28|Harvest   |
10|B    |2010/08/24|Tillage   |
11|B    |2010/09/29|Seeding   |
12|B    |2011/05/01|Fertilizer|
13|B    |2011/07/12|Harvest   |
14|A    |2011/09/01|Seeding   |
15|A    |2011/10/10|Spraying  |
16|A    |2012/04/10|Fertilizer|
17|A    |2012/08/02|Harvest   |

Now I would like to add a column called "Season" that contains only the year when a field was harvested but is automatically updated for all other activites between the last harvest and this harvest for individual fields. Note that harvest dates are different for different fields.Another point is that the seeding was maybe done in 2011 but the field was harvested in 2012 and all this should then be called season 2012. In this example the output would ideally look like:
ID|Field|Date      |Activity  |Season
 1|A    |2012/08/01|Tillage   |2013
 2|A    |2012/08/24|Seeding   |2013
 3|A    |2013/03/05|Spraying  |2013
 4|A    |2013/03/05|Fertilizer|2013
 5|A    |2013/07/25|Harvest   |2013
 6|B    |2012/09/01|Tillage   |2013
 7|B    |2012/09/05|Seeding   |2013
 8|B    |2013/04/05|Spraying  |2013
 9|B    |2013/07/28|Harvest   |2013
10|B    |2010/08/24|Tillage   |2011
11|B    |2010/09/29|Seeding   |2011
12|B    |2011/05/01|Fertilizer|2011
13|B    |2011/07/12|Harvest   |2011
14|A    |2011/09/01|Seeding   |2012
15|A    |2011/10/10|Spraying  |2012
16|A    |2012/04/10|Fertilizer|2012
17|A    |2012/08/02|Harvest   |2012

Can anyone help?
EDIT:
What would I need to change if I start recording more information and give all "Activities" a separate column?
I tried:
DF <- read.table(text="ID|Field|Date      |Tillage|Seeding|Fertilizer|Spraying|Harvest
 1|A    |2012/08/01|Plough   |NA|NA|NA|NA
 2|A    |2012/08/24|NA   |Wheat|NA|NA|NA
 3|A    |2013/03/05|NA  |NA|NA|ProduktA|NA
 4|A    |2013/03/05|NA|NA|TypeB|NA|NA
 5|A    |2013/07/25|NA   |NA|NA|NA|9t
 6|B    |2012/09/01|Plough   |NA|NA|NA|NA
 7|B    |2012/09/05|NA   |Barley|NA|NA|NA
 8|B    |2013/04/05|NA  |NA|NA|ProductB|NA
 9|B    |2013/07/28|NA   |NA|NA|NA|10t
10|B    |2010/08/24|Cultivator   |NA|NA|NA|NA
11|B    |2010/09/29|NA   |NA|NA|NA|NA
12|B    |2011/05/01|NA|NA|TypeB|NA|NA
13|B    |2011/07/12|NA   |NA|NA|NA|6t
14|A    |2011/09/01|NA   |Barley|NA|NA|NA
15|A    |2011/10/10|NA  |NA|NA|ProductC|NA
16|A    |2012/04/10|NA|NA|TypeA|NA|NA
17|A    |2012/08/02|NA   |NA|NA|NA|7t|", 
                 sep="|", header=TRUE, stringsAsFactors=FALSE)

DT <- data.table(DF)
DT[, Harvest:=gsub(" ", "", Harvest, fixed=TRUE)]
DT[, Date:=as.POSIXct(Date)]
setkeyv(DT, c("Field", "Date"))
DT[, Season:=cumsum(c("", !is.na(head(Harvest, -1)))), by=Field]
DT[, Season:=max(year(Date)), by=list(Field, Season)]

This does unfortunately not work. Can someone solve this,too?

A:

Assuming there was a harvest recorded for each season on each field (a dangerous assumption, but otherwise I see no way to do this):
DF <- read.table(text="ID|Field|Date      |Activity  |
 1|A    |2012/08/01|Tillage   |
 2|A    |2012/08/24|Seeding   |
 3|A    |2013/03/05|Spraying  |
 4|A    |2013/03/05|Fertilizer|
 5|A    |2013/07/25|Harvest   |
 6|B    |2012/09/01|Tillage   |
 7|B    |2012/09/05|Seeding   |
 8|B    |2013/04/05|Spraying  |
 9|B    |2013/07/28|Harvest   |
10|B    |2010/08/24|Tillage   |
11|B    |2010/09/29|Seeding   |
12|B    |2011/05/01|Fertilizer|
13|B    |2011/07/12|Harvest   |
14|A    |2011/09/01|Seeding   |
15|A    |2011/10/10|Spraying  |
16|A    |2012/04/10|Fertilizer|
17|A    |2012/08/02|Harvest   |", 
                 sep="|", header=TRUE, stringsAsFactors=FALSE)

library(data.table)
DT <- data.table(DF[, 1:4])
DT[, Activity:=gsub(" ", "", Activity, fixed=TRUE)]
DT[, Date:=as.POSIXct(Date)]
setkeyv(DT, c("Field", "Date"))
DT[, Season:=cumsum(c("", head(Activity, -1)) == "Harvest"), by=Field]
DT[, Season:=max(year(Date)), by=list(Field, Season)]
#     ID Field       Date   Activity Season
#  1: 14 A     2011-09-01    Seeding   2012
#  2: 15 A     2011-10-10   Spraying   2012
#  3: 16 A     2012-04-10 Fertilizer   2012
#  4:  1 A     2012-08-01    Tillage   2012
#  5: 17 A     2012-08-02    Harvest   2012
#  6:  2 A     2012-08-24    Seeding   2013
#  7:  3 A     2013-03-05   Spraying   2013
#  8:  4 A     2013-03-05 Fertilizer   2013
#  9:  5 A     2013-07-25    Harvest   2013
# 10: 10 B     2010-08-24    Tillage   2011
# 11: 11 B     2010-09-29    Seeding   2011
# 12: 12 B     2011-05-01 Fertilizer   2011
# 13: 13 B     2011-07-12    Harvest   2011
# 14:  6 B     2012-09-01    Tillage   2013
# 15:  7 B     2012-09-05    Seeding   2013
# 16:  8 B     2013-04-05   Spraying   2013
# 17:  9 B     2013-07-28    Harvest   2013

PS: I think this should also be possible (and more efficient) with a rolling join, but couldn't see immediately how to do that.

