How to delete certain columns in multiple csv files in a Folder?

Question

How can I import multiple CSV files (15000 CSV files) in R and delete certain columns in each file and save it?

Answer 1

You could automatize a read-manipulate-write approach.

Say, your .csvs are in your working directory.

ldir <- getwd()    

Store their names in a vector.

## list names
nm <- paste0(ldir, dir(pattern="tab\\d"))  ## list names
nm <- paste0(ldir, c("tab1.csv", "tab2.csv", "tab3.csv", "tab4.csv"))  ## or hard coded

First let's inspect heads of our example .csvs.

## inspect head of each .csv
lapply(nm, function(i) head(read.csv(i), 3))
# [[1]]
#           X1        X2         X3        X4
# 1  1.3709584  1.304870 -0.3066386 0.4554501
# 2 -0.5646982  2.286645 -1.7813084 0.7048373
# 3  0.3631284 -1.388861 -0.1719174 1.0351035
# 
# [[2]]
#           X1         X2         X3          X4
# 1  0.2059986  0.3219253 -0.3672346 -1.04311894
# 2 -0.3610573 -0.7838389  0.1852306 -0.09018639
# 3  0.7581632  1.5757275  0.5818237  0.62351816
# 
# [[3]]
#           X1         X2        X3          X4
# 1 1.51270701  1.3921164  1.200965 -0.02509255
# 2 0.25792144 -0.4761739  1.044751  0.10807273
# 3 0.08844023  0.6503486 -1.003209 -0.48543524
# 
# [[4]]
#           X1            X2         X3          X4
# 1 -1.4936251  5.676206e-01 -0.0861073 -0.04069848
# 2 -1.4704357 -4.928774e-01 -0.8876790 -1.55154482
# 3  0.1247024  6.288407e-05 -0.4446840  1.16716955

Then, put names of columns to delete in another vector.

## select columns to delete
to.del <- c("X2", "X4")

Finally, we use mapply to multivariately loop through both vectors. In FUN, there are two versions of write.csv, one that overwrites the original .csvs, and one that makes a copy.

## read, delete, and write back
mapply(function(x, y) {
  r <- read.csv(x)
  # write.csv(r[, !names(r) %in% y], row.names=FALSE, file=x)  ## use to override
  write.csv(r[, !names(r) %in% y], row.names=FALSE, 
              file=paste0(dirname(x), "copy_of_", basename(x)))  ## use for copy
  }, nm, list(to.del))

Let's check, if everything worked as expected.

## check result
lapply(paste0(dirname(nm), "copy_of_", basename(nm)), function(i) 
  head(read.csv(i), 3))
# [[1]]
#           X1         X3
# 1  1.3709584 -0.3066386
# 2 -0.5646982 -1.7813084
# 3  0.3631284 -0.1719174
# 
# [[2]]
#           X1         X3
# 1  0.2059986 -0.3672346
# 2 -0.3610573  0.1852306
# 3  0.7581632  0.5818237
# 
# [[3]]
#           X1        X3
# 1 1.51270701  1.200965
# 2 0.25792144  1.044751
# 3 0.08844023 -1.003209
# 
# [[4]]
#           X1         X3
# 1 -1.4936251 -0.0861073
# 2 -1.4704357 -0.8876790
# 3  0.1247024 -0.4446840

Yap.

---

Example data:

## generate example .csvs in wd
set.seed(42)
sapply(1:4, function(i) write.csv(data.frame(matrix(rnorm(10*4), 10, 4)), 
                                  row.names=FALSE, file=paste0("tab", i, ".csv")))