2.9 External Datasets

We often want to import from CSV, stata data and excel files. Sometimes, we also want to export to CSV and excel files. Finally, we cover how to convert your R code into Word document through knit and export your table using the package stargazer.

2.9.1 Working Directory

To work with external file, we need to set the folder containing the file as working directory. Otherwise, we need to write the full path of the file.

To set working directory, we use setwd().

setwd("C:/user/folder/")

2.9.2 Import from CSV

To import csv, we can use read.csv().

The program will find a file in the working directory. If the file is stored in a sub directory, we will say ``./folder/file.csv’’ instead.

If the data has header, then set header = TRUE so that the column name is given by the header row. Otherwise, set header to FALSE.

# If the file has header, then type
df <- read.csv("file.csv", header= TRUE)
# If the file has no header, then type
df <- read.csv("file.csv", header= FALSE)

2.9.3 Import from Stata

To import from Stata data file, we need the package foreign.

library(foreign)
df <-read.dta("datafile.dta")

2.9.4 Import from Excel

To import from xlsx file, we need the package xlsx

library(xlsx)
workbook <- "path/file.xlsx"
df <-read.xlsx(workbook, n)
# n is n-th worksheet to import

2.9.5 Export to CSV

To export dataframe to a csve file, we can use write.csv(). The file will be saved in your current working directory, which can be changed if you wish.

write.csv(df,file="filename.csv")

Note that a csv file that can be directly read by Excel.

2.9.6 Export to xlsx

To export to xlss file, we need to install package XLConnect.

install.packages("XLConnect")
library(XLConnect)
# Load and Excel workbook (create if not exist)
wb = loadWorkbook("test.xlsx",create=T)
# Create Worksheet in the workbook
createSheet(wb,"AAPL"); createSheet(wb,"IBM")
AAPL<-data.frame(P=c(1,2,4),Q=c(2,3,5))
IBM <-data.frame(P=c(2,4,6),Q=c(7,8,9))  
# Write things into worksheet
writeWorksheet(wb, AAPL,sheet = "AAPL", 
               startRow = 1, startCol = 1)
writeWorksheet(wb, IBM,sheet = "IBM", 
               startRow = 1, startCol = 1)
# Save to workbook
saveWorkbook(wb)

2.9.7 Export to Word: Knitr

Sometimes, we want to export our working in RStudio as printable or pdf/word format. The easiest way to do it is through the knitr package. Install the package and then go to File > Knit document > MS Word in RStudio.

2.9.8 Export Table: stargazer

The package ``stargazer’’ is useful to export table to latex, text or html (open in word and copy and paste). This saves considerable time to get the formatting right.

install.packages("stargazer");
library(stargazer);
stargazer(df)  #latex
stargazer(df,  type="text", out="test.txt") 
stargazer(df,  type="html", out="test.html")