Exporting tables to xls or ppt

Slavek · January 25, 2019, 2:47pm

I've been looking for a way of exporting my results (tables in particular) to excel or ppt but all I can find is exporting entire datasets or exporting tables to txt files.
Is any way of exporting:

tables from my console to excel (table format is not that important but it would be good to be able to export at least its content)
plots to ppt (not as images)

Is this function available in R Studio?

Can you help?

andresrcs · January 25, 2019, 2:49pm

We don't really have enough info to help you out. Could you ask this with a minimal REPRoducible EXample (reprex)? A reprex makes it much easier for others to understand your issue and figure out how to help.

Slavek · January 25, 2019, 2:52pm

This is a general request related to all types of tables renerated in R Studio console (such as table function, CrossTable from gmodels package or any other form of table)...

taras · January 25, 2019, 3:00pm

With general request comes general response
For export into Excel, you could use a function like openxlsx::write.xlsx() or readr::write_csv() (csv will be consumed by Excel without a problem)

For export to ppt, you could:

Save plots with ggplot2's ggsave() and then paste them into your ppt
Switch from an .R script to a .Rmd document and knit it as a presentation.

Feel free to explore every mentioned function by calling help on it in the console. You'll find out what arguments you need to pass and how to edit your outcome.
(for example, type ?write_csv in the console after loading the readr package)

martin.R · January 25, 2019, 3:04pm

You can insert plots straight into PowerPoint like this:
https://davidgohel.github.io/officer/articles/powerpoint.html

Slavek · January 25, 2019, 3:15pm

library(readxl)
#> Warning: package 'readxl' was built under R version 3.4.4
df <- read_excel("C:/Users/sdanilowicz/Documents/Test File for tables.xlsx", sheet = "Sales")

summary(df)
#>      URN            InterviewDate                       A2       
#>  Length:14          Min.   :2017-10-15 08:42:00   Min.   :10.00  
#>  Class :character   1st Qu.:2018-11-11 21:29:30   1st Qu.:10.00  
#>  Mode  :character   Median :2018-12-09 01:27:30   Median :10.00  
#>                     Mean   :2018-09-12 10:15:51   Mean   :17.14  
#>                     3rd Qu.:2018-12-18 20:19:45   3rd Qu.:20.00  
#>                     Max.   :2018-12-31 09:51:00   Max.   :40.00  
#>        B1            C1               D1            E1     
#>  Min.   :100   Min.   : 90.00   Min.   :  0   Min.   :100  
#>  1st Qu.:100   1st Qu.:100.00   1st Qu.:100   1st Qu.:100  
#>  Median :100   Median :100.00   Median :100   Median :100  
#>  Mean   :100   Mean   : 99.29   Mean   : 85   Mean   :100  
#>  3rd Qu.:100   3rd Qu.:100.00   3rd Qu.:100   3rd Qu.:100  
#>  Max.   :100   Max.   :100.00   Max.   :100   Max.   :100  
#>        F1               G1               H1               I1        
#>  Min.   : 90.00   Min.   :  0.00   Min.   : 90.00   Min.   : 90.00  
#>  1st Qu.:100.00   1st Qu.:100.00   1st Qu.:100.00   1st Qu.:100.00  
#>  Median :100.00   Median :100.00   Median :100.00   Median :100.00  
#>  Mean   : 98.57   Mean   : 92.86   Mean   : 98.57   Mean   : 98.57  
#>  3rd Qu.:100.00   3rd Qu.:100.00   3rd Qu.:100.00   3rd Qu.:100.00  
#>  Max.   :100.00   Max.   :100.00   Max.   :100.00   Max.   :100.00  
#>     Top2Rec   
#>  Min.   :100  
#>  1st Qu.:100  
#>  Median :100  
#>  Mean   :100  
#>  3rd Qu.:100  
#>  Max.   :100
str(df)
#> Classes 'tbl_df', 'tbl' and 'data.frame':    14 obs. of  12 variables:
#>  $ URN          : chr  "9ES014144243" "9ES014260564" "9ES014493281" "9ES014501736" ...
#>  $ InterviewDate: POSIXct, format: "2017-10-15 08:42:00" "2017-10-17 16:12:00" ...
#>  $ A2           : num  40 10 10 20 40 20 10 10 10 10 ...
#>  $ B1           : num  100 100 100 100 100 100 100 100 100 100 ...
#>  $ C1           : num  100 100 100 100 100 100 100 100 100 90 ...
#>  $ D1           : num  100 100 100 100 100 100 100 100 0 90 ...
#>  $ E1           : num  100 100 100 100 100 100 100 100 100 100 ...
#>  $ F1           : num  100 100 100 100 100 100 100 100 90 90 ...
#>  $ G1           : num  100 100 100 100 100 100 100 100 100 0 ...
#>  $ H1           : num  100 100 100 100 100 100 100 90 100 90 ...
#>  $ I1           : num  100 100 90 100 100 100 100 100 100 90 ...
#>  $ Top2Rec      : num  100 100 100 100 100 100 100 100 100 100 ...


