How to remove NAs in multiple CSV files in a folder

Question

[paramount.amin]

I have a folder containing 15000 CSV files and I need to remove all NAs in all those files.

[cpad0112]

Remove NAs meaning:

1. Replace NAs with "" ?
2. Remove rows with NAs?

[paramount.amin]

I need to remove rows with NAs

[shoujun.gu]

You have to write a script to iterate on each csv to drop rows that contains NA.

[rpolicastro]

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.

Answer 1

[cpad0112]

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

Answer 2

[zx8754]

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"))
}

[rpolicastro]

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.

[zx8754]

By far I am not a regex expert, but "*.csv" works fine for me. In any case, OP can test and amend as needed.

[rpolicastro]

It will work, but not as intended or expected. It's better to use properly formatted regular expressions to avoid capturing Mechanicsville, Virginia.

[zx8754]

OK, convinced :) updated the post, thank you.

[rpolicastro]

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.

[paramount.amin]

This code doesn't produce a ".clean.csv" file