I have a folder containing 15000 CSV files and I need to remove all NAs in all those files.
•
written
by
paramount.amin •
if files do not have headers, try running the following command (take a back up of your files and create a directory by name "output", before you proceed):
$ parallel 'grep -v "NA" {} > output/new_{.}.csv' ::: *.csv
if files have headers, install tsv-utils and run following command:
$ parallel 'keep-header {} -- grep -v "NA" > output/new_{.}.csv' ::: *.csv
Probably this job more suitable for bash, but here is using R: find all files, keep only complete cases (remove any row that has NA), then output with renamed filename.
library(data.table)
for(i in list.files("path/to/files", pattern = ".*\\.csv", full.names = TRUE)){
d <- fread(i)
fwrite(d[ complete.cases(d), ], file = paste0(i, ".clean.csv"))
}
R uses POSIX extended regex, so * means 0 or more times, and . means any character except newline. Your regex as written should most likely be .*\\.csv, however, csv$ should be sufficient.
It will work, but not as intended or expected. It's better to use properly formatted regular expressions to avoid capturing Mechanicsville, Virginia.
Take it from someone whose had all kinds of strange unintended regex captures that it's worth it to make sure it's properly formatted, haha.
This code doesn't produce a ".clean.csv" file
Log in to answer this question.
Do you want to remove any rows/columns with NA values, or replace NA with something? For this question you may want to include an example of what one of the files looks like.
Remove NAs meaning:
I need to remove rows with NAs
You have to write a script to iterate on each csv to drop rows that contains NA.