Deleting specific Columns in multiple CSV files using rstudio

Hello Everyone

I have a folder, consist of 15000 separate CSV files. All of these csv files have the same heading ( date, time, event,...). I want to remove the event column in all of my csv files. How can I do that?

Thank you

• 5,436 views • link

written 5.9 years ago by paramount.amin •

3 answers

You can do this with cut.

mkdir cleaned
for file in $(find . -name "*\.csv"); do cut -d"," -f1,2,4- $file > cleaned/$(basename $file); done

If you really want to do it in R.

library("data.table")
library("purrr")

dir.create("cleaned")
files <- list.files(pattern="\\.csv$", full.names=TRUE)
walk(files, function(x) {
  DT <- fread(x, sep=",")
  DT[, event := NULL]
  fwrite(DT, file.path("cleaned", basename(x)), sep=",", col.names=TRUE, row.names=FALSE, quote=FALSE)
})

EDIT: awk command amended to a cut command after the kind correction from @Alex Reynolds

• 0 views • link

written 5.9 years ago by rpolicastro

If you're working with tab- or comma-delimited data, I would respectfully suggest checking this answer before running it on 15k files.

In the case of a tab delimiter, the awk command shown will not remove the column, it will only put a blank string in its place, and it will replace tabs with spaces:

$ echo -e 'A\tB\tC\tD' | awk '{ $3 = ""; print $0; }' | cat -te
A B  D$

Instead, perhaps use cut -f:

$ echo -e 'A\tB\tC\tD' | cut -f1,2,4- | cat -te 
A^IB^ID$

Likewise, if the delimiter is a comma, instead of a tab, the issue is the same (and the FS and OFS variables must be overridden for use with CSV input):

$ echo -e 'A,B,C,D' | awk -v FS="," -v OFS="," '{ $3 = ""; print $0; }' | cat -te
A,B,,D$

Use of cut is probably desired:

$ echo -e 'A,B,C,D' | cut -d"," -f1,2,4- | cat -te
A,B,D$

Adjust the removed column index (from 3 to i), as needed.

• 0 views • link

written 5.9 years ago by Alex Reynolds

You are indeed correct and I have amended my answer. Many thanks for the correction.

• 0 views • link

written 5.9 years ago by rpolicastro

Awesome!

1-How can I remove two or more columns like event, time, ...?

Thank you so much

• 0 views • link

written 5.9 years ago by paramount.amin •

Alex Reynolds gives a great explanation in his reply if you choose to use the cut option. For the R option you can change DT[, event := NULL] to DT[, c("event", "time") := NULL] to remove as many columns as you wish.

EDIT: Alex's post seems to have disappeared so I'll expand on cut a little bit. The part of cut that lets you select columns is the -f1,2,4- part. For that example you select the first, second, and fourth and above columns. -f1-5 would select columns one through five. -f2-5,7- would select columns two through five, and then columns seven and above.

• 0 views • link

written 5.9 years ago by rpolicastro

Thank you for your prompt and great answer to this problem. I really appreciate it

• 0 views • link

written 5.9 years ago by paramount.amin •

Do you have specific need for R to remove columns? You can do it outside R. With Gnu-Parallel and tsv-utils:

$ parallel  'tsv-select -H -d "," -e "event" {} > output_directory/{.}_new.csv' ::: *.csv

with Gnu-Parallel and csvtk:

$ parallel csvtk cut -f -event {} -o {.}_new.txt ::: *.txt

Create output directory before running the code.

• 0 views • link

written 5.9 years ago by cpad0112

Awesome!

1-How can I remove two or more columns like event, time, ...?

Thank you so much

• 0 views • link

written 5.9 years ago by paramount.amin •

Use cut -f where the arguments you pass are the columns you want. Any indices you don't specify will get removed or "cut".

For example:

• cut -f1-10 will remove columns 11 and above • cut -f1,2,5,8 will remove all columns, except columns 1, 2, 5, and 8 • cut -f4,6- will remove all columns, except column 4 and columns 6 and above

This should demonstrate the general principle. The numbers you use are specific to your dataset.

If you are working with comma-separated data, don't forget to add -d "," to specify that the column delimiter is a comma character.

• 0 views • link

written 5.9 years ago by Alex Reynolds