#### 3.2 文本文件的读写 ###
### 3.2.1 base包
import.txt <- read.table("data/iris.txt",header = TRUE) # 读入iris.txt文件
head(import.txt)
import.csv <- read.table("data/iris.csv",header = TRUE,sep = ",") #读入iris.csv文件
head(import.csv)
import.csv1 <- read.csv("data/iris.csv") # 利用read.csv将iris.csv文件读入
head(import.csv1)
# 修改行名称
w0 <- read.table("data/iris.txt",header = T,row.names = paste0("row",1:150))
head(w0)
file.exists("data/women.txt") # 查找当前文件夹是否存在women.txt文件,返回FALSE
write.table(women,"data/women.txt",row.names = F,col.names = F) # 将women数据集导到本地
file.exists("data/women.txt") # 查找当前文件夹是否存在women.txt文件,返回TRUE
women1 <- read.table("data/women.txt") # 将women.txt文件读入到R中
women1
#读入数据时并设置列名
women2 <- read.table("data/women.txt",header = F,col.names = c("height","weihgt"))
women2
# skip参数和nrows参数的使用
read.table("data/women.txt",skip = 10,col.names = c("height","weight"))
read.table("data/women.txt",nrows = 6,col.names = c("height","weight"))
read.table("data/women.txt",skip = 10,nrows = 3,col.names = c("height","weight"))
# 读取非结构化文本文件
unstructuredText <- readLines("data/unstructuredText.txt")
unstructuredText
### readr包
library(readr)
# 读入csv文件
iris1 <- read_csv("data/iris.csv")
iris1
# 读入txt文件
read_delim("data/iris.txt",delim = " ") # 将分隔符设置为空格
read_delim("data/women.txt",delim = " ",col_names = FALSE)
read_delim("data/women.txt",delim = " ",col_names = c("height","weight"))
read_delim("data/women.txt",delim = " ",col_names = c("height","weight"),
col_types = list(col_character(),col_double()))
# 查看文件大小
paste(round(file.size("data/ccFraud.csv")/(1024*1024),0),"M")
# 计算利用read.csv导数据耗时
system.time(read.csv("data/ccFraud.csv"))
# 计算利用read_csv导数据耗时
system.time(read_csv("data/ccFraud.csv"))
### data.table包
library(data.table)
system.time(fread("data/ccFraud.csv"))
# 导入全部数据
ccFraud <- fread("data/ccFraud.csv")
str(ccFraud)
# 只导入custID和fraudRisk变量
y <- fread("data/ccFraud.csv",select = c("custID","fraudRisk"))
str(y)
# 剔除fraudRisk变量
x <- fread("../data/ccFraud.csv",drop = "fraudRisk")
str(x)
# 对比导出数据集的耗时
system.time(write.csv(ccFraud,"data/ccFraud1.csv",row.names = F))
system.time(fwrite(ccFraud,"data/ccFraud2.csv",row.names = F))
#### 3.3 Excel文件读写 ###
# 利用RODBC包读入
library(RODBC)
channel <- odbcConnectExcel2007("../data/sample.xlsx") # 建立连接
odbcdf <- sqlFetch(channel,'data') # 读取工作表data的数据
odbcClose(channel) # 关闭连接
odbcdf
# 利用xlsx包读取EXcel数据
library(xlsx)
res <- read.xlsx('../data/sample.xlsx',1,encoding="UTF-8") # 利用read.xlsx函数读取Excel文件
res
detach(package:xlsx)
# 利用XLConnect包读取Excel数据
library(XLConnect)
wb <- loadWorkbook("data/sample.xlsx") # 加工作薄加载到R中
xldf<-readWorksheet(wb,sheet=getSheets(wb)[1]) #读取第一个工作表的数据
xldf
# 利用readxl包读取Excel数据
library(readxl)
readexcel <- read_excel("data/sample.xlsx",1,col_names = T)
readexcel
# 利用openxlsx包读取Excel数据
library(openxlsx)
opxl <- read.xlsx("data/sample.xlsx")
opxl
# 3.3.1 xlsx包
# wrtie.xlsx函数
library(xlsx)
# 查看data目录下是否包含test_output.xlsx文件
file.exists("data/test_output.xlsx")
# 导出iris到本地
write.xlsx(iris,"data/test_output.xlsx",
sheetName = "iris",row.names = F)
# 查看文件是否存在
file.exists("../data/test_output.xlsx")
# 对现有工作簿追加文件
write.xlsx(mtcars,"data/test_output.xlsx",
sheetName = "mtcars",append = TRUE)
file <- "data/test_output.xlsx"
wb <- loadWorkbook(file) # 将文件加载到R中
sheets <- getSheets(wb) # 读取wb中的工作表
sheets # 返回每个指向工作表的java对象引用列表
names(sheets) # 查看列表名字
removeSheet(wb,sheetName = names(sheets)[2]) # 移除mtcars工作表
getSheets(wb) # 查看wb中的工作表
createSheet(wb,sheetName = "women") # 在wb中创建women工作表
getSheets(wb) # 查看wb中的工作表
(sheet <- getSheets(wb)[[2]]) # 取第二个sheet
rows <- getRows(sheet) # 获得所有行
cells <- getCells(rows) # 返回所有非空的单元格
values <- lapply(cells, getCellValue) # 提取非空单元格的值
values # 查看结果
addDataFrame(women,getSheets(wb)[[2]],row.names = FALSE) # 将women数据框添加到wb中的women工作表中
cells <- getCells(getRows(getSheets(wb)[[2]]))
lapply(cells[1:2],getCellValue) # 查看前面两个非空单元格的值
# 将所作操作保存到原文件中
file
saveWorkbook(wb,file)
# 读入Excel文件
# 方式一:通过指定sheetIndex参数实现
iris1 <- read.xlsx(file,sheetIndex = 1)
head(iris1)
# 方式二:通过指定sheetName参数实现
iris2 <- read.xlsx(file,sheetName = "iris")
head(iris2)
# 读取部分数据
read.xlsx(file,sheetIndex = 1,rowIndex = 1:6,colIndex = 1:4)
# 3.3.2 XLconnect包
# https://github.com/miraisolutions/xlconnect
file <- "data/test_output.xlsx"
# 读取或创建一个XLSX文件,此步相当于建立一个连接
library(XLConnect)
wb <- loadWorkbook(file) # create参数默认为FALSE,如果为TRUE,则创建一个XLSX文件
sheets <- getSheets(wb) # 读取wb中的工作表
sheets # 查看现有工作表的名称
createSheet(wb,name = "mtcars") # 创建新工作表
# 将mtcars数据框写入mtcars工作表中,默认从第一个单元格A1开始写入
writeWorksheet(wb,data = mtcars,sheet = "mtcars",header = TRUE)
# 存入硬盘,直到此步方才有文档生成
saveWorkbook(wb) # 存入硬盘,直到此步方才有文档生成
mtcars1 <- readWorksheet(wb,"mtcars") # 读取mtcars工作表的数据
head(mtcars1) # 查看前六行数据
# 直接将cars数据框写入到Excel文件中
writeWorksheetToFile(file,cars,"cars")
# 查看前7行
readWorksheet(wb,"cars",endRow = 7)
# 重新加载文件
wb <- loadWorkbook(file)
readWorksheet(wb,"cars",endRow = 7)
# 一次性将文件中所有工作表的前7行数据读入
(all <- readWorksheet(wb,sheet = getSheets(wb),endRow = 7))
# 提取women数据
all$women
# 通过readWorksheetFromFile直接从文件中读取数据
cars <- readWorksheetFromFile(file,sheet = "cars")
head(cars)
# 3.3.3 openxlsx包
file <- "data/test_output.xlsx"
if(!require(openxlsx)) install.packages("openxlsx") #加载openxlsx包,若不存在则在线安装
wb <- loadWorkbook(file) # 加载Excel文件到R中,保存为wb对象
wb #查看wb对象
# 读取第一个工作表的前7行数据
read.xlsx(file,sheet = 1,rows = 1:7) # 直接从Excel文件中读取
read.xlsx(wb,sheet = 1,rows = 1:7) # 从wb对象中读取
read.xlsx(wb,sheet = 1:2) # 同时读取两个sheet数据会报错
(sheet_name <- sheets(wb)) # 读取wb对象中的工作表名称
is.vector(sheet_name) #判断是否为向量
# 删除某些单元格数据
deleteData(wb,sheet = 1,cols = c(2,3,4,5),rows = c(2,3,4,5),gridExpand = FALSE)
# 通过saveWorkbook保存操作结果
saveWorkbook(wb,file,overwrite = TRUE)
# 查看最新结果
read.xlsx(wb,sheet = 1,rows = 1:7)
# 删除某一区域范围内的数据
deleteData(wb,sheet = 1,cols = c(1,2,3),rows = c(7,8),gridExpand = TRUE)
saveWorkbook(wb,file,overwrite = TRUE) # 保存结果
read.xlsx(wb,sheet = 1,rows = 1:8) # 查看最新结果
removeWorksheet(wb,"iris") # 移除wb对象中的iris工作表
sheets(wb) # 查看此时wb对象中的工作表名称
addWorksheet(wb,"iris") # 在wb对象中新增iris工作表
sheets(wb)
wb # 查看wb对象
#利用writeData写入数据
writeData(wb,sheet = "iris",x = iris)
#利用writeDataTable从第1行第7列作为起始位置开始写入
writeDataTable(wb,sheet = "iris",x = iris,startCol = 7)
saveWorkbook(wb,file,overwrite = TRUE) # 保存结果
# 3.3.4 readxl包
file <- "data/test_output.xlsx"
if(!require(readxl)) install.packages("readxl") # 加载readxl包
(name <- excel_sheets(file)) # 查看文件包含的工作表名称
mydata <- read_excel(file,sheet = "women") # 读取women工作表的数据
head(mydata)
# 导入大数据集,对比各函数的读取数据耗时
rm(list=ls());gc()
# 利用xlsx包的read.xlsx函数读取
system.time(xlsx::read.xlsx("data/ccFraud.xlsx",1))
# 利用XLConnect包的readWorksheet函数读取
rm(list=ls());gc()
system.time(XLConnect::readWorksheetFromFile("data/ccFraud.xlsx",1))
# 利用openxlsx包的read.xlsx函数读取
rm(list=ls());gc()
system.time(openxlsx::read.xlsx("data/ccFraud.xlsx",1))
# 利用readxl包的read_excel函数读取
rm(list=ls());gc()
system.time(readxl::read_excel("data/ccFraud.xlsx",1))
#### 3.4 数据库文件读写 ###
# 3.4.1 RODBC包
library(RODBC)
channel <-odbcConnect("daniel","root","123456")
channel
odbcGetInfo(channel)
sqlSave(channel,mtcars,"mydata",append= FALSE) #将mtcars表写入MySQL中
mydata1 <- sqlFetch(channel,"mydata") # 直接将MySQL中的mydata表读入到R中
head(mydata1)
mydata2 <- sqlQuery(channel,"select * from mydata") # 将sql语句运行后的结果读入到R中
head(mydata2)
result <- sqlQuery(channel,"select vs,am,avg(mpg) from mydata group by vs,am") #对数据进行聚合
result
sqlDrop(channel,"mydata") # 删除MySQL中的mydata表
odbcClose(channel) # 关闭连接
odbcGetInfo(channel)
# 3.4.2 RMySQL包
library(RMySQL)
conn <- dbConnect(MySQL(),dbname = "test",user = "root",password = "123456") # 建立连接
dbGetInfo(conn) #查看详细信息
dbListTables(conn) # 查看当前库的所有表名
dbWriteTable(conn,"mydata",mtcars) # 将mtcars数据写入到MySQL中,保存为表mydata
dbListTables(conn) # 查看当前库的所有表名
dbListFields(conn,"mydata") # 查看mydata的列名
mydata1 <- dbReadTable(conn,"mydata") # 读取MySQL的mydata表
head(mydata1)
mydata2 <- dbGetQuery (conn,"select * from mydata limit 6") # 执行sql语句,返回查询结果
mydata2
dbDisconnect(conn) # 断开连接
标签:读取,写入,####,base,3.2,import,txt,###,语言
From: https://blog.csdn.net/2301_76574743/article/details/140331965