基于R语言的统计分析基础:使用SQL语句操作数据集

server/2024/9/24 15:47:37/

在使用R语言数据分析时,可以融合SQL语言使数据聚集操作更加便利,同时也可以增加对SQL语句的熟悉。借助sqldfDBIRSDLite等包,可以在R环境中直接运用SQL语句,轻松实现数据的分组统计、汇总分析,SQL的强大查询能力简化了数据处理步骤,让复杂分析变得简单。

举个例子,使用DBI包操作R语言自带数据集USArrests

library(DBI)  
# 初始化一个临时的内存数据库并将一个data.frame复制到其中  
con <- dbConnect(RSQLite::SQLite(), ":memory:")  
data(USArrests)  
dbWriteTable(con, "USArrests", USArrests)  
dbListTables(con)  # 将所有查询结果提取到一个data frame中  
dbGetQuery(con, "SELECT * FROM USArrests")  # 或者分批进行  
rs <- dbSendQuery(con, "SELECT * FROM USArrests")  
d1 <- dbFetch(rs, n = 10)  # 以10行为单位提取数据  
dbHasCompleted(rs)  
d2 <- dbFetch(rs, n = -1)  # 提取所有剩余数据  
dbHasCompleted(rs)  
dbClearResult(rs)  # 清理  
dbDisconnect(con)

在这里插入图片描述

sqldfRSQLite_26">安装sqldf包和RSQLite

在进行操作前需要先安装sqldf包和RSQLite

install.packages("sqldf")  
install.packages("RSQLite") 

随后在脚本或命令行中导入这两个包:

library(sqldf)  
library(RSQLite) 

sqldf_37">sqldf包函数参数说明

参数名称描述
xSQL 查询语句,可以是单个字符串或字符串向量。如果 x 缺失,则建立一个数据库连接,后续 sqldf 语句将使用这个连接。
stringsAsFactors是否将字符型列转换为因子类型。
row.names是否为数据框添加行名列。
envir指定查找数据框的环境。
method指定输出数据框的列类型转换方式。
file.format用于读取文件到数据库的参数设置。
dbname数据库名称,对于 SQLite 和 h2 默认是 :memory:,表示嵌入式数据库
drv指定数据库驱动,如 "SQLite", "MySQL", "h2", "PostgreSQL"
user, password, host, port数据库连接的用户名、密码、主机和端口。
dllSQLite 可加载扩展的名称。
connection指定已存在的数据库连接。
verbose是否显示详细输出。

使用案例

1.创建数据库文件

这里我将R语言自带的iris数据集制作成.sqlite文件,在制作.sqlite文件时需要注意下,要将列命"Sepal.Length"更改为"Sepal_Length"格式,方便数据库操作。

library(RSQLite)   
# 创建SQLite数据库连接  
data("iris") 
names(iris) <- c("Sepal_Length", "Sepal_Width", "Petal_Length", "Petal_Width", "Species")
con <- dbConnect(RSQLite::SQLite(), dbname = "iris.sqlite")  
# 将iris数据集复制到数据库中的新表  
dbWriteTable(con, "iris_table", iris)  
# 关闭数据库连接(个人推荐每次在脚本结束时关闭连接)  
dbDisconnect(con)  

代码运行成功后会在工作目录中生成.sqlite文件和.sqlite-journal文件
在这里插入图片描述

2.查询表中数据

这里,使用刚才建立的iris.sqlite数据库驱动为 SQLite,并使用 sqldf 函数执行了一个简单的 SQL 查询,即选择 iris 表中的所有列和行。查询结果存储在 iris_query_from_db 数据框中,随后被打印出来。

library(RSQLite)   
library(sqldf)
# 创建SQLite数据库连接  
con <- dbConnect(RSQLite::SQLite(), dbname = "iris.sqlite")  
# 将iris数据集复制到数据库中的新表  
dbWriteTable(con, "iris_table", iris)  
# 关闭数据库连接(个人推荐每次在脚本结束时关闭连接)  
dbDisconnect(con)  
iris_query_from_db <- sqldf("SELECT * FROM main.iris_table WHERE Species = 'setosa'", dbname = "iris.sqlite")
# 打印结果
print(iris_query_from_db)

在这里插入图片描述

3.查询列名

先通过PRAGMA table_info命令从数据库中检索iris_table表的列信息。检索到的信息存储在一个数据框column_names中,随后提取column_names数据框中的name列,并将其转换为字符向量column_names_list,最后打印出这个列名列表。

column_names <- sqldf("PRAGMA table_info(iris_table);", dbname = "iris.sqlite")  
column_names_list <- as.character(column_names$name)  
print(column_names_list)

在这里插入图片描述

4.查询前几条记录

这里仍使用刚才创建的数据库文件,只查询前6行

sql">iris_first_few <- sqldf("SELECT * FROM main.iris_table LIMIT 5", dbname = "iris.sqlite") 
iris_first_few 

在这里插入图片描述

5.插入数据

先创建了一个包含新数据的数据框new_row,然后,使用dbExecute函数和参数化查询将新数据插入到iris_table表中,参数化查询可以帮助防止SQL注入攻击,并提高查询的安全性。

