Transcription
Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthExport Pivot Table to R Using RExcelAbout RExcelAbout PivotTablesKeith Halbert1Why Transferto R?1 DepartmentHow it WorksExampleConclusionRichard Heiberger12 DidacticErich Neuwirth2of Statistics, Fox School of BusinessTemple UniversityCenter for Computer Science and Institute for Scientific ComputingUniversity of ViennaAppendixuseR! 2010Gaithersburg, MD
ContentsExport PivotTable to RUsing RExcelHalbert,Heiberger,Neuwirth1 About RExcel2 About Pivot TablesAbout RExcelAbout PivotTablesWhy Transferto R?3 Why Transfer to R?4 How it WorksHow it WorksExample5 ExampleConclusionAppendix6 Conclusion7 Appendix
RExcelExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelMicrosoft Excel, available on almost everyone’s machine,provides a familiar interface with which users are comfortable.RExcel is a free add-in to Excel that places the full power of Rat the Excel user’s fingertips.About PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixprovides menu access to many R functions directly fromExcel by placing the Rcmdr menu on the Excel menu barcapable of data transfer to and from Rallows the placement of R graphs into the Excel automaticrecalculation modelallows any R function to be used in Excel formulas
RExcelExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixOnce installed, the user connects Excel to R through the Add-insmenu. The help documentation can be accessed from here as well.
Pivot TablesExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixPivot tables are datasummarization tools common tospreadsheet software (such asMicrosoft Excel). They provide ameans for quick and intuitivetabulation of data.The simple data set and pivottable on the right demonstratesthis capability. Here, the averageof Height is shown with Sex as arow variable.
Pivot TablesExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthThe variable selection ismade through use of theField List shown on theright.About RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionHere, Sex has been placedon the rows and we haveselected the average ofHeight to be oursummary function.AppendixExcel allows for fields tobe dragged, dropped, andfiltered in this list.
When Pivot Tables Are UsedExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?to summarize large transactional data setsto find relationships and groupings within datato quickly filter data setsto organize data in a format that is easy to chartHow it WorksExampleConclusionPivot tables are the single most powerful feature in allof Excel.Appendix Bill Jelen and Michael Alexander, Pivot Table Data Crunching
Why Transfer a Pivot Table to R?Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixR graphics are fantasticR has many powerful tools for manipulating and analyzingmultidimensional tablesre-creation in R of an existing Excel pivot table couldintroduce error; automatic transfer is safer
How it WorksExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixPutPivottable has been included in RExcel since February 2010.The table created in R is of class structable, a flatcontingency table provided in the excellent vcd package(Visualizing Categorical Data).Options for transfer:right-click on a pivot tableuse VBA macro RInterface.PutPivottable(not discussed in these slides)
The Right-Clicking OptionExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixWith RExcel activated, the user right-clicks anywhere in a pivottable and selects Put Pivottable.
The Right-Clicking OptionExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThe previous action brings up the Put table in R dialog box,where the name is assigned. After execution the table may bemanipulated as desired using RExcel or the R console.
A Classic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?The titanic data consist of the following variables regardingthe passengers of the ill-fated maiden voyage of the Titanic:Class: levels first, second, third and crewAge: levels child and adultSex: levels M and FSurvive: levels die and liveHow it WorksExampleConclusionAppendixThese are recoded from the original. See the Reference sectionfor more information. Two of the 2201 records ive
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixWe begin with a simple statistical question: Does the class of thepassenger help to explain survival?We place Class on the rows and Survive on the Columns.
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthTo perform a chi-square test of independence, Excel requires us tomanually create a table of expected cell counts. These are thenumbers we would expect if Class and Survive were independent.About RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixCHITEST() calculates a p-value (which indicates dependence).
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThe chi-square test is an approximation. For some count data wemay require Fisher’s exact test, which Excel does not provide. Toperform either of these tests in R, first we transfer the table and giveit a sensible name.
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesThe RExcel function REval() evaluates R code and brings the resultto Excel. The chi-square approximation is fine for these data. Thep-values are similarly tiny.In chisq.test() R calculates the table of expected values directlyfrom the pivot table. The manual calculation is not necessary.Why Transferto R?How it WorksExampleConclusionAppendixEven in this simple example, Excel cannot compete with Rwhen it comes to statistical analysis. We now turn ourattention to graphics.
Titanic ExampleExport PivotTable to RUsing RExcelBelow is a standard Excel plot of our pivot table.Halbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThis is a simple table and Excel’s plot is OK. Next we examine amosaic plot from package vcd. These are great for comparing countdata, but are not available in Excel.
Mosaic PlotExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendix
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixWe add two additional fields: a second row variable (Sex) anda second column variable (Age).
Titanic ExampleExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixBelow is the best we can do in Excel. The mosaic plot on thenext slide is superior. Excel pivot table users should haveaccess to these plots.
The Mosaic Plot is SuperiorExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendix
ConclusionExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixRExcel allows for instantaneous transfer of pivot tablesfrom Excel to Ronce the table is transferred the user has the full power ofR to do statistical analysisR packages such as vcd provide useful graphics that arenot available in Excel
References and LinksExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?Baier, T. and Neuwirth, E. (2007), Excel :: COM :: R.Computational Statistics 22/1 91-108Dawson, Robert J. MacG. (1995) The ”Unusual Episode” Data Revisited.Journal of Statistics Education v.3, n.3Heiberger, R. and Neuwirth, E. (2009), R Through Excel. Springer VerlagHornik, K. The Strucplot Framework: Visualizing Multi-way Contingency Tableswith vcd. Journal of Statistical Software, American Statistical Associationvol. 17(i03).Jelen B. and Alexander, M. (2005), Pivot Table Data Crunching. Que PublishingReport on the Loss of the ’Titanic’ (S.S.) (1990),British Board of Trade Inquiry Report (reprint). Allan Sutton PublishingHow it WorksExampleConclusionAppendixRExcelInstaller at nstaller/index.htmlRExcel Homepagehttp://rcom.univie.ac.at/Titanic /datasets.dawson.html
R Code for Creating Mosaic PlotsExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixmosaic( e Class Survive,direction c("v", "h"),data test,highlighting "Survive",highlighting fill c("salmon", "skyblue"))mosaic( e Class Survive Sex Age,direction c("v", "h"),data test,highlighting "Survive",highlighting fill c("salmon", "skyblue"))
Below is a standard Excel plot of our pivot table. This is a simple table and Excel's plot is OK. Next we examine a mosaic plot from package vcd. These are great for comparing count data, but are not available in Excel. Export Pivot Table to R Using RExcel Halbert, Heiberger, Neuwirth About RExcel About Pivot Tables Why Transfer
