4

I'm trying to add columns to a csv file from multiple files. This is what I have tried so far, but keep ending up with either an empty file or just the column from the last file.

    while read i; do
        awk '{print $4}' ${i}.txt > ${i}_temp
        awk 'NR==FNR{a[NR]=$0;next}{print a[FNR],$0}' OFS=, test.csv ${i}_temp >> test.csv
    done < file_list

The file list is just a list of accession numbers:

NA123
NA124
NA125
...

The text files that they correspond to have 4 columns in them, and I want to copy the last column and add it as the next column in a csv file. The contents of the files look like this:

NA123.txt:
April-18    10:00    18:00    8
April-19    09:00    19:00    10
April-20    11:00    16:00    5
...
NA124.txt:
April-18    14:00    18:00    4
April-19    09:00    15:00    6
April-20    07:00    16:00    9
...
NA125.txt:
April-18    10:00    22:00    12
April-19    09:00    12:00    3
April-20    06:00    16:00    10
...
test.csv:
0,
1,
2,
...

I would like the output to be:

0,8,4,12
1,10,6,3
2,5,9,10
...

What do I need to change about this or is there a more efficient way to do this?

1

Maybe you could try to use paste and one additional temp file

touch temp

while read i; do
        awk '{print $4}' ${i}.txt > ${i}_temp
        paste temp ${i}_temp > test.csv
        cp test.csv temp
done < file_list

rm temp
2

try:

 <file_list xargs -I % awk '
     { system("sed -i '\'' " NR "s/,\\?$/," $4 "/'\'' test.csv" ) }' %.txt

the xargs is reading filename from file_list file and feed to awk thought xarags's variable called % with .txt suffix as filename contains.

with sed '#s/$/something/' test.csv" command that used to append something at the end $ of a specific line number # in a file like test.csv; so at above, $4 is the string that need to appended to specific line number taking from NR; ,\?$ is saying might your line ends with comma , which I used because you have it in your test.csv. this sed command is calling by awk's system() function.

note that sed is writing in-place any changes, so you will not notice terminal ouput, do:

$ cat test.csv
0,8,4,12
1,10,6,3
2,5,9,10
1

Why not just use paste?

$ cat in1; cat in2
row1,col2,col3
row2,col2,col3
row3,col2,col3
row4,col2,col3
row1
row2
row3
row4
$ paste -d, in1 in2
row1,col2,col3,row1
row2,col2,col3,row2
row3,col2,col3,row3
row4,col2,col3,row4
  • OP needs merge column 4 from all files where their partial name is in file_list with test.csv file – αғsнιη May 9 at 1:42
  • That was not clear in the original presentation of the question which I addressed with this answer. – DopeGhoti May 9 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.