Repeat Correlations

General
,
1

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.

2

Try this

library(tidyverse)

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
)

mass2 %>% 
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarize(cor_coeff = cor(depth, value), .groups = "drop") %>%
  # pivot so correlation coefficients are in columns
  pivot_wider(values_from = "cor_coeff")
#> Warning in cor(depth, value): the standard deviation is zero

#> Warning in cor(depth, value): the standard deviation is zero

#> Warning in cor(depth, value): the standard deviation is zero
#> # A tibble: 22 x 4
#>    well   Rain_30Total Rain10DayTotal Rain20DayTotal
#>    <chr>         <dbl>          <dbl>          <dbl>
#>  1 BW02         0.0310        -0.0841          0.261
#>  2 DEPFLD       0.503          0.424           0.656
#>  3 DEPPBD       0.476          0.324           0.570
#>  4 DEPPBS       0.5            0.349           0.592
#>  5 MW01        -0.966         -0.990          -0.914
#>  6 MW02         0.349          0.189           0.450
#>  7 MW04         1              1.00            1.00 
#>  8 MW06         0.444          0.289           0.540
#>  9 MW07         0.440          0.285           0.536
#> 10 MW11         0.523          0.363           0.697
#> # ... with 12 more rows

Created on 2021-07-28 by the reprex package (v1.0.0)

3

@arthur.t Thanks for your quick reply.

I was able to get this to run, but I am only getting one correlation coefficient output for .groups: The pivoting and grouping appeared to work. Note: my dataframe is called "surficial", and depth is "result". Everything else the same.

surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarize(cor_coeff = cor(result, value), .groups = "drop")
#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% group_by(well, : could not find function "%>%"

Created on 2021-07-28 by the reprex package (v1.0.0)

4

Hm. Try to library(tidyverse) first.

1 Like
5

I found the issue: dplyr/plyr--so I stuck dplyr:: in front of group_by and summarize--appeared to work!!
I need to do your suggestion regarding tidyverse to hopefully resolve this.

THANK YOU SO MUCH. Saved me hours--and my boss wanted this quickly!!!!!!

1 Like
6

@arthur.t I found the issue: dplyr/plyr--so I stuck "dplyr::" in front of both "group_by" and "summarize"--it worked!

I need to do your suggestion regarding tidyverse to hopefully resolve this.

THANK YOU SO MUCH. Saved me hours--and my boss wanted this quickly!!!!!!

7

@arthur.t This question wont die!

I am now trying to extract p-values for these correlations.

the "cor.test" function presumably outputs p-values. But I keep getting an error thrown.
The "Hmisc" package also outputs p-values for correlations. But I am stumped.

Thank you for your help.

Here is my code

### p-values
correlations <- surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  dplyr::group_by(well, name) %>% 
  dplyr::summarize(cor_coeff = cor.test(result, value,method = "spearman"), .groups = "drop") %>%
  # pivot so correlation coefficients are in columns
  pivot_wider(values_from = "cor_coeff")
#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% dplyr::group_by(well, : could not find function "%>%"
correlations
#> Error in eval(expr, envir, enclos): object 'correlations' not found
write.csv(correlations, "Depth to Rainfall Spearman Correlations2.csv", row.names = FALSE)
#> Error in is.data.frame(x): object 'correlations' not found

pvalue <- correlations$p.value
#> Error in eval(expr, envir, enclos): object 'correlations' not found

Created on 2021-07-30 by the reprex package (v1.0.0)

8

I think you need library(tidyverse). The error message suggests that it's not loaded.

9

@arthur.t Hi So I reloaded tidyverse, and I get this error.

Again, I am trying to extract the p-values from this correlation you helped me with earlier.

Thanks!

### p-values
correlation2 <- surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  dplyr::group_by(well, name) %>% 
  dplyr::summarize(cor_coeff = cor.test(result, value, method = "spearman"), .groups = "drop") %>%
  # pivot so correlation coefficients are in columns
  pivot_wider(values_from = "cor_coeff")
