Export R output in Excel

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Export R output in Excel

bmac
Hi,

How do I export results from R to Excel in a format-friendly way? For example, when I copy and paste my results into excel, the formatting is messed up.

Thanks.
 
Bryan Mac
[hidden email]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Rolf Turner
On 29/12/16 10:45, Bryan Mac wrote:
> Hi,
>
> How do I export results from R to Excel in a format-friendly way? For
> example, when I copy and paste my results into excel, the formatting
> is messed up.


Short answer:  *Don't*.  ("Friends don't let friends use excel for
statistics.")

Longer answer:  Googling on "export R data to excel" yields lots of
"useful" hits --- "useful" given the (false) assertion that it is useful
to export things to excel.

cheers,

Rolf Turner

--
Technical Editor ANZJS
Department of Statistics
University of Auckland
Phone: +64-9-373-7599 ext. 88276

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

bmac
Hi Rolf,

I wanted to export the output/results of R to an Excel file for easier comparisons/reporting. When I tried to copy and paste my output to an excel file the formatting was off.
I want to export my descriptive stats and the linear regression.

I googled “Export R output to excel” but did not find most of the hints “useful”. if anything, it got me more confused.

Thanks.

Bryan Mac
[hidden email]



> On Dec 28, 2016, at 3:15 PM, Rolf Turner <[hidden email]> wrote:
>
> On 29/12/16 10:45, Bryan Mac wrote:
>> Hi,
>>
>> How do I export results from R to Excel in a format-friendly way? For
>> example, when I copy and paste my results into excel, the formatting
>> is messed up.
>
>
> Short answer:  *Don't*.  ("Friends don't let friends use excel for statistics.")
>
> Longer answer:  Googling on "export R data to excel" yields lots of "useful" hits --- "useful" given the (false) assertion that it is useful to export things to excel.
>
> cheers,
>
> Rolf Turner
>
> --
> Technical Editor ANZJS
> Department of Statistics
> University of Auckland
> Phone: +64-9-373-7599 ext. 88276

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Rolf Turner
On 29/12/16 12:48, Bryan Mac wrote:
> Hi Rolf,
>
> I wanted to export the output/results of R to an Excel file for
> easier comparisons/reporting. When I tried to copy and paste my
> output to an excel file the formatting was off. I want to export my
> descriptive stats and the linear regression.

This makes little to no sense to me.  Spreadsheets are for use in
storing data, not for displaying the output of analyses.  (I know that
Excel users do this sort of thing, but then people do all sorts of
irrational things.)

> I googled “Export R output to excel” but did not find most of the
> hints “useful”. if anything, it got me more confused.
>
> Thanks.

(1) The best advice is still "*Don't*."

(2) You do not need Excel to make comparisons and report.  In fact it is
a handicap.  Re-think your strategy.

(3) If you insist in proceeding in a wrong-headed manner, isn't the item
about XLConnect (3rd answer, 1st hit) "useful"?

cheers,

Rolf Turner

--
Technical Editor ANZJS
Department of Statistics
University of Auckland
Phone: +64-9-373-7599 ext. 88276

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

Jim Lemon-4
In reply to this post by bmac
Hi Bryan,
When I have to do something like this, I usually go through HTML
output and import it into MS Word. I am not suggesting that this is
the best thing to do, but it might get you out of trouble. I'm not
sure whether importing HTML into Excel will work as well. I assume
that you are running analyses in R and want to export the output that
appears in the console window. If so, try producing HTML output with
the prettyR or R2HTML packages and importing it. There are other ways
to do this, but the learning curve is steep and you might not want to
climb it right now.

Jim


On Thu, Dec 29, 2016 at 8:45 AM, Bryan Mac <[hidden email]> wrote:

> Hi,
>
> How do I export results from R to Excel in a format-friendly way? For example, when I copy and paste my results into excel, the formatting is messed up.
>
> Thanks.
>
> Bryan Mac
> [hidden email]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

bmac
Hi Jim,

Your assumption is correct. When running the analyses in R and want to export the output that appears in the console window to Excel(.csv) file.
I believe it is easier to do if the export it done to an Excel (.CSV) file.

So is there a way to export the analyses in the console window to a .CSV file with the good formatting ?
I am looking to export the whole output if possible.

I found this code, but it doesn’t cover the whole output of the console.

write.csv(coef(summary(test)), file=“test.csv”)

My whole output consists of descriptives and regressions.

Best,

Bryan Mac
[hidden email]



