Excel Data

{readxl} and {gdata} are only able to import Excel data, not to edit it
#Importing Excel Data
excel_sheets("data.xlsx") #get the list of sheets
data1 <- read_excel("data.xlsx", sheet = 1)
data2 <- read_excel("data.xlsx", sheet = 2)
data<- list(data1, data2)
#automatic it with lapply()
my_workbook <- lapply(excel_sheets("data.xlsx"), read_excel, path = "data.xlsx", skip=2)
read_excel("data.xlsx", sheet = 1, col_names=TRUE, col_types=FALSE)
read_excel("data.xlsx", sheet = 1, col_names=c(name1, name2), col_types=FALSE)
read_excel("data.xlsx", sheet = 1, col_names=TRUE, col_types=c("text","text")) #numeric/data/blank
library(XLConnect)
Just as readxl and gdata, you can use XLConnect to import data from Excel file into R.
# Build connection to urbanpop.xlsx
my_book <- loadWorkbook("urbanpop.xlsx")
# List the sheets in my_book
getSheets(my_book)
# Import the second sheet in my_book
readWorksheet(my_book, sheet=2)
my_book <- loadWorkbook(“urbanpop.xlsx”) sheets <- getSheets(my_book) all <- lapply(sheets, readWorksheet, object = my_book) str(all)
# Import columns 3, 4, and 5 from second sheet in my_book: urbanpop_sel urbanpop_sel <- readWorksheet(my_book, sheet = 2)[3:5] # Import first column from second sheet in my_book: countries countries<- readWorksheet(my_book, sheet=2)[1] # cbind() urbanpop_sel and countries together: selection selection<- cbind( countries,urbanpop_sel)
#{gdata} - by Gregory Warnes
Suite of tools for data manupulations, elegant extention of utils, not for big data files
library(gdata)
# Import the second sheet of data.xls
data<-read.xls("data.xls", sheet=2)
# Column names for urban_pop
columns <- c("country", paste0("year_", 1967:1974))
# Finish the read.xls call
urban_pop <- read.xls("urbanpop.xls", sheet = 2, skip = 50, header = FALSE, stringsAsFactors = FALSE, col.names = columns)


data1 <- read.xls(path, sheet = 1, stringsAsFactors = FALSE)
data2 <- read.xls(path, sheet = 2, stringsAsFactors = FALSE)
data <- cbind(data1, data2[-1])
# Remove all rows with NAs from urban: urban_clean
data_clean<- na.omit(data)

XLConnect’s approach of providing an actual interface to an Excel file makes it able to edit your Excel files from inside R. In this exercise, you’ll create a new sheet. In the next exercise, you’ll populate the sheet with data, and save the results in a new Excel file.

# Build connection to urbanpop.xlsx
my_book <- loadWorkbook("urbanpop.xlsx")
# Add a worksheet to my_book, named "data_summary"
createSheet(my_book, name="data_summary")
# Use getSheets() on my_book
getSheets(my_book)
# Build connection to urbanpop.xlsx
my_book <- loadWorkbook("urbanpop.xlsx")
# Add a worksheet to my_book, named "data_summary"
createSheet(my_book, "data_summary")
# Create data frame: summ
sheets <- getSheets(my_book)[1:3]
dims <- sapply(sheets, function(x) dim(readWorksheet(my_book, sheet = x)), USE.NAMES = FALSE)
summ <- data.frame(sheets = sheets,
nrows = dims[1, ],
ncols = dims[2, ])
# Add data in summ to "data_summary" sheet
writeWorksheet(my_book, summ, "data_summary")
# Save workbook as summary.xlsx saveWorkbook(my_book, "summary.xlsx")
# Rename "data_summary" sheet to "summary" renameSheet(my_book, "data_summary","summary") # Print out sheets of my_book getSheets(my_book) # Save workbook to "renamed.xlsx" saveWorkbook(my_book,"renamed.xlsx")
# Build connection to renamed.xlsx: my_book my_book<- loadWorkbook("renamed.xlsx") # Remove the fourth sheet removeSheet(my_book, sheet="summary") # Save workbook to "clean.xlsx" saveWorkbook(my_book, "clean.xlsx")