Adding columns from 2 files with variable number of columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding columns from 2 files with variable number of columns
# 1  
Old 09-10-2018
Adding columns from 2 files with variable number of columns

I have two files, file1 and file2 who have identical number of rows and columns. However, the script is supposed to be used for for different files and I cannot know the format in advance. Also, the number of columns changes within the file, some rows have more and some less columns (they are shaped like an upper triangular matrix).,as below.
file1 :
Code:
11 12 13 14 15
21 22 23 24
31 32 33
41 42
51

file2:
Code:
61 62 63 64 65
71 72 73 74
81 82 83
91 92
101

I need to add the values of the elements in the same row and column of the two files and print them in the corresponding row/column. So I need to get
Code:
72 74 76 78 80
92 94 96 98
112 114 116
132 134
152

I've tried with:
Code:
paste file1 file2 > file3
awk '{for(i=1;i<(NF/2);i++) {x=$i+$(i+NF/2);print x}}' file3

but that is neither adding them up properly nor printing them properly. What would be a way to do add these element considering I'm dealing with large files and variable sizes?

Last edited by maya3; 09-10-2018 at 12:18 PM.. Reason: Type in the first post
# 2  
Old 09-10-2018
using your approach:
Code:
paste file1 file2 |\
 awk '{for(i=1;i<=NF/2;i++) printf("%s%s", $i+$((NF/2)+i), (i==NF/2)?ORS:OFS)}'


Last edited by vgersh99; 09-10-2018 at 12:18 PM..
This User Gave Thanks to vgersh99 For This Post:
# 3  
Old 09-10-2018
I've now tried this but it also doesn't add up the values properly nor print them in the right format. I suppose my approach isn't the way to go, it was simply the only thing I could think of doing. Is there a better approach to go at it?
# 4  
Old 09-10-2018
Quote:
Originally Posted by maya3
I've now tried this but it also doesn't add up the values properly nor print them in the right format. I suppose my approach isn't the way to go, it was simply the only thing I could think of doing. Is there a better approach to go at it?
strange... given you sample files and the script:
Code:
#!/bin/ksh

paste maya1.txt maya2.txt |\
 awk '{for(i=1;i<=NF/2;i++) printf("%s%s", $i+$((NF/2)+i), (i==NF/2)?ORS:OFS)}'

I get the desired result:
Code:
72 74 76 78 80
92 94 96 98
112 114 116
132 134
152

Can you run cat -vet on both files and post the corresponding output within code tags, please.
This User Gave Thanks to vgersh99 For This Post:
# 5  
Old 09-10-2018
Yes it does work, sorry, I've typed i<NF/2 instead of i<=NF/2.

May I ask, what does
Code:
(i==NF/2)?ORS:OFS

mean? I assume it means that when i is equal to half of the number of records it goes into newline? How does it exactly do that?

Last edited by maya3; 09-10-2018 at 01:29 PM..
# 6  
Old 09-10-2018
you can think of this as short hand for a long hand mnemonic of:
Code:
if (i==NF/2)
   output ORS
else
   output OFS

This User Gave Thanks to vgersh99 For This Post:
# 7  
Old 09-10-2018
Does the fact that I am printing it as string (printf and %s) make any problems with precision or format for later use? The entries in file1 and file2 can be either float or decimal and the precision is very important for the task, so I tried replacing %s with %d or %i to keep the output in the same format but that didn't give the result in a triangular form like it did with %s. Why is that?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

2. Shell Programming and Scripting

Adding info to end of line if two columns match from files with different separators

I have two files (csv and vcf) which look exactly like this S1.csv func,gene,start,info "exonic","AL","2309","het" "exonic","NEF","6912","hom"S1.vcf ##fileinfo #CHROM POS ID INFO chr1 4567 rs323211 1/1:84,104,99 chr4 2309 rs346742 1/1:27,213,90 chr6 5834 ... (5 Replies)
Discussion started by: Sarah_19
5 Replies

3. Shell Programming and Scripting

Adding columns with values dependent on existing columns

Hello I have a file as below chr1 start ref alt code1 code2 chr1 18884 C CAAAA 2 0 chr1 135419 TATACA T 2 0 chr1 332045 T TTG 0 2 chr1 453838 T TAC 2 0 chr1 567652 T TG 1 0 chr1 602541 ... (2 Replies)
Discussion started by: plumb_r
2 Replies

4. Shell Programming and Scripting

Adding Multiple Files via Columns

I have a number of files with multiple rows that I need to add together. Let say I have 10 files: Each file has a great number of rows and columns. I need to add these files together the following way. In other words, If, for example, file A occupies Columns 1 to 19, I want to add file B... (7 Replies)
Discussion started by: Ernst
7 Replies

5. UNIX for Dummies Questions & Answers

Adding new columns to txt files

Dear all, I have a question. I have a txt.file as below. i want to add 3 more columns: column3=conlum 2*column2; column4=(1-column2)*(1-column2); column5=1-column3-column4. Do you know how to do it? Thanks a lot! file: column1 column2 a 1 b 20 c 30 d 3 ... (2 Replies)
Discussion started by: forevertl
2 Replies

6. Shell Programming and Scripting

merging files and adding special columns

Hi everyone, I got a problem with merging files and hoped one of you would have an idea how to approach this issue. I tried it with awk, but didn't get far. This is what I have: I got 40 files looking like the ones below. All have three columns but the number of rows differs (20000 to 50000).... (6 Replies)
Discussion started by: TuAd
6 Replies

7. Shell Programming and Scripting

Adding columns of two files

Hello everyone, I have two files containing 6 columns and thousands of rows. I want to add them (i.e. first column of first file + first column of second file and so on) and print the output in a third file. Can you please help me. Thanks a lot (7 Replies)
Discussion started by: chandra321
7 Replies

8. Shell Programming and Scripting

awk adding columns from different files

Hi, I have two files and I need to add column 3 of file1 to column 3 of file 2 > file3 I also need to repeat for column 4. Thanks (1 Reply)
Discussion started by: dsstamps
1 Replies

9. Shell Programming and Scripting

comparing files - adding/subtracting/formating columns

I have two files: file1.txt: FS Total Used Free Used% /u01 10000 8000 2000 80% /u02 10000 8000 2000 80% /u03 10000 8000 2000 80% /u04 10000 8000 2000 80% /u05 10000 8000 2000 80% /u06 10000 8000 2000 80% /u07 10000 8000 2000 80% /u10 10000 5000 5000 50% file2.txt:... (7 Replies)
Discussion started by: oabdalla
7 Replies

10. Shell Programming and Scripting

Adding columns to excel files using Perl

How do I add 4 columns to an excel file using Perl? The 4 headers for those columns will all have different names? Please help and I greatly appreciate... (1 Reply)
Discussion started by: dolo21taf
1 Replies
Login or Register to Ask a Question