Hello,
I have, what seems, fairly simple questions:
I need to correlate water levels in wells versus rainfall in the previous 10, 20, and 20 days (separately) for 23 wells, for 12 measurements.
Ideally I end up with 3 matrices representing correlations between water level and rainfall for each well at 10, 20 and 30 days. Well names should be at the top and the left of the matrices.
Now I am doing it one-by-one, and this will take some time. I am sure there is a better way!
Thank you in advance.
########################################################
Data consists of: well (23 of these), date (14 of these), event (14 of these), depth, Rain10DayTotal, Rain20DayTotal, Rain 30DayTotal.
I have reprexed a subset of the data for 3 sampling dates:
mass2 <- tibble::tribble(
~well, ~date, ~event, ~depth, ~Rain10DayTotal, ~Rain20DayTotal, ~Rain_30Total,
"DEPFLD", "11/13/2017", 1L, 37, 0.21, 0.36, 0.86,
"MWBS", "11/13/2017", 1L, 22.15, 0.21, 0.36, 0.86,
"DEPPBD", "11/14/2017", 1L, 19.5, 0.21, 0.36, 0.62,
"DEPPBS", "11/14/2017", 1L, 17.5, 0.21, 0.36, 0.62,
"MW01", "11/14/2017", 1L, 4.3, 0.21, 0.36, 0.62,
"MW02", "11/14/2017", 1L, 15.7, 0.21, 0.36, 0.62,
"MW04", "11/14/2017", 1L, 38, 0.21, 0.36, 0.62,
"MW06", "11/14/2017", 1L, 9.3, 0.21, 0.36, 0.62,
"MW07", "11/14/2017", 1L, 9.3, 0.21, 0.36, 0.62,
"MW11", "11/14/2017", 1L, 20.95, 0.21, 0.36, 0.62,
"MW14", "11/14/2017", 1L, 6.2, 0.21, 0.36, 0.62,
"MW17", "11/14/2017", 1L, 6.1, 0.21, 0.36, 0.62,
"MW20", "11/14/2017", 1L, 3.55, 0.21, 0.36, 0.62,
"MW22", "11/14/2017", 1L, 15.4, 0.21, 0.36, 0.62,
"MWAI", "11/14/2017", 1L, 58.6, 0.21, 0.36, 0.62,
"MWBU", "11/14/2017", 1L, 51.6, 0.21, 0.36, 0.62,
"BW02", "11/16/2017", 1L, 1.1, 0.04, 0.36, 0.62,
"MWCI", "11/16/2017", 1L, 51.6, 0.04, 0.36, 0.62,
"MWEU", "11/16/2017", 1L, 12.5, 0.04, 0.36, 0.62,
"SW01", "11/16/2017", 1L, 0, 0.04, 0.36, 0.62,
"MWDS", "11/17/2017", 1L, 21, 0.04, 0.36, 0.57,
"MWDU", "11/17/2017", 1L, 76, 0.04, 0.36, 0.57,
"DEPFLD", "4/3/2018", 2L, 40.7, 0.32, 1.56, 1.73,
"MW01", "4/3/2018", 2L, 4.5, 0.32, 1.56, 1.73,
"MW14", "4/3/2018", 2L, 7.4, 0.32, 1.56, 1.73,
"MW22", "4/3/2018", 2L, 17.2, 0.32, 1.56, 1.73,
"MWDS", "4/3/2018", 2L, 28.6, 0.32, 1.56, 1.73,
"MWDU", "4/3/2018", 2L, 79.6, 0.32, 1.56, 1.73,
"MWEU", "4/3/2018", 2L, 17.8, 0.32, 1.56, 1.73,
"DEPPBD", "4/4/2018", 2L, 23.4, 0.32, 1.56, 1.73,
"DEPPBS", "4/4/2018", 2L, 22.7, 0.32, 1.56, 1.73,
"MW06", "4/4/2018", 2L, 12.4, 0.32, 1.56, 1.73,
"MW07", "4/4/2018", 2L, 11, 0.32, 1.56, 1.73,
"MW02", "4/5/2018", 2L, 20.4, 0.32, 1.56, 1.73,
"MW04", "4/5/2018", 2L, 39.6, 0.32, 1.56, 1.73,
"MW11", "4/5/2018", 2L, 25.8, 0.32, 1.56, 1.73,
"MW17", "4/5/2018", 2L, 8.1, 0.32, 1.56, 1.73,
"MWBU", "4/5/2018", 2L, 55, 0.32, 1.56, 1.73,
"MWCI", "4/5/2018", 2L, 52.1, 0.32, 1.56, 1.73,
"BW02", "4/6/2018", 2L, 2.6, 0.26, 1.56, 1.69,
"MW20", "4/6/2018", 2L, 6.3, 0.26, 1.56, 1.69,
"MWAI", "4/6/2018", 2L, 53, 0.26, 1.56, 1.69,
"SW01", "4/6/2018", 2L, 0, 0.26, 1.56, 1.69,
"DEPFLD", "6/18/2018", 3L, 40.2, 2.67, 4.13, 7.16,
"MW01", "6/18/2018", 3L, 2.7, 2.67, 4.13, 7.16,
"MW22", "6/18/2018", 3L, 15.71, 2.67, 4.13, 7.16,
"MWDS", "6/18/2018", 3L, 27.4, 2.67, 4.13, 7.16,
"MWDU", "6/18/2018", 3L, 79, 2.67, 4.13, 7.16,
"DEPPBD", "6/19/2018", 3L, 22.4, 1.98, 3.97, 5.43,
"DEPPBS", "6/19/2018", 3L, 21.5, 1.98, 3.97, 5.43,
"MW02", "6/19/2018", 3L, 18.6, 1.98, 3.97, 5.43,
"MW06", "6/19/2018", 3L, 11.5, 1.98, 3.97, 5.43,
"MW07", "6/19/2018", 3L, 10.5, 1.98, 3.97, 5.43,
"MW17", "6/19/2018", 3L, 7, 1.98, 3.97, 5.43,
"MW11", "6/20/2018", 3L, 24.7, 1.72, 3.03, 5.02,
"MWBS", "6/20/2018", 3L, 28.01, 1.72, 3.03, 5.02,
"MWBU", "6/20/2018", 3L, 53.98, 1.72, 3.03, 5.02,
"MWEU", "6/20/2018", 3L, 17.1, 1.72, 3.03, 5.02,
"BW02", "6/21/2018", 3L, 1.58, 1.72, 3.01, 5.01,
"MW20", "6/21/2018", 3L, 6.22, 1.72, 3.01, 5.01,
"MWAI", "6/21/2018", 3L, 52.46, 1.72, 3.01, 5.01,
"MWCI", "6/21/2018", 3L, 51.9, 1.72, 3.01, 5.01,
"SW01", "6/21/2018", 3L, 0, 1.72, 3.01, 5.01
)
head(mass2)
#> # A tibble: 6 x 7
#> well date event depth Rain10DayTotal Rain20DayTotal Rain_30Total
#> <chr> <chr> <int> <dbl> <dbl> <dbl> <dbl>
#> 1 DEPFLD 11/13/2017 1 37 0.21 0.36 0.86
#> 2 MWBS 11/13/2017 1 22.2 0.21 0.36 0.86
#> 3 DEPPBD 11/14/2017 1 19.5 0.21 0.36 0.62
#> 4 DEPPBS 11/14/2017 1 17.5 0.21 0.36 0.62
#> 5 MW01 11/14/2017 1 4.3 0.21 0.36 0.62
#> 6 MW02 11/14/2017 1 15.7 0.21 0.36 0.62
Created on 2021-07-28 by the reprex package (v1.0.0)
#############################################
########################################
My code at the moment--again doing it one-by-one:
### Then select data from only MW04
well04 <- mass %>%
filter(well %in% c("MW04", "MW04R"))
#> Error in mass %>% filter(well %in% c("MW04", "MW04R")): could not find function "%>%"
rain.10day <-well04 %>%
select(result, Rain10DayTotal)
#> Error in well04 %>% select(result, Rain10DayTotal): could not find function "%>%"
### Now run correlation
wellmw04_cor <- cor(rain.10day, method = "spearman", use = "complete.obs")
#> Error in is.data.frame(x): object 'rain.10day' not found
wellmw04_cor
#> Error in eval(expr, envir, enclos): object 'wellmw04_cor' not found
corrplot(wellmw04_cor, method = "circle", type = "lower")
#> Error in corrplot(wellmw04_cor, method = "circle", type = "lower"): could not find function "corrplot"
Created on 2021-07-28 by the reprex package (v1.0.0)
A similar question was answered by @robjhyndman and my question follows up on a question that @pieterjanvc was helping me on.