Shell Script – Adding a Column of Values in a Tab Delimited File

awklinuxshell-script

How can I add a Column of values in a file which has a certain number of rows.
I have a input file like this:

Input file:

SPATA17 1   217947738
LYPLAL1 1   219383905
FAM47E  4   77192838
SHROOM3 4   77660162
SHROOM3 4   77660731
SHROOM3 4   77662248

Output file:

SPATA17 1   217947738 file1
LYPLAL1 1   219383905 file1
FAM47E  4   77192838  file1
SHROOM3 4   77660162  file1
SHROOM3 4   77660731  file1
SHROOM3 4   77662248  file1

In this case, I want to add a Column of values, upto the number of rows in the file.The value remains consistent, such as "file1".

The reason is I have 100 of those files.I don't want to open each file and paste a column.
Also is there any way to automate this, by going in a directory and adding a column of values.
The value comes from the filename, which has to be added in each row of the file in the last/first column.

Best Answer

You can use a one-liner loop like this:

for f in file1 file2 file3; do sed -i "s/$/\t$f/" $f; done

For each file in the list, this will use sed to append to the end of each line a tab and the filename.

Explanation:

Using the -i flag with sed to perform a replacement in-place, overwriting the file
Perform a substitution with s/PATTERN/REPLACEMENT/. In this example PATTERN is $, the end of the line, and REPLACEMENT is \t (= a TAB), and $f is the filename, from the loop variable. The s/// command is within double-quotes so that the shell can expand variables.

Related Solutions

Shell – Merge some tab-delimited files

The following script ought to do an outer join on column (field) 1 of all the tab-delimited files passed as arguments. It uses the join command, which does an outer join on sorted files, 2 files at a time.

It will join every line in the files, including the header lines. If you want the headers to be excluded, change the two sort commands to something that produces a sorted file that omits them.

#!/bin/sh
if test $# -lt 2
then
    echo usage: gjoin file1 file2 ...
    exit 1
fi
sort -t $'\t' -k 1 "$1" > result
shift
for f in "$@"
do
    sort -t $'\t' -k 1 "$f" > temp
    join -1 1 -2 1 -t $'\t' result temp > newresult
    mv newresult result
done
cat result
rm result temp

If you have an older shell, $'\t' will not be replaced by a tab, so you'll need to use 'TAB', where you put a literal tab between the quotes.

Optimizations are possible if, instead of /bin/sh, you can use a modern shell such as bash or ksh; for instance, the lines

sort -t $'\t' -k 1 "$f" > temp
join -1 1 -2 1 -t $'\t' result temp > newresult

can be replaced by

join -1 1 -2 1 -t $'\t' result <(sort -t $'\t' -k 1 "$f") > newresult

Awk – Cumulative Sum of Values in Column with Same ID

Increment the count after printing the current line.

awk '{print $1, $2, sum[$1]; sum[$1] += $2}' file

This takes advantage of awk treating undefined variables as the empty string, or (in numeric context) as zero.

If you don't waant the incremental sum 0 printed, use

if ($2 != "") sum[$1] += $2

Best Answer

Related Solutions

Shell – Merge some tab-delimited files

Awk – Cumulative Sum of Values in Column with Same ID

Related Question