Sponsored Content
Top Forums Shell Programming and Scripting Generate tabular data based on a column value from an existing data file Post 302757647 by himanish on Thursday 17th of January 2013 10:27:51 PM
Old 01-17-2013
Generate tabular data based on a column value from an existing data file

Hi,

I have a data file with :
Code:
01/28/2012,1,1,98995
01/28/2012,1,2,7195
01/29/2012,1,1,98995
01/29/2012,1,2,7195
01/30/2012,1,1,98896
01/30/2012,1,2,7083
01/31/2012,1,1,98896
01/31/2012,1,2,7083
02/01/2012,1,1,98896
02/01/2012,1,2,7083
02/02/2012,1,1,98899
02/02/2012,1,2,7083


I need to find the first occurring date and the last occurring date for a particular value in column 4 for each combination of column 2 and 3

For example the output from above data should be:
Code:
1,1,01/28/2012,01/29/2012,98955
1,1,01/30/2012,02/01/2012,98896
1,1,02/02/2012,99/99/9999,98899
1,2,01/28/2012,01/29/2012,7195
1,2,01/30/2012,99/99/9999,7083

because the value (column 4)=98955 for (column 2)=1 and (column 3)=1 first occurs on date 01/28/2012 and last occurs on date 01/29/2012, after which on 01/30/2012 it changes to 98896,so a new row is added.When a value is the last value in the file, the last occurance date is 99/99/9999.

Not sure whether i could make it clear but is unix shell scripting(with awk maybe) the best way to achieve this or does this need something like perl ?

Last edited by Franklin52; 01-18-2013 at 04:01 AM.. Reason: Please use code tags for data and code samples
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

converting a tabular format data to comma seperated data in KSH

Hi, Could anyone help me in changing a tabular format output to comma seperated file pls in K-sh. Its very urgent. E.g : username empid ------------------------ sri 123 to username,empid sri,123 Thanks, Hema:confused: (2 Replies)
Discussion started by: Hemamalini
2 Replies

2. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies

3. Shell Programming and Scripting

add more data to existing data in a file

Hi all, I need help to add additional data from file2 to existing data in file 1 using awk, sed or perl. the ID in file 1 should match against field $3 in file2 file1 #this is a new game ID HR_1 BASE1 30 BASE2 37 DETAIL No TYPE L @@ ID HR_10 BASE1 6030 BASE2 ... (4 Replies)
Discussion started by: redse171
4 Replies

4. Shell Programming and Scripting

generate report based on data in files.

Hi All, I need to develop a shell script which does sanity check of a data file, as below. 1. For DATE columns, it should check if date is given in proper format or not? For example, if format of date is expected as DD-MON-YYYY HH24:MI:SS and we received the date in formation like DDMMYYYY HH24,... (1 Reply)
Discussion started by: ace_friends22
1 Replies

5. Ubuntu

How to add a data column in existing file

Hi All I need to add a column on my existing data file. I know similar posts are there but none of them were meeting my requirement. My input is 1.20 3.44 4.88 5.11 4.99 3.22 1.89 3.89 2.90 Desired output 1 1.20 3.44 4.88 2 5.11 4.99 3.22 3 1.89 3.89 2.90 I will... (2 Replies)
Discussion started by: mahbub03
2 Replies

6. Shell Programming and Scripting

Script for extracting data from csv file based on column values.

Hi all, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (3 Replies)
Discussion started by: Vivekit82
3 Replies

7. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

8. Shell Programming and Scripting

Generate Join clause based on key data

Hi, I have a file pk.txt which has pk data in following format TableName | PK Employee | id Contact|name,country My Output should be Employee | t1.id=s.id Contact| t1.name=s.name AND t1.country=s.country I started of like this: for LIST in `cat pk.txt` do... (5 Replies)
Discussion started by: wahi80
5 Replies

9. Shell Programming and Scripting

Help with add existing file name as new data column in new output file

Input File 1 cat S1.txt MI0043 2731 miR-1 Input File 2 cat S4.txt MI006 310 CiR-1 MI057 10 CiR-24 MI750 5 CiR-24 Desired Output File 1 cat S1.txt.out MI0043 2731 miR-1 S1.txt Desired Output File 2 cat S4.txt.out MI006 310 CiR-1 S4.txt (3 Replies)
Discussion started by: perl_beginner
3 Replies

10. Shell Programming and Scripting

In PErl script: need to read the data one file and generate multiple files based on the data

