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
# 1  
Old 11-18-2010
Splitting data from one row as multiple columns
Hi

I have a file containing some data as follows:
HTML Code:
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
I need to use awk to print out the values in column 4 as separate columns for each distict value in column 1 as follows:
HTML Code:
516414  378715 367817 383585
586868  597981 579270 599585
474937  468922 474098
I tried to store the distinct values of column1 in a variable and use that in a loop to store the values in an array to be printed later, but I seem to be doing something majorly wrong.
HTML Code:
a=`awk '{print $1}' 4.txt | sort -u`
j=0
for i in ${a[@]}
do 
j=`expr $j + 1`
val[j]=`awk -v dat=\`echo $i\` '$1=i {print $4}' data.txt`; echo $j
done
Can someone help me?
Thanks
# 2  
Old 11-18-2010
Code:
awk '{print$4}' infile | egrep -ve '^[:blank:]*$' | xargs -n4 echo

# 3  
Old 11-18-2010
Thanks for the quick reply Cstgnb. I guess I was not too clear in what I needed. The script you provided converts the desired output column to a row. So instead of getting:
516414 378715 367817 383585
586868 597981 579270 599585
474937 468922 474098
I get:

HTML Code:
516414 586868 474937 378715
597981 468922 367817 579270
474098 383585 402510 599585
385237 601330 484801 374488
574312 481109 392561 584313
Also I cannot use 'xargs -n4' as I need to print out all the values in column 4 (that have a matching column 1 value) as separate columns.

Thanks

---------- Post updated at 06:05 PM ---------- Previous update was at 05:46 PM ----------

Another way to look at it is to be able to print sets of values from the 4th column as a new columns if there exists a row of blank spaces.
Last edited by annazpereira; 11-18-2010 at 07:13 PM..
# 4  
Old 11-18-2010
Lame solution Smilie
Code:
$ for i in 1 5 9 13 2 6 10 14 3 7 11; do awk -v var=$i 'NR==var {print $4}' FILE; done | xargs -n4 echo
516414 378715 367817 383585
586868 597981 579270 599585
474937 468922 474098

# 5  
Old 11-18-2010
Thanks Cabrao, but I cannot use NR to separate the values since the number of records will vary from time to time!!
# 6  
Old 11-18-2010
Code:
# cat file
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
# awk '{_=NR%4}_{a[_]=((a[_])?a[_]FS:x)$4;l=(_>l)?_:l}END{for(i=0;++i<=l;)print a[i]}'  file
516414 378715 367817 383585
586868 597981 579270 599585
474937 468922 474098

# 7  
Old 11-18-2010
Wow Danmero, you are very very close to the solution, against my data file I get :
HTML Code:
516414   474098 402510 385237
586868 378715   599585 601330
474937 597981 367817 484801
So I get values from other rows in column 4 mixed in too. I don't quite understand your solution.
{_=NR%4}_{a[_]=((a[_])?a[_]FS:x)$4;l=(_>l)?_:l} What exactly is this doing?

I don't know if NR%4 will work since there can be a row of spaces after 3/4/5/6 rows of data
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