Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Pivoting values from column to rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Pivoting values from column to rows
# 1  
Old 04-13-2017
Pivoting values from column to rows
I/P:
I/P:

Code:
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:
Code:
                     a      b    c
2017/01/01     10   20   40
2017/02/01     10    20   30

Last edited by Don Cragun; 04-13-2017 at 08:16 PM.. Reason: Add missing CODE tags and remove duplicated text.
# 2  
Old 04-13-2017
What operating system and shell are you using?

What have you tried to solve this problem on your own?
# 3  
Old 04-13-2017
I am using Linux and I don't know how to accomplish this one.
# 4  
Old 04-14-2017
You didn't answer the question about what shell you're using. The following was written and tested using a Korn shell, but should work with any shell that is based on Bourne shell syntax (e.g., ash, bash, dash, ksh, and zsh; but not csh and its derivatives):
Code:
#!/bin/ksh
awk -v debug=$# '
BEGIN {	OFS = "\t"
}
!($1 in d) {
	# We have a new date; save it for use in output headings.
	date[++nd] = $1
	d[$1] = nd
	if(debug)
		printf("date[%d]=%s & d[%s]=%d added\n", nd, $1, $1, nd)
}
!($2 in h) {
	# We have a new output column heaer; save it for use in output headings.
	head[++nh] = $2
	h[$2] = nh
	if(debug)
		printf("head[%d]=%s & h[%s]=%d added\n", nh, $2, $2, nh)
}
{	# Save the data from this input row for output later.
	row[$1, $2] = $3
	if(debug)
		printf("row[%s, %s]=%s added\n", $1, $2, $3)
}
END {	# Print header line.
	printf(OFS OFS)
	for(i = 1; i <= nh; i++)
		printf("%s%s", head[i], (i == nh) ? ORS : OFS)
	# Print accumulated data rows.
	for(i = 1; i <= nd; i++) {
		printf("%s%s", date[i], OFS)
		for(j = 1; j <= nh; j++)
			printf("%s%s", row[date[i], head[j]],
			    (j == nh) ? ORS : OFS)
	}
}' file

If you invoke this script without operands, it produces the output:
Code:
		a	b	c
2017/01/01	10	20	40
2017/02/01	10	20	30

if the file named file contains the sample input you provided in post #1 in this thread. If you invoke it with one or more operands, it provides debugging information showing how the arrays used to control the output are loaded from the input file in addition to producing the desired results:
Code:
date[1]=2017/01/01 & d[2017/01/01]=1 added
head[1]=a & h[a]=1 added
row[2017/01/01, a]=10 added
head[2]=b & h[b]=2 added
row[2017/01/01, b]=20 added
head[3]=c & h[c]=3 added
row[2017/01/01, c]=40 added
date[2]=2017/02/01 & d[2017/02/01]=2 added
row[2017/02/01, a]=10 added
row[2017/02/01, b]=20 added
row[2017/02/01, c]=30 added
		a	b	c
2017/01/01	10	20	40
2017/02/01	10	20	30

If someone else wants to try this on a Solaris/SunOS system, change awk in this script to /usr/xpg4/bin/awk or nawk.
This User Gave Thanks to Don Cragun For This Post:
Booo
# 5  
Old 04-14-2017
Try also
Code:
awk     '
        {LN[$1]; HD[$2]; MX[$1,$2]=$3}

END     {               printf "%10s", ""; for (i in HD) printf "%10s", i; print "";
         for (j in LN) {printf "%10s",j;   for (i in HD) printf "%10s", MX[j,i]; print ""}
        }
' file
                   a         b         c
2017/02/01        10        20        30
2017/01/01        10        20        40

This User Gave Thanks to RudiC For This Post:
RavinderSingh13
# 6  
Old 04-14-2017
Thanks and It worked fine as long as the rows and columns are same.

if the seventh line contains "2017/02/01 d 70" then it fails.
# 7  
Old 04-14-2017
In what way did my suggestion fail? If I change the last line of your sample file as suggested in post #6, I get the following output from the code I suggested in post #4:
Code:
		a	b	c	d
2017/01/01	10	20	40	
2017/02/01	10	20		30

What output were you expecting?

PS: Note that the code I suggested prints out columns in the order in which input field 2 values were first seen and prints out rows in the order in which input field 1 values were first seen.

The simpler and faster code RudiC suggested prints rows and columns in random order (but the values in each column should be consistent).
Last edited by Don Cragun; 04-14-2017 at 09:07 PM.. Reason: Add PS.
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. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

3. Shell Programming and Scripting

Choosing rows based on column values

I have a .csv file: A,B,0.6 C,D,-0.7 D,E,0.1 A,E,0.45 D,G, -0.4 I want to select rows based on the values of the 3rd columns such that it is >=0.5 or <= -0.5 Thanks. A,B,0.6 D,G, -0.7 (1 Reply)
Discussion started by: Sanchari
1 Replies

4. Shell Programming and Scripting

Remove rows with e column values

Hi All, I have a big file with 232 columns and 9 million rows, I want to delete all rows with same column values in col3 through col232. Also the output should be sorted based on first 2 columns. Here is a reduced example with 6 columns. I want to remove rows with duplicate values in col3... (9 Replies)
Discussion started by: alpesh
9 Replies

5. 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

6. Shell Programming and Scripting

join rows based on the column values

Hi, Please help me to convert the input file to a new one. input file: -------- 1231231231 3 A 4561223343 0 D 1231231231 1 A 1231231231 2 A 1231231231 4 D 7654343444 2 A 4561223343 1 D 4561223343 2 D the output should be: -------------------- 1231231231 3#1#2 A 4561223343 0 D... (3 Replies)
Discussion started by: vsachan
3 Replies

7. UNIX for Dummies Questions & Answers

count number of rows based on other column values

Could anybody help with this? I have input below ..... david,39 david,39 emelie,40 clarissa,22 bob,42 bob,42 tim,32 bob,39 david,38 emelie,47 what i want to do is count how many names there are with different ages, so output would be like this .... david,2 emelie,2 clarissa,1... (3 Replies)
Discussion started by: itsme999
3 Replies

8. UNIX for Dummies Questions & Answers

How to assign scores to rows based on column values

Hi, I'm trying to assign a score to each row which will allow me to identify which rows differ. In the example file below, I've used "," to indicate column separators (my actual file has tab separators). In this example, I'd like to identify that row 1 and row 5 are the same, and row 2 and row... (4 Replies)
Discussion started by: auburn
4 Replies

9. Shell Programming and Scripting

How to consolidate values in one column from different rows into one?

Hi Guys, Thank you all for helping me with my different queries and I continue to get better at scripting because of help from all of you! I have a file that would look something like - ID SUB ID VALUE 1 10 5 2 18 7 1 ... (1 Reply)
Discussion started by: sncoupons
1 Replies

10. Shell Programming and Scripting

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 ... (1 Reply)
Discussion started by: dprakash
1 Replies
Login or Register to Ask a Question