Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Splitting data from one row as multiple columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting data from one row as multiple columns
# 15  
Old 11-18-2010
All in one call
Code:
# awk 'END{Y=(Y<y)?y:Y;m=1;while(m<x){for(n=0;n++<Y;){printf((a[m,n])?a[m,n]:z)FS}print((a[m,n])?a[m,n]:z);m++}}!NF{Y=(Y<y)?y:Y;x++;y=0}NF{a[++y,x]=$4}' x=1 z=000000 file
516414 378715 468922 367817 383585
586868 597981 000000 579270 599585
474937 597981 000000 474098 000000
000000 468922 000000 000000 000000

@rdcwayx you surprise me today Smilie
# 16  
Old 11-19-2010
Or you could use Perl -

Code:
$
$
$ cat f41
11-17-2010:13:26     64    4  516414 1392258
11-17-2010:13:26     128   4  586868  695603
11-17-2010:13:26     256   4  474937 1642294
 
11-17-2010:13:32     64    4  378715 1357066
11-17-2010:13:32     128   4  597981 1684006
11-17-2010:13:32     256   4  468922 1673001
 
11-17-2010:13:36     64    4  367817 1553394
11-17-2010:13:36     128   4  579270 1526043
11-17-2010:13:36     256   4  474098 1629830
 