> On Dec 28, 2016, at 6:33 PM, Jim Lemon <[hidden email]> wrote:
>
> Hi Bryan,
> When I have to do something like this, I usually go through HTML
> output and import it into MS Word. I am not suggesting that this is
> the best thing to do, but it might get you out of trouble. I'm not
> sure whether importing HTML into Excel will work as well. I assume
> that you are running analyses in R and want to export the output that
> appears in the console window. If so, try producing HTML output with
> the prettyR or R2HTML packages and importing it. There are other ways
> to do this, but the learning curve is steep and you might not want to
> climb it right now.
>
> Jim
>
>
> On Thu, Dec 29, 2016 at 8:45 AM, Bryan Mac <[hidden email]> wrote:
>> Hi,
>>
>> How do I export results from R to Excel in a format-friendly way? For example, when I copy and paste my results into excel, the formatting is messed up.
>>
>> Thanks.
>>
>> Bryan Mac
>> [hidden email]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

OmarGon
In reply to this post by Jim Lemon-4
use "write.csv("you-df", "name-of-file.csv", row.names = FALSE).

And Google please, as others have suggested.

2016-12-28 21:33 GMT-05:00 Jim Lemon <[hidden email]>:

> Hi Bryan,
> When I have to do something like this, I usually go through HTML
> output and import it into MS Word. I am not suggesting that this is
> the best thing to do, but it might get you out of trouble. I'm not
> sure whether importing HTML into Excel will work as well. I assume
> that you are running analyses in R and want to export the output that
> appears in the console window. If so, try producing HTML output with
> the prettyR or R2HTML packages and importing it. There are other ways
> to do this, but the learning curve is steep and you might not want to
> climb it right now.
>
> Jim
>
>
> On Thu, Dec 29, 2016 at 8:45 AM, Bryan Mac <[hidden email]> wrote:
> > Hi,
> >
> > How do I export results from R to Excel in a format-friendly way? For
> example, when I copy and paste my results into excel, the formatting is
> messed up.
> >
> > Thanks.
> >
> > Bryan Mac
> > [hidden email]
> >
> > ______________________________________________
> > [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

PIKAL Petr
In reply to this post by bmac
Hi

For rectangular data

write.table(tab, "clipboard", sep = "\t", row.names = F)
followed by Ctrl-V in Excel

or
write.table(tab, "somefile.xls", sep = "\t", row.names = F)

For free format output like summary(somefit) I prefer to copy it to Word and use font like  Courier New with monospaced letters

Cheers
Petr


> -----Original Message-----
> From: R-help [mailto:[hidden email]] On Behalf Of Bryan Mac
> Sent: Wednesday, December 28, 2016 10:45 PM
> To: [hidden email]
> Subject: [R] Export R output in Excel
>
> Hi,
>
> How do I export results from R to Excel in a format-friendly way? For
> example, when I copy and paste my results into excel, the formatting is
> messed up.
>
> Thanks.
>
> Bryan Mac
> [hidden email]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-
> guide.html
> and provide commented, minimal, self-contained, reproducible code.

________________________________
Tento e-mail a jakékoliv k němu připojené dokumenty jsou důvěrné a jsou určeny pouze jeho adresátům.
Jestliže jste obdržel(a) tento e-mail omylem, informujte laskavě neprodleně jeho odesílatele. Obsah tohoto emailu i s přílohami a jeho kopie vymažte ze svého systému.
Nejste-li zamýšleným adresátem tohoto emailu, nejste oprávněni tento email jakkoliv užívat, rozšiřovat, kopírovat či zveřejňovat.
Odesílatel e-mailu neodpovídá za eventuální škodu způsobenou modifikacemi či zpožděním přenosu e-mailu.

V případě, že je tento e-mail součástí obchodního jednání:
- vyhrazuje si odesílatel právo ukončit kdykoliv jednání o uzavření smlouvy, a to z jakéhokoliv důvodu i bez uvedení důvodu.
- a obsahuje-li nabídku, je adresát oprávněn nabídku bezodkladně přijmout; Odesílatel tohoto e-mailu (nabídky) vylučuje přijetí nabídky ze strany příjemce s dodatkem či odchylkou.
- trvá odesílatel na tom, že příslušná smlouva je uzavřena teprve výslovným dosažením shody na všech jejích náležitostech.
- odesílatel tohoto emailu informuje, že není oprávněn uzavírat za společnost žádné smlouvy s výjimkou případů, kdy k tomu byl písemně zmocněn nebo písemně pověřen a takové pověření nebo plná moc byly adresátovi tohoto emailu případně osobě, kterou adresát zastupuje, předloženy nebo jejich existence je adresátovi či osobě jím zastoupené známá.

This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients.
If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system.
If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner.
The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email.

In case that this e-mail forms part of business dealings:
- the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning.
- if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation.
- the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects.
- the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.
______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

JohnDee
In reply to this post by bmac
On Wed, 28 Dec 2016 13:45:25 -0800
Bryan Mac <[hidden email]> wrote:

> Hi,
>
> How do I export results from R to Excel in a format-friendly way? For
> example, when I copy and paste my results into excel, the formatting
> is messed up.
>
> Thanks.
>  
> Bryan Mac
> [hidden email]
>
Your purpose is not clear.  If you are planning to do MORE statistical
work with excel using R output, don't do it.  Learn the R equivalents.
There's nothing that you can do in Excel that can't be done in R.  And,
while Microsoft has made great strides in the reliability of it's
numbers and calculating routines, it is still a spreadsheet.

If you want to add tabular data to a word file from R, out
put it to a csv or tab delimited format and then copy into Word or
LibreOffice Writer. From there you can select the text of the table and
transform it into a table using the table menu. If you want formatted
statistical output, you can sink() the output to an asscii or utf
text file, paste it into Word and then convert the font of the pasted
segment to a fixed pitch font.  R output tends to follow the old-time
typewriter approach to formatting (spaces and tabs), meaning that kerned
fonts behave in horrible ways, fonts are rubbery, etc.


--

John

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

Jim Lemon-4
In reply to this post by bmac
Hi Bryan,
What functions like "htmlize" (prettyR) do is format the basic R
output into HTML tables with the option of interspersed graphics.
While I usually stop at the HTML stage, the output files can be
imported into Word for those who cannot work out how to open them with
an HTML browser. I just tried the example for "htmlize" and it imports
into Libre Office Writer fine, but doesn't fit so well into Libre
Office Calc, which does not bode well for an import into Excel. At
best you will get formatted output, but you cannot play with the
numbers as you would in a spreadsheet. Petr's suggestion, which I just
read, is an alternative that may be more useful to you.

Jim


On Thu, Dec 29, 2016 at 2:58 PM, Bryan Mac <[hidden email]> wrote:

> Hi Jim,
>
> Your assumption is correct. When running the analyses in R and want to export the output that appears in the console window to Excel(.csv) file.
> I believe it is easier to do if the export it done to an Excel (.CSV) file.
>
> So is there a way to export the analyses in the console window to a .CSV file with the good formatting ?
> I am looking to export the whole output if possible.
>
> I found this code, but it doesn’t cover the whole output of the console.
>
> write.csv(coef(summary(test)), file=“test.csv”)
>
> My whole output consists of descriptives and regressions.
>
> Best,
>
> Bryan Mac
> [hidden email]
>
>
>
>> On Dec 28, 2016, at 6:33 PM, Jim Lemon <[hidden email]> wrote:
>>
>> Hi Bryan,
>> When I have to do something like this, I usually go through HTML
>> output and import it into MS Word. I am not suggesting that this is
>> the best thing to do, but it might get you out of trouble. I'm not
>> sure whether importing HTML into Excel will work as well. I assume
>> that you are running analyses in R and want to export the output that
>> appears in the console window. If so, try producing HTML output with
>> the prettyR or R2HTML packages and importing it. There are other ways
>> to do this, but the learning curve is steep and you might not want to
>> climb it right now.
>>
>> Jim
>>
>>
>> On Thu, Dec 29, 2016 at 8:45 AM, Bryan Mac <[hidden email]> wrote:
>>> Hi,
>>>
>>> How do I export results from R to Excel in a format-friendly way? For example, when I copy and paste my results into excel, the formatting is messed up.
>>>
>>> Thanks.
>>>
>>> Bryan Mac
>>> [hidden email]
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Erich Subscriptions
In reply to this post by Rolf Turner
Well, my few cents again.
the packages
openxslx and xlsx allow to write dataframes as Excel sheets.
(xlsx is Java based, so it has more requirements to run than openxlsx,
which is just C++ based)

On Windows, R tools for Visual Studio allows Excel export.
For Windows, there also is our Excel add-in RExcel allowing
to use R from within Excel, and the R package rcom
which also allows to interact with Excel from R (more than just writing Excel workbooks).
Our products (rcom and RExcel), however, are not unter a FOSS license.

And a more general remark: There are a lot of things where R is a much better choice than Excel,
but there are a few things where it really makes sense  to use spreadsheets.

Spreadsheets offer a totally different paradigm to work with data, or more generally,
numbers and formulas.
One can interact with the data directly, not hide them behind variable names.
And, the interaction is haptic, gesture based, not expressed as a language.

Rearranging the layout of a pivot table by dragging variable “blocks”
is very intuitive and something which R itself doe not offer
(in fact, I wrote an add-in for R Commander to implement it).

Of course, Excel is not a good chice for a polished reproducible workflow.
But I think quite a few people (including me), when starting a new project,
are not ready immediately to set up this “perfect” workflow,
and it is much easier to experiment with the data with a spreadsheet based
interface.

For me, working with spreadsheets is more like improvising some Jazz,
and writing R code is like writing a score for a composition.





> On 29 Dec 2016, at 00:15, Rolf Turner <[hidden email]> wrote:
>
> On 29/12/16 10:45, Bryan Mac wrote:
>> Hi,
>>
>> How do I export results from R to Excel in a format-friendly way? For
>> example, when I copy and paste my results into excel, the formatting
>> is messed up.
>
>
> Short answer:  *Don't*.  ("Friends don't let friends use excel for statistics.")
>
> Longer answer:  Googling on "export R data to excel" yields lots of "useful" hits --- "useful" given the (false) assertion that it is useful to export things to excel.
>
> cheers,
>
> Rolf Turner
>
> --
> Technical Editor ANZJS
> Department of Statistics
> University of Auckland
> Phone: +64-9-373-7599 ext. 88276
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Peter Dalgaard-2
In reply to this post by Rolf Turner
I don't really disagree with the below, but part of the issue is that analyses do not typically output results in data frame like formats (neither in the textual form or as R objects). Some people have attacked this issue by wrangling output into data frames, check the "broom" package.

(The ideology of sweeping much of the flexibility of R away by turning all data structures into data sets strikes me a bit much like reinventing SAS, but this might be a spot where it comes in useful.)

-pd

> On 29 Dec 2016, at 01:05 , Rolf Turner <[hidden email]> wrote:
>
> On 29/12/16 12:48, Bryan Mac wrote:
>> Hi Rolf,
>>
>> I wanted to export the output/results of R to an Excel file for
>> easier comparisons/reporting. When I tried to copy and paste my
>> output to an excel file the formatting was off. I want to export my
>> descriptive stats and the linear regression.
>
> This makes little to no sense to me.  Spreadsheets are for use in storing data, not for displaying the output of analyses.  (I know that Excel users do this sort of thing, but then people do all sorts of irrational things.)
>
>> I googled “Export R output to excel” but did not find most of the
>> hints “useful”. if anything, it got me more confused.
>>
>> Thanks.
>
> (1) The best advice is still "*Don't*."
>
> (2) You do not need Excel to make comparisons and report.  In fact it is a handicap.  Re-think your strategy.
>
> (3) If you insist in proceeding in a wrong-headed manner, isn't the item about XLConnect (3rd answer, 1st hit) "useful"?
>
> cheers,
>
> Rolf Turner
>
> --
> Technical Editor ANZJS
> Department of Statistics
> University of Auckland
> Phone: +64-9-373-7599 ext. 88276
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

--
Peter Dalgaard, Professor,
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: (+45)38153501
Office: A 4.23
Email: [hidden email]  Priv: [hidden email]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

Spencer Graves-4
In reply to this post by Jim Lemon-4
       The "writeFindFn2xls" function in the sos package include 3
different ways to write an Excel workbook, depending on which packages,
etc., you have installed.  I wrote that, because I could not find one
contributed package that as easy to install on every operating system.  
This writes an Excel workbook with 3 sheets. That may not be what you
want, but it might provide other options.


Spencer Graves

On 2016-12-29 3:19 AM, Jim Lemon wrote:

> Hi Bryan,
> What functions like "htmlize" (prettyR) do is format the basic R
> output into HTML tables with the option of interspersed graphics.
> While I usually stop at the HTML stage, the output files can be
> imported into Word for those who cannot work out how to open them with
> an HTML browser. I just tried the example for "htmlize" and it imports
> into Libre Office Writer fine, but doesn't fit so well into Libre
> Office Calc, which does not bode well for an import into Excel. At
> best you will get formatted output, but you cannot play with the
> numbers as you would in a spreadsheet. Petr's suggestion, which I just
> read, is an alternative that may be more useful to you.
>
> Jim
>
>
> On Thu, Dec 29, 2016 at 2:58 PM, Bryan Mac <[hidden email]> wrote:
>> Hi Jim,
>>
>> Your assumption is correct. When running the analyses in R and want to export the output that appears in the console window to Excel(.csv) file.
>> I believe it is easier to do if the export it done to an Excel (.CSV) file.
>>
>> So is there a way to export the analyses in the console window to a .CSV file with the good formatting ?
>> I am looking to export the whole output if possible.
>>
>> I found this code, but it doesn’t cover the whole output of the console.
>>
>> write.csv(coef(summary(test)), file=“test.csv”)
>>
>> My whole output consists of descriptives and regressions.
>>
>> Best,
>>
>> Bryan Mac
>> [hidden email]
>>
>>
>>
>>> On Dec 28, 2016, at 6:33 PM, Jim Lemon <[hidden email]> wrote:
>>>
>>> Hi Bryan,
>>> When I have to do something like this, I usually go through HTML
>>> output and import it into MS Word. I am not suggesting that this is
>>> the best thing to do, but it might get you out of trouble. I'm not
>>> sure whether importing HTML into Excel will work as well. I assume
>>> that you are running analyses in R and want to export the output that
>>> appears in the console window. If so, try producing HTML output with
>>> the prettyR or R2HTML packages and importing it. There are other ways
>>> to do this, but the learning curve is steep and you might not want to
>>> climb it right now.
>>>
>>> Jim
>>>
>>>
>>> On Thu, Dec 29, 2016 at 8:45 AM, Bryan Mac <[hidden email]> wrote:
>>>> Hi,
>>>>
>>>> How do I export results from R to Excel in a format-friendly way? For example, when I copy and paste my results into excel, the formatting is messed up.
>>>>
>>>> Thanks.
>>>>
>>>> Bryan Mac
>>>> [hidden email]
>>>>
>>>> ______________________________________________
>>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>>> and provide commented, minimal, self-contained, reproducible code.
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Bert Gunter-2
In reply to this post by Erich Subscriptions
(Private -- as this is just my personal opinion and not really helpful).

I found your comments informative. Thank you.

My own experience with scientific colleagues -- biologists mostly --
who use Excel in the way that you describe is that the "haptic" (great
word!) ease with which they manipulate the data almost inevitably
results in errors. That is, the *lack* of enforced structure in Excel
allows them to do things that they shouldn't or don't mean to do,
typically without raising any flags, typically causing downstream
errors that can be hard to trace. Irreproducibility follows.

My point is that the structure that you consider burdensome -- at
least initially -- is desirable exactly because it forces them to
think more carefully about what they are doing. Debugging, or worse
yet, failure to realize that debugging is needed, takes far more time
and is far more consequential.

As I said, just my opinion, no reply necessary, and I do appreciate
your thoughtful remarks.

Best,
Bert Gunter

Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Thu, Dec 29, 2016 at 2:16 AM, Erich Subscriptions
<[hidden email]> wrote:

> Well, my few cents again.
> the packages
> openxslx and xlsx allow to write dataframes as Excel sheets.
> (xlsx is Java based, so it has more requirements to run than openxlsx,
> which is just C++ based)
>
> On Windows, R tools for Visual Studio allows Excel export.
> For Windows, there also is our Excel add-in RExcel allowing
> to use R from within Excel, and the R package rcom
> which also allows to interact with Excel from R (more than just writing Excel workbooks).
> Our products (rcom and RExcel), however, are not unter a FOSS license.
>
> And a more general remark: There are a lot of things where R is a much better choice than Excel,
> but there are a few things where it really makes sense  to use spreadsheets.
>
> Spreadsheets offer a totally different paradigm to work with data, or more generally,
> numbers and formulas.
> One can interact with the data directly, not hide them behind variable names.
> And, the interaction is haptic, gesture based, not expressed as a language.
>
> Rearranging the layout of a pivot table by dragging variable “blocks”
> is very intuitive and something which R itself doe not offer
> (in fact, I wrote an add-in for R Commander to implement it).
>
> Of course, Excel is not a good chice for a polished reproducible workflow.
> But I think quite a few people (including me), when starting a new project,
> are not ready immediately to set up this “perfect” workflow,
> and it is much easier to experiment with the data with a spreadsheet based
> interface.
>
> For me, working with spreadsheets is more like improvising some Jazz,
> and writing R code is like writing a score for a composition.
>
>
>
>
>
>> On 29 Dec 2016, at 00:15, Rolf Turner <[hidden email]> wrote:
>>
>> On 29/12/16 10:45, Bryan Mac wrote:
>>> Hi,
>>>
>>> How do I export results from R to Excel in a format-friendly way? For
>>> example, when I copy and paste my results into excel, the formatting
>>> is messed up.
>>
>>
>> Short answer:  *Don't*.  ("Friends don't let friends use excel for statistics.")
>>
>> Longer answer:  Googling on "export R data to excel" yields lots of "useful" hits --- "useful" given the (false) assertion that it is useful to export things to excel.
>>
>> cheers,
>>
>> Rolf Turner
>>
>> --
>> Technical Editor ANZJS
>> Department of Statistics
>> University of Auckland
>> Phone: +64-9-373-7599 ext. 88276
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Bert Gunter-2
Oh nuts! I replied all. I apologize for the noise!

Cheers,
Bert


Bert Gunter

"The trouble with having an open mind is that people keep coming along
and sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )


