R tutorials for Business Analyst – R Exporting Data to Excel, CSV, SAS, STATA, Text File

Hits: 167

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()

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

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

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

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