R:从具有稍微不同的列标题(不同的空格)的txt文件中读取特定列并绑定它们

Question

我有许多txt文件，它们在用；分隔的列中包含相同类型的数字数据；。但是有些文件有带空格的列标题，有些则没有(由不同的人创建(。有些有我不想要的额外列。

例如，一个文件可能有一个标题，如：

ASomeName; BSomeName; C(someName%) 

而另一个文件头可能是

A Some Name; B Some Name; C(someName%); D some name

在我调用"之前，如何清除名称中的空格；读取"；命令

#These are the files I have
filenames<-list.files(pattern = "*.txt",recursive = TRUE,full.names = TRUE)%>%as_tibble()
#These are the columns I would like:
colSelect=c("Date","Time","Timestamp" ,"PM2_5(ug/m3)","PM10(ug/m3)","PM01(ug/m3)","Temperature(C)",  "Humidity(%RH)", "CO2(ppm)")
#This is how I read them if they have the same columns
ldf <- vroom::vroom(filenames, col_select = colSelect,delim=";",id = "sensor" )%>%janitor::clean_names()

清除标头脚本

我写了一个破坏性的脚本，它将读取整个文件，清除标题中的空格，删除文件，然后使用相同的名称重新写入(vroom有时抱怨无法打开X数千个文件(文件。不是一种高效的做事方式。

cleanHeaders<-function(filename){
d<-vroom::vroom(filename,delim=";")%>%janitor::clean_names()
#print(head(d))
if (file.exists(filename)) {
#Delete file if it exists
file.remove(filename)
}
vroom::vroom_write(d,filename,delim = ";")
}
lapply(filenames,cleanHeaders) 

Answer 1

fread的select参数允许整数索引。如果所需的列始终位于同一位置，则说明您的工作已完成。

colIndexes = c(1,3,4,7,9,18,21)
data = lapply(filenames, fread, select = colIndexes)

我想vroom也有这个功能，但由于你已经在选择你想要的列，我认为懒散地评估你的字符列一点都没有帮助，所以我建议你坚持使用data.table。

不过，对于更健壮的解决方案，因为您无法控制表的结构：您可以读取每个文件的一行，捕获并清理列名，然后将它们与干净版本的colSelect向量进行匹配。

library(data.table)
library(janitor)
library(purrr)
filenames <- list.files(pattern = "*.txt",
recursive = TRUE,
full.names = TRUE)
# read the first row of data to capture and clean the column names
clean_col_names <- function(filename){
colnames(janitor::clean_names(fread(filename, nrow = 1)))
}
clean_column_names <- map(.x = filenames, 
.f = clean_col_names)
# clean the colSelect vector
colSelect <- janitor::make_clean_names(c("Date",
"Time",
"Timestamp" ,
"PM2_5(ug/m3)",
"PM10(ug/m3)",
"PM01(ug/m3)",
"Temperature(C)",
"Humidity(%RH)",
"CO2(ppm)"))
# match each set of column names against the clean colSelect
select_indices <- map(.x = clean_column_names, 
.f = function(cols) match(colSelect, cols))
# use map2 to read only the matched indexes for each column
data <- purrr::map2(.x = filenames, 
.y = select_indices, 
~fread(input = .x, select = .y))

(在这里，purrr可以很容易地被传统的lapply取代，我选择purrr是因为它的配方符号更干净(