sql - 使用 RSQLite 在 r 中直接使用 SQL 操作数据框

Question

我有一个表格的数据集

我想在 R 中使用 SQL 更改为下面的这种形式。

我知道我可以每天简单地这样做，dplyr但这里的重点是学习使用 SQL 创建和操作一个小型关系数据库。

• Price需要转成数值。删除“R”和中间的空格。

• coordinates需要变成2个坐标Long和Lat

• floor size需要从删除空格和末尾的“m^2”的字符串中转换为数字。

最小工作示例

# Data to copy into sheet

       Price                            coordinates floor.size surburb       date
 R 1 750 000 -33.93082074573843, 18.857342125467635      68 m²     Jhb 2021-06-24
 R 1 250 000 -33.930077157927855, 18.85420954236195      56 m²     Jhb 2021-06-17
 R 2 520 000 -33.92954929205658, 18.857504799977896      62 m²     Jhb 2021-06-24

在 R markdown 中操作的代码

```{r}
#install.packages("RSQLite", repos = "http://cran.us.r-project.org")

library(readxl)
library(dplyr)
library(RSQLite)
library(DBI)
library(knitr)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(connection = "db")


# Import data
dataH <- read_excel("C:/Users/Dell/Desktop/exampledata.xlsx")

``` 

```{sql, connection = db}
# SQL code passed directly
```

编辑1：

@Onyambu 的答案几乎有效。它正在产生坐标错误。例如，在下面的图像中，最后两个坐标应该以“-33.930989501123, 18.857270308516927”Long而不是“.85 ”开头。coordinate我将如何解决这个问题？

标签： sqlrr-markdowndata-wranglingrsqlite

Answer 1

使用基本的 sql 函数，您可以执行以下操作：

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE,connection = "db")
```

```{r}
db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

txt <- "Price coordinates floor.size surburb date\n
     'R 1 750 000' '-33.93082074573843, 18.857342125467635' '68 m²' Jhb 2021-06-24\n
     'R 1 250 000' '-33.930077157927855, 18.85420954236195' '56 m²' Jhb 2021-06-17\n
     'R 2 520 000' '-33.92954929205658, 18.857504799977896' '62 m²' Jhb 2021-06-24"

dataH <- read.table(text = txt, header = TRUE) 
DBI::dbWriteTable(db, 'dataH', dataH)
```


```{sql}
SELECT REPLACE(SUBSTRING(price, 3, 100), ' ', '') price,
       replace(SUBSTRING(coordinates, 1, 20), ',', '') Lat,
       SUBSTRING(coordinates, 21, 255) Long,
       SUBSTRING(`floor.size`, 1, 2) floor_size,
       surburb,
       date
FROM dataH
```