Solved

Perl - formate date and count

Posted on 2013-01-31
2
308 Views
Last Modified: 2013-01-31
I have some very long text files with lines such as:

1st column is the date:  2nd column is an id number

20120101: 549017
20120101: 339146
20120101: 535768
20120102: 535768
20120102: 541576
20120102: 292492
20120201: 442169
20120201: 438927
20120201: 574126
20120201: 592525
20120301: 361568
20120301: 535768
20120301: 535768
20120401: 535768
20120401: 535768

I'm trying to get a count of how many times each ID number was used per month, so can take the first 6 digits of the date to get the year/month and then match the ID's for each month with a count.

I'm getting part of what I need but not sure how to group the months in this way then do a count per ID with each month.

Need to get an output in a format such as:

ID             Month-Year  Times-Used
535768     Jan-2012       1322
695232     Feb-2012       3214
.......

Thanks for any help suggestions.
0
Comment
Question by:bt707
2 Comments
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 38842103
#!/usr/bin/perl
my %count;
/\b(\d{6})\d\d:\s*(\d+)/ && $count{$2}{$1}++ while <DATA>;
print "ID             Month-Year  Times-Used\n";
for my $id (sort keys %count ){
   for $mon (sort keys %{$count{$id}} ){
       printf"%-14d %s-%.4s    %d\n",$id,(qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec))[$mon%100-1],$mon,$count{$id}{$mon};
    }
}


__DATA__
20120101: 549017
    20120101: 339146
    20120101: 535768
    20120102: 535768
    20120102: 541576
    20120102: 292492
    20120201: 442169
    20120201: 438927
    20120201: 574126
    20120201: 592525
    20120301: 361568
    20120301: 535768
    20120301: 535768
    20120401: 535768
    20120401: 535768
0
 

Author Closing Comment

by:bt707
ID: 38842117
Thanks ozo, way better than what all I was doing.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question