Exporting tables to xls or ppt

#4

With general request comes general response :smile:
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)

3 Likes

Is "create a reprex" becoming a bit too stackoverflow-esque?
#5

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

1 Like

#6
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)

0 Likes

#7

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

0 Likes

#8

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

0 Likes

#9

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

0 Likes

#10
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)

0 Likes

#11

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

0 Likes

#12

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

## 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)
1 Like

#13

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.

0 Likes

#14

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

0 Likes

#15

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?

0 Likes

#16

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")
2 Likes

#17

As taras mentioned before 'everything is possible' :wink:. 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)
0 Likes

#18

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)?

0 Likes

#19

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)

0 Likes

#20

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

3 Likes

#21

Hurrrray :clap:
I get it now.
I simply need to save a table I need as ned data frame and export it.
I did that:

ddply(NL.Current.data, .(year,hy), summarize,  A2TB=mean(A2TB), B1=mean(B1), C1=mean(C1), D1=mean(D1,na.rm=TRUE), E1=mean(E1), F1=mean(F1), G1=mean(G1))

having this in my console:

  year hy     A2TB       B1       C1       D1       E1       F1       G1
1 2017  2 80.02937 91.64464 92.86344 84.05172 92.29075 93.95742 58.46549
2 2018  1 79.39543 91.75817 92.38125 85.85480 91.81370 93.25725 56.78593
3 2018  2 78.93082 91.24738 92.45283 86.92946 91.56184 92.98742 56.06918

then I saved that as df.stats dataframe:

df.stats <- ddply(NL.Current.data, .(year,hy), summarize,  A2TB=mean(A2TB), B1=mean(B1), C1=mean(C1), D1=mean(D1,na.rm=TRUE), E1=mean(E1), F1=mean(F1), G1=mean(G1))

and used Adam83's code:

df.stats 
      write.xlsx(df.stats, "writeStats.xlsx", colNames = TRUE, rowNames=TRUE)

and I've got my Excel table!

Well done and thank you very much!!!

The only issue I have now is exporting CrossTables (generated in my reprex using gmodels library) as a different solution is required I suppose...

2 Likes

#22

Since it seems that your original question has been answered, would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

0 Likes

closed #23

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes