Deleting a column in multiple files that are comma separated

Shell Programming and Scripting
1

Hi,

I have a directory that contains say 100 files named sequencially like input_1.25_50_C1.txt
input_1.25_50_C2.txt
input_1.25_50_C3.txt
input_1.25_50_C4.txt
..
..
..
input_1.25_50_C100.txt

an example of the content in each of the file is:

"NAME" "MEM.SHIP"
"cgd1_10" "cgd1_10" 0.938962779873022
"cgd1_1860" "cgd1_1860" 0.728179501334915
"cgd1_2500" "cgd1_2500" 0.751709755780061
"cgd1_2560" "cgd1_2560" 0.8086690400501
"cgd1_2640" "cgd1_2640" 0.750586302165714
"cgd1_2800" "cgd1_2800" 0.954020921349781
"cgd1_290" "cgd1_290" 0.757355995574881
"cgd1_2950" "cgd1_2950" 0.558344549451538
"cgd1_3010" "cgd1_3010" 0.667420188529345
"cgd1_50" "cgd1_50" 0.545172222953814
"cgd2_1000" "cgd2_1000" 0.944926355001402
"cgd2_1690" "cgd2_1690" 0.861817882469458
"cgd3_1820" "cgd3_1820" 0.817150133730118
"cgd3_2150" "cgd3_2150" 0.553855831143878
"cgd4_2220" "cgd4_2220" 0.577452218116135

I want to delete the second & third column of each file, make the file tab delimited and replace the column heading "NAME" in the first column as [Gene]

Is there a way in awk or shell programming to do this at a directory level; without openning each file and editing it.

Please let me know.

Lucky Ali

2

I think I am missing something here as usual.

Your input file seems to only have 3 columns, if you want to delete 2 and 3 what is left to delimit with tabs? You also say it is coma delimited but I see no coma's....

3

Sorry,
Forget about the tab delimit and comma separated. It was my mistake. I just wanted the first column with out enclosed with " " and change the heading for all the files in the Directory.

---------- Post updated at 02:39 PM ---------- Previous update was at 02:37 PM ----------

Need to delete column 2 and 3

---------- Post updated at 02:45 PM ---------- Previous update was at 02:39 PM ----------

Also if I import the above file in excel, I have to use ',' as delimter

4

suggestion to get the first column of each file

for FILE in *.txt
do
    NEWFILE=new-$FILE # It's an example
    cut -d' ' -f1 $FILE > $NEWFILE
done
5

Hi
I type this:

for FILE in *.txt; do NEWFILE=new-$FILE; cut -d ' ' -f1 $FILE > $NEWFILE; done

but it just copied the file like that into another file with the new name

Let me know what mistake I did

6

Option for cut : -d [delimiter] i've put a space between the quotes : -d ' '.
-f [field number(s)].
if You don't specify -d, the [TAB] will be used as delimiter. In any case the delimiter must be a single caracter.
It seems that you did'nt put anything between the quotes.

7

I specified a space in between the quotes and tried again. But still it just copying the file in a new file; not getting the first column. Let me know if you need any other info

8

I tried with a file containing your sample data and returns the first column.
I thought the delimiter in your file was a space but if i'ts a TAB just remove the -d option

for FILE in *.txt; do NEWFILE=new-$FILE; cut -f1 $FILE > $NEWFILE; done

for test you can remove the " > $NEWFILE " for a screen output and when it works, put it again.

9

Actually this is the content of each file.

"","NAME","MEM.SHIP"
"cgd2_3990","cgd2_3990",0.502835961835107
"cgd2_580","cgd2_580",0.518630633066887
"cgd3_2220","cgd3_2220",0.542186902734677
"cgd4_1650","cgd4_1650",0.583454483346155
"cgd4_260","cgd4_260",0.551425561426544
"cgd5_840","cgd5_840",0.675010580680461
"cgd6_70","cgd6_70",0.517322140721342
"cgd8_1520","cgd8_1520",0.743127593091679

The first column is "","

---------- Post updated at 04:02 PM ---------- Previous update was at 03:38 PM ----------

The file was comma separated and when I specified -d ',' it worked fine.
Thanks.
the out put of the file looks like

"NAME"
"cgd1_200"
"cgd1_3210"
"cgd1_560"
"cgd2_2760"
"cgd2_290"
"cgd3_3210"
"cgd3_3310"
"cgd3_660"
"cgd5_2130"
"cgd5_4080"
"cgd6_3690"
"cgd6_4480"
"cgd8_1540"
"cgd8_3860

I have another question:
I wanted to remove the " " from the above.
I can apply regular expression to this file using the command %s/"//ig.
But I have to do it for each and every file.

How do i apply the above regular expression in awk or shell programming so that I do it to all the files.

Please let me know.

---------- Post updated at 04:05 PM ---------- Previous update was at 04:02 PM ----------

Frans,

The file was comma separated and when I specified -d ',' it worked fine.
Thanks.
the out put of the file looks like

"NAME"
"cgd1_200"
"cgd1_3210"
"cgd1_560"
"cgd2_2760"
"cgd2_290"
"cgd3_3210"
"cgd3_3310"
"cgd3_660"
"cgd5_2130"
"cgd5_4080"
"cgd6_3690"
"cgd6_4480"
"cgd8_1540"
"cgd8_3860

I have another question:
I wanted to remove the " " from the above.
I can apply regular expression to this file using the command %s/"//ig.
But I have to do it for each and every file.

How do i apply the above regular expression in awk or shell programming so that I do it to all the files.

Please let me know.

10

replace the

cut -d',' -f1 > $NEWFILE

with

cut -d',' -f1 | sed 's/"//g' > $NEWFILE