How to put correlation analysis results in a csv table in R

You can create a dataframe in your by function extracting the values that you want.

result <- do.call(rbind, by(df, df$group, FUN = function(x) {
  tmp <- cor.test(x$var1, x$var2, method = "spearman")
  data.frame(group = x$group[1], rho = tmp$estimate, p.value = tmp$p.value)
}))
result
#   group    rho p.value
#G1    G1 -0.261   0.470
#G2    G2 -0.442   0.204

To write the data to csv we can use write.csv :

write.csv(result, 'result.csv', row.names = FALSE)

The spear object is a list of two lists (one htest list for each group):

str(spear)
List of 2
 $ G1:List of 8
  ..$ statistic  : Named num 140
  .. ..- attr(*, "names")= chr "S"
  ..$ parameter  : NULL
  ..$ p.value    : num 0.682
  ..$ estimate   : Named num 0.152
  .. ..- attr(*, "names")= chr "rho"
  ..$ null.value : Named num 0
  .. ..- attr(*, "names")= chr "rho"
  ..$ alternative: chr "two.sided"
  ..$ method     : chr "Spearman's rank correlation rho"
  ..$ data.name  : chr "X$var1 and X$var2"
  ..- attr(*, "class")= chr "htest"
 $ G2:List of 8
  ..$ statistic  : Named num 180
  .. ..- attr(*, "names")= chr "S"
  ..$ parameter  : NULL
  ..$ p.value    : num 0.811
  ..$ estimate   : Named num -0.0909
  .. ..- attr(*, "names")= chr "rho"
  ..$ null.value : Named num 0
  .. ..- attr(*, "names")= chr "rho"
  ..$ alternative: chr "two.sided"
  ..$ method     : chr "Spearman's rank correlation rho"
  ..$ data.name  : chr "X$var1 and X$var2"
  ..- attr(*, "class")= chr "htest"
 - attr(*, "dim")= int 2
 - attr(*, "dimnames")=List of 1
  ..$ df$group: chr [1:2] "G1" "G2"
 - attr(*, "call")= language by.data.frame(data = df, INDICES = df$group, FUN = function(X) cor.test(X$var1,      X$var2, method = "spearman"))
 - attr(*, "class")= chr "by"

The tidy function from the broom package can convert an htest list to a data.frame (a tibble really).

library(broom)
lapply(spear, tidy)
$G1
# A tibble: 1 x 5
  estimate statistic p.value method                          alternative
     <dbl>     <dbl>   <dbl> <chr>                           <chr>      
1    0.152       140   0.682 Spearman's rank correlation rho two.sided  

$G2
# A tibble: 1 x 5
  estimate statistic p.value method                          alternative
     <dbl>     <dbl>   <dbl> <chr>                           <chr>      
1  -0.0909       180   0.811 Spearman's rank correlation rho two.sided  

You still have a list, but it is much more workable. With some dplyr functions, we can make this list look like what you want:

library(dplyr)

lapply(spear, tidy) %>% 
  bind_rows() %>% 
  mutate(group = names(spear)) %>%
  rename(rho = estimate) %>%
  select(group, rho, p.value)

# A tibble: 2 x 3
  group     rho p.value
  <chr>   <dbl>   <dbl>
1 G1     0.152    0.682
2 G2    -0.0909   0.811

We can use lapply() to extract values from the output objects, convert them to data frames, and write the combined data frame to a csv file.

set.seed(95014) # to make example reproducible
df <- data.frame(group = rep(c("G1", "G2"), each = 10),
                 var1 = rnorm(20),
                 var2 = rnorm(20))

spear <- by(df, df$group, FUN = function(X) cor.test(X$var1, X$var2, method = "spearman"))

At this point the object spear is a list that contains one element per by group. We will process each element, extracting the required elements and returning them as a list of objects of type data.frame().

Since the elements in the list are named by group, we use unique(df$group) to drive lapply(), enabling us to assign the names as a column in the output data frames.

# convert to data frame 
dfList <- lapply(unique(df$group),function(x){
     y <- spear[[x]]
     data.frame(group = x,
                rho_value = y$estimate,
                p_value = y$p.value)
})

Finally, we use do.call() to bind the list of data frames as the first argument to write.csv(), resulting in a single data frame that is then written to disk in comma separated values format.

write.csv(do.call(rbind,dfList),"./data/rhoCalcs.csv",row.names=FALSE)

...and the output:

"group","rho_value","p_value"
"G1",0.260606060606061,0.469675250206491
"G2",0.333333333333333,0.348846243872153

A solution using sapply instead of by.

gu <- unique(df$group)  ## putting the groups into a vector
r <- sapply(gu, function(x) 
  cor.test(~ var1 + var2, df[df$group == x, ], method="spearman")[c("estimate", "p.value")])
r <- cbind(group=gu, matrix(unlist(r), 2, b=T, dimnames=list(NULL, c("rho", "p_value"))))
write.csv(r, file="xyz.csv", row.names=FALSE, quote=FALSE)

Alternatively, if you need to do that more often, you could wrap it in a small function.

my.cor.test <- function(x) {
  ct <- cor.test(~ var1 + var2, df[df$group == x, ], 
                 method="spearman")[c("estimate", "p.value")]
  setNames(c(x, unlist(ct)), c("group", "rho", "p_value"))
}

gu <- unique(df$group)  ## putting unique groups into a vector
r <- t(sapply(gu, my.cor.test))
write.csv(r, file="xyz.csv", row.names=FALSE, quote=FALSE)

Gives

group,rho,p_value
G1,-0.696969696969697,0.0311410954595681
G2,0.0545454545454545,0.891638843953118