Solved

Perl - formate date and count

Posted on 2013-01-31
2
304 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now