Sponsored Content
Top Forums Shell Programming and Scripting Pivoting Dynamic rows into columns Post 302302870 by dprakash on Wednesday 1st of April 2009 08:10:27 AM
Old 04-01-2009
Pivoting Dynamic rows into columns

Original file we are getting .......
Item Period Amt
P1 106 1000
P1 206 1500
P1 106 2000
P2 256 5800
P2 650 7500
My output should be like this
Item 106 206 256 650 ............
P1 1000 1500 0 0 ............
P1 2000 0 0 0 ...........
P2 0 0 5800 7500 ...............
...........
...........
................
Hi all
my requirement is quite tricky . ..its very urgent...
i want to convert 1st file into as 2nd file........
i want to convert Period col values into columns and amount should be placed w.r.t Period value
If corresponding amount is not available for Period then 0 should be enter for that period...
and Items can be dynamically change.... means it can come P3,P4 etc.......
Thanks
Prakash
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Columns to rows

I have a file which has values seperated by ~ as shown below and I have to achieve a column to row from this. Incoming row is like this How do i get the occueance of ~ and then split it into rows as and below is what I have to achieve. Thanks (16 Replies)
Discussion started by: mgirinath
16 Replies

2. Shell Programming and Scripting

Bash: Reading out rows of a file into a dynamic array and check first literal

Hello, i have a file "Movie.ini" looking e.g. like follows * MOVIE A bla bla MOVIE B blubb blubb MOVIE C I'd like to read the file "Movie.ini" with cat and grep and check whether it includes the string MOVIE only with a '*' at the beginnig. By doing "cat Movie.ini| grep MOVIE... (14 Replies)
Discussion started by: ABE2202
14 Replies

3. Shell Programming and Scripting

Rows into columns?

I have a file thats space delimited that looks something like this: Joe Smith jsmith 43234 bill1;bill2;read;read2;schedule Andy Summers asummers 11232 bill1;read Beth McConnel bmconnel 43443 read;read2;schedule;bill Susan Fowler sfowler 09332 bill1;read;schedule I need to... (8 Replies)
Discussion started by: regexnub
8 Replies

4. Shell Programming and Scripting

Transposing rows and columns (pivoting) using shell scripting

Here is the contents of an input file. A,1,2,3,4 10,aaa,bbb,ccc,ddd 11,eee,fff,ggg,hhh 12,iii,jjj,lll,mmm 13,nnn,ooo,ppp I wanted the output to be A 10 1 aaa 10 2 bbb 10 3 ccc 10 4 ddd 11 1 eee 11 2 fff 11 3 ggg 11 4 hhh ..... and so on How to do it in ksh... (9 Replies)
Discussion started by: ksatish89
9 Replies

5. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

6. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

7. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

8. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

9. UNIX for Beginners Questions & Answers

Pivoting values from column to rows

I/P: I/P: 2017/01/01 a 10 2017/01/01 b 20 2017/01/01 c 40 2017/02/01 a 10 2017/02/01 b 20 2017/02/01 c 30 O/P: a b c 2017/01/01 10 20 40 2017/02/01 10 20 30 (18 Replies)
Discussion started by: Booo
18 Replies

10. UNIX for Beginners Questions & Answers

Columns to rows

HI UNIX Gurus, Not sure if this was already asked and an UNIX Guru has replied but I could not find what i wanted. I have linux environment and need help on this. I have several files like this. a,1 b,1 utc,10/12/2019 local,10/12/2018 name,xxxy deg,feh 10,12 20,8 30,50 32,64 46,65... (5 Replies)
Discussion started by: Roopensingh
5 Replies
Period(3pm)						User Contributed Perl Documentation					       Period(3pm)

