2

I would like to make a loop in which a certain column (in my case column 4) from a text file is added as last column to a new text file. I have in total around 500 text files (V1-V500) from which I want to take the fourth column and add it to the new text file (columns separated by tabs). All text files have the same number of lines. In addition, the heading of the column that was added should contain the file name of the text file where it was originally from. I've tried to work out a command line with awk and a for-loop already, but none of my commands work. I've tried command lines based on the command line of a previous post. I'm working in Linux with GNU tools available.

To give an example: V1 text file

header1 header2 header3 header4
1       5       9       13 
2       6       10      14
3       7       11      15
4       8       12      16

V2 text file:

header1 header2 header3 header4
17       25       21      29 
18       26       22      30
19       27       23      31
20       28       24      32

NEW text file:

V1 V2
13 29
14 30
15 31
16 32

Thanks for your help!

0

4 Answers 4

4

With awk parsing all files.

awk -F'\t' -v OFS='\t' '{
        x = (FNR==1 ? FILENAME : $4)
        a[FNR] = (FNR==NR ? x : a[FNR] OFS x)
    } 
    END {
        for (i=1;i<=FNR;i++) print a[i]
    }' V{1..500}

x is what we keep from every line and a is the new line we build. Both are assigned using a conditional expression. FNR is the line number of the current input file, NR the total one. FNR==NR means "when parsing the first file". Also I have assumed tab-delimited inputs and output.

0
2

A non-awk solution:

#!/bin/bash
for num in {1..500}; do
        echo V$num > temp_$num #put the filename as header
        < V$num tail -n+2 | cut -f4 >> temp_$num #get the contents of column 4
        if [[ -f V_new ]]; then #if not first iteration
                paste V_new temp_$num > temp #combine previous files with current file
                mv temp V_new
        else # if first iteration
                mv temp_$num V_new 
        fi
done
5
  • 1
    Did you test this? Cut uses tabs as separators by default, and I don't think it would work even if you set the separator to spaces since there are multiple spaces in the input file.
    – terdon
    Apr 20 at 18:53
  • @terdon Yes I tested with tabs in the input files since the OP said "columns separated by tabs". You're right that it would need to be slightly different if the input file columns were separated by spaces.
    – enharmonic
    Apr 20 at 19:24
  • @terdon line 4 would be < V$num tail -n+2 | tr -s ' ' | cut -d' ' -f4 >> temp_$num if the input file were space-delimited
    – enharmonic
    Apr 20 at 19:34
  • Where did the OP say it was tab separated? I missed that. If so, this should indeed work. If it's spaces, however, cut -d ' ' -f4 will only work if there is exactly 1 space between each field. Try echo "a b" | cut -d ' ' -f 2 (that's two spaces between a and b), and you'll see it will print a space.
    – terdon
    Apr 21 at 9:15
  • 1
    @terdon right, that's why I have tr -s ' ' in the pipeline to reduce multiple spaces to one.
    – enharmonic
    Apr 21 at 23:24
1

While you can do it in awk, or other such tools, I would suggest simpler approach here:

$ printf 'paste ' > script
$ printf "<(awk 'NR==1{print FILENAME; next}{print \$4}' %s) \\\\\n" V{1..500} >> script
$ sh ./script

That will create a complex paste command in the file script looking like this:

$ head script 
paste <(awk 'NR==1{print FILENAME; next}{print $4}' V1) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V2) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V3) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V4) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V5) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V6) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V7) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V8) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V9) \
<(awk 'NR==1{print FILENAME; next}{print $4}' V10) \

So executing script will run paste with the awk command extracting column 4 of each of the 500 input files. Using the two files of your example, that would produce:

$ printf 'paste ' > script
$ printf "<(awk 'NR==1{print FILENAME; next}{print \$4}' %s) \\\\\n" V* >> script
$ sh ./script
V1  V2
13  29
14  30
15  31
16  32
2
  • This will not scale. Paste will run into the limits of too many open file descriptors.
    – guest_7
    Apr 22 at 13:07
  • @guest_7 that's a fair point, yes. It should work for 500 files which is all the OP asked for though. I tested it with 500 on my system, at least, and it worked fine. 1000 or more files does indeed break it though.
    – terdon
    Apr 22 at 13:32
0

Using the re-shaper utility, rs, we can do it in a for loop. Assuming the V files are TAB separated.

for f in V{1..500};do
  awk -F '\t' '{$0=NR>1?$4:FILENAME}1' "$f" | paste -s -
done  | rs -c -C -T

cut+xargs+paste

##> temp dir and file open limit
tmp=$(mktemp -d)
LIM=$(echo "$(ulimit -n) 0.9*1/p"|dc)

##> get the 4th columns
for f in V{1..500};do
  {
  head -n 1 - > /dev/null
  cut -f 4 - > "$tmp/$f"
  } < "$f"
done

cd "$tmp"

##> paste the 4th columns
echo V{1..500} | tee log | paste -s -
< log xargs -r -n "$LIM" paste |
split -l "$(< V1 wc -l)"
paste x*

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.