How to save the output of multiple confusionMatrix to single csv file in r?

Question

I have calculated confusionMatrix() for multiple stations using following code

    library(tidyverse)
    result <- df %>% 
      xtabs( ~ Observed + Forecasted + Station, data =.) %>% 
      array_tree(.,margin=3) %>% 
      map(~caret::confusionMatrix(as.table(.x)))

Then I have tried to calculate different confusion matrix based indices using the following code

    as.matrix(result, what = "classes")
    as.matrix(result, what = "overall")

which returns:

   #>          [,1]   
   #> Aizawl   List,6  
   #> Serchhip List,6

My question is how can I write the output to a .csv file?

Below is some sample data to help illustrate my problem:

    df = structure(list(Station = c("Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", "Aizawl", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", 
    "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip", "Serchhip"
    ), Observed = c(1, 1, 1, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 
    1, 3, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 3, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 3, 3, 4, 1, 1, 4, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    4, 4, 4, 3, 4, 1, 1, 1, 1, 1, 3, 5, 5, 5, 3, 1, 1, 3, 1, 1, 1, 
    1, 1, 5, 3, 4, 1, 1, 1, 1, 1, 3, 1, 4, 1, 1, 1, 1, 1, 4, 4, 5, 
    1, 5, 4, 5, 5, 5, 5, 1, 5, 1, 4, 5, 4, 4, 5, 4, 5, 5, 3, 1, 5, 
    3, 4, 3, 4, 5, 5, 5, 5, 4, 4, 5, 4, 4, 5, 5, 5, 5, 4, 5, 5, 5, 
    5, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 3, 5, 5, 1, 1, 3, 4, 1, 1, 1, 1, 1, 1, 1, 1, 
    3, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 3, 
    3, 3, 1, 1, 1, 1, 1, 1, 1, 4, 4, 1, 3, 4, 1, 1, 1, 1, 1, 1, 1, 
    1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 3, 
    6, 5, 5, 4, 1, 5, 1, 1, 1, 1, 4, 5, 5, 5, 5, 5, 5, 1, 1, 4, 1, 
    4, 4, 4, 5, 1, 1, 4, 3, 5, 4, 5, 5, 5, 5, 5, 4, 4, 4, 4, 5, 1, 
    6, 5, 5), Forecasted = c(1, 1, 1, 5, 5, 1, 1, 1, 5, 5, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5, 5, 5, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 5, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 1, 4, 4, 1, 1, 5, 3, 1, 
    1, 1, 4, 5, 5, 5, 5, 1, 1, 1, 5, 5, 1, 5, 5, 5, 4, 5, 4, 4, 4, 
    3, 4, 4, 1, 1, 5, 5, 4, 4, 4, 1, 1, 1, 4, 4, 4, 4, 4, 4, 1, 1, 
    5, 4, 4, 5, 4, 4, 4, 4, 5, 4, 5, 5, 5, 5, 5, 4, 5, 5, 4, 1, 1, 
    4, 4, 5, 5, 5, 5, 1, 4, 5, 5, 1, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 
    5, 5, 5, 5, 5, 5, 1, 1, 1, 5, 4, 1, 1, 1, 5, 4, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 5, 5, 5, 6, 5, 5, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 5, 5, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 5, 5, 4, 1, 1, 1, 1, 1, 4, 1, 1, 1, 4, 4, 4, 4, 1, 4, 1, 3, 
    1, 1, 1, 4, 4, 4, 4, 4, 4, 1, 1, 1, 4, 4, 3, 5, 5, 5, 4, 3, 5, 
    5, 5, 5, 5, 4, 5, 5, 5, 4, 5, 4, 4, 5, 5, 4, 4, 5, 4, 1, 4, 4, 
    5, 5, 4, 5, 4, 5, 4, 5, 5, 5, 1, 4, 5, 5, 5, 4, 5, 5, 5, 5, 5, 
    5, 5, 5, 5, 5, 6)), row.names = c(NA, 333L), class = "data.frame")

Thank you in advance for any help you are able to provide!

score 1 · Accepted Answer · edited Jun 16 '20 at 21:54

The problem with using the as.matrix() function in this example is you're creating a list of lists. Instead of:

as.matrix(result, what = "classes")
as.matrix(result, what = "overall")

try creating data frames to house your results which you can populate by iterating through your original result list. The code below should do the trick.

## iterate through all six parts of the confusionMatrix: "positive", "table", "overall", "byClass", "mode", "dots" 
for(i in 1:length(names(result[[1]]))){
  ##create a data frame to house the data for export
  data <- data.frame()
  ## iterate through all results; in the example we have Aizawl" and "Serchhip"
  for(j in 1:length(names(result))){
    ## load the data into a data frame
    df <- data.frame(result[[j]][i])
    ## if data is empty no need to alter or append to data frame so skip to next
    if(nrow(df)==0){next}
    ## add a name column for identifying between result sets; in the example we have Aizawl" and "Serchhip"
    df$name <- names(result)[j]
    ## append the loaded data to the data frame for export
    data <- rbind(data, df)
  }
  ## if data is empty no need to export, therefore skip to next
  if(nrow(data)==0){next}
  ## write the data to a csv with the name of the part of the condusionMatrix it contains
  ## row.names changed to TRUE based on OP's comments
  write.csv(data, file = paste0(names(result[[1]])[i],".csv"), row.names = TRUE, na = "")
}

Unless, you'd like you can transform the data frames before using the write.csv() function. In which case you can use

for(i in 1:length(names(result[[1]]))){
  data <- data.frame()
  for(j in 1:length(names(result))){
    df <- data.frame(result[[j]][i])
    if(nrow(df)==0){next}
    df$name <- names(result)[j]
    data <- rbind(data, df)
  }
  if(nrow(data)==0){next}
  assign(names(result[[1]])[i], data)
}

Actually I could able to do it separately. So, I don't want to do it separately as I am having many stations. For representation I have provided only 2 stations. — UseR10085, Jun 01 '20 at 13:57
@Bappa Das Does this updated code work? If so please close this question. — Joshua Mire, Jun 02 '20 at 03:07
Making `row.names = T` is better as I can have the class name in the output. It would be better if you can elaborate the utility of second for loop a bit more. — UseR10085, Jun 02 '20 at 06:17
Please visit [this](https://stackoverflow.com/questions/62129863/how-to-use-pairwise-wilcox-test-using-dplyr), if you can answer it. — UseR10085, Jun 02 '20 at 06:28
Thank you for accepting my answer. I've updated it with some notes to explain the logic behind it. I'll take a look at the other post and reply if I have an answer/questions. Best wishes! — Joshua Mire, Jun 02 '20 at 06:56

How to save the output of multiple confusionMatrix to single csv file in r?

1 Answers1