How to store loop table data

Question

I am a newbie in R. Could somebody please help me with this problem? I want to store the loop table data and export it into excel file, but I have not succeeded. Thanks.

Qquest7 <- c("A", "B", "A", "A", "A", "B", "B", "B", "B", "A")
Qquest24 <- c("neutral", "somewhat satisfied", "somewhat satisfied", "not able to rate", "somewhat satisfied", "less satisfied", "not able to rate", "dissatisfied", "very satisfied", "dissatisfied")
Qquest25 <- c("not able to rate", "not able to rate", "not able to rate", "somewhat satisfied", "not able to rate", "not able to rate", "dissatisfied", "dissatisfied", "not able to rate", "very satisfied")
Qquest26 <- c("not able to rate", "somewhat satisfied", "not able to rate", "less satisfied", "not able to rate", "neutral", "somewhat satisfied", "neutral", "neutral", "somewhat satisfied")
Qquest27 <- c("very satisfied", "not able to rate", "somewhat satisfied", "neutral", "very satisfied", "neutral", "neutral", "somewhat satisfied", "neutral", "not able to rate")
Qquest28 <- c("not able to rate", "not able to rate", "not able to rate", "not able to rate", "not able to rate", "not able to rate", "very satisfied", "neutral", "somewhat satisfied", "neutral")
Qquest29 <- c("desktop", "laptop", "tablet", "cellphone", "desktop", "desktop", "tablet", "laptop", "cellphone", "laptop")        

df <- data.frame(Qquest7, Qquest24, Qquest25, Qquest26, Qquest27, Qquest28, Qquest29)

library(openxlsx)
trial2429 <- c("Qquest24","Qquest25", "Qquest26", "Qquest27", "Qquest28", "Qquest29")
x <- data.frame()
y <- data.frame()
for (i in df[trial2429]){
  x[i] <- table(df$Qquest7, i)
  y <- print(x)
}
write.xlsx(y, file = "trial2429.xlsx")

Possible duplicate of [Export data to Excel](https://stackoverflow.com/questions/19414605/export-data-to-excel) — Sean Pianka, Aug 06 '18 at 13:06
What does "not succeeded" exactly mean? What is the error/problem? — arashka, Aug 06 '18 at 13:26
The problem is if I add the index [i] after x, it does not produce the result but give me an error message instead "Error in value[[jvseq[[jjj]]]] : subscript out of bounds". If I remove [i], everything works fine. Multiple tables can be printed in the console but I do not know how to export it into csv or excel as only 1 table appears in the generated excel file. — Nann, Aug 06 '18 at 14:02
Awesome, thanks. So- how do you expect the output to be stored in your `y` data.frame, in terms of structure? The `table` for the different questions produces different variable/column names, so I'm not sure exactly what your desired output is. — Luke C, Aug 06 '18 at 17:25
What is your desired result? All tables in one? Different sheets? — Parfait, Aug 06 '18 at 19:01
@LukeC: thank you so much. It is solved and the solution is more complicated than I thought. — Nann, Aug 07 '18 at 06:38
@Parfait: my desired result is the one below in the answer section. — Nann, Aug 07 '18 at 06:39
@Nann - no problem, glad I guessed correctly. I'm sure there is a direct-to-excel version, but I haven't seen it before. Cheers! — Luke C, Aug 07 '18 at 21:13
@LukeC: I do not understand why you had to name trial2429. Is it not already a variable name? — Nann, Aug 08 '18 at 11:41
Good catch- honestly it's just a holdover from the way I was using this function in my own work- with the way your data is setup you could probably modify the function to not need it. However, to clarify- it's not naming `trial2429`, it's assigning names to the values within `trial2429` so that the `lapply` function has named objects as input and therefore names for the objects it outputs. The `export_results` function here actually needs a list with named objects as input to work. — Luke C, Aug 08 '18 at 17:47

Luke C · Accepted Answer · 2018-08-06T18:15:43.457

I'm not 100% what you're after as desired output, but this function I've used for my own purposes might do the trick for you. Using your df and trial2429 for data, first give trial2429 some names:

names(trial2429) <- trial2429

Now, build a function that takes a list and sequentially adds its contents to a .csv file:

## Export list
export_results <- function(df_list, file_name = "outfile.csv") {
  if (file.exists(file_name)) {
    file.remove(file_name)
    print("Removed old file.")
  }

  ## Clean export function
  writeout <- function(table_name) {
    write(table_name, file_name, sep = ",", append = TRUE)
    tab.out <- df_list[[table_name]]
    tab.out <- cbind(rownames(tab.out), tab.out)
    write.table(
      tab.out,
      file_name,
      row.names = FALSE,
      sep = ",",
      append = TRUE
    )
    write("", file_name, sep = ",", append = TRUE)
  }

  for (i in names(df_list)) {
    writeout(i)
  }
}

Get the table version for each question that you're after, and store it in a list:

q.list <- lapply(trial2429, function(x){
  table(df$Qquest7, df[[x]])
})

Call the function defined above on that list:

export_results(q.list, file_name = "trial2429.csv")

It'll throw some warning messages, but it seems to not cause any problems- your output should like this in Excel:

Edit: Fixed number-of-columns issue

How to store loop table data

1 Answers1