df$year <- as.numeric(format(df$InterviewDate,'%Y'))
df$month <- as.numeric(format(df$InterviewDate,'%m'))

library(gmodels)
#> Warning: package 'gmodels' was built under R version 3.4.4
CrossTable(df$year,df$month, format="SPSS")
#> 
#>    Cell Contents
#> |-------------------------|
#> |                   Count |
#> | Chi-square contribution |
#> |             Row Percent |
#> |          Column Percent |
#> |           Total Percent |
#> |-------------------------|
#> 
#> Total Observations in Table:  14 
#> 
#>              | df$month 
#>      df$year |       10  |       11  |       12  | Row Total | 
#> -------------|-----------|-----------|-----------|-----------|
#>         2017 |        2  |        1  |        0  |        3  | 
#>              |    5.762  |    0.024  |    1.714  |           | 
#>              |   66.667% |   33.333% |    0.000% |   21.429% | 
#>              |  100.000% |   25.000% |    0.000% |           | 
#>              |   14.286% |    7.143% |    0.000% |           | 
#> -------------|-----------|-----------|-----------|-----------|
#>         2018 |        0  |        3  |        8  |       11  | 
#>              |    1.571  |    0.006  |    0.468  |           | 
#>              |    0.000% |   27.273% |   72.727% |   78.571% | 
#>              |    0.000% |   75.000% |  100.000% |           | 
#>              |    0.000% |   21.429% |   57.143% |           | 
#> -------------|-----------|-----------|-----------|-----------|
#> Column Total |        2  |        4  |        8  |       14  | 
#>              |   14.286% |   28.571% |   57.143% |           | 
#> -------------|-----------|-----------|-----------|-----------|
#> 
#> 

library(zoo)
#> Warning: package 'zoo' was built under R version 3.4.4
#> 
#> Attaching package: 'zoo'
#> The following objects are masked from 'package:base':
#> 
#>     as.Date, as.Date.numeric
df$yq <- as.yearqtr(df$InterviewDate, format = "%Y-%m-%d")


CrossTable(df$year,df$yq)
#> 
#>  
#>    Cell Contents
#> |-------------------------|
#> |                       N |
#> | Chi-square contribution |
#> |           N / Row Total |
#> |           N / Col Total |
#> |         N / Table Total |
#> |-------------------------|
#> 
#>  
#> Total Observations in Table:  14 
#> 
#>  
#>              | df$yq 
#>      df$year |   2017 Q4 |   2018 Q4 | Row Total | 
#> -------------|-----------|-----------|-----------|
#>         2017 |         3 |         0 |         3 | 
#>              |     8.643 |     2.357 |           | 
#>              |     1.000 |     0.000 |     0.214 | 
#>              |     1.000 |     0.000 |           | 
#>              |     0.214 |     0.000 |           | 
#> -------------|-----------|-----------|-----------|
#>         2018 |         0 |        11 |        11 | 
#>              |     2.357 |     0.643 |           | 
#>              |     0.000 |     1.000 |     0.786 | 
#>              |     0.000 |     1.000 |           | 
#>              |     0.000 |     0.786 |           | 
#> -------------|-----------|-----------|-----------|
#> Column Total |         3 |        11 |        14 | 
#>              |     0.214 |     0.786 |           | 
#> -------------|-----------|-----------|-----------|
#> 
#>

Created on 2019-01-25 by the reprex package (v0.2.1)

taras · January 25, 2019, 3:16pm

