Generate tabular data based on a column value from an existing data file Post: 302757647

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

01-17-2013

Registered User

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

himanish

View Public Profile for himanish

Find all posts by himanish

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. 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...

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. 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,...

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...

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....

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...

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...

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

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...

LEARN ABOUT CENTOS

zlaqr1.f

zlaqr1.f(3)							      LAPACK							       zlaqr1.f(3)

NAME

       zlaqr1.f -

SYNOPSIS

   Functions/Subroutines
       subroutine zlaqr1 (N, H, LDH, S1, S2, V)
	   ZLAQR1 sets a scalar multiple of the first column of the product of 2-by-2 or 3-by-3 matrix H and specified shifts.

Function/Subroutine Documentation
   subroutine zlaqr1 (integerN, complex*16, dimension( ldh, * )H, integerLDH, complex*16S1, complex*16S2, complex*16, dimension( * )V)
       ZLAQR1 sets a scalar multiple of the first column of the product of 2-by-2 or 3-by-3 matrix H and specified shifts.

       Purpose:

		 Given a 2-by-2 or 3-by-3 matrix H, ZLAQR1 sets v to a
		 scalar multiple of the first column of the product

		 (*)  K = (H - s1*I)*(H - s2*I)

		 scaling to avoid overflows and most underflows.

		 This is useful for starting double implicit shift bulges
		 in the QR algorithm.

       Parameters:
	   N

		     N is integer
			 Order of the matrix H. N must be either 2 or 3.

	   H

		     H is COMPLEX*16 array of dimension (LDH,N)
			 The 2-by-2 or 3-by-3 matrix H in (*).

	   LDH

		     LDH is integer
			 The leading dimension of H as declared in
			 the calling procedure.  LDH.GE.N

	   S1

		     S1 is COMPLEX*16

	   S2

		     S2 is COMPLEX*16

		     S1 and S2 are the shifts defining K in (*) above.

	   V

		     V is COMPLEX*16 array of dimension N
			 A scalar multiple of the first column of the
			 matrix K in (*).

       Author:
	   Univ. of Tennessee

	   Univ. of California Berkeley

	   Univ. of Colorado Denver

	   NAG Ltd.

       Date:
	   September 2012

       Contributors:
	   Karen Braman and Ralph Byers, Department of Mathematics, University of Kansas, USA

       Definition at line 108 of file zlaqr1.f.

Author
       Generated automatically by Doxygen for LAPACK from the source code.

Version 3.4.2							  Tue Sep 25 2012						       zlaqr1.f(3)

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

converting a tabular format data to comma seperated data in KSH

Discussion started by: Hemamalini

2. Shell Programming and Scripting

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

Discussion started by: patrick87

3. Shell Programming and Scripting

add more data to existing data in a file

Discussion started by: redse171

4. Shell Programming and Scripting

generate report based on data in files.

Discussion started by: ace_friends22