Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Pivot multiple

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Pivot multiple
# 1  
Old 12-11-2013
Pivot multiple
Dear friend,

I want to sum popul based on ville and reg.

input
Code:
date    country    ville    reg    popul
20131101    INDIA    Gujarat    College    322047286
20131101    USA    Oregon 2    Kindergaten    477305599
20131101    INDIA    Delhi 1    Ecole    255029428
20131101    MEXICO    Ardu 2    Unidentified    12700
20131101    MEXICO    Ardu 2    Kindergaten    382870479
20131101    INDIA    Delhi 2    Kindergaten    280004848
20131101    USA    Oregon 2    Unidentified    9400
20131102    USA    Auckland    College    149720282
20131102    MEXICO    Ardu 1    Student & Employee    18925553
20131102    INDIA    Delhi 2    Unidentified    15925
20131102    USA    Oregon 1    Student & Employee    14236005
20131102    USA    Auckland    Kindergaten    374236693
20131102    MEXICO    Ardu 1    Ecole    226592538
20131102    INDIA    Gujarat    Student & Employee    7976589
20131102    USA    Oregon 1    Ecole    151688356
20131103    USA    Auckland    Unidentified    7375
20131103    MEXICO    Stemp    College    122930128
20131103    USA    Auckland    Kindergaten    592258933
20131103    USA    Auckland    College    133411400
20131103    USA    Virginia    Student & Employee    7799909
20131103    MEXICO    Stemp    Kindergaten    611243392
20131103    USA    Virginia    Unidentified    10575
20131104    INDIA    Gujarat    College    358369384
20131104    INDIA    Gujarat    Ecole    67330897
20131104    USA    Virginia    Ecole    84923771
20131104    MEXICO    Stemp    Unidentified    29400
20131104    USA    Virginia    Student & Employee    21741745
20131105    INDIA    Delhi 1    Kindergaten    617543929
20131105    USA    Oregon 2    Kindergaten    365362849
20131105    USA    Oregon 2    College    168136515
20131105    MEXICO    Stemp    Student & Employee    18429693
20131105    USA    Oregon 2    Ecole    187090109
20131105    INDIA    Delhi 1    Unidentified    38300

Expected output

Code:
ville    College    Ecole    Kindergaten    Student & Employee    Unidentified
Ardu 1    0    226592538    0    18925553    0
Ardu 2    0    0    382870479    0    12700
Auckland    283131682    0    966495626    0    7375
Delhi 1    0    255029428    617543929    0    38300
Delhi 2    0    0    280004848    0    15925
Gujarat    680416670    67330897    0    7976589    0
Oregon 1    0    151688356    0    14236005    0
Oregon 2    168136515    187090109    842668448    0    9400
Stemp    122930128    0    611243392    18429693    29400
Virginia    0    84923771    0    29541654    10575

# 2  
Old 12-11-2013
Try
Code:
awk     'NR==1  {next}
         {for (i=1; i<=LnCnt; i++) if ($3 == Ln[i]) break; if (i > LnCnt) Ln[++LnCnt]=$3}
         {for (j=1; j<=HdCnt; j++) if ($4 == Hd[j]) break; if (j > HdCnt) Hd[++HdCnt]=$4}
         {Mx[$3,$4] += $5}
         END {printf "%15s", ""; for (j=1; j<=HdCnt; j++)  printf "%15s", Hd[j]; printf "\n";
              for (i=1; i<=LnCnt; i++)  {printf "%15s", Ln[i];
                                         for (j=1; j<=HdCnt; j++) printf "%015s", Mx[Ln[i], Hd[j]];
                                         printf "\n"
                                        }
             }
        ' FS="\t" file
                       College    Kindergaten          Ecole   UnidentifiedStudent & Employee
        Gujarat      358369384                      67330897                       7976589
       Oregon 2      168136515      365362849      187090109           9400
        Delhi 1                     617543929      255029428          38300
         Ardu 2                     382870479                         12700
        Delhi 2                     280004848                         15925
       Auckland      133411400      592258933                          7375
         Ardu 1                                    226592538                      18925553
       Oregon 1                                    151688356                      14236005
          Stemp      122930128      611243392                         29400       18429693
       Virginia                                     84923771          10575       21741745

Make very sure that columns are TAB separated and fields contain just spaces, if at all.
Last edited by RudiC; 12-11-2013 at 05:44 PM..
# 3  
Old 12-11-2013
A pipelined approach, very old school, is to sort your input by ville and then reg, so you can add up each reg for a ville and spit out the sum when the ville changes. To start the loop, you need a last_ville variable set to a safe value or unset. To end the loop, you need to add in a dummy line at EOF (which does not need to be sorted).
Code:
#!/bin/bash
 
unset last_ville
 
IFS='\t
'
 
echo "ville\tCollege\tEcole\tKindergaten\tStudent & Employee\tUnidentified"
 
