Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Perl - formate date and count

Posted on 2013-01-31
2
Medium Priority
?
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 84

Accepted Solution

by:
ozo earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Six Sigma Control Plans

721 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