(R Tutorials for Business Analyst)
In this end-to-end example, you will know how we can export a dataset to excel, csv, SAS, STATA and TEXT file in R. It is important to know this exporting process because we can upload or import these formatted file to their respective software to do further processing as well as dashboard reporting.
R Exporting Data to Excel, CSV, SAS, STATA, Text File
How to Export Data from R
In this tutorial, we will learn how to export data from R environment to different formats.
To export data to the hard drive, you need the file path and an extension. First of all, the path is the location where the data will be stored. In this tutorial, you will see how to store data on:
- The hard drive
- Google Drive
- Dropbox
Secondly, R allows the users to export the data into different types of files. We cover the essential file’s extension:
- csv
- xlsx
- RDS
- SAS
- SPSS
- STATA
Overall, it is not difficult to export data from R.
In this tutorial, you will learn-
- Export to Hard drive
- Export CSV
- Export Excel file
- Export to different software
- Export SAS file
- Export STATA file
Export to Hard drive
To begin with, you can save the data directly into the working directory. The following code prints the path of your working directory:
directory <-getwd() directory
Output:
## [1] "/Users/15_Export_to_do"
By default, the file will be saved in the below path.
For Mac OS:
/Users/USERNAME/Downloads/
For Windows:
C:UsersUSERNAMEDocuments
You can, of course, set a different path. For instance, you can change the path to the download folder.
Create data frame
First of all, let’s import the mtcars dataset and get the mean of mpg and disp grouped by gear.
library(dplyr) df <-mtcars % > % select(mpg, disp, gear) % > % group_by(gear) % > % summarize(mean_mpg = mean(mpg), mean_disp = mean(disp)) df
Output::
## # A tibble: 3 x 3 ## gear mean_mpg mean_disp ## <dbl> <dbl> lt;dbl> ## 1 3 16.10667 326.3000 ## 2 4 24.53333 123.0167 ## 3 5 21.38000 202.4800
The table contains three rows and three columns. You can create a CSV file with the function write.csv().
Export CSV
The basic syntax is:
write.csv(df, path) arguments -df: Dataset to save. Need to be the same name of the data frame in the environment. -path: A string. Set the destination path. Path + filename + extension i.e. "/Users/USERNAME/Downloads/mydata.csv" or the filename + extension if the folder is the same as the working directory
Example:
write.csv(df, "table_car.csv")
Code Explanation
- write.csv(df, “table_car.csv”): Create a CSV file in the hard drive:
- df: name of the data frame in the environment
- “table_car.csv”: Name the file table_car and store it as csv
Note: You can use the function write.csv2() to separate the rows with a semicolon.
write.csv2(df, "table_car.csv")
Note: For pedagogical purpose only, we created a function called open_folder() to open the directory folder for you. You just need to run the code below and see where the csv file is stored. You should see a file names table_car.csv.
# Run this code to create the function open_folder <-function(dir){ if (.Platform['OS.type'] == "windows"){ shell.exec(dir) } else { system(paste(Sys.getenv("R_BROWSER"), dir)) } } # Call the function to open the folder open_folder(directory)
Export to Excel file
Export data to Excel is trivial for Windows users and trickier for Mac OS user. Both users will use the library xlsx to create an Excel file. The slight difference comes from the installation of the library. Indeed, the library xlsx uses Java to create the file. Java needs to be installed if not present in your machine.
Windows users
If you are a Windows user, you can install the library directly with conda:
conda install -c r r-xlsx
Once the library installed, you can use the function write.xlsx(). A new Excel workbook is created in the working directory
library(xlsx) write.xlsx(df, "table_car.xlsx")
If you are a Mac OS user, you need to follow these steps:
- Step 1: Install the latest version of Java
- Step 2: Install library rJava
- Step 3: Install library xlsx
Step 1) You could download Java from official Oracle site and install it.
You can go back to Rstudio and check which version of Java is installed.
system("java -version")
At the time of the tutorial, the latest version of Java is 9.0.4.
Step 2) You need to install rjava in R. We recommended you to install R and Rstudio with Anaconda. Anaconda manages the dependencies between libraries. In this sense, Anaconda will handle the intricacies of rJava installation.
First of all, you need to update conda and then install the library. You can copy and paste the next two lines of code in the terminal.
conda - conda update conda install -c r r-rjava
Next, open rjava in Rstudio
library(rJava)
Step 3) Finally, it is time to install xlsx. Once again, you can use conda to do it:
conda install -c r r-xlsx
Just as the windows users, you can save data with the function write.xlsx()
library(xlsx)
Output:
## Loading required package: xlsxjars
write.xlsx(df, "table_car.xlsx")
Export to different software
Exporting data to different software is as simple as importing them. The library “haven” provides a convenient way to export data to
- spss
- sas
- stata
First of all, import the library. If you don’t have “haven”, you can go here to install it.
library(haven)
SPSS file
Below is the code to export the data to SPSS software:
write_sav(df, "table_car.sav")
Export SAS file
Just as simple as spss, you can export to sas
write_sas(df, "table_car.sas7bdat")
Export STATA file
Finally, haven library allows writing .dta file.
write_dta(df, "table_car.dta")
R
If you want to save a data frame or any other R object, you can use the save() function.
save(df, file ='table_car.RData')
You can check the files created above in the present working directory
Summary
We can summarize all the functions in the table below
Library | Objective | Function |
---|---|---|
base | Export csv | write.csv() |
xlsx | Export excel | write.xlsx() |
haven | Export spss | write_sav() |
haven | Export sas | write_sas() |
haven | Export stata | write_dta() |
base | Export R | save() |
googledrive | Upload Google Drive | drive_upload() |
googledrive | Open in Google Drive | drive_browse() |
googledrive | Retrieve file ID | drive_get(as_id()) |
googledrive | Dowload from Google Drive | download_google() |
googledrive | Remove file from Google Drive | drive_rm() |
rdrop2 | Authentification | drop_auth() |
rdrop2 | Create a folder | drop_create() |
rdrop2 | Upload to Dropbox | drop_upload() |
rdrop2 | Read csv from Dropbox | drop_read_csv |
rdrop2 | Delete file from Dropbox | drop_delete() |
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
How to load data from csv file using Pandas | Jupyter Notebook | Python Data Science for beginners
Load data from a CSV file in the local directory | Jupyter Notebook | R Data Science for beginners
Data Wrangling in Python – How to Save A pandas Dataframe As A CSV