I have various .txt files stored in multiple folders. The txt files have various columns, one of which is Temperature. Few initial txt files temperature column name as T2 [°C] while others have it as T2 [?C]. I want to keep the temperature column name as T2 [°C] in all the files. I do not want to change the names of other columns. Also, the number of columns in all the files is not the same. (e.g. Few files have columns such as Pressure, Temperature, Radiation, Wind velocity, Wind direction and other files have only Pressure, Temperature and Radiation. It can be thought of as a case of missing data. I could think of a logical condition that whereever we have T2 [?C], it should be replaced with T2 [°C])
I tried to use colnames(my_dataframe)[colnames(my_dataframe) == "id"] ="c1" but it didn't work.
I am using following code in R
setwd("D:/Data/RawData/Task")
dir <- "D:/Data/RawData/Task/"
fnames <- list.files(dir, full.names = T, recursive = TRUE)
colnames(fnames)[colnames(fnames) == "T2 [?C]"] ="T2 [°C]"
xy <- do.call(rbind, lapply(fnames, read.table, header=TRUE, sep = "\t", check.names = FALSE,
skip = 27))
Could anyone please help me in changing the column name as well as in fixing the number of columns in all the files.
We can tidy the column names in an lapply function and merge using rbindlist, fill = TRUE which fills missing columns with NA. I chose to replace [] with () in the column names (use of [ ] may lead to issues).
#libraries
library(data.table)
#list of files
filelist <- list.files("D:/Data/RawData/Task/",
full.names = TRUE,
recursive = TRUE
pattern = ".txt$")
#read
dt <- lapply(filelist, fread)
#adjust colnames
dt.tidied <- lapply(dt, FUN = function(x){
#adjust ? to °
setnames(x, old = "T2 [?C]", new = "T2 [°C]", skip_absent = TRUE)
#replace [] with ()
colnames(x) <- gsub("\\[", "(", colnames(x))
colnames(x) <- gsub("\\]", ")", colnames(x))
#return
return(x)
})
#bind, filling missing columns to NA
merged <- rbindlist(dt.tidied, fill = TRUE)