Export Pivot Table to R Using RExcel

1 Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixExport Pivot Table to R Using RExcelKeith Halbert1 Richard Heiberger1 Erich Neuwirth21 Department of Statistics, Fox School of BusinessTemple University2 Didactic Center for Computer Science and Institute for Scientific ComputingUniversity of ViennauseR! 2010 Gaithersburg, MDExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixContents1 About RExcel2 About Pivot Tables3 Why Transfer to R?4 How it Works5 Example6 Conclusion7 AppendixExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

2 How it WorksExampleConclusionAppendixRExcelMicr osoft Excel, available on almost everyone s machine,provides a familiar interface with which users are is a free add-in to Excel that places the full power ofRat the Excel user s menu access to manyRfunctions directly fromExcel by placing the Rcmdr menu on the Excel menu barcapable of data transfer to and fromRallows the placement ofRgraphs into the Excel automaticrecalculation modelallowsanyRfunction to be used in Excel formulasExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixRExcelOnce installed, the user connects Excel toRthrough the Add-insmenu.

3 The help documentation can be accessed from here as PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixPivot TablesPivot tables are datasummarization tools common tospreadsheet software (such asMicrosoft Excel). They provide ameans for quick and intuitivetabulation of simple data set and pivottable on the right demonstratesthis capability. Here, the averageofHeightis shown withSexas arow PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixPivot TablesThe variable selection ismade through use of theField List shown on ,Sexhas been placedon the rows and we haveselected the average ofHeightto be oursummary allows for fields tobe dragged, dropped, andfiltered in this PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

4 How it WorksExampleConclusionAppendixWhen Pivot Tables Are Usedto summarize large transactional data setsto find relationships and groupings within datato quickly filter data setsto organize data in a format that is easy to chartPivot tables are the single most powerful feature in allof Excel. Bill Jelen and Michael Alexander, Pivot Table data CrunchingExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixWhy Transfer a Pivot Table toR?Rgraphics are fantasticRhas many powerful tools for manipulating and analyzingmultidimensional tablesre-creation inRof an existing Excel Pivot Table couldintroduce error; automatic transfer is saferExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

5 How it WorksExampleConclusionAppendixHow it WorksPutPivottable has been included in RExcel since February Table created inRis of classstructable, a flatcontingency Table provided in the excellentvcdpackage(Visualizing Categorical data ).Options for transfer:right-click on a Pivot tableuse VBA (not discussed in these slides) Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThe Right-Clicking OptionWith RExcel activated, the user right-clicks anywhere in a pivottable and selectsPut PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThe Right-Clicking OptionThe previous action brings up thePut Table in Rdialog box,where the name is assigned.

6 After execution the Table may bemanipulated as desired Using RExcel or PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixA Classic ExampleThetitanicdata consist of the following variables regardingthe passengers of the ill-fated maiden voyage of the Titanic:Class: levelsfirst,second,thirdandcrewAge: levelschildandadultSex: levelsMandFSurvive: levelsdieandliveThese are recoded from the original. See the Reference sectionfor more information. Two of the 2201 records Age Sex Survivethird child F diecrew adult M liveExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

7 How it WorksExampleConclusionAppendixTitanic ExampleWe begin with a simple statistical question: Does the class of thepassenger help to explain survival?We placeClasson the rows andSurviveon the PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixTitanic ExampleTo perform a chi-square test of independence, Excel requires us tomanually create a Table of expected cell counts. These are thenumbers we would expect ifClassandSurvivewere ()calculates a p-value (which indicates dependence). Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixTitanic ExampleThe chi-square test is an approximation.

8 For some count data wemay require Fisher s exact test, which Excel does not provide. Toperform either of these tests inR, first we transfer the Table and giveit a sensible PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixTitanic ExampleThe RExcel functionREval()evaluatesRcode and brings the resultto Excel. The chi-square approximation is fine for these data . Thep-values are similarly () Rcalculates the Table of expected values directlyfrom the Pivot Table . The manual calculation is not in this simple example, Excel cannot compete withRwhen it comes to statistical analysis. We now turn ourattention to PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

9 How it WorksExampleConclusionAppendixTitanic ExampleBelow is a standard Excel plot of our Pivot is a simple Table and Excel s plot is OK. Next we examine amosaic plot from packagevcd. These are great for comparing countdata, but are not available in PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixMosaic PlotExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixTitanic ExampleWe add two additional fields: a second row variable (Sex) anda second column variable (Age). Export PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?

10 How it WorksExampleConclusionAppendixTitanic ExampleBelow is the best we can do in Excel. The mosaic plot on thenext slide is superior. Excel Pivot Table users should haveaccess to these PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixThe Mosaic Plot is SuperiorExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?How it WorksExampleConclusionAppendixConclusion RExcel allows for instantaneous transfer of Pivot tablesfrom Excel toRonce the Table is transferred the user has the full power ofRto do statistical analysisRpackages such asvcdprovide useful graphics that arenot available in ExcelExport PivotTable to RUsing RExcelHalbert,Heiberger,NeuwirthAbout RExcelAbout PivotTablesWhy Transferto R?