NAME
Time::Period - A Perl module to deal with time periods. SYNOPSIS
"use Time::Period;" "$result = inPeriod($time, $period);" DESCRIPTION
The inPeriod function determines if a given time falls within a given period. inPeriod returns 1 if the time does fall within the given period, 0 if not, and -1 if inPeriod detects a malformed time or period. The time is specified as per the "time()" function, which is assumed to be the number of non-leap seconds since January 1, 1970. The period is specified as a string which adheres to the format sub-period[, sub-period...] or the string "none" or whitespace. The string "none" is not case sensitive. If the period is blank, then any time period is assumed because the time period has not been restricted. In that case, inPeriod returns 1. If the period is "none", then no time period applies and inPeriod returns 0. A sub-period is of the form scale {range [range ...]} [scale {range [range ...]}] Scale must be one of nine different scales (or their equivalent codes): Scale | Scale | Valid Range Values | Code | *******|*******|************************************************ year | yr | n where n is an integer 0<=n<=99 or n>=1970 month | mo | 1-12 or jan, feb, mar, apr, may, jun, jul, | | aug, sep, oct, nov, dec week | wk | 1-6 yday | yd | 1-365 mday | md | 1-31 wday | wd | 1-7 or su, mo, tu, we, th, fr, sa hour | hr | 0-23 or 12am 1am-11am 12noon 12pm 1pm-11pm minute | min | 0-59 second | sec | 0-59 The same scale type may be specified multiple times. Additional scales simply extend the range defined by previous scales of the same type. The range for a given scale must be a valid value in the form of v or v-v For the range specification v-v, if the second value is larger than the first value, the range wraps around unless the scale specification is year. Year does not wrap because the year is never really reset, it just increments. Ignoring that fact has lead to the dreaded year 2000 night- mare. When the year rolls over from 99 to 00, it has really rolled over a century, not gone back a century. inPeriod supports the danger- ous two digit year notation because it is so rampant. However, inPeriod converts the two digit notation to four digits by prepending the first two digits from the current year. In the case of 99-1972, the 99 is translated to whatever current century it is (probably 20th), and then range 99-1972 is treated as 1972-1999. If it were the 21st century, then the range would be 1972-2099. Anyway, if v-v is 9-2 and the scale is month, September, October, November, December, January, and February are the months that the range specifies. If v-v is 2-9, then the valid months are February, March, April, May, Jun, July, August, and September. 9-2 is the same as Sep-Feb. v isn't a point in time. In the context of the hour scale, 9 specifies the time period from 9:00:00 am to 9:59:59 am. This is what most people would call 9-10. In other words, v is discrete in its time scale. 9 changes to 10 when 9:59:59 changes to 10:00:00, but it is 9 from 9:00:00 to 9:59:59. Just before 9:00:00, v was 8. Note that whitespace can be anywhere and case is not important. Note also that scales must be specified either in long form (year, month, week, etc.) or in code form (yr, mo, wk, etc.). Scale forms may be mixed in a period statement. Furthermore, when using letters to specify ranges, only the first two for week days or the first three for months are significant. January is a valid specification for jan, and Sunday is a valid specification for su. Sun is also valid for su. PERIOD EXAMPLES To specify a time period from Monday through Friday, 9am to 5pm, use a period such as wd {Mon-Fri} hr {9am-4pm} When specifing a range by using -, it is best to think of - as meaning through. It is 9am through 4pm, which is just before 5pm. To specify a time period from Monday through Friday, 9am to 5pm on Monday, Wednesday, and Friday, and 9am to 3pm on Tuesday and Thursday, use a period such as wd {Mon Wed Fri} hr {9am-4pm}, wd{Tue Thu} hr {9am-2pm} To specify a time period that extends Mon-Fri 9am-5pm, but alternates weeks in a month, use a period such as wk {1 3 5} wd {Mon Wed Fri} hr {9am-4pm} Or how about a period that specifies winter? mo {Nov-Feb} This is equivalent to the previous example: mo {Jan-Feb Nov-Dec} As is mo {jan feb nov dec} And this is too: mo {Jan Feb}, mo {Nov Dec} Wait! So is this: mo {Jan Feb} mo {Nov Dec} To specify a period that describes every other half-hour, use something like minute { 0-29 } To specify the morning, use hour { 12am-11am } Remember, 11am is not 11:00:00am, but rather 11:00:00am - 11:59:59am. Hmmmm, 5 second blocks could be a fun period... sec {0-4 10-14 20-24 30-34 40-44 50-54} To specify every first half-hour on alternating week days, and the second half-hour the rest of the week, use the period wd {1 3 5 7} min {0-29}, wd {2 4 6} min {30-59} VERSION
1.20 HISTORY
Version 1.20 ------------ - Added the ability to specify no time period. Version 1.13 ------------ - Cleaned up the error checking code. Version 1.12 ------------ - Updated email and web space information. Version 1.11 ------------ - Minor bug fix in 1.10. Version 1.10 ------------ - Released. AUTHOR
Patrick Ryan <pgryan@geocities.com> COPYRIGHT
Copyright (c) 1997 Patrick Ryan. All rights reserved. This Perl module uses the conditions given by Perl. This module may only be dis- tributed and or modified under the conditions given by Perl. DATE
August 26, 1997 SOURCE
This distribution can be found at http://www.geocities.com/SiliconValley/Lakes/8456/ or http://www.perl.com/CPAN/modules/by-module/Time/ perl v5.8.8 1998-02-05 Period(3pm)
All times are GMT -4. The time now is 01:36 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy