1

I have a file which has the patterns that I have to find in the given data set and add the numbers in a columns of the data. I want to write a script or use awk/sed/grep to automatically query the patterns in a given file to cross check with the data I have and to add the numbers in the columns with matching pattern.

Example:-

Data

TITLE,COUNT,RESA

10th ECOMF,PAB  1   MAT

10th ICOCPS (CPV-'10)   23  SCI

10th ICOCPS (CPV-'10)   6   SCI

10th ICOMAMOS (M2S-X)   3   EEE

JOP 32  COP

AIP 34  Sff

JOP 43  COP

HIT 42  tilde

My pattern file consists names of the journals:-

10th ECOMF,PAB

10th ICOCPS (CPV-'10)

10th ICOCPS (CPV-'10)

10th ICOMAMOS (M2S-X)

JOP

AIP

JOP

HIT

SUPERCONDUCTIVITY (M2S-X)

Desired output:

10th ECOMF,PAB  1

10th ICOCPS (CPV-'10)   29

10th ICOMAMOS (M2S-X)   3

JOP 75

AIP 34

HIT 42

.

.

so on,

PS:- Data is Tab-separated, I same data in CSV too.

Improve this question
2
  • Is your pattern file tab separated? 10th ICOCPS (CPV-'10) is this one column?? Commented Aug 8, 2017 at 5:02
  • @user3138373 Yes, it is one column. File is in tab-separated values. :: Kamaraj, Thanks for the edit. Commented Aug 8, 2017 at 11:39

1 Answer 1

Reset to default
2

awk solution:

awk 'BEGIN{ FS=OFS="\t" }
     NR==FNR{ if(NF && !($0 in b)) { a[++c]=$0; b[$0] } next }{ b[$1]+=$2 }
     END{ len=length(a); for(i=1;i<=len;i++) if(a[i] in b) print a[i],b[a[i]] }' patterns data

  • BEGIN{ FS=OFS="\t" } - setting field separator

  • a[++c]=$0; b[$0] - collecting unique journal names from patterns file

  • b[$1]+=$2 - summing up the numbers for each journal name within data file

  • if(a[i] in b) print a[i],b[a[i]] - append sum value to each journal name in the pattern file

The output:

10th ECOMF,PAB  1
10th ICOCPS (CPV-'10)   29
10th ICOMAMOS (M2S-X)   3
JOP 75
AIP 34
HIT 42
...
Improve this answer
2
  • Thank you, it works perfectly. I upvoted the answer, but it says -"vote cast by those with less than 15 reputations are recorded, but do not change the publicly displayed score " Commented Aug 8, 2017 at 11:38
  • 1
    @CCC, you have my upvote to your question Commented Aug 8, 2017 at 12:34

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.