Cannot write excel file using xlsx package in R

2
votes

I am using dplyr to create an object that I then use xlsx to write out to a spreadhseet.

I run the following code:

provFundedProp <- compensationBase2014 %>% 
group_by(provinciallyFunded) %>% 
summarise(total=sum(fundingRaw)) %>% 
mutate(percent = paste0(round(100 * total/sum(total),1), "%"))

Which I then write to the first sheet:

write.xlsx(provFundedProp, file="output/provFundedProp.xlsx",     
sheetName="provFundingSector")

This works fine and gives me the file I need.

I then run the following code going down a level:

provFundedServiceDivision <- compensationBase2014 %>% 
group_by(serviceDivision,provinciallyFunded) %>% 
summarise(total=sum(fundingRaw)) %>% 
mutate(percent = paste0(round(100 * total/sum(total),1), "%"))

#write to second sheet
write.xlsx(provFundedServiceDivision, file="output/provFundedSD.xlsx",   
sheetName="provFundingSD")

Which gives me the following error:

Error: cannot convert object to a data frame

I am going crazy here. Does anyone have any idea what the heck is going on? I have tried this with multiple wueries and I have no idea what is up.

class(provFundedServiceDivision) [1] "grouped_df" "tbl_df" "tbl" 
"data.frame" 

Classes ‘grouped_df’, ‘tbl_df’, ‘tbl’ and 'data.frame': 6 obs. of  4   
variables:
$ serviceDivision   : chr  "AS" "AS" "CLS" "CLS" ...
$ provinciallyFunded: chr  "NPF" "PF" "NPF" "PF" ...
$ total             : num  1.90e+06 3.97e+07 2.93e+07 5.70e+08 9.55e+07 ...
$ percent           : chr  "4.6%" "95.4%" "4.9%" "95.1%" ...
- attr(*, "vars")=List of 1
..$ : symbol serviceDivision
- attr(*, "labels")='data.frame':   3 obs. of  1 variable:
..$ serviceDivision: chr  "AS" "CLS" "GS"
..- attr(*, "vars")=List of 1
.. ..$ : symbol serviceDivision
..- attr(*, "drop")= logi TRUE
- attr(*, "indices")=List of 3
..$ : int  0 1
..$ : int  2 3
..$ : int  4 5
- attr(*, "drop")= logi TRUE
- attr(*, "group_sizes")= int  2 2 2
- attr(*, "biggest_group_size")= int 2

> traceback()
7: stop(list(message = "cannot convert object to a data frame", 
call = NULL, cppstack = NULL))
6: .Call("dplyr_cbind_all", PACKAGE = "dplyr", dots)
5: cbind_all(x)
4: bind_cols(...)
3: cbind(deparse.level, ...)
2: cbind(rownames = rownames(x), x)
1: write.xlsx(provFundedServiceDivision, file = "output/provFundedSD.xlsx", 
sheetName = "provFundingSD")
2
rdplyrxlsx
What does this return ... class(provFundedServiceDivision) – IRTFM
At the very least, you could provide the output of str(provFundedServiceDivision). Maybe even the results of traceback() after getting the error but only if you really want help. ;) – joran
Put the information in your question, please. That is why you are allowed to edit your questions. – joran
[NOTE: Since @Stedy deleted his answer, I've moved my comment here] I've run into this issue before as well. I haven't dug into the reasons for it, but it's possible that write.xlsx doesn't look beyond the first class of the object. In any case, I've found that wrapping the data frame in as.data.frame(my_data) resolves the issue, presumably because it converts the tbl_df back to a vanilla R data frame. – eipi10

2 Answers

3
votes

eipi10 saved the day with his solution! I used the following code and everything worked fine:

write.xlsx(as.data.frame(provFundedServiceDivision), 
file="output/provFundedSD.xlsx", sheetName="provFundingSD")

thanks to everyone for reading and helping me out. This is my first question on stack overflow. Cheers!

3
votes

Use ungroup() at the end of your dplyr chain:

provFundedServiceDivision <- compensationBase2014 %>% 
    group_by(serviceDivision,provinciallyFunded) %>% 
    summarise(total=sum(fundingRaw)) %>% 
    mutate(percent = paste0(round(100 * total/sum(total),1), "%")) %>%

    # Add ungroup to the end
    ungroup()

#write to second sheet
write.xlsx(provFundedServiceDivision, file="output/provFundedSD.xlsx",   
           sheetName="provFundingSD")

Instead of...

class(provFundedServiceDivision)[1]
[1] "grouped_df"

You get...

class(provFundedServiceDivision)[1]
[1] "tbl_df"