Add a column from one file as last column to another file

Question

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!

Answer 1

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.

Answer 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

Answer 3

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

Answer 4

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*