sql - 如果限于本地机器，最好使用 R 和 SQL

Question

我正在努力改进我的工作流程，并希望社区能够提供见解，因为我对“大数据”还比较陌生。

我通常从公共(public)资源下载多个数据帧，这些数据帧可能相互关联。提取多个数据帧后，我会执行各种数据处理步骤(例如过滤、排序、聚合、自定义计算)，然后再对我的最终数据集进行回归分析和/或其他推断统计。

具体来说，您会推荐哪种策略:

1. 将所有数据作为单独的文件从 Web 下载到我的本地机器上，然后使用 R 直接处理它(就像我一直在做的那样)。我看到的潜在问题是一切都在 R 的工作环境中，这可能会减慢进程并使我的机器崩溃。

或

1. 将所有数据作为单独的文件从 Web 下载到我的本地机器上，在本地机器上使用 sqldf 创建数据库，并使用 sqldf 进行后续查询> 在 R 中进行最终数据分析之前从该数据库中提取和汇总信息。我看到的潜在问题是，一个数据库由一些表/数据帧组成，是在我的本地计算机上使用 sqldf< 创建的 的大小比简单地保存几个单独的 .csv 文件要大。

我非常熟悉统计技术，但我承认在数据库管理和服务器操作方面存在一些知识空白。我已经熟悉了 SQL 作为一种语言的具体细节，并且知道如何将 sqldf 与在 R 工作环境中运行的数据帧一起使用。但是，坦率地说，与仅学习如何使用基本 R 函数来过滤、排序和聚合数据相比，我不知道它有什么优势。此外，我已经阅读了一些关于将 SQL Server 与 R 配对的宣传的网页，但我不确定这对我来说是否是一个好的选择，因为我在本地运行所有内容。

关于如何通过将 R 与 SQL 的某些实现相结合来改进我的数据处理和分析，对于这个新手有什么建议吗？

提前致谢!

Answer 1

鉴于您正在寻找“工作流程最佳实践”，因此应该非常重视可再现性 和透明度。由于您的目标是数据分析而不是数据收集或数据库管理，因此没有充分的理由创建您自己的数据库，自定义数据库可能会使您的工作流程和分析不那么透明。 简而言之，如果你不需要建立数据库，就不要。

听起来您的工作流程如下:

1. 从公共(public)资源下载数据(最好是 .csv 或类似的好格式)
2. 清理和处理数据
3. 对(可能关联的)清理后的数据进行分析

我建议将您的工作流程分为两个不同的步骤:

1。下载和清理数据

如果您的文件都是 .csv(或其他常规分隔文件)，那么这一步您只需要 data.table 包。您可以编写单个 R 脚本来下载、清理和保存您需要的数据。下面是一个最小的例子:

# Download data
library(data.table)
salary_data <- fread('https://data.phila.gov/api/views/25gh-t2gp/rows.csv')

# Clean data (only looking at City Council salaries)
cleaned_data <- salary_data[Department == 'CITY COUNCIL']

# Saving cleaned data
save(cleaned_data, file = 'my_file_name.rda', compress = TRUE)

理想情况下，您只需运行此文件一次即可生成您实际执行统计分析的数据集。如果您决定以不同方式清理或处理您的数据，只需重新访问此文件，进行适当的更改，然后重新运行它。我建议为您正在下载的每个文件准备一个脚本，这样您就可以很容易地看到您是如何直接从源头处理原始数据的(透明度)。只需拥有此文件即可满足可再现性。

2。统计分析

如果您需要合并您的数据集，data.table 提供了一种快速且透明的方法来执行此操作。只需加载您清理过的个人数据集，确定您将用于合并它们的 key ，然后合并它们。然后对合并的数据集运行分析。下面是此功能的示例:

# dt1 has salary information for 10 people and dt2 
# has the number of kids for the same 10 people
library(data.table)
dt1 <- data.table(id = 1:10, salary = sample(0:100000, 10)
dt2 <- data.table(id = 1:10, kids = sample(0:5, 10)
save(dt1, file = 'dt1.rda', compress = TRUE)
save(dt2, file = 'dt2.rda', compress = TRUE)

# Loading and merging data
load(file = 'dt1.rda')
load(file = 'dt2.rda')
setkey(dt1, id)
setkey(dt2, id)
merged_dt <- merge(dt1, dt2)

# Doing regression analysis on merged data
reg <- lm(salary ~ kids, data = merged_dt)

这使得合并过程和后续分析变得透明和可重现。

总结

此过程可确保您的数据源、数据清理/处理和分析记录完备、透明且可重现。此外，此过程可根据您的计算机进行扩展。如果您不需要构建数据库，那就不要构建。

如果数据对我的电脑来说太大怎么办？如果您需要更多空间，只需在专用服务器或 Amazon Web Services 计算机上运行您已经编写的代码。

如果数据对于专用服务器来说太大了怎么办？数据很可能存储在实际数据库中，唯一改变的工作流程是您的数据下载和(可能)一些处理的一部分将是对数据库的 SQL 查询(很可能使用在 R 中运行 SQL 查询的 DBI 包)，然后它应该足够小以在本地或专用服务器上运行。

如果我的数据太大了怎么办？您可能应该研究更强大的大数据语言，例如 Hadoop。

补充说明:如果您的数据不是常规分隔格式(例如 Excel、SAS 或 Stata 文件)，那么我建议使用 download_file() 函数与 tidyverse 包相结合(它具有阅读这些不太愉快但常见文件的奇妙能力)

library(tidyverse)
taxi_data_excel <- download.file(url = 'http://www.nyc.gov/html/tlc/downloads/excel/current_medallion_drivers.xls', destfile = 'taxi_data_excel.xls')
taxi_data <- read_excel('taxi_data_excel.xls')

然后像往常一样进行清洁。