On Thu, Dec 29, 2016 at 6:40 AM, Bert Gunter <[hidden email]> wrote:

> (Private -- as this is just my personal opinion and not really helpful).
>
> I found your comments informative. Thank you.
>
> My own experience with scientific colleagues -- biologists mostly --
> who use Excel in the way that you describe is that the "haptic" (great
> word!) ease with which they manipulate the data almost inevitably
> results in errors. That is, the *lack* of enforced structure in Excel
> allows them to do things that they shouldn't or don't mean to do,
> typically without raising any flags, typically causing downstream
> errors that can be hard to trace. Irreproducibility follows.
>
> My point is that the structure that you consider burdensome -- at
> least initially -- is desirable exactly because it forces them to
> think more carefully about what they are doing. Debugging, or worse
> yet, failure to realize that debugging is needed, takes far more time
> and is far more consequential.
>
> As I said, just my opinion, no reply necessary, and I do appreciate
> your thoughtful remarks.
>
> Best,
> Bert Gunter
>
> Bert Gunter
>
> "The trouble with having an open mind is that people keep coming along
> and sticking things into it."
> -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
>
>
> On Thu, Dec 29, 2016 at 2:16 AM, Erich Subscriptions
> <[hidden email]> wrote:
>> Well, my few cents again.
>> the packages
>> openxslx and xlsx allow to write dataframes as Excel sheets.
>> (xlsx is Java based, so it has more requirements to run than openxlsx,
>> which is just C++ based)
>>
>> On Windows, R tools for Visual Studio allows Excel export.
>> For Windows, there also is our Excel add-in RExcel allowing
>> to use R from within Excel, and the R package rcom
>> which also allows to interact with Excel from R (more than just writing Excel workbooks).
>> Our products (rcom and RExcel), however, are not unter a FOSS license.
>>
>> And a more general remark: There are a lot of things where R is a much better choice than Excel,
>> but there are a few things where it really makes sense  to use spreadsheets.
>>
>> Spreadsheets offer a totally different paradigm to work with data, or more generally,
>> numbers and formulas.
>> One can interact with the data directly, not hide them behind variable names.
>> And, the interaction is haptic, gesture based, not expressed as a language.
>>
>> Rearranging the layout of a pivot table by dragging variable “blocks”
>> is very intuitive and something which R itself doe not offer
>> (in fact, I wrote an add-in for R Commander to implement it).
>>
>> Of course, Excel is not a good chice for a polished reproducible workflow.
>> But I think quite a few people (including me), when starting a new project,
>> are not ready immediately to set up this “perfect” workflow,
>> and it is much easier to experiment with the data with a spreadsheet based
>> interface.
>>
>> For me, working with spreadsheets is more like improvising some Jazz,
>> and writing R code is like writing a score for a composition.
>>
>>
>>
>>
>>
>>> On 29 Dec 2016, at 00:15, Rolf Turner <[hidden email]> wrote:
>>>
>>> On 29/12/16 10:45, Bryan Mac wrote:
>>>> Hi,
>>>>
>>>> How do I export results from R to Excel in a format-friendly way? For
>>>> example, when I copy and paste my results into excel, the formatting
>>>> is messed up.
>>>
>>>
>>> Short answer:  *Don't*.  ("Friends don't let friends use excel for statistics.")
>>>
>>> Longer answer:  Googling on "export R data to excel" yields lots of "useful" hits --- "useful" given the (false) assertion that it is useful to export things to excel.
>>>
>>> cheers,
>>>
>>> Rolf Turner
>>>
>>> --
>>> Technical Editor ANZJS
>>> Department of Statistics
>>> University of Auckland
>>> Phone: +64-9-373-7599 ext. 88276
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Export R output in Excel

