Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

If the 1th column of file f1 and file f2 is the same, then export those line with maximum string of

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting If the 1th column of file f1 and file f2 is the same, then export those line with maximum string of
# 1  
Old 04-26-2018
If the 1th column of file f1 and file f2 is the same, then export those line with maximum string of
please help to write a awk command-line programs to achieve the following functions: Thank in advance.

Requeset Description:
compare two files f1 and f2, export to file f3:
1 Delete duplicate rows of in file f1 and file f2
2 If the 1th column of file f1 and file f2 is the same, then export those line with maximum string of 2nd column.
for example:
Code:
  
 0.1-37    < 0.2-53;   
 6.1.4-b.0 < 6.1.5-c.2;   
 9.13.2    < 11.5.6;    
 18b-16    > 8c-7;   
 D15       < F4;   
 1.b5_a    < 1.b12_d   
 4c5.8     < 4c12.8   
 d18g      < d18j

3 Rule: For the 2nd column of 2 files:
> num of 0-9 consecutive occurrences may be different, such as 9.13.2 vs 11.5.6, D15 vs F4
> The type, order, num of other characters (such as '.' '_' '-' 'A-Z' 'a-Z') except 0-9 is the same.
like 6.1.4-b.0 vs 6.1.5-c.2, 1.b5_a vs 1.b12_d, D15 vs F4 ....
> if find the 1st large string after comparison, then stop comparing the 2nd column, and output this line of those file,
such as 'IO 1.b5_a' of f1, 'IO 1.b12_d' of f2, will output 'IO 1.b12_d'

4 cat f1:
Code:
 PK      0.1-37  
 Art     6.1.4-b.0  
 Fle     9.13.2     
 Uni     18b-16   
 STD     D15   
 IO      1.b5_a  
 FPG     4c5.8 
 SRA     d18g 
 .... 
 ....

cat f2:
Code:
 Uni     8c-7 
 IO      1.b12_d 
 Art     6.1.5-c.2 
 PK      0.2-53 
 Fle     11.5.6 
 SRA     d18j 
 STD     F4 
 FPG     4c12.8 
 .... 
 ....

desired file f3:
Code:
 Art     6.1.5-c.2 
 Fle     11.5.6 
 IO      1.b12_d 
 PK      0.2-53 
 STD     F4 
 Uni     18b-16   
 FPG     4c12.8 
 SRA     d18j 
 ... 
 ...

# 2  
Old 04-26-2018
Code:
awk '
NR==FNR {a[$1]=$2; next}
{ if (length(a[$1])) {
      line1="";
      c=split($2, a1, "[^0-9A-Za-z]");

      for (i=1; i<=c; i++)  {
         t=u=a1[i];
         gsub("[0-9]", " ", t); gsub("[A-Za-z]", " ", u);
         d=split(t, a2); e=split(u, a3);
         if (a1[i] ~ /^[0-9]/) {
            for (j=1; j<=e; j++) line1=line1 (sprintf("%5s",  a3[j]));
            for (j=1; j<=d; j++) line1=line1 (sprintf("%5s",  a2[j]));
         } else {
            for (j=1; j<=d; j++) line1=line1 (sprintf("%5s",  a2[j]));
            for (j=1; j<=e; j++) line1=line1 (sprintf("%5s",  a3[j]));
         }
      }

      line2="";
      c=split(a[$1], a1, "[^0-9A-Za-z]");

      for (i=1; i<=c; i++)  {
         t=u=a1[i];
         gsub("[0-9]", " ", t); gsub("[A-Za-z]", " ", u);
         d=split(t, a2); e=split(u, a3);
         if (a1[i] ~ /^[0-9]/) {
            for (j=1; j<=e; j++) line2=line2 (sprintf("%5s",  a3[j]));
            for (j=1; j<=d; j++) line2=line2 (sprintf("%5s",  a2[j]));
         } else {
            for (j=1; j<=d; j++) line2=line2 (sprintf("%5s",  a2[j]));
            for (j=1; j<=e; j++) line2=line2 (sprintf("%5s",  a3[j]));
         }
      }
      if (line1 > line2 ) {print $1, $2} else {print $1, a[$1]}
   } else {
      print $1, $2;
   }
   delete a[$1];
}
END {
   for (i in a) if (length(a[i])) print i, a[i];
}
' OFS="\t" f1 f2

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

2. Shell Programming and Scripting

How to export the string to a text file.

Hi, I have used this find . -type f -exec grep "491629461544" {} \; > test.txt to find the string and export it to file. The problem is, when i used the above command in Shell script, the file is created but the searched string is recursively written in that file and even the file... (10 Replies)
Discussion started by: nanthagopal
10 Replies

3. Shell Programming and Scripting

AWK, Perl or Shell? Unique strings and their maximum values from 3 column data file

I have a file containing data like so: 2012-01-02 GREEN 4 2012-01-02 GREEN 6 2012-01-02 GREEN 7 2012-01-02 BLUE 4 2012-01-02 BLUE 3 2012-01-02 GREEN 4 2012-01-02 RED 4 2012-01-02 RED 8 2012-01-02 GREEN 4 2012-01-02 YELLOW 5 2012-01-02 YELLOW 2 I can't always predict what the... (4 Replies)
Discussion started by: rich@ardz
4 Replies

4. Shell Programming and Scripting

search a string in a particular column of file and return the line number of the line

Hi All, Can you please guide me to search a string in a particular column of file and return the line number of the line where it was found using awk. As an example : abc.txt 7000,john,2,1,0,1,6 7001,elen,2,2,0,1,7 7002,sami,2,3,0,1,6 7003,mike,1,4,0,2,1 8001,nike,1,5,0,1,8... (3 Replies)
Discussion started by: arunshankar.c
3 Replies

5. Shell Programming and Scripting

Replace 2nd column for each line in a csv file with fixed string+random number

Hi experts, My csv file looks like this U;cake;michael;temp;;;; U;bread;john;temp;;;; U;cocktails;sarah;temp;;;; I'd like to change the value fo 2nd column to cf+random number , which will look maybe something like this U;cf20187;michael;temp;;;; U;cf8926;john;temp;;;;... (7 Replies)
Discussion started by: tententen
7 Replies

6. Shell Programming and Scripting

Reformatting single column text file starting new line when finding particular string

Hi, I have a single colum file and I need to reformat the file so that it creates a new line every time it come to an IP address and the following lines are corresponding rows until it comes to the next IP address. I want to turn this 172.xx.xx.xx gwpusprdrp02_pv seinwnprd03... (7 Replies)
Discussion started by: kieranfoley
7 Replies

7. Shell Programming and Scripting

Match a line in File 1 with Column in File 2 and print whole line in file 2 when matched

Hi Experts, I am very new to scripting and have a prb since few days and it is urgent to solve so much appreciated if u help me. i have 2 files file1.txt 9647810043118 9647810043126 9647810043155 9647810043161 9647810043166 9647810043185 9647810043200 9647810043203 9647810043250... (22 Replies)
Discussion started by: mustafa.abdulsa
22 Replies

8. Shell Programming and Scripting

replace (sed?) a single line/string in file with multiple lines (string) from another file??

Can someone tell me how I can do this? e.g: Say file1.txt contains: today is monday the 22 of NOVEMBER 2010 and file2.txt contains: the 11th month of How do i replace the word NOVEMBER with (5 Replies)
Discussion started by: tuathan
5 Replies

9. Shell Programming and Scripting

Need to convert the content of file into COLUMN (To export into excel)

I have multiple condition in file as below. MONITOR "ALERT_INFO" DESCRIPTION "Triggered when informational Netware alert occured" MAXTHRESHOLD 95 SEVERITY Normal MONITOR "ALERT_MAJOR" DESCRIPTION "Triggered when major Netware alert occured" MAXTHRESHOLD SEVERITY Major I need to... (6 Replies)
Discussion started by: velocitnitin
6 Replies

10. UNIX for Advanced & Expert Users

Print the line containing the maximum value in a column

Dear all! I want to find the maximum value in two specific columns with numbers, and then print the entire line containing this value. The file may look like: 1001 34.5 68.7 67 1002 22.0 40.1 32 1003 11.3 34.8 45 I want to find the maximum value within column 2... (22 Replies)
Discussion started by: kingkong
22 Replies
Login or Register to Ask a Question