Adding a Column of values in a tab delimited file

Question

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.

Solution 1

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.

Solution 2

Come on why you guys recommend those powerful tools when there's paste command!

$ cat a
A
B
C
D
$ cat b
1
2
3
4
$ paste a b
A   1
B   2
C   3
D   4

With a little trickery, you could use paste for the OP's purpose. However, it will not replace the files inplace:

for f in file1 file2 file3; do 
    paste $f <(yes $f | head -n $(cat $f | wc -l)) > $f.new
done

This will paste the respective filename as the last column of each file into new file filename.new

Solution 3

You can use awk:

awk '{print $0, FILENAME}' file1 file2 file3 ...

Comments

[cuonglm]

No, FILENAME is a variable in awk, it expand to current file name that awk is processing. You just do it one, feed all files to awk.

[Ron]

ok,but how to direct the output into a new file, of each file?does awk stores the each file while processing?

[cuonglm]

If you have GNU awk 4.1.0 or later, you can use -i to edit inplace. Otherwise, you should redirect awk ouput to a temp file, then use grep to extract line from each files.

[Ron]

The code works.Can you explain the contents within quotes?

[Ron]

Just as "awk" is used while working with columns, is 'sed' also used for similar situations.I am newbie to 'awk' and 'sed'.

[janos]

@Ron sed is most practical for pattern substitution and saving in-place. For your requirement of saving the file it was a relatively convenient option. If you don't need to write back to the same file you're processing, then awk is usually much easier to work with.

[fedorqui]

Well you can do for file in *; do awk 'BEGIN{OFS="\t"}{print $0, FILENAME}' $file; done

[user5359531]

Personally, I get tripped up by awk's input/output field separators too often, and so try to avoid using it whenever possible, making sed more appealing.

[fachexot]

Thanks! paste is surely a hidden gem.