counting occurences of pattern from an input file to match against a huge file

Question

I have a list of colleges in a text file, and in a separate file I have a list of publications with affiliation. I want to write a script, to check, how many times a publication is repeated and to count the number of times universities has collaborated. My data is below; "p1" is the title of the paper, "Affiliation" are the colleges involved to published it

Example:-

Data

UID, Affiliation

p1    "ADPRI, S"
p1    "ADPRI, S"
p2    "ADPRI, S"
p2    "AAC&S, H"
p3    "AAC&S, H"
p3    "HU, USA" 
p3    "Penn, USA"
p4    "AAC&S, H"  
p5    "AAC&S, H"  
p6    "AAC&S, H"  
p7    "AAC&S, H"  
p8    "AU, A"  
p9    "AECI, A"  
p10   "AECI, A" 
p10   "AECI, A" 

In the above data, paper "p2" is linked to "ADPRI, S" and "AAC&S, H".
Similarly "p3" is linked to colleges "AAC&S, H", "HU, USA", "Penn, USA".
So my script should give a file where the count of number of collaboration is given between two colleges. For above data it will be

Desired output:

 College_A       College_B       Collaborated
  ADPRI, S       AAC&S, H            2
  HU, USA        Penn, USA           1
  ....
  ....
 so on for all the colleges,

**I used sort and uniq command on "column 2", to get the number of colleges, which is list of 797 colleges and my database has more than 20000 papers published by those colleges. My data also has lot of white spaces and special characters. **

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

Answer 1

gawk solution.

Usage: ./program.awk input.txt

Also, you can do: ./program.awk input.txt | column -t -s $'\t' for the pretty displaying, if the alignment is lost.

#!/usr/bin/awk -f

function pub_to_aff() {
    for(i in pub_arr) {
        for(j in pub_arr) {
            if(i != j)
                aff_arr[i][j]++;    
        }   
    }   
    delete pub_arr;
}

BEGIN {
    OFS = "\t";
    FS = "\t";
}

$1 != prev_uid {
    prev_uid = $1; 
    pub_to_aff();
}
{
    pub_arr[$2] = 1;
}

END {
    pub_to_aff();
    print "College_A", "College_B", "Collaborated";

    for(i in aff_arr) {
        for(j in aff_arr[i]) {
            print i, j, aff_arr[i][j];          
        }   
    }   
}

Input - two lines were added for demonstration - to p3 and p4.

p1  "ADPRI, S"
p1  "ADPRI, S"
p2  "ADPRI, S"
p2  "AAC&S, H"
p3  "AAC&S, H"
p3  "ADPRI, S"
p3  "HU, USA"
p3  "Penn, USA"
p4  "AAC&S, H"
p4  "ADPRI, S"
p5  "AAC&S, H"
p6  "AAC&S, H"
p7  "AAC&S, H"
p8  "AU, A"
p9  "AECI, A"
p10 "AECI, A"
p10 "AECI, A"

Output

College_A   College_B   Collaborated
"AAC&S, H"  "HU, USA"   1
"AAC&S, H"  "Penn, USA" 1
"AAC&S, H"  "ADPRI, S"  3
"HU, USA"   "AAC&S, H"  1
"HU, USA"   "Penn, USA" 1
"HU, USA"   "ADPRI, S"  1
"Penn, USA" "AAC&S, H"  1
"Penn, USA" "HU, USA"   1
"Penn, USA" "ADPRI, S"  1
"ADPRI, S"  "AAC&S, H"  3
"ADPRI, S"  "HU, USA"   1
"ADPRI, S"  "Penn, USA" 1

---

EDIT - real data testing.

Input - I left only part of your sample.txt content and changed couple of lines for demonstration of script working. Note, if the input file doesn't contain collaborated universities, the script will output the one line only - header.

WOS:000355337800046 "ACHARYA NARENDRA DEV COLL, NEW DELHI"
WOS:000355337800046 "ACHARYA NARENDRA DEV COLL, NEW DELHI"
WOS:000355337800046 "ACHARYA PRAFULLA CHANDRA COLL. KOLKATA"
WOS:000328700900001 "ACHARYA PRAFULLA CHANDRA COLL. KOLKATA"
WOS:000338233800012 "ADAMAS INST TECHNOL, KOLKATA"
WOS:000338233800012 "ADARSH MAHAVIDYALAYA DHAMANGAON RAILWAY, AMRAVATI"
WOS:000349637600009 "ADARSH MAHAVIDYALAYA DHAMANGAON RAILWAY, AMRAVATI"
WOS:000314892400031 "ADITYA INST TECHNOL & MANAGEMENT, TEKKALI"

Used command: ./program.awk sample.txt | column -t -s $'\t'

Output

College_A                                            College_B                                            Collaborated
"ADAMAS INST TECHNOL, KOLKATA"                       "ADARSH MAHAVIDYALAYA DHAMANGAON RAILWAY, AMRAVATI"  1
"ACHARYA NARENDRA DEV COLL, NEW DELHI"               "ACHARYA PRAFULLA CHANDRA COLL. KOLKATA"             1
"ACHARYA PRAFULLA CHANDRA COLL. KOLKATA"             "ACHARYA NARENDRA DEV COLL, NEW DELHI"               1
"ADARSH MAHAVIDYALAYA DHAMANGAON RAILWAY, AMRAVATI"  "ADAMAS INST TECHNOL, KOLKATA"                       1

Answer 2

With Perl:

#!/usr/bin/env perl

use strict;
use warnings;

use List::MoreUtils qw(uniq);
use Set::Intersection;

my ( %papers, @colleges );

while (<>) {          
    chomp; 
    my ( $paper, $college ) = m/(\S+)\t"(.+)"/g;

    # normalize college names
    $college =~ s/\s\+/ /go;
    $college =~ s/^\s\+//go;
    $college =~ s/\s\+$//go;

    $papers{$college} //= [];
    push @{ $papers{$college} }, $paper;
}

@colleges = sort keys %papers;
for my $college (@colleges) {
    $papers{$college} = [ uniq sort @{ $papers{$college} } ];
}

print qq(College_A\tCollege_B\tCollaborated\n);
for ( my $i = 0 ; $i < @colleges - 1 ; $i++ ) {
    for ( my $j = $i + 1 ; $j < @colleges ; $j++ ) {
        my $collaborations = scalar get_intersection(
            { -preordered => 1 },
            $papers{ $colleges[$i] },
            $papers{ $colleges[$j] }
        );  
        print $colleges[$i], "\t", $colleges[$j], "\t", $collaborations, "\n"
          if ($collaborations);
    }
}

With Python:

#!/usr/bin/env python

from __future__ import print_function

import re
import sys
from collections import defaultdict

papers = defaultdict(lambda: set())
for line in sys.stdin:
    paper, college = line.split("\t")
    college = re.sub(r'^"|"$', '', college)
    college = re.sub(r'\s+', ' ', college)
    college = re.sub(r'^\s+|\s+$', '', college)
    papers[college].add(paper)

colleges = sorted(papers.keys())

print("College_A\tCollege_B\tCollaborated")
for i in range(len(colleges) - 1):
    for j in range(i + 1, len(colleges)):
        collaborations = len(papers[colleges[i]].intersection(papers[colleges[j]]))
        if collaborations:
            print("%s\t%s\t%d" % (colleges[i], colleges[j], collaborations))