Concatenate column values when header is Matching from multiple files Post: 302982109

Sponsored Content

Top Forums UNIX for Beginners Questions & Answers Concatenate column values when header is Matching from multiple files Post 302982109 by RavinderSingh13 on Saturday 24th of September 2016 01:52:43 AM

09-24-2016

Moderator

Hello Nina,

So number of columns were not mentioned before so solution was according to it, so following may help you with dynamic number of columns but condition is Input_files should have same number of columns(based on row vice), if this condition is TRUE(which seems to be as per your post) then following may help you in same. Let's say following are the Input_files.

Code:

cat a.txt
Name 9/1 9/2
X 1 7 7 8 9 10 12 12 12 12 12 13
y 2 8 3 4 5 6 7 8 8 9 1 212 12 12
z 3 9 1 1 2
a 4 10 1
b 5 11
c 6 12 12 13 14

cat b.txt
Name 9/1 9/2
X 1 7 7 8 9 10 12 12 12 12 12 13
y 22 4 13 2 53 26 17 18 82 19 111 12 13 4
z 3 9 1 1 2
a 4 10 1
b 5 11
c 6 12 12 13 14

cat c.txt
Name 9/1 9/2
X 1 7 7 8 9 10 12 12 12 12 12 13
y 2 8 3 4 5 6 7 8 8 9 1 212 12 12
z 3 9 1 1 2
a 4 10 1
b 5 11
c 6 12 12 13 14

Then following is the code for same.

Code:

paste *.txt | awk 'function get(field){q=NF/3;for(i=field;i<=NF;i+=q){$field=i>field?$field "/" $i:$field;}} NR>1{for(j=2;j<=NF/3;j++){get(j)};for(j=NF/3+1;j<=NF;j++){$j=""};sub(/[[:space:]]+$/,X,$0);print}'

Output will be as follows.

Code:

X 1/1/1 7/7/7 7/7/7 8/8/8 9/9/9 10/10/10 12/12/12 12/12/12 12/12/12 12/12/12 12/12/12 13/13/13                          
y 2/22/2 8/4/8 3/13/3 4/2/4 5/53/5 6/26/6 7/17/7 8/18/8 8/82/8 9/19/9 1/111/1 212/12/212 12/13/12 12/4/12                              
z 3/3/3 9/9/9 1/1/1 1/1/1 2/2/2            
a 4/4/4 10/10/10 1/1/1        
b 5/5/5 11/11/11      
c 6/6/6 12/12/12 12/12/12 13/13/13 14/14/14

Also in above code wherever I have used NF/3 so it is because of 3 Input_files are there if they vary in numbers then you could change according to their number. Kindly do let me know if this helps you or if you have any issues on same.
EDIT: Adding a non-one liner form of solution too here.

Code:

paste *.txt | awk 'function get(field){
					q=NF/3;
					for(i=field;i<=NF;i+=q){
								$field=i>field?$field "/" $i:$field;
							       }
                                      } 
                   NR>1               {
					for(j=2;j<=NF/3;j++)   {
								get(j)
                                                               };
					for(j=NF/3+1;j<=NF;j++){
								$j=""
							       };
                                        sub(/[[:space:]]+$/,X,$0);
					print
 				      }
                  '

Thanks,
R. Singh

Last edited by RavinderSingh13; 09-24-2016 at 03:06 AM.. Reason: Adding a non-one liner form of solution on same too succcessfully now.

This User Gave Thanks to RavinderSingh13 For This Post:

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Joining multiple files based on one column with different and similar values (shell or perl)

Hi, I have nine files looking similar to file1 & file2 below. File1: 1 ABCA1 1 ABCC8 1 ABR:N 1 ACACB 1 ACAP2 1 ACOT1 1 ACSBG 1 ACTR1 1 ACTRT 1 ADAMT 1 AEN:N 1 AKAP1File2: 1 A4GAL 1 ACTBL 1 ACTL7

2. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256...

3. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

4. UNIX for Dummies Questions & Answers

shift values in one column as header for values in another column

Hi Gurus, I have a tab separated text file with two columns. I would like to make the first column values as headings for the second column values. Ex. >value1 subjects >value2 priorities >value3 requirements ...etc and I want to have a file >value1 subjects >value2 priorities...

5. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use...

6. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines...

7. Shell Programming and Scripting

Sum column values matching other field

this is part of a KT i am going thru. i am writing a script in bash shell, linux where i have 2 columns where 1st signifies the nth hour like 00, 01, 02...23 and 2nd the file size. sample data attached. Desired output is 3 columns which will give the nth hour, number of entries in nth hour and...

8. Shell Programming and Scripting

Extracting values based on line-column numbers from multiple text files

Dear All, I have to solve the following problems with multiple tab-separated text file but I don't know how. Any help would be greatly appreciated. I have access to Linux mint (but not as a professional). I have multiple tab-delimited files with the following structure: file1: 1 44 2 ...

9. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

I have a file (myfile.txt) with contents like this: 1.txt apple is 3.txt apple is 5.txt apple is 2.txt apple is a 7.txt apple is a 8.txt apple is a fruit 4.txt orange not a fruit 6.txt zero isThe above file is already sorted using this command: sort -k2 myfile.txtMy objective is to get...

10. Shell Programming and Scripting

Comparing same column from two files, printing whole row with matching values

First I'd like to apologize if I opened a thread which is already open somewhere. I did a bit of searching but could quite find what I was looking for, so I will try to explaing what I need. I'm writing a script on our server, got to a point where I have two files with results. Example: File1...

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Joining multiple files based on one column with different and similar values (shell or perl)

Discussion started by: seqbiologist

2. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Discussion started by: Vavad

3. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Discussion started by: Vavad

4. UNIX for Dummies Questions & Answers

shift values in one column as header for values in another column

Discussion started by: Unilearn

5. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

Discussion started by: Santoshbn

6. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

Discussion started by: Bastami

7. Shell Programming and Scripting

Sum column values matching other field

Discussion started by: alpha_1

8. Shell Programming and Scripting

Extracting values based on line-column numbers from multiple text files

Discussion started by: Bastami

9. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

Discussion started by: shoaibjameel123

10. Shell Programming and Scripting

Comparing same column from two files, printing whole row with matching values

Discussion started by: mitabrev83