**R Programming – DataScience+**, and kindly contributed to R-bloggers)

Categories

Tags

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

Related Post

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

**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...