Problem
Suppose I have two variables: (1) heat index for each county in a state, $h_{it}$, and (2) acres in each county, $acres_{it}$. The data has 10 years and also includes a variable for the amount of ice cream melted, $y_{it}$ for each county and year in the sample.
I'm told that a strong predictor of ice cream melt can be found by weighting the heat index by the size of the county and then aggregate to state-level data, such that:
$$\frac{\sum_{i} h_{it} \cdot acres_{it}}{\sum_{i} acres_{it}} = h_{st}$$
A simple linear regression can then predict the state-level ice cream melt by:
$$y_{st} = \beta_{1}h_{st} + \epsilon_{it}$$
Question
My question is related to the weighting. Is there a specific name for this type of weight and how is the weighting performed?
To me, I'm thinking about it as subsetting each year, applying the weight for all counties in that year, and then aggregate down to state level data. This would provide a heat index for each state in each year.
Is this the appropriate way to do this type of weighting?
R Code:
In R I would do something like this:
library(dplyr)
# Sample Data
dat <- structure(list(year = c(2000L, 2001L, 2002L, 2000L, 2001L, 2002L,
2000L, 2001L, 2002L, 2000L, 2001L, 2002L), county = c(1L, 1L,
1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L), state = c("CA", "CA",
"CA", "CA", "CA", "CA", "CO", "CO", "CO", "CO", "CO", "CO"),
y = c(5L, 10L, 7L, 4L, 2L, 8L, 9L, 11L, 2L, 5L, 6L, 8L),
h = c(5L, 7L, 1L, 9L, 6L, 4L, 8L, 2L, 5L, 8L, 7L, 1L), acres = c(10L,
25L, 40L, 8L, 13L, 42L, 50L, 24L, 57L, 24L, 35L, 15L)), .Names = c("year",
"county", "state", "y", "h", "acres"), class = "data.frame", row.names = c(NA,
-12L))
# Build Weighted Variable
dat <- dat %>%
group_by(year) %>%
mutate(w = acres/sum(acres, na.rm = TRUE))
# Apply Weight
dat$h <- dat$h * dat$w
# Aggregate to State-level
dat <- dat %>%
group_by(year, state) %>%
summarise(h = sum(h, na.rm = TRUE))