We have the data looks like below in a log file. I want to generat files based on the string between two hash(#) symbol like below Source: #ext1#test1.tale2 drop #ext1#test11.tale21 drop #ext1#test123.tale21 drop #ext2#test1.tale21 drop #ext2#test12.tale21 drop #ext3#test11.tale21 drop... (5 Replies)
Discussion started by: Sanjeev G
5 Replies
MooseX::Types::ISO8601(3pm)				User Contributed Perl Documentation			       MooseX::Types::ISO8601(3pm)

NAME
MooseX::Types::ISO8601 - ISO8601 date and duration string type constraints and coercions for Moose SYNOPSIS
use MooseX::Types::ISO8601 qw/ ISO8601TimeDurationStr /; has duration => ( isa => ISO8601TimeDurationStr, is => 'ro', coerce => 1, ); Class->new( duration => 60 ); # 60s => PT00H01M00S Class->new( duration => DateTime::Duration->new(%args) ) DESCRIPTION
This module packages several TypeConstraints with coercions for working with ISO8601 date strings and the DateTime suite of objects. DATE CONSTRAINTS
ISO8601DateStr An ISO8601 date string. E.g. "2009-06-11" ISO8601TimeStr An ISO8601 time string. E.g. "12:06:34Z" ISO8601DateTimeStr An ISO8601 combined datetime string. E.g. "2009-06-11T12:06:34Z" ISO8601DateTimeTZStr An ISO8601 combined datetime string with a fully specified timezone. E.g. "2009-06-11T12:06:34+00:00" COERCIONS The date types will coerce from: " Num " The number is treated as a time in seconds since the unix epoch " DateTime " The duration represented as a DateTime object. " Str " Non-expanded date and time string representations. e.g.:- 20120113 => 2012-01-13 170500Z => 17:05:00Z 20120113T170500Z => 2012-01-13T17:05:00Z Representations of UTC time zone (only an offset of zero is supported) e.g.:- 17:05:00+00:00 => 17:05:00Z 17:05:00+00 => 17:05:00Z 170500+0000 => 17:05:00Z 2012-01-13T17:05:00+00:00 => 2012-01-13T17:05:00Z 2012-01-13T17:05:00+00 => 2012-01-13T17:05:00Z 20120113T170500+0000 => 2012-01-13T17:05:00Z Also supports non-standards mixing of expanded and non-expanded representations e.g.:- 2012-01-13T170500Z => 2012-01-13T17:05:00Z 20120113T17:05:00Z => 2012-01-13T17:05:00Z DURATION CONSTRAINTS
ISO8601DateDurationStr An ISO8601 date duration string. E.g. "P01Y01M01D" ISO8601TimeDurationStr An ISO8601 time duration string. E.g. "PT01H01M01S" ISO8601DateTimeDurationStr An ISO8601 comboined date and time duration string. E.g. "P01Y01M01DT01H01M01S" COERCIONS The duration types will coerce from: " Num " The number is treated as a time in seconds " DateTime::Duration " The duration represented as a DateTime::Duration object. The duration types will coerce to: " Duration " A DateTime::Duration, i.e. the " Duration " constraint from MooseX::Types::DateTime. SEE ALSO
o MooseX::Types::DateTime o DateTime o DateTime::Duration o DateTime::Format::Duration VERSION CONTROL
http://github.com/bobtfish/moosex-types-iso8601/tree/master Patches are welcome. SEE ALSO
o http://en.wikipedia.org/wiki/ISO_8601 o http://dotat.at/tmp/ISO_8601-2004_E.pdf FEATURES
Fractional seconds If provided, the number of seconds in time types is represented to microsecond accuracy. A full stop character is used as the decimal seperator, which is allowed, but deprecated in preference to the comma character in ISO 8601:2004. BUGS
Probably full of them, patches are very welcome. Specifically missing features: o No timezone support - all times are assumed UTC o No week number type o "Basic format", which lacks seperator characters, is not supported for reading or writing. o Tests are rubbish. AUTHOR
Tomas Doran (t0m) "<bobtfish@bobtfish.net>" Dave Lambley "<davel@state51.co.uk>" The development of this code was sponsored by my employer <http://www.state51.co.uk>. Contributors Aaron Moses COPYRIGHT
Copyright (c) 2009 Tomas Doran. Some rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.14.2 2012-05-11 MooseX::Types::ISO8601(3pm)
All times are GMT -4. The time now is 06:30 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy