I have two data.tables showing temperatures for multiple locations (identified by geocode).
I would like to create a third one based on a subtraction of them. Here they are:
library(data.table)
# Generate random data:
geocode <- paste0("N", 1:10)
dates <- seq(as.Date("2000-01-01"), as.Date("2004-12-31"), by="month")
models <- c("A", "B", "C", "D", "E")
temp <- runif(length(geocode)*length(dates)*length(models), min=0, max=30)
dt1 <- data.table(expand.grid(Location=geocode,Date=dates,Model=models),Temperature=temp)
ref <- runif(length(geocode), min=0, max=30)
dt2 <- data.table(expand.grid(Location=geocode), Temperature=ref)
I would like to conditionally subtract dt2 from dt1. By each location (geocode), I would like to subtract temperature in dt2 from temperature in dt1, preserving the other columns (Date and Model).
How to achieve this? I would know how to do if it was a single data table, but I've never tried to do algebra on two different data tables like this before.