Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Create Pivot table

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create Pivot table
# 1  
Old 03-06-2011
Create Pivot table
I would like to use awk to parse a file with three columns in, like:


Code:
Chennai,01,1
Chennai,07,1
Chennai,08,3
Chennai,09,6
Chennai,10,12
Chennai,11,19
Chennai,12,10
Chennai,13,12
Kerala,09,2
AP,10,1
AP,11,1
Delhi,13,1
Kerala,13,3
Chennai,00,3
Chennai,01,1
Chennai,02,1
Chennai,07,5
Chennai,08,5
Chennai,09,4
Chennai,10,15
Chennai,11,16
Chennai,12,9
Chennai,13,12




they represent the Circle the hour and the number of transactions.
There are thousand of lines, I would like to generate a single table
with hours as headers, the row name will be the Circle name, and the cells will be the sum of number of transaction for that Circle name
in that particular hour like:
Code:
=========== 0 1 2 3 4 5 6 7 ... 23
Chennai xxx xxx xxx ... . . . . . . . . . . . . . .
Kerala xxx xxx xxx ... . . . . . . . . . . . . . .
Delhi xxx xxx xxx ... . . . . . . . . . . . . . .
.
.
.
AP xxx xxx xxx ... . . . . . . . . . . . . . .

I only have access to the standard AWK as operating system HP-UX, no gawk ....

Thanks.
Last edited by radoulov; 03-06-2011 at 02:39 PM.. Reason: Code tags, please!
# 2  
Old 03-06-2011
Code:
awk -F, 'END {
  printf "%-15s", ("name" OFS)
  for (i = 0; ++i <= 23;)
    printf "%02d%s", i, (i < 23 ? OFS : RS RS)
  for (C in c) {
    printf "%-15s", (C OFS)
    for (i = 0; ++i <= 23;) 
      printf "%02d%s", t[C, sprintf("%02d", i)], (i < 23 ? OFS : RS)              
    }  
 }
{ 
  t[$1, $2] += $3; c[$1]  
  }' infile

# 3  
Old 03-08-2011
Create Pivot table
Input file is having data, they represent the Producttype,Circle,Hour,Servicetype,Count.
There are thousand of lines, I would like to generate a single table
with hours as headers, the row name will be the Circle|Producttype|Servicetype name,
and the cells will be the sum of number of transaction for that Circle name in that particular hour like:
Code:
Producttype,Circle,Hour,Servicetype,Count
Activation,Mumbai,20,13501,514
Activation,Mumbai,20,13502,27
Activation,Kerala,21,13501,1129
Activation,Kerala,21,13502,47
Activation,Mumbai,21,13501,600
Activation,Mumbai,21,13502,27
Activation,Kerala,22,13501,521
Activation,Kerala,22,13501,600
Activation,Kerala,22,13502,15
Activation,Mumbai,22,13501,205
Activation,Mumbai,22,13502,5
Activation,Mumbai,22,13502,500
Activation,Kerala,23,13501,242
Activation,Kerala,23,13502,1
Activation,Mumbai,23,13501,190
Activation,Mumbai,23,13502,1
PlanChange,Kerala,10,13502,18
PlanChange,Kerala,11,13502,11
PlanChange,Kerala,12,13502,2
PlanChange,Mumbai,14,13502,2
PlanChange,Kerala,15,13502,4
PlanChange,Mumbai,15,13502,7
PlanChange,Kerala,16,13502,1
PlanChange,Kerala,18,13502,1
PlanChange,Mumbai,18,13502,2
PlanChange,Mumbai,20,13502,1
PlanChange,Mumbai,21,13502,1
PlanChange,Mumbai,22,13502,4
PlanChange,Mumbai,23,13502,7

Radoulov Code using for Pivot table using awk command.
Code:
awk -F, 'END {
printf "%-36s", ("CIRCLE" OFS)
for (i = -1; ++i <= 23;)
printf "%04d%s", i, (i < 23 ? OFS : RS RS)
for (C in c) {
printf "%-38s", (C OFS)
for (i = -1; ++i <= 23;)
printf "%04d%s", t[C, sprintf("%02d", i)], (i < 23 ? OFS : RS)
}
}
{
t[$2,$1,$4,$3] += $5; c[$2,$1,$4]
}' ordercreated.txt

Result showing as
Code:
CIRCLE 0000 0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012 0013 0014 0015 0016 0017 0018 0019 0020 0021 0022 0023
ORISSAServiceDisconnect13501 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0219 0052
PunjabServiceDisconnect13502 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0004 0000
DelhiVAS13501 0031 0013 0004 0002 0000 0000 0010 0015 0059 0139 2082 0153 0242 0166 0384 0591 0538 0481 0315 0235 0188 2925 2271 1208
DelhiVAS13502 0001 0000 0002 0000 0000 0000 0000 0001 0000 0014 0001 0006 0024 0007 0011 0018 0019 0002 0020 0008 0009 0006 0011 0007
UP(E)Suspend13502 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0002 0001 0001 0000 0000 0000 0000
Instead of above result, wanted result in format as
CIRCLE 0000 0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012 0013 0014 0015 0016 0017 0018 0019 0020 0021 0022 0023
ORISSA|ServiceDisconnect|13501 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0219 0052
Punjab|ServiceDisconnect|13502 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0004 0000
Delhi|VAS|13501 0031 0013 0004 0002 0000 0000 0010 0015 0059 0139 2082 0153 0242 0166 0384 0591 0538 0481 0315 0235 0188 2925 2271 1208
Delhi|VAS|13502 0001 0000 0002 0000 0000 0000 0000 0001 0000 0014 0001 0006 0024 0007 0011 0018 0019 0002 0020 0008 0009 0006 0011 0007
UP(E)|Suspend|13502 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0002 0001 0001 0000 0000 0000 0000

