2

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.

  • 2
    p1 has duplicated affiliation, why? – RomanPerekhrest Nov 2 '17 at 16:54
  • That means, there are two authors from the same university/college. – CCC Nov 2 '17 at 18:02
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
  • Thank you it, works for the above data, but with minor glitches. For example:- in output it displays "Penn " instead of "Pen, USA", similarly for others, after comma country is missing. If I apply to my entire dataset only two lines are there in the output. – CCC Nov 6 '17 at 2:37
  • @CCC Does the "Penn, USA" string have the tab between "Penn, and USA"? I was assumed, that it is a space and the tab is only between p3 and "Penn, USA". Because the p3 is the first column / field and the "Penn, USA" is the second column / field. It should not have tabs inside the column string - tabs only for columns delimiting. – MiniMax Nov 6 '17 at 13:06
  • 1
    @CCC But the one tab should be in between two first columns - WOS:000337466200077 and "WMO ARTS.... Does it present there? Is your file separated by tabs? You can place part of your file (20-30 lines) to the any share service, like google drive, and put the link here. Then our discussion will be more productive. – MiniMax Nov 8 '17 at 10:30
  • 1
    @CCC I tested the script with your sample.txt and it works as expected. But sample.txt doesn't contain any collaborated universities, so the script outputs nothing, except the header line. I changed some lines, to simulate collaboration and the script output these universities. See the EDIT section of my answer. – MiniMax Nov 8 '17 at 14:23
  • 1
    @CCC Try to add a few collaborated universities into your original sample.txt by yourself, not copied from here. Don't change any formatting. Does it works? I did the same and it worked in my case. Why it doesn't work in your case, I don't have assumptions. – MiniMax Nov 9 '17 at 10:50
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))
  • I did perl code.pl but it says Can't locate Set/Intersection.pm in @INC (you may need to install the Set::Intersection module), I am doing that now, but how to give my files as input here? – CCC Nov 2 '17 at 19:01
  • 1
    For Perl: script.pl affiliations.txt, or script.pl <affiliations.txt. For Python: script.py <affiliations.txt. – Satō Katsura Nov 2 '17 at 19:06
  • Thank you, works like a charm. Will you find time to explain it? Also, can the same be done using awk/sed/grep? – CCC Nov 2 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.