Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Awk: Interchange the Rows and column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk: Interchange the Rows and column
# 1  
Old 04-25-2014
Awk: Interchange the Rows and column
Hi,
I have following input and want to change it to following output

INPUT
Code:
01-APR-14,KB,822714
01-APR-14,MB,8133431
02-APR-14,KB,757140
02-APR-14,MB,7770368
03-APR-14,KB,815427
03-APR-14,MB,7590511
04-APR-14,MB,7529895
04-APR-14,KB,779561
05-APR-14,MB,8151537
05-APR-14,KB,809675

Output:
Code:
DATE,KB,MB
01-APR-14,822714,8133431
02-APR-14,757140,7770368
03-APR-14,815427,7590511
04-APR-14,779561,7529895
05-APR-14,809675,8151537

code:
Code:
awk -F, '{a[$1]=a[$1]?a[$1]FS$3:$1FS$3}
END{for(i in a) print a[i]|"sort"}
' INPUT

Present output from the above code is as follows with error as on 05-APR-14 MB field is coming as first entry against rest of the KB fields due to which output is not generating correct output. Please suggest
Code:
01-APR-14,822714,8133431
02-APR-14,757140,7770368
03-APR-14,815427,7590511
04-APR-14,7529895(MB field),779561(KB FIELD)
05-APR-14,8151537(MB field),809675(KB FIELD)

Moderator's Comments:
Mod Comment CODE tags also for output samples
Last edited by Scrutinizer; 04-25-2014 at 06:38 AM.. Reason: Please ignore & do not post; mod:extra CODE tags
# 2  
Old 04-25-2014
Hello,

If order doesn't matters then following may help you.


Code:
sort -t, -k2 check_change_check12113 | awk -F"\," 'BEGIN{print "DATE,KB,MB"} {a[$1]=a[$1] OFS $3} END{for(i in a){gsub(/^[[:space:]]/,X,a[i]) gsub(/^\,/,X,a[i]); print i OFS a[i]}}' OFS=","

Output will as follows.

Code:
DATE,KB,MB
04-APR-14,779561,7529895
03-APR-14,815427,7590511
02-APR-14,757140,7770368
05-APR-14,809675,8151537
01-APR-14,822714,8133431


Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
siramitsharma
# 3  
Old 04-25-2014
With maintaning order

Code:
sort -t, -k2 input_file | nawk -F, '{a[$1]=a[$1]?a[$1]FS$3:$1FS$3} END{for(i in a) print a[i]|"sort"}'

This User Gave Thanks to Makarand Dodmis For This Post:
siramitsharma
# 4  
Old 04-25-2014
Try:
Code:
awk 'BEGIN{print "DATE,KB,MB"} {A[$2]=$3} !(NR%2){print $1, A["KB"], A["MB"]}' FS=, OFS=, file

Code:
DATE,KB,MB
01-APR-14,822714,8133431
02-APR-14,757140,7770368
03-APR-14,815427,7590511
04-APR-14,779561,7529895
05-APR-14,809675,8151537

Last edited by Scrutinizer; 04-25-2014 at 07:28 AM..
These 2 Users Gave Thanks to Scrutinizer For This Post:
RavinderSingh13 siramitsharma
# 5  
Old 04-26-2014
Hi Scrutinizer,
Unable to understand the below, can you please throw some light

Code:
!(NR%2){print $1, A["KB"], A["MB"]}

# 6  
Old 04-26-2014
Sure:
Code:
!(NR%2){                        # On every even line 
  print $1, A["KB"], A["MB"]    # Print the first field, array element "KB" and "MB"
}

The array always has only two elements..
This User Gave Thanks to Scrutinizer For This Post:
siramitsharma
# 7  
Old 04-28-2014
Thanks Scrutinizer for code, I am big fan of you.

Thanks,
R. Singh
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare values in multiple rows in one column using awk

I would like to compare values in column 8, and grep the ones where the different is > 1, columns 1 and 2 are the key for array. Every 4 rows the records values in columns 1 and 2 changed. Then, the comparison in the column 8 need to be done for the 4 rows everytime columns 1 and 2 changed ... (4 Replies)
Discussion started by: jiam912
4 Replies

2. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

3. Shell Programming and Scripting

Create new rows for each column value with awk

Hi, I have the following type of data that is separated by tabs: -2 abandonar abandono abandonas abandona abandonamos abandonáis abandonan -4 abandonado abandonada abandonados abandonadas -2 abandona abandonos ... (1 Reply)
Discussion started by: owwow14
1 Replies

4. Shell Programming and Scripting

UNIX script to interchange column values

Hi, I have the below script which is fetching the data from a file as below: awk -F "~" 'NR>1 {print $5}' 1.txt The o/p is like: 1452350 1458638 1452350 2 5696837 No i want to put 5696837 before 1458638 in above o/p So final o/p should look like: 1452350 5696837 1458638 (2 Replies)
Discussion started by: Vivekit82
2 Replies

5. Shell Programming and Scripting

awk to transpose preceding row to 1st column of next rows

Gurus: How can I transpose the output below to a format in which I can plot a graph to show VSZ memory usage by PIDs? stdout: Tue Jan 22 07:29:19 CUT 2013 42336296 1841272 java wilyadm 21889232 438616 jlaunch sidadm 42532994 414336 jlaunch sidadm Tue Jan 22 07:49:20 CUT 2013... (1 Reply)
Discussion started by: ux4me
1 Replies

6. Shell Programming and Scripting

awk command to print only selected rows in a particular column specified by column name

Dear All, I have a data file input.csv like below. (Only five column shown here for example.) Data1,StepNo,Data2,Data3,Data4 2,1,3,4,5 3,1,5,6,7 3,2,4,5,6 5,3,5,5,6 From this I want the below output Data1,StepNo,Data2,Data3,Data4 2,1,3,4,5 3,1,5,6,7 where the second column... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

awk transpose rows to column

Need to transpose in awk rows to column like this: input: A1,6,5,4 3,2,1, A2,8,7,9,10,11,12,13,14 A3,1,2,3,5,7,8,9 A4,9,4,8,1,5,3, output: A1,1 A1,2 A1,4 ... A2,7 A2,8 ... A3,1 A3,2 ... A4,1 A4,3 (5 Replies)
Discussion started by: sdf
5 Replies

8. UNIX for Dummies Questions & Answers

how to count number of rows and sum of column using awk

Hi All, I have the following input which i want to process using AWK. Rows,NC,amount 1,1202,0.192387 2,1201,0.111111 3,1201,0.123456 i want the following output count of rows = 3 ,sum of amount = 0.426954 Many thanks (2 Replies)
Discussion started by: pistachio
2 Replies

9. Shell Programming and Scripting

awk to select rows based on condition on column

I have got a file like this 003ABC00281020091005000100042.810001 ... (8 Replies)
Discussion started by: Maruti
8 Replies

10. Shell Programming and Scripting

Requesting an AWK code to generate averaged rows in a column

Hello, I request your kind help in solving this problem... I have a file with two columns and "n" number of rows. For the first column, it won't be change. For the second column, I want to take the average of the first three rows. Then assign the averaged value to the first three rows. This... (8 Replies)
Discussion started by: solracq
8 Replies
Login or Register to Ask a Question