new_row <- data.frame(  Sepal.Length = 5.9,  Sepal.Width = 3.0,  Petal.Length = 5.1,  Petal.Width = 1.8,  Species = "virginica"  
)  
dbExecute(con, "INSERT INTO iris_table (Sepal_Length, Sepal_Width,Petal_Length, Petal_Width, Species) VALUES (?, ?, ?, ?, ?)",  params = list(5.9, 3.0, 5.1, 1.8, "virginica"))  
6.数据筛选

这里筛选出Petal_Width 为0.2的所有数据

sql">iris_petal_width_0_2 <- sqldf("SELECT * FROM main.iris_table WHERE Petal_Width = 0.2", dbname = "iris.sqlite")
iris_petal_width_0_2

在这里插入图片描述

7.数据聚合

通过sqldf()函数执行一个SQL查询,该查询从iris表中选取每个物种(Species),并计算其平均花萼长度(Sepal_Length)和花萼宽度(Sepal_Width)。查询结果存储在一个数据框ass中,

sql">ass <- sqldf('SELECT Species, AVG("Sepal_Length") AS Sepal_Length, AVG("Sepal_Width") AS Sepal_Width FROM iris GROUP BY Species')
ass

在这里插入图片描述

8. 多表连接

先创建两个数据框DF1DF2,它们分别包含IDValue列以及IDDetail列。然后通过sqldf函数执行一个SQL左连接查询,将DF1DF2ID列进行连接。查询结果包含DF1的所有列以及DF2中的Detail列,当ID匹配时,Detail列显示相应值,否则显示NA。查询结果存储在一个数据框result中,并打印出来以供查看。

sql"># 建立两个数据框 DF1 和 DF2
DF1 <- data.frame(ID = 1:5, Value = 10:14)
DF2 <- data.frame(ID = 3:7, Detail = letters[1:5])
# 使用 SQL 进行连接查询
result <- sqldf("SELECT DF1.*, DF2.Detail FROM DF1 LEFT JOIN DF2 ON DF1.ID = DF2.ID")
result

在这里插入图片描述

最后切记有个好习惯,关闭数据库连接

dbDisconnect(con)

http://www.ppmy.cn/server/121411.html

相关文章

【STM32】定时器

一、 定时器概述 定义 ​ 设置等待时间&#xff0c; 到达后则执行指定操作的硬件。 STM32F407 的定时器有以下特征 ​ 具有基本的定时功能&#xff0c; 也有 PWM 输出&#xff08;灯光亮度控制、 电机的转速&#xff09;、 脉冲捕获功能&#xff08;红外捕捉&#xff09;。…

6--SpringBootWeb案例(详解)

目录 环境搭建 部门管理 查询部门 接口文档 代码 删除部门 接口文档 代码 新增部门 接口文档 代码 已有前端&#xff0c;根据接口文档完成后端功能的开发 成品如下&#xff1a; 环境搭建 1. 准备数据库表 (dept 、 emp) -- 部门管理 create table dept( id int un…

仿黑神话悟空跑动-脚下波纹特效(键盘wasd控制走动)

vue使用three.js实现仿黑神话悟空跑动-脚下波纹特效 玩家角色的正面始终朝向鼠标方向&#xff0c;且在按下 W 键时&#xff0c;玩家角色会朝着鼠标方向前进 空格建跳跃 <template><div ref"container" class"container" click"onClick"…

在vue中嵌入vitepress,基于markdown文件生成静态网页从而嵌入社团周报系统的一些想法和思路

什么是vitepress vitepress是一种将markdown文件渲染成静态网页的技术 其使用仅需几行命令即可 //在根目录安装vitepress npm add -D vitepress //初始化vitepress&#xff0c;添加相关配置文件&#xff0c;选择主题&#xff0c;描述&#xff0c;框架等 npx vitepress init //…

黑马头条day3-2 自媒体文章管理

前边还有一个 素材列表查询 没什么难度 就略过了 查询所有频道和查询自媒体文章也是和素材列表查询类似 就是普通的查询 所以略过了 文章发布 这个其实挺复杂的 一共三张表 一个文章表 一个素材表 一个文章和素材的关联表 区分修改与新增就是看是否存在id 如果是保存草稿…

CertiK因发现Apple Vision Pro眼动追踪技术漏洞,第6次获苹果认可

​2024年9月20日&#xff0c;头部Web3.0安全机构CertiK自豪地宣布&#xff0c;CertiK的工程师因发现Apple Vision Pro MR&#xff08;混合现实&#xff09;头显设备中的关键漏洞而获得Apple公司认可&#xff0c;这已经是Apple公司第六次公开发布对CertiK的致谢&#xff0c;Cert…

解决Matlab串口通信中接收到的消息不能正常显示

问题描述 如图&#xff0c;经过函数把接收到的十六进制字符串转换为EEE754标准浮点数后速度角度无法正常解析显示&#xff0c;其中速度角度的解码过程如下&#xff1a; &#xff08;以速度为例&#xff09; yv_temp1 dec2hex(data_receive(2)); yv_temp2 dec2hex(data_receive…

二叉树遍历、查找、深度等

在面试中&#xff0c;二叉树问题是一个常见的主题。下面我将展示如何在 Python 3.11 中实现二叉树的基本结构和几种常见的面试题解法&#xff0c;包括二叉树的遍历、查找、深度等。 1. 二叉树节点的定义 class TreeNode:def __init__(self, value0, leftNone, rightNone):sel…