#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% dplyr::group_by(well, : could not find function "%>%"
correlations2
#> Error in eval(expr, envir, enclos): object 'correlations2' not found
write.csv(correlation2, "Depth to Rainfall Spearman Correlations2.csv", row.names = FALSE)
#> Error in is.data.frame(x): object 'correlation2' not found

Created on 2021-08-01 by the reprex package (v1.0.0)

10

This error suggests tidyverse is not loaded, because %>% is an operator that's loaded with tidyverse.

11

arthur.t--Hi again.

So I reloaded tidyverse, and the error appears to be resolved.

I also changed "summarize" to "summarise" (although I thought either work).

I get a new errror thrown at me. I think it is because the data are not in a matrix. Is this an output from your code?
thanks,

Here is my reprex:

12

arthur.t

Not sure if I loaded the reprex:

correlation2 <- surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarise(cor_coeff = cor.test(result, value, method = "spearman"), .groups = "drop") %>%
  # pivot so correlation coefficients are in columns
  pivot_wider(values_from = "cor_coeff")
#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% group_by(well, : could not find function "%>%"
correlations2
#> Error in eval(expr, envir, enclos): object 'correlations2' not found
write.csv(correlation2, "Depth to Rainfall Spearman Correlations2.csv", row.names = FALSE)
#> Error in is.data.frame(x): object 'correlation2' not found

Created on 2021-08-03 by the reprex package (v2.0.0)

13

High Craigdux. Maybe you're having an issue specifically with your reprex. Because I see the same error as before in this one.

This line here specifically is the first that throws the error and is associated with not loading tidyverse. And then every line afterwards will throw errors because the first command fails to complete.

If you're getting a different error now, I can't see it in this reprex.

#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% group_by(well, : could not find function "%>%"
14

arthur.t

Sorry about this problem continuing. (I have included the reprex using a different method below.

Do you think the problem is because the pivoted data are not a vector? This "cor.test" routine spits out both r values and p-values. Is this the problem (compared to the "cor" routine that just spits out r-values)? This is the error I am getting:

Error: Problem with summarise() column cor_coeff.
i cor_coeff = cor.test(result, value, method = "spearman").
x cor_coeff must be a vector, not a htest object.
i The error occurred in group 1: well = BW02, name = "Rain_30Total".

And hopefully a correct reprex:

### To Calculate p-values
correlation2 <- surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarise(cor_coeff = cor.test(result, value, method = "spearman"), .groups = "drop") %>%
  # pivot so correlation coefficients are in columns
  pivot_wider(values_from = "cor_coeff")
#> Error in surficial %>% pivot_longer(starts_with("Rain")) %>% group_by(well, : could not find function "%>%"
correlations2
#> Error in eval(expr, envir, enclos): object 'correlations2' not found

Created on 2021-08-03 by the reprex package (v2.0.0)

15

No prob. Yes, cor.test seems to produce a more complex object. Try this. You will need to install broom.

library(tidyverse)

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
)

mass_cor <- mass2 %>% 
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # filter so at least 2 observations
  group_by(well, name) %>% 
  mutate(n_obs = n()) %>%
  ungroup() %>%
  filter(n_obs > 2) %>%
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarize(cor_coeff = list(cor.test(depth, value))) %>%
  ungroup() %>%
  # unnest the cor_coeff list
  mutate(cor_coeff = map(cor_coeff, broom::tidy)) %>%
  unnest(cor_coeff)
#> Warning in cor(x, y): the standard deviation is zero

#> Warning in cor(x, y): the standard deviation is zero