(
sort -t'\t' -k3,5 in_file
echo "x\tx\tx\tdummyXX
)|while read date country ville reg popul
do
 if [ "$ville" != "$last_ville" ]
 then
  if [ "$last_ville" != "" ]
  then
   echo "$last_ville\t$coll_sum\t$ecole_sum\t$kinder_sum\t$se_sum\t$unid_sum"
  fi
  if [ "$ville" = "dummyXX" ]
  then
   break
  fi
  last_ville="$ville" coll_sum=0 ecole_sum=0 kinder_sum=0 se_sum=0 unid_sum=0
 fi
 case "$reg" in
 (College)
  (( coll_sum += popul ))
  ;;
 (Ecole)
 .
 .
 .
 (*) #default are unidentified
  (( unid_sum += popul ))
  ;;
 esac
done

I am assuming the fields are tab separated, so the embedded blanks do not set off the sort field identification. When is say \t, I mean a real tab character. If you "typeset -A pop_sum" and use the $ref as $pop_sum's key, it can hold all the sums, but then emptying it is very chatty. You might destroy it and recreate it, but then "" is not a zero, so you need to find a way to add the zero's for "" when printing. Tab separated text is very spreadsheet friendly.
Last edited by DGPickett; 12-11-2013 at 05:52 PM..
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pivot example

Hi all, I am new to shell scripting so pardon me for the questions I will be asking. I was given a task where I have to pivot my data Example Source SGPAPCTUMACCHEA Expected output SGP APC TUM SGP APC ACC SGP APC HEA Can anybody assist me on this?Please use CODE tags as required... (3 Replies)
Discussion started by: redaela
3 Replies

2. Programming

DB Query for Pivot

hi team With below results in Db2 v10.5 . Please refer column A and B are same,while Staus column defers with distinct values . A B STATUS Insert Update Old Insert Update New Insert Update Final Can someone guide how to... (2 Replies)
Discussion started by: Perlbaby
2 Replies

3. UNIX for Beginners Questions & Answers

Data Pivot

Good Day, I have file input 6285296582710|20170509|INDOTEL 6285296835209|20170509|INDOTEL 6285296940311|20170509|INDOTEL 6285297027737|20170509|MULTIFLAG 6285297027737|20170509|DELTA 6285297304373|20170510|INDOTEL 6285297384129|20170510|INDOTEL 6285296940311|20170510|MULTIFLAG... (2 Replies)
Discussion started by: radius
2 Replies

4. Shell Programming and Scripting

Pivot data using awk

Hi My Input is like below DELETE|MPI|AUD_UPD_AGENT|MPISYS INSERT|MPI|AUD_UPD_AGENT|MPISYS SELECT|MPI|AUD_UPD_AGENT|MPISYS UPDATE|MPI|AUD_UPD_AGENT|MPISYS DELETE|MPI|BDYMOD|MPISYS INSERT|MPI|BDYMOD|MPISYS SELECT|MPI|BDYMOD|MPISYS UPDATE|MPI|BDYMOD|MPISYS DELETE|MPI|BDYMOD_DESC|MPISYS... (4 Replies)
Discussion started by: dineshaila
4 Replies

5. Shell Programming and Scripting

Pivot Column using awk

Hello everyone I have a 20M file which is having the below sample layout 1111,ABC,100 1111,DEF,200 1111,XYZ,300 4444,LMN,100 4444,AKH,500 4444,WCD,400 2222,ABC,100 7777,DEF,300 7777,WCD,300 I need to covert this to below format Output Party_ID|ABC|AKH|DEF|LMN|WCD|XYZ... (5 Replies)
Discussion started by: morbid_angel
5 Replies

6. Shell Programming and Scripting

Pivot using awk

Hi, I am writing a code to basically pivot the data. awk -v var1="" -v var2="" -v var3="" -v var4="" -v var5="" -v Disp=0\ 'BEGIN {FS=":"; OFS="|";}\ /^Pattern1/ {var1=$2;Disp=0;} \ /^Pattern2/ {var2=$2;} \ /^Pattern3/ {var3=$2;} \ /^Pattern4/ {var4=$2;} \ /^Pattern5/... (5 Replies)
Discussion started by: tostay2003
5 Replies

7. Shell Programming and Scripting

Pivot script

Hi Please suggest a script that would do a horizontal pivot , on the fields separated by a semicolon Below is my input file 1|c2|aa 1|c3|dd 1|c4|cc 1|c5|aa 1|c6|ss 1|c7|dd 1|c8|bb 1|c9|jjj 1|c10|kkk 1|c11|fffg 1|c12|nnn;indi;pak;linf;wer 1|c13|lllnk;li;sdfsd;oiuo 1|c14|ppp... (5 Replies)
Discussion started by: mabarif16
5 Replies

8. Shell Programming and Scripting

Pivot file contents

Hi All, I am trying to pivot the contents in a file. Ex: I have a file sample.txt with data "A B C D", i need the contents to pivot & resulting file should look like "A B C ... (3 Replies)
Discussion started by: new_ds_man
3 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

pivot

I have a sql table with : Acitvity Date Value ABC 7/11 10 DEF 7/11 98 ABC 7/12 23 DEF 7/12 100 SER 7/12 67 GRH 7/13 123 HJY 7/14 12 I... (4 Replies)
Discussion started by: mukhanj
4 Replies
Login or Register to Ask a Question