R help mailing list-2
In reply to this post by PIKAL Petr
Hi Bryan (and Petr),

If you want to write tsv-style data from R to clipboard on a Mac (e.g.
for pasting into Numbers), you should do:

> x1 <- matrix(1:6, nrow =2)

> clip <- pipe("pbcopy", "w")
> write.table(x1, file=clip, sep = "\t", row.names = FALSE, fileEncoding = "UTF-8" )
> close(clip)
> gc()

> ?write.table
> ?connections

Adding an extra call to gc() (garbage collection) after writing to
clipboard will
close all unused connections (useful if a connection has been entered
incorrectly).

HTH,

Bill
William Michels, Ph.D.

http://stackoverflow.com/questions/14547069/how-to-write-from-r-to-the-clipboard-on-a-mac
http://stackoverflow.com/questions/30445875/what-exactly-is-a-connection-in-r


On Wed, Dec 28, 2016 at 11:14 PM, PIKAL Petr <[hidden email]> wrote:

> Hi
>
> For rectangular data
>
> write.table(tab, "clipboard", sep = "\t", row.names = F)
> followed by Ctrl-V in Excel
>
> or
> write.table(tab, "somefile.xls", sep = "\t", row.names = F)
>
> For free format output like summary(somefit) I prefer to copy it to Word and use font like  Courier New with monospaced letters
>
> Cheers
> Petr
>
>
>> -----Original Message-----
>> From: R-help [mailto:[hidden email]] On Behalf Of Bryan Mac
>> Sent: Wednesday, December 28, 2016 10:45 PM
>> To: [hidden email]
>> Subject: [R] Export R output in Excel
>>
>> Hi,
>>
>> How do I export results from R to Excel in a format-friendly way? For
>> example, when I copy and paste my results into excel, the formatting is
>> messed up.
>>
>> Thanks.
>>
>> Bryan Mac
>> [hidden email]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-
>> guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
> ________________________________
> Tento e-mail a jakékoliv k němu připojené dokumenty jsou důvěrné a jsou určeny pouze jeho adresátům.
> Jestliže jste obdržel(a) tento e-mail omylem, informujte laskavě neprodleně jeho odesílatele. Obsah tohoto emailu i s přílohami a jeho kopie vymažte ze svého systému.
> Nejste-li zamýšleným adresátem tohoto emailu, nejste oprávněni tento email jakkoliv užívat, rozšiřovat, kopírovat či zveřejňovat.
> Odesílatel e-mailu neodpovídá za eventuální škodu způsobenou modifikacemi či zpožděním přenosu e-mailu.
>
> V případě, že je tento e-mail součástí obchodního jednání:
> - vyhrazuje si odesílatel právo ukončit kdykoliv jednání o uzavření smlouvy, a to z jakéhokoliv důvodu i bez uvedení důvodu.
> - a obsahuje-li nabídku, je adresát oprávněn nabídku bezodkladně přijmout; Odesílatel tohoto e-mailu (nabídky) vylučuje přijetí nabídky ze strany příjemce s dodatkem či odchylkou.
> - trvá odesílatel na tom, že příslušná smlouva je uzavřena teprve výslovným dosažením shody na všech jejích náležitostech.
> - odesílatel tohoto emailu informuje, že není oprávněn uzavírat za společnost žádné smlouvy s výjimkou případů, kdy k tomu byl písemně zmocněn nebo písemně pověřen a takové pověření nebo plná moc byly adresátovi tohoto emailu případně osobě, kterou adresát zastupuje, předloženy nebo jejich existence je adresátovi či osobě jím zastoupené známá.
>
> This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients.
> If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system.
> If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner.
> The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email.
>
> In case that this e-mail forms part of business dealings:
> - the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning.
> - if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation.
> - the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects.
> - the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

