Use R with Excel: Importing and Exporting Data

October 16, 2018
By

(This article was first published on R Programming – DataScience+, and kindly contributed to R-bloggers)

    Categories

    1. Data Management

    Tags

    1. Databases
    2. Import Data
    3. R Programming
    4. Tips & Tricks

    In this article, you learn how to connect R with Excel by importing and exporting data between the two programs.

    Excel: pros and cons

    Excel is still very popular among companies and organizations. One of the main advantages in a spreadsheet is ease of providing the user with a rapid overview of a dataset, using visualizations and tables. On the other hand, given the incredible growth of data, the spreadsheet really lacks efficient and agile solutions for data management and data cleaning.

    Solution: Use R with Excel

    Fortunately, it is possible for the data scientist/analyst to empower the advantages of both Excel and R. This can be done with some value adding and efficient packages for R. What Excel lack in data management and data cleaning – R is an excellent and efficient solution for these tasks. Furthermore, it is also possible to create the analytics in R and export the result into Excel for reporting. Basically, you have two great solutions (1): You can import Excel datasets into R for data management, data cleaning and analytics (2): You can export analytical results and clean data from R into Excel for further analytics or presentation.

    The below sections presents these solutions with coding examples in R. First we look at how you import Excel into R:

    Import Excel spread sheet in R

    # Read R library
    library(readxl)
    # Import xlsx
    MyExcelSheet <- read.xlsx(file="/ExcelSheet.xlsx",sep = ",", header=TRUE)  # read sheet
    

    Now let us use the above import coding with an Excel sheet. We use this demand data:

    Import Excel spread sheet in R with a dataset

    # Read R library
    library(readxl)
    # Import xlsx dataset
    demand <- read_excel("~/R work/DataScience+/Make R speak Excel/demand.xls")
    head(demand)

    The above coding gives us the following table

     head(demand)
    # A tibble: 6 x 7
         p1    p2    p3     y    q1    q2    q3        
    1 129.   76.6  59.6  92.8 128.  140.  129. 
    2 156.  186.  124.   69.4 133.   23.2  37.7
    3 111.  100.   36.6 103.   98.3  59.4 295. 
    4  55.1 240.   34.8 145.  374    55.7 386. 
    5 156   196.  134.   80.2  50.3 116.   44.8
    6  92.2 150.  129.  110   194   126.   66.6
    

    Export Excel spread sheet in R

    Now it is time to Export Excel sheet from R into Excel:

    Export spread sheet from R to Excel

    # Read R library
    library(readxl)
    library(xlsx)
    # Export xlsx sheet from R to Excel
    write.xlsx(MyExcelSheet, "/ExcelSheet.xlsx") # write sheet
    

    Let us use the above export coding with the demand excel sheet. First we do some data management in R:

    Export spread sheet from R to Excel with a dataset

    # Read R library
    library(readxl)
    library(xlsx)
    # Import xlsx dataset
    demand <- read_excel("~/R work/DataScience+/Make R speak Excel/demand.xls")
    # Data management in R
    demand$psum <- demand$p1 + demand$p2 + demand$p3
    head(demand)
    # Export sheet to Excel
    write.xlsx(MyExcelSheet, "/ExcelSheet.xlsx") # write sheet
    

    The above coding gives us the following table

    head(demand)
    # A tibble: 6 x 8
         p1    p2    p3     y    q1    q2    q3  psum         
    1 129.   76.6  59.6  92.8 128.  140.  129.   265.
    2 156.  186.  124.   69.4 133.   23.2  37.7  465.
    3 111.  100.   36.6 103.   98.3  59.4 295.   247.
    4  55.1 240.   34.8 145.  374    55.7 386.   330.
    5 156   196.  134.   80.2  50.3 116.   44.8  486.
    6  92.2 150.  129.  110   194   126.   66.6  371.
    

    Happy coding!

    References

    1. Using readxl in R – CRAN.R-project.org
    2. Using xlsx in R – CRAN.R-project.org

    Related Post

    1. Combining data in R: the skill of merging, joining, and stacking
    2. Efficient data management and SQL data selection in R
    3. Essential data cleaning for ad-hoc tasks in R
    4. Proteomics Data Analysis (2/3): Data Filtering and Missing Value Imputation
    5. Clean Your Data in Seconds with This R Function

    To leave a comment for the author, please follow the link and comment on their blog: R Programming – DataScience+.

    R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



    If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

    Comments are closed.

    Search R-bloggers

    Sponsors

    Never miss an update!
    Subscribe to R-bloggers to receive
    e-mails with the latest R posts.
    (You will not see this message again.)

    Click here to close (This popup will not appear again)