Oh, so you want these summary tables from your console to be exported into Excel, is that correct?

Slavek · January 25, 2019, 3:19pm

I think these functions would export the entire data frame rather than tables produced...

Slavek · January 25, 2019, 3:21pm

Yes, just tables. Ideally in a format which allows editing in excel...

Slavek · January 25, 2019, 3:25pm

library(readxl)
#> Warning: package 'readxl' was built under R version 3.4.4
df <- read_excel("C:/Users/sdanilowicz/Documents/Test File for tables.xlsx", sheet = "Sales")
df$year <- as.numeric(format(df$InterviewDate,'%Y'))
df$month <- as.numeric(format(df$InterviewDate,'%m'))

table(df$month)
#> 
#> 10 11 12 
#>  2  4  8

reprex()
#> Error in reprex(): could not find function "reprex"

Created on 2019-01-25 by the reprex package (v0.2.1)

Slavek · January 25, 2019, 3:26pm

Even simple tables like the one above...
I just need to find a way of exporting my results to excel

adam83 · January 25, 2019, 3:39pm

Hey Slavek there is a nice example for the openxlsx::write.xlsx() function. It may be a easy solution for you

## Lists elements are written to individual worksheets, using list names as sheet names if available
l <- list("IRIS" = iris, "MTCATS" = mtcars, matrix(runif(1000), ncol = 5))
write.xlsx(l, "writeList1.xlsx", colNames = TRUE)

taras · January 25, 2019, 3:39pm

That is correct. Seems like the CrossTable function you're using is providing you with summary info, but it isn't quite a suitable data frame, I would not want this to end up in Excel.

If I was tasked with exporting these summary tables to Excel (for whatever reason), I'd probably wrangle the data a bit (a bit of dplyr and tidyr functions: group_by, summarise, gather() and spread should do it) and get the results in the format I want which is a data frame and not a markup/markdown table.

I hope others have a better advice for you. I do question the need to export summary statistics into Excel, but it is probably none of my business.

taras · January 25, 2019, 3:40pm

Well, he wants summary statistics into Excel, not the data frames...

Slavek · January 25, 2019, 3:58pm

even if results are not formated (for example based on ddply function from plyr package) I would like to export them to excel. Is it possible at all?

taras · January 25, 2019, 4:16pm

Everything is possible, but your solution sometimes may match the complexity/uniqueness of your problem.

Here is an example of how to make some summary export possible:

library(tidyverse)
df <- tibble(a = c(1,2,3,5,7,8,4,3,5,7),
             b = c(9,6,3,3,5,6,7,3,2,4))
summary <- as_data_frame(summary(df))
write_csv(summary, "summary.csv")

adam83 · January 25, 2019, 4:50pm

As taras mentioned before 'everything is possible' . So, your summary statistics have to be in a suitable data frame, otherwise you have to prepare your summary results before your export to an excel-file (you need some coding). So back to taras example:

library(openxlsx)
library(tibble)
df <- tibble(a = c(1,2,3,5,7,8,4,3,5,7),
             b = c(9,6,3,3,5,6,7,3,2,4))
df.stats <- apply(df, 2, summary)
write.xlsx(df.stats, "writeStats.xlsx", colNames = TRUE, rowNames=TRUE)

Slavek · January 28, 2019, 5:40pm

Thank you but I don't need to export any form of summary (codes above) but full tables with results. Maybe the best option would be exporting ggplot plots with all data behind to ppt or excel (editable outputs)?

andresrcs · January 28, 2019, 5:57pm

Is not clear what do you mean with "exporting ggplot plots with all data behind to ppt or excel "

At the beginning of this thread you were asking for a general answer and @taras already gave you one that you can generalize as needed.

library(tidyverse)
df <- tibble(a = c(1,2,3,5,7,8,4,3,5,7),
             b = c(9,6,3,3,5,6,7,3,2,4))
summary <- as_data_frame(summary(df))
write_csv(summary, "summary.csv")

Summarizing, you can export to excel everything you can manage to put into a rectangular format (i.e. dataframe)

taras · January 28, 2019, 6:24pm

Now I am completely lost...

Full tables can be exported with write_csv() or any other weapon of choice, I think we touched on that before, but you said you don't want "the entire data frame", and so my question is:

giphy