Perl, group, sort, count question

I have a file (data.txt) of tab separated data which contains eight columns per line. I need to return results similar to a pivot table in excel. Where the results are grouped by two columns (col3 and col6) with a count of each occurrence of the distinct values from column 3.

~~~~~~~~~~~~~~~~~    sample data  from data.txt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~
08f0.39b7.4531 100.236.140.244  R5/0/0/D4     r-online(pt)      7070  1   Ki5/0/0:0  576
001d.d07a.bbf2 101.236.133.97   R5/0/0/D1     r-online(pt)      1801  1   Ki5/0/0:1  577
5239.5540.fbc7 100.236.133.130  R5/0/0/D4     online(pt)      3543  1   Ki5/0/0:0 586
105f.4cd8.a723 101.236.128.196  R5/0/0/D4     online(pt)      1753  2   Ki5/0/0:1 589
125f.4952.4e85 101.235.250.181  R5/0/0/D4     r-online(pt)      1729  2   Ki5/0/0:2  577
7cb2.1004.35f6 102.236.166.155  R5/0/0/D1     online      1795  1   Ki5/0/0:0  585
105f.4955.dde9 101.237.185.45   R5/0/0/D4     r-online(pt)      1368  1   Ki5/0/0:2 591
102f.4955.d891 100.236.141.73   R5/0/0/D1     online(pt)      1786  2   Ki5/0/0:1  584
0880.39d8.578d 109.235.251.182  R5/0/0/D1     r-online(pt)      1816  2   Ki5/0/0:2  583
c8ff.2637.6122 108.236.117.231  R5/0/0/D1     online      1659  1   Ki5/0/0:2  582
cffb.2635.80d6 102.235.251.44   R5/0/0/D1     r-online(pt)      690   0   Ki5/0/0:0 588
~~~~~~~~~~~~~~~~~~~~~~~~  end sample data   ~~~~~~~~~~~~~~~~~~~~~~~~~


The expected results would be as follows:
FYI, The format of the results is not that important so here are three examples:


~~~~~~~~~~~~~~~~~~~~~ sample results ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ki5/0/0:0      
online 1      
online(pt) 1      
r-online(pt) 2

Ki5/0/0:1      
online 0      
online(pt) 2      
r-online(pt) 0

Ki5/0/0:2      
online 1      
online(pt) 0      
r-online(pt) 3

or this:

Ki5/0/0:0 online 1
Ki5/0/0:0 online(pt) 1
Ki5/0/0:0 r-online(pt) 2
Ki5/0/0:1 online 0
Ki5/0/0:1 online(pt) 2
Ki5/0/0:1 r-online(pt) 0
Ki5/0/0:2 online 1
Ki5/0/0:2 online(pt) 0
Ki5/0/0:2 r-online(pt) 3

or this:

Ki5/0/0:0      Ki5/0/0:1      Ki5/0/0:2
online 1      online 0      online 1
online(pt) 1      online(pt) 2      online(pt) 0
r-online(pt) 2      r-online(pt) 0      r-online(pt) 3
~~~~~~~~~~~~~~~~~~~~~~~ end sample results ~~~~~~~~~~~~~~~~~~~~~~


FWIW I experimented with reading the file into an array like below and trying to manipulate the results but never found anything that worked as expected:

~~~~~~~~~~~~~~~~~~~~~~~~ sample read file into an array ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#!/usr/bin/perl

use strict;
use warnings;

my $col0;
my $col1;
my $col2;
my $col3;
my $col4;
my $col5;
my $col6;
my $col7;

while ( my $line = <> ) {
      @data = ($col0, $col1, $col2, $col3, $col4, $col5, $col6, $col7) = split (/\s+/, $line); {
        #process data here
        #print results here
        }
       
}

~~~~~~~~~~~~~~~~~~~~~~~~ end sample read file into an array ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
LVL 1
1 LinePerlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FishMongerCommented:
#!usr/bin/perl

use strict;
use warnings;
use Data::Dumper;

my %data;

while (<DATA>) {
    my ($col3, $col5, $col6) = (split /\s+/)[3,5,6];
    $data{$col6}{$col3} += $col5;
}
print Dumper \%data;


__DATA__
08f0.39b7.4531 100.236.140.244  R5/0/0/D4     r-online(pt)      7070  1   Ki5/0/0:0  576
001d.d07a.bbf2 101.236.133.97   R5/0/0/D1     r-online(pt)      1801  1   Ki5/0/0:1  577
5239.5540.fbc7 100.236.133.130  R5/0/0/D4     online(pt)      3543  1   Ki5/0/0:0 586
105f.4cd8.a723 101.236.128.196  R5/0/0/D4     online(pt)      1753  2   Ki5/0/0:1 589
125f.4952.4e85 101.235.250.181  R5/0/0/D4     r-online(pt)      1729  2   Ki5/0/0:2  577
7cb2.1004.35f6 102.236.166.155  R5/0/0/D1     online      1795  1   Ki5/0/0:0  585
105f.4955.dde9 101.237.185.45   R5/0/0/D4     r-online(pt)      1368  1   Ki5/0/0:2 591
102f.4955.d891 100.236.141.73   R5/0/0/D1     online(pt)      1786  2   Ki5/0/0:1  584
0880.39d8.578d 109.235.251.182  R5/0/0/D1     r-online(pt)      1816  2   Ki5/0/0:2  583
c8ff.2637.6122 108.236.117.231  R5/0/0/D1     online      1659  1   Ki5/0/0:2  582
cffb.2635.80d6 102.235.251.44   R5/0/0/D1     r-online(pt)      690   0   Ki5/0/0:0 588

Open in new window


Outputs:
$VAR1 = {
          'Ki5/0/0:1' => {
                           'r-online(pt)' => 1,
                           'online(pt)' => 4
                         },
          'Ki5/0/0:0' => {
                           'online(pt)' => 1,
                           'online' => 1,
                           'r-online(pt)' => 1
                         },
          'Ki5/0/0:2' => {
                           'online' => 1,
                           'r-online(pt)' => 5
                         }
        };

I just dumped out the hash.  You could/should loop over the data and output it in your desired format.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1 LinePerlAuthor Commented:
Perfect FishMonger, I had played with data::dumper but could never get the snytax correct, thanks
1 LinePerlAuthor Commented:
Exactly what I was looking for, thanks FishMonger
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

1 LinePerlAuthor Commented:
Actually I was a little overzealous before actually doing some verification. The counts are actually not correct.

for K15/0/0:1, your code returns 'r-online(pt)' => 1, 'online(pt)' => 4

BUT

it should return 'r-online(pt)' => 1, 'online(pt)' => 2


Can you tell me why you are using col5 in your code, we do not want or need any information from col5, we are only interested in col3 and col6, maybe thats what is creating the discrepancy.
FishMongerCommented:
I wasn't sure which field you wanted use to calculate the numbers.

This adjustment should get the results you want.
    my ($col3, $col6) = (split /\s+/)[3,6];
    $data{$col6}{$col3}++;

Open in new window

1 LinePerlAuthor Commented:
perfect, thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.