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
# 8  
Old 11-18-2010
Try this one and will see
Code:
awk 'END{for(i=0;++i<=l;)print a[i]}!NF{l=(_>l)?_:l;_=0}NF{_++;a[_]=((a[_])?a[_]FS:x)$4}'  file

# 9  
Old 11-18-2010
Copy the testawk.awk from the solution: https://www.unix.com/shell-programmin...transpose.html
Code:
$ cat testawk.awk

{
for (i=1;i<=NF;i++)
{
 arr[NR,i]=$i;
 if(big <= NF)
  big=NF;
 }
}
 
END {
  for(i=1;i<=big;i++)
   {
    for(j=1;j<=NR;j++)
    {
     printf("%s\t",arr[j,i]);
    }
    printf("\n");
   }
}

Code:
awk 'BEGIN{RS=""}{for (i=4;i<=NF;i=i+5) printf $i FS;printf ORS}' infile  |awk -f testawk.awk

516414  378715  367817  383585
586868  597981  579270  599585
474937  468922  474098

---------- Post updated at 11:01 AM ---------- Previous update was at 10:54 AM ----------

@ 丹
Quote:
Originally Posted by danmero
Try this one and will see
Code:
awk 'END{for(i=0;++i<=l;)print a[i]}!NF{l=(_>l)?_:l;_=0}NF{_++;a[_]=((a[_])?a[_]FS:x)$4}'  file

Because the number of records will vary from time to time!!
Code:
$ cat infile
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     128   4  597981 1684006
11-17-2010:13:32     256   4  468922 1673001

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

My code:

Code:
awk 'BEGIN{RS=""}{for (i=4;i<=NF;i=i+5) printf $i FS;printf ORS}' infile  |awk -f testawk.awk

516414  378715  468922  367817  383585
586868  597981          579270  599585
474937  597981          474098
        468922


Your code:

Code:
awk '!NF{l=(_>l)?_:l;_=0}NF{_++;a[_]=((a[_])?a[_]FS:x)$4}END{for(i=0;++i<=l;)print a[i]}' infile
516414 378715 468922 367817 383585
586868 597981 579270 599585
474937 597981 474098
468922

You know the difference now. Smilie
# 10  
Old 11-18-2010
Quote:
Originally Posted by rdcwayx
You know the difference now. Smilie
Yes .. but I don't get the point Smilie ... late in the night.

Another one ,
Code:
awk '{a[$2]=((a[$2])?a[$2] FS:x)$4;c=(c>$2)?c:$2}END{for(i=0;i++<=c;) if(a[i])print a[i]}' file

# 11  
Old 11-18-2010
Thanks a ton Danmero and rdcwayx for looking at this. Danmero, the columns are still getting messed up when I use your solution Smilie Thanks for the try though!!
Rdcwayx your solution is perfect!!!! Thank you!! I had 16 columns in my data file so I updated the loop accordingly. There is still a slight modification I need to do. Is there a way to add zeros for rows that have no value?
# 12  
Old 11-18-2010
Code:
awk '
BEGIN{RS=""} 
NR==FNR{max=(NF>max)?NF:max;next} 
{for (i=4;i<=max;i=i+5) printf ($i=="")?"000000" FS:$i FS;printf ORS}' infile infile |awk -f testawk.awk

516414  378715  468922  367817  383585
586868  597981  000000  579270  599585
474937  597981  000000  474098  000000
000000  468922  000000  000000  000000

This User Gave Thanks to rdcwayx For This Post:
annazpereira
# 13  
Old 11-18-2010
Wohoooooooooo!!! It works it works!!! Thankyou rdcwayx. I have been struggling with this all day. I needed this data to be formatted this way so that it can be used to draw a wallchart using gnuplot.
Thankyou very very much everyone for your inputs!!!
# 14  
Old 11-18-2010
Code:
n=$(awk '!/^$/{print$4}' infile | wc -l) ; awk '!/^$/{print$4}' infile | eval split -l $((1+$n/4)) - myF ; paste myF* | tee output

Code:
[ctsgnb@shell ~]$ n=$(awk '!/^$/{print$4}' infile | wc -l) ; awk '!/^$/{print$4}' infile | eval split -l $((1+$n/4)) - myF ; paste myF* | tee output
516414  378715  367817  383585
586868  597981  579270  599585
474937  468922  474098
[ctsgnb@shell ~]$ cat output
516414  378715  367817  383585
586868  597981  579270  599585
474937  468922  474098
[ctsgnb@shell ~]$ rm myF*
[ctsgnb@shell ~]$
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