Code:
$
$ cat inputfile.csv
VALUE,USER1,relatedUSER1,relatedUSER2
-1,userA,userB,userC
1,userN,userD,userB
0,userF,userH,userG
0,userT,userH,userB
1,userN,userB,userA
-1,userA,userF,userC
0,userF,userH,userU
1,userB,userJ,userU
-1,userJ,userD,userA
-1,userA,userB,userC
-1,userN,userD,userB
0,userN,userH,userG
$
$ cat -n script.pl
1 #!/usr/bin/perl -w
2 # ----------------------------------------------------------------------------------------------------------
3 # Note that the hash %mtx has keys that look like this: "X,Y", where X is USER1 in the input file and Y
4 # is either relatedUSER1 or relatedUSER2. Each key value is a pointer to an array that looks like this:
5 # [ n1, n2, n3, n4 ]
6 # Here, n1 = relationship count, n2 = +ve relation count, n3 = neutral relation count and
7 # n4 = -ve relation count
8 # ----------------------------------------------------------------------------------------------------------
9 use strict;
10 my %mtx; # a hash for storing the matrix information
11 # the header line
12 my $header = "SUPERUSER,relatedUSER,TOTFrequencyrelations,(1)Frequency,(0)Frequency,(-1)Frequency,VALUE";
13 my $file = $ARGV[0]; # first command-line arg is the file name
14 open(FH, "<", $file) or die "Can't open $file: $!"; # try opening it, die if unsuccessful
15 while (<FH>) { # loop through file
16 next if $. == 1; # skip the first line
17 chomp; # remove the EOL character
18 my ($n, $u, $x, $y) = split/,/; # tokenize the line; assign local variables
19 $mtx{"$u,$x"}->[0]++; # tokens (2,3) and (2,4) are the keys
20 $mtx{"$u,$y"}->[0]++; # increment their relation counts
21 if ($n > 0) { $mtx{"$u,$x"}->[1]++; $mtx{"$u,$y"}->[1]++ } # increment the counts of +ve/0/-ve relations
22 elsif ($n < 0) { $mtx{"$u,$x"}->[3]++; $mtx{"$u,$y"}->[3]++ } # To get a better idea of what the hash %mtx
23 else { $mtx{"$u,$x"}->[2]++; $mtx{"$u,$y"}->[2]++ } # looks like, try Data::Dumper after file close
24 } # done looping
25 close (FH) or die "Can't close $file: $!"; # close the file or die if unsuccessful
26 print $header, "\n"; # start with printing the header first
27 for my $k (sort keys %mtx) { # sort the hash keys and loop through them
28 my $pos = $mtx{$k}->[1] // 0; # if a relation count is undefined
29 my $zro = $mtx{$k}->[2] // 0; # or non-existent
30 my $ngt = $mtx{$k}->[3] // 0; # set it to 0
31 my $val = ($pos + $ngt) == 0 ? 0 : ($pos - $ngt)/($pos + $ngt); # derive the value at the farthest position
32 print "$k,$mtx{$k}->[0],$pos,$zro,$ngt,$val\n"; # and print everything
33 } # we're done
$
$ perl script.pl inputfile.csv
SUPERUSER,relatedUSER,TOTFrequencyrelations,(1)Frequency,(0)Frequency,(-1)Frequency,VALUE
userA,userB,2,0,0,2,-1
userA,userC,3,0,0,3,-1
userA,userF,1,0,0,1,-1
userB,userJ,1,1,0,0,1
userB,userU,1,1,0,0,1
userF,userG,1,0,1,0,0
userF,userH,2,0,2,0,0
userF,userU,1,0,1,0,0
userJ,userA,1,0,0,1,-1
userJ,userD,1,0,0,1,-1
userN,userA,1,1,0,0,1
userN,userB,3,2,0,1,0.333333333333333
userN,userD,2,1,0,1,0
userN,userG,1,0,1,0,0
userN,userH,1,0,1,0,0
userT,userB,1,0,1,0,0
userT,userH,1,0,1,0,0
$
$