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.
p1
has duplicated affiliation, why?