Erich Subscriptions
In reply to this post by Bert Gunter-2
Just a very brief footnote.
I is easy to write badly structured spreadsheets.
But if people dong this would not have spreadsheets
and be forded to write code, they probably also
would write badly structured code.

There is a lot of bad R code around also!



> On Dec 29, 2016, at 15:40, Bert Gunter <[hidden email]> wrote:
>
> (Private -- as this is just my personal opinion and not really helpful).
>
> I found your comments informative. Thank you.
>
> My own experience with scientific colleagues -- biologists mostly --
> who use Excel in the way that you describe is that the "haptic" (great
> word!) ease with which they manipulate the data almost inevitably
> results in errors. That is, the *lack* of enforced structure in Excel
> allows them to do things that they shouldn't or don't mean to do,
> typically without raising any flags, typically causing downstream
> errors that can be hard to trace. Irreproducibility follows.
>
> My point is that the structure that you consider burdensome -- at
> least initially -- is desirable exactly because it forces them to
> think more carefully about what they are doing. Debugging, or worse
> yet, failure to realize that debugging is needed, takes far more time
> and is far more consequential.
>
> As I said, just my opinion, no reply necessary, and I do appreciate
> your thoughtful remarks.
>
> Best,
> Bert Gunter
>
> Bert Gunter
>
> "The trouble with having an open mind is that people keep coming along
> and sticking things into it."
> -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
>
>
> On Thu, Dec 29, 2016 at 2:16 AM, Erich Subscriptions
> <[hidden email]> wrote:
>> Well, my few cents again.
>> the packages
>> openxslx and xlsx allow to write dataframes as Excel sheets.
>> (xlsx is Java based, so it has more requirements to run than openxlsx,
>> which is just C++ based)
>>
>> On Windows, R tools for Visual Studio allows Excel export.
>> For Windows, there also is our Excel add-in RExcel allowing
>> to use R from within Excel, and the R package rcom
>> which also allows to interact with Excel from R (more than just writing Excel workbooks).
>> Our products (rcom and RExcel), however, are not unter a FOSS license.
>>
>> And a more general remark: There are a lot of things where R is a much better choice than Excel,
>> but there are a few things where it really makes sense  to use spreadsheets.
>>
>> Spreadsheets offer a totally different paradigm to work with data, or more generally,
>> numbers and formulas.
>> One can interact with the data directly, not hide them behind variable names.
>> And, the interaction is haptic, gesture based, not expressed as a language.
>>
>> Rearranging the layout of a pivot table by dragging variable “blocks”
>> is very intuitive and something which R itself doe not offer
>> (in fact, I wrote an add-in for R Commander to implement it).
>>
>> Of course, Excel is not a good chice for a polished reproducible workflow.
>> But I think quite a few people (including me), when starting a new project,
>> are not ready immediately to set up this “perfect” workflow,
>> and it is much easier to experiment with the data with a spreadsheet based
>> interface.
>>
>> For me, working with spreadsheets is more like improvising some Jazz,
>> and writing R code is like writing a score for a composition.
>>
>>
>>
>>
>>
>>> On 29 Dec 2016, at 00:15, Rolf Turner <[hidden email]> wrote:
>>>
>>> On 29/12/16 10:45, Bryan Mac wrote:
>>>> Hi,
>>>>
>>>> How do I export results from R to Excel in a format-friendly way? For
>>>> example, when I copy and paste my results into excel, the formatting
>>>> is messed up.
>>>
>>>
>>> Short answer:  *Don't*.  ("Friends don't let friends use excel for statistics.")
>>>
>>> Longer answer:  Googling on "export R data to excel" yields lots of "useful" hits --- "useful" given the (false) assertion that it is useful to export things to excel.
>>>
>>> cheers,
>>>
>>> Rolf Turner
>>>
>>> --
>>> Technical Editor ANZJS
>>> Department of Statistics
>>> University of Auckland
>>> Phone: +64-9-373-7599 ext. 88276
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: [FORGED] Export R output in Excel

John McKown
On Thu, Dec 29, 2016 at 4:32 PM, Erich Subscriptions <
[hidden email]> wrote:

> Just a very brief footnote.
> I is easy to write badly structured spreadsheets.
> But if people dong this would not have spreadsheets
> and be forded to write code, they probably also
> would write badly structured code.
>

​Very true. ​There is a very old quote: "You can write FORTRAN in any
language." Basically FORTRAN was, I think, the very first "high level"
language (1957 - COBOL was in 1959) and it had very poor (NO) structuring.
Coding it in is much like writing a bad mathematical proof (yes, I know
FORTRAN). When better languages came along, the original programmers wrote
code in the "if it were only FORTRAN" mode.

ref: https://en.wikiquote.org/wiki/Fortran




>
> There is a lot of bad R code around also!
>
>

--
Heisenberg may have been here.

http://xkcd.com/1770/

Maranatha! <><
John McKown

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.