11-17-2010:13:37     64    4  383585 1492919
11-17-2010:13:37     256   4  599585 1036624
$
$
$
$ perl -ane 'BEGIN {@x=([])}
 if (!/^\s*$/){
   if ($. == 1 or $F[0] eq $prev) {push @{ $x[$#x] }, $F[3]}
   else {push @x, [$F[3]]}
   $prev = $F[0];
 }
 END {
   for $i (0..$#x) {$max = $#{$x[$i]} > $max ? $#{$x[$i]} : $max}
   for $i (0..$max) {
     for $j (0..$#x) {print ${$x[$j]}[$i],"\t"}
     print "\n";
   }
 }' f41
516414  378715  367817  383585
586868  597981  579270  599585
474937  468922  474098
$
$
$

tyler_durden

---------- Post updated at 10:19 PM ---------- Previous update was at 05:20 PM ----------

And a variant of the script for the same data file -

Code:
$
$
$ perl -ane '
 if (!/^\s*$/){
   if ($F[0] eq $prev) {push @{$x[$#x]},$F[3]}
   else {push @x,[$F[3]]}
   $max = $#{$x[$#x]} // $max;
   $prev = $F[0];
 }
 END {
   for $i (0..$max+1) {
     for $j (0..$#x) {print ${$x[$j]}[$i],"\t"}
     print "\n";
   }
 }' f4
516414  378715  367817  383585
586868  597981  579270  599585
474937  468922  474098
$
$
$

tyler_durden
# 17  
Old 11-26-2010
Code:
awk '{print$4}' infile | egrep -ve '^[:blank:]*$' | pr -t -4 -

# 18  
Old 11-30-2010
perl
Code:
my $cnt;
my $max;
while(<DATA>){
  my @tmp  =split;
  if(exists $hash{$tmp[0]}){
     $hash{$tmp[0]}->{++$cnt}=$tmp[3];
  }
  else{
    $max=($cnt>$max)?$cnt:$max;
    $cnt=0;
    $hash{$tmp[0]}->{0}=$tmp[3];
    $hash{$tmp[0]}->{-1}=$.;
  }
}
$max=($cnt>$max)?$cnt:$max;
for(my $i=0;$i<$max;$i++){
foreach my $key(sort {$hash{$a}->{-1} <=> $hash{$b}->{-1}} keys %hash){
 print $hash{$key}->{$i}," ";
}
print "\n";
}
__DATA__
11-17-2010:13:26     64    4  516414 1392258
11-17-2010:13:26     128   4  586868  695603
11-17-2010:13:26     256   4  474937 1642294

11-17-2010:13:32     64    4  378715 1357066
11-17-2010:13:32     128   4  597981 1684006
11-17-2010:13:32     256   4  468922 1673001

11-17-2010:13:36     64    4  367817 1553394
11-17-2010:13:36     128   4  579270 1526043
11-17-2010:13:36     256   4  474098 1629830

11-17-2010:13:37     64    4  383585 1492919
11-17-2010:13:37     64    4  383585 1492919
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Splitting single row into multiple rows based on for every 10 digits of last field of the row

Hi ALL, We have requirement in a file, i have multiple rows. Example below: Input file rows 01,1,102319,0,0,70,26,U,1,331,000000113200000011920000001212 01,1,102319,0,1,80,20,U,1,241,00000059420000006021 I need my output file should be as mentioned below. Last field should split for... (4 Replies)
Discussion started by: kotra
4 Replies

2. UNIX for Dummies Questions & Answers

Splitting up a text file into multiple files by columns

Hi, I have a space delimited text file with multiple columns 102 columns. I want to break it up into 100 files labelled 1.txt through 100.txt (n.txt). Each text file will contain the first two columns and in addition the nth column (that corresponds to n.txt). The third file will contain the... (1 Reply)
Discussion started by: evelibertine
1 Replies

3. Shell Programming and Scripting

Splitting the data in a column into several columns

Hi, I have the following input file 32895901-d17f-414c-ac93-3e7e0f5ec240 AND @GDF_INPUT 73b129e1-1fa9-4c0d-b95b-4682e5389612 AUS @GDF_INPUT 40f82e88-d1ff-4ce2-9b8e-d827ddb39447 BEL @GDF_INPUT 36e9c3f1-042a-43a4-a80e-4a3bc2513d01 BGR @GDF_INPUT I want to split column 3 into two columns:... (1 Reply)
Discussion started by: ramky79
1 Replies

4. Shell Programming and Scripting

splitting a huge line of file into multiple lines with fixed number of columns

Hi, I have a huge file with a single line. But I want to break that line into lines of with each line having five columns. My file is like this: code: "hi","there","how","are","you?","It","was","great","working","with","you.","hope","to","work","you." I want it like this: code:... (1 Reply)
Discussion started by: rajsharma
1 Replies

5. Shell Programming and Scripting

Format row data into columns

I have a file which looks like this: /* ----------------- EDW$MOC139_R_NNA_BR_SUM_FACT2 ----------------- */ insert_job: EDW$MOC139_R_NNA_BR_SUM_FACT2 job_type: c command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh m machine: edwprod02.dsm.pwj.com #owner:... (29 Replies)
Discussion started by: Gangadhar Reddy
29 Replies

6. Shell Programming and Scripting

Help converting row data to columns

I've been trying to figure this out for a while but I'm completely stumped. I have files with data in rows and I need to convert the data to columns. Each record contains four rows with a "field name: value" pair. I would like to convert it to four columns with the field names as column headers... (5 Replies)
Discussion started by: happy_ee
5 Replies

7. Shell Programming and Scripting

Search multiple columns in each row

Hi All, I have to search in multiple columns for multiple values, if the match is found then print the values as below. Eg: cat t1 Z|VLD_AB_P|VLD_CD_P|VLD_EF_F|VLD_GH_F|100 Y|VLD_AB_F|VLD_CD_F|VLD_EF_P|VLD_GH_P|101 if then print "Invalid AB in $6" if then print "Invalid CD in... (6 Replies)
Discussion started by: gsjdrr
6 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

How to insert data befor some field in a row of data depending up on values in row

Hi I need to do some thing like "find and insert before that " in a file which contains many records. This will be clear with the following example. The original data record should be some thing like this 60119827 RTMS_LOCATION_CDR INSTANT_POSITION_QUERY 1236574686123083rtmssrv7 ... (8 Replies)
Discussion started by: aemunathan
8 Replies

10. Shell Programming and Scripting

Transpose - Multiple row to Multiple columns

I have seen many posts to transpose rows into columns, but not for multiple rows to columns..so please help me how to do this.. I have around 1000 rows and 1000 columns, i want to transpose it.. A1 A2 A3 B1 B2 B3 C1 C2 C3 D1 D2 D3 I want output to be like A1 B1 C1 D1 A2 B2 C2 D2... (5 Replies)
Discussion started by: ganeshss
5 Replies
Login or Register to Ask a Question