#> Warning in cor(x, y): the standard deviation is zero
#> `summarise()` has grouped output by 'well'. You can override using the `.groups` argument.
print(mass_cor)
#> # A tibble: 57 x 8
#>    well   name    estimate statistic p.value parameter method        alternative
#>    <chr>  <chr>      <dbl>     <dbl>   <dbl>     <int> <chr>         <chr>      
#>  1 BW02   Rain_3~   0.0310    0.0310   0.980         1 Pearson's pr~ two.sided  
#>  2 BW02   Rain10~  -0.0841   -0.0844   0.946         1 Pearson's pr~ two.sided  
#>  3 BW02   Rain20~   0.261     0.271    0.832         1 Pearson's pr~ two.sided  
#>  4 DEPFLD Rain_3~   0.503     0.581    0.665         1 Pearson's pr~ two.sided  
#>  5 DEPFLD Rain10~   0.424     0.469    0.721         1 Pearson's pr~ two.sided  
#>  6 DEPFLD Rain20~   0.656     0.869    0.544         1 Pearson's pr~ two.sided  
#>  7 DEPPBD Rain_3~   0.476     0.542    0.684         1 Pearson's pr~ two.sided  
#>  8 DEPPBD Rain10~   0.324     0.342    0.790         1 Pearson's pr~ two.sided  
#>  9 DEPPBD Rain20~   0.570     0.694    0.614         1 Pearson's pr~ two.sided  
#> 10 DEPPBS Rain_3~   0.5       0.577    0.667         1 Pearson's pr~ two.sided  
#> # ... with 47 more rows

mass_cor_coeff <- mass_cor %>%
  pivot_wider(values_from = estimate, id_cols = well)
mass_cor_coeff
#> # A tibble: 19 x 4
#>    well   Rain_30Total Rain10DayTotal Rain20DayTotal
#>    <chr>         <dbl>          <dbl>          <dbl>
#>  1 BW02         0.0310       -0.0841          0.261 
#>  2 DEPFLD       0.503         0.424           0.656 
#>  3 DEPPBD       0.476         0.324           0.570 
#>  4 DEPPBS       0.5           0.349           0.592 
#>  5 MW01        -0.966        -0.990          -0.914 
#>  6 MW02         0.349         0.189           0.450 
#>  7 MW06         0.444         0.289           0.540 
#>  8 MW07         0.440         0.285           0.536 
#>  9 MW11         0.523         0.363           0.697 
#> 10 MW17         0.164        -0.00218         0.271 
#> 11 MW20         0.670         0.503           0.823 
#> 12 MW22        -0.201        -0.317          -0.0450
#> 13 MWAI        -0.744        -0.591          -0.878 
#> 14 MWBU         0.455         0.288           0.638 
#> 15 MWCI         0.353         0.268           0.551 
#> 16 MWDS         0.516         0.456           0.639 
#> 17 MWDU         0.508         0.448           0.632 
#> 18 MWEU         0.603         0.529           0.762 
#> 19 SW01        NA            NA              NA

Created on 2021-08-03 by the reprex package (v1.0.0)

16

Hi, @arthur.t I appear to be still having problems. REprex below Someone in my office supplied me with a working code that seems to be working.

The code you supplied (I changed mass2 to mass):

library(tidyverse)
#> Warning: package 'tidyverse' was built under R version 4.0.5
#> Warning: package 'ggplot2' was built under R version 4.0.5
#> Warning: package 'tibble' was built under R version 4.0.5
#> Warning: package 'tidyr' was built under R version 4.0.5
#> Warning: package 'readr' was built under R version 4.0.5
#> Warning: package 'dplyr' was built under R version 4.0.5

mass_cor <- mass %>% 
 # pivot rain total
 pivot_longer(starts_with("Rain")) %>% 
 # filter so at least 2 observations
 group_by(well, name) %>% 
 mutate(n_obs = n()) %>%
 ungroup() %>%
 filter(n_obs > 2) %>%
 # calculate correlation coefficient with depth for each combination of well and day interval
 group_by(well, name) %>% 
 summarise(cor_coeff = list(cor.test(depth, value))) %>%
 ungroup() %>%
 # unnest the cor_coeff list
 mutate(cor_coeff = map(cor_coeff, broom::tidy)) %>%
 unnest(cor_coeff)
#> Error in pivot_longer(., starts_with("Rain")): object 'mass' not found

# unnest the cor_coeff list
mutate(cor_coeff = map(cor_coeff, broom::tidy)) %>%
 unnest(cor_coeff)
#> Error in map(cor_coeff, broom::tidy): object 'cor_coeff' not found
#> Warning in cor(x, y): the standard deviation is zero

#> Warning in cor(x, y): the standard deviation is zero

#> Warning in cor(x, y): the standard deviation is zero
#> `summarise()` has grouped output by 'well'. You can override using the `.groups` argument.
print(mass_cor)
#> Error in print(mass_cor): object 'mass_cor' not found
#> # A tibble: 57 x 8
#>    well   name    estimate statistic p.value parameter method        alternative
#>    <chr>  <chr>      <dbl>     <dbl>   <dbl>     <int> <chr>         <chr>      
#>  1 BW02   Rain_3~   0.0310    0.0310   0.980         1 Pearson's pr~ two.sided  
#>  2 BW02   Rain10~  -0.0841   -0.0844   0.946         1 Pearson's pr~ two.sided  
#>  3 BW02   Rain20~   0.261     0.271    0.832         1 Pearson's pr~ two.sided  
#>  4 DEPFLD Rain_3~   0.503     0.581    0.665         1 Pearson's pr~ two.sided  
#>  5 DEPFLD Rain10~   0.424     0.469    0.721         1 Pearson's pr~ two.sided  
#>  6 DEPFLD Rain20~   0.656     0.869    0.544         1 Pearson's pr~ two.sided  
#>  7 DEPPBD Rain_3~   0.476     0.542    0.684         1 Pearson's pr~ two.sided  
#>  8 DEPPBD Rain10~   0.324     0.342    0.790         1 Pearson's pr~ two.sided  
#>  9 DEPPBD Rain20~   0.570     0.694    0.614         1 Pearson's pr~ two.sided  
#> 10 DEPPBS Rain_3~   0.5       0.577    0.667         1 Pearson's pr~ two.sided  
#> # ... with 47 more rows

mass_cor_coeff <- mass_cor %>%
 pivot_wider(values_from = estimate, id_cols = well)
#> Error in pivot_wider(., values_from = estimate, id_cols = well): object 'mass_cor' not found
mass_cor_coeff
#> Error in eval(expr, envir, enclos): object 'mass_cor_coeff' not found

Created on 2021-08-03 by the reprex package (v2.0.0)

The code, based on your original code that appears to work:

## From Paul Schueller---It worked!!!!

library(tidyverse)
#> Warning: package 'tidyverse' was built under R version 4.0.5
#> Warning: package 'ggplot2' was built under R version 4.0.5
#> Warning: package 'tibble' was built under R version 4.0.5
#> Warning: package 'tidyr' was built under R version 4.0.5
#> Warning: package 'readr' was built under R version 4.0.5
#> Warning: package 'dplyr' was built under R version 4.0.5

correlation2 <- surficial %>%
  # pivot rain total
  pivot_longer(starts_with("Rain")) %>% 
  # calculate correlation coefficient with depth for each combination of well and day interval
  group_by(well, name) %>% 
  summarise(cor_coeff = cor(result, value,method = "spearman"), 
            cor_pVal = cor.test(result, value, method = "spearman")$p.value, .groups = "drop") #%>%
#> Error in pivot_longer(., starts_with("Rain")): object 'surficial' not found
# pivot so correlation coefficients are in columns
#pivot_wider(values_from = c("cor_coeff", "cor_pVal"))
correlation2
#> Error in eval(expr, envir, enclos): object 'correlation2' not found
write.csv(correlation2, "p-values for Spearman Correlations2.csv", row.names = FALSE)
#> Error in is.data.frame(x): object 'correlation2' not found

Created on 2021-08-03 by the reprex package (v2.0.0)

Thanks again for your help!!!

I now know to use just "tidyverse"-- I need to clean up the ones I dont need!

17

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.