I have two dataframes, df1 (10×2) containing 10 zipcodes and the total number of PV installations in per zipcode, and df2, which is a matrix 10×10 matrix, indicating which zipcodes are neighbouring, indicated by 1/0.
Dataframe 1
| zipcode | PV |
|---|---|
| 5095 | 19 |
| 5999 | 29 |
| 3434 | 1 |
Dataframe 2
| 5095 | 5999 | 3434 |
|---|---|---|
| 0 | 1 | 0 |
| 1 | 0 | 1 |
| 0 | 1 | 0 |
As dataframe 2 shows, zipcode 5095 is neighbouring 5999, zipcode 5999 is neighbouring both 5095 and 3434, and zipcode 3434 is neighbouring 5999.
The goal is to calculate the number of PV installations in areas surrounding each zipcode.
So for zipcode 5095, this value needs to be 29, for zipcode 5999 it needs to be 20 and for zipcode 3434 the answer is 29.
How can I do this?
>Solution :
Multiply df2 by df1$PV element-wise and sum the columns:
df1 <- data.frame(zipcode = c(5095, 5999, 3434), PV = c(19, 29, 1))
df2 <- matrix(rep(0:1, length.out = 9), 3, 3)
setNames(colSums(df2*df1$PV), df1$zipcode)
#> 5095 5999 3434
#> 29 20 29
No need for setNames if df2 is named.
df2 <- matrix(rep(0:1, length.out = 9), 3, 3, 0, rep(list(df1$zipcode), 2))
colSums(df2*df1$PV)
#> 5095 5999 3434
#> 29 20 29