Last edited by Franklin52; 03-08-2011 at 06:15 AM.. Reason: Please use code tags
# 4  
Old 03-08-2011
try this,
Code:
 awk -F"," 'BEGIN{printf "Circle|Producttype|Servicetype|"
for(i=0;i<=23;i++){printf "%04d ",i;}printf "\n";}
NR>1{b[$2"|"$1"|"$4]++;a[$1"|"$2"|"$3"|"$4]+=$5} 
END {for(y in b) {printf "%s\t\t",y;for(i=0;i<=23;i++)
{for(k in a){split(k,x,"|");if(y==x[2]"|"x[1]"|"x[4] && i==int(x[3]))
{printf "%04d ",a[k];z=1} }
if(z==0){printf "%04d ",0;};z=0;}printf "\n";}}' ordercreated.txt

Last edited by pravin27; 03-08-2011 at 06:53 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awk- Pivot Table Averages

Hi everyone, Has anyone figured out yet how to do pivot table averages using AWK. I didn't see anything with regards to doing averages. For example, suppose you have the following table with various individuals and their scores in round1 and round2: SAMPLE SCORE1 SCORE2 British ... (6 Replies)
Discussion started by: Geneanalyst
6 Replies

2. Shell Programming and Scripting

Create a pivot table from CSV file

Gents, Can you please help me to create a pivot table from a csv file. ( I have zip the csv file) Using the file attached, columns 1,28 and 21 i would like to get something like this output JD Val 1 2 3 4 5 6 7 8 9 10 11 12 Total... (4 Replies)
Discussion started by: jiam912
4 Replies

3. Shell Programming and Scripting

Create Script from table

I have a table ABC with 4 columns and below data Col1,Col2,Col3,Col4 prod,10,12,joba prod,10,11,jobb qa,10,12,jobc I want to create an output file like this Server:prod StartTime:10 EndTime:12 JobName:joba Server:prod StartTime:10 EndTime:11 JobName:jobb (3 Replies)
Discussion started by: traininfa
3 Replies

4. Programming

Create table with date format.

Hello, Could you please let me know the correct format of CREATE TABLE statement with DATE format. CREATE TABLE EMP_TABLE1 ( NAME VARCHAR(6) PRIMARY KEY, ADDRESS VARCHAR(6), BIRTH_DATE DATE ); I want BIRTH_DATE to be in "YYYYMMDDHHMISS" format. How we can create table with... (4 Replies)
Discussion started by: Poonamol
4 Replies

5. Shell Programming and Scripting

Create DB table through shell script

Hi, Can anyone tell me that, How to create table in Oracle database through shell script(ksh). Table contains 3 fields, 1] Emp ID, String, primary key 2] Name, String 3] B Date, date. Thanks in advance. (6 Replies)
Discussion started by: Poonamol
6 Replies

6. UNIX and Linux Applications

create table via stored procedure (passing the table name to it)

hi there, I am trying to create a stored procedure that i can pass the table name to and it will create a table with that name. but for some reason it creates with what i have defined as the variable name . In the case of the example below it creates a table called 'tname' for example ... (6 Replies)
Discussion started by: rethink
6 Replies

7. UNIX for Dummies Questions & Answers

Create a table - very new to unix

I need to create a simple table of information by grepping several columns from various files and display them all at once with simple headers on top. Can anyone help get me started? I am very new to unix so I really have no idea how to work with this and I appreciate any help I can get! Let me... (11 Replies)
Discussion started by: aj250
11 Replies

8. Shell Programming and Scripting

Create table

Hi I want create table based on csv file .I have come up with some informations getting columns names and then no idea from that .Please any help file.txt col1|col2|col3|col4|col5 1234|zxxxx|xcvvv|300|null file.sh file=$1 c1=`head -1 $file|tr "|" "\n" |cat -n` echo "$c1"... (31 Replies)
Discussion started by: mohan705
31 Replies

9. Shell Programming and Scripting

Pivot table

Hello everyone, I would like to use awk to parse a file with three columns in, like: monday 0 1 monday 1 1 monday 2 1 monday 3 1 monday 4 1 monday 5 1 tuesday 0 5 tuesday 1 1 tuesday 2 1 tuesday 3 1 tuesday 4 1 wednesday 0 1 monday 5 25 they represent the day the hour and the... (2 Replies)
Discussion started by: gio001
2 Replies

10. Shell Programming and Scripting

create new table/field

Dear Folks, If I have 2 files, say A and B in format: A: $1 $2 01032 12856 01041 13351 01042 11071 01042 12854 01042 12862 01042 12866 . . . and B: (2 Replies)
Discussion started by: Gr4wk
2 Replies
Login or Register to Ask a Question